Insights into Company
← SQL learning path

Queries and Aggregation

Learn SQL from fundamentals to advanced interview and production-level database concepts through practical examples, questions and quizzes.

Complete SQL Learning & Interview Path

Learn SQL by working with realistic databases instead of memorising isolated commands. Every concept can be connected with practical tables such as students, employees, departments, countries, states, customers, products and orders.

SQL Fundamentals SELECT WHERE JOINs GROUP BY Subqueries Indexes Transactions Window Functions Interview Questions
Start Here

SQL through a real database

SQL is used to store, retrieve, modify and analyse structured data. Instead of learning commands independently, imagine that we are building a small company database.

Student table

A student database can contain identity, name, course and marks.

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(100), course VARCHAR(100), marks INT );

Employee table

Employee information can be connected with departments, salaries and managers.

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department_id INT, salary DECIMAL(12,2) );

Country / State table

Geographic data is useful for understanding relationships and JOINs.

CREATE TABLE states ( id INT PRIMARY KEY, name VARCHAR(100), country_id INT );

Customer / Orders

E-commerce data provides practical examples for aggregation, JOINs and transactions.

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, total DECIMAL(12,2), order_date DATE );
id name course marks
1 Rahul Java 85
2 Priya SQL 92
3 Amit React 76
SQL Learning Block 1

Questions 1 – 10

1
Question 11 of 10

How do you retrieve unique department IDs?

Solution

SELECT DISTINCT department_id FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 12 of 10

How do you sort employees by salary?

Solution

SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary DESC;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 13 of 10

How do you get the top 5 highest-paid employees?

Solution

MySQL: SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
SQL Server: SELECT TOP 5 * FROM employees ORDER BY salary DESC;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 14 of 10

How do you count employees?

Solution

SELECT COUNT(*) FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 15 of 10

How do you find the maximum salary?

Solution

SELECT MAX(salary) FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 16 of 10

How do you find the minimum salary?

Solution

SELECT MIN(salary) FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 17 of 10

How do you find the average salary?

Solution

SELECT AVG(salary) FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 18 of 10

How do you find total salary?

Solution

SELECT SUM(salary) FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 19 of 10

Find the number of employees in each department.

Solution

SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 20 of 10

Find average salary of each department.

Solution

SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Knowledge Check

Quiz after Questions 1 – 10

Test yourself before moving to the next SQL learning block.

1. How do you retrieve unique department IDs?
2. How do you sort employees by salary?
3. How do you get the top 5 highest-paid employees?
4. How do you count employees?
5. How do you find the maximum salary?
6. How do you find the minimum salary?
7. How do you find the average salary?
8. How do you find total salary?
9. Find the number of employees in each department.
10. Find average salary of each department.

Showing questions 1–10 of 13