> masteringswe_

SQL - MySQL

Start Spaced Repetition Session154 cards

Questions

Querying Data8

SELECT FROM - specific columns
codinglocked
Sign up to unlock
SELECT without FROM
codinglocked
Sign up to unlock
DISTINCT - NULL values
conceptuallocked
Sign up to unlock
DISTINCT - multiple columns
codinglocked
Sign up to unlock
SELECT DISTINCT
codinglocked
Sign up to unlock
SQL clause evaluation order
conceptuallocked
Sign up to unlock
SELECT * - select all columns
conceptuallocked
Sign up to unlock
Column alias
codinglocked
Sign up to unlock

Filtering22

AND vs OR precedence
conceptuallocked
Sign up to unlock
WHERE - NOT IN
codinglocked
Sign up to unlock
OR - return values and NULL
conceptuallocked
Sign up to unlock
AND/OR short-circuit evaluation
conceptuallocked
Sign up to unlock
AND - return values and NULL
conceptuallocked
Sign up to unlock
SQL comparison operators
conceptuallocked
Sign up to unlock
WHERE - LIKE wildcards
codinglocked
Sign up to unlock
WHERE - IS NULL vs = NULL
conceptuallocked
Sign up to unlock
WHERE - BETWEEN
codinglocked
Sign up to unlock
WHERE - IN
codinglocked
Sign up to unlock
WHERE - AND and OR
codinglocked
Sign up to unlock
WHERE clause
conceptuallocked
Sign up to unlock
IN - NULL handling
conceptuallocked
Sign up to unlock
NOT IN - NULL pitfall
conceptuallocked
Sign up to unlock
BETWEEN - NOT BETWEEN
codinglocked
Sign up to unlock
LIKE - NOT LIKE
codinglocked
Sign up to unlock
BETWEEN - date ranges
codinglocked
Sign up to unlock
LIKE - case sensitivity
conceptuallocked
Sign up to unlock
LIKE - ESCAPE clause
codinglocked
Sign up to unlock
BETWEEN - NULL operands
conceptuallocked
Sign up to unlock
IS NULL - return values
conceptuallocked
Sign up to unlock
IS NOT NULL
codinglocked
Sign up to unlock

Sorting & Limiting10

ORDER BY - NULL placement
conceptuallocked
Sign up to unlock
ORDER BY - custom order with FIELD()
codinglocked
Sign up to unlock
ORDER BY - multiple columns
codinglocked
Sign up to unlock
ORDER BY - expression and alias
codinglocked
Sign up to unlock
ORDER BY - ASC and DESC
conceptuallocked
Sign up to unlock
LIMIT clause
conceptuallocked
Sign up to unlock
LIMIT - pagination
codinglocked
Sign up to unlock
LIMIT - with ORDER BY
conceptuallocked
Sign up to unlock
LIMIT - with DISTINCT
conceptuallocked
Sign up to unlock
LIMIT - nth highest value
codinglocked
Sign up to unlock

Joins18

Table alias
conceptuallocked
Sign up to unlock
JOIN - USING vs ON
conceptuallocked
Sign up to unlock
INNER JOIN
conceptuallocked
Sign up to unlock
FULL OUTER JOIN in MySQL
codinglocked
Sign up to unlock
SQL join types
conceptuallocked
Sign up to unlock
INNER JOIN - multiple tables
codinglocked
Sign up to unlock
INNER JOIN - with GROUP BY
codinglocked
Sign up to unlock
Non-equi join
conceptuallocked
Sign up to unlock
LEFT JOIN
conceptuallocked
Sign up to unlock
LEFT JOIN - find unmatched rows
codinglocked
Sign up to unlock
LEFT JOIN - condition in ON vs WHERE
conceptuallocked
Sign up to unlock
RIGHT JOIN
conceptuallocked
Sign up to unlock
Self join
conceptuallocked
Sign up to unlock
Self join - manager hierarchy
codinglocked
Sign up to unlock
Self join - pairs within a group
codinglocked
Sign up to unlock
CROSS JOIN with WHERE
conceptuallocked
Sign up to unlock
CROSS JOIN
conceptuallocked
Sign up to unlock
CROSS JOIN - fill missing combinations
codinglocked
Sign up to unlock

Grouping & Aggregation17

GROUP BY clause
conceptuallocked
Sign up to unlock
GROUP BY - aggregate per group
codinglocked
Sign up to unlock
GROUP BY - column alias
conceptuallocked
Sign up to unlock
GROUP BY - expression
codinglocked
Sign up to unlock
GROUP BY - multiple columns
codinglocked
Sign up to unlock
GROUP BY vs DISTINCT
conceptuallocked
Sign up to unlock
GROUP BY - result ordering
conceptuallocked
Sign up to unlock
HAVING - multiple conditions
codinglocked
Sign up to unlock
HAVING vs WHERE
conceptuallocked
Sign up to unlock
HAVING clause
conceptuallocked
Sign up to unlock
HAVING COUNT - filter groups by size
codinglocked
Sign up to unlock
HAVING COUNT - find duplicates
codinglocked
Sign up to unlock
WITH ROLLUP
codinglocked
Sign up to unlock
HAVING - SELECT alias
conceptuallocked
Sign up to unlock
Grouping sets
conceptuallocked
Sign up to unlock
GROUPING() function
codinglocked
Sign up to unlock
ROLLUP - multiple columns hierarchy
conceptuallocked
Sign up to unlock

Subqueries9

Subquery
conceptuallocked
Sign up to unlock
Subquery - IN with multiple rows
codinglocked
Sign up to unlock
Subquery - comparison operator
codinglocked
Sign up to unlock
Correlated subquery - above group average
codinglocked
Sign up to unlock
Correlated subquery
conceptuallocked
Sign up to unlock
EXISTS and NOT EXISTS
codinglocked
Sign up to unlock
Derived table - join with a base table
codinglocked
Sign up to unlock
Derived table - aggregate over grouped results
codinglocked
Sign up to unlock
Derived table
conceptuallocked
Sign up to unlock

Set Operations10

UNION vs UNION ALL
conceptuallocked
Sign up to unlock
UNION
conceptuallocked
Sign up to unlock
UNION vs JOIN
conceptuallocked
Sign up to unlock
UNION - output column names
codinglocked
Sign up to unlock
UNION - tag each row's source
codinglocked
Sign up to unlock
EXCEPT ALL vs EXCEPT DISTINCT
conceptuallocked
Sign up to unlock
EXCEPT
conceptuallocked
Sign up to unlock
UNION - ORDER BY
codinglocked
Sign up to unlock
INTERSECT
conceptuallocked
Sign up to unlock
INTERSECT ALL vs INTERSECT DISTINCT
conceptuallocked
Sign up to unlock

Data Types9

MySQL data type categories
conceptuallocked
Sign up to unlock
DECIMAL vs FLOAT/DOUBLE
conceptuallocked
Sign up to unlock
MySQL BOOLEAN type
conceptuallocked
Sign up to unlock
CHAR vs VARCHAR
conceptuallocked
Sign up to unlock
MySQL integer types and UNSIGNED
conceptuallocked
Sign up to unlock
ENUM vs SET
conceptuallocked
Sign up to unlock
TEXT vs BLOB
conceptuallocked
Sign up to unlock
MySQL JSON data type
conceptuallocked
Sign up to unlock
DATETIME vs TIMESTAMP
conceptuallocked
Sign up to unlock

Table Features11

AUTO_INCREMENT
codinglocked
Sign up to unlock
AUTO_INCREMENT - reset value
codinglocked
Sign up to unlock
AUTO_INCREMENT - add to existing table
codinglocked
Sign up to unlock
LAST_INSERT_ID()
codinglocked
Sign up to unlock
Temporary table
conceptuallocked
Sign up to unlock
Temporary tables with connection pooling
conceptuallocked
Sign up to unlock
Temporary table - create from a query
codinglocked
Sign up to unlock
Generated column
codinglocked
Sign up to unlock
Temporary table - name shadowing and DROP TEMPORARY TABLE
conceptuallocked
Sign up to unlock
Generated column - VIRTUAL vs STORED
conceptuallocked
Sign up to unlock
Generated column - expression rules
conceptuallocked
Sign up to unlock

Constraints23

Primary key
conceptuallocked
Sign up to unlock
Primary key - add and drop on existing table
codinglocked
Sign up to unlock
Composite primary key
codinglocked
Sign up to unlock
Foreign key
conceptuallocked
Sign up to unlock
Foreign key - ON DELETE / ON UPDATE options
conceptuallocked
Sign up to unlock
Foreign key - drop constraint
codinglocked
Sign up to unlock
Self-referencing foreign key
codinglocked
Sign up to unlock
Disabling foreign key checks
codinglocked
Sign up to unlock
NOT NULL constraint
conceptuallocked
Sign up to unlock
NOT NULL - add to existing column
codinglocked
Sign up to unlock
NOT NULL - remove from column
codinglocked
Sign up to unlock
UNIQUE constraint
codinglocked
Sign up to unlock
UNIQUE - NULL values
conceptuallocked
Sign up to unlock
Primary key vs UNIQUE
conceptuallocked
Sign up to unlock
UNIQUE - index and add/drop
codinglocked
Sign up to unlock
CHECK constraint
codinglocked
Sign up to unlock
CHECK - column vs table constraint
codinglocked
Sign up to unlock
CHECK - NULL and UNKNOWN
conceptuallocked
Sign up to unlock
CHECK - ENFORCED vs NOT ENFORCED
conceptuallocked
Sign up to unlock
CHECK - add, drop, and auto names
codinglocked
Sign up to unlock
DEFAULT keyword in INSERT
codinglocked
Sign up to unlock
DEFAULT - add and drop on existing column
codinglocked
Sign up to unlock
DEFAULT constraint
codinglocked
Sign up to unlock

Modifying Data17

INSERT statement
codinglocked
Sign up to unlock
INSERT - multiple rows in one statement
codinglocked
Sign up to unlock
INSERT - date literals and expressions
codinglocked
Sign up to unlock
INSERT INTO ... SELECT
codinglocked
Sign up to unlock
Multi-row INSERT - max_allowed_packet limit
conceptuallocked
Sign up to unlock
INSERT - scalar subqueries in VALUES
codinglocked
Sign up to unlock
UPDATE statement
codinglocked
Sign up to unlock
UPDATE - set from existing value with REPLACE()
codinglocked
Sign up to unlock
UPDATE JOIN - INNER JOIN
codinglocked
Sign up to unlock
UPDATE - subquery in SET
codinglocked
Sign up to unlock
UPDATE - LOW_PRIORITY and IGNORE
conceptuallocked
Sign up to unlock
DELETE - ORDER BY and LIMIT
codinglocked
Sign up to unlock
UPDATE JOIN - LEFT JOIN with fallback
codinglocked
Sign up to unlock
DELETE statement
codinglocked
Sign up to unlock
DELETE vs TRUNCATE TABLE
conceptuallocked
Sign up to unlock
DELETE JOIN - INNER JOIN across tables
codinglocked
Sign up to unlock
DELETE JOIN - LEFT JOIN orphan rows
codinglocked
Sign up to unlock