SQL Query Template

1. SELECT column_name,column_name FROM table_name WHERE column_name operator value AND column_name operator value OR column_name operator value;

2. SELECT DISTINCT column_name,column_name FROM table_name;

//to return only distinct (different) values.

3. SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC;

4. INSERT INTO table_name VALUES (value1,value2,value3,...);

// first form does not specify the column names where the data will be inserted, only their values

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

// second form specifies both the column names and the values to be inserted

6. UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

7. DELETE FROM table_name WHERE some_column=some_value;

// it deletes rows in a table. Note: this command can’t be undo

8. DELETE * FROM table_name;

// deletes all rows from table. Note: this command can’t be undo

9. SELECT TOP number|percent column_name(s) FROM table_name;

10. SELECT * FROM Customers WHERE City LIKE 's%' OR LIKE ‘%op’ OR LIKE ‘%lan%’;

//"%" sign is used to define wildcards (missing letters) both before and after the pattern.

11. SELECT * FROM Customers WHERE City LIKE '_erlin'

// substitute for a single character

12. SELECT * FROM Customers WHERE City LIKE '[a-c]%' OR LIKE ‘[abc]’ OR LIKE ‘[def]%’ OR LIKE ‘%[!abc]’

// Sets and ranges of characters to match or Matches only a character NOT specified within the brackets

13. SELECT * FROM Customers WHERE City IN ('Paris','London');

// IN operator allows you to specify multiple values in a WHERE clause.

14. SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

15. SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

16. SELECT column_name AS alias_name FROM table_name;

// SQL aliases are used to give a database table, or a column in a table, a temporary name.

17. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

18. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

// also called LEFT OUTER JOIN

19. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

// also called RIGHT OUTER JOIN

20. SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

21. SELECT column_name(s) FROM table1

UNION // no. of column_names selected should be equal in both table

SELECT column_name(s) FROM table2;

Ex: SELECT CategoryID, CategoryName,Description FROM Categories

UNION

SELECT ShipperID, ShipperName, Phone FROM Shippers;

22. CREATE TABLE tablename

(column1 char(20) PRIMARY KEY,

// Fixed length character string. UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness

column2 varchar(30) UNIQUE,

// Variable-length character string. Max size is specified in parenthesis. PRIMARY KEY automatically has a UNIQUE

column3 number(23) FOREIGN KEY,

// Number value with a max number of columns specified in parenthesis

column4 Date NOT NULL,

// Date value

column5 number(23,4) DEFAULT,

// A number with a max no. of digits of "size" and a maximum number of "d" digits to the right of the decimal

column6 int CHECK);

// Check: Ensures that the value in a column meets a specific condition

23. DROP TABLE table_name

// DROP TABLE statement is used to delete a table.

24. TRUNCATE TABLE table_name

// if we only want to delete the data inside the table, and not the table itself?

25. ALTER TABLE table_name ADD column_name datatype

// ADD, DELETE or MODIFY

26. CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

27. SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

a. AVG() - SELECT AVG(column_name) AS display_column_name FROM table_name;

b. COUNT() - SELECT COUNT(DISTINCT column_name) FROM table_name;

c. FIRST() - SELECT FIRST(column_name) FROM table_name

//works only with MS Access

d. LAST() - SELECT LAST(column_name) FROM table_name;

//works only with MS Access

e. MAX() - SELECT MAX(column_name) AS display_column_name FROM table_name;

f. MIN() - SELECT MIN(column_name) AS display_column_name FROM table_name;

g. SUM() - SELECT SUM(column_name) FROM table_name

28. SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

a. UCASE() - Converts a field to upper case

b. LCASE() - Converts a field to lower case

c. MID() - Extract characters from a text field

d. LEN() - Returns the length of a text field

e. ROUND() - Rounds a numeric field to the number of decimals specified

f. NOW() - Returns the current system date and time

g. FORMAT() - Formats how a field is to be displayed

29. SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

Ex: SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders

LEFT JOIN Shippers

ON Orders.ShipperID=Shippers.ShipperID

GROUP BY ShipperName;

30. SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

Ex: 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;