SQL Query

Title Query Description
Distinct (unique) SELECT DISTINCT Country FROM Customers;
And/or/not SELECT * FROM Customers WHERE Country='Germany' OR Country='Spain' and NOT Country='Japan;
Order by SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
Insert into INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
Null values SELECT column_names FROM table_name
WHERE column_name IS NULL;
Update UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
Delete DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
Top rows SELECT * FROM Customers LIMIT 3;
SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
SELECT TOP 3 * FROM Customers WHERE Country='Germany';		 MySQL
Oracle
SQL Server/MS Access
MIN/MAX SELECT MIN(Price) AS SmallestPrice FROM Products;
SELECT MAX(Price) AS LargestPrice FROM Products;
Count, Average, Sum SELECT COUNT(ProductID), AVG(Price), SUM(Quantity) FROM Products;
Like SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; _ : single char
%: multiple char
h[oa]t: finds hot and hat, but not hit
h[^oa]t: finds hit, but not hot and hat
c[a-b]t: finds cat and cbt
IN SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
Between SELECT * FROM Products WHERE Price BETWEEN 10 AND 20
SELECT * FROM Products WHERE ProductName BETWEEN "Carnarvon" AND "Chef"
SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
Union SELECT City, Country FROM Customers WHERE Country='Germany'
UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;		 Only union will take distinct values whereas union all will take all values
GroupBY --
Having --
Exists SELECT SupplierName FROM Suppliers WHERE
EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);		 get data from subquery if it returns some row(s)
Any/All
INTO SELECT column1, column2, column3, INTO newtable [IN externaldb] FROM oldtable WHERE condition;
SELECT * INTO newtable FROM oldtable WHERE 1 = 0;		 create a new table with data from oldtable
create and empty table
Insert into select INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';		 copies data from one table and insert into other
Case SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;		 Here a new column 'QuantityText' will be created
Ifnull SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products; if any value in column UnitsOnOrder is null then 0 will be used
Stored procedure CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
EXEC SelectAllCustomers;		 Just like a function which you can call later