SQL Flashcards
(37 cards)
What is the difference between WHERE and HAVING?
WHERE filters rows before aggregation. HAVING filters after aggregation.
Example: SELECT department, COUNT() FROM employees GROUP BY department HAVING COUNT() > 10;
How would you find duplicate records in a table?
SELECT column1, COUNT() FROM table_name GROUP BY column1 HAVING COUNT() > 1.
This returns records where column1 appears more than once — useful in data quality checks.
What SQL functions would you use to handle missing or NULL values?
COALESCE() returns the first non-null value. IS NULL / IS NOT NULL to filter nulls.
Example: SELECT COALESCE(phone_number, ‘Missing’) FROM customers;
How would you retrieve the top 5 highest risk scores from a table?
SELECT * FROM risk_scores ORDER BY score DESC LIMIT 5.
In Oracle: SELECT * FROM ( SELECT * FROM risk_scores ORDER BY score DESC ) WHERE ROWNUM <= 5;
How would you join two tables and only include records that exist in both?
Use an INNER JOIN: SELECT a.id, a.name, b.balance FROM accounts a INNER JOIN balances b ON a.id = b.account_id.
How would you calculate the average loan amount by customer type?
SELECT customer_type, AVG(loan_amount) AS avg_loan FROM loans GROUP BY customer_type.
What’s the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN?
INNER JOIN: Only matching rows in both tables. LEFT JOIN: All rows from the left table, with NULLs for non-matches on the right. FULL OUTER JOIN: All rows from both tables; NULLs where there’s no match.
How would you write a query to identify accounts with transactions above the 90th percentile?
SELECT * FROM transactions WHERE amount > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) FROM transactions).
Not all databases support PERCENTILE_CONT; fallback could be calculating it in Python.
How would you check if your joins are inflating row counts?
Compare row counts before and after the join: SELECT COUNT() FROM table1; SELECT COUNT() FROM table2; SELECT COUNT(*) FROM table1 JOIN table2 ON condition.
Also useful: check for duplicate keys using GROUP BY key HAVING COUNT(*) > 1.
How would you write a query to validate that for each model run, there’s a test result logged?
SELECT m.model_id FROM model_runs m LEFT JOIN test_results t ON m.model_id = t.model_id WHERE t.model_id IS NULL.
What is a primary key
A unique none nullable identifier that for each record on the table consisting of one or more columns, however each table can only have one primary key
What is a join
Function that allows to combine tables based on some logic from the columns that a user defines. (Join two tables together)
What are the main types of join
Inner, outer, left, and right.
Difference between SQL and MYSQL
SQL is a coding language and MYSQL is piece of software or a relational database management tool
What’s the difference between DELETE and TRUNCATE?
DELETE is a row-by-row operation with a WHERE clause, allows rollback, is fully logged, and fires triggers. TRUNCATE is a bulk operation without a WHERE clause, sometimes allows rollback, has minimal logging, and does not fire triggers.
Use DELETE for selective removal with logic or triggers. Use TRUNCATE for fast full-table wipes.
What is a Virtual Table?
A virtual table is a result set returned by a query, not a real stored table. Common examples include Views and CTEs. They represent a transformation of other data.
What is a CTE (Common Table Expression)? Why use it?
A CTE is a temporary result set defined using WITH that can be referenced in a main SQL query. It improves readability, avoids repeated subqueries, and supports recursive queries. (Only accessed until the dml statement is accessed)
Example: WITH high_risk AS (SELECT * FROM risk_scores WHERE score > 90) SELECT customer_id FROM high_risk WHERE region = ‘West’;
What are the different types of Indexes in SQL?
Common types of indexes include B-Tree Index, Bitmap Index, Unique Index, Composite Index, Full-Text Index, and Hash Index (in some systems). They speed up data retrieval.
What’s the difference between a View and a CTE?
A View is stored in the database and can be reused across queries, while a CTE is temporary for the duration of a query and exists only within that query.
Use View for a reusable reporting layer and CTE for breaking complex logic inside one query.
What are Primary Key and Foreign Key?
A Primary Key uniquely identifies each row in a table and must be unique and NOT NULL. A Foreign Key is a field in one table that refers to the Primary Key in another, enforcing referential integrity.
What are INNER, LEFT, RIGHT, and FULL OUTER joins?
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right. RIGHT JOIN returns all rows from the right and matching from the left. FULL OUTER JOIN returns all rows from both tables, matched where possible.
What is the logical order of SQL query execution?
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT/OFFSET
What’s the difference between DELETE and TRUNCATE?
DELETE: Row-by-row removal, supports WHERE, fully logged, slower, can be rolled back, fires triggers.
TRUNCATE: Fast, deletes all rows, minimal logging, cannot use WHERE, does not fire triggers, limited rollback.
