NEW
Font size
WorksheetsSQL Subqueries and Functions Quiz
Total questions: 60
Worksheet time: 30mins
Which of the following best describes a scalar subquery?
A subquery that returns multiple rows and columns
A subquery that returns only one row but multiple columns
A subquery that returns a single value (one row, one column)
A subquery used only in the FROM clause
What is the key difference between correlated and non-correlated subqueries?
Correlated subqueries can return multiple columns, while non-correlated cannot
Correlated subqueries reference columns from the outer query, while non-correlated do not
Non-correlated subqueries always use the FULL JOINs, while correlated subqueries never do
Non-correlated subqueries are slower than correlated subqueries
Which of the following scenarios is best suited for a self-join?
Comparing rows within the same table
Combining results of multiple queries
Traversing hierarchical data recursively
Removing duplicates in a table
What is the result of a CROSS JOIN between two tables?
Rows matched by common columns
All combinations of rows from both tables (Cartesian product)
Only rows with NULL values from both tables
A join that eliminates duplicate rows
Which JOIN type returns all rows from both tables, whether they match or not?
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
The SQL operation UNION ALL differs from UNION because:
UNION ALL requires identical column names, while UNION does not
UNION ALL includes duplicates, while UNION removes duplicates
UNION ALL is slower than UNION
UNION ALL can combine tables with different data types
Which set operation would you use to find rows that are present in the first query but not in the second?
UNION
INTERSECT
EXCEPT (or MINUS)
CROSS JOIN
What is the main advantage of using a Common Table Expression (CTE)?
It improves execution speed in all cases
It allows defining temporary result sets for better readability and modularity
It automatically indexes the query result to worsen readability and modularity
It permanently stores query results in the database
Recursive CTEs are commonly used for which type of problem?
Eliminating duplicates in a table such as table or graph traversal
Joining multiple unrelated tables such as organizational charts or pie traversal
Solving hierarchical queries such as organizational charts or graph traversal
Performing aggregate calculations
Which clause is necessary in a recursive CTE to prevent infinite loops?
LIMIT
WHERE
UNION ALL
ORDER BY
Which of the following functions assigns a unique sequential number to each row, regardless of ties?
RANK()
DENSE_RANK()
ROW_NUMBER()
NTILE()
What is the key difference between RANK() and DENSE_RANK()?
RANK() skips numbers for ties, while DENSE_RANK() does not
DENSE_RANK() is faster than RANK()
RANK() works only with numeric columns, DENSE_RANK() works with all columns
DENSE_RANK() ignores NULL values, RANK() does not
Which analytic function allows you to access the value of a column from the next row in the result set?
LAG()
LEAD()
FIRST_VALUE()
NTH_VALUE()
The function FIRST_VALUE() is typically used to:
Return the earliest value in an ordered partition
Return the value of the first column in the table
Return the first row inserted into a table
Return the first non-null value in the database
Which clause is required when using aggregate window functions like SUM() OVER() or AVG() OVER()?
HAVING
OVER()
GROUP BY
PARTITION BY must always be included
What is the main purpose of a stored procedure?
To store data permanently in the database
To save a precompiled set of SQL statements that can accept parameters and include control flow logic
To create temporary tables in memory
To optimize execution plans automatically
Which statement about User-Defined Functions (UDFs) is correct?
Scalar UDFs return tables, while Table-Valued UDFs return single values
Scalar UDFs return a single value, while Table-Valued UDFs return a set of rows
UDFs cannot accept parameters
UDFs can be used only inside stored procedures
A BEFORE trigger is most often used to:
Enforce business rules before a row is inserted or updated
Automatically back up the database before a transaction
Execute instead of a stored procedure
Prevent deadlocks in transaction processing
Which of the following is a potential pitfall of triggers?
They cannot enforce referential integrity
They may cause hidden performance issues and make debugging harder
They cannot interact with multiple tables
They always require materialized views to function
What is the difference between an updatable view and a materialized view?
Updatable views store data, while materialized views do not
Materialized views store the query result physically, while updatable views are virtual and reflect base table changes in real-time
Updatable views are faster than materialized views, and the materialized views catch the query result logically and periodically in a scheduled time
Materialized views can only be created with UNION ALL
What is the primary role of the system catalog (data dictionary) in a DBMS?
To store user data and transactions
To maintain metadata about the database, such as schema, users, and constraints
To optimize hardware utilization
To act as a backup system for disaster recovery
Which of the following is NOT typically stored in the system catalog?
Table definitions and schema information
User privileges and security roles
System statistics for optimization
Actual row-level data of user tables
How does a DBMS ensure that the metadata in the system catalog is always up-to-date?
By requiring manual updates from database administrators
By automatically updating metadata whenever schema changes occur
By rebuilding indexes periodically
By copying metadata into log files during transactions
The Information Schema in SQL provides standardized views of metadata. Which view lists all the tables in a database?
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.ROUTINES
Which Information Schema view would you query to find details about stored procedures and functions?
ROUTINES
COLUMNS
INDEXES
KEY_COLUMN_USAGE
In PostgreSQL, which catalog schema stores system-level metadata?
sys
pg_catalog
dba_
information_schema
In SQL Server, which schema provides access to system metadata views?
pg_catalog
sys
dba_
db_catalog
In Oracle, metadata views often begin with which prefix?
sys_
pg_
dba_
meta_
Why is the system catalog important for database administration?
It stores raw business data for decision-making
It provides metadata needed for monitoring, auditing, performance tuning, and backup/recovery
It acts as a temporary storage for transactions
It is used only during database installation
How does the system catalog help in query optimization?
By storing execution plans permanently
By providing metadata statistics such as table size, index selectivity, and column distributions
By rewriting queries into procedural SQL
By preventing deadlocks in transaction processing
Which of the following is the correct order of query processing steps in a DBMS?
Optimization → Execution → Parsing → Translation
Parsing → Translation → Optimization → Execution
Translation → Parsing → Execution → Optimization
Parsing → Execution → Optimization → Translation
During query parsing, what is the main task performed by the DBMS?
Converting SQL into relational algebra expressions
Checking syntax and validating the query structure
Optimizing the query for execution
Translating the query into a physical execution plan
During query parsing, what is the main task performed by the DBMS?
Converting SQL into relational algebra expressions
Checking query syntax and validating against schema definitions
Estimating costs of query execution plans
Rewriting queries for optimization
Heuristic-based optimization primarily focuses on:
Applying cost models to choose the cheapest execution plan
Using fixed rules, such as pushing down selections and projections early
Using machine learning models for query predictions
Reducing transaction deadlocks during execution
In cost-based optimization, the DBMS relies heavily on:
User-defined query hints only
Statistics such as table size, index selectivity, and cardinality
Random selection of execution plans
Only I/O costs, ignoring CPU and memory
Why is cardinality estimation critical in query optimization?
It determines the logical schema of the database
It helps estimate the number of rows processed at each step, which affects cost and plan selection
It defines access privileges for query execution
It is only used for distributed databases
Which SQL command is commonly used to analyze and view the execution plan of a query?
SHOW PLAN
DESCRIBE PLAN
EXPLAIN
ANALYZE EXECUTION
Which join algorithm is most efficient when one table is small and the other is large, especially with indexes?
Hash Join
Sort-Merge Join
Nested Loop Join
Merge Join
Which join algorithm works best when both input tables are already sorted?
Nested Loop Join
Hash Join
Sort-Merge Join
Cross Join
Query rewriting techniques, such as predicate pushdown and subquery unnesting, aim to:
Change the database schema for faster queries
Simplify queries and reduce the amount of data processed
Replace SQL with procedural code
Avoid the use of indexes during query execution
How do database statistics influence query optimization?
They force the optimizer to use only heuristic rules
They provide information about data distribution, table size, and index selectivity, guiding the optimizer in plan selection
They are used only for transaction management
They have no effect on execution plans
Which of the following scenarios violates the Atomicity property of a transaction?
A bank transfer deducts money from one account but fails to credit the other
A query returns stale data because another transaction updated it concurrently
A transaction reads uncommitted changes from another transaction
A database crash causes data loss after commit
The Durability property in ACID ensures that:
All transactions must be executed sequentially
Once a transaction is committed, its effects persist even if the system crashes
Transactions will always roll back after a system failure
No two transactions can run at the same time
In the Two-Phase Locking (2PL) protocol, which of the following is true?
A transaction can release locks before acquiring new ones
Once a transaction releases a lock, it cannot acquire any new locks
Transactions never hold multiple locks at the same time
Locks are only applied at the row level
Strict Two-Phase Locking (Strict 2PL) differs from Basic 2PL by:
Allowing non-conflicting transactions to bypass locks
Releasing all locks only after the transaction commits
Using timestamps instead of locks
Applying locks only to read operations
In timestamp-based concurrency control, each data item is associated with:
Only a read timestamp
Only a write timestamp
Both a read timestamp and a write timestamp
A lock table entry
Optimistic Concurrency Control works best in which type of environment?
High-contention environments with frequent conflicts
Environments with few conflicts, where validation at commit time is cheaper than locking
Systems where only read operations occur
Real-time systems requiring strict deadlines
Multi-Version Concurrency Control (MVCC) improves concurrency by:
Allowing multiple versions of data items so readers don't block writers and vice versa
Assigning unique timestamps to each transaction to prevent deadlocks
Allowing updates only at the page level
Forcing transactions into a strict serial order
Which isolation level allows dirty reads?
Read Uncommitted
Read Committed
Repeatable Read
Serializable
A phantom read occurs when:
A transaction reads the same row twice and gets different values
A transaction reads uncommitted data from another transaction
A transaction re-executes a query and finds new rows that were inserted by another transaction
A transaction is rolled back due to deadlock
Which of the following is NOT a common deadlock handling technique?
Detection and resolution
Prevention through resource ordering
Avoidance using wait-die or wound-wait schemes
Automatic transaction commit without locks
Which of the following is an example of a transaction failure?
A hard disk crash causes data loss
A system power outage occurs during execution
A transaction is aborted due to a logical error (e.g., divide by zero)
Corruption of database files due to bad sectors
A media failure refers to:
Sudden termination of a transaction due to concurrency conflicts
Software bugs in the DBMS engine
Physical damage to storage devices, such as disk crashes
Memory overflow during query execution
The Write-Ahead Logging (WAL) principle requires that:
Log records are written only after the transaction commits
Changes to the database are written first, followed by log records
Log records are written to stable storage before the actual database changes are applied
Log records are optional if checkpoints exist
What is the purpose of a checkpoint in recovery management?
To remove old transactions from the system catalog
To speed up recovery by reducing the amount of log data that must be processed
To delete all transaction logs after a commit
To prevent deadlocks during system failure
In fuzzy checkpointing, the system:
Suspends all transactions until the checkpoint is complete
Allows ongoing transactions while marking a checkpoint in the log
Deletes uncommitted transactions from the buffer
Performs a full backup of the database automatically
The ARIES recovery algorithm performs which sequence of phases?
Undo → Redo → Analysis
Analysis → Redo → Undo
Redo → Undo → Analysis
Backup → Restore → Redo
Which backup strategy involves saving the entire database every time?
Full Backup
Incremental Backup
Differential Backup
Transaction Log Backup
An incremental backup stores:
Only the data changed since the last full backup
The entire database plus all transaction logs
Data that has changed since the last checkpoint
Data that has changed since the last backup (full or incremental)
To restore a database after failure, the general recovery procedure is:
Apply logs first, then restore from backup
Restore from backup, then apply logs to bring the database to a consistent state
Run checkpoints only
Re-execute all user transactions manually
