SQL Exercises with Answers
Practise SQL with 202 interactive exercises: multiple choice, fill-in-the-blank, output prediction and debugging. Every exercise gives instant feedback, a hint and the full solution.
Topic All topics Introduction SELECT WHERE (Filtering) NULL Values ORDER BY & GROUP BY Functions CASE Expressions INSERT, UPDATE, DELETE Dates & Times CREATE & ALTER TABLE Constraints & Keys JOIN More Joins UNION & Set Operations Subqueries & CTEs Data Cleaning Table Relationships Normalization Views CTEs & Recursive Queries Indexes & Performance Transactions Window Functions Triggers & Procedures Security & Permissions SQL syntax and comments SQL operators and precedence SELECT DISTINCT SQL aliases LIKE and wildcard patterns IN, BETWEEN, ANY and ALL EXISTS and NOT EXISTS IS NULL and IS NOT NULL LIMIT, TOP and FETCH FIRST SQL keywords and commands MIN() MAX() COUNT() SUM() AVG() Aggregate vs window functions GROUP BY and HAVING SQL string functions SQL numeric and mathematical functions SQL date and time functions COALESCE and NULLIF Conditional aggregation CAST() and CONVERT() Multiple-column and NULL sorting CREATE DATABASE and DROP DATABASE CREATE, ALTER and DROP TABLE SQL data types INSERT INTO SELECT SELECT INTO DEFAULT, CHECK and UNIQUE constraints AUTO_INCREMENT and identity columns Primary keys and composite keys Foreign keys and referential actions Temporary tables Database schema and metadata INNER JOIN LEFT, RIGHT and FULL OUTER JOIN Self joins and cross joins Anti-joins and unmatched records UNION vs UNION ALL INTERSECT and EXCEPT Correlated subqueries Derived tables Common table expressions Recursive queries Pivoting and conditional aggregation Ranking Functions LAG and LEAD Running Totals and Averages Window Frames and Partitions Index Types and Composite Indexes EXPLAIN and Execution Plans Query Optimization ACID and Isolation Levels Deadlocks and Locking Procedures vs Functions Trigger Use Cases Database Design from Requirements Entity-Relationship (ER) Diagrams One-to-One, One-to-Many and Many-to-Many Relationships Normalization: 1NF, 2NF, 3NF and BCNF Data Integrity and Referential Integrity SQL Data Cleaning and Deduplication Importing and Exporting CSV Data Database Backup and Restore Basics SQL Security, Roles and Permissions Parameterized Queries and SQL Injection Prevention MySQL tutorial PostgreSQL tutorial SQL Server and T-SQL SQLite tutorial SQL dialect differences SQL interview questions SQL coding challenges SQL exercises by difficulty SQL business query problems SQL reporting projects All types Fill in the blanks Multiple choice Predict the output
Any level Easy Medium Hard
Score 0 / 202
In a database table, what is a row?
A One piece of information, such as a name B The name of the table C A type of query D One complete record, such as one customer
A row is a record; a column is a single field.
Show answer
Why does almost every table have an id primary key?
A It makes the table sort faster B SQL won't run without a column called id C It uniquely identifies each row so other tables can refer to it D It stores the date the row was added
Primary keys are unique and stable, which lets tables link to each other.
Show answer
How many rows does SELECT * FROM customers; return for the example shop?
A 1 B 5 C 4 D 20
The customers table has five rows: Ana, Ben, Chloe, Dev and Ema.
Show answer
Which column is the best primary key for a customers table?
A id B name C city D joined
Names and cities can repeat; an id is unique and never changes.
Show answer
Which keyword renames a column in the result?
A RENAME B NAME C ALIAS D AS
column AS new_name sets an alias.
Show answer
medium SELECT How many rows does this return for the example shop?
SELECT DISTINCT city FROM customers; A 5 B 4 C 3 D 2
Lisbon, Leeds, Lyon and Porto. Leeds appears only once.
Show answer
medium SELECT What does this return for the Pen row?
SELECT name, price * 10 AS ten_pens FROM products; A Pen, 12.00 B Pen, 1.20 C Pen, 10 D An error, because ten_pens isn't a column
The calculation creates a new column; 1.20 x 10 is 12.00.
Show answer
medium SELECT How many rows does SELECT DISTINCT city FROM customers; return for this course's customers table?
A 5 B 3 C 4 D 1
Leeds appears twice, so the five customers have four different cities.
Show answer
Which names does this return?
SELECT name FROM customers
WHERE city IN ( 'Lisbon' , 'Porto' ) ; A Ana and Ema B Ana C Ben and Dev D Every customer
Ana lives in Lisbon and Ema in Porto.
Show answer
Which pattern finds product names that end in "lamp"?
A LIKE 'lamp%' B LIKE '_lamp' C LIKE '%lamp' D LIKE 'lamp'
% before the word means "anything, then lamp at the end".
Show answer
How many products does this return?
SELECT name FROM products
WHERE stock BETWEEN 15 AND 40 ; A 3 B 1 C 2 D 4
Backpack (15), Water bottle (40) and Desk lamp (22). BETWEEN includes both ends.
Show answer
Which condition finds products whose name starts with "B"?
A name = 'B%' B name LIKE '%B' C name LIKE 'B%' D name IN ('B')
% stands for any number of characters after the B.
Show answer
Complete the query to find customers with no email address.
SELECT name FROM customers WHERE email ;
IS NULL is the only reliable way to test for a missing value.
Check answer Show solution
What does NULL mean in a database?
A The value is unknown or missing B Zero C An empty string D The row was deleted
NULL is a marker for missing information.
Show answer
A column bonus holds 100, NULL and 200. What does SELECT COUNT(bonus) FROM staff; return?
A 3 B 2 C 300 D NULL
COUNT(column) skips NULLs.
Show answer
What does SELECT COALESCE(NULL, NULL, 'x', 'y'); return?
A x B NULL C y D xy
COALESCE returns the first value that isn't NULL.
Show answer
Using the same bonus values (100, NULL, 200), what does AVG(bonus) return?
A 100 B NULL C 300 D 150
AVG ignores the NULL and averages 100 and 200.
Show answer
SELECT COUNT ( * ) FROM customers WHERE city = 'Leeds' ; A 1 B 5 C Ben, Dev D 2
COUNT returns a number: Ben and Dev live in Leeds, so 2.
Show answer
Which clause keeps only the groups whose total stock is over 100?
A WHERE SUM(stock) > 100 B LIMIT 100 C HAVING SUM(stock) > 100 D ORDER BY SUM(stock) > 100
HAVING filters groups after GROUP BY has created them.
Show answer
Which product does this return?
SELECT name FROM products
ORDER BY stock DESC
LIMIT 1 ; A Pen B Notebook C Backpack D Lunch box
Pen has the most stock (300), so it's first when sorted high to low.
Show answer
Which query lists categories that have more than one product?
A SELECT category FROM products WHERE COUNT(*) > 1; B SELECT category FROM products ORDER BY COUNT(*) > 1; C SELECT category, COUNT(*) > 1 FROM products; D SELECT category FROM products GROUP BY category HAVING COUNT(*) > 1;
HAVING filters groups after GROUP BY; WHERE can't use aggregates.
Show answer
Complete the query so every city is shown in capital letters.
SELECT ( city) FROM customers;
UPPER() returns the text in capital letters.
Check answer Show solution
SELECT LENGTH ( 'Water bottle' ) ; A 11 B 12 C 2 D Water bottle
The space counts as a character: 5 + 1 + 6 = 12.
Show answer
SELECT COALESCE ( NULL , NULL , 'none' , 'other' ) ; A NULL B none C other D none, other
COALESCE returns the first value that isn't NULL.
Show answer
Which expression avoids a division-by-zero error when qty is 0?
A total / NULLIF(qty, 0) B total / qty C total / COALESCE(qty, 0) D ABS(total / qty)
NULLIF turns 0 into NULL, and dividing by NULL gives NULL instead of an error.
Show answer
SELECT SUBSTR ( 'Notebook' , 5 , 4 ) ; A Note B eboo C ebook D book
Start at character 5 ("b") and take 4 characters.
Show answer
Complete the expression so it ends correctly.
SELECT name,
CASE WHEN price > 10 THEN 'Pricey' ELSE 'Cheap' AS label
FROM products;
Every CASE expression must finish with END.
Check answer Show solution
What label does the Pen (price 1.20) get?
CASE
WHEN price >= 20 THEN 'High'
WHEN price >= 5 THEN 'Mid'
ELSE 'Low'
END A High B Mid C NULL D Low
Neither condition is true, so ELSE applies.
Show answer
What does a CASE with no ELSE return when no WHEN matches?
A NULL B 0 C An empty string D An error
The missing ELSE behaves like ELSE NULL.
Show answer
What does this return for the example products?
SELECT SUM ( CASE WHEN category = 'Kitchen' THEN 1 ELSE 0 END ) FROM products; A 1 B 2 C 6 D Kitchen
The water bottle and the lunch box are in Kitchen.
Show answer
Which ORDER BY puts sold-out products last, then sorts by name?
A ORDER BY stock DESC, name B ORDER BY name, stock = 0 C ORDER BY CASE WHEN stock = 0 THEN 1 ELSE 0 END, name D ORDER BY CASE stock WHEN 0 THEN name END
In-stock rows get 0 and sort first; ties are broken by name.
Show answer
How many rows does this change in the example products table?
UPDATE products SET stock = stock + 10 WHERE stock < 30 ; A 1 B 3 C 2 D 6
Backpack (15), Lunch box (0) and Desk lamp (22) have less than 30 in stock.
Show answer
What's the safest first step before running a DELETE?
A Run a SELECT with the same WHERE clause to see which rows match B Run it and check afterwards C Remove the WHERE clause to be sure it works D Restart the database
Checking the WHERE with SELECT shows exactly what will be deleted.
Show answer
What happens if you run DELETE FROM orders; with no WHERE clause?
A Nothing, it's an error B Only the last order is deleted C The orders table itself is dropped D Every row in the orders table is deleted
Always check the WHERE clause with a SELECT first.
Show answer
Complete the SQLite query so it groups orders by year and month.
SELECT strftime ( ' ', order_date) AS month, COUNT(*)
FROM orders
GROUP BY month;
'%Y-%m' gives values like 2024-07, keeping each year separate.
Check answer Show solution
Which date format should you use in SQL?
A 07/05/2024 B 5 July 2024 C 20240705 D 2024-07-05
YYYY-MM-DD is unambiguous and sorts correctly.
Show answer
What does SELECT date('2024-07-30', '+5 days'); return in SQLite?
A 2024-07-35 B 2024-08-05 C 2024-08-04 D NULL
SQLite rolls over into August: 31 July is one day, then four more.
Show answer
Which WHERE clause returns every order placed in July 2024 from a timestamp column?
A created_at = '2024-07' B created_at >= '2024-07-01' AND created_at < '2024-08-01' C created_at BETWEEN '2024-07-01' AND '2024-07-31' D created_at LIKE '07%'
The exclusive end includes every time on 31 July.
Show answer
How do you get the number of days between two dates in MySQL?
A DATEDIFF(b, a) B julianday(b) - julianday(a) C DAYS(b - a) D b - a
SQLite uses julianday; PostgreSQL subtracts dates directly.
Show answer
Complete the statement that creates a table.
tags (
id INTEGER PRIMARY KEY ,
name TEXT NOT NULL
) ;
CREATE TABLE is followed by the table name and the column list in brackets.
Check answer Show solution
Which type is best for storing prices?
A FLOAT B DECIMAL(10, 2) C TEXT D INTEGER
DECIMAL stores exact values, which is what money needs.
Show answer
After ALTER TABLE customers ADD COLUMN vip BOOLEAN;, what is Dev's vip value?
A FALSE B 0 C NULL D An error
Existing rows get NULL unless the column has a DEFAULT.
Show answer
What's the difference between DELETE FROM t; and DROP TABLE t;?
A There's no difference B DROP removes rows but keeps the table C DELETE only works with WHERE D DELETE removes the rows but keeps the table; DROP removes the table itself
After DROP, the table no longer exists.
Show answer
Complete the column so every product must have a name.
name TEXT
NOT NULL forbids missing values.
Check answer Show solution
Complete the column so customer_id must match a customer.
customer_id INTEGER customers ( id)
REFERENCES creates a foreign key to another table's primary key.
Check answer Show solution
Which constraint stops two users signing up with the same email?
A NOT NULL B DEFAULT C UNIQUE D CHECK
UNIQUE rejects duplicate values.
Show answer
With ON DELETE RESTRICT, what happens when you delete a customer who has orders?
A Their orders are deleted too B Their orders get customer_id NULL C Only the oldest order is deleted D The delete is refused with an error
RESTRICT protects the related rows.
Show answer
Which is true about a primary key?
A It can contain NULL B It's unique, not null, and a table has only one C A table can have several D It must be called id
It may span several columns, but there's only one per table.
Show answer
medium JOIN Which join returns every customer, even those without orders?
A INNER JOIN orders B customers LEFT JOIN orders C orders LEFT JOIN customers D JOIN without ON
The left table, customers, keeps all of its rows.
Show answer
medium JOIN How many rows does this return?
SELECT c. name
FROM customers c
JOIN orders o ON o. customer_id = c. id
WHERE c. city = 'Leeds' ; A 3 B 1 C 2 D 5
Ben (2 orders) and Dev (1 order) live in Leeds: one row per order, so 3.
Show answer
hard JOIN After a LEFT JOIN from customers to orders, what is o.id for Ema?
A 0 B 5 C NULL D An empty string
When there's no match, every column from the right table is NULL.
Show answer
hard JOIN Using this course's tables, how many rows does an INNER JOIN of customers to orders return?
A 5 B 11 C 6 D 30
Each of the six orders matches one customer; Ema has no orders, so she doesn't appear.
Show answer
Complete the anti-join that finds customers who have no orders.
SELECT c. name
FROM customers c
LEFT JOIN orders o ON o. customer_id = c. id
WHERE o. id ;
Unmatched rows have NULL in every column from orders.
Check answer Show solution
A Joining a table to a view B Joining a table to itself using two aliases C A join with no condition D A join that removes duplicates
Useful when rows refer to other rows in the same table.
Show answer
A table of 4 colours is CROSS JOINed with a table of 5 sizes. How many rows come back?
A 9 B 5 C 20 D 4
Every colour is paired with every size: 4 times 5.
Show answer
Which join keeps every row from both tables?
A INNER JOIN B FULL OUTER JOIN C LEFT JOIN D CROSS JOIN
Unmatched rows on either side get NULLs.
Show answer
Why does adding WHERE o.status = 'paid' to a LEFT JOIN from customers to orders remove customers with no orders?
A Their order columns are NULL, and NULL = 'paid' isn't true, so WHERE drops them B LEFT JOIN doesn't allow WHERE C It only checks the first order D status must be indexed
Move the condition into ON to keep them.
Show answer
Complete the query so every row from both tables is kept, including duplicates.
SELECT city FROM customers
UNION SELECT city FROM subscribers;
UNION ALL keeps duplicates; plain UNION removes them.
Check answer Show solution
What does UNION do with duplicate rows?
A Removes them, so each row appears once B Keeps them all C Raises an error D Moves them to the end
Use UNION ALL to keep duplicates.
Show answer
customers has 5 rows and subscribers has 4. How many rows does SELECT name FROM customers UNION ALL SELECT name FROM subscribers return?
A 5 B 9 C 7 D 20
UNION ALL simply stacks all rows: 5 plus 4.
Show answer
Which operation returns names that appear in both lists?
A INTERSECT B UNION C EXCEPT D CROSS JOIN
INTERSECT keeps rows found in both results.
Show answer
Where do the column names of a UNION result come from?
A The last query B Whichever query has aliases C They're numbered automatically D The first query
Put aliases in the first SELECT.
Show answer
Complete the query so it finds products cheaper than the average.
SELECT name FROM products
WHERE price < ( AVG ( price) FROM products) ;
A subquery is a full SELECT statement inside brackets.
Check answer Show solution
How many rows does this return for the example shop?
SELECT name FROM customers
WHERE id IN ( SELECT customer_id FROM orders) ; A 6 B 5 C 1 D 4
Ana, Ben, Chloe and Dev have orders. IN ignores duplicates, so each appears once.
Show answer
Why is SELECT 1 used inside EXISTS?
A It returns the number 1 to the outer query B It limits the subquery to one row C It's required syntax D EXISTS only checks whether a row exists, so the selected value doesn't matter
Any value would work; 1 is a common convention.
Show answer
What is a correlated subquery?
A A subquery that refers to a column of the outer query's current row B A subquery in the FROM clause C A subquery that returns one value D Two subqueries joined together
It's evaluated for each outer row.
Show answer
Complete the query to remove spaces from both ends of each name.
SELECT ( name) FROM signups;
TRIM removes leading and trailing spaces.
Check answer Show solution
Which clause keeps only groups that appear more than once?
A WHERE COUNT(*) > 1 B HAVING COUNT(*) > 1 C ORDER BY COUNT(*) D LIMIT 1
HAVING filters groups after GROUP BY.
Show answer
What does SELECT NULLIF(TRIM(' '), ''); return?
A ' ' B '' C 0 D NULL
TRIM leaves an empty string, and NULLIF turns it into NULL.
Show answer
Why lowercase emails before looking for duplicates?
A Lowercase uses less space B Emails must be lowercase to be valid C The same address may have been typed with different capitals D GROUP BY ignores capitals
Ana@Example.com and ana@example.com are the same mailbox.
Show answer
Which DELETE keeps one copy of each duplicated email?
A DELETE FROM signups WHERE id NOT IN (SELECT MIN(id) FROM signups GROUP BY email) B DELETE FROM signups WHERE email IN (SELECT email FROM signups) C DELETE FROM signups GROUP BY email D DELETE DISTINCT FROM signups
Each group keeps its lowest id; every other row is removed.
Show answer
Complete the junction table's composite primary key.
CREATE TABLE product_tags (
product_id INTEGER REFERENCES products ( id) ,
tag_id INTEGER REFERENCES tags ( id) ,
PRIMARY KEY ( )
) ;
Both columns together identify each pair, and stop duplicates.
Check answer Show solution
In a one-to-many relationship, where does the foreign key go?
A On the "one" side B In both tables C On the "many" side D In a junction table
Each order stores its customer_id.
Show answer
Students can take many courses, and courses have many students. What do you need?
A A junction table such as enrolments with student_id and course_id B A course_id column in students C A student_ids column in courses D One table for both
Many-to-many relationships need a junction table.
Show answer
What does ON DELETE CASCADE do?
A Prevents the delete B Sets the foreign key to 0 C Copies the row to an archive D Deletes the linked rows automatically
Use it where linked rows have no meaning on their own.
Show answer
Using the tag data in this lesson, how many rows does SELECT * FROM product_tags WHERE tag_id = 1; return?
A 1 B 3 C 2 D 6
Notebook, Pen and Backpack all have the "back to school" tag.
Show answer
Complete the rule of normalization.
Each fact is stored in exactly
Storing facts once prevents contradictions.
Check answer Show solution
Which column breaks first normal form?
A price DECIMAL B phone_numbers TEXT containing '0123, 0456, 0789' C city TEXT D id INTEGER PRIMARY KEY
1NF requires one value per column.
Show answer
You delete a customer's only order and lose all record of the customer. Which problem is this?
A Update anomaly B Insert anomaly C Delete anomaly D Repeating group
Customer data shouldn't depend on orders existing.
Show answer
An orders table has columns id, customer_id and customer_city. Which normal form does customer_city break?
A 1NF B 2NF C 3NF D None
city depends on the customer, not directly on the order.
Show answer
Why does order_lines store unit_price even though products has a price?
A It records the price actually charged, which can differ from today's price B It's a mistake C It makes joins faster D SQL requires it
Historical values are separate facts.
Show answer
How do you read data from a view called cheap_products?
A RUN VIEW cheap_products; B SELECT * FROM cheap_products; C OPEN cheap_products; D SHOW cheap_products;
Views are queried exactly like tables.
Show answer
medium Views A product's price changes from 9 to 12. What does cheap_products (price < 10) show afterwards?
A The old price, 9 B The new price, 12 C An error until the view is refreshed D The product no longer appears
The view reruns its query, and 12 isn't below 10.
Show answer
medium Views What does DROP VIEW order_details; delete?
A The orders, customers and products tables B Every order C Only the saved view, not any data D The view and its rows
A view holds no rows of its own.
Show answer
Which is a good reason to create a view?
A To make a slow query run faster B To reuse a complex query and hide sensitive columns from report users C To back up a table D To add a constraint
Ordinary views don't speed up queries, but they simplify and protect access.
Show answer
Complete the keyword that starts a common table expression.
AS (
SELECT * FROM orders WHERE order_date >= '2024-07-05'
)
SELECT COUNT ( * ) FROM recent;
WITH introduces one or more named CTEs.
Check answer Show solution
How long does a CTE exist?
A Until the database restarts B Until you drop it C Only for the statement it belongs to D For the whole session
It isn't saved anywhere.
Show answer
WITH RECURSIVE n ( x) AS (
SELECT 2 UNION ALL SELECT x * 2 FROM n WHERE x < 16
)
SELECT x FROM n; A 2, 4, 8 B 2, 4, 8, 16, 32 C 16 D 2, 4, 8, 16
When x is 16, the condition x < 16 fails, so no 32 is produced.
Show answer
How do you write two CTEs in one query?
A Two WITH keywords B Separate them with UNION C One WITH, with the CTEs separated by commas D It isn't possible
WITH a AS (...), b AS (...) SELECT ...
Show answer
What are the two parts of a recursive CTE?
A A SELECT and a DELETE B Two views C A WHERE and a HAVING D An anchor query and a recursive member, joined by UNION ALL
The anchor starts; the recursive member repeats until no rows are returned.
Show answer
What does a full table scan mean?
A The database reads every row to find the matches B The table is copied C Only the first row is read D The index is rebuilt
It's what happens when no suitable index exists.
Show answer
Which columns automatically get an index?
A Every column B Only text columns C Foreign keys in every database D Primary keys and UNIQUE columns
Other columns, including foreign keys in SQLite and PostgreSQL, need indexes added by you.
Show answer
An index exists on (category, price). Which query can't use it efficiently?
A WHERE category = 'Kitchen' B WHERE price < 10 C WHERE category = 'Kitchen' AND price < 10 D WHERE category = 'Kitchen' ORDER BY price
A composite index helps only when its leading column is used.
Show answer
What's the downside of adding many indexes to a table?
A Queries return fewer rows B SELECT statements stop working C Inserts, updates and deletes get slower, and storage grows D Primary keys are removed
Every index must be updated when data changes.
Show answer
A Cancels every change made since BEGIN B Repeats the last statement C Deletes the table D Commits the transaction
The data returns to how it was before the transaction began.
Show answer
What is Sam's balance after this?
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE owner = 'Sam' ;
ROLLBACK; A 70 B 100 C 130 D NULL
The rollback cancels the update.
Show answer
Which ACID property means committed data survives a power cut?
A Atomicity B Consistency C Durability D Isolation
Committed changes are written safely to storage.
Show answer
Why shouldn't a transaction stay open while waiting for user input?
A It uses more disk space B It automatically commits after one minute C Users can see the uncommitted data D It holds locks that can make other users wait
Keep transactions short and do slow work outside them.
Show answer
Complete the query so products are numbered by price, highest first.
SELECT name, ROW_NUMBER ( ) ( ORDER BY price DESC ) AS n
FROM products;
OVER turns a function into a window function.
Check answer Show solution
Complete the window so the ranking restarts for each category.
RANK ( ) OVER ( ORDER BY price DESC )
PARTITION BY splits the rows into separate windows.
Check answer Show solution
Three rows have scores 90, 80 and 80. What does DENSE_RANK() OVER (ORDER BY score DESC) give?
A 1, 2, 2 B 1, 2, 3 C 1, 2, 2 then the next would be 4 D 1, 1, 2
Ties share a rank, and DENSE_RANK leaves no gap after them.
Show answer
Which function returns the value from the previous row?
A LEAD B PREV C LAG D FIRST
LAG looks back; LEAD looks ahead.
Show answer
How is a window function different from GROUP BY?
A It's faster B It calculates across related rows but keeps every row in the result C It can't use aggregates D It only works on numbers
GROUP BY collapses each group into one row.
Show answer
Complete the trigger so it runs after a row is deleted from orders.
CREATE TRIGGER log_delete
DELETE ON orders
BEGIN
INSERT INTO deleted_orders VALUES ( OLD. id ) ;
END ;
AFTER DELETE runs once the row has been removed; OLD holds its values.
Check answer Show solution
In an UPDATE trigger, what does OLD.price contain?
A The price before the update B The price after the update C The lowest price ever D NULL
NEW holds the value after the update.
Show answer
Which trigger timing can stop a bad insert before it's saved?
A AFTER INSERT B AFTER DELETE C Any timing D BEFORE INSERT
BEFORE triggers run first and can raise an error.
Show answer
In this lesson's audit example, how many rows does price_history contain after the two UPDATEs?
A 0 B 2 C 1 D 6
The pen's price didn't change, so the WHEN condition skipped it.
Show answer
What's the difference between a stored procedure and a function in PostgreSQL?
A Procedures are run with CALL to perform actions; functions return a value and can be used inside queries B There's none C Functions can't take parameters D Procedures only work in SQLite
Use a function when you need a value in a SELECT.
Show answer
Complete the parameterized query in Python's sqlite3.
cur. execute ( "SELECT * FROM users WHERE email = ", (email,))
? is sqlite3's placeholder; the value is passed separately.
Check answer Show solution
What causes SQL injection?
A Using too many indexes B Using SELECT * C Putting user input directly into SQL text D Long passwords
Input then becomes part of the SQL code.
Show answer
With a parameterized query, a visitor searches for the name ' OR '1'='1. How many customers are returned?
A All of them B None, unless someone really has that name C An error is raised D Only the first one
The input is compared as plain text.
Show answer
Which statement lets the user reports read the orders table?
A ALLOW reports ON orders B GRANT SELECT ON orders TO reports; C GIVE SELECT orders reports; D SET reports = SELECT;
GRANT privilege ON object TO user.
Show answer
What does the principle of least privilege mean?
A Every user gets the same privileges B Only one person may use the database C Each user or application gets only the permissions it needs D Privileges expire after a day
It limits the damage if an account is misused.
Show answer
Which sale ids pass (paid AND amount>=20) OR pending?
A 101, 103, 105 and 106 B 102, 103, 104 and 105 C Only 103 and 105 D Every sale except 104
The paid twenties qualify, and both pending records qualify through the separate pending branch.
Show answer
How many rows does the distinct city list return, including its NULL row?
A Two B Four C Three D Five
The selected city values are Leeds, Porto and one NULL; the repeated Leeds is removed.
Show answer
What are the output labels for sale 103 in the alias example?
A name and amount, because AS is ignored B buyer and total, which appear only in the exercise C customer_id and sale_id D customer_name and sale_amount
The selected expressions explicitly define customer_name and sale_amount as their output aliases.
Show answer
Which customer matches the pattern 'B_n'?
A Ben B Ana C Cara D Both Ben and Dev
B and n are literal letters, and underscore matches the single e between them.
Show answer
Which sale ids have an amount IN (5,30)?
A 101 and 103 B 102 and 106 C 103 and 105 D 102, 104 and 106
Sale 102 has thirty and sale 106 has five. NULL does not equal either listed value.
Show answer
Which customer remains in the correlated NOT EXISTS example?
A Cara B Ana and Ben C Dev D Every customer because sale 106 has a NULL customer link
Dev alone has no matching sale. The unrelated NULL link does not poison the correlated absence test.
Show answer
Which sale is returned by WHERE amount IS NULL?
A Sale 106 B Sales 104 and 106 C Every sale with amount below twenty D Sale 104
Sale 104 has the only missing amount; sale 106 has a known amount of five.
Show answer
Which two ids are selected by the largest-known-amount LIMIT example?
A 102 and 103 B 102 and 105 C 104 and 102 D 103 and 105
Thirty ranks first, and id ascending resolves the two twenties in favour of 103 at the second-row boundary.
Show answer
What counts appear in the status-summary command example?
A paid: 3 and pending: 2 B paid: 4 and pending: 2 C paid: 4 and pending: 1 D paid: 55 and pending: 30
COUNT(*) counts sale records, including the anonymous paid sale and the unknown-amount pending sale.
Show answer
medium MIN() What minimum is reported for customer 3 in the worked grouping?
A 0 B NULL C 20 D 5
Customer 3 has one unknown amount and one known twenty. MIN ignores the unknown candidate.
Show answer
medium MAX() Which sale rows are returned by the maximum-amount comparison?
A Only sale 106 because it is latest B Sales 103 and 105 C Every paid sale D Only sale 102
The maximum known amount is thirty, which belongs only to sale 102 in the fixture.
Show answer
What are COUNT(*), COUNT(amount) and COUNT(DISTINCT amount), respectively?
A 6, 5 and 4 B 6, 6 and 5 C 5, 5 and 4 D 6, 4 and 4
Six sales exist, five have known amounts, and the repeated twenty leaves four different known amounts.
Show answer
medium SUM() What is the known amount total for paid sales?
A 85 B 55 C 35 D 50
The paid known amounts are ten, twenty, twenty and five, totalling fifty-five.
Show answer
medium AVG() What average is returned for paid sales only?
A 17 B 11 C 13.75 D 18.333
The four paid known amounts total fifty-five; fifty-five divided by four is 13.75.
Show answer
How many rows remain in the window-total example for customer 1?
A One row showing customer_total 40 B Two rows showing totals 10 and 30 C Six rows, each showing the overall total 85 D Two rows, each showing customer_total 40
The window calculation retains both Ana sale rows and annotates each with the partition-wide total.
Show answer
Which customers pass HAVING COUNT(*)>=2 in the identified-sales summary?
A Customers 1 and 3 B Only customer 1 C Customers 1, 2 and 3 D Customers 3 and 4
Ana and Cara each have two real sale records, including Cara’s record with a missing amount.
Show answer
What city_key is returned for Dev by LOWER(city)?
A An empty string B NULL C The text 'null' D The text 'unknown'
LOWER propagates the missing city; no fallback string is included in that expression.
Show answer
What quarter_amount is returned for sale 106?
A 1 B 5 C 1.25 D 0.25
Five divided by the decimal operand four is 1.25; the calculation preserves the fraction.
Show answer
Which sale ids fall in the February half-open range?
A 101, 102 and 103 B 103, 104 and 105 C Only 104 D 104, 105 and 106
The included February 1 start and excluded March 1 end contain the three February dates.
Show answer
What city_label does COALESCE(city,'Unknown') return for Dev?
A Unknown B NULL C Leeds D An empty string
Dev’s city is NULL, so COALESCE selects the second text argument.
Show answer
What are the known_count and missing_count in the NULL-bucket exercise?
A 6 and 0 B 5 and 1 C 4 and 2 D 1 and 5
The complementary IS NOT NULL and IS NULL conditions allocate five known amounts and one missing amount.
Show answer
What value is returned by the explicit decimal quarter calculation for sale 106?
A 1 B 5.00 C 1.25 D 0
The decimal calculation divides the known amount five by four without using a truncated integer quotient.
Show answer
Which id appears last in the explicit NULL-last ascending sort?
A 102 B 106 C 105 D 104
Sale 104 has the missing amount and receives a sort key of one; known amounts receive zero.
Show answer
After PostgreSQL CREATE DATABASE training_reference succeeds, what else is required before defining tables there?
A Connect to training_reference explicitly B Nothing; the current connection switches automatically C Use the SQLite PRAGMA table_info command to switch databases D Insert a row into pg_database manually
Creating a PostgreSQL database does not reconnect the existing session to the new container.
Show answer
Which fields exist in lesson_notes after its ADD operation?
A Only id and note B id, note and extra_info C Only extra_info because ALTER replaces the original fields D id, note and label
ADD introduces extra_info while preserving the two columns from CREATE TABLE.
Show answer
Which sale ids have a known amount below twenty?
A 101, 104 and 106 B 103 and 105 C 101 and 106 D 102 and 104
Ten and five satisfy the numeric condition. The unknown amount is not known to be below twenty.
Show answer
How many rows are written into pending_sales_copy in the worked solution?
A Two: sales 102 and 104 B Four: every paid sale C Six: every source sale D One: sale 102
The pending filter finds two records, but the known-amount condition excludes sale 104.
Show answer
What row count appears in the paid snapshot made from the fixture?
A Four B Three C Five D Six
Paid sales 101, 103, 105 and 106 all contribute, including the anonymous paid sale.
Show answer
What status is saved when checked_sales omits the status column during insertion?
A NULL B pending C paid D An empty string
The DEFAULT supplies pending because the insertion omits the destination column.
Show answer
What id does Finn receive in the fresh uninterrupted SQLite example?
A 1 B 0 C 2 D NULL
Ema receives one and the next omitted INTEGER PRIMARY KEY value in this demonstration is two. This does not promise gap-free real-world numbering.
Show answer
Can customer_tags contain both (1,newsletter) and (2,newsletter)?
A No; tag is individually unique B No; every customer id may appear only once C Only if the composite primary key is removed D Yes; the complete customer-tag pairs differ
The composite key protects combination uniqueness, not individual uniqueness of each component.
Show answer
What remains after the SET NULL example deletes its practice parent?
A Child sale 1 remains with customer_id NULL B Child sale 1 is deleted C Child sale 1 keeps customer_id 1 D The parent is recreated automatically
ON DELETE SET NULL preserves the child and clears the reference through its nullable field.
Show answer
What are the smallest and largest known values in paid_stage?
A 5 and 30 B 5 and 20 C 10 and 20 D 0 and 20
The temporary stage contains only paid amounts ten, twenty, twenty and five; pending thirty is absent.
Show answer
How many rows appear in the full identified-customer INNER JOIN example?
A Four B Six C Seven D Five
Five sales have a matching customer; Dev has no sale and sale 106 has no identified partner.
Show answer
Which customer has a NULL paid_sale_id in the ON-filtered LEFT JOIN exercise?
A Dev B Cara C Ben D Ana
Dev is preserved from the left input but has no qualifying paid sale partner.
Show answer
Who is Dev’s immediate manager in the self-join output?
A Ana B Ben C Cara D NULL
Dev’s manager_id is two, which resolves to Ben. The query follows one level rather than the entire ancestry.
Show answer
Which customer is incorrectly added if the anti-join tests s.amount IS NULL instead of s.id IS NULL?
A Ana B Ben C Cara D No customer; the predicates always mean the same thing
Cara has a real sale with a missing amount, so a nullable amount is not evidence of no relationship.
Show answer
How many occurrences does the UNION ALL city-count exercise retain?
A Two B Three C Five D Four
Three known-city customer rows plus the extra Leeds row produce four occurrences when duplicates are retained.
Show answer
Which customer id remains in customers EXCEPT sales customer links?
A 4 B NULL C 1 D No ids because the sales input contains NULL
Set difference retains Dev’s id four, which is absent from the second selected set. Its NULL member does not poison set comparison.
Show answer
Which row survives the doubled_amount>40 derived-table exercise?
A Sales 103 and 105 with doubled_amount 40 B Every known-amount sale C Sale 102 with doubled_amount 60 D No rows because outer WHERE cannot see any inner aliases
The derived table exposes doubled_amount as an input column. Only thirty doubled to sixty exceeds forty.
Show answer
Which customer ids pass sale_count>=2 in the worked CTE exercise?
A Only 1 B 1, 2 and 3 C 3 and 4 D 1 and 3
The customer-level CTE counts two sales for Ana and Cara; the outer WHERE filters that exposed count.
Show answer
What root-relative depth does Dev have when the anchor is Ben?
A 1 B 0 C 2 D Dev is absent because only direct manager names are selected
Ben is depth zero and Dev is his direct child, so the recursive step gives Dev depth one.
Show answer
What missing_amounts value is reported for Dev by the guarded exercise?
A 1 B 0 C 2 D NULL
The sales.id presence guard prevents Dev’s no-sale placeholder from being counted as an incomplete real transaction.
Show answer
Your report must return exactly one latest record per customer. Which approach gives a dependable choice?
A RANK ordered only by customer name B DENSE_RANK without any ordering C ROW_NUMBER partitioned by customer, ordered by timestamp descending and a unique ID
Row numbering selects one row per partition when filtered to 1. A timestamp and unique ID establish which row wins, including equal timestamps.
Show answer
May follows March in a table where April is missing. What does LAG(amount) ordered by month return for May?
A March's amount, because March is the previous available row B Zero, because April is missing C An error, because monthly sequences must be continuous
LAG moves by row positions in the ordered input. It neither creates missing dates nor interprets their business meaning.
Show answer
Which frame calculates a three-observation moving average including the current row?
A ROWS BETWEEN 3 PRECEDING AND CURRENT ROW B ROWS BETWEEN 2 PRECEDING AND CURRENT ROW C ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
Two preceding rows plus the current row gives three observations. Three preceding rows would include four observations once enough history exists.
Show answer
Two sales share the same day. The report needs a total after each individual sale. Which design is clearest?
A RANGE ordered only by day, while expecting different totals for peers B A final ORDER BY with no window ordering C ROWS with a window order containing day and unique sale ID
A row frame and unique ordering specify the individual-sale sequence. A peer-aware day range intentionally includes tied day values together.
Show answer
Which query best matches an ordinary index on (customer_id, created_day)?
A Filter one customer and then search that customer's date range B Search only an unrelated amount column C Search the contents of a JSON field not included in the index
The leading customer column groups entries, and the following day column supports a range inside that group. Other search requirements need their own plan.
Show answer
You want to inspect a DELETE plan without changing rows. Which first step is appropriate?
A Assume every command containing EXPLAIN is read-only B Use the engine's estimated-plan facility in a controlled environment C Run execution analysis on the production DELETE immediately
Execution-analysis modes may run the statement. Estimated-plan facilities are the appropriate starting point when execution must not occur.
Show answer
An October timestamp report needs all times in October. Which boundary is clearest?
A timestamp <= October 31 at midnight only B Filter only by the month number without checking the year C timestamp >= October 1 and timestamp < November 1
The half-open range covers the complete month and its intended year without guessing a final fractional-second value.
Show answer
The conditional stock update affects zero rows. What should the application do?
A Treat the reservation as unsuccessful and avoid confirming a ticket B Confirm success because UPDATE had valid syntax C Subtract stock again without checking availability
A successful statement execution and a successful business operation are different. Zero affected rows means no available matching stock was changed.
Show answer
Two transfer paths update the same account pair in opposite orders. Which change reduces a common deadlock pattern?
A Wait for a user confirmation while holding the first lock B Make both paths acquire accounts in the same stable ID order C Ignore the second update's error and commit whatever happened
Consistent resource ordering reduces cycles. Longer lock holding increases contention, and ignoring errors can break the transfer's required result.
Show answer
A PostgreSQL discount function reads a changing price table. Is IMMUTABLE an appropriate declaration?
A Yes, every function is immutable by definition B Yes, if its name contains price C No, the same arguments can yield a different result when table data changes
Volatility declarations must match behavior. Reading mutable database state prevents the function from being a purely argument-dependent immutable calculation.
Show answer
An audit trigger should record actual price changes only. Which test is essential?
A Update a price to its current value and confirm no extra change record appears B Test only CREATE TABLE syntax C Assume every UPDATE changes the value
A statement can target a row without changing its price. The trigger's comparison must distinguish an actual change from a repeated assignment.
Show answer
Two physical copies share one book title. What needs separate identity?
A Only the title, with all copies sharing that identity B Only the borrower, with copy numbers stored as comma-separated text C Only the loan date, even when several copies are lent together D Each copy that can be lent independently
Lending operates on individual copies, not only descriptions.
Show answer
A diagram promises every confirmed order has an item. What is needed beyond the child foreign key?
A A checked confirmation rule or reviewed database mechanism B A foreign key from items to orders alone C A unique order ID without checking item existence D A nullable item reference stored in the order
The foreign key prevents orphan children but does not require a child to exist.
Show answer
One department needs many employees. Should employees.department_id be unique?
A Yes, because the parent must be uniquely referenced by all children B No, because several employees must reference the same department C Yes, because foreign keys always require unique child values D Yes, because uniqueness is needed to list the employees later
Uniqueness would restrict the relationship to at most one employee per department.
Show answer
Two requests both pass an email-exists pre-check. What enforces uniqueness?
A A preliminary SELECT without the database unique rule B A validation message generated before either write C A transaction whose statements never enforce the uniqueness rule D A database unique constraint
Concurrent requests can invalidate a pre-check.
Show answer
Two products have the same name but different supplier keys. What is correct?
A Apply the documented identity rule before merging B Merge all equal display names even when supplier identities differ C Delete the older row without reviewing its references D Treat product names as universally unique business keys
Display similarity is not necessarily business identity.
Show answer
A CSV is on your laptop but COPY runs on a remote PostgreSQL server. What fits?
A Server-side COPY using the laptop path as though it exists remotely B A client transfer such as psql \copy C Grant broad server-file access without transferring the file D Remove the CSV header and expect the server to find the local file
Client-side transfer avoids confusing client and server file locations.
Show answer
A replica immediately receives an accidental DELETE. What additional protection is needed?
A Replication alone because it automatically reverses user mistakes B A new replica created after the deleted state has propagated C A tested backup and point-in-time recovery strategy where supported D A larger read cache without any historical data copy
Replication does not automatically preserve a recoverable pre-mistake state.
Show answer
Reporting needs product names but no updates. What should be granted?
A Schema-administration privileges so future reports never fail B All privileges on every table including confidential data C UPDATE permission because reading implies editing D Only the required read access under the selected dialect
Grants should match the responsibility rather than convenience.
Show answer
A bound LIKE value contains %. What must be decided?
A Whether wildcards are intended or need separate pattern escaping B Preparation automatically converts percent to a literal character C HTML escaping removes all database wildcard meaning D Binding percent means the query cannot be resource-intensive
Binding prevents injection while preserving LIKE's pattern behavior.
Show answer
What is the stock value of BAG in the supplied data?
A 12, because the price is always the total B 3, because only quantity contributes C 36, calculated as twelve times three
Inventory value multiplies each item’s unit price by its quantity.
Show answer
Which count gives Beacon zero after the left join?
A COUNT(e.id) B COUNT(*) C COUNT(p.id)
Only a real matched entry has a non-NULL entry key.
Show answer
Why does invoice 3 appear before invoice 4 at the boundary?
A Ana always sorts before every other customer automatically B The two amounts tie, and id ascending resolves the tie C TOP ignores ORDER BY
The explicit unique-key ordering establishes a deterministic choice.
Show answer
How many remaining pages are known for Field?
A 80 B NULL C 60
The saved total eighty minus the known progress twenty is sixty.
Show answer
Why use a CASE missing-value sort key?
A To make NULL placement explicit across engines B To change NULL into the numeric value zero C To remove the need for ORDER BY
The CASE sorts by presence without replacing the actual measurement.
Show answer
How many employees share the second-highest distinct salary here?
A One because all salary values are unique B Two: Ben and Cara C Five because MAX counts employees
Both real employee rows have the second distinct value sixty.
Show answer
Which event is excluded by the user-day selection rule?
A Event 4 because January 4 is not consecutive B Event 6 because its type is click C Event 2 because event 1 has the smaller id for the same pair
The challenge selects one minimum-id record per user and day, not only consecutive or view events.
Show answer
Which department has two result records but only one known score?
A Advanced B Foundation C Optional
Cara has eighty and Dev has an unknown score, so the two count forms differ.
Show answer
What is Ana’s net paid value under this lesson’s definition?
A 140 because refunds do not count B 105 C 205 because order 1 has two refund rows
Ana has gross 140 and recorded refunds 35, giving 105.
Show answer
How many distinct users are active across the complete period?
A Six from adding the daily counts B Seven from counting event rows C Three
Distinct user identities across the period are 1, 2 and 3.
Show answer
No exercises match those filters.