- What is an SQL Query?
- Data Retrieval Queries (SELECT-Based)
- Data Summarizing (Aggregation) Queries
- Combining Data from Different Tables (Join Queries)
- More Complex Queries (Advanced Analysis)
- Data Modification Queries
- Data Definition and Optimization Queries
- Queries for Handling Special Data and Cases
- How to Write Good Queries (Best Practices)
- Conclusion
SQL is a standard programming language used to manage and manipulate relational databases. It allows users to access, retrieve, modify, and analyze data stored in a structured manner, making it an essential tool for data management and analysis.
In this tutorial, we will learn about different types of SQL queries. You will see over 40 examples. They are grouped so you can understand what each type of query does.
You can use this Online SQL Compiler Tool to easily test queries.
What is an SQL Query?
An SQL query is a request you send to a database asking it to retrieve or manipulate data.
You use keywords like SELECT (to get data), FROM (to say which table to get data from), WHERE (to filter data), JOIN (to combine data from different tables), and others to create a query.
Why are queries important? Because they let you get exactly the data you need, change data, or look at data to find insights.
Here is a basic example:
SELECT first_name, last_name
FROM employees
WHERE department = 'Marketing';
This query asks the database to:
SELECTthefirst_nameandlast_name.FROMthe table namedemployees.WHEREthedepartmentcolumn has the value ‘Marketing’.
It means: “Show me the first and last names of all employees who work in the ‘Marketing’ department.”
Master Data Analytics in SQL!
-Learn database basics, write powerful queries, and solve real-world problems with 39 coding exercises and 2 projects.
-Perfect for anyone who wants hands-on SQL skills fast.
👉 Start the course now
Data Retrieval Queries (SELECT-Based)
These queries help you pull data out of your tables.
1. Basic SELECT
To get specific columns.
SELECT product_name, price
FROM products;
This asks for the names and prices of all products.
2. WHERE Clause
To filter data based on a rule.
SELECT *
FROM customers
WHERE age > 25;
This asks for all columns (*) for customers who are older than 25.
3. DISTINCT
Shows only unique values, removing repeats.
SELECT DISTINCT country
FROM customers;
This asks for a list of all the different countries your customers are in. Each country will appear only once.
4. ORDER BY
Sort the results.
SELECT product_name, price
FROM products
ORDER BY price ASC;
This asks for product names and prices, sorted from the lowest price (ASC means ascending) to the highest. You could use DESC for descending (highest to lowest).
5. LIMIT / TOP
Show only a certain number of rows.
SELECT *
FROM orders
LIMIT 10;
(Note: LIMIT is common in MySQL, PostgreSQL, etc. SQL Server uses TOP).
This asks for the first 10 rows from the orders table. SQL Server Example: SELECT TOP 10 * FROM orders;
6. LIKE
Find data that matches a pattern.
SELECT first_name
FROM customers
WHERE first_name LIKE 'S%';
This asks for the first names of customers where the name starts with the letter ‘S’. The % is a wildcard meaning “any characters here”. You could use %s for names ending with ‘S’ or %s% for names with ‘S’ anywhere.
7. IN
Match against a list of values.
SELECT *
FROM products
WHERE category IN ('Books', 'Toys');
This asks for all products where the category is either ‘Books’ or ‘Toys’.
8. BETWEEN
Filter data within a range (for numbers or dates)
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
This asks for all orders placed between the start and end dates of 2024.
9. Top-N Queries
Get the top few records based on a sort.
SELECT TOP 5 product_name, sales
FROM products
ORDER BY sales DESC;
(Using TOP for SQL Server, similar to LIMIT). This asks for the names and sales of the top 5 products, sorted by sales from highest down.
10. Pagination
Get data in smaller chunks (like showing page 3 of results)
SELECT *
FROM orders
ORDER BY order_date
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
(Note: Syntax can vary slightly by database system).
This gets orders sorted by date. It skips the first 20 rows (OFFSET) and then gets the next 10 rows (Workspace NEXT 10 ROWS ONLY). This is useful for showing results 21 through 30.
Data Summarizing (Aggregation) Queries
These queries help you calculate things based on your data.
11. COUNT
Count how many rows match.
SELECT COUNT(*)
FROM employees
WHERE department = 'Finance';
This counts how many employees work in the ‘Finance’ department. COUNT(*) counts all rows that match the WHERE rule.
12. SUM
Add up values in a column.
SELECT SUM(salary)
FROM employees;
This adds up the salaries of all employees to get the total salary amount.
13. AVG
Find the average value in a column.
SELECT AVG(price)
FROM products
WHERE category = 'Electronics';
This calculates the average price of products in the ‘Electronics’ category.
14. MIN / MAX
Find the smallest or largest value.
SELECT MAX(salary), MIN(salary)
FROM employees;
This finds the highest salary and the lowest salary among all employees.
15. GROUP BY
Apply calculations to groups of rows.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
This groups the employees by their department. Then, for each department, it counts how many employees are in that group. Result: a list of departments and the number of employees in each.
16. HAVING
Filter groups based on a condition (used after GROUP BY).
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
This first groups employees by department and calculates the average salary for each. Then, HAVING filters those groups, showing only departments where the average salary is more than 60,000.
17. STRING_AGG
Combine strings from multiple rows into one (syntax can vary).
SELECT department, STRING_AGG(first_name, ', ') AS team
FROM employees
GROUP BY department;
(Note: STRING_AGG is common in SQL Server. PostgreSQL has string_agg, MySQL has GROUP_CONCAT).
This groups employees by department and then joins the first names within each department into a single string, separated by ‘, ‘.
Combining Data from Different Tables (Join Queries)
Joins help you get data that is spread across two or more tables that are related to each other.
18. INNER JOIN
Show rows where there is a match in both tables.
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
This combines employees (given the alias e) and departments (d) tables. It links rows where the department_id in employees matches the id in departments. It will only show employees who have a matching department in the departments table.
19. LEFT JOIN:
Show all rows from the “left” table, and matching rows from the “right” table. If no match on the right, show nulls for right-side columns.
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
This shows all customers (c). If a customer has placed orders (o), it shows their name and the order ID. If a customer has not placed any orders, their name will still appear, but the order_id column will be null.
20. RIGHT JOIN
Show all rows from the “right” table, and matching rows from the “left” table. If no match on the left, show nulls for left-side columns. (Less common, often can be rewritten as a LEFT JOIN).
SELECT o.order_id, c.customer_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;
This shows all customers (c), just like the LEFT JOIN example above. The result is similar but written from the perspective of keeping all rows from the customers table (the right table here).
21. Self JOIN
Join a table to itself to compare rows within the same table.
SELECT e1.first_name AS Employee, e2.first_name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;
This treats the employees table as two separate copies (e1 and e2). It matches an employee (e1) to their manager (e2) using the manager_id column.
22. Cross JOIN
Show every possible combination of rows from two tables.
SELECT p.product_name, c.color
FROM products p
CROSS JOIN colors c;
If you have 10 products and 5 colors, this will list all 50 possible pairs (Product1 with Color1, Product1 with Color2, …, Product10 with Color5).
Also Read: Joins in SQL
More Complex Queries (Advanced Analysis)
These queries help you do more complex data analysis.
23. CASE Statement
Add if/then/else logic inside your query.
SELECT first_name,
CASE
WHEN salary > 70000 THEN 'High'
ELSE 'Standard'
END AS salary_level
FROM employees;
This adds a new column called salary_level. It checks each employee’s salary. If it’s more than 70000, it puts ‘High’; otherwise, it puts ‘Standard’.
24. SUBQUERY
Use the result of one query as part of another query.
SELECT *
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');
The inner query (SELECT id FROM departments WHERE name = 'Sales') finds the ID number for the ‘Sales’ department. The main query then uses this ID to find all employees who have that department_id.
25. WINDOW Functions
Perform calculations across a set of table rows that are related to the current row. They don’t group rows like GROUP BY.
SELECT first_name, salary, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
RANK() is a window function. OVER (PARTITION BY department ORDER BY salary DESC) defines the “window.” It means: for each department (PARTITION BY department), rank the employees based on their salary from highest to lowest (ORDER BY salary DESC).
The result shows each employee and their rank within their department.
26. ROW_NUMBER
Give a unique number to each row in your result set.
SELECT ROW_NUMBER() OVER (ORDER BY order_date) AS row_num, order_id
FROM orders;
ROW_NUMBER() assigns a number starting from 1 based on the order you specify (ORDER BY order_date). It numbers the orders based on when they happened.
27. PARTITION BY
Used within window functions to divide rows into groups or “partitions” for calculations.
SELECT department, first_name, salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM employees;
This uses PARTITION BY department with SUM() as a window function. For each employee’s row, it shows the sum of all salaries within that employee’s department. The SUM is calculated for the whole department, not just the individual employee.
28. WITH Clause (CTE – Common Table Expression)
Create a temporary, named result set that you can use within a single query. Makes complex queries easier to read.
WITH high_earners AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT *
FROM high_earners
WHERE department = 'IT';
The WITH high_earners AS (...) part creates a temporary view called high_earners containing employees with salary over 80000. The second SELECT then uses this temporary view to find employees from that group who are in the ‘IT’ department.
29. PIVOT
Turn rows into columns (syntax varies a lot by database).
-- Example concept (syntax highly dependent on database)
SELECT department, [2023], [2024]
FROM (SELECT department, year, salary FROM employees) AS SourceData
PIVOT (SUM(salary) FOR year IN ([2023], [2024])) AS PivotTable;
The idea is to take data where you might have rows like (Sales, 2023, 100000), (Sales, 2024, 110000), (IT, 2023, 90000) and turn it into columns like:
Department 2023 2024
-----------|--------|--------
Sales | 100000 | 110000
IT | 90000 | (null)
It helps summarize data by showing categories as columns.
30. UNION
Combine the results of two or more SELECT queries into one result set. Removes duplicate rows by default.
SELECT first_name FROM employees
UNION
SELECT first_name FROM contractors;
This lists all unique first names found in either the employees table or the contractors table. Use UNION ALL if you want to include duplicate names.
Data Modification Queries
These queries let you add, change, or remove data in your tables.
31. INSERT
Add one new row to a table.
INSERT INTO customers (first_name, email)
VALUES ('Alice', 'alice@example.com');
This adds a new customer to the customers table, providing values for the first_name and email columns.
32. Bulk INSERT
Add multiple new rows at once.
INSERT INTO products (product_name, price)
VALUES ('Laptop', 999.99), ('Phone', 499.99);
This adds two new products to the products table with just one command.
33. UPDATE
Change existing data in one or more rows.
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'HR';
This finds all employees in the ‘HR’ department and increases their salary by 5% (multiplies it by 1.05).
34. DELETE
Remove one or more rows from a table.
DELETE FROM orders
WHERE status = 'Cancelled' AND order_date < '2023-01-01';
This removes all rows from the orders table where the order status is ‘Cancelled’ AND the order date was before January 1, 2023. Be very careful with DELETE! If you don’t use a WHERE clause, it will delete all rows!
35. Transaction Management
Group multiple data changes into a single unit of work. Either all changes succeed (COMMIT) or none of them do (ROLLBACK).
BEGIN TRANSACTION; -- Start the group of commands
UPDATE accounts SET balance = balance - 50 WHERE id = 1; -- Take 50 from account 1
UPDATE accounts SET balance = balance + 50 WHERE id = 2; -- Add 50 to account 2
-- If both updates worked:
COMMIT; -- Save the changes
-- If something went wrong with either update:
-- ROLLBACK; -- Undo both changes back to how they were before BEGIN TRANSACTION
This shows how to transfer money safely. You start a transaction. If taking money from account 1 works and giving it to account 2 also works, you COMMIT (save) both changes. If either step fails, you would ROLLBACK to undo both, preventing money from just disappearing or appearing.
Data Definition and Optimization Queries
These queries help you build and fine-tune the database structure itself.
36. CREATE TABLE
Make a new table in your database.
CREATE TABLE projects (
id INT PRIMARY KEY,
project_name VARCHAR(100),
start_date DATE
);
This creates a new table named projects. It defines three columns: id (a whole number that is the main identifier for each project), project_name (text up to 100 characters), and start_date (a date).
37. CREATE INDEX
Build a special lookup structure on a column to make searching faster.
CREATE INDEX idx_customer_email
ON customers(email);
This creates an index on the email column in the customers table. If you often search for customers using their email address, this index will help the database find the rows much faster.
38. CREATE VIEW
Create a “virtual table” based on the result of a query. It doesn’t store data itself but makes complex queries look simpler.
CREATE VIEW active_customers AS
SELECT *
FROM customers
WHERE last_purchase > '2024-01-01';
This creates a view named active_customers. Now, instead of writing the full SELECT * FROM customers WHERE last_purchase > '2024-01-01'; every time, you can just query SELECT * FROM active_customers; and it will give you the same result (all customers who made a purchase after Jan 1, 2024).
39. Stored Procedure
Save a set of SQL statements as a named unit. You can run it later just by calling its name. Good for repeated tasks.
CREATE PROCEDURE GetTopEarners (@threshold INT)
AS
SELECT *
FROM employees
WHERE salary > @threshold;
(Syntax varies by database). This creates a stored procedure named GetTopEarners. It takes one input value, @threshold (a whole number). When you run this procedure and give it a number (e.g., EXEC GetTopEarners 75000), it runs the SELECT statement inside, showing employees with salary over that number.
Queries for Handling Special Data and Cases
These queries help you work with missing data, dates, and find problems like duplicates.
40. NULL Handling
Check if a value is missing (null).
SELECT first_name
FROM customers
WHERE phone IS NULL;
NULL means “no value.” It’s different from zero or an empty text. This query finds customers whose phone column has no value stored. Use IS NOT NULL to find rows where a value does exist.
41. COALESCE
Show the first non-null value from a list.
SELECT COALESCE(email, phone, 'No contact') AS contact_info
FROM customers;
This looks at the email column. If it’s not null, it shows the email. If email is null, it looks at the phone. If phone is not null, it shows the phone. If both are null, it shows the text ‘No contact’. It gives you the best available contact info.
42. DATE Functions
Work with date and time data (functions vary by database).
SELECT order_id, DATEADD(day, 5, order_date) AS due_date
FROM orders;
(Note: DATEADD is for SQL Server. Other databases have different functions like INTERVAL or date + integer). This adds 5 days to the order_date for each order and shows it in a new column called due_date. There are many functions to get parts of dates (year, month), find differences, etc.
43. Duplicate Detection
Find rows that are the same based on one or more columns.
SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
This groups customers by their email address. Then, it uses HAVING to find only those groups where the count of rows is greater than 1. This means those email addresses appear more than once in the table, showing duplicates.
How to Write Good Queries (Best Practices)
Write Clearly:
- Use short names (aliases) for tables in joins (like
eforemployees). - Make your code easy to read by using spaces and putting parts on new lines.
- Add comments (
--at the start of a line) to explain tricky parts.
Make Them Fast:
- Use
CREATE INDEXon columns you often filter or join on. - Only select the columns you actually need, don’t just use
SELECT *. - Look at the “query plan” (a tool in database software) to see how the database runs your query and where it’s slow.
Avoid Problems:
- Remember that
NULLis tricky; useIS NULLorIS NOT NULLto check for it. - Test your queries on a small amount of data first before running them on everything.
- Double-check your
JOINconditions to make sure you are linking tables correctly.
Conclusion
SQL queries are powerful tools. They let you get, change, and understand the data in your databases.
We’ve looked at many types of queries, from simple selections to complex joins and data changes.
What should you do next? Practice! Find some sample databases online (like AdventureWorks or Chinook are popular ones) and try running these queries yourself.
There are many websites and resources with more SQL information and places where you can practice writing queries. You can also enroll for Free SQL Courses.
Keep trying and exploring. The more you write and run queries, the better you will become at talking to databases!
Other SQL Resources:
