The AS keyword is used to assign an alias to a column or a table.

Syntax: Column Alias

SELECT column_name AS alias_name
FROM table_name;

Syntax: Table Alias

SELECT column_name
FROM table_name AS alias_name;

DISTINCT is used remove duplicate rows from the result set of a query, returning only unique values.

It can be applied to one or more columns in a query to ensure that the combination of these columns’ values is unique in the returned results.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

WHERE is used to filter records that fulfil a specified condition.

Syntax:

SELECT * FROM Customers
WHERE [conditions]

BETWEEN selects value in a given range where the values stated are inclusive.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example: Return patient’s first and last name that has weight between 100 and 120.

SELECT first_name, last_name
FROM patients
WHERE weight BETWEEN 100 AND 120;

Sort the result in ascending (default) or descending (DESC) order.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC]

RAND is used to generate a random floating-point number between 0 (inclusive) and 1 (exclusive).

Example: Generating a Random Number

SELECT RAND(100) AS random_value;

• To generate a single random number between 0 and 1:
• (100) is a seed that makes the random number generation predictable and reproducible

Example: Select Random Rows

SELECT *
FROM customers
ORDER BY RAND();

• The ORDER BY RAND() clause sorts the rows in random order.

Used to specify the maximum number of rows that a query should return.

Syntax:

SELECT column_name(s)
FROM table_name
LIMIT number_of_rows

Example:

SELECT * 
FROM employees 
LIMIT 10;

• This query returns the first 10 rows from the employees table.

It is used in SQL to skip a certain number of rows before returning the remaining results. It’s often used in conjunction with the LIMIT.

Syntax:

SELECT column_name(s)
FROM table_name
LIMIT number_of_rows [OFFSET start_position];

Example:

SELECT *
FROM your_table
LIMIT 10 OFFSET 5;

• The LIMIT clause specifies the maximum number of rows to return, which is 10 rows (or fewer)
• The OFFSET clause specifies the number of rows to skip from the top, which is 5.

Perform pattern matching against string data with wildcard characters. LIKE is case sensitive, ILIKE is case insensitive.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE columnn (I)LIKE pattern:

• Purpose: The AND operator is used to combine multiple conditions in such a way that all conditions must be true for the row to be included in the result set.
• Behavior: If all conditions connected by AND are true, the overall expression evaluates to true; otherwise, it evaluates to false.

Example

SELECT * 
FROM employees 
WHERE department = 'Sales' 
AND salary > 50000;

• This query selects rows where both conditions are true: the department is ‘Sales’ and the salary is greater than 50,000.

• Purpose: The OR operator is used to combine multiple conditions in such a way that if any one of the conditions is true, the row will be included in the result set.
• Behavior: If at least one condition connected by OR is true, the overall expression evaluates to true; if all conditions are false, it evaluates to false.

Example

SELECT * 
FROM employees 
WHERE department = 'Sales' 
OR department = 'HR';

• This query selects rows where either condition is true: the department is ‘Sales’ or the department is ‘HR’.

• Purpose: The NOT operator is used to negate a condition, meaning it inverts the result of the condition.
• Behavior: If the condition is true, NOT makes it false; if the condition is false, NOT makes it true.

Example

SELECT * 
FROM employees 
WHERE NOT department = 'HR';

• This query selects rows where the department is not ‘HR’.

The IN operator is used to simplify complex queries that need to check whether a value in a column matches any value in a given list.

Example:

SELECT customer_id, first_name, city
FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

• This query returns all rows where the city is either “New York,” “Los Angeles,” or “Chicago.”

Filter records that contains null values.

SELECT * FROM _tabble
WHERE col IS (NOT) NULL

Aggregate Functions definitions:

• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column

GROUP BY is a SQL clause used to group rows based on one or more columns. It’s often used in conjunction with aggregate functions like SUM, AVG, COUNT, MIN, and MAX to summarise data.

Example:

SELECT department, SUM(amount) AS total_sales
FROM sales
GROUP BY department;

• The query returns the total sales amount per department.

HAVING allow us to filter the groups based on aggregate values after an aggregation has already taken place.

Example:

SELECT department, SUM(amount) AS total_sales
FROM sales
GROUP BY department
HAVING SUM(amount) > 1000;

• The query returns departments where the total sales amount exceeds $1000.

• HAVING filters those groups based on conditions applied to the aggregated data.
• WHERE filters individual rows before they are grouped.