Data Analytics Data Wrangling and SQL Syllabus

List the three main types of cardinality in an ER model.

One-to-one (1:1): each row in A relates to at most one row in B. One-to-many (1:N): one row in A relates to many rows in B. Many-to-many (M:N): rows in A relate to many in B and vice versa, usually resolved with a junction/bridge table.

How is a many-to-many relationship implemented in a relational database?

It is implemented with a junction (bridge/associative) table that holds foreign keys pointing to the primary keys of both related tables. Each row represents one pairing, turning the M:N relationship into two 1:N relationships.

What do the SELECT and FROM clauses do in a SQL query?

SELECT specifies which columns (or expressions) to return in the result set. FROM specifies the table (or tables) from which to retrieve the rows. SELECT * returns all columns.

What does the WHERE clause do in SQL, and give an example.

WHERE filters rows, returning only those that satisfy a condition, before any grouping. Example: SELECT name FROM customers WHERE country = 'US' AND age >= 18.

In a SQL WHERE clause, what is the difference between the IN, BETWEEN, and LIKE operators?

IN tests whether a value matches any item in a list, e.g. country IN ('US','UK'). BETWEEN tests an inclusive range, e.g. age BETWEEN 18 AND 30. LIKE matches a text pattern using wildcards % (any string) and _ (single character).

How do you filter for NULL values in SQL, and why can't you use = NULL?

Use IS NULL or IS NOT NULL. You cannot use = NULL because NULL represents an unknown value; any comparison with NULL using = or <> yields UNKNOWN (not TRUE), so no rows would match.

What does the ORDER BY clause do, and what is its default sort direction?

ORDER BY sorts the result set by one or more columns. The default direction is ascending (ASC). Use DESC for descending order, e.g. ORDER BY sales DESC, name ASC.

What does the LIMIT clause do in SQL?

LIMIT restricts the number of rows returned by the query, e.g. LIMIT 10 returns at most 10 rows. Combined with ORDER BY it gives 'top N' results; OFFSET can skip rows for pagination.

What does the DISTINCT keyword do in a SELECT statement?

DISTINCT removes duplicate rows from the result set, returning only unique combinations of the selected columns. Example: SELECT DISTINCT country FROM customers returns each country once.

What is a column alias in SQL, and how is it created?

An alias is a temporary, readable name given to a column or expression in the result set, created with the AS keyword (AS is optional). Example: SELECT price * quantity AS total_cost. Table aliases work similarly, e.g. FROM customers AS c.

What does the GROUP BY clause do in SQL?

GROUP BY groups rows that share the same values in specified columns into summary rows, so aggregate functions can be applied per group. Example: SELECT country, COUNT(*) FROM customers GROUP BY country.

What is the difference between the WHERE and HAVING clauses in SQL?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. Example: HAVING COUNT(*) > 5 keeps only groups with more than 5 rows.

List the five main SQL aggregate functions and what each computes.

COUNT: number of rows/values. SUM: total of numeric values. AVG: arithmetic mean. MIN: smallest value. MAX: largest value. All operate over a group (or the whole table if no GROUP BY).

How does COUNT(*) differ from COUNT(column_name) in SQL?

COUNT(*) counts all rows including those with NULLs. COUNT(column_name) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-null values.

What is the logical execution order of the main SQL clauses (FROM through ORDER BY)?

FROM (and JOINs) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This is why aliases defined in SELECT usually can't be used in WHERE but can be used in ORDER BY.

What does an INNER JOIN return?

An INNER JOIN returns only the rows that have matching values in both tables based on the join condition. Rows with no match in either table are excluded.

What is the difference between a LEFT JOIN and a RIGHT JOIN?

A LEFT JOIN returns all rows from the left (first) table plus matching rows from the right, with NULLs where no match exists. A RIGHT JOIN returns all rows from the right (second) table plus matching rows from the left, with NULLs where no match exists.

What does a FULL OUTER JOIN return?

A FULL OUTER JOIN returns all rows from both tables: matched rows are combined, and unmatched rows from either side appear with NULLs for the missing side's columns.

What is a CROSS JOIN, and how many rows does it produce?

A CROSS JOIN returns the Cartesian product of two tables: every row of the first table paired with every row of the second. If the tables have $m$ and $n$ rows, the result has $m \times n$ rows.

What is a self join in SQL, and when is it used?

A self join is a join of a table to itself, using table aliases to distinguish the two copies. It is used for hierarchical or comparative data within one table, e.g. matching employees to their managers stored in the same employees table.

What is the basic syntax to join two tables on a matching key in SQL?

SELECT columns FROM tableA a INNER JOIN tableB b ON a.key = b.key. The ON clause specifies the matching condition; using aliases (a, b) qualifies which table each column comes from.

What does the UNION operator do in SQL, and what are its requirements?

UNION combines the result sets of two or more SELECT statements into one, stacking rows vertically. Each SELECT must have the same number of columns with compatible data types in the same order. UNION also removes duplicate rows.

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result (requiring a de-duplication step). UNION ALL keeps all rows including duplicates and is therefore faster because no duplicate check is performed.

How does a UNION differ conceptually from a JOIN?

A JOIN combines tables horizontally, adding columns by matching rows on a condition. A UNION combines queries vertically, stacking rows on top of each other; the queries must have compatible column structures.

What is a subquery in SQL?

A subquery (inner query) is a query nested inside another SQL statement. It can appear in the SELECT, FROM, or WHERE clause and provides a value, a list, or a derived table used by the outer query.

What is the difference between a correlated and a non-correlated (self-contained) subquery?

A non-correlated subquery runs independently once and passes its result to the outer query. A correlated subquery references columns from the outer query and is re-evaluated for each row the outer query processes, making it typically slower.

When must you use IN (or ANY) versus = with a subquery in a WHERE clause?

Use = only when the subquery is guaranteed to return a single value (one row, one column). Use IN (or ANY/ALL) when the subquery returns multiple values, e.g. WHERE id IN (SELECT id FROM orders).

What is a Common Table Expression (CTE), and what keyword introduces it?

A CTE is a named, temporary result set defined at the start of a query with the WITH keyword and referenced within that single statement. Syntax: WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name. It improves readability of complex queries.

What are two advantages of using a CTE over a nested subquery?

(1) Readability/reusability: a CTE is named and can be referenced multiple times in the same query instead of repeating a subquery. (2) It allows recursion (recursive CTEs) for hierarchical data, which plain subqueries cannot do; it also lets you structure logic top-to-bottom.

What is a recursive CTE used for?

A recursive CTE references itself to process hierarchical or tree-structured data, such as organizational charts, bill-of-materials, or graph traversals. It has an anchor member (base case) combined via UNION ALL with a recursive member that repeats until no new rows are produced.

In SQL, what is the difference between using EXISTS and IN with a subquery for filtering?

IN compares a column's value against the list of values returned by the subquery. EXISTS returns TRUE as soon as the (usually correlated) subquery returns any row, stopping early. EXISTS is often more efficient for large subquery results and handles NULLs more predictably than IN.