2. [9 points] There are six tables describing a company, describing employees, departments, buildings, which department(s)...

Transcribed Image Text:

2. [9 points] There are six tables describing a company, describing employees, departments, buildings, which department(s) an employee works in (and a percentage of the time for each), department managers (possibly more than one per department), and in which building an employee works (an employee may have more than one office). The primary key of each table is the attribute(s) in capitals. Other attributes are not necessarily unique. EID 001 002 003 004 005 EMP-100,000 tuples, 1,000 pages EName Jane $124,000 $32,000 $99,000 Joe $55,000 Jenny $51,000 EID values range from 1 to 100,000 Jim John Salary Start Date End Date BUILDING-2,000 tuples, 10 pages Address BID 201 202 203 DID 101 102 103 3/1/93 null null 2/29/96 12/12/98 null 2/2/92 null 5/5/95 null BName ATC 1600 Ampitheatre CCC 500 Crittenden MFB 123 Shoreline BID values range from 1 to 2,000 DEPT-1,000 tuples, 5 pages Annual Budget $1,001,000 $500,000 $2,000,000 DName Research Development Sales DID values range from 1 to 1000 IN DEPT-110,000 tuples, 550 pages EID Percent_Time 100 100 60 40 100 100 001 002 003 003 004 005 IN BUILDING -110.000 tuples, 550 pages EID BID 001 201 002 201 003 003 DID 101 102 101 102 103 103 004 005 202 203 202 203 MANAGES DEPT-800 tuples, 4 pages EID DID 003 003 001 101 102 103 (1) Which of the following queries finds the names of buildings where more than 50 employees work? In this box list which of a, b, c, d, e is correct (you may list multiple answers) a. b. C. d. e. SELECT Bname FROM IN BUILDING GROUP BY BID WHERE Count(*) > 50 SELECT Bname FROM BUILDING WHERE BID IN (SELECT BID FROM In Building GROUP BY BID HAVING Count(*) > 50) SELECT Bname FROM Building B, In Building I WHERE B.BID = I.BID GROUP BY B.BID HAVING Count(*) > 50 SELECT Bname FROM Building B WHERE 50 < (SELECT Count(*) FROM In Building I WHERE I.BID = B.BID) None of the above 2. [9 points] There are six tables describing a company, describing employees, departments, buildings, which department(s) an employee works in (and a percentage of the time for each), department managers (possibly more than one per department), and in which building an employee works (an employee may have more than one office). The primary key of each table is the attribute(s) in capitals. Other attributes are not necessarily unique. EID 001 002 003 004 005 EMP-100,000 tuples, 1,000 pages EName Jane $124,000 $32,000 $99,000 Joe $55,000 Jenny $51,000 EID values range from 1 to 100,000 Jim John Salary Start Date End Date BUILDING-2,000 tuples, 10 pages Address BID 201 202 203 DID 101 102 103 3/1/93 null null 2/29/96 12/12/98 null 2/2/92 null 5/5/95 null BName ATC 1600 Ampitheatre CCC 500 Crittenden MFB 123 Shoreline BID values range from 1 to 2,000 DEPT-1,000 tuples, 5 pages Annual Budget $1,001,000 $500,000 $2,000,000 DName Research Development Sales DID values range from 1 to 1000 IN DEPT-110,000 tuples, 550 pages EID Percent_Time 100 100 60 40 100 100 001 002 003 003 004 005 IN BUILDING -110.000 tuples, 550 pages EID BID 001 201 002 201 003 003 DID 101 102 101 102 103 103 004 005 202 203 202 203 MANAGES DEPT-800 tuples, 4 pages EID DID 003 003 001 101 102 103 (1) Which of the following queries finds the names of buildings where more than 50 employees work? In this box list which of a, b, c, d, e is correct (you may list multiple answers) a. b. C. d. e. SELECT Bname FROM IN BUILDING GROUP BY BID WHERE Count(*) > 50 SELECT Bname FROM BUILDING WHERE BID IN (SELECT BID FROM In Building GROUP BY BID HAVING Count(*) > 50) SELECT Bname FROM Building B, In Building I WHERE B.BID = I.BID GROUP BY B.BID HAVING Count(*) > 50 SELECT Bname FROM Building B WHERE 50 < (SELECT Count(*) FROM In Building I WHERE I.BID = B.BID) None of the above

Answer rating: 100% (QA)

a SELECT Bname FROM IN BUILDING GROUP BY BID HAVING COUNT 50 ... View the full answer