- 1 Employeeld Membership Type E1 Ashforce E3 Bass Es Bass E2 Vallance E6 Null F4 Mission Table 1 Employees Mtype Budge 1 (377.1 KiB) Viewed 36 times
(1) employeeld membership Type E1 AshForce E3 Bass ES Bass E2 Vallance E6 NULL F4 Mission Table 1: Employees mType budget Bass 675.00 Vallance Mission Ashforce 348.00 250.00 NULL Table 2: Budget Table 1 records employees' membership type and Table 2 records the budget for each membership type. Determine the output produced by each of the following queries: i. SELECT distinct b.* FROM employees as e RIGHT JOIN budget as b ON e. membership Type = b.mtype; (3 marks) ii. SELECT SUM(budget) as totalbudget, Mtype FROM employees as e INNER JOIN budget as b ON e.membership Type = b.m Type GROUP BY mType; (3 marks) iii. SELECT F.EmployeeID, S.EmployeeID, F.membership FROM Employees F JOIN Employees S ON F.membership Type=S.membership Type WHERE F.EmployeelD<S.EmployeeID ORDER BY F. Employee D, S.EmployeeID; (3 marks) (2) Consider the following two tables called Employee and Department. The following data shows details of projects and the employees who are placed on those projects along with their job titles and the hours worked by each employee on each project. Employee SMITH EMPNO ENAME JOB 7934 MILLER CLERK 7782 CLARK MANAGER 7839 KING PRESIDENT 7369 CLERK 7876 ADAMS CLERK 7566 JONES MANAGER 7788 SCOTT ANALYST 7902 FORD ANALYST 7900 JAMES CLERK 7654 MARTIN SALESMAN 7521 WARD SALESMAN 7844 TURNER SALESMAN 7499 ALLEN SALESMAN MGR HIREDATE 7782 23-Jan-82 7839 9-Jun-81 17-NOV-81 7902 17-DEC-80 7788 12-Jan-83 7839 2-Apr-81 7566 9-Dec-82 7566 3-Dec-81 7698 3-Dec-81 7698 28-SEP-81 7698 22-FEB-81 7698 8-Sep-81 7698 01-MAY-81 SAL 1300 2450 5000 800 1100 2975 3000 3000 950 1250 1250 1500 COMM DEPT 10 10 10 20 20 20 20 20 30 30 1400 500 30 30 0 1600 300 30 Department DEPTNO DNAME 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS LOC NEW YORK DALLAS CHICAGO BOSTON (a) The foilowing correlated subquery is executed on the two tables. SELECT deptno,dname, loc FROM department d WHERE EXISTS (SELECT 'X' FROM employee e WHERE e deptno = d.deptno); which generates the following output: DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO i. Explain the term "correlation" and discuss how the correlation works in (4 marks) this query. Write an alternative query to generate the same output using an IN(subquery) construct with no correlation. (3 marks) iii. Write an alternative query to generate the same output using a join instead of any subquery. (3 marks) (b) A user connects to a database and issues the following commands on the Employee table (as shown above) that he owns. UPDATE employee SET sal = sal + 500; UPDATE employee SET sal = sal + 2000 WHERE ename = 'FORD'; COMMIT; UPDATE employee SET sal = sal +99; ROLLBACK, What will be the value of sal for: (1)SMITH (2)FORD Explain your reasoning. (4 marks) A user creates the following view based on the above shown Employee and Department tables: (c) CREATE VIEW ed AS SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,d.deptno, d.dname, d.loc FROM employee e JOIN department d WHERE e.deptno = d.deptno; The Department table has a primary key on the deptno column. State which of the following update statements on the view will succeed and which will not. Give reasons for the successes and failures. (1) UF DATE ed SET sal = 1000 WHERE ename = 'MILLER': (2) UPDATE ed SET dname = 'FINANCE' WHERE ename = 'MILLER'; (3 marks) (d) Examine the following query. SELECT empno, ename, sal, comm, sal+ comm tot_comp FROM employee WHERE deptno = 30; which generates the following output: EMPNO ENAME 7499 ALLEN 7521 WARD 7654 MARTIN 7698 BLAKE 7844 TURNER 900 JAMES SAL 1600 1250 1250 2850 1500 950 COMM 300 500 1400 TOT_COMP 1900 1750 2650 0 1500 Comment on the data produced in the TOT_COMP column and how the outcome of this query could be improved. (3 marks) (e) Management needs to know details of employees who earn more than any employee in department 30. The developer, Mary, writes the following query to provide this information. SELECT ename, sal, deptno FROM employee WHERE sal > ANY(SELECT sal FROM employee WHERE deptno = 30); This statement generates the following output. DEPTNO 10 SAL 5000 2450 1300 10 10 ENAME KING CLARK MILLER SCOTT JONES ADAMS FORD WARD 20 20 3000 2975 1100 3000 1250 20 20 30