SQL Statements

HAVING Examples

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;

COUNT() Example

SELECT COUNT(ProductID) FROM Products;

UNION Example (only distinct values)

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

UNION ALL Example (duplicate values also)

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;

UNION With WHERE

SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;

UNION ALL With WHERE

SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;

Alias for Columns Examples

SELECT CustomerID as ID, CustomerName AS Customer FROM Customers; SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

(Address, PostalCode, City and Country)

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;

More HAVING Examples

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10; Try it Yourself » SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE LastName = 'Davolio' OR LastName = 'Fuller' GROUP BY LastName HAVING COUNT(Orders.OrderID) > 25;

MAX() Example

SELECT MAX(Price) AS LargestPrice FROM Products;

MIN() Example

SELECT MIN(Price) AS SmallestPrice FROM Products;

ANY Examples

SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);

SUM() Example

SELECT SUM(Quantity) FROM OrderDetails;

GROUP BY With JOIN Example

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;

INSERT INTO

The INSERT INTO statement is used to insert new records in a table.

MIN() and MAX()

The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT INTO Statement

The SELECT INTO statement copies data from one table into a new table.

TOP, LIMIT or ROWNUM

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.

UPDATE Syntax

UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;

FROM

FROM table_name

Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements. --Select all: SELECT * FROM Customers; SELECT * FROM Customers -- WHERE City='Berlin'; /*Select all the columns of all the records in the Customers table:*/ SELECT * FROM Customers;

DELETE Syntax

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

INSERT INTO SELECT Examples

INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers; INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';

INSERT INTO SELECT Syntax

INSERT INTO table2 SELECT * FROM table1 WHERE condition; INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;

INSERT INTO Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); INSERT INTO table_name VALUES (value1, value2, value3, ...);

Another UNION Example

SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;

SELECT INTO Syntax

SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition; SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition;

LIMIT (MySQL)

SELECT * FROM Customers LIMIT 3; SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;

ORDER BY DESC

SELECT * FROM Customers ORDER BY Country DESC; SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

ROWNUM (Oracle)

SELECT * FROM Customers WHERE ROWNUM <= 3; SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;

SELECT

SELECT *, SELECT column1, column2 FROM table_name;

AVG() Example

SELECT AVG(Price) FROM Products;

SQL EXISTS Examples

SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20); SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);

TOP (SQL)

SELECT TOP 3 * FROM Customers; SELECT TOP 3 * FROM Customers WHERE Country='Germany';

ORDER BY Syntax

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

EXISTS Syntax

SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

ALL Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

ANY Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

HAVING Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

GROUP BY Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;

UNION Syntax

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

UNION ALL Syntax

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

SQL Statements order

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (sweaty feat will give horrible odors)

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.

ALL Example

The ALL operator returns TRUE if all of the subquery values meet the condition. SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

ANY and ALL Operators

The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition.

ANY

The ANY operator returns TRUE if any of the subquery values meet the condition.

SQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows that matches a specified criteria. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.

DELETE

The DELETE statement is used to delete existing records in a table.

EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records.

GROUP BY Statement

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. INSERT INTO SELECT requires that data types in source and target tables match The existing records in the target table are unaffected

UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in each SELECT statement must also be in the same order

UPDATE

The UPDATE statement is used to modify the existing records in a table.

WHERE

The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition. WHERE Country='Mexico';

Alias for Tables Example

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter): SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;