Unlock the Secrets of SQL: Essential Skills for Developers

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Explanation:

• We create two tables: departments to hold department details and employees to hold employee information.
• The department_id in employees is a foreign key that references the departments table.

1.2. Inserting Data into Tables (INSERT INTO)

Problem: Insert a single department and an employee into the tables.

-- Insert one department
INSERT INTO departments (department_id, department_name)
VALUES (1, 'Marketing');

-- Insert one employee
INSERT INTO employees (employee_id, name, department_id, salary)
VALUES (1, 'John Doe', 1, 50000);

We insert a single department into the departments table and then insert a single employee into the employees table, linking them via department_id.

1.3. Inserting Multiple Records

Problem: Insert multiple records into both the departments and employees tables.

-- Insert multiple departments
INSERT INTO departments (department_id, department_name)
VALUES
    (2, 'Sales'),
    (3, 'HR'),
    (4, 'Engineering');

-- Insert multiple employees
INSERT INTO employees (employee_id, name, department_id, salary)
VALUES
    (2, 'Jane Smith', 2, 60000),
    (3, 'Sam Wilson', 3, 55000),
    (4, 'Lisa Brown', 4, 70000);
    (5, 'Alice White', 1, 90000);

Explanation:

• This query inserts multiple departments into the departments table and multiple employees into the employees table, ensuring each employee is linked to a valid department_id.

1.4. Basic Data Retrieval (SELECT Statement)

Problem: Retrieve all columns from the employees table.

SELECT * 
FROM employees;

Explanation:

• SELECT * fetches all columns from the employees table.

1.5. Filtering Data with WHERE Clause

Problem: Retrieve details of employees who work in the ‘Marketing’ department.

SELECT e.*
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Marketing';

Explanation:

• The INNER JOIN combines the employees and departments tables based on the department_id.
• The WHERE clause filters employees who belong to the ‘Marketing’ department by matching department_name.

1.6. Sorting Results (ORDER BY Clause)

Problem: Retrieve the names of employees and their salaries, sorted by salary in descending order.

SELECT e.name, e.salary
FROM employees e
ORDER BY e.salary DESC;

Explanation:

• ORDER BY e.salary DESC sorts the result set in descending order by salary, showing the highest-paid employees first.

Problem 2: Retrieve departments and their total salary expenditure, sorted by total salary in ascending order.

SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY total_salary ASC;

Explanation:

• SUM(e.salary) calculates the total salary for each department.
• GROUP BY d.department_name groups the results by department.
• ORDER BY total_salary ASC sorts the results by total salary in ascending order.

1.7. Aggregate Functions (SUM, COUNT, AVG)

Problem 1: Find the total salary expenditure for the ‘Sales’ department.

SELECT SUM(e.salary) AS total_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

Explanation:

• The INNER JOIN links the employees and departments tables using department_id.
• The WHERE clause filters employees working in the ‘Sales’ department.
• SUM(e.salary) calculates the total salary for all employees in that department.

Problem 2: Find the number of employees in each department.

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Explanation:

• COUNT(e.employee_id) counts the number of employees in each department.
• GROUP BY d.department_name groups the results by department.

Problem 3: Find the average salary for each department.

SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Explanation:

• AVG(e.salary) calculates the average salary per department.
• GROUP BY d.department_name ensures the average salary is computed for each department separately.

2. Intermediate Level

2.1. Using JOINs (INNER JOIN)

Problem: Retrieve employee names along with their department names from the employees and departments tables.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Explanation:

• INNER JOIN connects the employees table with the departments table on the department_id field.

2.2. Grouping Data with GROUP BY

Problem: Find the average salary by department.

SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Explanation:

• INNER JOIN connects the employees table with the departments table using department_id.
• AVG(e.salary) calculates the average salary per department.
• GROUP BY d.department_name groups the results by department, ensuring the average salary is calculated for each department separately.

2.3. Using HAVING with Aggregate Functions

Problem: Retrieve departments that have more than 10 employees.

SELECT d.department_name, COUNT(*) AS employee_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING COUNT(*) > 10;

Explanation:

• INNER JOIN links the employees table with the departments table using department_id.COUNT(*) counts the number of employees in each department.GROUP BY d.department_name groups the employees by department.HAVING COUNT(*) > 10 filters the results to return only departments that have more than 10 employees.

2.4. Subqueries

Problem: Retrieve employees who earn more than the average salary of the company.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation:

• The subquery calculates the average salary, and the main query retrieves employees earning above that average.

3. Advanced Level

3.1. Complex Subqueries

Problem: Find employees who have a salary higher than anyone in the ‘HR’ department.

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > ALL (
    SELECT e2.salary 
    FROM employees e2
    INNER JOIN departments d ON e2.department_id = d.department_id
    WHERE d.department_name = 'HR'
);

Explanation:

• The main query selects employees who have a salary greater than all employees in the ‘HR’ department.
• The subquery (SELECT e2.salary) retrieves the salaries of employees working in the ‘HR’ department by joining employees with departments based on department_id and filtering by department_name = 'HR'.
• WHERE e.salary > ALL (...) ensures that only employees with a salary higher than all employees in the ‘HR’ department are returned.

3.2. Window Functions (ROW_NUMBER, RANK)

Problem: Rank employees by their salary within each department.

SELECT e.name, d.department_name, e.salary,
       ROW_NUMBER() OVER (PARTITION BY d.department_name ORDER BY e.salary DESC) AS salary_rank
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Explanation:

• ROW_NUMBER() ranks employees by salary within each department (PARTITION BY d.department_name).
• ORDER BY e.salary DESC ensures the highest salaries get the highest rank (i.e., salary_rank).
• The alias salary_rank replaces rank to avoid conflicts with the reserved keyword.

3.3. Self JOIN

Problem: Find all pairs of employees who work in the same department.

SELECT e1.name AS employee_1, e2.name AS employee_2, d.department_name
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id
INNER JOIN departments d ON e1.department_id = d.department_id
WHERE e1.name <> e2.name;

Explanation:

• This is a self-join, where the employees table (e1) is joined with itself (e2) on department_id to find employees who work in the same department.
• INNER JOIN departments d is used to get the department name from the departments table.
• The WHERE e1.name <> e2.name ensures we do not pair an employee with themselves.
• The query retrieves all pairs of employees (employee_1 and employee_2) who work in the same department, along with the department name.

3.4. Common Table Expressions (CTEs)

Problem: Find the second highest salary in the company.

WITH SalaryRank AS (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT salary
FROM SalaryRank
WHERE salary_rank = 2;

Explanation:

• salary_rank replaces rank to avoid using the reserved keyword in MySQL.
• The ROW_NUMBER() function assigns a unique ranking to each salary in descending order.
• The outer query filters the results to return only the employee with the second-highest salary.

Alternative with RANK()

WITH SalaryRank AS (
    SELECT salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT salary
FROM SalaryRank
WHERE salary_rank = 2;

Conclusion

Mastering SQL requires continuous practice with real-world problems. Start with simple queries, and gradually explore advanced techniques like joins, subqueries, and window functions. SQL is a powerful tool that allows you to manipulate and analyze data efficiently, and these practice exercises are a great way to improve your skills, whether you are a beginner or an experienced user.