Consider the following schema: Projects(pid: integer, pName: string, status: Str

Question

Consider the following schema: Projects(pid: integer, pName: string, status: String budget: real) Departments(did: integer, dname: string, budget: real, location: string) Supports(pid: integer, did: integer, startDate: Date, endDate: Date) Write the following in SQL statements. 1. Find the average cost of projects. 2. Find the total cost of projects for each department from January 1, 2007 to December 31, 2011. 3. Find the departments whose budget is higher than the department of Information Technology. Note that Information Technology is a department name. 4. Find the ids of departments who

Explanation / Answer

Please Rate It with LifeSaver

1. SELECT pName, AVG(budget) FROM PROJECTS GROUP BY pName

2. SELECT D.dname, SUM(P.budget) FROM Projects P, Departments D, Supports S WHERE S.pid=P.pid AND S.did=D.did AND startDate>'01/01/2007' AND endDate<'31/31/2011' GROUP BY D.dname

3. SELECT dname FROM Departments WHERE budget > ( SELECT budget FROM Departments WHERE dname='Information Technology')

4. SELECT D.did FROM Departments D, Projects P,Supports S WHERE S.pid=P.pid AND S.did=D.did AND P.pName='Data Warehouse' OR P.pName='Data Center'

5. SELECT S.pid,COUNT(S.did) FROM Supports S GROUP BY S.pid HAVING COUNT(S.did)>1

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---