CPS 510 EXAM SCRIPT EXAMS SET VIEW
AHEAD EXAM 2026 100% CORRECT.
⫸ 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')`.
Answer: Answer: A) `UPDATE Student SET status = 'Honors'
WHERE gpa > (SELECT AVG(gpa) FROM Student WHERE major
= 'CS')`
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
,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.. 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 5 - SECTION 1
Which SQL aggregate function ignores NULL values by default?
Choices:
A) `COUNT(*)`
B) `AVG(column_name)`
C) `ISNULL(column_name)`
D) `COALESCE(column_name)`. 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`. Answer: Answer: C) `UNIQUE`
Explanation: The `UNIQUE` constraint ensures that all values in a
column are distinct from one another.
⫸ QUESTION 7 - SECTION 1
A correlated subquery is best described as:
Choices:
A) A subquery that executes once for the entire outer query.
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.. 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 8 - SECTION 1
Which keyword is used to add a new column to an existing table?
Choices:
A) `UPDATE TABLE ... ADD`
B) `INSERT INTO ... COLUMN`
C) `ALTER TABLE ... ADD`
D) `MODIFY TABLE ... ADD`. Answer: Answer: C) `ALTER
TABLE ... ADD`
[cite_start]Explanation: The `ALTER TABLE` statement is used to
modify the structure of an existing table, such as adding or dropping
columns[cite: 2639].
⫸ ### **SECTION 2 - RELATIONAL ALGEBRA AND QBE**
QUESTION 9 - SECTION 2
In Relational Algebra, which operator is used to select specific rows
from a relation based on a condition?
Choices:
A) Projection ($\pi$)
B) Selection ($\sigma$)
C) Cartesian Product ($\times$)
D) Join ($\bowtie$). Answer: Answer: B) Selection ($\sigma$)
[cite_start]Explanation: The selection operation ($\sigma$) is used to
choose a subset of tuples (rows) from a relation that satisfy a specific
selection condition[cite: 3556].
AHEAD EXAM 2026 100% CORRECT.
⫸ 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')`.
Answer: Answer: A) `UPDATE Student SET status = 'Honors'
WHERE gpa > (SELECT AVG(gpa) FROM Student WHERE major
= 'CS')`
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
,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.. 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 5 - SECTION 1
Which SQL aggregate function ignores NULL values by default?
Choices:
A) `COUNT(*)`
B) `AVG(column_name)`
C) `ISNULL(column_name)`
D) `COALESCE(column_name)`. 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`. Answer: Answer: C) `UNIQUE`
Explanation: The `UNIQUE` constraint ensures that all values in a
column are distinct from one another.
⫸ QUESTION 7 - SECTION 1
A correlated subquery is best described as:
Choices:
A) A subquery that executes once for the entire outer query.
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.. 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 8 - SECTION 1
Which keyword is used to add a new column to an existing table?
Choices:
A) `UPDATE TABLE ... ADD`
B) `INSERT INTO ... COLUMN`
C) `ALTER TABLE ... ADD`
D) `MODIFY TABLE ... ADD`. Answer: Answer: C) `ALTER
TABLE ... ADD`
[cite_start]Explanation: The `ALTER TABLE` statement is used to
modify the structure of an existing table, such as adding or dropping
columns[cite: 2639].
⫸ ### **SECTION 2 - RELATIONAL ALGEBRA AND QBE**
QUESTION 9 - SECTION 2
In Relational Algebra, which operator is used to select specific rows
from a relation based on a condition?
Choices:
A) Projection ($\pi$)
B) Selection ($\sigma$)
C) Cartesian Product ($\times$)
D) Join ($\bowtie$). Answer: Answer: B) Selection ($\sigma$)
[cite_start]Explanation: The selection operation ($\sigma$) is used to
choose a subset of tuples (rows) from a relation that satisfy a specific
selection condition[cite: 3556].
