Declarative Queries: SELECT, Joins & Filtering

What exactly does SELECT DISTINCT country, city deduplicate?

What breaks if the subquery behind an IN predicate selects two columns?

IN and EXISTS can express the same filter. What does each actually check?

In CONTAINS((firstname,lastname,email),'andy',FUZZY(0.3)), what does the 0.3 do and what's the default?

INTERSECT and EXCEPT are rare in practice. What join/predicate replaces each, and what must the replacement add?

Find tables literally named P then _ ... Why is ESCAPE needed here? ``` WHERE table_name LIKE 'P$_%' ESCAPE '$' ```

Some rows have firstname NULL. What does query 2 return? ``` -- 1 WHERE firstname IS NULL -- 2 WHERE firstname = NULL ```

Each SELECT returns the row ('A','B'). How many rows come back? ``` SELECT 'A' c1,'B' c2 FROM dummy UNION SELECT 'A' c1,'B' c2 FROM dummy ```

In a SQLScript SELECT query, which clauses are required and in what order must they appear?

Can you page results with just OFFSET 5 to skip the first five rows, without a LIMIT?

procedures has 20 rows. What does this return? ``` SELECT * FROM procedures LIMIT 10 OFFSET 5; ```

You want all of t1's columns plus t2.column1, and you try to give the t1.* part an alias. What's the catch?

When must you use a searched CASE (CASE WHEN <cond> ...) instead of a simple CASE (CASE <expr> WHEN <val> ...)?

IF isn't allowed inside a field-list expression. How do you guard value1/value2 against a divide-by-zero?

Both tables have a table_name column. Why does this fail? ``` SELECT table_name FROM m_cs_tables AS tab JOIN m_cs_columns AS col ON tab.table_name = col.table_name; ```

department = 'HR'. What is the dept column? ``` CASE department WHEN 'IT' THEN 'IT dept' WHEN 'SA' THEN 'Sales' END AS dept ```

id = 42, title = 'Fix bug'. What is the task column? ``` SELECT id || ' - ' || title AS task FROM tasks; ```

colors and sizes each have 6 rows. How do these differ? ``` -- A SELECT * FROM colors sizes; -- B SELECT * FROM colors, sizes; ```

colors (6 rows) CROSS JOIN sizes (6 rows) returns how many rows, and why spell out CROSS JOIN?

records.currency holds EUR, EUR, ALL, USD. currencies holds EUR, USD, GBP. After INNER JOIN on currency, which rows disappear?

An equi join and a theta (non-equivalent) join look almost identical in syntax. Why prefer the equi join?

A LEFT OUTER JOIN preserves every left row. Can it ever return MORE rows than the left table has?

With leading data on the left, when would you pick RIGHT OUTER JOIN vs FULL OUTER JOIN?

You turn a CROSS JOIN LATERAL into a LEFT OUTER variant to keep left rows that have no subquery match. What ON condition do you write?

How do you pass a parameter to a traditional database view vs a calculation (column) view in the FROM clause?

Dropping an SQL FUNCTION (...) RETURNS TABLE (...) BEGIN...END straight into a FROM clause — what does it cost, and since which release is it allowed?

Both use LEFT OUTER JOIN. Same rows? ``` -- A ... ON r.cur = c.cur WHERE c.cur <> 'EUR' -- B ... ON r.cur = c.cur AND c.cur <> 'EUR' ```

What's the difference? ``` status = ANY (1, 2, 4) status <> ALL ( SELECT id FROM status WHERE is_final) ```