Which SQL statement reads the data of a table without changing it?
Answer : SELECT
SELECT reads the requested rows. INSERT adds, UPDATE modifies and DELETE removes rows.
Questions and answers: SQL & Databases
Revise SQL & Databases: here is a selection of questions of every level, with the answer and an explanation. To test yourself without seeing the answers, play the quizzes.
Play the SQL & Databases quizzesEasy
Which keyword placed after SELECT removes the identical rows from the result?
Answer : DISTINCT
DISTINCT keeps only one occurrence of each identical row in the result.
Which clause keeps only the rows that meet a condition?
Answer : WHERE
WHERE filters the rows according to a condition, for example WHERE age >= 18.
Which clause is used to sort the rows of a result?
Answer : ORDER BY
ORDER BY name ASC sorts in ascending order, DESC in descending order.
Which statement adds a new row to a table?
Answer : INSERT
INSERT INTO adds a row; UPDATE modifies existing rows, CREATE creates a table and ALTER changes its structure.
In the relational model, what is a row of a table called?
Answer : A tuple, also called a record
A row is a tuple, also called a record; a column corresponds to an attribute.
What is the role of a primary key in a table?
Answer : Uniquely identify each row
The primary key cannot be NULL or repeated, which makes it possible to identify each row without ambiguity.
In what year did Edgar F. Codd publish the relational model of databases?
Answer : 1970
Edgar F. Codd published the founding article of the relational model in 1970, on which SQL is based.
Medium
Which join keeps only the rows that have a match in both tables?
Answer : INNER JOIN
INNER JOIN keeps only the matches; LEFT JOIN keeps all the rows of the left table, with NULL where there is no match.
Which query counts the number of clients for each city?
Answer : SELECT ville, COUNT(*) FROM clients GROUP BY ville
GROUP BY ville groups the rows by city, and COUNT(*) counts the rows of each group.
Which clause filters the groups after a GROUP BY, based on an aggregate?
Answer : HAVING
WHERE filters the rows before grouping; HAVING filters the groups, for example HAVING COUNT(*) > 5.
Which aggregate function calculates the average of a numeric column?
Answer : AVG
AVG calculates the average of the non-NULL values of a numeric column.
Which condition finds the rows where a column has no value?
Answer : WHERE email IS NULL
A comparison with = or <> to NULL is never true: we use IS NULL or IS NOT NULL.
In SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes), what does the subquery provide?
Answer : It provides the list of the client_id values present in the commandes table
The subquery returns a list of values; the outer query keeps the customers whose id appears in this list.
What is the role of a foreign key?
Answer : Reference the primary key of another table
A foreign key references a key, usually the primary key of another table; the DBMS can refuse a value that is absent from that table.
Which operator combines the results of two queries while removing the duplicate rows?
Answer : UNION
UNION merges the results while removing duplicates; UNION ALL keeps them all, without this removal step.
Hard
What does the letter A stand for in the acronym ACID?
Answer : Atomicity
Atomicity: a transaction is executed entirely or not at all. ACID also covers consistency, isolation and durability.
Which ANSI isolation level forbids dirty reads but allows non-repeatable reads?
Answer : READ COMMITTED
READ COMMITTED only reads committed data, but a row can change between two reads within the same transaction.
What does the second normal form (2NF) require?
Answer : Being in 1NF and avoiding partial dependencies on a composite key
2NF assumes 1NF, then that no non-key attribute depends on only part of a composite key.
Which structure does InnoDB, the MySQL storage engine, use by default for its indexes?
Answer : A B+ tree
InnoDB organizes its indexes as B+ trees, whose leaf nodes are linked together for range scans.
What does the EXPLAIN command do when placed in front of a SELECT query?
Answer : Shows the planned execution without running the query
EXPLAIN shows the planned execution (indexes, join order) without running the query; EXPLAIN ANALYZE, on the other hand, runs it.
What is an ordinary SQL view, created with CREATE VIEW?
Answer : A name given to a SELECT query, re-run on every read
An ordinary view stores nothing: it is re-executed on every read. A materialized view, on the other hand, keeps its results.
What is a trigger in a SQL database?
Answer : SQL code executed automatically on an INSERT, UPDATE or DELETE on a table
A trigger runs automatically when an event occurs on a table; a stored procedure, on the other hand, is called explicitly with CALL.
What does the CAP theorem, proposed by Eric Brewer, state?
Answer : A distributed system cannot guarantee consistency, availability and partition tolerance all at once
When the network splits between nodes, a distributed system must choose between consistency and availability.
Related themes
Play the SQL & Databases quizzes