Fall 2016 pm CS Final (10:15 am open book, handouts and notes EM DEPARTMENT DEPT
ID: 3581764 • Letter: F
Question
Fall 2016 pm CS Final (10:15 am open book, handouts and notes EM DEPARTMENT DEPT LOCATIONS PROJECT WORKs ON l Pro Hours DEPENDENT Relationship Lependent name ser Bdata 15 points) shown above, write an sQL query for the foll the company database schema operation (which is not a standard SQL th not use "C include an SQL query in query. Also, do not use a view, and do not relation). clause (in order to use its result as if it is another Retrieve the name of every department whose number of department l same as the number of locations of the projects controlled by that dep Suppose that a department has three locations (such as Cincinnati, Da Columbus). If all the projects controlled by this department are loca locations (such as Dayton, Chicago, and Seattle), then the departmen department has one or more locations, but each project has a single loca ts can be at the same location. In DEPT LOCATIONS relation, Dio ents the location of the department. In the PROJECT relation, Dnum ents the Dnumber of the department that controls the project, and PI ents the single location of the project.Explanation / Answer
Select d.Dname from Department d, Dept_Locations dl, Project p where d.Dnumber=dl.Dnumber and dl.Dnumber=p.Dnum and p.Dnum=d.Dnumber having count(dl.Dlocation)=count(p.Plocation) group by d.Dname;
Explanation:
d, dl and p are the variables used for representing tables department, dept_locations and project respectively.
1. As given in the question, we have to get the column of department name corresponding to the location. Department table have department name as Dname and department number as Dnumber but location is not there in this table.
2. Department number as Dnumber and department location as Dlocation are present in table dept_location. So we linked department and dept_location by using d.Dnumber = dl.Dnumber.
3. We have to compare the number of dept locations to number of project locations having same deptartment number. So we linked table dept_locations and project using dl.Dnumber = p.Dnum.
4. The department name is present in only department table so it's necessary to link it with both tables. So we linked table department and project using p.Dnum = d.Dnumber.
5. In sql, count is used for counting any number. As we have to compare the number of project locations to number of department locations so we used count(p.Plocation) = count(dl.Dlocation).
6. Group by is used so that we can't have the same department name twice or more.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.