SQL TESTS
COMMON THEORY QUESTIONS
+ ANSWERS
QUESTION 01
Q) WHAT IS A RELATIONAL
DATABASE?
A relational database (based on the
mathematical theory of relations) is
a collection of tabular datasets
(columns & rows) that relate to
each other through shared columns
QUESTION 02
RELATIONAL
Q) EXPLAIN WHAT A
DATABASE MANAGEMENT
SYSTEM (RDBMS) IS
A Relational Database Management
System (RDBMS) is software that
allows you to create and access a
relational database, and execute
commands & queries on the data within
it
QUESTION 03
Q) EXPLAIN THE DIFFERENCE
BETWEEN AN INNER JOIN & A LEFT
JOIN
An inner join returns rows where
there is a match (on the join
condition) in BOTH tables
A left join returns all rows from
the left table, even if there are no
matches in the right table. Where
no match is found, we are returned
null values
QUESTION 04
Q) EXPLAIN THE DIFFERENCE
BETWEEN THE WHERE CLAUSE AND
THE HAVING CLAUSE
The WHERE clause is used to apply
conditions/filters BEFORE any
aggregation takes place (or where no
aggregation takes place)
The HAVING clause is used to apply
conditions/filters AFTER aggregation
takes place
QUESTION 05
Q) EXPLAIN THE DIFFERENCE
BETWEEN UNION & UNION ALL
Both are used to stack multiple
datasets together (provided the
datasets have matching columns)
UNION removes duplicate records
from the resulting dataset...UNION ALL
does not
>> UNION is often slower than UNION
ALL due to the extra resource
required to remove duplicates
QUESTION 06
Q) EXPLAIN THE DIFFERENCE
BETWEEN RANK & DENSE RANK
RANK will give tied values the same
ranking. Following the tie it will assign
the next ranking based on the number
of rows (essentially skipping rankings)
DENSE RANK also gives tied values the
same ranking but following the tie will
go to the next number sequentially
>> Example
For the values [ 10, 10, 10, 11, 12 ]
RANK gives [ 1, 1, 1, 4, 5 ]
DENSE RANK gives [ 1, 1, 1, 2, 3 ]
QUESTION 07
Q) EXPLAIN WHAT AN AGGREGATION
FUNCTION DOES (AND LIST 3)
An Aggregation Function performs
calculations on set of values, and
returns a single value. (When
aggregating value by group(s), we also
use the GROUP BY statement)
Common ones are...
[ COUNT, SUM, AVG, MAX, MIN ]
QUESTION 08
Q) EXPLAIN WHAT A WINDOW
FUNCTION DOES (AND LIST 3)
Window functions look at values from
a set of rows (the window) and based
on this, return a value for each row
Any of...
[ RANK, DENSE RANK, ROW NUMBER, NTILE,
LAG, LEAD, FIRST VALUE, LAST VALUE ]
QUESTION 09
Q) EXPLAIN THE DIFFERENCE
BETWEEN A FULL OUTER JOIN & A
CROSS JOIN
A CROSS JOIN returns all possible
combinations of rows in both tables
(also - there is no ON condition)
A FULL OUTER JOIN returns all
possible rows from each table. Where
a match is found (based on the join
condition) we are returned the values
from the other table, otherwise we
are returned null values.
>> A FULL OUTER JOIN is essentially
like executing both a left join and right
join at the same time
QUESTION 10
Q) WHAT IS MEANT BY THE "ORDER
OF EXECUTION" IN SQL
The "order of execution" is the order
in which clauses within a query are
evaluated by the system
The order is...
FROM
JOIN + ON
WHERE
GROUP BY
HAVING
SELECT (& Window Functions)
ORDER BY
LIMIT
Knowing this can be useful when
looking to optimise queries
