SQL Subqueries for Data Analysis

Introduction

In this project, you will learn how to use subqueries to retrieve relevant information from the employee (emp) and department (dept) tables in the personnel database. You will practice writing complex SQL queries to access and analyze data from multiple tables.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and import the personnel database
• How to use a subquery to find the employee with the highest salary
• How to calculate the proportion of employees in a specific department compared to the entire company
• How to retrieve all employees working in a specific location using a subquery
• How to find employees whose salary exceeds the average salary in their department

🏆 Achievements

After completing this project, you will be able to:

• Understand the concept and use of subqueries in SQL
• Write complex SQL queries that combine data from multiple tables
• Analyze and extract meaningful insights from a database using subqueries
• Demonstrate your SQL skills in a practical, real-world scenario

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the personnel database.

1. Start the MySQL server using the sudo command without any password:

sudo service mysql start

2. Access the MySQL prompt:

sudo mysql

3. Import the data from the personnel.sql file into the MySQL database:

SOURCE /home/labex/project/personnel.sql;

This will create the personnel database and populate it with the necessary tables and data.

Query the Employee with the Highest Salary

In this step, you will learn how to use a subquery to retrieve the name of the employee with the highest salary.

1. Create a new file named subquery.sql in the /home/labex/project directory.
2. In the subquery.sql file, add the following SQL query:

-- Query to retrieve the employee(s) with the highest salary
SELECT ename AS `Name` FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

This query first finds the maximum salary in the emp table using a subquery, and then selects the ename (employee name) of the employee(s) who have that maximum salary.

Calculate the Proportion of Employees in Department 10

In this step, you will learn how to use a subquery to calculate the proportion of employees in department 10 compared to the entire company.

1. In the subquery.sql file, add the following SQL query:

-- Query to calculate the proportion of employees in department 10
SELECT d.dname AS Department, COUNT(e.empno) / (SELECT COUNT(empno) FROM emp) AS Proportion
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

This query first counts the number of employees in department 10 by joining the emp and dept tables. It then divides this count by the total number of employees in the emp table (using a subquery) to calculate the proportion.

Retrieve Employees Working in New York

In this step, you will learn how to use a subquery to retrieve all employees working in the New York location.

1. In the subquery.sql file, add the following SQL query:

-- Query to retrieve all employees working in New York
SELECT e.*
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';

This query joins the emp and dept tables to retrieve the employees whose department is located in New York.

Retrieve Employees with Salaries Exceeding the Department Average

In this step, you will learn how to use a subquery to retrieve employees whose salary is higher than the average salary in their respective department.

1. In the subquery.sql file, add the following SQL query:

-- Query to retrieve employees whose salary is higher than the average salary in their respective department
SELECT e.* FROM emp e
JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) AS avg
ON e.deptno = avg.deptno
WHERE e.sal > avg.avg_sal;

This query first calculates the average salary for each department using a subquery. It then joins this subquery with the emp table to select the employees whose salary is higher than the average salary in their department.

After completing these steps, your subquery.sql file should contain all the necessary SQL queries to fulfill the project requirements.

2. Save the file.
3. In the MySQL prompt, run the following command to execute the subquery.sql script:

SOURCE /home/labex/project/subquery.sql;

You should see the following output:

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.