Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6. - List the managers who are controlling more than 2 projects. - List the names of employees all of whose dependents were born before 1990. - Retrieve the average salary of employees who work totally no less than 30 hours on projects. EMPLOYEEFname Minit Lname SsnAddressSexSuper_ssn DnoBdateSalaryDEPARTMENTDname Dnumber Mgr_ssn Mgr_start_dateDEPT LOCATIONSsDnumber DlocationPROJECTPname Pnumber PlocationDnumWORKS_ONEssnPnoHoursFigure 5.5Schema diagram for theCOMPANY relationalDEPENDENTEssnDependent_name SexBdateRelationshipdatabase schema. Figure 5.6One possible database state for the COMPANY relational database schemaEMPLOYEEFnameSuper san DnD123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555M 40000 888665555MinitLnameSanBdateAddrossSar SalaryJohnB.Smith333445555 1955-12-08 638 Voes, Houston, TXZelaya 999887ITI 1968-01-19 3321 Castle, Spring, TXWallace 987654321 1941-06-20 291 Berry, Bellaire, TXFranklinWongAlicia25000 987654321JennilerSF43000 888665555Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX MJoyceEnglish 453453453 1972-07-31 5631 Rice, Houston, TXV JabbarE Borg38000 333445565F 25000 333445555M 25000 987654321A5Ahmad987987987 1969-03-29 980 Dallas, Houston, TXJames888665555 1937-11-10 450 Stone, Houston, TXM 55000 NULL1DEPT LOCATIONSDlocaionDEPARTMENTDramber1DrumberMgr start dateDnameMgr_sanRosearch53334455551988-05-22HoustonAdministration49876543211995-01-014StaffordHoadquartors8886655551981-06-19Belaire5SugarlandHouston5WORKS ONPROJECTEssnPnoHoursPhamePnumberPlocationDnum1234567891325ProductXBellaire5123456789275ProducfY2Sugarland56668844443.40.0Product3Hounton545345345320.0ComputorizationRoorganiration10Stafford4453453453220.020Houston1333445555210.0Newbenelits30Stafford4333445555310.03334455551010.0DEPENDENT3334455552010.0EssnDependent name SoxRelationshp1986 04-05 DaughterBdate9998877773030.0333445555AliceF9998877771010.0333445555TheodoreM1983-10-25Son1035.01958-05-03 Spoune1942-02-28 Spouse333445555Joy305.0987654321AbnerM9876543213020.0123456789MichaelM1988-01-04 Son9876543212015.0123456789AliceF1988-12-30Daughter88866555520NULL123456789ElizabethF1967-05-05Spouse

The given problem is related to database SQL queries where the database and sample data is given. The requirement is to make the SQL queries for the given 3 problems.LOGIC: The query will use join in table department and project based on dnumber and dnum in the tables. Mgr_ssn will be selected and grouped Then a having clause will be used on count(*) per manager and only records where count is more than 2 will be returned. SQL: Select department.mgr_ssn from department join project on department.dnumber = project.Dnumgroup by department.mgr_ssn having count(*)>2; OUTPUT: **Just a sample, please run on actual database to get sample records; LOGIC: The table employee and dependent will be used in join based on SSN and ESSN in both tables. concat function is used to join the 3 names of employee as full name. group by employee.ssn is performed so that the same employee is not returned multiple times if he has more than 1 dependent matching the condition. YEAR(date) will give the year of birth. SQL: Select concat(employee.Fname, ' ', employee.Minit, ' ', employee.Lname) as EmployeeNamefrom Employee join Dependent on Employee.ssn = Dependent.Essn where YEAR(Dependent.BDATE) <1990group by Employee.SSN; OUTPUT: Just a sample to understand the output.LOGIC: The employee table is joined with works_on table based on SSN and ESSN. AVG returns the average salary. SUM returns the sum of hours. SQL: Select AVG(Employee.Salary) as AVG_SALARY from Employee join works_on on Employee.SSN = works_on.ESSN having SUM(works_on.hours) >=30 OUTPUT: The output is appended in each query to make it easy to understand.