Title: SQL Mastery — LearnCoachAssist

Q: What is SQL?
A: Structured Query Language — a standardized language for managing relational databases.

Q: What is a relational database?
A: A database storing data in tables with relationships between them.

Q: What is RDBMS?
A: Relational Database Management System.

Q: Name common RDBMS.
A: PostgreSQL, MySQL, SQLite, Oracle, SQL Server.

Q: What is a table?
A: A collection of related data organized in rows and columns.

Q: What is a row?
A: A single record in a table.

Q: What is a column?
A: An attribute or field of a table.

Q: What is a primary key?
A: A unique identifier for each row in a table.

Q: What is a foreign key?
A: A column referencing the primary key of another table.

Q: What is a composite key?
A: A primary key consisting of multiple columns.

Q: What is a candidate key?
A: Any column or set that could serve as a primary key.

Q: What is referential integrity?
A: Ensuring foreign keys point to valid primary keys.

Q: What is normalization?
A: Organizing tables to reduce redundancy and dependency.

Q: What are normal forms?
A: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF — progressively stricter rules.

Q: What is 1NF?
A: Each cell holds a single atomic value.

Q: What is 2NF?
A: 1NF plus no partial dependencies on a composite primary key.

Q: What is 3NF?
A: 2NF plus no transitive dependencies.

Q: What is BCNF?
A: Boyce-Codd Normal Form, a stricter 3NF.

Q: What is denormalization?
A: Combining tables for query performance at cost of redundancy.

Q: What is SELECT?
A: Retrieves rows from one or more tables.

Q: What is FROM?
A: Specifies the source table(s).

Q: What is WHERE?
A: Filters rows based on a condition.

Q: What is GROUP BY?
A: Groups rows that share values in specified columns.

Q: What is HAVING?
A: Filters grouped results (like WHERE but after grouping).

Q: What is ORDER BY?
A: Sorts the result set.

Q: What is LIMIT?
A: Restricts the number of rows returned.

Q: What is OFFSET?
A: Skips a number of rows before returning results.

Q: What is DISTINCT?
A: Returns unique rows by removing duplicates.

Q: What is the order of SQL clause execution?
A: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Q: What is an alias (AS)?
A: A temporary name for a table or column.

Q: What is a JOIN?
A: Combines rows from two or more tables based on a related column.

Q: What is INNER JOIN?
A: Returns rows where the join condition matches in both tables.

Q: What is LEFT JOIN?
A: Returns all rows from left table, matched rows from right.

Q: What is RIGHT JOIN?
A: Returns all rows from right table, matched rows from left.

Q: What is FULL OUTER JOIN?
A: Returns rows when there's a match in either table.

Q: What is CROSS JOIN?
A: Cartesian product of two tables (every combination).

Q: What is a SELF JOIN?
A: Joining a table to itself.

Q: What is a NATURAL JOIN?
A: Auto-joins on columns with the same name (avoid in production).

Q: What is a USING clause?
A: Joins on a column with the same name in both tables.

Q: What is the difference between WHERE and ON?
A: ON specifies join conditions; WHERE filters after join.

Q: What is UNION?
A: Combines results of multiple SELECTs, removing duplicates.

Q: What is UNION ALL?
A: Combines results without removing duplicates (faster).

Q: What is INTERSECT?
A: Returns rows in both queries.

Q: What is EXCEPT (MINUS)?
A: Returns rows in first query but not second.

Q: What is a subquery?
A: A query nested inside another query.

Q: What is a correlated subquery?
A: A subquery referencing the outer query.

Q: What is EXISTS?
A: Returns true if a subquery returns any rows.

Q: What is NOT EXISTS?
A: Returns true if subquery returns no rows.

Q: What is IN?
A: Tests if a value matches any in a list or subquery.

Q: What is BETWEEN?
A: Tests if a value is within a range (inclusive).

Q: What is LIKE?
A: Pattern matching with % (any chars) and _ (single char).

Q: What is ILIKE?
A: Case-insensitive LIKE (PostgreSQL).

Q: What is REGEXP / REGEX?
A: Regular expression matching, syntax varies by RDBMS.

Q: What is NULL?
A: A marker indicating missing or unknown data.

Q: What is IS NULL?
A: Tests if a value is NULL.

Q: What is COALESCE?
A: Returns first non-NULL value from a list.

Q: What is NULLIF?
A: Returns NULL if two expressions are equal; otherwise first.

Q: What is the difference between NULL and empty string?
A: NULL is unknown; empty string is a known value of zero length.

Q: What is aggregate function?
A: Functions like COUNT, SUM, AVG operating on sets of rows.

Q: What is COUNT(*)?
A: Counts all rows including NULLs.

Q: What is COUNT(column)?
A: Counts non-NULL values in the column.

Q: What is COUNT(DISTINCT col)?
A: Counts unique non-NULL values.

Q: What is SUM?
A: Total of a numeric column.

Q: What is AVG?
A: Mean of a numeric column.

Q: What is MIN / MAX?
A: Smallest / largest value.

Q: What is the difference between GROUP BY and PARTITION BY?
A: GROUP BY collapses rows; PARTITION BY operates within rows in window functions.

Q: What is a window function?
A: Performs calculation across rows related to current row without collapsing.

Q: What is OVER()?
A: Defines the window for a window function.

Q: What is ROW_NUMBER()?
A: Assigns a unique number per row in a partition.

Q: What is RANK()?
A: Assigns ranks; ties get same rank, skipping next.

Q: What is DENSE_RANK()?
A: Like RANK but doesn't skip after ties.

Q: What is NTILE(n)?
A: Distributes rows into n approximately equal buckets.

Q: What is LAG()?
A: Returns value from a previous row in the partition.

Q: What is LEAD()?
A: Returns value from a following row.

Q: What is FIRST_VALUE / LAST_VALUE?
A: Returns first or last value in window.

Q: What is a frame clause?
A: Defines a subset of the partition for the window function.

Q: What is ROWS BETWEEN?
A: Frame defined by physical row positions.

Q: What is RANGE BETWEEN?
A: Frame defined by logical (value) range.

Q: What is a CTE?
A: Common Table Expression — named subquery via WITH.

Q: What is recursive CTE?
A: A CTE referencing itself; useful for hierarchical data.

Q: What is a temporary table?
A: A table that exists for the duration of a session.

Q: What is a view?
A: A virtual table defined by a query.

Q: What is a materialized view?
A: A view storing actual data, refreshed periodically.

Q: What is a stored procedure?
A: A reusable set of SQL statements stored in the database.

Q: What is a function (SQL)?
A: A reusable routine returning a value.

Q: What is a trigger?
A: Code that runs automatically on data events.

Q: What is CREATE TABLE?
A: Defines a new table with columns and constraints.

Q: What is ALTER TABLE?
A: Modifies an existing table's structure.

Q: What is DROP TABLE?
A: Removes a table and its data.

Q: What is TRUNCATE TABLE?
A: Removes all rows quickly without logging individual deletions.

Q: What is the difference between DELETE, TRUNCATE, DROP?
A: DELETE removes specific rows (slow, transactional); TRUNCATE empties table (fast); DROP removes table entirely.

Q: What is INSERT?
A: Adds new rows to a table.

Q: What is INSERT INTO ... SELECT?
A: Inserts results of a query.

Q: What is UPSERT?
A: Insert or update if conflict (ON CONFLICT in PostgreSQL, ON DUPLICATE KEY in MySQL, MERGE in standard SQL).

Q: What is MERGE?
A: Combines INSERT, UPDATE, DELETE based on a join condition.

Q: What is UPDATE?
A: Modifies existing rows.

Q: What is DELETE?
A: Removes rows matching a condition.

Q: What is a constraint?
A: A rule enforced on data: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY.

Q: What is NOT NULL?
A: Disallows NULL in a column.

Q: What is UNIQUE?
A: Ensures all values in a column are different.

Q: What is CHECK?
A: Custom validation expression for column values.

Q: What is DEFAULT?
A: Default value for a column when none is provided.

Q: What is an index?
A: A data structure improving query speed.

Q: What is a clustered index?
A: An index that determines the physical row order; one per table.

Q: What is a non-clustered index?
A: A separate structure with pointers to rows; multiple allowed.

Q: What is a B-tree index?
A: Most common index structure, balanced tree.

Q: What is a hash index?
A: Index using a hash function; supports equality lookups only.

Q: What is a unique index?
A: An index that also enforces uniqueness.

Q: What is a composite index?
A: An index on multiple columns.

Q: What is index selectivity?
A: The fraction of distinct values; higher selectivity = more useful index.

Q: What is covering index?
A: An index containing all columns needed by a query.

Q: What is index-only scan?
A: A query satisfied entirely from an index without touching the table.

Q: What is the cost of indexes?
A: They speed reads but slow writes and consume storage.

Q: What is EXPLAIN?
A: Shows the execution plan for a query.

Q: What is EXPLAIN ANALYZE?
A: Runs the query and shows actual timing per step.

Q: What is a query plan?
A: A description of how the database will execute a query.

Q: What is sequential scan vs index scan?
A: Sequential reads every row; index scan uses index for filtering.

Q: What is the difference between sort-merge join and hash join?
A: Sort-merge sorts both inputs; hash join builds hash on smaller input.

Q: What is nested loop join?
A: For each row in outer table, look up matches in inner table.

Q: What is a transaction?
A: A unit of work that is atomic, consistent, isolated, durable (ACID).

Q: What is ACID?
A: Atomicity, Consistency, Isolation, Durability — transaction properties.

Q: What is atomicity?
A: All operations in a transaction succeed or none do.

Q: What is consistency?
A: Transactions preserve database invariants.

Q: What is isolation?
A: Concurrent transactions don't interfere with each other.

Q: What is durability?
A: Committed changes survive crashes.

Q: What is BEGIN / COMMIT / ROLLBACK?
A: Statements controlling transaction lifecycle.

Q: What is SAVEPOINT?
A: A point within a transaction to which you can ROLLBACK.

Q: What is isolation level?
A: Degree to which a transaction is isolated from others.

Q: What are SQL isolation levels?
A: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Q: What is dirty read?
A: Reading uncommitted data from another transaction.

Q: What is non-repeatable read?
A: Re-reading data gives different result mid-transaction.

Q: What is phantom read?
A: A re-executed query returns different set of rows.

Q: What is a deadlock?
A: Two transactions blocking each other indefinitely.

Q: What is MVCC?
A: Multi-Version Concurrency Control — readers don't block writers and vice versa.

Q: What is optimistic concurrency?
A: Assumes conflicts are rare; checks at commit.

Q: What is pessimistic concurrency?
A: Acquires locks upfront to prevent conflicts.

Q: What is a row-level lock?
A: Lock on individual rows.

Q: What is a table-level lock?
A: Lock on entire table.

Q: What is SELECT FOR UPDATE?
A: Locks selected rows for subsequent update.

Q: What is the difference between OLTP and OLAP?
A: OLTP: many small transactions; OLAP: complex analytical queries on large data.

Q: What is a fact table?
A: Central table in a data warehouse storing measurements.

Q: What is a dimension table?
A: Provides descriptive context for facts.

Q: What is a star schema?
A: Fact table with related dimensions; common in data warehouses.

Q: What is a snowflake schema?
A: Normalized star schema with sub-dimensions.

Q: What is data warehouse?
A: An optimized analytical repository.

Q: What is a data lake?
A: Storage for raw data of any type at scale.

Q: What is column-oriented storage?
A: Storing columns separately for fast analytics (e.g., Parquet, ClickHouse).

Q: What is row-oriented storage?
A: Standard for OLTP; entire row stored together.

Q: What is partitioning?
A: Dividing a large table into smaller pieces.

Q: What is sharding?
A: Horizontal partitioning across multiple databases.

Q: What is replication?
A: Copying data across databases for redundancy or scaling.

Q: What is the difference between primary and standby?
A: Primary handles writes; standby is read-only replica.

Q: What is read replica?
A: A copy used to scale read traffic.

Q: What is connection pooling?
A: Reusing database connections instead of opening new ones.

Q: What is a prepared statement?
A: A pre-parsed SQL statement reused with different parameters.

Q: What is SQL injection?
A: An attack inserting malicious SQL through user input.

Q: How do prepared statements prevent SQL injection?
A: Parameters are bound separately from the SQL text.

Q: What is ORM?
A: Object-Relational Mapper — translates between objects and tables.

Q: Name ORM examples.
A: SQLAlchemy, Django ORM, Hibernate, Sequelize.

Q: What is the N+1 problem?
A: Issuing N additional queries when joining could fetch in one.

Q: How to avoid N+1?
A: Use JOINs, eager loading, or batch fetching.

Q: What is connection string?
A: A formatted string with database location and credentials.

Q: What is a database driver?
A: A client library implementing a database protocol.

Q: What is JDBC / ODBC?
A: Standard interfaces for connecting to databases from Java / general.

Q: What is GROUP_CONCAT (STRING_AGG, LISTAGG)?
A: Aggregates strings from rows into a single string.

Q: What is PIVOT?
A: Rotating rows to columns (SQL Server, some others).

Q: What is UNPIVOT?
A: Rotating columns to rows.

Q: What is JSON in SQL?
A: Many modern RDBMS support JSON columns and functions.

Q: What is JSONB (PostgreSQL)?
A: Binary JSON storage with indexing capabilities.

Q: What is the difference between TEXT, VARCHAR, CHAR?
A: TEXT/VARCHAR are variable; CHAR is fixed length.

Q: What is the difference between INT, BIGINT, SMALLINT?
A: Different ranges for integers.

Q: What is DECIMAL / NUMERIC?
A: Fixed-precision exact numeric type.

Q: What is FLOAT / DOUBLE?
A: Approximate numeric types.

Q: What is DATE / TIME / TIMESTAMP?
A: Temporal types for dates and times.

Q: What is TIMESTAMP WITH TIME ZONE?
A: A timestamp converted to UTC for storage.

Q: What is CURRENT_TIMESTAMP?
A: Returns the current date and time.

Q: What is EXTRACT?
A: Extracts a field (year, month, day) from a date.

Q: What is DATE_TRUNC?
A: Truncates a date to a specified precision.

Q: What is INTERVAL?
A: A time span (e.g., '1 day', '2 hours').

Q: What is the difference between WHERE and ON in OUTER JOIN?
A: WHERE applied after join can filter out NULLs from outer side; use ON for join conditions.

Q: What is the cardinality (DB)?
A: The number of unique values in a column.

Q: What is statistics (DB)?
A: Metadata used by the query optimizer.

Q: What is the query optimizer?
A: The component that chooses the execution plan.

Q: What is cost-based optimization?
A: Optimizer estimates cost of plans and picks cheapest.

Q: What is index fragmentation?
A: Disorder in index pages reducing efficiency.

Q: What is index rebuild?
A: Reorganizing index for performance.

Q: What is vacuum (PostgreSQL)?
A: Reclaims storage and updates statistics.

Q: What is autovacuum?
A: Automatic background vacuum process.

Q: What is bloating?
A: Wasted space from MVCC dead tuples.

Q: What is checkpoint?
A: Persisting dirty pages from memory to disk.

Q: What is WAL?
A: Write-Ahead Log — records changes before applying for durability.

Q: What is logical replication?
A: Replicating logical changes, not physical bytes.

Q: What is physical replication?
A: Streaming WAL to standby.

Q: What is point-in-time recovery?
A: Restoring database to a specific timestamp.

Q: What is a backup?
A: A copy of data for recovery purposes.

Q: What is logical backup?
A: SQL dump that recreates schema and data (pg_dump, mysqldump).

Q: What is physical backup?
A: Byte-level copy of database files.

Q: What is restore?
A: Recreating database from backup.

Q: What is database migration (schema)?
A: Versioned changes to schema, often via tools like Flyway, Liquibase, or Django migrations.

Q: What is rollback (migration)?
A: Reverting a schema change.

Q: What is zero-downtime migration?
A: Schema changes that don't require taking the DB offline.

Q: What is the strangler pattern?
A: Gradually replacing an old system; applies to DB migrations too.

Q: What is database normalization tradeoff?
A: Less redundancy but more joins; balance reads/writes.

Q: What is a CRUD operation?
A: Create, Read, Update, Delete.

Q: What is the SQL standard?
A: ISO/IEC 9075; RDBMS often deviate or extend.

Q: What is dialect?
A: A RDBMS-specific variant of SQL.

Q: What is the difference between Postgres and MySQL?
A: Postgres is feature-rich with strong standards compliance; MySQL is popular and very fast for reads.

Q: What is SQLite?
A: A lightweight, file-based, embedded SQL database.

Q: What is Oracle?
A: Enterprise RDBMS with extensive features.

Q: What is SQL Server?
A: Microsoft's RDBMS with T-SQL.

Q: What is T-SQL?
A: Transact-SQL — Microsoft and Sybase SQL extension.

Q: What is PL/pgSQL?
A: Procedural language for PostgreSQL.

Q: What is PL/SQL?
A: Oracle's procedural extension.

Q: What is NoSQL?
A: Non-relational databases: document, key-value, graph, columnar.

Q: When to use NoSQL vs SQL?
A: SQL for structured data with complex queries; NoSQL for flexible schema, scale, specific patterns.

Q: What is a primary key vs unique key?
A: Primary key is non-NULL and unique; unique key allows NULLs but enforces uniqueness.

Q: What is auto-increment?
A: A column generating unique sequential numbers automatically.

Q: What is SERIAL / IDENTITY?
A: Auto-increment column types.

Q: What is UUID?
A: Universally Unique Identifier; useful for distributed systems.

Q: What is the difference between UUID v1 and v4?
A: v1 includes timestamp/MAC; v4 is random.

Q: What is OFFSET pagination?
A: Skipping N rows; slow on large tables.

Q: What is cursor-based pagination?
A: Using a row's key (e.g., id > last_seen) for fast pagination.

Q: What is keyset pagination?
A: Same as cursor-based; very efficient.

Q: What is the difference between truncate and delete?
A: TRUNCATE is fast, non-transactional in some RDBMS, resets identity; DELETE is row-by-row and transactional.

Q: What is RETURNING clause?
A: Returns affected rows from INSERT/UPDATE/DELETE (PostgreSQL).

Q: What is an upsert pattern?
A: INSERT ... ON CONFLICT DO UPDATE (PostgreSQL).

Q: What is full-text search?
A: Searching textual content with relevance ranking.

Q: What is tsvector / tsquery (PostgreSQL)?
A: Types supporting full-text search.

Q: What is GIN / GiST index?
A: Specialized index types for full-text, JSONB, etc.

Q: What is a CHECK constraint example?
A: CHECK (price > 0) ensures positive prices.

Q: What is enum type?
A: A column type restricted to specific values.

Q: What is a domain?
A: A user-defined type with constraints.

Q: What is range type (PostgreSQL)?
A: Type representing a range of values (e.g., int4range).

Q: What is the importance of SQL formatting?
A: Readable queries are maintainable; conventions matter.

Q: What is parameterized query?
A: Query with placeholders, preventing injection.

Q: What is dynamic SQL?
A: SQL constructed at runtime; risky without proper escaping.

Q: What is the role of SELECT *?
A: Convenient but discouraged in production for performance and stability.

Q: What is the importance of explicit column lists?
A: Stable interfaces, reduced data transfer, clearer intent.

Q: What is the most common SQL performance issue?
A: Missing indexes, N+1 queries, full table scans on large tables.