SQL Filtering and Sorting with Real Life Examples

This article will provide an in-depth explanation of WHERE , HAVING , ORDER BY , GROUP BY and other clauses and related operators in SQL through employee and department table cases, helping you master SQL data filtering and sorting skills.

Table of contents

1. Table structure
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. ORDER BY clause
6. LIMIT clause
7. DISTINCT clause
8. AND , OR , NOT operators

Table structure

Employee List

emp_id	name	age	department_id	hire_date	Salary
1	john smith	35	101	2020-01-01	5000
2	jane doe	28	102	2019-03-15	6000
3	alice johnson	40	103	2018-06-20	7000
4	bob brown	55	null	2015-11-10	8000
5	charlie black	30	102	2021-02-01	5500

Department list

dept_id	Dept_name
101	hr
102	it
103	finance
104	Marketing

WHERE clause

The WHERE clause is used to filter records that meet certain criteria.

SQL Query:

 <code class="language-sql">SELECT name, age, salary FROM employees WHERE age > 30;</code>

result:

name	age	Salary
john smith	35	5000
alice johnson	40	7000
bob brown	55	8000

Description: Filter out information of employees older than 30 years old.

Example of AND operator:

 <code class="language-sql">SELECT name, age, salary FROM employees WHERE age > 30 AND salary > 5000;</code>

result:

name	age	Salary
alice johnson	40	7000
bob brown	55	8000

Description: Filter information of employees older than 30 years old and salary greater than 5,000.

GROUP BY clause

GROUP BY clause is used to group records with the same value and is often used in statistical aggregation operations.

SQL Query:

 <code class="language-sql">SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;</code>

result:

department_id	Employee_count
101	1
102	2
103	1

Description: Grouped by department ID and count the number of employees in each department.

HAVING clause

The HAVING clause is used to filter the results after GROUP BY grouping.

SQL Query:

 <code class="language-sql">SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 5500;</code>

result:

department_id	avg_salary
102	5750
103	7000

Description: Filter out departments with an average salary of more than 5,500.

ORDER BY clause

ORDER BY clause is used to sort the result set.

SQL query (ascending order):

 <code class="language-sql">SELECT name, salary FROM employees ORDER BY salary;</code>

result:

name	Salary
john smith	5000
charlie black	5500
jane doe	6000
alice johnson	7000
bob brown	8000

SQL query (descending order):

 <code class="language-sql">SELECT name, salary FROM employees ORDER BY salary DESC;</code>

result:

name	Salary
bob brown	8000
alice johnson	7000
jane doe	6000
charlie black	5500
john smith	5000

LIMIT clause

The LIMIT clause is used to limit the number of records returned.

SQL Query:

 <code class="language-sql">SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;</code>

result:

name	Salary
bob brown	8000
alice johnson	7000
jane doe	6000

Description: Only the information of the top 3 employees is returned.

DISTINCT clause

The DISTINCT clause is used to remove duplicate values.

SQL Query:

 <code class="language-sql">SELECT DISTINCT department_id FROM employees;</code>

result:

department_id

101

102

103

Description: Returns the unique department ID.

AND , OR , NOT operators

These operators are used to combine multiple conditions.

(Omit repeated And, Or, Not examples, keep the space simple)

in conclusion

Through actual cases, this article explains in detail the key clauses and operators used in SQL for data filtering and sorting. Proficient in this knowledge will effectively improve your SQL query efficiency and data analysis capabilities.

The above is the detailed content of SQL Filtering and Sorting with Real Life Examples. For more information, please follow other related articles on the PHP Chinese website!