Specify The Following Queries On The Database In Figure 1.1

Specify the following queries on the database in Figure 1.1 in SQL

Specify the following queries on the database described in Figure 1.1, and show the query results based on 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; can we specify this query in SQL? Why or why not?

Exercise -2: In SQL, specify the following queries on the database in Figure 1.1 using nested queries and 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 an Ssn of '' (empty or null).

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

Exercise -3: Specify the following views in SQL on the COMPANY database schema shown in Figure 1.1:

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

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

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

Paper For Above instruction

Specify the following queries on the database in Figure 1 1 in SQL

Specify the following queries on the database in Figure 1.1 in SQL

SQL (Structured Query Language) is an essential tool for managing and querying relational databases. It enables precise retrieval, manipulation, and management of data stored in structured tables. In the context of the exercises provided, SQL queries are crafted to extract specific information from the database schema depicted in Figure 1.1 and summarized in Figure 1.2. These queries involve aggregation, nested subqueries, and views, which are fundamental concepts in SQL for complex data retrieval.

Exercise 1: Aggregation and Conditional Filtering

The first exercise involves identifying departments where the average employee salary exceeds $30,000. The goal is to retrieve both the department name and the number of employees in each qualifying department. This requires the use of aggregation functions such as AVG() for average salaries and COUNT() for counting employees.

Additionally, the second part questions whether it is feasible to find the number of male employees earning more than $30,000 in each department. This involves adding a filtering condition based on gender and salary. In SQL, such a query can be constructed using WHERE clause combined with GROUP BY. However, when filtering based on specific conditions such as gender and salary within each department, the query must incorporate proper grouping and filtering to yield correct results.

An example SQL query for the first task might look like:

SELECT d.dname, COUNT(e.ssn) AS num_employees

FROM department d


JOIN employee e ON d.dnumber = e.dno


GROUP BY d.dname


HAVING AVG(e.salary) > 30000;

This query joins the department and employee tables on department number, calculates the average salary for each department, and filters for those with an average above $30,000, returning the department name and employee count.

For the second part, to count male employees making more than $30,000 in each department, the query would be:

SELECT d.dname, COUNT(e.ssn) AS male_high_salary_count

FROM department d


JOIN employee e ON d.dnumber = e.dno


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


GROUP BY d.dname;

This statement filters employees by gender and salary before aggregation, thus returning the number of male employees earning over $30,000 per department.

Exercise 2: Nested Queries and Advanced SQL

Part 1: Employee in Department of Highest Salaried Employee

This task requires retrieving the names of all employees who work in the same department as the employee with the highest salary. The approach involves a nested query that first determines the maximum salary and identifies the associated department, then retrieves employees working in that department.

Example SQL:

SELECT e1.ename

FROM employee e1


WHERE e1.dno = (


SELECT d.dnumber


FROM employee e2


JOIN department d ON e2.dno = d.dnumber


WHERE e2.salary = (


SELECT MAX(salary) FROM employee


)


);

This query first selects the maximum salary, finds the department of that employee, and then retrieves all employees working in the same department.

Part 2: Employees with Supervisors whose Ssn is blank or null

Assuming Ssn is the supervisor's Social Security Number stored in the employee table as super_ssn, this query can be written as:

SELECT e.ename

FROM employee e


WHERE e.super_ssn IS NULL OR e.super_ssn = '';

Part 3: Employees earning at least $10,000 more than the least-paid employee

This involves a nested subquery to find the minimum salary and then filter employees earning at least $10,000 more than this minimum. Example:

SELECT e.ename

FROM employee e


WHERE e.salary >= (


SELECT MIN(salary) FROM employee


) + 10000;

Exercise 3: Creating Views for Complex Data Representation

Part 1: Department, Manager, and Manager Salary

This view joins department and employee tables, linking managers via the mgr_ssn:

CREATE VIEW department_manager_info AS

SELECT d.dname AS department_name,


e.ename AS manager_name,


e.salary AS manager_salary


FROM department d


JOIN employee e ON d.mgr_ssn = e.ssn;

Part 2: Employees in Research Department with Supervisors

This view involves joining employee tables with the department table for filtering:

CREATE VIEW research_employee_supervisor AS

SELECT e.ename AS employee_name,


s.ename AS supervisor_name,


e.salary AS employee_salary


FROM employee e


LEFT JOIN employee s ON e.super_ssn = s.ssn


JOIN department d ON e.dno = d.dnumber


WHERE d.dname = 'Research';

Part 3: Projects with Multiple Employees

This view aggregates data by project, counting employees, and summing hours:

CREATE VIEW project_summary AS

SELECT p.pname AS project_name,


d.dname AS department_name,


COUNT(e.ssN) AS num_employees,


SUM(e.hrs_per_week) AS total_hours_per_week


FROM project p


JOIN department d ON p.dnum = d.dnumber


JOIN works_on w ON p.pnumber = w.pno


JOIN employee e ON w.essn = e.ssn


GROUP BY p.pname, d.dname


HAVING COUNT(e.ssn) > 1;

Conclusion

These SQL queries demonstrate the power of relational database query language for retrieving complex information involving filtering, aggregation, nested queries, and views. Proper understanding of these concepts allows efficient extraction and presentation of data tailored to varied informational needs in organizational contexts.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Database System Concepts (7th ed.). McGraw-Hill Education.
  • Neumann, P. G. (2014). “Design and Implementation of Modern Database Management Systems.” ACM Computing Surveys, 46(3), 1-41.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson.
  • Abadi, D. J. (2018). “The Case for Data Engineering.” Communications of the ACM, 61(5), 56-63.
  • Hsu, R., & Wang, Z. (2017). “Advanced Query Techniques for Data Analysis.” IEEE Transactions on Knowledge and Data Engineering, 29(8), 1730-1743.
  • Kim, W. (2010). “Views in SQL: Theory and Practice.” Journal of Information Science, 36(2), 258–273.
  • Johnson, R., & Zicari, R. V. (2012). “Big Data and Data Management: Challenges and Techniques.” IEEE Computer, 45(6), 38-45.
  • O'Neil, P., & O'Neil, E. (2014). Database: Principles, Programming, and Practice. Cengage Learning.

Related Assignments