Question: 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 &

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 \\$

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

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

You are auditing general cash for the Pittsburgh Supply Company for the fiscal year ended July 31, 2019. The client has not prepared the July 31 bank reconciliation. After a brief discussion with the...

plz solve all of them or let someone else do them 1. Prove by example the following relational algebra property: (5 points) I((R))=(R) as long as contains the attributes in You may use the COMPANY...

1. Prove by example the following relational algebra property: ( (R))= (R) as long as contains the attributes in You may use the COMPANY schema or another other schemas of your choice. Figure 5.6 One...

7. Produce a relation showing the employee first name, name of his/her dependent, and relationship to the employee. (0.75 point) 8. Find the first and last name of all employees whose salary is above...

Database HW can you solve all questions if you can't, please leave it for someone else to solve them all draw if it's needed 1. Prove by example the following relational algebra property: (5 points)...

1. Prove by example the following relational algebra property: (5 points) ((R))=(R) as long as contains the attributes in You may use the COMPANY schema or another other schema of your choice. Figure...

acording to the company schema pliz i want a solution coming from the (company scheme) please do not give a solution in the app already because I already seen them GEIT 3341: Database I 5\% Bonus...

database draw if it's needed plz solve q 1 according to the company schema GEIT 3341: Database I 5\% Bonus Assignment Due date: Thursday December 22, 2022 1. Prove by example the following relational...

Supposed that each of the following Update operations is applied directly to the database state shown in the Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the...

A: find the area between the line x +y= 2, the x- axis and the curve y? = x. B: use the definition of derivative to find for y = (x? + 2x).

A uniform solid disk and a uniform hoop are placed side by side at the top of an incline of height h. If they are released from rest and roll without slipping, which object reaches the bottom first?...

The nominal rate is really the true rate. Group of answer choices True False

CT Corp Comprehensive Question Canadian Tire Corporation, Limited ( Canadian Tire ) is a family of companies that includes a retail segment and a financial services division, among others. The retail...

Individually design a job specifi cation for a university lecturer in marketing, then compare results in groups of fi ve. Produce a fi nal version taking the best parts of each of your efforts.

Set out some of the opportunities presented to Sarah in the Meteor Telecoms case study. How would she best take advantage of them?

It is often said that competencies for a manufacturing environment are different to those for a service environment. Draw up a list of fi ve differing competencies for each sector and three...