{"id":107432,"date":"2025-05-14T15:58:00","date_gmt":"2025-05-14T10:28:00","guid":{"rendered":"https:\/\/www.mygreatlearning.com\/blog\/sql-queries-with-examples\/"},"modified":"2025-10-17T17:00:34","modified_gmt":"2025-10-17T11:30:34","slug":"sql-queries-with-examples","status":"publish","type":"post","link":"https:\/\/www.mygreatlearning.com\/blog\/sql-queries-with-examples\/","title":{"rendered":"27 SQL Query Examples to Practice Data Analysis"},"content":{"rendered":"\n

This guide provides 27 practical SQL query examples to help you master data analysis. We will start from basic data retrieval with SELECT<\/strong> to advanced analytical techniques using window functions<\/strong> and CTEs<\/strong>.<\/p>\n\n\n\n

This article focuses specifically on querying<\/strong> - the method of asking questions to get answers from your data. If you need a foundational overview of all SQL command types (like DDL for creating tables or DCL for permissions) first, see our complete guide to SQL Commands<\/a>.<\/p>\n\n\n\n

We'll use two simple tables for most examples: employees<\/strong> and departments<\/strong>.<\/p>\n\n\n\n

employees table:<\/strong><\/p>\n\n\n\n

employee_id<\/th>first_name<\/th>last_name<\/th>salary<\/th>department_id<\/th>hire_date<\/th><\/tr><\/thead>
1<\/td>John<\/td>Smith<\/td>60000<\/td>1<\/td>2022-01-15<\/td><\/tr>
2<\/td>Jane<\/td>Doe<\/td>75000<\/td>1<\/td>2021-03-20<\/td><\/tr>
3<\/td>Peter<\/td>Jones<\/td>90000<\/td>2<\/td>2020-05-10<\/td><\/tr>
4<\/td>Mary<\/td>Williams<\/td>80000<\/td>2<\/td>2021-08-01<\/td><\/tr>
5<\/td>David<\/td>Brown<\/td>65000<\/td>3<\/td>2023-01-05<\/td><\/tr>
6<\/td>null<\/td>Davis<\/td>55000<\/td>3<\/td>2023-02-12<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

departments table:<\/strong><\/p>\n\n\n\n

department_id<\/th>department_name<\/th><\/tr><\/thead>
1<\/td>Engineering<\/td><\/tr>
2<\/td>Sales<\/td><\/tr>
3<\/td>Marketing<\/td><\/tr>
4<\/td>HR<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n
\n
\n
<\/div>\n Academy Pro<\/span>\n <\/div>\n

\n SQL Course<\/a>\n <\/p>\n

Master SQL and Database management with this SQL course: Practical training with guided projects, AI support, and expert instructors.<\/p>\n

\n
\n
<\/div>\n 7 Hrs<\/span>\n <\/div>\n
\n
<\/div>\n 2 Projects<\/span>\n <\/div>\n <\/div>\n \n Take SQL Course Now\n
<\/div>\n <\/a>\n <\/div>\n <\/div>\n\n\n\n

The Basics: Data Retrieval<\/h2>\n\n\n\n

These are the basic queries you'll use every single day. Don't just learn them, know them.<\/p>\n\n\n\n

1. SELECT *<\/h3>\n\n\n\n

What it does:<\/strong> Selects all columns from a table.<\/p>\n\n\n\n

Example:<\/strong><\/p>\n\n\n

\nSELECT * FROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> This query returns every single row and column from the employees<\/code> table. Use it to get a quick look at your data.<\/p>\n\n\n\n
2. SELECT [specific columns]<\/h3>\n\n\n\n
What it does:<\/strong> Selects only the columns you specify. More efficient than SELECT *<\/code>.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT first_name, last_name, salary FROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Returns only the first_name<\/code>, last_name<\/code>, and salary<\/code> for all employees.<\/p>\n\n\n\n
3. WHERE<\/h3>\n\n\n\n
What it does:<\/strong> Filters records based on a condition.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT * FROM employees WHERE salary > 70000;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> This shows all information for employees earning more than 70,000.<\/p>\n\n\n\n
4. AND \/ OR<\/h3>\n\n\n\n
What it does:<\/strong> Combines multiple conditions in a WHERE<\/code> clause.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT * FROM employees WHERE salary > 70000 AND department_id = 2;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Retrieves employees who are in department 2 AND<\/strong> have a salary over 70,000.<\/p>\n\n\n\n
5. ORDER BY<\/h3>\n\n\n\n
What it does:<\/strong> Sorts the results. The default is ascending (ASC<\/code>).<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT first_name, salary FROM employees ORDER BY salary DESC;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Displays employee first names and salaries, sorted from highest salary to lowest.<\/p>\n\n\n\n
6. LIMIT<\/h3>\n\n\n\n
What it does:<\/strong> Restricts the number of rows returned.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT * FROM employees ORDER BY hire_date DESC LIMIT 3;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Gets the top 3 most recently hired employees.<\/p>\n\n\n\n
7. DISTINCT<\/h3>\n\n\n\n
What it does:<\/strong> Returns only unique values.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT DISTINCT department_id FROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Shows a list of unique department_ids<\/code> that have employees, which would be 1, 2, and 3.<\/p>\n\n\n\n
8. LIKE<\/h3>\n\n\n\n
What it does:<\/strong> Used in a WHERE<\/code> clause to search for a specific pattern in a column.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT first_name, last_name FROM employees WHERE last_name LIKE 'J%';\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Finds all employees whose last name starts with 'J'. The %<\/code> is a wildcard.<\/p>\n\n\n\n
9. IS NULL \/ IS NOT NULL<\/h3>\n\n\n\n
What it does:<\/strong> Checks for empty values.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT * FROM employees WHERE first_name IS NULL;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Retrieves rows where the first_name<\/code> has not been entered, like employee_id 6.<\/p>\n\n\n\n
Medium Level Queries: Joins and Aggregations<\/h2>\n\n\n\n
This is where you start combining and analyzing data. Master these, and you're hireable.<\/p>\n\n\n\n
10. INNER JOIN<\/h3>\n\n\n\n
What it does:<\/strong> Combines rows from two or more tables based on a related column. Returns only matching rows.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT e.first_name, e.last_name, d.department_name\nFROM employees e\nINNER JOIN departments d ON e.department_id = d.department_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> This query lists each employee and their corresponding department name. The HR department won't show up because no employee is assigned to it.<\/p>\n\n\n\n
11. LEFT JOIN<\/h3>\n\n\n\n
What it does:<\/strong> Returns all records from the left table (employees<\/code>), and the matched records from the right table (departments<\/code>).<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT e.first_name, d.department_name\nFROM employees e\nLEFT JOIN departments d ON e.department_id = d.department_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Shows all employees and their department. If an employee had a department_id<\/code> that didn't exist in departments<\/code>, their department_name<\/code> would be NULL<\/code>.<\/p>\n\n\n\n
12. RIGHT JOIN<\/h3>\n\n\n\n
What it does:<\/strong> Returns all records from the right table (departments<\/code>), and the matched records from the left.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT e.first_name, d.department_name\nFROM employees e\nRIGHT JOIN departments d ON e.department_id = d.department_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> This will list all departments and the employees in them. The HR department will appear with a NULL first_name<\/code> because it has no employees.<\/p>\n\n\n\n
Also Read: SQL Joins - Inner, Left, Right & Full Join<\/a><\/p>\n\n\n\n
13. COUNT()<\/h3>\n\n\n\n
What it does:<\/strong> An aggregate function that counts the number of rows.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT COUNT(*) FROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Returns the total number of employees, which is 6.<\/p>\n\n\n\n
14. SUM()<\/h3>\n\n\n\n
What it does:<\/strong> Calculates the total sum of a numeric column.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT SUM(salary) FROM employees WHERE department_id = 1;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Calculates the total salary for all employees in the Engineering department (60000 + 75000).<\/p>\n\n\n\n
15. AVG()<\/h3>\n\n\n\n
What it does:<\/strong> Calculates the average value of a numeric column.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT AVG(salary) FROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Gives the average salary of all employees.<\/p>\n\n\n\n
16. GROUP BY<\/h3>\n\n\n\n
What it does:<\/strong> Groups rows that have the same values into summary rows. Often used with aggregate functions.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT department_id, COUNT(employee_id) as num_employees\nFROM employees\nGROUP BY department_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Shows how many employees are in each department.<\/p>\n\n\n\n
17. HAVING<\/h3>\n\n\n\n
What it does:<\/strong> Filters groups based on a condition after GROUP BY<\/code>. WHERE<\/code> filters rows before grouping.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT d.department_name, AVG(e.salary) as avg_salary\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id\nGROUP BY d.department_name\nHAVING AVG(e.salary) > 70000;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> This query shows departments where the average salary is greater than 70,000.<\/p>\n\n\n\n
18. CASE<\/h3>\n\n\n\n
What it does:<\/strong> Adds if-then-else logic to your queries.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT first_name, salary,\n    CASE\n        WHEN salary > 85000 THEN 'High Earner'\n        WHEN salary > 70000 THEN 'Mid Earner'\n        ELSE 'Standard Earner'\n    END as salary_bracket\nFROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Creates a new column salary_bracket<\/code> and categorizes each employee based on their salary.<\/p>\n\n\n\n
19. Subquery (or Inner Query)<\/h3>\n\n\n\n
What it does:<\/strong> A query nested inside another query.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT first_name, last_name\nFROM employees\nWHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Gets the names of all employees who work in the 'Sales' department. The inner query finds the department_id<\/code> for Sales first.<\/p>\n\n\n\n
Advanced SQL Queries: Window Functions and CTEs<\/h2>\n\n\n\n
If you can use these fluently, you're not a junior anymore. These are for complex analysis.<\/p>\n\n\n\n
20. Common Table Expression (CTE)<\/h3>\n\n\n\n
What it does:<\/strong> Creates a temporary, named result set that you can reference within your main query.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nWITH HighSalaries AS (\n    SELECT employee_id, first_name, salary\n    FROM employees\n    WHERE salary > 75000\n)\nSELECT * FROM HighSalaries;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> The WITH<\/code> clause defines a temporary table HighSalaries<\/code>, which is then queried. It's cleaner than a subquery for complex logic.<\/p>\n\n\n\n
21. ROW_NUMBER()<\/h3>\n\n\n\n
What it does:<\/strong> A window function that assigns a unique number to each row within a partition.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT\n    first_name,\n    department_name,\n    salary,\n    ROW_NUMBER() OVER(PARTITION BY department_name ORDER BY salary DESC) as rank_in_dept\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> This ranks employees within each department based on salary. The highest salary in each department gets rank 1.<\/p>\n\n\n\n
22. RANK() & DENSE_RANK()<\/h3>\n\n\n\n
What it does:<\/strong> Ranks rows within a partition. RANK()<\/code> will skip ranks after a tie (e.g., 1, 2, 2, 4), while DENSE_RANK()<\/code> will not (e.g., 1, 2, 2, 3).<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT\n    first_name,\n    salary,\n    RANK() OVER(ORDER BY salary DESC) as salary_rank\nFROM employees;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Assigns a rank to each employee based on their overall salary.<\/p>\n\n\n\n
23. LAG() & LEAD()<\/h3>\n\n\n\n
What it does:<\/strong> Accesses data from a previous row (LAG<\/code>) or a subsequent row (LEAD<\/code>) without a self-join.<\/p>\n\n\n\n
Example:<\/strong> (Assume a table sales<\/code> with sale_date<\/code> and sale_amount<\/code>)<\/p>\n\n\n
\nSELECT\n    sale_date,\n    sale_amount,\n    LAG(sale_amount, 1, 0) OVER(ORDER BY sale_date) as previous_day_sales\nFROM sales;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Shows each day's sales next to the previous day's sales, making day-over-day comparisons easy.<\/p>\n\n\n\n
24. Moving Average<\/h3>\n\n\n\n
What it does:<\/strong> Calculates averages over a specific window of rows.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT\n    sale_date,\n    sale_amount,\n    AVG(sale_amount) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_day_moving_avg\nFROM sales;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> For each day, it calculates the average sales of that day and the two preceding days.<\/p>\n\n\n\n
25. PIVOT (Conditional Aggregation)<\/h3>\n\n\n\n
What it does:<\/strong> Rotates a table by turning unique values from one column into multiple columns.<\/p>\n\n\n\n
Example:<\/strong> (using CASE<\/code> for general compatibility)<\/p>\n\n\n
\nSELECT\n    department_id,\n    COUNT(CASE WHEN salary > 70000 THEN 1 END) as high_earners,\n    COUNT(CASE WHEN salary &lt;= 70000 THEN 1 END) as standard_earners\nFROM employees\nGROUP BY department_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Creates a report showing the count of high vs. standard earners for each department.<\/p>\n\n\n\n
26. Self Join<\/h3>\n\n\n\n
What it does:<\/strong> Joining a table to itself to compare rows within the same table.<\/p>\n\n\n\n
Example:<\/strong> Find employees who were hired on the same day.<\/p>\n\n\n
\nSELECT e1.first_name, e1.last_name, e2.first_name, e2.last_name, e1.hire_date\nFROM employees e1\nJOIN employees e2 ON e1.hire_date = e2.hire_date AND e1.employee_id > e2.employee_id;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Compares every employee record (e1<\/code>) with every other employee record (e2<\/code>) to find matches on hire_date<\/code>. e1.employee_id > e2.employee_id<\/code> prevents duplicates and self-matching.<\/p>\n\n\n\n
27. UNION \/ UNION ALL<\/h3>\n\n\n\n
What it does:<\/strong> Combines the result set of two or more SELECT<\/code> statements. UNION<\/code> removes duplicates, UNION ALL<\/code> includes all records.<\/p>\n\n\n\n
Example:<\/strong><\/p>\n\n\n
\nSELECT first_name, last_name FROM employees WHERE department_id = 1\nUNION\nSELECT first_name, last_name FROM employees WHERE department_id = 2;\n<\/pre><\/div>\n\n\n
Explanation:<\/strong> Creates a single list of all employees from departments 1 and 2.<\/p>\n\n\n\n
Best Practices for Writing Effective Queries<\/h2>\n\n\n\n