CSC 240                                                                Spring 2007

04/20/027                                                             MidtermExam #2                                                Test Form A

Name:

Instructions:

    Answer all questions in the answer sheet provided. On Multiple Choice, choose the ONE BEST ANSWER.

    Remember to put the letter of your test form on the top of your answer sheet.

    Sample Movie Database design (blue) and contents (yellow) are provided for reference.

    Hand in Test, Answer Sheet, Sample Database, and Help Sheets, all with your name.

Multiple Choice

(4 points each)

1. Which of the following SQL commands never causes a change to the database

A) INSERT

B) UPDATE

C) SELECT

D) DELETE

E) All of the above

F) None of the above

2. Which of the following describes RESTRICT in the relational algebra?

A) returns all rows that meet some criteria such as an attribute being greater than some value

B) returns all rows that are in both of two tables

C) returns all rows that are in the first but not the second table

D) returns all rows that are in either one table or another (or both)

E) returns all rows in the table, with particular attributes chosen for display

3. The SELECT statement in SQL can carry out which of the following Relational Algebra operations?

A) RESTRICT

B) PROJECT

C) NATURAL JOIN

D) All of the above

E) None of the above

4. In SQL, which of the following are statements used for modifying the contents of the database?

A) REMOVE

B) ADD

C) UPDATE

D) All of the above

E) None of the above

True/False

(3 points each)

5. In the relational algebra, Cartesian Product is a fancy name for a “natural join.”

6. According to the relational algebra, a PROJECT of a table which does not include the primary key can result in fewer rows in the result than in the original table.

7. Relational algebra INTERSECTion is commutative. I.e. Table A INTERSECT Table B produces the same result as Table B INTERSECT Table A.

8. In SQL queries, computed values can be included in the results of the query.   

9. Since the result of an SQL query is a table, we can actually load data into a table as a result of a SELECT query.

Completion (fill in the blank) (mostly key terms (not all of which are a single word))

(4 points each)

10. In a(n) _________ (Relational Algebra operation) , two tables are joined, however, the unmatched pairs would be retained and the values for the unmatched other tables would be left blank or null.

11. According to the relational algebra, in order to do a union between two tables, the tables must be ________.

12. When a row is deleted, and the foreign key of row(s) in another table refers to the deleted row, one possible alternative for dealing with the situation is for the delete to be ________; that is the referring row(s) would be deleted as well.

13. In SQL, if we want to ensure that no duplicate rows are displayed, the key word ________ should be used.

14. In SQL queries, sometimes some sort of summary info is desired instead of a listing of a bunch of individual rows. Such ________ functions available include, SUM, MIN, MAX, etc.

Short Answer

(points as shown)

 (5 points)

15. What relational algebra operation is carried out by the following SQL statement?

                SELECT SECTION.*, PROFESSOR.*

                FROM SECTION,PROFESSOR;

SQL Short Answer

(7 points each)

16. Display the results of the following SQL queries (executed on the provided portion of the Movies sample database (yellow)  – View table can be found on (handwritten) page 8)

SELECT *

FROM view

WHERE rating >= 9;

17. What (very specifically!)  happens as a result of the following SQL statement? (executed on the provided portion of the Movies sample database (yellow)  – Actor table can be found on (handwritten) pages 4-5)

UPDATE Actor

SET PlaceOfBirth = ‘Scotland’

WHERE PlaceOfBirth LIKE ‘%Scotland’;

SQL – Write SQL statements to handle the below requests – based on the attached Movies sample database design (blue). The database contents can be used to illustrate the data if necessary)

(points as shown)

                (6 points)

18. Show for each country, the number of customers in that country.

(9 points)

19. Show the Title, MPAA Rating, and Runtime for the Movie that won best picture for 1973 (Best Picture attribute has a value of 1 for yes and 0 for no)

(15 points)

20. Show Movie Title, Year Released, MPAA Rating, and box office revenue, for all movies released since 1970 matching the keyword ‘Sequel’, sorted by Year (descending).