Figure 11, Figure 12, Exercise 1: Specify The Following Quer ✓ Solved

Figure11figure12exercise 1 Specify The Following Queries On The Dat

Specify the following queries on the database in Figure 1.1 in SQL. Show the query results if each query is applied to the database state in Figure 1.2.

1. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.

2. Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees (as in Exercise 1.a). Can we specify this query in SQL? Why or why not?

In SQL, specify the following queries on the database in Figure 1.1 using the concept of nested queries and other concepts described in this chapter.

1. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.

2. Retrieve the names of all employees whose supervisor’s supervisor has ‘’ for Ssn.

3. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.

Specify the following views in SQL on the COMPANY database schema shown in Figure 1.1.

1. A view that has the department name, manager name, and manager salary for every department

2. A view that has the employee name, supervisor name, and employee salary for each employee who works in the ‘Research’ department

3. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it

Sample Paper For Above instruction

Introduction

This paper presents a comprehensive set of SQL queries and view definitions based on the database schema illustrated in Figures 1.1 and 1.2. The queries aim to extract specific insights related to departmental salaries, employee-supervisor relationships, and project participation, adhering to the instructions provided. Utilizing nested queries, aggregate functions, and views, these SQL statements demonstrate advanced data retrieval techniques suitable for managing complex relational databases.

Part 1: Queries on Departments and Employees

1. Departments with Average Salaries > $30,000

The first query identifies departments where the average employee salary exceeds $30,000. It retrieves the department name and employee count for such departments using an INNER JOIN to connect departments with employees and an AGGREGATE function to calculate the average salary.

SELECT d.dept_name, COUNT(e.emp_id) AS num_employees

FROM Department d


JOIN Employee e ON d.dept_id = e.dept_id


GROUP BY d.dept_name


HAVING AVG(e.salary) > 30000;

This query filters departments based on the average salary condition, providing insights into high-paying departments.

2. Number of Male Employees Earning > $30,000 per Department

For retrieving the number of male employees earning more than $30,000 per department, the query applies a WHERE clause filtering based on gender and salary, grouping by department.

SELECT d.dept_name, COUNT(e.emp_id) AS male_high_earners

FROM Department d


JOIN Employee e ON d.dept_id = e.dept_id


WHERE e.gender = 'M' AND e.salary > 30000


GROUP BY d.dept_name;

This compound condition addresses the question, and the query's design depends on the constraints of the specific database schema.

Part 2: Advanced Queries with Nested Queries

1. Employees in the Department of Highest Paid Employee

This query finds the employee(s) working in the department where the highest-paid employee resides. It employs a nested SELECT to find the maximum salary and then identifies the corresponding department and employees.

SELECT e.emp_name

FROM Employee e


WHERE e.dept_id = (


SELECT d.dept_id


FROM Employee e2


WHERE e2.salary = (


SELECT MAX(salary)


FROM Employee


)


);

2. Employees Whose Supervisor’s Supervisor has a Blank Ssn

This query retrieves employees whose supervisor’s supervisor has an empty ('') Ssn. The composition of self-joins allows navigating the supervisor hierarchy.

SELECT e.emp_name

FROM Employee e


JOIN Employee s ON e.supervisor_ssn = s.ssn


JOIN Employee ss ON s.supervisor_ssn = ss.ssn


WHERE ss.ssn = '';

3. Employees Making at Least $10,000 More Than Least Paid Employee

The query compares each employee’s salary to the minimum salary in the company using a subquery.

SELECT e.emp_name

FROM Employee e


WHERE e.salary >= (


SELECT MIN(salary) + 10000


FROM Employee


);

Part 3: View Definitions

1. Department and Manager Details

This view combines Department and Manager tables to present department name, manager name, and salary.

CREATE VIEW DeptManagerInfo AS

SELECT d.dept_name, m.emp_name AS manager_name, m.salary AS manager_salary


FROM Department d


JOIN Employee m ON d.manager_ssn = m.ssn;

2. Employees in Research Department with Supervisors

This view joins Employee with itself to provide employee and supervisor names and salaries, focusing on employees within the 'Research' department.

CREATE VIEW ResearchEmployees AS

SELECT e.emp_name AS employee_name, s.emp_name AS supervisor_name, e.salary AS employee_salary


FROM Employee e


JOIN Employee s ON e.supervisor_ssn = s.ssn


JOIN Department d ON e.dept_id = d.dept_id


WHERE d.dept_name = 'Research';

3. Projects with Multiple Employees and Total Hours

This view aggregates project data, including project name, controlling department, employee count, and total hours, for projects with more than one employee.

CREATE VIEW ProjectDetails AS

SELECT p.project_name, d.dept_name AS controlling_department, COUNT(e.emp_id) AS num_employees,


SUM(e.hours_worked) AS total_hours_per_week


FROM Project p


JOIN Department d ON p.control_dept_id = d.dept_id


JOIN Assignment a ON p.project_id = a.project_id


JOIN Employee e ON a.emp_id = e.emp_id


GROUP BY p.project_name, d.dept_name


HAVING COUNT(e.emp_id) > 1;

Conclusion

The presented SQL queries and views demonstrate advanced data manipulation techniques to extract meaningful insights from the database schema. These queries facilitate departmental salary analysis, employee-supervisor relationships, and project management, enhancing the database's utility for managerial decision-making and reporting.

References

  • Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems. Pearson.
  • Database System Concepts. McGraw-Hill.
  • Fundamentals of Database Systems. Pearson Education.
  • Modern Database Management. Pearson.
  • Fundamentals of Database Systems. Pearson.
  • An Introduction to Database Systems. Pearson.
  • Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Database Systems: Design, Implementation, & Management. Course Technology.
  • Database Design and Development. Cengage Learning.

Related Assignments