Advanced SQL

1. WHERE Clause

The WHERE clause is used to filter the rows that are returned by a SELECT statement. The syntax for the WHERE clause is as follows:

WHERE condition;

The condition is a Boolean expression that can be used to filter the rows. For example, the following WHERE clause will only return rows where the name column is equal to John Doe:

WHERE name = ‘John Doe’;

You can use a variety of operators in the WHERE clause, such as:

• = for equal to
• < for less than
  • for greater than
• <= for less than or equal to
• >= for greater than or equal to
• LIKE for a pattern match
• IN for a list of values
• BETWEEN for a range of values

2. GROUP BY Clause

The GROUP BY clause is used to group rows together based on a common value. The syntax for the GROUP BY clause is as follows:

GROUP BY column_name;

The column_name specifies the column that you want to group the rows by. You can group by multiple columns by separating them with commas. For example, the following GROUP BY clause will group the rows together by the country column:

GROUP BY country;

The GROUP BY clause can be used with aggregate functions, such as:

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()

These functions will return a single value for each group. For example, the following SELECT statement will return the number of customers in each country:

SELECT country, COUNT(*) AS number_of_customers

FROM customers

GROUP BY country;

3. HAVING Clause

The HAVING clause is used to filter the groups that are returned by a GROUP BY clause. The syntax for the HAVING clause is as follows:

HAVING condition;

The condition is a Boolean expression that can be used to filter the groups. For example, the following HAVING clause will only return groups where the number of customers is greater than 10:

HAVING COUNT(*) > 10;

4. ORDER BY Clause

The ORDER BY clause is used to sort the rows that are returned by a SELECT statement. The syntax for the ORDER BY clause is as follows:

ORDER BY column_name;

The column_name specifies the column that you want to sort the rows by. You can sort the rows in ascending or descending order by adding ASC or DESC after the column name. For example, the following ORDER BY clause will sort the rows in ascending order by the name column:

ORDER BY name ASC;

You can also sort by multiple columns by separating them with commas. For example, the following ORDER BY clause will sort the rows in ascending order by the name column, and then by the country column:

ORDER BY name ASC, country ASC;