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;