| SELECT |
SELECT column1, column2, ... FROM table_name; |
SELECT statement is used to select data from a database. |
SELECT * FROM Customers; |
| WHERE |
SELECT column1, column2, ... FROM table_name WHERE condition; |
WHERE clause is used to extract only those records that fulfill a specified condition |
SELECT * FROM Customers WHERE Country='Mexico'; |
| COUNT |
SELECT COUNT * FROM table_name; |
COUNT is a function that takes the name of a column as argument and counts the number of rows when the column is not NULL |
SELECT COUNT(country) FROM placeofinterest WHERE country = 'Canada' |
| DISTINCT |
SELECT DISTINCT columnname FROM table_name; |
DISTINCT function is used to specify that the statement is a query which returns unique values in specified columns. |
SELECT DISTINCT country FROM placeofinterest WHERE type='historical'; |
| LIMIT |
SELECT * FROM table_name LIMIT number; |
LIMIT is a clause to specify the maximum number of rows the result set must have. |
SELECT * FROM placeofinterest WHERE airport="pearson" LIMIT 5; |
| INSERT |
INSERT INTO table_name (column1,column2,column3...) VALUES(value1,value2,value3...); |
INSERT is used to insert new rows in the table. |
INSERT INTO placeofinterest (name,type,city,country,airport) VALUES('Niagara Waterfalls','Nature','Toronto','Canada','Pearson'); |
| UPDATE |
UPDATE table_name SET[[column1]=[VALUES]] WHERE [condition]; |
UPDATE used to update the rows in the table. |
UPDATE placeofinterest SET name = 'Niagara Falls' WHERE name = "Niagara Waterfalls"; |
| DELETE |
DELETE FROM table_name WHERE [condition]; |
DELETE statement is used to remove rows from the table which are specified in the WHERE condition. |
DELETE FROM placeofinterest WHERE city IN ('Rome','Vienna'); |
| CREATE TABLE |
CREATE TABLE table_name (col1 datatype optional keyword, col2 datatype optional keyword,col3 datatype optional keyword,..., coln datatype optional keyword) |
CREATE TABLE statement is to create the table. Each column in the table is specified with its name, data type and an optional keyword which could be PRIMARY KEY, NOT NULL, etc., |
CREATE TABLE employee ( employee_id char(2) PRIMARY KEY, first_name varchar(30) NOT NULL, mobile int); |
| ALTER TABLE ADD COLUMN |
ALTER TABLE table_name ADD COLUMN column_name_1 datatype....ADD COLUMN column_name_n datatype; |
ALTER TABLE statement is used to add the columns to a table. |
ALTER TABLE employee ADD COLUMN income bigint; |
| ALTER TABLE ALTER COLUMN |
ALTER TABLE table_name ALTER COLUMN column_name_1 SET DATA TYPE datatype; |
ALTER TABLE ALTER COLUMN statement is used to modify the data type of columns. |
ALTER TABLE employee ALTER COLUMN mobile SET DATA TYPE CHAR(20); |
| ALTER TABLE DROP COLUMN |
ALTER TABLE table_name DROP COLUMN column_name_1 ; |
ALTER TABLE DROP COLUMN statement is used to remove columns from a table. |
ALTER TABLE employee DROP COLUMN mobile ; |
| ALTER TABLE - RENAME COLUMN |
ALTER TABLE table_name RENAME COLUMN current_column_name TO new_column_name; |
ALTER TABLE RENAME COLUMN statement is used to rename the columns in a table. |
ALTER TABLE employee RENAME COLUMN first_name TO name ; |
| TRUNCATE TABLE |
TRUNCATE TABLE table_name IMMEDIATE; |
TRUNCATE statement is used to delete all of the rows in a table. The IMMEDIATE specifies to process the statement immediately and that it cannot be undone. |
TRUNCATE TABLE employee IMMEDIATE ; |
| DROP TABLE |
DROP TABLE table_name ; |
Use the DROP TABLE statement to delete a table from a database. If you delete a table that contains data, by default the data will be deleted alongside the table. |
DROP TABLE employee; |
| LIKE |
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; |
LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator which are percent sign(%) and underscore sign (_). |
SELECT f_name , l_name FROM employees WHERE address LIKE '%Elgin,IL%'; |
| BETWEEN |
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; |
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. |
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000; |
| ORDER BY |
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; |
Order BY keyword is used to sort the result-set in ascending or descending order. The default is ascending. |
SELECT f_name, l_name, dep_id FROM employees ORDER BY dep_id DESC, l_name; |
| GROUP BY |
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); |
GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. |
SELECT dep_id, COUNT(*) FROM employees GROUP BY dep_id; |
| COUNT |
SELECT COUNT(column_name) FROM table_name WHERE condition; |
COUNT function returns the number of rows that matches a specified criterion. |
SELECT COUNT(dep_id) FROM employees; |
| AVG |
SELECT AVG(column_name) FROM table_name WHERE condition; |
AVG function returns the average value of a numeric column. |
SELECT AVG(column_name) FROM table_name WHERE condition; |
| SUM |
SELECT SUM(column_name) FROM table_name WHERE condition; |
SUM function returns the total sum of a numeric column. |
SELECT SUM(salary) FROM employees; |
| MIN |
MIN SELECT MIN(column_name) FROM table_name WHERE condition; |
MIN function returns the smallest value of the SELECTed column. |
SELECT MIN(salary) FROM employees; |
| MAX |
SELECT MAX(column_name) FROM table_name WHERE condition; |
MAX function returns the largest value of the SELECTed column. |
SELECT MAX(salary) FROM employees; |
| ROUND |
SELECT ROUND(2number, decimals, operation) AS RoundValue; |
ROUND function rounds a number to a specified number of decimal places. |
SELECT ROUND(salary) FROM employees; |
| LENGTH |
SELECT LENGTH(column_name) FROM table; |
LENGTH function returns the length of a string (in bytes). |
SELECT LENGTH(f_name) FROM employees; |
| UCACE |
SELECT UCASE(column_name) FROM table; |
UCASE function that displays the column name in each table in uppercase. |
SELECT UCASE(f_name) FROM employees; |
| DISTINCT |
SELECT DISTINCT(column_name) FROM table; |
DISTINCT function is used to display data without duplicates. |
SELECT DISTINCT(UCASE(f_name)) FROM employees; |
| DAY |
SELECT DAY(column_name) FROM table |
DAY function returns the day of the month for a given date |
SELECT DAY(b_date) FROM employees where emp_id = 'E1002'; |
| CURRENT DATE |
SELECT (CURRENT DATE - COLUMN) FROM table; |
CURRENT DATE is used to display the current date.This can be subtracted from the previous date to get the difference. |
SELECT YEAR(CURRENT DATE - b_date) As AGE, CURRENT_DATE, b_date FROM employees; |
| ｜Subquery |
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) |
Subquery is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. |
SELECT emp_id, fmame, lname, salary FROM employees where salary < (SELECT AVG(salary) FROM employees); SELECT * FROM ( SELECT emp_id, f_name, l_name, dep_id FROM employees) AS emp4all; SELECT * FROM employees WHERE job_id IN (SELECT job_ident FROM jobs); |
| Implicit Inner Join |
SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name; |
Implicit Inner Join combines the two or more records but displays only matching values in both tables. Inner join applies only the specified columns. |
SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident; |
| Implicit Cross Join |
SELECT column_name(s) FROM table1, table2; |
Implicit Cross Join defines as a Cartesian product where the number of rows in the first table multiplied by the number of rows in the second table. |
SELECT * FROM employees, jobs; |