Cps 510 Actual Test Set 2026 Precise Solutions Delivered

CPS 510 ACTUAL TEST SET 2026 PRECISE
SOLUTIONS DELIVERED

◍ QUESTION 2 - 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`
B) `SELECT major, COUNT(*) FROM Student GROUP BY major
HAVING AVG(gpa) > 3.0`
C) `SELECT major, COUNT(*) FROM Student GROUP BY major
WHERE AVG(gpa) > 3.0`
D) `SELECT major, COUNT(*) FROM Student HAVING AVG(gpa)
> 3.0`.Answer: B) `SELECT major, COUNT(*) FROM Student
GROUP BY major HAVING AVG(gpa) > 3.0`
[cite_start]Explanation: The `WHERE` clause filters rows before
grouping, while `HAVING` filters groups after the aggregation (like
`AVG`) has been performed[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')`.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: 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: 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: 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: 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: 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].