• Tutorial 6
  • Exercises
    • Q1 Retrieve employees names who are working in the Research department.
    • Q2 Retrieve project names where the last name of the department manager is Wong
    • Q3 Retrieve managers names having projects in ‘Stafford’
    • Q4 Retrieve the names of all employees in ‘Research’ department who work more than 10 hours per week on theProductX project.
    • Q5 List the names of all employees who have a dependent with the same first name as themselves.
    • Q6 For each project, list the project name and the total hours per week (by all employees) spent on that project.
    • Q7 Retrieve the names of all employees who do not work on any project.
    • Q8 Retrieve project names where the department manager last name is Wong or project names where employee smith is working in.

Tutorial 6

Exercises

Regard the following Relational model

Q1 Retrieve employees names who are working in the Research department.

• cross product:

  SELECT FNAME,LNAME
  from employee,department
  where DNO = DNUMBER and DNAME = 'Research'
  

• JOIN Query:

  SELECT FNAME,LNAME FROM 
  employee JOIN department ON DNO = DNUMBER 
  Where DNAME = 'Research'
  

Q2 Retrieve project names where the last name of the department manager is Wong

• cross product:

  SELECT PNAME
  from employee,department,project
  where DNUM = DNUMBER and MGRSSN = SSN and LNAME = "Wong"
  

• JOIN Query:

  SELECT PNAME from 
  employee JOIN department ON MGRSSN = SSN
  JOIN project ON DNUM = DNUMBER 
  WHERE LNAME = "Wong"
  

Q3 Retrieve managers names having projects in ‘Stafford’

• cross product:

  SELECT FNAME,LNAME
  from employee,department,project
  where DNUM = DNUMBER and MGRSSN = SSN and Plocation = "Stanfford"
  

• JOIN Query

  SELECT FNAME,LNAME from 
  employee JOIN department ON MGRSSN = SSN 
  JOIN project ON DNUM = DNUMBER 
  WHERE Plocation = "Stanfford"
  

Q4 Retrieve the names of all employees in ‘Research’ department who work more than 10 hours per week on theProductX project.

• JOIN Query

  select FName,MINIT,LNAME
  from employee join works_on on ESSN = SSN join project on PNO = PNUMBER join department on DNO =
  DNUMBER
  where DNAME = ‘Research’ and PName = 'ProductX' and hours>10;
  

• cross product:

  Select FNAME,MINIT,LNAME
  From employee,works_on,project,department
  Where ESSN = SSN and PNUMBER = PNO and DNO = Dnumber and DName = ‘Research’ and Hours > 10 and
  PNAME = 'ProductX';
  

Q5 List the names of all employees who have a dependent with the same first name as themselves.

select FNAME, MINIT, LNAME
from employee join dependent on ESSN =ssn
where FNAME = DEPENDENT_NAME;

Q6 For each project, list the project name and the total hours per week (by all employees) spent on that project.

select PNAME, sum(HOURS)
from project join works_on on PNO = PNUMBER
group by PNAME;

Q7 Retrieve the names of all employees who do not work on any project.

select Fname
from employee
where SSN not in (select SSN from employee, works_on
where SSN = ESSN );

Q8 Retrieve project names where the department manager last name is Wong or project names where employee smith is working in.

• Nested Query

  SELECT PNAME
  from project
  where PNUMBER in
  (SELECT PNUMBER from employee
  JOIN department on MGRSSN = SSN
  JOIN project on DNUM = DNUMBER
  where LNAME = "Wong" )
  or PNUMBER in (SELECT PNUMBER from employee
  JOIN works_on on ESSN = SSN
  JOIN project on PNO = PNUMBER
  where LNAME = "Smith" )
  

• Union Clause

  SELECT Pname from
  project JOIN JOIN department on DNUM = DNUMBER
  JOIN employee ON MGRSSN = SSN
  WHERE Lname= "Wong"
  UNION
  SELECT Pname from
  project JOIN works_on on Pnumber = Pno
  JOIN employee on SSN = ESSN
  WHERE Lname= "Smith"