HAVING Clause

Having

Having Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;

read more

SQL Joins

SQL Joins  Microsoft SQL Server 2008 Management and Administration

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements

1) SQL Inner Join  PostgreSQL: A Better Way to Learn PostgreSQL (Prima Development)


In SQL Inner Join  the query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate

SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

2. SQL Equi-join     Build Your Own Database Driven Website Using PHP and MySQL
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

SQL provides an optional shorthand notation for expressing equi-joins, by way of the USING construct
 SELECT *
FROM employee
INNER JOIN department
USING (DepartmentID


3) SQL Natural join   Beginning Database Design: From Novice to Professional
 A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns

SELECT *
FROM employee NATURAL JOIN department

4) SQL Cross join      Learn SQL

A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.

SELECT *
FROM employee CROSS JOIN department


5) SQL Left Outer Join     Sams Teach Yourself SQL in 24 Hours (4th Edition)

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).

SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

6) SQL Right Outer Join  Learning MySQL

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.

SELECT * 
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

7. SQL Full outer join    SQL: A Beginner's Guide

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT *FROM employee

FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

8. Self-join          MySQL: Your visual blueprint for creating open source databases

A self-join is joining a table to itself

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country 
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
                                                                         

read more

Full Text Search

Full-Text Search (Contains & Free Text )

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max)

FREE TEXT
 

The FREETEXT predicate is part of the WHERE clause, and supports searching for words and phrases in text columns. The FREETEXT predicate is better suited for finding documents containing combinations of the search terms spread throughout the column, in contrast to the CONTAINS predicate, which is better suited for finding "exact" matches. You can also perform exact-phrase matching with the FREETEXT predicate by placing the phrase in double quotation marks.

 

This query returns you all paths to files which contain the "Hello World" text.

SELECT Path

 FROM SCOPE()
WHERE FREETEXT(Contents, 'Hello World')

read more

CONTAINS Predicate

CONTAINS Statement

The CONTAINS predicate provides several text-matching options for building queries. Typically, you use this predicate to query the Contents property— the text contents of a file. You can also query other textual properties with content, such as DocTitle, DocSubject, or a user-defined property. This predicate is an optional part of the optional WHERE clause of the SELECT statement.

1 The below stated query finds all employees with a dept 10 and  that contain the e-name "jhones".

SELECT E-Name, Dept
FROM Employee
WHERE Dept=10
AND CONTAINS(E-Name, 'jhones');
 
2. Using CONTAINS and OR with
The following example returns all category descriptions containing strings with prefixes of either "chain" or "full".
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');

3. Using CONTAINS with
The following example returns all product names that have the word bike near the word performance.

SELECT Description

FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');

4. Using CONTAINS with

The following example searches for all products with words of the form ride: riding, ridden, and so on.

SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');

5.Using CONTAINS with A Logical Operator (AND)
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');

read more

WHERE Clause

The WHERE clause specifies the search condition for selecting rows in the virtual table defined by the FROM clause. The matching rows constitute the resulting rowset. The WHERE clause is an optional part of the SELECT statement. However, if the WHERE clause is absent, the resulting rowset can be extremely large because all rows of the virtual table defined by the FROM clause are returned in the resulting rowset.

Select e_name

from employee

where dept_id=10;

Extract all those employees that have 'ali' as their names and are working in dept 10

Select *
from employee
where e_name='ali'
and dept=10;
Select all invoices for the entire month of April of 2010
Select *
from invoice
where invoid_date between'2010-04-01 00:00:00' and '2010-04-31 23:59:59'

read more

EXISTS

EXISTS simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing.
SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')

read more

Like Operator

1. LIKE with the % wildcard character
The following example finds all telephone numbers that have area code 415 in the Contact table.

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE phone LIKE '415%'

The following example finds all telephone numbers that have area last digits 415 in the Contact table.

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE phone LIKE '%415'

The following example finds all telephone numbers that have area 415 in the Contact table.

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE phone LIKE '%415%'

3. Usng NOT LIKE with the % wildcard character

SELECT FirstName, LastName, Phone
FROM Person.Contact
WHERE Phone NOT LIKE '415%' AND FirstName = 'Gail'ORDER BY LastName;

4. Using the [ ] wildcard characters

The following example finds Contacts with the first name of Cheryl or Sheryl.

SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE FirstName LIKE '[CS]heryl';

The following example finds the rows for Contacts with last names of Zheng or Zhang.

SELECT LastName, FirstName, Phone
FROM Person.ContactWHERE LastName
LIKE 'Zh[ae]ng'ORDER BY LastName ASC, FirstName ASC;

read more

UNION/INTERSECT CLAUSES

1. Union Clause
The UNION clause combines the results from two or more SQL SELECT statements into a single result set containing rows from all the queries in the UNION operation

By default, UNION eliminates duplicate rows from the combined result set.

select count(*) as total applications
from Employee_Application

union

select count(*) as total_applciations
from Loan_Applications

2.Intersect Clause

The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

SELECT ProductID FROM Production.Product
INTERSECT
SELECT ProductID FROM Production.WorkOrder

3. Except Clause
SELECT ProductID FROM Production.Product
EXCEPT
SELECT ProductID FROM Production.WorkOrder

read more

INSERT Statement Syntax

Adds a new row to a table or a view.

Examples
1. Use a simple INSERT

Insert into table_name VALUES (1, 'Row #1')

2.Insert data that is not in the same order as the columns

Insert into table_name (column_2, column_1) VALUES ('Row #1',1)

3. Insert data with fewer values than columns

i. Insert into table_name (column_4) VALUES ('Explicit value')
ii. Insert into table_name (column_2,column_4) VALUES ('Explicit value', 'Explicit value')
iii.Insert into table_name (column_2,column_3,column_4) VALUES ('Explicit value',-44,'Explicit value')

4.Load data into a table with an identity column

SET IDENTITY_INSERT T1 ON INSERT INTO T1 (column_1,column_2) VALUES (-99,'Explicit identity value')

5 .Insert data using the TOP clause in a SELECT statement

INSERT INTO new_authors SELECT TOP 10 * FROM authors

6. Insert data using default values options

insert into table_name default values

read more

SELECT - SQL Command

Select Syntax

Purpose: Retrieves data from one or more tables

The following code examples show many ways of retrieving data with the SQL SELECT command

Example#1 Select Fields
SELECT emp_id, e_name FROM Employee

Example#2 Using Alias
displays all records from a query in a specified column using the AS Column_Name clause

SELECT city_name AS 'City' FROM customer

Example#3 Top 10 valus in SQL SERVER

SELECT top 10 * FROM customer


Example#4 Top 10 valus in MySQL

SELECT * FROM customer
limit 10


Example#4 Second Largest value in MySQL

SELECT * FROM customer
limit 2,2

read more