Exam 1 Review

Northeastern University

Mark Fontenot, PhD

Exam Format

• Quick Answer Questions (MC, T/F, etc.)
• Short Answer Questions
• Determine the Output
• Fill in Blank
• etc.
• Query Writing
• In Relational Algebra
• In SQL

2

Exam Format

• 60 minutes to complete - 5 mins handout/retrieve time
• There will be multiple versions of the exam
• Please bring Pencil and Functional Eraser.
• They don’t have to be connected.
• Don’t expect to be able to borrow from someone.
• Early arrivers, sit near center of room.
• See other Slide Deck for Cheat Sheet Rules

3

Sample Quick Answer Questions

1. The/A ______________ is a subset of the/a _______________ of the domains of a set of _______________.

a. DBMS, relational model, tables

b. cartesian product, relation, attributes

c. relation, DBMS, models

d. relation, cartesian product, attributes

​

4

Sample Quick Answer Questions

2. The result of a select operation (in relational algebra) is a relation.

a. True

b. False

​

​

5

Sample Quick Answer Questions

3. Given the hypothetical SQL select statement

SELECT x FROM y WHERE z;

the project relational operator performs the same function as _____ in the SQL statement above.

a. x

b. y

c. z

d. none of the above

6

Sample Quick Answer Questions

4. The _____ operator in relational algebra allows for filtering rows.

a. select

b. project

c. join

d. natural join

7

Sample Quick Answer Questions

5. The Cartesian product is the result of a natural join between two tables that do not have any attribute names in common.

​

a. True

b. False

8

Sample Quick Answer Questions

6. Which of the following characters is the wildcard character for returning all attributes in a SQL query?

​

a. !

b. ?

c. *

d. %

9

Short Answer Examples

1. What is the output of the following relational algebra expression?

10

Courses

Grades

Students

Short Answer Examples

2. What is the output of the following relational algebra expression?

11

Courses

Grades

Students

Short Answer Examples

3. Assume Students relation contains 10 rows and Assume Grades relation contains 50 rows. How many tuples are in the cartesian product of Students with Grades?

12

Courses

Students

Grades

Short Answer Examples

4. How many attributes in the natural join of Courses with Grades?

13

Courses

Grades

Students

Short Answer Examples

Assume there were 20 rows in the Students relation and 40 rows in the Grades relation. Further, assume that 4 students have taken no classes (IOW, they don’t appear in the Grades relation). How many tuples in � Students ⨝ Grades?

​

14

Courses

Grades

Students

Short Answer Examples

5. Convert the following Relational Algebra expression to SQL.

15

Courses

Grades

Students

Short Answer Examples

Assume there were 20 rows in the Students table and 40 rows in the Grades table. Further, assume that 4 students have taken no classes. How many rows would be in the result of the following query?

16

Courses

Grades

Students

SELECT *

FROM Students s LEFT OUTER JOIN Grades g

ON s.S_ID = g.S_ID;

Use Northwind to Answer the Following Questions.

17

SQL Time

18

1. How many customers are there in the following countries: Germany, France, Belgium? Order the output alphabetically by country name.

SQL Time

19

2. What is the total value of Northwind’s current product inventory?

SQL Time

20

3. For which customers do we ship to 20 or more countries? The output should be an alphabetic list of the customers’ Company Names.

SQL Time

21

4. Northwind is about to do an inventory audit where the quantity of products on the shelves are counted and compared to the database. The warehouse is organized by Supplier and then by Product ID (from left to right on the shelves). Provide a list of all products to include the ProductID, ProductName, Supplier ID, and current stock organized in a way most efficient for the inventory audit.