PostgreSQL Exercises – Practice and Sharpen Your SQL Skills

Welcome to The Coding College, your ultimate destination for mastering coding and programming skills! This post provides a curated set of PostgreSQL exercises to help you strengthen your SQL skills with real-world scenarios.

Whether you are a beginner or an advanced user, these exercises will cover core PostgreSQL topics, including creating tables, manipulating data, and performing advanced queries.

Benefits of Practicing PostgreSQL Exercises

1. Hands-On Learning: Enhance your understanding by solving practical problems.
2. Problem-Solving Skills: Improve your ability to write optimized and efficient queries.
3. Interview Preparation: Get ready for technical interviews with database-related questions.

Sample Dataset

We’ll use a sample database named company with the following tables:

Table 1: employees

Table 2: projects

PostgreSQL Exercise Set

1. Basic Queries

• List All Employees
  Write a query to display all columns from the employees table.

SELECT * FROM employees;  

• Employee Count by Department
  Count the number of employees in each department.

SELECT department, COUNT(*) AS total_employees  
FROM employees  
GROUP BY department;  

• Highest Salary
  Find the employee with the highest salary.

SELECT name, salary  
FROM employees  
ORDER BY salary DESC  
LIMIT 1;  

2. Joins and Relationships

• List Projects by Employees’ Departments
  Display project names and the names of employees from the same department.

SELECT p.project_name, e.name AS employee_name  
FROM projects p  
JOIN employees e  
ON p.department = e.department;  

• Projects with Budget Greater Than Employee Salaries
  Find projects where the budget is greater than the highest salary in the department.

SELECT p.project_name, p.budget  
FROM projects p  
WHERE p.budget > ALL (  
    SELECT salary  
    FROM employees e  
    WHERE e.department = p.department  
);  

3. Advanced Queries

• Employees Joined After 2020
  List all employees who joined after 2020.

SELECT name, join_date  
FROM employees  
WHERE join_date > '2020-12-31';  

• Department with the Maximum Budget
  Identify the department that has the highest project budget.

SELECT department, MAX(budget) AS highest_budget  
FROM projects  
GROUP BY department;  

• Employee Salary Increase
  Update salaries for employees in the IT department by 10%.

UPDATE employees  
SET salary = salary * 1.10  
WHERE department = 'IT';  

4. Functions and Aggregations

• Total Budget for All Projects
  Calculate the total budget allocated for all projects.

SELECT SUM(budget) AS total_budget  
FROM projects;  

• Average Salary by Department
  Find the average salary for each department.

SELECT department, AVG(salary) AS average_salary  
FROM employees  
GROUP BY department;  

5. Complex Scenarios

• Find Over-Budget Projects
  List projects where the budget exceeds $100,000.

SELECT *  
FROM projects  
WHERE budget > 100000;  

• Employees Without Projects
  Find employees who are not assigned to any project.

SELECT e.name  
FROM employees e  
LEFT JOIN projects p  
ON e.department = p.department  
WHERE p.project_id IS NULL;  

• Department Performance
  Rank departments by the sum of their employees’ salaries.

SELECT department, SUM(salary) AS total_salary  
FROM employees  
GROUP BY department  
ORDER BY total_salary DESC;  

How to Use These Exercises

• Practice in a Test Environment: Set up a test database in PostgreSQL to execute these queries.
• Customize the Dataset: Modify the sample data to fit your specific use case.
• Combine Concepts: Try creating new queries by combining different exercises.

Explore More on The Coding College

We’re committed to delivering top-notch content aligned with Google’s E-E-A-T (Expertise, Experience, Authority, Trustworthiness) guidelines. For more PostgreSQL tutorials and coding tips, visit The Coding College.

Conclusion

By practicing these PostgreSQL exercises, you’ll gain hands-on experience and boost your confidence in database management. Whether you’re preparing for an interview or honing your skills, these exercises will set you on the path to mastery.

Stay tuned to The Coding College for more engaging and informative content!