程序代写代做代考 database CS5481 Data Engineering Tutorial 2

CS5481 Data Engineering Tutorial 2
Using relational operators, specify the following queries on the COMPANY relational database schema shown below.
1. Retrieve the name (Fname, Lname) and address (Address) of all employees who work for the ‘Research’ department.
2. List names (Fname, Lname) of all managers and the name of the departments (Dname) they manage, if any.
TEMP(EMPLOYEE ⟕ SSn=Mgr_ssn DEPARTMENT) RESULT Lname,Fname, Dname (TEMP)
3. For every project located in ‘Stafford’, list the project number (Pnumber), the controlling department number (Dnum), and the department manager’s last name (Lname), address (Address) and birth date (Bdate).

CS5481 Data Engineering Tutorial 2
4. Retrieve the names (Lname and Fname) of employees in department number 5 who work more than 10 hours per week on the ‘ProductX’ project.
EMP_W_X( Pname=’ProductX’ (PROJECT)) Pnumber=Pno WORKS_ON EMP_WORK_10EMPLOYEE Ssn=Essn ( Hours>10 (EMP_W_X)) RESULT Lname,Fname ( Dno=5 (EMP_WORK_10))
5. Make a list of all project numbers (Pnumber) for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
SMITHS(Essn)(Ssn ( Lname=’Smith’ (EMPLOYEE))) SMITH_WORKER_PROJSPno (WORKS_ON SMITHS) MGRSLname,Dnumber (EMPLOYEE Ssn=Mgr_ssn DEPARTMENT) SMITH_MANAGED_DEPTS(Dnum)(Dnumber ( Lname=’Smith’ (MGRS))) SMITH_MGR_PROJSPnumber (SMITH_MANAGED_DEPTS PROJECT) RESULT(SMITH_WORKER_PROJS  SMITH_MGR_PROJS)
6. Retrieve the names (Lname and Fname) of employees who have no dependents.
ALL_EMPSSsn (EMPLOYEE) EMPS_WITH_DEPS(Ssn)(Essn (DEPENDENT)) EMPS_WITHOUT_DEPS(ALL_EMPS – EMPS_WITH_DEPS) RESULTLname,Fname (EMPS_WITHOUT_DEPS EMPLOYEE)
7. List the names (Lnamd and Fname) of all employees with two or more dependents.
EMPS_WITH_DEPS(Ssn,no_of_dependent)(Essncount (Dependent_name) (DEPENDENT)) EMPS_WITH_DEPS2 no_of_dependent ≥ 2 (EMPS_WITH_DEPS) RESULTLname,Fname (EMPS_WITH_DEPS2 EMPLOYEE)
8. Retrieve each department number (Dno), the number of employees in the department, and their average salary.
Dnocount (Ssn), avg (Salary) (EMPLOYEE)