DBMS MCQs - Advanced SQL: Subqueries, GROUP BY, Views and Triggers (Set 3)

Q1. Which pair of SQL commands belongs to DCL (Data Control Language)?

a) COMMIT and ROLLBACK

b) GRANT and REVOKE

c) CREATE and ALTER

d) INSERT and UPDATE

Q2. The SAVEPOINT command belongs to which category of SQL?

a) DDL

b) DML

c) DCL

d) TCL

Q3. Which statement about the HAVING clause is correct?

a) It filters groups after GROUP BY is applied and can use aggregate functions in its condition

b) It filters individual rows before grouping and cannot use aggregate functions

c) It can be used only in queries that have no GROUP BY clause

d) It is used to sort the groups produced by GROUP BY

Q4. Table T has a single column marks with the five values shown. What does the query return?

marks
10
NULL
20
NULL
30

SELECT COUNT(*), COUNT(marks), AVG(marks) FROM T;

a) 5, 5, 12

b) 3, 3, 20

c) 5, 3, 20

d) 5, 3, 12

Q5. The HR department has 3 rows in table emp, and the bonus column is NULL in all of them. What does this query return?

SELECT SUM(bonus), COUNT(bonus) FROM emp WHERE dept = 'HR';

a) 0, 0

b) 0, 3

c) NULL, 3

d) NULL, 0

Q6. Which regions does the following query return?

Sales(region, amount):
N | 100
S | 200
N | 300
E | 50
S | 100
N | 50

SELECT region, SUM(amount) FROM Sales
GROUP BY region HAVING SUM(amount) > 250;

a) Only N

b) N and S

c) N, S and E

d) Only S

Q7. What happens with this query in standard SQL?

SELECT dept, name, MAX(salary)
FROM emp
GROUP BY dept;

a) It is invalid, because name is neither in the GROUP BY clause nor inside an aggregate function

b) It returns the name of the highest-paid employee in each department

c) It returns every employee along with the maximum salary of the table

d) It returns a single row for the whole table

Q8. Table A has column x with values 1, 2, 3. Table B has column y with values 1 and NULL. What does this query return?

SELECT x FROM A
WHERE x NOT IN (SELECT y FROM B);

a) 2 and 3

b) Only 3

c) No rows

d) 1, 2 and 3

Q9. What makes a subquery a correlated subquery?

a) It refers to a column of the outer query, so it is logically evaluated once for each row of the outer query

b) It returns more than one column

c) It is written in the FROM clause

d) It contains a GROUP BY clause

Q10. The condition WHERE EXISTS (subquery) is TRUE when:

a) The subquery returns exactly one row

b) The first column of the subquery result is not NULL

c) The subquery result contains no NULL values

d) The subquery returns at least one row

Q11. What does this correlated query return on the table shown?

emp(name, salary):
A | 500
B | 700
C | 700
D | 300

SELECT name FROM emp e1
WHERE 2 = (SELECT COUNT(DISTINCT salary)
           FROM emp e2
           WHERE e2.salary >= e1.salary);

a) B and C

b) A

c) D

d) A and D

Q12. Customers has ids 1, 2, 3. Orders has cust_id values 1, 1, 2, 4. How many rows does this query return?

SELECT *
FROM Customers c
LEFT JOIN Orders o ON c.id = o.cust_id;

a) 3

b) 5

c) 4

d) 6

Q13. Which kind of view is generally NOT updatable (you cannot run INSERT or UPDATE through it)?

a) A view defined using GROUP BY and aggregate functions

b) A view selecting some columns, including the primary key, from one table

c) A view on one table with a WHERE filter

d) A view on one table defined WITH CHECK OPTION

Q14. What is the purpose of WITH CHECK OPTION in a view definition?

a) It makes the view read-only

b) It rejects inserts and updates through the view that would create rows not satisfying the view's WHERE condition

c) It stores the view's result physically like a table

d) It verifies foreign key constraints of the base table

Q15. A table has a row-level trigger (FOR EACH ROW, AFTER UPDATE) and a statement-level AFTER UPDATE trigger. A single UPDATE statement modifies 50 rows. How many trigger executions happen in total?

a) 1

b) 50

c) 100

d) 51

Q16. Which statement correctly distinguishes a stored function from a stored procedure in most relational databases?

a) A stored procedure cannot take any parameters

b) A stored function cannot contain a SELECT statement

c) A stored function returns a value and can be used inside a SQL expression, while a procedure is invoked on its own using CALL or EXEC

d) Stored procedures run on the client, while functions run on the server

Q17. Salaries ordered from highest to lowest are 900, 800, 800, 700. For the row with salary 700, what values do RANK(), DENSE_RANK() and ROW_NUMBER() give, all using ORDER BY salary DESC?

a) 3, 3, 4

b) 4, 3, 4

c) 4, 4, 4

d) 3, 4, 4

Q18. Table emp has 6 rows spread across 2 departments. What does this query return?

SELECT dept, salary,
       SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM emp;

a) 6 rows, each showing its department's total salary alongside its own salary

b) 2 rows, one total per department

c) 1 row with the overall total

d) An error, because salary is not in a GROUP BY clause