SQL Mastery Practice Exam — LearnCoachAssist

Question 1 SQL Mastery

What is dynamic SQL?

Index using a hash function; supports equality lookups only. Normalized star schema with sub-dimensions. SQL constructed at runtime; risky without proper escaping. A copy of data for recovery purposes.

Question 2 SQL Mastery

What is a CTE?

Common Table Expression — named subquery via WITH. Filters rows based on a condition. Returns all rows from left table, matched rows from right. SQL constructed at runtime; risky without proper escaping.

Question 3 SQL Mastery

What is a transaction?

Returns rows in first query but not second. A unit of work that is atomic, consistent, isolated, durable (ACID). FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Dividing a large table into smaller pieces.

Question 4 SQL Mastery

Name common RDBMS.

Counts non-NULL values in the column. A virtual table defined by a query. Copying data across databases for redundancy or scaling. PostgreSQL, MySQL, SQLite, Oracle, SQL Server.

Question 5 SQL Mastery

What is ALTER TABLE?

Removes all rows quickly without logging individual deletions. Modifies an existing table's structure. Lock on individual rows. Lock on entire table.

Question 6 SQL Mastery

What is the SQL standard?

A subquery referencing the outer query. ISO/IEC 9075; RDBMS often deviate or extend. Create, Read, Update, Delete. A temporary name for a table or column.

Question 7 SQL Mastery

What is a star schema?

Less redundancy but more joins; balance reads/writes. Fact table with related dimensions; common in data warehouses. A user-defined type with constraints. Smallest / largest value.

Question 8 SQL Mastery

What is JSON in SQL?

An index containing all columns needed by a query. Replicating logical changes, not physical bytes. Dividing a large table into smaller pieces. Many modern RDBMS support JSON columns and functions.

Question 9 SQL Mastery

What is the importance of SQL formatting?

Temporal types for dates and times. 1NF, 2NF, 3NF, BCNF, 4NF, 5NF — progressively stricter rules. Retrieves rows from one or more tables. Readable queries are maintainable; conventions matter.

Question 10 SQL Mastery

What is IS NULL?

Oracle's procedural extension. Assigns a unique number per row in a partition. Issuing N additional queries when joining could fetch in one. Tests if a value is NULL.

Question 11 SQL Mastery

What is SQL Server?

Microsoft's RDBMS with T-SQL. Sequential reads every row; index scan uses index for filtering. All operations in a transaction succeed or none do. Total of a numeric column.

Question 12 SQL Mastery

What is a stored procedure?

A reusable set of SQL statements stored in the database. A query nested inside another query. GROUP BY collapses rows; PARTITION BY operates within rows in window functions. Returns rows when there's a match in either table.

Question 13 SQL Mastery

What is BCNF?

Defines the window for a window function. Boyce-Codd Normal Form, a stricter 3NF. Total of a numeric column. 2NF plus no transitive dependencies.

Question 14 SQL Mastery

What is the cardinality (DB)?

Two transactions blocking each other indefinitely. Filters grouped results (like WHERE but after grouping). Each cell holds a single atomic value. The number of unique values in a column.

Question 15 SQL Mastery

What is the role of SELECT *?

Convenient but discouraged in production for performance and stability. A view storing actual data, refreshed periodically. Same as cursor-based; very efficient. SQL dump that recreates schema and data (pg_dump, mysqldump).

Question 16 SQL Mastery

What is tsvector / tsquery (PostgreSQL)?

Returns true if a subquery returns any rows. Types supporting full-text search. The number of unique values in a column. Acquires locks upfront to prevent conflicts.

Question 17 SQL Mastery

What is a CHECK constraint example?

Committed changes survive crashes. CHECK (price > 0) ensures positive prices. Extracts a field (year, month, day) from a date. Inserts results of a query.

Question 18 SQL Mastery

What is an index?

A data structure improving query speed. Procedural language for PostgreSQL. Extracts a field (year, month, day) from a date. Searching textual content with relevance ranking.

Question 19 SQL Mastery

What is full-text search?

Searching textual content with relevance ranking. Inserts results of a query. Modifies an existing table's structure. Counts all rows including NULLs.

Question 20 SQL Mastery

What is DISTINCT?

Defines the window for a window function. Restricts the number of rows returned. Returns unique rows by removing duplicates. Convenient but discouraged in production for performance and stability.

Question 21 SQL Mastery

What is normalization?

Organizing tables to reduce redundancy and dependency. Central table in a data warehouse storing measurements. Transactions preserve database invariants. Standard interfaces for connecting to databases from Java / general.

Question 22 SQL Mastery

What is ACID?

A timestamp converted to UTC for storage. Atomicity, Consistency, Isolation, Durability — transaction properties. Non-relational databases: document, key-value, graph, columnar. Rotating rows to columns (SQL Server, some others).

Question 23 SQL Mastery

What is a unique index?

Atomicity, Consistency, Isolation, Durability — transaction properties. A virtual table defined by a query. Returns first or last value in window. An index that also enforces uniqueness.

Question 24 SQL Mastery

What is the difference between TEXT, VARCHAR, CHAR?

Using a row's key (e.g., id > last_seen) for fast pagination. Schema changes that don't require taking the DB offline. TEXT/VARCHAR are variable; CHAR is fixed length. Groups rows that share values in specified columns.

Question 25 SQL Mastery

What is AVG?

Truncates a date to a specified precision. An optimized analytical repository. WHERE applied after join can filter out NULLs from outer side; use ON for join conditions. Mean of a numeric column.

Question 26 SQL Mastery

What is the cost of indexes?

Shows the execution plan for a query. They speed reads but slow writes and consume storage. Sorts the result set. Relational Database Management System.

Question 27 SQL Mastery

What is nested loop join?

For each row in outer table, look up matches in inner table. Returns unique rows by removing duplicates. Skipping N rows; slow on large tables. Dividing a large table into smaller pieces.

Question 28 SQL Mastery

What is UNPIVOT?

Auto-increment column types. Acquires locks upfront to prevent conflicts. A column referencing the primary key of another table. Rotating columns to rows.

Question 29 SQL Mastery

What is cost-based optimization?

Optimizer estimates cost of plans and picks cheapest. Filters grouped results (like WHERE but after grouping). Restricts the number of rows returned. Sequential reads every row; index scan uses index for filtering.

Question 30 SQL Mastery

What is a temporary table?

A reusable set of SQL statements stored in the database. A table that exists for the duration of a session. Committed changes survive crashes. Query with placeholders, preventing injection.

Question 31 SQL Mastery

What is SELECT FOR UPDATE?

Statements controlling transaction lifecycle. WHERE applied after join can filter out NULLs from outer side; use ON for join conditions. Locks selected rows for subsequent update. Convenient but discouraged in production for performance and stability.

Question 32 SQL Mastery

What is vacuum (PostgreSQL)?

Rotating rows to columns (SQL Server, some others). Joins on a column with the same name in both tables. Locks selected rows for subsequent update. Reclaims storage and updates statistics.

Question 33 SQL Mastery

What is NoSQL?

A client library implementing a database protocol. Non-relational databases: document, key-value, graph, columnar. Standard for OLTP; entire row stored together. Most common index structure, balanced tree.

Question 34 SQL Mastery

What is row-oriented storage?

A unit of work that is atomic, consistent, isolated, durable (ACID). Assumes conflicts are rare; checks at commit. Standard for OLTP; entire row stored together. Concurrent transactions don't interfere with each other.

Question 35 SQL Mastery

What is connection pooling?

Returns affected rows from INSERT/UPDATE/DELETE (PostgreSQL). Reusing database connections instead of opening new ones. Shows the execution plan for a query. Returns unique rows by removing duplicates.

Question 36 SQL Mastery

What is BEGIN / COMMIT / ROLLBACK?

Pattern matching with % (any chars) and _ (single char). A query nested inside another query. Statements controlling transaction lifecycle. A unit of work that is atomic, consistent, isolated, durable (ACID).

Question 37 SQL Mastery

What is NULLIF?

Combines results without removing duplicates (faster). Ensures all values in a column are different. An index that determines the physical row order; one per table. Returns NULL if two expressions are equal; otherwise first.

Question 38 SQL Mastery

What is DATE / TIME / TIMESTAMP?

Temporal types for dates and times. Sort-merge sorts both inputs; hash join builds hash on smaller input. The fraction of distinct values; higher selectivity = more useful index. READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Question 39 SQL Mastery

What is a primary key vs unique key?

Primary key is non-NULL and unique; unique key allows NULLs but enforces uniqueness. A timestamp converted to UTC for storage. 1NF plus no partial dependencies on a composite primary key. Returns all rows from left table, matched rows from right.

Question 40 SQL Mastery

What is RETURNING clause?

Combining tables for query performance at cost of redundancy. Readable queries are maintainable; conventions matter. Removes all rows quickly without logging individual deletions. Returns affected rows from INSERT/UPDATE/DELETE (PostgreSQL).

Question 41 SQL Mastery

What is T-SQL?

A pre-parsed SQL statement reused with different parameters. Transact-SQL — Microsoft and Sybase SQL extension. A reusable set of SQL statements stored in the database. A description of how the database will execute a query.

Question 42 SQL Mastery

What is RANGE BETWEEN?

Auto-joins on columns with the same name (avoid in production). Parameters are bound separately from the SQL text. Frame defined by logical (value) range. Optimizer estimates cost of plans and picks cheapest.

Question 43 SQL Mastery

What is a composite index?

Relational Database Management System. Smallest / largest value. An index on multiple columns. A column generating unique sequential numbers automatically.

Question 44 SQL Mastery

What is database migration (schema)?

Combines results of multiple SELECTs, removing duplicates. Extracts a field (year, month, day) from a date. Versioned changes to schema, often via tools like Flyway, Liquibase, or Django migrations. Filters grouped results (like WHERE but after grouping).

Question 45 SQL Mastery

What is LIMIT?

Replicating logical changes, not physical bytes. Restricts the number of rows returned. Combines results without removing duplicates (faster). A RDBMS-specific variant of SQL.

Question 46 SQL Mastery

What are SQL isolation levels?

Tests if a value is NULL. Automatic background vacuum process. READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. A single record in a table.

Question 47 SQL Mastery

What is INTERSECT?

Approximate numeric types. Returns NULL if two expressions are equal; otherwise first. Inserts results of a query. Returns rows in both queries.

Question 48 SQL Mastery

What is partitioning?

A query satisfied entirely from an index without touching the table. SQL constructed at runtime; risky without proper escaping. Acquires locks upfront to prevent conflicts. Dividing a large table into smaller pieces.

Question 49 SQL Mastery

What is non-repeatable read?

OLTP: many small transactions; OLAP: complex analytical queries on large data. Re-reading data gives different result mid-transaction. Common Table Expression — named subquery via WITH. A point within a transaction to which you can ROLLBACK.

Question 50 SQL Mastery

What is PL/pgSQL?

Retrieves rows from one or more tables. Procedural language for PostgreSQL. WHERE applied after join can filter out NULLs from outer side; use ON for join conditions. Primary handles writes; standby is read-only replica.