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 account:

1, Find the ENGINEERID and ENAME of the engineer(s) who are currently working on every project under category “Web”. The result should be sorted by the ENGINEERID of the engineer in ascending order. The ordering of the columns is shown below:

ENGINEERID                    ENAME

2, Find the ENAME of the engineer(s) who have the highest salary at the office named “Database Group”. The result should be sorted by ENAME in descending order.

3, Find the ANAME and APRICE of the cheapest 5 applications maintained by engineer named ‘Jane Xiu’. The result should be sorted by APRICE in descending order. The ordering of the columns is shown below: (Assumption: The price of each application maintained by ‘Jane Xiu’ is unique.)

ANAME                         APRICE

4, 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

5, 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

6, 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

7, 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

create table office (officeid int Identity(1,1) not null, ONAME varchar(20), OADDRESS varchar(20), OPHONENUM varchar(20),PRIMARY KEY(officeid))

create table ENGINEER (ENGINEERID int Identity(1,1) not null, ENAME varchar(20), ESALARY int, OFFICEID int FOREIGN KEY references office(officeid),PRIMARY KEY(ENGINEERID))

create table PROJECT (PROJECTID int Identity(1,1) not null, OFFICEID int FOREIGN KEY references office(officeid), PNAME varchar(20), PCATEGORY varchar(20), PBUDGET int, PEXPENDITURE varchar(20),PRIMARY KEY(PROJECTID))

create table APPLICATION (APPID int Identity(1,1) not null, ANAME varchar(20), APRICE int, ADATE date, PROJECTID int FOREIGN KEY references PROJECT(PROJECTID),PRIMARY KEY(APPID))

create table MAINTAIN (APPID int FOREIGN KEY references APPLICATION(APPID), ENGINEERID int FOREIGN KEY references ENGINEER(ENGINEERID))

create table RESEARCH (PROJECTID int FOREIGN KEY references PROJECT(PROJECTID), ENGINEEERID int FOREIGN KEY references ENGINEER(ENGINEERID))

1)select ENGINEERID,ENAME from ENGINEER JOIN office on ENGINEER.OFFICEID=office.officeid JOIN PROJECT ON office.officeid=PROJECT.OFFICEID where PCATEGORY='web' ORDER BY ENGINEERID ASC

3)select ANAME,APRICE from ENGINEER JOIN MAINTAIN on ENGINEER.ENGINEERID=MAINTAIN.ENGINEERID JOIN APPLICATION on MAINTAIN.APPID=APPLICATION.APPID where ENAME='Jane Xiu' Order by APRICE asc

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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