Unit 5: Introduction to SQL - Practice Quiz
1 What is SQL mainly used for?
2 Which SQL command creates a new table?
SELECT
GRANT
INSERT
CREATE
3 Which DDL command changes the structure of an existing table?
UPDATE
COMMIT
SELECT
ALTER
4 Which SQL command adds a new row to a table?
REVOKE
ROLLBACK
ALTER
INSERT
5 Which SQL command changes existing data in a table?
GRANT
COMMIT
CREATE
UPDATE
6 Which DCL command gives a user database privileges?
ROLLBACK
DELETE
DROP
GRANT
7 Which TCL command permanently saves the changes made in a transaction?
SELECT
COMMIT
ALTER
REVOKE
8 Which key uniquely identifies each row in a table?
9 Which key creates a relationship by referring to a key in another table?
10 Which aggregate function returns the smallest value in a column?
MAX()
MIN()
SUM()
AVG()
11 Which aggregate function calculates the arithmetic mean of numeric values?
COUNT()
MAX()
AVG()
SUM()
12 Which aggregate function returns the number of rows matched by a query?
MIN()
COUNT()
SUM()
MAX()
13 What does a self join do?
14 Which operator is used in the matching condition of an equi join?
>
=
<
<>
15 Which rows are returned by an inner join?
16 What does a left outer join return?
17 What result does a cross join produce?
18
What is the main purpose of the GROUP BY clause?
19
Which clause filters groups after a GROUP BY operation?
HAVING
ORDER BY
SELECT
WHERE
20 Which clause sorts the rows in a query result?
GROUP BY
ORDER BY
WHERE
HAVING
21
A developer writes SELECT name FROM Student WHERE marks >= 75; without specifying how records should be scanned. Which SQL characteristic does this demonstrate?
22
A populated Employee table needs a new email column without removing its existing rows. Which command is most appropriate?
CREATE COLUMN email IN Employee VARCHAR(100);
UPDATE Employee ADD email VARCHAR(100);
ALTER TABLE Employee ADD email VARCHAR(100);
INSERT INTO Employee email VARCHAR(100);
23
Which statement removes both the TemporaryData table definition and all rows stored in it?
DELETE TABLE TemporaryData;
DROP TABLE TemporaryData;
REMOVE TABLE TemporaryData;
TRUNCATE DATABASE TemporaryData;
24 The salary of every employee in department 4 must increase by 10%. Which statement performs the required change?
UPDATE Employee SET salary = salary + 10 WHERE dept_id = 4;
MODIFY Employee SET salary = salary * 1.10 WHERE dept_id = 4;
ALTER Employee SET salary = salary * 1.10 WHERE dept_id = 4;
UPDATE Employee SET salary = salary * 1.10 WHERE dept_id = 4;
25
User reporter should be allowed to read the Sales table but not modify it. Which command provides the required privilege?
GRANT UPDATE ON Sales TO reporter;
ALLOW SELECT ON Sales FOR reporter;
GRANT SELECT ON Sales TO reporter;
REVOKE SELECT ON Sales FROM reporter;
26
A transaction creates savepoint before_update, performs two valid inserts, and then performs an incorrect update. Which command undoes only the work after that savepoint?
ROLLBACK TO before_update;
COMMIT TO before_update;
REVOKE before_update;
ROLLBACK TRANSACTION;
27
In Enrollment(student_id, course_id, semester), a student may take many courses and a course may have many students. Each student can enroll in a course only once per semester. Which primary key best enforces this rule?
(student_id, course_id)
(course_id, semester)
(student_id, course_id, semester)
(student_id, semester)
28
The column Order.customer_id references Customer.customer_id. What type of key is Order.customer_id in the Order table?
29
A department has salary values 40000, 50000, and NULL. What does AVG(salary) return for that department?
45000
NULL
30000
50000
30 Which query returns the lowest and highest product prices in a single result row?
SELECT MIN(price), MAX(price) FROM Product;
SELECT COUNT(price), MAX(price) FROM Product;
SELECT MIN(price), SUM(price) FROM Product;
SELECT AVG(price), MAX(price) FROM Product;
31
Table Employee(emp_id, name, manager_id) stores each manager's ID in the same table. Which query correctly lists each employee with the employee's manager?
SELECT e.name, m.name FROM Employee e JOIN Employee m ON e.emp_id = m.manager_id;
SELECT e.name, m.name FROM Employee e CROSS JOIN Employee m ON e.manager_id = m.emp_id;
SELECT e.name, m.name FROM Employee e JOIN Employee m ON e.emp_id = m.emp_id;
SELECT e.name, m.name FROM Employee e JOIN Employee m ON e.manager_id = m.emp_id;
32
Which condition makes a join between Orders and Customer an equi join?
Orders.customer_id = Customer.customer_id
Orders.customer_id <> Customer.customer_id
Orders.customer_id BETWEEN 1 AND 10
Orders.customer_id > Customer.customer_id
33
A query must return only customers who have at least one matching order. Which join should be used between Customer and Orders?
FULL OUTER JOIN
INNER JOIN
CROSS JOIN
LEFT OUTER JOIN
34 A report must list every customer, including customers who have placed no orders. Which query meets this requirement?
SELECT * FROM Customer c INNER JOIN Orders o ON c.id = o.customer_id;
SELECT * FROM Customer c RIGHT JOIN Orders o ON c.id = o.customer_id;
SELECT * FROM Customer c CROSS JOIN Orders o;
SELECT * FROM Customer c LEFT JOIN Orders o ON c.id = o.customer_id;
35
Table Size contains 6 rows and table Color contains 4 rows. How many rows are produced by Size CROSS JOIN Color?
36 Which query correctly returns the number of employees in each department?
SELECT dept_id, COUNT(*) FROM Employee ORDER BY dept_id;
SELECT dept_id, COUNT(*) FROM Employee HAVING dept_id;
SELECT dept_id, COUNT(*) FROM Employee GROUP BY dept_id;
SELECT dept_id, COUNT(*) FROM Employee WHERE dept_id;
37
Which query returns only departments whose average salary is greater than 60000?
SELECT dept_id FROM Employee GROUP BY dept_id HAVING AVG(salary) > 60000;
SELECT dept_id FROM Employee HAVING salary > 60000 GROUP BY dept_id;
SELECT dept_id FROM Employee WHERE AVG(salary) > 60000 GROUP BY dept_id;
SELECT dept_id FROM Employee GROUP BY dept_id WHERE AVG(salary) > 60000;
38 A product list must be sorted from highest to lowest price, with equal-priced products sorted alphabetically by name. Which clause is correct?
ORDER BY price DESC, name ASC
ORDER BY name DESC, price ASC
ORDER BY price ASC, name DESC
ORDER BY name ASC, price DESC
39
A table contains 5 rows, but bonus is NULL in 2 of them. What values are returned by COUNT(*) and COUNT(bonus)?
3 and 3
5 and 5
3 and 5
5 and 3
40
Which query calculates total sales for each region and retains only regions whose total exceeds 100000?
SELECT region, SUM(amount) FROM Sales GROUP BY region HAVING SUM(amount) > 100000;
SELECT region, SUM(amount) FROM Sales WHERE SUM(amount) > 100000 GROUP BY region;
SELECT region, SUM(amount) FROM Sales HAVING amount > 100000 GROUP BY region;
SELECT region, SUM(amount) FROM Sales ORDER BY region HAVING SUM(amount) > 100000;
41
Table T(x) contains the rows 1, 2, and NULL. Under standard SQL three-valued logic, which values are returned by SELECT x FROM T WHERE NOT (x = 1);?
2
2 and NULL
1 and NULL
NULL
42
Table Child has a foreign key referencing Parent(id). What happens under standard SQL when DROP TABLE Parent RESTRICT; is executed while that foreign key exists?
RESTRICT propagates.
43
Before the following statement, department A has salaries 100, 200, 300, and department B has salaries 50, 150. What is the total salary afterward?
UPDATE Employee AS e SET salary = salary + 10 WHERE salary < (SELECT AVG(e2.salary) FROM Employee AS e2 WHERE e2.dept = e.dept);
820
830
810
800
44
Alice grants SELECT on R to Bob with the grant option, and Bob grants it to Carol. Alice then executes REVOKE GRANT OPTION FOR SELECT ON R FROM Bob CASCADE;. What is the resulting access?
SELECT; Carol loses SELECT.
SELECT; Carol retains SELECT.
SELECT; Carol loses SELECT.
SELECT; Carol retains SELECT.
45
Accounts A and B initially contain 500 each. A transaction executes: subtract 100 from A; create savepoint S; add 100 to B; ROLLBACK TO S; then COMMIT. What balances are committed?
400, B = 500
500, B = 500
500, B = 600
400, B = 600
46
For relation R(A, B, C, D), the functional dependencies are , , and . Which set lists all candidate keys?
{A, B, C} only
{A, D} and {B, D}
{A} and {B}
{A, C} and {B, C}
47
Department has primary key dept_id and a UNIQUE NOT NULL column dept_code. Employee.dept_code is nullable. Which constraint declaration is valid under standard relational key rules?
Employee.dept_code must reference only Department.dept_id.
Department.dept_code cannot be referenced because it is an alternate key.
Employee.dept_code may reference Department.dept_code.
Employee.dept_code cannot contain NULL after becoming a foreign key.
48
Column v contains 10, NULL, 20, and 30. What tuple is produced by SELECT COUNT(*), COUNT(v), SUM(v), AVG(v), MIN(v), MAX(v) FROM T;?
(4, 4, 60, 15, 10, 30)
(4, 3, 60, 20, 10, 30)
(4, 3, 60, 15, NULL, 30)
(3, 3, 60, 20, 10, 30)
49
Customer A has two matching orders, while customer B has none. For each customer, a query computes COUNT(*) and COUNT(o.order_id) after a LEFT JOIN to Orders o. What pairs are returned for A and B?
(2, 2), B: (1, 0)
(2, 2), B: (0, 0)
(1, 2), B: (1, 1)
(2, 1), B: (1, 0)
50
Table Employee(id, manager_id) stores a hierarchy. Which query finds employees whose manager's manager has id = 1?
SELECT e.id FROM Employee e JOIN Employee m ON e.manager_id = m.manager_id JOIN Employee g ON m.id = g.id WHERE g.id = 1;
SELECT e.id FROM Employee e JOIN Employee m ON e.manager_id = m.id JOIN Employee g ON m.manager_id = g.id WHERE g.id = 1;
SELECT e.id FROM Employee e JOIN Employee m ON e.id = m.id JOIN Employee g ON m.manager_id = g.manager_id WHERE g.id = 1;
SELECT e.id FROM Employee e JOIN Employee m ON m.manager_id = e.id JOIN Employee g ON g.manager_id = m.id WHERE g.id = 1;
51
Table A contains join-key values 1, 1, 2, and table B contains 1, 1, 1, 3. How many rows result from A JOIN B ON A.key = B.key?
3
7
5
6
52
A contains (k,v) rows (1,10), (2,NULL), (3,20). B contains (1,10), (1,NULL), (2,NULL), (3,30). How many rows result from A JOIN B ON A.k = B.k AND A.v = B.v?
2
1
3
4
53
Three employees reference departments 10, 20, and 30. Department 10 is active, department 20 is inactive, and department 30 is absent. What does the following return?
SELECT COUNT(*), COUNT(d.id) FROM Employee e LEFT JOIN Department d ON e.dept_id = d.id AND d.active = 1;
(3, 2)
(1, 1)
(2, 1)
(3, 1)
54
A contains keys 1, 1, 2, and B contains keys 1, 3, 3. How many rows are produced by A FULL OUTER JOIN B ON A.key = B.key?
6
7
4
5
55
A contains x = 1, 2, 3, and B contains y = 2, 3. How many rows remain after SELECT * FROM A CROSS JOIN B WHERE A.x < B.y;?
4
3
6
2
56
Sales rows are ('E','A',10), ('E','A',NULL), ('E','B',20), (NULL,'A',5), and (NULL,'A',15). Ignoring row order, what does SELECT region, product, COUNT(amount), SUM(amount) FROM Sales GROUP BY region, product; return?
[(NULL,A,2,20), (E,A,1,10), (E,B,1,20)]
[(NULL,A,2,20), (E,A,2,10), (E,B,1,20)]
[(NULL,A,1,20), (E,A,1,10), (E,B,1,20)]
[(NULL,A,2,20), (E,A,1,NULL), (E,B,1,20)]
57
Department A has salaries 10 and 20; department B has salary 100. What is returned by SELECT AVG(dept_avg) FROM (SELECT dept, AVG(salary) AS dept_avg FROM Employee GROUP BY dept) AS D;?
65.00
43.33
50.00
57.50
58
Product A has amounts 10, NULL; B has 5, 15; and C has NULL, NULL. Which products survive GROUP BY product HAVING COUNT(amount) = COUNT(*)?
59
Table T contains amounts 10, 20, and NULL. What does SELECT COUNT(*) FROM T HAVING AVG(amount) > 12; return when no GROUP BY is present?
1
2
3
60
Which clause selects the three highest-paid employees deterministically, choosing the lower employee_id first whenever salaries tie?
ORDER BY salary ASC, employee_id ASC FETCH FIRST 3 ROWS ONLY
ORDER BY salary DESC, employee_id DESC FETCH FIRST 3 ROWS ONLY
ORDER BY salary DESC, employee_id ASC FETCH FIRST 3 ROWS ONLY
ORDER BY employee_id ASC, salary DESC FETCH FIRST 3 ROWS ONLY
Did this save you a night before the exam?
LPU Notes is free, and it stays free. Ads cover part of the server bill. The rest comes out of a student's own pocket: the domain, the storage, and keeping the site up through the weeks everyone needs it at once.
The payment button didn't load. An ad blocker or a filtered network is the usual reason. to try again.
Nothing here is ever locked, and nothing unlocks. Chip in only if it was worth it. What it pays for →