SQL Clause
- SQL clauses are the basics of SQL queries that determine the operations to be executed on the database.
- Every clause performs a unique task and, in combination, enables you to find, filter, order, change, or group the data in different modes.
- So, let’s go through some of the SQL clauses that are most frequently used together with the examples.
1. WHERE Clause
- The WHERE clause limits records to those that satisfy a particular condition.
- It only includes rows that fulfill the condition in the result set.
- Syntax:
SELECT column1, column2
FROM table_name
WHERE condition;
- Example:
SELECT * FROM employees WHERE age >30;
- This query delivers all ID and Title columns from the employees table where the age at entry is more than 30.
Logical Operators in WHERE Clause:
- As such, one can make use of logical operators like AND, OR, and NOT to put various conditions together.
- Example:
SELECT * FROM employees WHERE age >30AND department ='Sales';
- This will filter the employees in the “Sales” department who are over 30 years of age.
2. ORDER BY Clause
- The ORDER BY clause is a technical detail, used to sort the result set which may be displayed in a certain order.
- By default, it really is an ascending order.
- But you can also ask for the descending order if the command is followed by DESC.
- Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC|DESC];
- Example:
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;
- This brings back the first_name and last_name of employees, and folders will be arranged by last_name in the ascending order.
Multiple Sorting:
- It means you can put columns in a different order and check how the search calls it.
SELECT first_name, last_name, age FROM employees ORDER BY department ASC, age DESC;
- Here, the employees are first categorized by a department in ascending order, and within each department, they are then sorted by age in descending order.
3. GROUP BY Clause
- GROUP BY is an important clause that allows you to group data rows if they have the same value in a specified column.
- GROUP BY together with the aggregate functions such as “count”, “sum”, “avg” is commonly used for data summarization.
- Syntax:
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;
- Example:
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;
- This is the set of divisions of employees according to their department with the number of employees in each department counted.
Notes:
- GROUP BY comes after the WHERE clause and before the ORDER BY clause.
- You can group columns:
SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;
4. HAVING Clause
- The HAVING clause is put in place to filter the records after the GROUP BY procedure. It has an operation similar to the WHERE clause, but it must be used with the groups of data.
- Syntax:
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING condition;
- Example:
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
- The department employees are grouped and the only ones that belong to the departments with more than 5 people are included.
Notes:
- The HAVING clause is used when you want to filter on the result of an aggregate function.
5. DISTINCT Clause
- The DISTINCT clause is employed to delete replicate rows from the result set.
- Syntax:
SELECT DISTINCT column1, column2
FROM table_name;
- Example:
SELECT DISTINCT department FROM employees;
- As such, this query returns unique department names from the employees table.
6. LIMIT Clause
- The LIMIT clause is used to tell the SQL to respond with just a certain number of rows. It is always best to use it when controlling sets of results with large tables.
- Syntax:
SELECT column1, column2
FROM table_name
LIMIT number_of_rows;
- Example:
SELECT * FROM employees LIMIT 10;
- This query retrieves the first 10 rows from the employees table.