Chapter 7: Advanced SQL - Comprehensive Exam

Exam Information

Total Points: 100

Time: Self-paced

Instructions: Answer all questions. Some questions are marked as "Past Exam" from previous exams. For written questions, include key concepts for full credit.

Section 1: Multiple Choice Questions (40 points)

Question 1

PAST EXAM 4 points

Which of the following SQL queries will return sailors who have reserved boat number 103?

Sailors(sid, sname, rating, age) Reserves(sid, bid, day) Boats(bid, bname, color)

SELECT sname FROM Sailors WHERE sid = 103;

SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid = R.sid AND R.bid = 103;

SELECT sname FROM Sailors WHERE bid = 103;

SELECT * FROM Reserves WHERE sid = 103;

Question 2

PAST EXAM 4 points

What is the difference between WHERE and HAVING clauses in SQL?

WHERE filters tuples before grouping, HAVING filters groups after grouping

WHERE and HAVING are exactly the same

WHERE is used with aggregates, HAVING is not

HAVING can only be used with COUNT function

Question 3

4 points

Which SQL query returns the name and age of the youngest sailor?

Sailors(sid, sname, rating, age)

SELECT sname, age FROM Sailors WHERE age = MIN(age);

SELECT sname, age FROM Sailors WHERE age <= ALL(SELECT age FROM Sailors);

SELECT MIN(age) FROM Sailors;

SELECT sname, age FROM Sailors ORDER BY age LIMIT 1;

Question 4

PAST EXAM 4 points

What is the result of a LEFT OUTER JOIN?

Returns only matching tuples from both tables

Returns all tuples from the left table, with NULLs for non-matching right table tuples

Returns all tuples from the right table only

Returns the Cartesian product of both tables

Question 5

4 points

Which aggregate function should be used to count unique values in a column?

COUNT(*)

COUNT(DISTINCT column_name)

SUM(DISTINCT column_name)

UNIQUE(column_name)

Question 6

PAST EXAM 4 points

What does the EXISTS operator do in a nested query?

Returns TRUE if the subquery returns at least one tuple

Returns TRUE only if the subquery returns exactly one tuple

Counts the number of tuples in the subquery

Checks if a value exists in a list

Question 7

4 points

What is the purpose of a VIEW in SQL?

To permanently store query results

To create a virtual table based on a query that doesn't store data physically

To delete tables from the database

To create indexes on tables

Question 8

4 points

Which of the following is a correlated nested query?

SELECT * FROM Employee WHERE Dno IN (SELECT Dnumber FROM Department);

SELECT * FROM Employee E WHERE Salary > (SELECT AVG(Salary) FROM Employee WHERE Dno = E.Dno);

SELECT MAX(Salary) FROM Employee;

SELECT * FROM Employee WHERE Salary > 50000;

Question 9

PAST EXAM 4 points

What is NULL in SQL?

A special value representing unknown, unavailable, or not applicable data

The number zero

An empty string

A boolean false value

Question 10

4 points

Which operator is used to check if a value is NULL?

= NULL

IS NULL

== NULL

NULL?

Section 2: SQL Query Writing (30 points)

Database Schema for Questions 11-14:

Student(StudentID, Name, Major, GPA) Course(CourseID, Title, Credits, DeptName) Enrollment(StudentID, CourseID, Grade, Semester)

Question 11

PAST EXAM 8 points

Write an SQL query to find the names of all students who have a GPA greater than 3.5 and are majoring in 'CS'. Use proper SQL syntax.

Hint: Use SELECT, FROM, WHERE with AND condition

Question 12

PAST EXAM 8 points

Write an SQL query to find the average GPA for each major. Display the major name and average GPA. Order results by average GPA in descending order.

Hint: Use GROUP BY with AVG() aggregate function and ORDER BY

Question 13

7 points

Write an SQL query to find all students enrolled in 'CS340' course using a JOIN operation. Display student name and course title.

Hint: Join Student, Enrollment, and Course tables

Question 14

7 points

Write an SQL query to find majors that have more than 5 students with GPA above 3.0. Display the major name and count of students.

Hint: Use WHERE to filter GPA, GROUP BY major, and HAVING for count condition

Section 3: Short Answer Questions (30 points)

Question 15

PAST EXAM 10 points

Explain the difference between INNER JOIN and LEFT OUTER JOIN with an example. In your answer, describe when each should be used and what happens to non-matching tuples.

Key concepts to include: matching tuples, NULL values, use cases, non-matching behavior

Question 16

PAST EXAM 10 points

What are aggregate functions in SQL? List at least 4 aggregate functions and explain what each one does. Provide an example scenario where you would use GROUP BY with an aggregate function.

Key concepts: COUNT, SUM, AVG, MAX, MIN, GROUP BY, examples

Question 17

10 points

Explain what a VIEW is in SQL. Discuss the difference between materialized and non-materialized views. What are the advantages of using views in database design?

Key concepts: virtual table, materialized vs non-materialized, security, simplification, advantages

Make sure you've answered all questions before submitting!