Write SQL statment for each of the following requirement

Write SQL statement for each of the following requirement

Question:

Consider the following relational schema

EMPLOYEE (EmpNo, Name, DOB, DepNo)

DEPARTMENT (DepNo, DName, Location, Head)

JOBHISTORY (EmpNo, Position, StartDate, EndDate, Salary)

where

EmpNo is the primary key for table EMPLOYEE

DepNo is the primary key for table DEPARTMENT

(EmpNo, StartDate) is the composite primary key for table JOBHISTORY

Write an SQL statement for each of the following queries.

a) List all the employees (by name) who are working at the Finance department.

b) List all the employees who are working at the same department as Michael.

c) Find the number of employees currently working at each department (by name).

d) List all the employees (by Name) who are currently working as Software Engineer.

e) Count the average salary for all the employees who are currently working at the Information Technology department.

Solution:

a) List all the employees (by name) who are working at the Finance department.

Expected result – Names of employees of Finance department

Where to get? – from EMPLOYEE and DEPARTMENT tables

How? – join both tables on DepNo attribute.

WHERE clause conditions – join condition Employee.DepNo = Department.DepNo  and Dname = ‘Finance’

Query:

SELECT Name FROM Employee, Department

WHERE Employee.DepNo = Department.DepNo

AND DName = ‘Finance’;

b) List all the employees who are working at the same department as Michael.

Expected result – Names of employees of Michael’s department

Where to get? – from EMPLOYEE table

How? – use sub-query.

WHERE clause conditions – filter Michael’s department using Name = ‘Michael’ in the sub-query.

Query:

SELECT Name FROM Employee

WHERE DepNo = (SELECT DepNo FROM Employee

WHERE Name = ‘Michael’);

c) Find the number of employees currently working at each department (by name).

Expected result – Count of employees at each department (individual count)

Where to get? – from EMPLOYEE and DEPARTMENT tables

How? – use join and aggregate function count().

WHERE clause conditions – join condition Employee.DepNo = Department.DepNo.

Any other clauses to be used – GROUP BY clause

Query:

SELECT DName, Count(*) FROM Employee, Department

WHERE Employee.DepNo = Department.DepNo

GROUP BY DName;

d) List all the employees (by Name) who are currently working as Software Engineer.

Expected result – Names of employees working as software engineers

Where to get? – from EMPLOYEE and JOBHISTORY tables

How? – use join.

WHERE clause conditions – join condition Employee.EmpNo = Jobhistory.EmpNo, Position = ‘Software Engineer’, and EndDate is NULL of empty.

Any other clauses to be used – No

Query:

SELECT Name FROM Employee, Jobhistory

WHERE Employee.EmpNo = Jobhistory.EmpNo

AND Position = ‘Software Engineer’

AND EndDate = ‘’;

e) Count the average salary for all the employees who are currently working at the Information Technology department.

Expected result – Average salary of employees of Information Technology department

Where to get? – from EMPLOYEE, DEPARTMENT, and JOBHISTORY tables

How? – use join and aggregate function avg().

WHERE clause conditions – join condition Employee.DepNo = Department.DepNo, Employee.EmpNo = Jobhistory.EmpNo, DName = ‘Information Technology’ and EndDate is NULL or empty [we need to include only current salary. That means salary of current position].

Any other clauses to be used – No

Query:

SELECT Avg(*) FROM Employee, Department, Jobhistory

WHERE Employee.DepNo = Department.DepNo

AND Employee.EmpNo = Jobhistory.EmpNo

AND DName = ‘Information Technology’

AND EndDate = ‘’;

 ***********

Go to Natural join in DBMS page (SQL)

Go to Equi-join in DBMS page (SQL)

Go to Multiple choice questions in SQL/Relational algebra page
Go to Solved exercise 1 
Go to Solved exercise 2
Go to Solved exercise 3