Which statement about SQL keywords is true?

They aren't case-sensitive, but uppercase is a common convention

What is a relational database?

A database that stores data in tables that can be linked by keys

A database that stores only numbers

What does SELECT name, city FROM customers; return?

Only the name and city columns, for every row

What does DISTINCT do?

Removes duplicate rows from the result

Deletes duplicates from the table

What's wrong with SELECT name price FROM products;?

It returns one column, name, labelled "price"

Nothing, it returns both columns

It always causes a syntax error

What does AS do in SELECT price * 1.2 AS with_tax?

Gives the calculated column a name

SQL279 questions in the bank

SQL Quiz: Test Your SQL Knowledge

Pick how many questions you want, choose a difficulty and go. You'll see why each answer is right or wrong, then you can retry to beat your score.

Questions

Difficulty

Topic

Mode

What does SQL stand for?
- Simple Query Logic
- Sequential Question Language
- Structured Query Language
- Server Quick Lookup
What does * mean in SELECT * FROM products?
- Multiply the columns
- All columns
- All tables
- The first row
Which statement about SQL keywords is true?
- They must always be uppercase
- They must always be lowercase
- Only SELECT is case-sensitive
- They aren't case-sensitive, but uppercase is a common convention
What is a relational database?
- A database that stores data in tables that can be linked by keys
- A database that stores only numbers
- A spreadsheet file
- A database for family trees
What does SQL stand for?
- Simple Query Logic
- System Quick List
- Structured Query Language
- Sorted Question Language
What does SELECT name, city FROM customers; return?
- Only the name and city columns, for every row
- Every column
- Only the first row
- The column names without data
What does DISTINCT do?
- Sorts the result
- Counts the rows
- Deletes duplicates from the table
- Removes duplicate rows from the result
Does an alias change the column name in the table?
- Yes, permanently
- No, only in the query's result
- Only if you use quotes
- Only for calculated columns
What's wrong with SELECT name price FROM products;?
- Nothing, it returns both columns
- It returns one column, name, labelled "price"
- It returns only price
- It always causes a syntax error
What does AS do in SELECT price * 1.2 AS with_tax?
- Filters the rows
- Sorts the result
- Creates a new table
- Gives the calculated column a name
Which clause filters rows in a SELECT query?
- FILTER
- WHERE
- FROM
- ORDER BY
How should you write a text value in a condition?
- Leeds
- (Leeds)
- {Leeds}
- 'Leeds'
How do you find rows where city has no value?
- WHERE city = NULL
- WHERE city = ''
- WHERE city IS NULL
- WHERE NULL(city)
Is price BETWEEN 10 AND 20 the same as price >= 10 AND price <= 20?
- No, BETWEEN excludes both ends
- No, BETWEEN excludes 20
- Only for whole numbers
- Yes, BETWEEN includes both ends
Which rows does WHERE price BETWEEN 5 AND 10 include?
- Prices above 5 and below 10 only
- Prices from 5 to 10, including 5 and 10
- Only prices of exactly 5 or 10
- Prices below 5 or above 10
Which condition finds rows where phone has a value?
- phone != NULL
- phone <> ''
- phone IS NOT NULL
- NOT phone = NULL
What is 10 + NULL?
- NULL
- 10
- 0
- An error
What's the difference between COUNT(*) and COUNT(email)?
- None
- COUNT(*) counts all rows; COUNT(email) counts rows where email isn't NULL
- COUNT(email) counts unique emails
- COUNT(*) skips NULLs
What does NULL OR TRUE evaluate to?
- NULL
- FALSE
- TRUE
- An error
Why can WHERE id NOT IN (subquery) return no rows unexpectedly?
- Subqueries can't be used with NOT IN
- If the subquery returns a NULL, every NOT IN comparison becomes unknown
- NOT IN only works with numbers
- The subquery runs once per row
What is the default sort direction of ORDER BY?
- Descending
- Ascending
- Random
- The order rows were added
What does GROUP BY category do?
- Sorts the rows by category
- Removes the category column
- Deletes duplicate categories from the table
- Creates one result row per category, for use with aggregate functions
Which function returns the number of rows?
- SUM()
- TOTAL()
- COUNT()
- NUMBER()
In which order must these clauses appear?
- SELECT, FROM, WHERE, GROUP BY, ORDER BY
- SELECT, WHERE, FROM, ORDER BY
- SELECT, FROM, ORDER BY, WHERE
- FROM, SELECT, GROUP BY, WHERE
What does ORDER BY price DESC do?
- Sorts cheapest first
- Sorts most expensive first
- Removes duplicate prices
- Groups by price
What kind of function returns one value for each row?
- An aggregate function
- A window function
- A scalar function
- A table function
What does ROUND(8.756, 1) return?
- 8.7
- 8.8
- 9
- 8.75
How do you join two pieces of text in standard SQL?
- With +
- With &
- With .
- With ||
What does COALESCE(phone, 'n/a') show when phone is NULL?
- n/a
- NULL
- An empty string
- An error
Why can WHERE LOWER(email) = 'a@b.com' be slow on a big table?
- LOWER is deprecated
- Text comparisons are always slow
- The function runs on every row, so a normal index on email can't be used
- It returns duplicate rows
What keyword must end every CASE expression?
- END
- STOP
- DONE
- ENDCASE
If two WHEN conditions are both true for a row, which result is used?
- The first one listed
- The last one listed
- Both, joined together
- Neither, it returns NULL
Where can a CASE expression be used?
- Only in SELECT
- Only in WHERE
- Only inside aggregate functions
- Anywhere a value is allowed, such as SELECT, ORDER BY and GROUP BY
What does SUM(CASE WHEN x > 0 THEN 1 ELSE 0 END) calculate?
- The total of x
- The average of x
- The number of rows where x is greater than 0
- Whether any row has x > 0
Why can't a simple CASE use WHEN NULL?
- NULL isn't allowed in CASE
- The simple form tests equality, and nothing is equal to NULL
- It must be written as WHEN 'NULL'
- It only works in MySQL
Which statement adds a new row?
- ADD
- UPDATE
- INSERT
- CREATE ROW
What happens if you run DELETE FROM orders; with no WHERE?
- Nothing, WHERE is required
- Only the last row is deleted
- The orders table itself is removed
- Every row in orders is deleted
What does CRUD stand for?
- Copy, Run, Undo, Drop
- Create, Run, Use, Delete
- Create, Read, Update, Delete
- Count, Read, Update, Debug
Which statement increases every Kitchen price by 2?
- UPDATE products price + 2 WHERE category = 'Kitchen';
- UPDATE products SET price = price + 2 WHERE category = 'Kitchen';
- INSERT INTO products SET price = price + 2;
- SET products.price = price + 2 WHERE category = 'Kitchen';
Which statement changes existing rows?
- UPDATE
- INSERT
- SELECT
- ALTER
Which function gives today's date in SQLite?
- date('now')
- NOW()
- TODAY()
- CURDATE()
Why should monthly reports group by year and month together?
- It's faster
- Grouping by month alone would combine the same month from different years
- Databases require it
- Months can't be grouped
Why do dates in YYYY-MM-DD format sort correctly even as text?
- Databases sort dates specially
- They contain hyphens
- They're shorter
- The largest unit comes first, so alphabetical order matches time order
In SQLite, what does strftime('%w', '2024-07-07') return for a Sunday?
- 7
- 1
- 0
- Sun
Why store timestamps in UTC?
- It avoids confusion from time zones and daylight-saving changes; convert when displaying
- UTC uses less storage
- Local time can't be stored
- SQL only supports UTC
What does DDL stand for?
- Data Download Language
- Database Design Logic
- Direct Data Link
- Data Definition Language
What does IF NOT EXISTS do in CREATE TABLE?
- Deletes the old table first
- Skips creation without an error if the table already exists
- Creates an empty copy
- Checks the data types
How do you write an auto-numbered id in MySQL?
- id INT AUTO_INCREMENT PRIMARY KEY
- id SERIAL
- id AUTO PRIMARY
- id INTEGER NUMBERED
What value do existing rows get in a newly added column without a default?
- 0
- An empty string
- NULL
- The value from the previous row
What does CREATE TABLE copy AS SELECT ... do?
- Creates a new table containing the rows the query returns
- Creates a view
- Renames a table
- Links two tables together
What does a constraint do?
- Enforces a rule on the data, rejecting changes that break it
- Speeds up queries
- Creates a backup
- Sorts the table
What does DEFAULT 1 do?
- Forces the value to always be 1
- Rejects values other than 1
- Sets the first row to 1
- Uses 1 when an INSERT doesn't give a value
What is referential integrity?
- Every table has an index
- Every foreign key value matches an existing row in the referenced table
- Every column has a name
- Data is encrypted
What must you run in SQLite before foreign keys are enforced?
- ENABLE KEYS;
- SET FOREIGN = TRUE;
- PRAGMA foreign_keys = ON;
- Nothing, they're always on
What is a composite primary key?
- A key made of text
- Two separate primary keys
- A foreign key that points to itself
- A primary key made of more than one column
What does a foreign key store?
- A password
- The primary key of a row in another table
- A copy of the whole related row
- The table's name
What does INNER JOIN return?
- All rows from both tables
- All rows from the left table
- Rows with no match
- Only rows that have a match in both tables
Why use aliases like o and c?
- They make queries shorter and clear up which table a column comes from
- They make queries run faster
- They rename the tables permanently
- They are required for every join
How do you find customers who have never placed an order?
- LEFT JOIN orders and keep rows WHERE o.id IS NULL
- INNER JOIN orders WHERE o.id = 0
- SELECT customers WHERE orders = NULL
- RIGHT JOIN customers WHERE c.id IS NULL
What does a table alias like customers c do?
- Copies the table
- Renames the table permanently
- Creates an index
- Gives the table a short name to use in the rest of the query
Which join would you rewrite as a LEFT JOIN by swapping the table order?
- INNER JOIN
- CROSS JOIN
- RIGHT JOIN
- FULL OUTER JOIN
In a self join of staff to staff, why use LEFT JOIN for managers?
- It's faster
- Self joins require LEFT JOIN
- To remove duplicates
- So employees without a manager, such as the director, still appear
Which database lacks FULL OUTER JOIN?
- PostgreSQL
- SQL Server
- SQLite 3.39+
- MySQL
What does FROM a, b without a WHERE condition produce?
- An error
- Every combination of rows, like a CROSS JOIN
- An inner join on id
- Only matching rows
Which query finds products never ordered?
- SELECT name FROM products JOIN orders ON orders.product_id = products.id
- SELECT name FROM products WHERE id = NULL
- SELECT p.name FROM products p LEFT JOIN orders o ON o.product_id = p.id WHERE o.id IS NULL
- SELECT name FROM products CROSS JOIN orders
What must every query in a UNION have in common?
- The same table
- The same WHERE clause
- The same number of columns, in compatible types and order
- The same number of rows
Why is UNION ALL usually faster than UNION?
- It uses an index
- It skips the second query
- It doesn't need to check for and remove duplicates
- It returns fewer rows
What does A EXCEPT B return?
- Rows in A that aren't in B
- Rows in both A and B
- Rows in B that aren't in A
- All rows except duplicates
How many ORDER BY clauses does a sorted UNION need?
- One, at the end
- One per query
- None, it sorts automatically
- Two
Will UNION treat "Leeds" and "Leeds " (with a space) as duplicates?
- Yes
- No, rows must match exactly
- Only in MySQL
- Only with UNION ALL
Where must a subquery be written?
- After a semicolon
- Inside brackets
- In a separate file
- Only after WHERE
What does a scalar subquery return?
- A list of values
- A whole table
- A single value
- True or false
What problem can NOT IN (subquery) have?
- It returns no rows if the subquery's list contains NULL
- It's always slow
- It ignores duplicates
- It only works with numbers
What does WITH define?
- A new permanent table
- An index
- A named subquery (CTE) used by the following query
- A transaction
What must a subquery in the FROM clause have in MySQL and PostgreSQL?
- A WHERE clause
- An ORDER BY
- A LIMIT
- An alias
What does LOWER('LYON') return?
- LYON
- lyon
- Lyon
- l
What's the safest first step before an UPDATE that cleans data?
- Drop the table
- Run the same expressions in a SELECT and check the results
- Turn off constraints
- Delete duplicates
In SQLite, what does CAST('n/a' AS INTEGER) return?
- 0
- NULL
- An error
- 'n/a'
After cleaning a column of emails, how do you stop duplicates returning?
- Run the cleanup every day
- Use UNION
- Add an index on id
- Add a UNIQUE constraint on the email column
Which pattern finds emails missing a dot after the @?
- email LIKE '%@%'
- email = '%.%'
- email NOT LIKE '%_@_%._%'
- email NOT LIKE '.'
Which relationship is "one customer, many orders"?
- One-to-one
- Many-to-many
- Self join
- One-to-many
What stops the same product and tag being linked twice in a junction table?
- A composite primary key (or UNIQUE constraint) on both columns
- An index on product_id
- ON DELETE CASCADE
- NOT NULL
What's the usual default behaviour when you delete a row that other rows reference?
- The other rows are deleted
- The references become NULL
- The delete is refused
- Nothing is checked
What's another name for a junction table?
- View
- Link or bridge table
- Index
- Temporary table
What does an entity relationship diagram show?
- Query speed
- Rows of data
- User permissions
- Tables and the relationships between them
What is the main goal of normalization?
- Faster typing
- Removing repeated data so each fact is stored once
- Making tables wider
- Using fewer keys
What does 2NF add to 1NF?
- Every table needs an index
- No NULLs are allowed
- Non-key columns must depend on the whole composite key
- Columns must be sorted
What's a repeating group?
- Columns like item1, item2, item3 that hold the same kind of value
- A GROUP BY clause
- Two identical rows
- A junction table
How do you get a flat report from a normalised database?
- Copy the data into a spreadsheet
- Denormalise every table
- Use UNION
- Join the tables in a query, optionally saved as a view
When can denormalisation make sense?
- For read-heavy reporting where speed is measured and data is rebuilt from a normalised source
- Always, it's simpler
- When you don't know SQL
- Never
What does a normal view store?
- A copy of the data
- An index
- A saved query
- A backup file
Are view results up to date when the underlying tables change?
- No, you must refresh normal views
- Only once a day
- Only in MySQL
- Yes, the query runs again each time the view is used
How do you change a view's query in SQLite?
- DROP VIEW and then CREATE VIEW again
- ALTER VIEW
- CREATE OR REPLACE VIEW
- UPDATE VIEW
What is a materialized view?
- A view with no columns
- A view that stores its results and is refreshed on demand
- A view that can't be dropped
- A view on one table only
How can a view help with data privacy?
- It can expose only some columns and rows, and users can be granted access to the view alone
- It encrypts the table
- It deletes personal data
- It prevents all queries
What's the main benefit of a CTE?
- It makes tables permanent
- It splits a complex query into readable, named steps
- It adds an index
- It locks the table
When should a CTE become a view instead?
- When many different queries need the same logic
- When it has more than two columns
- When it uses a join
- Never
Which problem do recursive CTEs solve well?
- Deleting duplicates
- Encrypting data
- Creating indexes
- Walking hierarchies like organisation charts and category trees
Why generate a series of dates before joining orders?
- To speed up the query
- So days with no orders still appear, with a count of 0
- To sort the orders
- Dates can't be grouped otherwise
What stops a recursive CTE?
- The LIMIT on the anchor
- A semicolon
- The recursive member returning no new rows, usually because of a WHERE condition
- The database after one round
What is the main purpose of an index?
- To find rows faster
- To store backups
- To enforce data types
- To join tables
In SQLite's EXPLAIN QUERY PLAN output, which word shows an index is used?
- SCAN
- SEARCH ... USING INDEX
- INDEXED
- FAST
What data structure do most database indexes use?
- A linked list
- A queue
- A B-tree
- A stack
Why can WHERE UPPER(name) = 'PEN' miss an index on name?
- The function changes the value, so the sorted index can't be used directly
- UPPER is slower than LOWER
- Indexes don't work with text
- It returns NULL
Which query benefits from an index on orders(order_date)?
- SELECT COUNT(*) FROM customers
- SELECT * FROM products WHERE price > 10
- INSERT INTO orders VALUES (...)
- SELECT * FROM orders WHERE order_date >= '2024-07-01' ORDER BY order_date
What is a transaction?
- A single SELECT statement
- A backup of the database
- A group of statements that succeed or fail together
- A type of index
What does autocommit mean?
- Changes are never saved
- Transactions commit every hour
- COMMIT is written for you at the end of a script
- Each statement is committed automatically as soon as it succeeds
What does the A in ACID stand for?
- Accuracy
- Atomicity
- Availability
- Access
What does ROLLBACK TO before_change do?
- Undoes changes made after the savepoint named before_change
- Cancels the whole transaction
- Creates a savepoint
- Commits up to the savepoint
What happens to an uncommitted transaction if the database server crashes?
- It's committed when the server restarts
- Half of it is saved
- The database is corrupted
- It's rolled back automatically
Which keyword marks a window function?
- OVER
- WINDOW
- PARTITION
- RANKED
What does PARTITION BY do inside OVER?
- Sorts the whole result
- Deletes duplicate rows
- Creates a new table
- Splits rows into groups so the calculation restarts for each group
Scores 50, 40, 40, 30 with RANK() descending give which ranks?
- 1, 2, 3, 4
- 1, 2, 2, 3
- 1, 2, 2, 4
- 1, 1, 2, 3
How do you calculate a running total of amount by date?
- SUM(amount) GROUP BY date
- SUM(amount) OVER (ORDER BY date)
- RUNNING(amount)
- TOTAL(amount) BY date
Why can't you filter on a window function's result in WHERE?
- WHERE only accepts numbers
- WHERE is evaluated before window functions are calculated
- Window functions return NULL in WHERE
- It's allowed only in PostgreSQL
What starts a trigger?
- A timer
- A SELECT query
- An INSERT, UPDATE or DELETE on its table
- Restarting the database
Which is better for "quantity must be at least 1"?
- A trigger
- A CHECK constraint
- A stored procedure
- A view
How do you run a stored procedure in MySQL?
- SELECT name();
- RUN name;
- EXEC TRIGGER name;
- CALL name();
Why might a developer be surprised by a trigger?
- Triggers slow down SELECT queries
- Triggers delete tables
- Triggers can't be dropped
- Triggers run silently, so an UPDATE can change other tables without the code showing it
Why put an audit log in a trigger rather than in application code?
- It records changes made by every program and person, not just one application
- It's faster to write
- Applications can't write to tables
- Triggers use less storage
Which is the best defence against SQL injection?
- Hiding error messages
- Shorter table names
- Using UPPER CASE keywords
- Parameterized queries
What does -- do in the injected input ' OR '1'='1' --?
- Subtracts a number
- Starts a comment, hiding the rest of the original query
- Ends the database connection
- Deletes a row
What is a database role?
- A named group of privileges that can be granted to users
- A table column
- A type of index
- A backup
Can a placeholder be used for a table name?
- Yes, always
- Only in SQLite
- Only with GRANT
- No, placeholders are for values; pick table names from a fixed list in code
How should user passwords be stored?
- As plain text in a protected table
- As salted hashes from a slow algorithm such as bcrypt or Argon2
- Encrypted with a key stored next to them
- Reversed and uppercased
Which text is a string literal?
- Leeds without quotes
- The column identifier city
- 'Leeds'
What does -- followed by a space start?
- A comment that continues to the end of the line
- A comment that ends only at a semicolon
- A text literal that becomes a selected value
Which binds more tightly without parentheses?
- AND binds more tightly than OR
- OR binds more tightly than AND
- AND and OR are always grouped strictly left to right
What happens to an unknown condition in WHERE?
- It is accepted as true because no false value is known
- The row is excluded unless the full condition becomes true
- It always rejects the row even when another OR branch is true
What does DISTINCT city, name compare?
- Only city, because it is selected first
- The combined city-and-name values
- Each selected column independently rather than the complete pair
What is COUNT(DISTINCT city) for this dataset?
- Three because the distinct NULL counts as a known city
- Four because both Leeds customers count separately
- 2 because it ignores NULL and counts Leeds and Porto once
What does name AS customer_name change?
- The stored column name for future queries
- The saved text in every customer name
- The output column label for this query
Why use c.id and s.id in a join?
- To identify the intended table when both have an id column
- To make each table id refer to the same stored value
- To require both tables to be ordered by id before joining
What does _ mean in a LIKE pattern?
- Exactly one character
- Zero or more characters
- One literal underscore in every pattern
Which pattern finds a literal fragment anywhere in text?
- Only a trailing percent, which restricts the match to a prefix
- Percent before and after the properly escaped fragment
- No percent signs, which compares the whole value with the fragment
Does amount BETWEEN 10 AND 20 include twenty?
- No; only the lower endpoint is inclusive
- Yes, both endpoints are included
- No; both endpoints are excluded
Which database does not implement ANY/ALL subquery comparison syntax?
- PostgreSQL
- SQL Server
- SQLite
What makes EXISTS true?
- The subquery returns exactly one row
- The subquery selects at least one non-NULL value even when no row qualifies
- The subquery returns at least one qualifying row
What establishes the customer-specific relationship?
- The inner comparison s.customer_id = c.id
- The constant SELECT 1 by itself
- The final outer ORDER BY c.id
Which predicate finds missing amounts?
- amount IS NULL
- amount = NULL
- amount = 'NULL'
Which column reliably detects a missing sale in this left join?
- sales.amount, because every real sale must have a known amount
- sales.id, because it is a non-NULL primary key for real sale rows
- customers.id, because the preserved customer proves a sale exists
What makes a top-two result reproducible when amounts tie?
- Ordering only by amount while allowing tied rows to be chosen arbitrarily
- ORDER BY amount with a unique id tie-breaker
- Relying on the source insertion order
Which syntax is used immediately after SELECT in SQL Server?
- LIMIT n at the end of SELECT
- FETCH FIRST before the selected column list
- TOP (n)
Which command changes selected existing rows?
- INSERT
- ALTER TABLE
- UPDATE
What is the main difference between DELETE and DROP TABLE?
- DELETE removes rows, while DROP TABLE removes the table object
- DELETE removes the schema object while DROP only clears rows
- Both remove rows while preserving every table definition
What does MIN(amount) return here?
- 5
- 0 because unknown amounts are treated as zero
- NULL because any missing amount makes the whole minimum unknown
What does MIN return over an empty input?
- Zero, the neutral numeric minimum
- NULL
- No output row at all for an aggregate without GROUP BY
What is the difference between MAX and SUM?
- MAX adds values while SUM chooses the largest
- MAX chooses the largest value; SUM adds the known values
- MAX and SUM both return the same total whenever NULL is present
Do MAX(amount) and MAX(created_on) necessarily describe one sale?
- Yes, both maxima necessarily select fields from one source row
- Yes, but only when no GROUP BY is written
- No, the independent maxima can come from different records
How many rows does COUNT(amount) count here?
- Six, because COUNT(amount) counts every sale record
- Four, because COUNT(amount) removes repeated numeric amounts
- 5
Which count correctly gives Dev zero sales after a LEFT JOIN?
- COUNT(s.id)
- COUNT(*), because the preserved placeholder is a sale
- COUNT(s.amount), because an unknown amount means no sale exists
What is SUM(amount) over this dataset?
- 85
- 65, because equal amounts contribute once
- NULL, because one sale amount is unknown
Why is SUM(DISTINCT amount) not the total revenue here?
- It removes the anonymous sale rather than duplicate amounts
- It combines equal amounts from different real sales
- It excludes every amount on a pending sale automatically
What denominator does AVG(amount) use here?
- All six sale rows, including the unknown measurement
- Five non-NULL amounts
- The four different known amount values
What is the paid-only average?
- 17, the overall known-amount average
- 11, calculated by dividing the paid total by all five known amounts
- 13.75
Which form keeps individual sale rows?
- SUM(amount) with GROUP BY customer_id
- A scalar SUM(amount) with no grouping
- SUM(amount) OVER (PARTITION BY customer_id)
Why is re-summing a repeated window total dangerous?
- The same partition total can appear on multiple detail rows
- Window totals always ignore repeated input records automatically
- A repeated total is guaranteed to describe a different group on every row
Where should a COUNT(*) threshold be tested?
- In HAVING after grouping
- In WHERE before the groups exist
- In ORDER BY, which filters low-count groups
How many recorded sales belong to Cara?
- One, because a missing amount is not a sale
- Two, including the sale with an unknown amount
- Zero, because any NULL amount invalidates the whole customer group
Does SELECT UPPER(name) alter saved names?
- Yes, it permanently replaces the stored names
- No, it transforms values in the query result
- Yes, but only names already containing lowercase letters
What does MySQL LENGTH measure?
- Characters including every trailing space
- Only ASCII letters rather than bytes
- Bytes rather than the character count
Why use 4.0 rather than 4 in the division example?
- To force integer division and discard the fraction
- To make ROUND return a formatted text label
- To express a fractional numeric calculation
What does NULLIF(total,0) do in a denominator?
- Changes a zero denominator to NULL
- Converts every denominator to zero before division
- Replaces only a NULL denominator with zero
Which range safely expresses a full calendar month?
- At or after its start and before the next month starts
- Strictly after the month starts and through its last midnight
- Any timestamp between the first and last date names, regardless of clock time
Which SQLite function commonly extracts formatted date parts?
- DATEPART
- strftime
- EXTRACT
What does COALESCE(city,'Unknown') choose first?
- The last argument regardless of which values are present
- The first non-NULL argument
- The first argument even when it is NULL
What does NULLIF(0,0) return?
- Zero because the first argument is zero
- True because the two values are equal
- NULL
Which expression counts only matching rows?
- COUNT(CASE WHEN condition THEN 1 ELSE 0 END)
- COUNT(0), which counts every present constant
- COUNT(CASE WHEN condition THEN 1 END)
Where should a condition go if it affects only one summary column?
- Inside that column’s CASE expression
- In a shared WHERE that removes rows from every measure
- In the final ORDER BY, which decides contribution categories
Which conversion syntax is standard-style?
- CAST(expression AS target_type)
- CONVERT(type,expression,style), which is identical in every dialect
- CAST(target_type,expression), with a comma-separated argument order
Why retain raw values during an import?
- To guarantee every malformed string becomes a valid zero
- To distinguish invalid source text from genuine converted values or missing input
- To make source text unnecessary even when conversion discards information
Why add id after amount in ORDER BY?
- To make every amount become unique by changing its saved value
- To resolve equal amounts with a deterministic tie-breaker
- To sort all ids before considering the amount
Which portable expression can put NULL amounts last?
- COALESCE(amount,0), which always preserves the original numeric order
- CASE WHEN amount IS NULL THEN 0 ELSE 1 END ascending, which puts NULL first
- CASE WHEN amount IS NULL THEN 1 ELSE 0 END as the first ascending sort key
What does DROP DATABASE remove?
- Only selected rows in the currently open table
- Only the database name while all tables stay available
- The named database container and its contents
How does SQLite normally create a database?
- By opening a database file or in-memory connection
- By issuing CREATE DATABASE through the SQLite server
- By issuing USE on the current SQLite connection
Which command adds a column to an existing table?
- ALTER TABLE
- INSERT INTO
- CREATE INDEX
What remains after DELETE removes every row?
- No table definition; DELETE drops the object when the last row is removed
- The table definition remains
- A table whose columns have all been converted to NULL
Which field is usually best treated as text?
- A quantity that must be added numerically
- A telephone number whose leading zeros matter
- A monetary measure whose arithmetic precision matters
What adds a nonnegative business rule to a numeric column?
- A VARCHAR declaration that makes every saved number positive
- A DEFAULT value, which rejects any alternative input
- A suitable CHECK constraint
Must the target table already exist?
- No, INSERT INTO SELECT always creates its destination
- Only when a selected column contains NULL
- Yes, INSERT INTO SELECT writes into an existing table
How are source expressions mapped to target columns?
- By their order in the SELECT and destination column list
- By matching source aliases to destination names automatically
- By matching alphabetical column names regardless of expression order
What does SQL Server SELECT INTO new_table do?
- Creates the new table and inserts the selected rows
- Appends selected rows to an already existing table without defining it
- Assigns a client variable and never creates a table
What is the table-creation alternative in SQLite?
- INSERT INTO a missing table without defining it
- CREATE TABLE ... AS SELECT ...
- SELECT ... INTO new_table, using the T-SQL form unchanged
When is the default normally used in this example?
- Whenever an explicit NULL is supplied, even with NOT NULL
- When the INSERT omits status
- Whenever a SELECT reads an existing row
Why add NOT NULL beside CHECK(amount>=0)?
- CHECK requires every amount to equal zero unless NOT NULL overrides it
- NOT NULL makes the check condition unnecessary because all known numbers are nonnegative
- The CHECK alone can accept an unknown amount
Why avoid MAX(id)+1 for key allocation?
- Because MAX cannot read a primary-key column
- Because adding one is always forbidden even inside a controlled allocator
- Concurrent writers can choose the same next value
Does SQLite require AUTOINCREMENT for ordinary generated rowids?
- No, INTEGER PRIMARY KEY can generate them without it
- Yes, no omitted key can ever be generated without AUTOINCREMENT
- Yes, and AUTOINCREMENT guarantees consecutive ids with no gaps
What does PRIMARY KEY(customer_id,tag) make unique?
- Each complete customer-and-tag combination
- Each customer_id independently
- Each tag independently
Does a surrogate id remove the need for business uniqueness rules?
- Yes; different generated ids make duplicate business facts impossible
- No, separate UNIQUE constraints may still be required
- Yes; a primary key automatically recognises every business reference
What does ON DELETE SET NULL do to a matching child?
- Deletes every matching child row
- Keeps the child row and clears its reference
- Rejects the parent change regardless of child nullability
What is normally required for SQLite foreign-key enforcement?
- Only declaring a column whose name ends in _id
- Issuing the PostgreSQL CREATE DATABASE command first
- Enable PRAGMA foreign_keys on the connection
Does a temporary table automatically refresh from its source?
- Yes, every SELECT reruns the original insertion source
- Yes, but only when the source and stage use the same column names
- No, it holds the rows inserted into it
Which SQL Server name commonly denotes a local temporary table?
- #paid_stage
- paid_stage without a prefix, which always becomes temporary
- ##paid_stage, which has global rather than local temporary-table semantics
What is metadata?
- Information describing database objects and their definitions
- Only the ordinary customer names stored as records
- A duplicate copy of each table’s business rows
Which SQLite command inspects ordinary table columns?
- DESCRIBE table_name, as universal SQLite syntax
- PRAGMA table_info(table_name)
- INFORMATION_SCHEMA.COLUMNS, which SQLite always provides as a built-in view
What happens to an input row without a qualifying partner?
- It is always preserved with NULL partner fields
- INNER JOIN excludes it from the result
- It is removed permanently from the source table
Which relationship connects these tables correctly?
- sales.id = customers.id
- sales.amount = customers.id
- sales.customer_id = customers.id
Which side does LEFT JOIN preserve?
- Only the right input
- Only rows that match both inputs
- The left input
Which field should detect an absent sale in this sample?
- The joined sale primary key rather than its nullable amount
- The joined nullable amount because NULL always proves no sale
- The customer primary key because every preserved customer must have a sale
How many rows result from crossing four customers with two statuses?
- Eight
- Six, by adding the two input counts
- Four, because CROSS JOIN returns the larger input only
Why are aliases required for a readable self join?
- They physically create two permanent copies of the original table
- They distinguish the separate roles of the same table
- They make one self join automatically traverse every hierarchy level
Which right-side column safely detects no sale here?
- sales.amount, which is missing only for nonexistent sales
- sales.id
- customers.id, which is present for every outer customer
Why can NOT IN fail for this relationship question?
- NOT IN automatically duplicates rows when the set repeats an id
- NOT IN compares only the first returned customer id
- The returned customer_id set includes NULL
Which operation preserves repeated selected rows?
- UNION without ALL
- SELECT DISTINCT over the combined selected rows
- UNION ALL
How do columns correspond between union branches?
- By position with compatible types
- By automatic matching of each branch’s column aliases
- By alphabetical column names instead of selected expression order
Is A EXCEPT B the same as B EXCEPT A?
- No, EXCEPT is directional
- Yes, EXCEPT is a symmetric difference in both directions
- Yes, whenever both inputs select a column with the same name
Does the NULL sales reference poison the shown EXCEPT result?
- Yes, one NULL removes every first-set row
- No, set comparison has its own duplicate and equality rules
- Yes, EXCEPT has exactly the same unknown-membership behaviour as NOT IN
Which reference makes the subquery correlated?
- Parentheses alone, even without any enclosing-row reference
- The inner predicate’s reference to c.id from the outer query
- The selected alias known_total by itself
Why does correlated COUNT give Dev zero?
- COUNT treats the left-side customer itself as one matching sale
- COUNT converts the empty input into one zero-valued sale record
- COUNT over his empty matching input returns zero
Where is a derived table commonly written?
- As an output alias without a SELECT input
- As a stored CREATE TABLE statement inside WHERE
- As a parenthesised SELECT in FROM
Why preaggregate two independent detail sources before joining?
- To avoid multiplying detail rows and inflating measures
- To remove all legitimate repeated monetary values automatically
- To guarantee every derived stage is physically cached by the optimiser
How long is an ordinary CTE name available?
- For the statement containing its WITH clause
- For the entire connection until it disconnects
- Permanently for every connection after the first WITH
What is the grain of customer_totals in the example?
- One row per individual paid sale
- One row per customer link with paid sales
- One row for the complete database regardless of customer link
What selects the starting rows?
- The recursive term without any anchor input
- The anchor term
- The final ORDER BY clause
Which comparison finds direct descendants in this model?
- child.id = current_node.manager_id, which walks toward parents
- child.id = current_node.id, which repeatedly matches the same node
- child.manager_id = current_node.id
What grouping grain gives one pivot row per customer?
- Customer identity and status as separate grouping dimensions
- Only status, which merges different customers into status rows
- Customer identity, with status tested inside CASE
Why include s.id IS NOT NULL in the missing-amount count?
- To distinguish a real incomplete sale from an outer-join placeholder
- To force every unknown amount to become a stored zero
- To exclude every sale that has a known id and amount
For values 100, 80, 80 and 50 ordered descending, what does RANK return?
- 1, 2, 2, 4
- 1, 2, 3, 4
- 1, 2, 2, 3
What happens if employee_id is added to a RANK window order where every employee ID is unique?
- All employees get rank 1
- Equal sales amounts no longer tie under that complete ordering
- It changes display order without affecting the rank calculation
Which function reads a value from a following row?
- LAG
- LEAD
- COUNT
What does LAG(amount, 1, 0) do when a previous row exists but its amount is NULL?
- It always returns the default 0
- It skips the previous row automatically
- It returns that stored NULL
For amounts 10, 20 and 30, what is the running total on the third row?
- 30
- 20
- 60
Why can a three-row moving average cover more than three calendar days?
- Some calendar days may have no rows in the input
- AVG always skips weekends
- ROWS automatically expands to a whole month
What does PARTITION BY customer_id change?
- Each customer's window calculation uses an independent group of rows
- It combines every customer into one stored row
- It changes customer IDs permanently
Why can LAST_VALUE return the current row's value?
- LAST_VALUE always ignores ordering
- Its active frame may end at the current row
- It always reads the last inserted record
What should adding a normal index change?
- The correct result rows of every SELECT
- The possible access paths and maintenance work
- All NULL values into zero
When is an index covering?
- Whenever it contains at least two columns
- Whenever its name starts with idx
- When it supplies all values needed by a particular query
What does a query execution plan describe?
- The HTML layout of a database report
- A permanent guarantee that the query always uses one index
- The operations the database chooses to produce the query result
An estimated plan shows cost 50. Can you report that the query takes 50 milliseconds?
- No, cost units are engine-specific estimates
- Yes, every SQL engine measures costs in milliseconds
- Yes, unless the query contains a join
What must an optimized rewrite preserve?
- The query's required result semantics
- The original whitespace
- The original index name at all costs
Why can joining two one-to-many child tables inflate a total?
- JOIN automatically rounds all monetary values
- Each child combination can repeat the parent and other child values
- SUM always counts each table only once
Which ACID property groups supported transaction changes into all-or-nothing work?
- Alphabetical order
- Atomicity
- Pagination
What should happen after a retryable serialization failure?
- Continue from the middle with values read before the failure
- Assume the database already confirmed the business operation
- Retry the complete logical transaction under a bounded retry policy
What distinguishes a deadlock from ordinary blocking?
- Any query lasting more than one second
- Every use of a transaction
- A cycle where transactions wait on resources held by one another
What should a deadlock retry usually repeat?
- The complete logical transaction with fresh reads
- Only the failed final statement using stale values
- Every external payment without an idempotency policy
Which PostgreSQL command invokes a procedure?
- CALL
- ORDER BY
- PARTITION BY
Which comparison is safe across SQL engines?
- Functions can never change data in any engine
- Check each engine's restrictions before relying on function side effects or procedure transaction control
- Procedures always commit independently of their callers
What activates a database trigger?
- A reader opening any HTML page
- A configured database event such as an insert, update or delete
- Only an explicit CALL from the application
Why is a SQL Server trigger written for one scalar inserted row risky?
- SQL Server forbids every multi-row update
- Triggers run only when a table is empty
- One statement can populate inserted and deleted with multiple rows
Why separate a course from its scheduled sessions?
- A course and a scheduled session must always share one primary key
- One course can occur at different times with separate bookings
- A course describes a person while a session describes a price
- A course can never have more than one date
What should be clarified before enforcing a unique booking pair?
- Whether student names should be used as primary keys
- Whether every booking can omit its session reference
- Whether canceled and repeated bookings need retained history
- Whether a foreign key automatically preserves all cancellation history
What implements the required order-to-customer link?
- A nullable foreign key with no required-reference rule
- A customer name copied into the order without a key
- A non-null foreign key in orders
- A list of order IDs stored in the customer row
What enforces at most one profile for a customer?
- A foreign key alone with repeated customer values permitted
- NOT NULL alone on the profile name
- A report that uses DISTINCT to hide duplicate profiles
- A unique customer reference in the profile table
What normally implements many-to-many?
- A comma-separated list of related IDs in one parent column
- A foreign key on one table that can name only one related row
- A repeated set of session1 and session2 columns
- A junction table with two foreign keys
What changes a child foreign key into an at-most-one link?
- Uniqueness on the referencing key
- NOT NULL alone on the referencing key
- A foreign key alone without a unique constraint
- Using LEFT JOIN in every report
What causes the 2NF issue in composite-key enrollments?
- A student name depends on student_id alone
- A grade depends on the whole student-course pair
- The table uses an integer surrogate ID
- A student name contains more than one word
What does BCNF require of a nontrivial dependency determinant?
- It must be the selected primary key specifically, excluding other superkeys
- It must be a superkey
- It must determine only one non-key column
- It must appear in every foreign-key declaration
Which combination requires a nonnegative price to be present?
- CHECK(price >= 0) alone while price remains nullable
- NOT NULL plus CHECK(price >= 0)
- NOT NULL alone with no value-range rule
- A DEFAULT 0 while null and negative values remain allowed
What does a child foreign key prevent?
- Every parent having no children
- Every valid reference pointing to an inactive parent under application rules
- A non-null reference to a nonexistent permitted parent
- Every unauthorized user reading an otherwise valid row
What makes a survivor ranking deterministic when timestamps tie?
- ORDER BY updated_at alone when several timestamps are equal
- A GROUP BY without any survivor preference
- A unique tie-breaker such as id
- Selecting an arbitrary MIN(name) as the surviving record ID
What should happen before deleting a referenced duplicate customer?
- Delete the parent first and assume all child references repair themselves
- Disable foreign keys permanently before deletion
- Leave orders attached to losing IDs and hide them from reports
- Review and migrate child references through a survivor mapping
Why use text staging columns for an inconsistent feed?
- To allow malformed fields to enter production unchanged
- To let implicit casts decide the supplier's business formats
- To replace all future integrity constraints with manual review
- Validate representations before converting and promoting them
What should a bulk-load success report include?
- Accepted, rejected and warning outcomes under the chosen policy
- Only a successful command exit without checking warnings or rejected rows
- Only the number of columns in the destination table
- Only the CSV filename and its modification time
What does RPO describe?
- The tolerated data-loss interval
- The maximum duration of restoring service after a failure
- The retention period of the oldest backup only
- The amount of RAM needed by the backup tool
What best proves a backup is usable?
- A completed upload without attempting recovery
- A tested restore with meaningful data and application checks
- A backup filename that includes the current date
- A replica being online without testing historical recovery
Which account should normally run public web requests?
- A superuser to avoid future permission errors
- A role with only required runtime privileges
- The migration owner reused for every web request
- One unrestricted identity shared by reporting and runtime
Why test as the intended role rather than the table owner?
- The owner has exactly the same permissions as every role
- Role names override all inherited privileges automatically
- Ownership can grant capabilities the role lacks
- A successful administrator query proves the runtime can execute it
What is the essential parameterization boundary?
- Trimming input and joining it into quoted SQL text
- Removing a few suspicious keywords before concatenation
- SQL structure is separate from supplied values
- HTML-escaping the input and then inserting it into SQL
What should handle a user-selected sort column?
- A normal value placeholder used as a column identifier
- Raw submitted text concatenated into ORDER BY
- A stored procedure that concatenates the same unchecked sort text
- An application-owned allowlist of SQL identifiers
Which feature generates omitted ids in this MySQL table?
- AUTO_INCREMENT
- A column alias
- ORDER BY quantity
Does MySQL provide native FULL OUTER JOIN?
- Yes, with the same syntax as every other database
- No; the required full-match result needs a correct alternative
- Only when all prices are decimal
Which PostgreSQL feature retrieves an inserted key in the same statement?
- USE database_name
- RETURNING
- SELECT MAX(id) without a transaction
Does CHECK(hours>=0) alone require hours to be present?
- Yes, every CHECK forbids NULL
- Only when the project name is Atlas
- No, add NOT NULL if presence is required
Which T-SQL feature generates the sample ids?
- AUTO_INCREMENT
- A client GO separator
- IDENTITY(1,1)
What does GO represent in common SQL Server scripts?
- A client-interpreted batch separator
- A WHERE condition
- A portable SQL command that changes a database
What normally creates or opens a SQLite database?
- Opening a file or in-memory database through a connection
- CREATE DATABASE followed by USE
- A MySQL ENGINE clause
What is normally needed to enforce SQLite foreign keys?
- Only give every reference a name ending in _id
- Enable foreign_keys on the relevant connection
- Use ORDER BY on every INSERT
Which engine lacks native FULL OUTER JOIN?
- PostgreSQL
- MySQL
- SQL Server
Does SQLite DECIMAL enforce server-style fixed precision?
- Yes, every DECIMAL declaration enforces the same representation
- Only when a SELECT contains DISTINCT
- No, ordinary SQLite uses type affinity instead
Which function gives peers the same rank without rank-number gaps?
- ROW_NUMBER
- COUNT(*)
- DENSE_RANK
Which predicate tests an unknown salary?
- salary IS NULL
- salary = NULL
- salary = 'NULL'
What does PARTITION BY user_id,event_day establish?
- Separate ranking groups for each complete user-day pair
- One global rank for all users
- A permanent unique constraint on the table
What should a second event on an existing day change?
- Every existing day must be deleted
- Event count can increase while distinct active-day count stays unchanged
- Distinct-day count must always increase
Which clause tests a grouped result-count threshold?
- A same-level WHERE COUNT(*) condition
- HAVING
- AS before the table alias
Which function selects one deterministic highest-score row with an id tie-breaker?
- COUNT(*) without grouping
- MAX(score) with an arbitrary ungrouped name
- ROW_NUMBER over the department partition
Why aggregate refunds by order before the join?
- To remove every refund event permanently
- To make all customers share one id
- To preserve one prepared row per order and avoid multiplying gross amounts
What makes a monthly revenue query well-defined?
- An explicit population, event timing, currency and completeness policy
- Using SUM without checking the source relationship
- Relying on whichever date field is easiest to type
Why is January 3 known purchase value NULL?
- Its purchase has no known amount to contribute
- The purchase event does not exist
- Every purchase is automatically refunded
What is needed for a moving window representing calendar days?
- Assuming every observed row always represents every adjacent day
- A suitable complete-calendar input and an explicit frame policy
- Removing every day with missing amounts

RelaxedNo clock. Read the explanation after each answer.

Timed30 seconds per question. Great for revision.

Exam modeNo feedback until the end, then a full review.

About the SQL quiz

This SQL quiz checks the ideas from every lesson, from reading a table with SELECT to ranking rows with window functions. Many questions ask what a query returns for the course's example shop database, with its five customers, six products and six orders, so it helps to keep those tables in mind. Others check the vocabulary you'll need when reading documentation and error messages, such as "primary key", "aggregate", "constraint" and "transaction".

If you're still in the beginner stage, choose Easy and a single topic such as WHERE or ORDER BY. After the intermediate lessons, try Mixed with 10 questions to practise switching between filtering, joining and changing data. Save Exam mode for the end of the course, when you want an honest picture of what you remember without seeing answers along the way.

Most SQL mistakes come from a handful of details: forgetting that BETWEEN includes both ends, comparing with = NULL instead of IS NULL, mixing AND and OR without brackets, or leaving out the WHERE in an UPDATE. When a question catches you out, read the explanation and revisit the linked lesson's "common mistakes" section. Recognising those traps in a quiz is the best way to avoid them in a real database.

Reference lessons add questions to this quiz too. Pick a reference topic to practise it on its own, or take a mixed quiz once you've finished the 25 core chapters to check everything together.

The question bank holds 279 questions drawn from 102 SQL lessons: 86 easy, 167 medium and 26 hard. Every time you start, the quiz picks a fresh random selection that matches your settings, so retaking it is a good way to practise. After each answer, or at the end in exam mode, you'll see why the right answer is right, and every question you miss links back to the lesson that teaches it.

SQL Quiz: Test Your SQL Knowledge

About the SQL quiz

What the SQL quiz covers

Beginner

Intermediate

Advanced

Syntax and commands reference

Functions and querying reference

Database and table reference

Joins and advanced queries reference

Windows and performance reference

Practical SQL

Dialects and SQL practice