CPS 510 Exam Review Questions with Correct Answers – Database Systems Study Guide – SQL, RA, Normalization, Indexing, Transactions

CPS 510 EXAM REVIEW QUESTIONS WITH
CORRECT ANSWERS LATEST UPDATE NEW
MODIFIED


QUESTION 1 - SECTION 1

SQL ESSENTIALS

What is the effect of the `DROP TABLE` command on a table and its data?

Choices:

A) It deletes the data but keeps the table structure.

B) It removes the table definition from the catalog and deletes all rows.

C) It marks the table as archived but retains the data.

D) It deletes the specific rows matching a condition. --CORRECT ANSWER--
Answer: B) It removes the table definition from the catalog and deletes all rows.

[cite_start]Explanation: `DROP TABLE` removes the table structure entirely
from the database schema, along with all data contained within it[cite: 2619].




QUESTION 2 - SECTION 1

A correlated subquery is best described as:

Choices:

A) A subquery that executes once for the entire outer query.


pg. 1

,B) A subquery that runs independently of the outer query.

C) A subquery that references a column from the outer query and executes once
for each row of the outer query.

D) A subquery found only in the `FROM` clause. --CORRECT ANSWER--
Answer: C) A subquery that references a column from the outer query and
executes once for each row of the outer query.

[cite_start]Explanation: A correlated subquery depends on values from the outer
query row currently being processed[cite: 2033].



QUESTION 3 - SECTION 1

When using the `UPDATE` statement with a subquery, which logic correctly
updates the `status` of students to 'Honors' if their GPA is higher than the
average GPA of the 'CS' department?

Choices:

A) `UPDATE Student SET status = 'Honors' WHERE gpa > (SELECT
AVG(gpa) FROM Student WHERE major = 'CS')`

B) `UPDATE Student SET status = 'Honors' WHERE gpa > AVG(gpa) AND
major = 'CS'`

C) `SELECT status = 'Honors' FROM Student WHERE gpa > (SELECT
AVG(gpa) FROM Student)`

D) `UPDATE Student SET status = 'Honors' HAVING gpa > (SELECT
AVG(gpa) FROM Student WHERE major = 'CS')` --CORRECT ANSWER--
Answer: A) `UPDATE Student SET status = 'Honors' WHERE gpa > (SELECT
AVG(gpa) FROM Student WHERE major = 'CS')`



pg. 2

,Explanation: An `UPDATE` statement uses a `WHERE` clause to identify
rows. [cite_start]The condition compares the individual `gpa` against the scalar
result of the subquery calculating the average[cite: 2033].



QUESTION 4 - SECTION 1

Which of the following SQL statements allows you to create a virtual table that
does not store data itself but displays data from base tables?

Choices:

A) CREATE TABLE AS SELECT

B) CREATE VIEW AS SELECT

C) CREATE INDEX AS SELECT

D) ALTER TABLE ADD VIEW --CORRECT ANSWER--Answer: B)
CREATE VIEW AS SELECT

Explanation: A view is a virtual table defined by a query. [cite_start]It does not
store data physically (unless materialized) but presents data derived from base
tables[cite: 2035].



QUESTION 5 - SECTION 1

Which SQL aggregate function ignores NULL values by default?

Choices:

A) `COUNT(*)`

B) `AVG(column_name)`

C) `ISNULL(column_name)`


pg. 3

, D) `COALESCE(column_name)` --CORRECT ANSWER--Answer: B)
`AVG(column_name)`

Explanation: Aggregate functions like `AVG`, `SUM`, and `COUNT(column)`
ignore NULL values in their calculation. [cite_start]`COUNT(*)` counts all
rows regardless of NULLs[cite: 2033].



QUESTION 6 - SECTION 1

When creating a table, which constraint ensures that a column cannot contain
duplicate values?

Choices:

A) `NOT NULL`

B) `FOREIGN KEY`

C) `UNIQUE`

D) `CHECK` --CORRECT ANSWER--Answer: C) `UNIQUE`

Explanation: The `UNIQUE` constraint ensures that all values in a column are
distinct from one another.



QUESTION 7 - SECTION 1

Consider the table `Student(id, name, gpa, major)`. You want to find the number
of students in each major, but only for majors where the average GPA is greater
than 3.0. Which query is correct?

Choices:

A) `SELECT major, COUNT(*) FROM Student WHERE AVG(gpa) > 3.0
GROUP BY major`

pg. 4