University of California, Berkeley College of Engineering Computer Science Division – EECS Spring 2006 Prof. Michael J. Franklin MIDTERM II – Practice Questions CS 186 Introduction to Database Systems NAME: ____________________________ STUDENT ID:__________________ IMPORTANT: Circle the last two letters of your class account: cs186 a b c d e f g h i j k l m n o p q r s t u v w x y z a b c d e f g h i j k l m n o p q r s t u v w x y z DISCUSSION SECTION DAY & TIME:____________ TA NAME: ___________ This is a closed book examination – but you are allowed one 8.5” x 11” sheet of notes (double sided). You should answer as many questions as possible. Partial credit will be given where appropriate. There are 100 points in all. You should read all of the questions before starting the exam, as some of the questions are substantially more time-consuming than others. Write all of your answers directly on this paper. Be sure to clearly indicate your final answer for each question. Also, be sure to state any assumptions that you are making in your answers. GOOD LUCK!!! Problem Possible Score 1. SQL 27 2. Join Cost Calculations 9 3. Query Optimization 24 4. ER Diagrams 25 5. Functional Dependencies 15 TOTAL 100 NOTE: The schedule this semester is somewhat different than the one that this exam was given. This semester, we will be concentrating primarily on SQL, cost estimation, query operators, and query optimization, as well as material from homeworks 2 and 3. These questions from an old exam are provided just as a study aid. We will post (some) answers before the midterm. MJF 3/18/06SID:____________________ CS 186 Midterm II November 10, 2004 Page 2 of 11 Question 1 –SQL [6 parts, 27 points total] For parts a-d, consider the following schema (primary keys are underlined): Student (sname, sid, gpa, level, deptno) Course (cno, cname, deptno, units) Dept (dname, deptno) Takes (sid, cno) a) [7 points] Write a SQL query that returns the names (i.e., snames) of students who have taken more courses outside their department than inside their department. For this question, you can assume that all students in the database have taken at least one course inside their department. (note: you should do scratch work elsewhere and just put your final answer here!) b) [3 points] Which of the following queries returns the department numbers of those departments for which there are no courses being offered? More than one choice may be correct. A) SELECT D.deptno FROM Dept D, Course C WHERE D.deptno NOT EQUAL C.deptno; B) SELECT C.deptno, COUNT(C.deptno) FROM Course C GROUP BY C.deptno HAVING COUNT (C.Deptno) = NULL; C) SELECT C.deptno FROM Course C WHERE C.deptno NOT IN (SELECT * FROM Dept); D) SELECT D.deptno FROM Dept D WHERE NOT EXISTS (SELECT * FROM Course C WHERE C.deptno = D.deptno); E) None of the aboveSID:____________________ CS 186 Midterm II November 10, 2004 Page 3 of 11 c) [3 points] Which of the following queries returns the id of the student with the highest GPA? More than one choice may be correct. A) SELECT S.sid FROM Students S WHERE S.gpa = MAX(S.gpa); B) SELECT S.sid, MAX(S.gpa); FROM Students S GROUP by S.gpa C) SELECT S.sid FROM Student S WHERE S.gpa > ALL (SELECT S.gpa FROM Student S); D) SELECT S.sid FROM Student S Where S.gpa = (SELECT MAX(S.gpa) FROM Student S); E) None of the above d) [3 points] Which of the following queries returns the sid of the students and the total units they are taking? More than one choice may be correct. A) SELECT S.sid, sum(C.units) FROM Student S, Takes T, Course C GROUP BY S.sid HAVING S.sid = T.sid AND T.cno = C.cno; B) SELECT S.sid, sum(C.units) FROM Student S, Takes T, Course C Where S.sid = T.sid AND T.cno = C.cno; GROUP BY S.sid C) SELECT S.sid, Temp.Sum1 FROM Student S, (SELECT sum(C.units) AS Sum1 FROM Takes T, Course C WHERE T.sid = S.sid AND T.cno = C.cno) AS Temp; D) SELECT S.sid, sum(C.units) FROM Student S, Takes T, Course C WHERE S.sid = T.sid AND T.cno = C.cno; E) None of the aboveSID:____________________ CS 186 Midterm II November 10, 2004 Page 4 of 11 e) [3 points] For the following schema: Athletes(name, country, sport, age, height, weight) Which of the following SQL queries reflects the English query statement: "For each country, find the average height of weightlifters, qualifying only those countries that have weightlifters with minimum weight of 160 pounds." More than one choice may be correct. A) SELECT country, avg(height) FROM Atheletes WHERE sport = "weightlifting" GROUP BY country, height, weight HAVING min(weight) >= 160; B) SELECT country, avg(height) FROM Atheletes GROUP BY country, sport HAVING min(weight) >= 160 AND sport = "weightlifting"; C) SELECT country, avg(height) FROM Atheletes WHERE sport ="weightlifting" GROUP BY country HAVING min(weight) >= 160; D) SELECT country, avg(height) FROM Atheletes WHERE sport = "weightlifting" AND min(weight) >= 160 GROUP BY country, weight; E) SELECT country, avg(height) FROM Atheletes WHERE sport ="weightlifting" GROUP BY country, height HAVING min(weight) >= 160; f) [8 points] For the schema in part (e), write a SQL query that returns for each sport, the name of the sport, the country that has the most athletes who play that sport, and the number of athletes of that country that play that sport. (note: you should do scratch work elsewhere and just put your final answer here!)SID:____________________ CS 186 Midterm II November 10, 2004 Page 5 of 11 Question 2 – Join Costs [3parts, 9 points total] For this question, you will consider the I/O cost of operations on two tables of a database. The database has the following schema (note, this schema is slightly different than the schema used in question 1). Students(sid, name, address, GPA) EnrolledIn(sid, classid, semester, year) Assume that tuples are of fixed size. There are 10 Students tuples per page and 200 EnrolledIn tuples per page. Also assume that there are 1000 pages in the Students relation, and 500 pages in the EnrolledIn relation. The data is unsorted, and tuples are distributed evenly throughout the database. For the following join strategies, give the I/O cost. Assume 52 pages in the buffer, and that no pages are
View Full Document
Unlocking...