SQL Queries On Scott & Tiger Schema

SQL Queries on Scott & Tiger Schema | Question and Solution Explanation

In the world of data management, SQL queries are essential for extracting valuable information from databases. As you progress in your SQL journey, it becomes crucial to master intermediate-level queries to tackle complex scenarios and derive meaningful insights. In this article, we will delve into a collection of intermediate SQL queries, providing comprehensive solutions and explanations to enhance your SQL skills.

Lets us consider an example of the “Scott” schema with the “tiger” user in Oracle, we can create tables with appropriate attributes. Here’s an example of creating a simple “employees” table in the “Scott” schema:

CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;
ALTER USER scott DEFAULT TABLESPACE users;

Next, let’s create an “employees” table within the “scott” schema:

CREATE TABLE scott.employees (
    employee_id   NUMBER(6) PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(100),
    hire_date     DATE,
    job_id        VARCHAR2(50),
    salary        NUMBER(8,2),
    department_id NUMBER(4)
);

This creates the “employees” table with the following attributes:

employee_id: Unique identifier for each employee (primary key)
first_name: First name of the employee
last_name: Last name of the employee
email: Email address of the employee
hire_date: Date when the employee was hired
job_id: Job ID representing the employee’s job title
salary: Salary of the employee
department_id: Department ID indicating the department to which the employee belongs

Now, let’s provide some SQL queries of different levels of complexity:

SQL Queries on Scott Tiger Schema

1. Retrieve the employee details along with their department name and location.

SELECT e.employee_id, e.first_name, e.last_name, d.department_name, d.location
FROM scott.employees e
JOIN departments d ON e.department_id = d.department_id;

Explanation: This query performs an inner join between the "employees" and "departments" tables based on the department_id column. It selects the employee_id, first_name, last_name from the employees table and department_name, location from the departments table.

2. Find the highest-paid employee in each department.

SELECT department_id, MAX(salary) AS max_salary
FROM scott.employees
GROUP BY department_id;

Explanation: This query selects the department_id and calculates the maximum salary using the MAX() function from the employees table. The results are grouped by department_id using the GROUP BY clause.

3. Retrieve the top 5 departments with the highest average salary.

SELECT d.department_id, d.department_name, AVG(e.salary) AS average_salary
FROM scott.employees e
JOIN scott.departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY average_salary DESC
FETCH FIRST 5 ROWS ONLY;

Explanation: This query performs an inner join between the "employees" and "departments" tables based on the department_id column. It calculates the average salary for each department, orders the results by average_salary in descending order, and limits the result set to the top 5 rows.

4. Find the employees who have a salary greater than the average salary of all employees.

SELECT *
FROM scott.employees
WHERE salary > (SELECT AVG(salary) FROM scott.employees);

Explanation: This query selects all columns from the employees table and filters the results by comparing the salary of each employee with the average salary of all employees obtained from the subquery.

5. Retrieve the employees who were hired in the same month and year as the employee with ID 100.

SELECT *
FROM scott.employees
WHERE TO_CHAR(hire_date, 'YYYY-MM') = (SELECT TO_CHAR(hire_date, 'YYYY-MM') FROM scott.employees WHERE employee_id = 100);

Explanation: This query selects all columns from the employees table and filters the results by comparing the month and year of the hire_date with the month and year of the hire_date of the employee with ID 100 obtained from the subquery.

6. Retrieve the employee details who have a salary greater than the average salary in their respective departments and belong to the ‘IT’ department

SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM scott.employees e
JOIN scott.departments d ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM scott.employees WHERE department_id = e.department_id)
  AND d.department_name = 'IT';

Explanation: This query selects the employee_id, first_name, last_name, salary, and department_name from the employees and departments tables. It filters the results to include only those employees whose salary is greater than the average salary in their respective departments and who belong to the 'IT' department.

7. Retrieve the total salary expense for each department, along with the number of employees in each department.

SELECT d.department_id, d.department_name, COUNT(*) AS employee_count, SUM(e.salary) AS total_salary
FROM scott.employees e
JOIN scott.departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;

This query performs an inner join between the employees and departments tables and calculates the count of employees and the sum of salaries for each department. The results are grouped by department_id and department_name.

8. Find the department(s) with the highest number of employees.

SELECT d.department_id, d.department_name, COUNT(*) AS employee_count
FROM scott.employees e
JOIN scott.departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MAX(emp_count) FROM (SELECT COUNT(*) AS emp_count FROM scott.employees GROUP BY department_id));

This query retrieves the department_id, department_name, and employee_count by performing an inner join between the employees and departments tables. The results are grouped by department_id and department_name. The HAVING clause is used to filter the departments with the highest number of employees by comparing the employee_count with the maximum employee count obtained from the subquery.

9. Retrieve the employees who have the same job title as the employee with ID 100.

SELECT e.employee_id, e.first_name, e.last_name, e.job_id
FROM scott.employees e
WHERE e.job_id = (SELECT job_id FROM scott.employees WHERE employee_id = 100);

This query selects the employee_id, first_name, last_name, and job_id from the employees table and filters the results to include only those employees whose job_id matches the job_id of the employee with ID 100 obtained from the subquery.

10. Find the employees who have a higher salary than all the employees in the ‘Sales’ department.

SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM scott.employees e
WHERE e.salary > ALL (SELECT salary FROM scott.employees WHERE department_id = (SELECT department_id FROM scott.departments WHERE department_name = 'Sales'));

This query retrieves the employee_id, first_name, last_name, and salary from the employees table. It filters the results to include only those employees whose salary is higher than all the salaries in the 'Sales' department obtained from the subquery.

Conclusion:

Mastering intermediate SQL queries empower you to handle complex scenarios efficiently and extract valuable insights from databases. By understanding and practicing these examples, you can enhance your SQL skills and become proficient in solving diverse data management challenges.

Remember to adapt these queries to your specific database schema and requirements, and continue exploring advanced SQL techniques to further broaden your expertise.