Question

Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNA) TA DEPARTMENT(DNAME, DNUMBER. MESSINS, MGRSTARTDATE) DEPT_LOCATIONS(DNUMBER. DLOCATION PROJECT(PNAME, PNUMBER. PLOCATION, DNLIM) WORKS_ONCESSN.PNG, HOURS) DEPENDENTESSN, DEPENDENT-NAME, SEX, BDATE, RELATIONSHIP) Write SQL statements for the following queries: a) List the names of those employees who work in the "Production" department (6 marks). b) Find the maximum salary, minimum salary, and the average salary among employees who work for the "Production department (6 marks). Count the number of projects which are undertaken by each individual department (6 marks) d) Find out all possible locations (including departmental and project locations) where "Mr. John Liang" works (6 marks). e) Create views of all attributes in EMPLOYEE for those employees who work in Department 3 (DNO=3) (6 marks).

Answer 1

Dear Student ,

As per requirement submitted above kindly find below solution.

Question a:

SQL query :

select name from employee where dno=(select dnumber from department where dname='Production');

Explanation :

• This sql query is using sub query.
• Inner query will return department number where department name is production
• Main query will return name of the employee from employee table.

******************************

Question b:

SQL query :

select min(salary),max(salary),avg(salary) from employee,department where employee.dno=department.dnumber and dname='Production';

Explanation :This sql query will join two tables employee,department based on department number and will return the result.

******************************

Question c:

SQL query :

select dname,count(dnum) from department,project
where
department.dnumber=project.dnum
group by dname;

Explanation :This sql query will join two tables department and project , will get the dname and count of the project in each department.

******************************

Question e:

SQL query :

create view view_employee
as
select * from employee where dno=3

Explanation :

• This sql query will create a view with name view_employee

******************************

NOTE :PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.