SQL Fundamentals
110 flashcards by MentisLibrary
Queries, joins, keys and constraints, indexes and query performance, normalization, window functions, and transactions, with a runnable example on almost every card. For anyone who has to write or read SQL at work.
- What does SQL stand for?
- Structured Query Language
- Which SQL statement retrieves data from a table?
- SELECT
- Which clause filters rows before grouping?
- WHERE
- Which clause filters groups after aggregation?
- HAVING
- Which clause sorts query results?
- ORDER BY
- Which keyword removes duplicate rows from a result set?
- DISTINCT
- Which clause limits the number of rows returned?
- LIMIT (or FETCH FIRST / TOP depending on the database)
- What does the OFFSET clause do?
- Skips a given number of rows before returning results
- What is an INNER JOIN?
- Returns only rows that have matching values in both tables
- What is a LEFT JOIN?
- Returns all rows from the left table, plus matching rows from the right table (NULLs where no match)
- What is a RIGHT JOIN?
- Returns all rows from the right table, plus matching rows from the left table
- What is a FULL OUTER JOIN?
- Returns all rows from both tables, with NULLs where there is no match on either side
- What is a CROSS JOIN?
- Returns the Cartesian product: every row of one table paired with every row of the other
- What is a self join?
- A table joined to itself using aliases
- What is the difference between UNION and UNION ALL?
- UNION removes duplicate rows; UNION ALL keeps all rows including duplicates
- What does GROUP BY do?
- Groups rows sharing the same values so aggregate functions can be applied per group
- Which function counts rows?
- COUNT()
- Which function adds up numeric values?
- SUM()
- Which function computes an average?
- AVG()
- Which functions return the smallest and largest values?
- MIN() and MAX()
- What is a primary key?
- A column (or set of columns) that uniquely identifies each row in a table
- What is a foreign key?
- A column that references the primary key of another table to enforce referential integrity
- What is a composite key?
- A primary key made up of two or more columns
- What is a candidate key?
- Any column or minimal set of columns that could uniquely identify rows
- What is a surrogate key?
- An artificial identifier (like an auto-increment integer or UUID) with no business meaning
- What is a UNIQUE constraint?
- Ensures all values in a column (or column set) are distinct
- What does NOT NULL do?
- Prevents a column from storing NULL values
- What is a CHECK constraint?
- Enforces that values in a column satisfy a boolean expression
- What is a DEFAULT constraint?
- Supplies a value automatically when an INSERT omits the column
- What is an index?
- A data structure that speeds up row lookups at the cost of extra storage and slower writes