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 pageGo to Solved exercise 1
Go to Solved exercise 2
Go to Solved exercise 3