EXIN SQL Foundation flashcards
EXIN SQL Foundation · Build recall with a focused review session.
32 cards. Read the term and the definition here. The deck at the top of this page is optional.
- Declarative language
- A language where the user specifies the desired result rather than the procedure. SQL is declarative; the database optimizer decides how to execute the query.
- Primary key
- A column or column combination that uniquely identifies every row in a table. It implies NOT NULL and UNIQUE, and a table has at most one.
- Three-valued logic
- SQL conditions evaluate to TRUE, FALSE or UNKNOWN. WHERE keeps only TRUE rows, so rows where a NULL comparison yields UNKNOWN are excluded from results.
- NULL comparison
- NULL means unknown, so col = NULL is never TRUE. Use IS NULL or IS NOT NULL to test for missing values instead of equality operators.
- Aggregate and NULL
- COUNT(col), SUM, AVG, MIN and MAX ignore NULL values. COUNT(*) counts all rows, including those where every column is NULL.
- Logical clause order
- FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. This explains why SELECT aliases cannot be used in WHERE.
- COMMIT and ROLLBACK
- COMMIT makes a transaction's changes permanent. ROLLBACK undoes uncommitted changes and returns the database to the state at the last commit.
- DCL statements
- Data Control Language manages permissions: GRANT gives privileges to users or roles, and REVOKE removes them. It does not change table data.
- CHAR versus VARCHAR
- CHAR(n) stores a fixed length and pads shorter values with spaces. VARCHAR(n) stores variable length up to n, using only the space needed.
- DECIMAL versus FLOAT
- DECIMAL(p,s) stores exact fixed-point numbers with p total digits and s decimals. FLOAT is approximate binary and can produce rounding errors, so avoid it for money.
- NOT NULL constraint
- A column constraint that forbids missing values. Every INSERT or UPDATE must supply a non-NULL value, otherwise the statement fails with an error.
- Foreign key
- A column referencing a primary or unique key in another table. It enforces referential integrity: values must exist in the parent table or be NULL.
- CHECK constraint
- A rule limiting allowed column values. It is violated only when the condition is FALSE; when the condition is UNKNOWN because of NULL, the row is accepted.
- TRUNCATE versus DELETE
- TRUNCATE quickly removes all rows and keeps the table structure. DELETE removes selected rows, fires triggers and can be rolled back.
- ALTER TABLE
- A DDL statement that changes an existing table: adding, dropping or modifying columns and adding or dropping constraints, without losing the remaining data.
- DEFAULT value
- A column property supplying a value when an INSERT omits that column. Without a default, an omitted column receives NULL if it is nullable.
- CREATE VIEW
- Defines a named stored query that behaves like a virtual table. It stores the query text, not the data, so results reflect current table contents.
- Timestamp type
- A data type storing both a calendar date and a time of day, often with fractional seconds. Some products add time zone information.
- WHERE versus HAVING
- WHERE filters rows before grouping and cannot contain aggregates. HAVING filters groups after GROUP BY and may contain aggregate functions.
- INNER JOIN
- Returns only row pairs where the join condition matches in both tables. Unmatched rows from either side are excluded from the result.
- LEFT OUTER JOIN
- Returns all rows from the left table plus matching right rows. Where no match exists, right-side columns appear as NULL in the output.
- Self join
- A join of a table to itself using two different aliases, commonly used to relate rows within one table, such as employees to their managers.
- DISTINCT keyword
- Removes duplicate rows from the result set. It applies to the entire selected row, so two rows differing in any selected column are both kept.
- LIKE wildcards
- In a LIKE pattern, percent matches any sequence of characters including none, and underscore matches exactly one character. Other characters match literally.
- ORDER BY
- Sorts the final result set. It defaults to ascending order, accepts DESC per column, and applies multiple sort keys from left to right.
- UPDATE without WHERE
- An UPDATE statement with no WHERE clause applies the change to every row in the table, which is a common and destructive mistake.
- Correlated subquery
- A subquery referencing columns of the outer query. It is evaluated repeatedly, once per outer row, and is often used with EXISTS.
- EXISTS operator
- Tests whether a subquery returns at least one row, yielding TRUE or FALSE. It is NULL-safe and often faster than IN for large sets.
- UNION versus UNION ALL
- UNION combines result sets and removes duplicate rows. UNION ALL keeps all rows, including duplicates, and therefore runs faster.
- CASE expression
- Returns a value based on conditions: CASE WHEN condition THEN result ELSE result END. Valid in SELECT, WHERE, ORDER BY and inside aggregates.
- Window function
- Computes a value across a set of related rows using OVER with PARTITION BY and ORDER BY, without collapsing rows as GROUP BY does.
- Index
- A structure that speeds up lookups, joins and sorts on indexed columns. It adds overhead to INSERT, UPDATE and DELETE because it must be maintained.