1) SELECT – Select statements are used to fetch data from a database. Every query will begin with SELECT.
Syntax
SELECT column_name
FROM table_name;
2) LIMIT – Limiting the number of rows returned from a table is a useful trick for speeding query time. To limit the number of rows, you can use the LIMIT command.
Syntax
SELECT column_name(s)
FROM table_name
LIMIT number;
3) AS – AS is a keyword in SQL that allows you to rename a column or table using an alias.
Syntax
SELECT column_name AS ‘Alias’
FROM table_name;
4) SELECT DISTINCT – Select distinct specifies that the statement is going to be a query that returns unique values in the specified column(s).
Syntax
SELECT DISTINCT column_name
FROM table_name;
5) WHERE – The WHERE clause filters rows that match a certain condition.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value;
6) HAVING – Having clause is similar to the WHERE clause, but it can only be used with aggregate functions while WHERE canโt.
Syntax
SELECT column_name, COUNT() FROM table_name GROUP BY column_name HAVING COUNT() > value;
7) COUNT – Count() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL.
Syntax
SELECT COUNT(column_name)
FROM table_name;
8) AND – And operator is used when filtering rows that match more than one condition.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
AND column_2 = value_2;
9) OR – OR is another conditional operator that allows you to subset rows if any of the conditions separated by OR are true.
Syntax
SELECT column_name
FROM table_name
WHERE column_name = value_1
OR column_name = value_2;
10) LIKE – Like is a special operator used with the WHERE clause to search for a specific pattern in a column.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
11) BETWEEN – Between allows you to subset rows within a certain range, which makes WHERE clauses simpler and easier to read.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;
12) IN – The IN command allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, โฆ);
13) MAX – Max() returns the maximum value in a numeric column. For text columns, MAX() returns the last value alphabetically.
Syntax
SELECT MAX(column_name)
FROM table_name;
14) AVERAGE – Avg() is an aggregate function that returns the average value for a numeric column.
Syntax
SELECT AVG(column_name)
FROM table_name;
15) AND – AND operator is used when filtering rows that match more than one condition.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
AND column_2 = value_2;
16) MIN – MIN() returns the minimum value in a numeric column. For text columns, MIN() returns the first value alphabetically.
Syntax
SELECT MIN(column_name)
FROM table_name;
17) MAX – MAX() is a function that takes the name of a column as an argument and returns the largest value in that column.
Syntax
SELECT MAX(column_name)
FROM table_name;
18) WITH – WITH clause lets you store the result of a query in a temporary table using an alias. You can also define multiple temporary tables using a comma and with one instance of the WITH keyword.
Syntax
WITH temporary_name AS (
SELECT *
FROM table_name)
SELECT *
FROM temporary_name
WHERE column_name operator value;
19) ALTER – Alter table lets you add columns to a table in a database.
Syntax
ALTER TABLE TableName
ADD ColumnName Datatype;
ALTER TABLE TableName
DROP COLUMN ColumnName;
20) DROP TABLE – Drop Table is used to delete both the structure and record in the table.
Syntax
DROP TABLE TableName;
21) TRUNCATE – This truncate command is used to delete all the rows from the table and free the space.
Syntax
TRUNCATE TABLE TableName;
22) AND – AND combines two or more conditions in a single query. All of the conditions must be met for the result to be returned.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
AND column_2 = value_2;
23) IS NULL – Is Null will return only rows with a NULL value.
Syntax
SELECT name
FROM table name
WHERE name IS NULL;
24) IS NOT NULL – Is Not Null does the opposite โ it will return only rows without a NULL value.
Syntax
SELECT name
FROM table name
WHERE name IS NOT NULL;
25) DELETE – Delete statements are used to remove rows from a table.
Syntax
DELETE FROM table_name
WHERE some_column = some_value;
26) INSERT – Insert statements are used to add a new row to a table.
Syntax
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, ‘value_2’, value_3);
27) OFFSET – The Offset statement works with ORDER BY and specifies the number of rows to skip before starting to return rows from the query.
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name
OFFSET rows_to_skip ROWS;
28) EXISTS – Exist is used to test for the existence of any record in a subquery.
Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
29) GROUP BY – Group by is a clause in SQL that is only used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.
Syntax
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
30) ORDER BY – Order by is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically.
Syntax
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;