Figure 3.6 One possible database state for the COMPANY relational database schema. EMPLOYEE \begin{tabular}{|l|c|l|c|c|c|c|l|c|c|} \hline Fname & Minit & Lname & Ssn & Bdate & Address & Sex & Salary & Super_ssn & Dno \\ \hline John & B & Smith & 123456789 & 19650109 & 731 Fondren, Houston, TX & M & 30000 & 333445555 & 5 \\ \hline Franklin & T & Wong & 333445555 & 19551208 & 638 Voss, Houston, TX & M & 40000 & 888665555 & 5 \\ \hline Alicia & J & Zelaya & 999887777 & 19680119 & 3321 Castle, Spring, TX & F & 25000 & 987654321 & 4 \\ \hline Jennifer & S & Wallace & 987654321 & 19410620 & 291 Berry, Bellaire, TX & F & 43000 & 888665555 & 4 \\ \hline Ramesh & K & Narayan & 666884444 & 19620915 & 975 Fire Oak, Humble, TX & M & 38000 & 333445555 & 5 \\ \hline Joyce & A & English & 453453453 & 19720731 & 5631 Rice, Houston, TX & F & 25000 & 333445555 & 5 \\ \hline Ahmad & V & Jabbar & 987987987 & 19690329 & 980 Dallas, Houston, TX & M & 25000 & 987654321 & 4 \\ \hline James & E & Borg & 888665555 & 19371110 & 450 Stone, Houston, TX & M & 55000 & NULL & 1 \\ \hline \end{tabular} DEPARTMENT DEPT_LOCATIONS \begin{tabular}{|l|c|c|c|} \hline \multicolumn{1}{|c|}{ Dname } & Dnumber & Mgr_ssn & Mgr_start_date \\ \hline Research & 5 & 333445555 & 19880522 \\ \hline Administration & 4 & 987654321 & 19950101 \\ \hline Headquarters & 1 & 888665555 & 19810619 \\ \hline \end{tabular} \begin{tabular}{|c|l|} \hline Dnumber & Dlocation \\ \hline 1 & Houston \\ \hline 4 & Stafford \\ \hline 5 & Bellaire \\ \hline 5 & Sugarland \\ \hline 5 & Houston \\ \hline \end{tabular} WORKS_ON PROJECT \begin{tabular}{|c|c|c|} \hline Essn & Pno & Hours \\ \hline 123456789 & 1 & 32.5 \\ \hline 123456789 & 2 & 7.5 \\ \hline 666881411 & 3 & 10.0 \\ \hline 453453453 & 1 & 20.0 \\ \hline 453453453 & 2 & 200 \\ \hline 333446666 & 2 & 10.0 \\ \hline 333445555 & 3 & 10.0 \\ \hline 333445555 & 10 & 10.0 \\ \hline 333445555 & 20 & 10.0 \\ \hline 999887777 & 30 & 30.0 \\ \hline 999887777 & 10 & 10.0 \\ \hline 987987987 & 10 & 35.0 \\ \hline 987987987 & 30 & 5.0 \\ \hline 987654321 & 30 & 20.0 \\ \hline 987654321 & 20 & 15.0 \\ \hline 888665555 & 20 & NULL \\ \hline \end{tabular} \begin{tabular}{|l|c|l|c|} \hline \multicolumn{1}{|c|}{ Pname } & Pnumber & Plocation & Dnum \\ \hline ProductX & 1 & Bellaire & 5 \\ \hline ProductY & 2 & Sugarland & 5 \\ \hline ProductZ & 3 & Houeton & 5 \\ \hline Computerization & 10 & Stafford & 4 \\ \hline Reuryanicatiun & 20 & Hnistnn & 1 \\ \hline Nowbonotito & 30 & Stafford & 4 \\ \hline \end{tabular} DEPENDENT \begin{tabular}{|l|l|c|c|l|} \hline Essn & Dependent name & Sex & Bdate & Relationship \\ \hline 333445555 & Alice & F & 19860405 & Daughter \\ \hline 333445555 & Theodore & M & 19831025 & Son \\ \hline 333445555 & duy & F & 19580503 & Spuuse \\ \hline 987654321 & Abner & M & 19420228 & Spouse \\ \hline 123456789 & Michael & M & 19880104 & Son \\ \hline 123456789 & Alice & F & 19881230 & Daughter \\ \hline 123456789 & Elizabeth & F & 19670505 & Spouse \\ \hline \end{tabular} Using the database schema above write and execute the queries below and paste the screenshots of the result of each query below each question: 1. List the first name and last name of employee(s) who are supervised by "James Bord". Hint: Use inner query with IN operator. If there are duplications in the resultant list then use DISTINCT keyword in the SELECT clause of the outer query. 2. How many projects do the departments other than the administration department control? List the department name and the number of projects it controls. Hint: Use inner query with IN or NOT IN operator 3. What is the total salary paid to the employees working on the "Computerization" project? Hint: Use innter query with IN operator 4. What is the average salary paid to the employees working on the projects other than the project "ProductZ"? Hint: Use innter query with IN or NOT IN operator 5. List the first name and last name of employee(s) who are supervised by supervisors "James Bord" or "Franklin Wong"? Hint: Use set operator UNION
