OFFICE (OFFICEID, ONAME, OADDRESS, OPHONENUM) ENGINEER (ENGINEERID, ENAME, ESALA

Question

OFFICE (OFFICEID, ONAME, OADDRESS, OPHONENUM)

ENGINEER (ENGINEERID, ENAME, ESALARY, OFFICEID)

PROJECT (PROJECTID, OFFICEID, PNAME, PCATEGORY, PBUDGET, PEXPENDITURE)

APPLICATION (APPID, ANAME, APRICE, ADATE, PROJECTID)

MAINTAIN (APPID, ENGINEERID)

RESEARCH (PROJECTID, ENGINEEERID)

Based on the above information, write the following queries in SQL statements on the provided MySQL database.

1, Find the APPID and ANAME of application(s) who are maintained by more than 2 engineers. The result should be sorted by APPID in ascending order. The ordering of the columns is shown below:

APPID                          ANAME

2, Find OFFICEID and ONAME of the office(s) of which the total budget of all the projects is larger than 40000. The result should be sorted by OFFICEID in ascending order. The ordering of the columns is shown below:

OFFICEID                          ONAME

3, The HEAD COUNT of a project is the number of engineers working on it (through the research relation only). Now, for every project category, calculate the average HEAD COUNT of the projects for that category. The result should be sorted by the PCATEGORY name. The ordering of the columns is shown below: (Note: You may assume that every project must have at least one engineer working on it and you may need to use AVG() in your query. Please refer to https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_avg)

PCATEGORY                     HEAD_COUNT

4, Find the ENGINEERID and ENAME of the project engineer who is working on the project(s) with the highest budget surplus (i.e. PBUDGET - PEXPENDITURE). The result should be sorted by ENGINEERID in ascending order. The ordering of the columns is shown below: (Note: You may need to perform arithmetic operation in your query. Please refer to https://dev.mysql.com/doc/refman/5.5/en/arithmetic-functions.html)

ENGINEERID                            ENAME

Explanation / Answer

Hello Please refre below queries

Please comment if any errors you face or needed more information

Query-1

select a.APPID,a.ANAME from APPLICATION a where a.APPID in
(select m.APPID from MAINTAIN m group by m.APPID having count(*) > 1) order by a.APPID asc

Query-2

select o.OFFICEID,o.ONAME from OFFICE o where o.OFFICEID in
(select p.OFFICEID from PROJECT p group by p.OFFICEID having SUM(p.PBUDGET)> 40000) order by o.OFFICEID

Query-3

select results.PCATEGORY , AVG(results.count) as HEAD_COUNT from

(select p.PCATEGORY as PCATEGORY , count(*) as count
from PROJECTp , RESEARCH r
where r.PROJECTID = p.PROJECTID group by p.PCATEGORY)results

Query-4

select e.ENGINEERID,e.ENAME from ENGINEER e where e.ENGINEERID =
(select e1.ENGINEERID from ENGINEER e1,PROJECT p,RESEARCH r
where e1.ENGINEERID =r.ENGINEERID and r.PROJECTID= p.PROJECTID
group by e1.ENGINEERID order by SUM(p.PBUDGET - p.PEXPENDITURE) LIMIT 1)

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.