Quiz: Prove your SQL Skills! Are you team SQL? Or noSQL?
Published Nov 8, 2024 3 min read Category Quiz Test your SQL Query Fundamentals
Has using an ORM been making you soft - in the skills dept?
Don’t worry, it happens to loads of devs.
Jump right in to prove your SQL Query Fundamentals! 👇
Which of the following SQL queries correctly retrieves rows where the name is “John”?
In SQL, single equal sign (=) is used for equality checks in the WHERE clause, not == or ===, which are JavaScript operators.
The correct syntax is SELECT * FROM users WHERE name = 'John';.
SELECT * FROM users WHERE name LIKE(John);
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name == "John";
SELECT * FROM users WHERE name === "John";
What does COUNT(column_name) do in SQL?
COUNT(column_name) counts the number of non-NULL values in a specified column. To count all rows, including NULLs, use COUNT(*).
You can also use COALESCE to ensure NULLs default to a non-NULL value. Something like: COUNT(COALESCE(column_name, 0)).
Counts all rows including NULL values
Counts only non-NULL values in a column
Counts NULL values as one unique value
Treats each NULL value as unique, similar to NaN !== NaN
What does a LEFT JOIN do in SQL?
A LEFT JOIN returns all rows from the left table and matched rows from the right table, with NULL for unmatched rows in the right table.
Returns one row per every matching row, ignores unmatched rows
Returns all rows from the left table, plus matching right rows
Returns all matched or unmatched rows, where NULLs indicate no match
Returns all rows from the right table, with any matching left rows
What does an INNER JOIN do in SQL?
An INNER JOIN returns rows where the join condition matches rows from both tables. Unmatched rows are not included in the result set.
Returns unmatched rows from both tables
Returns all rows from both tables, with NULLs for unmatched rows
Returns rows that satisfy the join condition in both tables
Returns unmatched rows from the right table
What is a correlated subquery in SQL?
A correlated subquery references columns from its outer query. Conceptually its result depends on the current outer row; the optimizer may transform execution, so a separate physical execution for every row is not guaranteed.
A subquery that runs once overall
A subquery used only in JOIN statements
A subquery that returns multiple columns only
A subquery that references columns from the outer query
What is the purpose of the WITH clause (Common Table Expression) in SQL?
The WITH clause, or Common Table Expression (CTE), is used to define a temporary result set that can be referenced within the main query. In PostgreSQL, WITH can be attached to statements such as SELECT, INSERT, UPDATE, DELETE, or MERGE.
This can help improve readability and maintainability of complex queries.
The syntax is:
Only allowed in DELETE statements
Used for aggregation functions
Used for defining views only
Defining a temporary result set for use in a larger SQL statement
What does the IS NULL operator do in SQL?
IS NULL checks if a specified column contains a NULL value. IS NOT NULL checks the opposite.
Checks if a value is NULL
Checks if a value is empty
Checks if a value is a string
Checks if a value is numeric
What does the IN operator do in SQL?
The IN operator filters rows to match any values in a specified list, often as an alternative to multiple OR conditions.
While it might return “unique rows” (and often does), it’s not necessarily the primary purpose.
An index is not required. A suitable B-tree index can help a selective IN filter, but the planner may prefer a sequential scan for many matches or a small table. UNIQUE enforces a data constraint; it is not a requirement for optimizing IN.
Returns unique rows
Requires an indexed column
Applies only to numeric columns
Returns rows matching values in a specified list
What does the COALESCE function do in SQL?
The COALESCE function returns the first non-NULL value in a specified list, useful for substituting default values when NULLs are encountered.
For example, COALESCE(column_name, 0) would return 0 if column_name is NULL.
Counts NULL values
Returns the last non-NULL argument
Returns the first non-NULL argument
Limited to IDENTITY columns
Originally from Printer Coalation
What is the purpose of the GROUP BY clause in SQL?
The GROUP BY clause groups rows that have the same values in specified columns, allowing aggregate functions to be applied on each group.
Hides duplicates
Only used in joins
Sorts rows in ascending order
Groups rows by the specified column(s)
Syntax should be GROUP WITH/USING
What does a FULL OUTER JOIN do in SQL?
A FULL OUTER JOIN returns all rows from both tables, filling unmatched rows with NULLs for missing values.
This is useful for comparing two tables and finding differences between them.
Returns only matched rows
Returns only rows with non-NULL values
Returns unmatched rows from the left table only
Returns unmatched rows from the right table only
Returns all rows from both tables, with NULLs for unmatched rows
