Wednesday, 25 April 2018

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 resultNames 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 resultNames 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 resultCount 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 usedGROUP 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 resultNames 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 resultAverage 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
 
 









No comments:

Post a Comment

Important properties of two phase locking protocol and its variants

Important properties of two phase locking protocol and its variants Properties of     2PL Serializability  2PL ensures co...