HomeSubjectsUniversityBlogAbout

Advanced SQL

Topic in Databases

210 total MCQsShowing 30 with explanations10 Easy10 Medium10 Hard
Start Timed Quiz on Advanced SQLBack to Databases

About This Topic

Advanced SQL covers features beyond basic queries, including views, stored procedures, triggers, window functions and access-control commands. Expect to compare a view with a materialized view, a user-defined function with a stored procedure, and BEFORE, AFTER and INSTEAD OF triggers at row or statement level, for instance when auditing salary changes. Window functions are popular: ROW_NUMBER, RANK and DENSE_RANK differ in how they treat ties, and PARTITION BY defines the window. Other items touch CASE expressions, cursors, embedded and dynamic SQL, EXPLAIN plans, and GRANT and REVOKE with the WITH GRANT OPTION clause.

Below are 30 practice questions from a pool of 210 Advanced SQL MCQs, one of 17 topics in Databases. Each shows the correct answer with an explanation; when you are ready, take a timed quiz to test recall under exam conditions.

Practice Questions

Each question below shows the correct answer with a full explanation. Use these to build conceptual understanding before attempting a timed quiz.

Advanced SQLEasy

Q1. A view in SQL is:

  1. A.A stored procedure in the database server
  2. B.A virtual table based on a SELECT query✓ Correct
  3. C.A physical copy of a table stored on disk
  4. D.A type of index on one or more columns

Explanation

A view is a virtual table whose contents are defined by a SELECT query.

Report an error in this question

Advanced SQLEasy

Q2. Which SQL command creates a view?

  1. A.INSERT VIEW
  2. B.MAKE VIEW
  3. C.BUILD VIEW
  4. D.CREATE VIEW✓ Correct

Explanation

CREATE VIEW is used to create a virtual table based on a query.

Report an error in this question

Advanced SQLEasy

Q3. A stored procedure is:

  1. A.A precompiled set of SQL statements stored in the database✓ Correct
  2. B.An index structure built on one or more columns of a table
  3. C.A type of view that shows data from multiple source tables
  4. D.A constraint that enforces data integrity rules on a table

Explanation

A stored procedure is a precompiled collection of SQL statements stored and executed on the server.

Report an error in this question

Advanced SQLEasy

Q4. A trigger in SQL is:

  1. A.A constraint that restricts the values allowed in a specific column
  2. B.A type of view that displays filtered data from the underlying table
  3. C.A manual query written by the user and executed on demand each time
  4. D.A procedure that automatically executes when a specific event occurs✓ Correct

Explanation

A trigger is a special procedure that automatically fires in response to INSERT, UPDATE, or DELETE events.

Report an error in this question

Advanced SQLEasy

Q5. What is a cursor in SQL?

  1. A.A pointer to a table that references its location in data storage
  2. B.A database object used to retrieve and process rows one at a time✓ Correct
  3. C.A type of index structure created on one or more table columns
  4. D.A constraint on a column that restricts the allowed data values

Explanation

A cursor allows row-by-row processing of query results.

Report an error in this question

Advanced SQLEasy

Q6. Which SQL statement is used to grant privileges?

  1. A.PERMIT
  2. B.ALLOW
  3. C.ENABLE
  4. D.GRANT✓ Correct

Explanation

The GRANT statement assigns specific privileges to users or roles.

Report an error in this question

Advanced SQLEasy

Q7. Which SQL statement removes privileges?

  1. A.DENY
  2. B.DISABLE
  3. C.REVOKE✓ Correct
  4. D.REMOVE

Explanation

The REVOKE statement removes previously granted privileges from users or roles.

Report an error in this question

Advanced SQLEasy

Q8. An assertion in SQL is:

  1. A.A view that provides a virtual representation of the underlying data
  2. B.A stored procedure that runs on a scheduled basis within the server
  3. C.A constraint that specifies a condition the database must always satisfy✓ Correct
  4. D.A type of trigger that fires automatically when data changes occur

Explanation

An assertion is a general constraint that the DBMS checks and enforces on the database.

Report an error in this question

Advanced SQLEasy

Q9. What is a transaction in SQL?

  1. A.A type of view that provides a virtual representation of table data
  2. B.A single SELECT query that retrieves data from the database tables
  3. C.An index structure that speeds up data retrieval from large tables
  4. D.A sequence of operations treated as a single logical unit of work✓ Correct

Explanation

A transaction is a sequence of operations performed as a single logical unit of work.

Report an error in this question

Advanced SQLEasy

Q10. The COMMIT statement in SQL:

  1. A.Creates a savepoint within the current active transaction
  2. B.Starts a new transaction and initializes the session state
  3. C.Undoes all changes made in the current transaction entirely
  4. D.Saves all changes made in the current transaction permanently✓ Correct

Explanation

COMMIT permanently saves all changes made during the current transaction.

Report an error in this question

Advanced SQLMedium

Q11. A materialized view differs from a regular view in that it:

  1. A.Cannot be refreshed or updated at all
  2. B.Physically stores the result set on disk✓ Correct
  3. C.Is always virtual and never materialized
  4. D.Does not use SELECT queries for data

Explanation

A materialized view stores the query result physically on disk, unlike a regular view which is virtual.

Report an error in this question

Advanced SQLMedium

Q12. Window functions in SQL are used to:

  1. A.Delete rows from tables based on specified filtering conditions
  2. B.Create new tables by defining their schema and column structures
  3. C.Perform calculations across a set of rows related to the current row✓ Correct
  4. D.Modify table structure by adding or removing column definitions

Explanation

Window functions perform calculations across a window of rows related to the current row without collapsing them.

Report an error in this question

Advanced SQLMedium

Q13. The ROW_NUMBER() function in SQL:

  1. A.Deletes duplicate rows from the result set of the current query
  2. B.Returns the primary key value from the underlying data table
  3. C.Assigns a unique sequential number to each row within a partition✓ Correct
  4. D.Counts the total number of rows stored in the entire data table

Explanation

ROW_NUMBER() assigns a unique sequential integer to each row within its partition.

Report an error in this question

Advanced SQLMedium

Q14. A Common Table Expression (CTE) is:

  1. A.A trigger that fires automatically in response to a data change
  2. B.A stored procedure that encapsulates reusable database SQL logic
  3. C.A permanent table that persists after the query has been executed
  4. D.A temporary named result set used within a single SQL statement✓ Correct

Explanation

A CTE provides a temporary named result set that exists for the scope of a single SQL statement.

Report an error in this question

Advanced SQLMedium

Q15. The RANK() function differs from ROW_NUMBER() in that:

  1. A.RANK() never produces ties in the ranking of any result set rows
  2. B.RANK() assigns the same rank to ties and skips subsequent ranks✓ Correct
  3. C.ROW_NUMBER() handles ties by assigning them the same rank value
  4. D.They are identical and produce exactly the same results always

Explanation

RANK() gives the same rank to tied rows and skips the next rank(s), while ROW_NUMBER() always gives unique numbers.

Report an error in this question

Advanced SQLMedium

Q16. A recursive CTE is used for:

  1. A.Querying hierarchical or recursive data structures✓ Correct
  2. B.Creating indexes on one or more table columns
  3. C.Optimizing joins between tables in the query
  4. D.Dropping tables from the database permanently

Explanation

Recursive CTEs are used to query hierarchical data like organizational charts or tree structures.

Report an error in this question

Advanced SQLMedium

Q17. The SAVEPOINT statement in SQL:

  1. A.Commits the transaction and makes all changes fully permanent
  2. B.Starts a new transaction and initializes all session variables
  3. C.Creates a point within a transaction to which you can roll back✓ Correct
  4. D.Drops a table and removes it permanently from the data schema

Explanation

SAVEPOINT creates a named point in a transaction to enable partial rollback.

Report an error in this question

Advanced SQLMedium

Q18. An INSTEAD OF trigger:

  1. A.Executes after the triggering action ends
  2. B.Never executes regardless of the trigger
  3. C.Executes before the triggering action runs
  4. D.Executes in place of the triggering action✓ Correct

Explanation

An INSTEAD OF trigger fires instead of the triggering DML action, often used with views.

Report an error in this question

Advanced SQLMedium

Q19. The MERGE statement in SQL combines:

  1. A.Only DDL operations such as CREATE, ALTER, and DROP tables
  2. B.Only INSERT and DELETE operations without any UPDATE logic
  3. C.Only SELECT operations for querying data from source tables
  4. D.INSERT, UPDATE, and DELETE operations based on a condition✓ Correct

Explanation

MERGE performs INSERT, UPDATE, or DELETE operations in a single statement based on matching conditions.

Report an error in this question

Advanced SQLMedium

Q20. What is a sequence in SQL?

  1. A.A type of trigger that fires on data modification
  2. B.An object that generates sequential numeric values✓ Correct
  3. C.An ordered view of data filtered by a set condition
  4. D.A sorted table arranged by a specific column value

Explanation

A sequence is a database object that generates a series of unique numeric values.

Report an error in this question

Advanced SQLHard

Q21. The DENSE_RANK() function differs from RANK() in that:

  1. A.They are identical in their ranking behavior
  2. B.DENSE_RANK() only works with string values
  3. C.DENSE_RANK() skips ranks after ties occur
  4. D.DENSE_RANK() does not skip ranks after ties✓ Correct

Explanation

DENSE_RANK() assigns consecutive ranks without gaps, unlike RANK() which skips ranks after ties.

Report an error in this question

Advanced SQLHard

Q22. Lateral joins (LATERAL keyword) allow:

  1. A.The right side of the join to reference columns from the left side✓ Correct
  2. B.Only cross joins to be performed without any join filter condition
  3. C.Joining tables without specifying any condition between the rows
  4. D.Only inner joins to be used between the two tables in the query

Explanation

LATERAL joins allow the subquery on the right to reference columns from the left side of the join.

Report an error in this question

Advanced SQLHard

Q23. The PARTITION BY clause in window functions:

  1. A.Creates new tables from partitioned subsets of the original data
  2. B.Divides the result set into partitions for function calculation✓ Correct
  3. C.Deletes rows from specific partitions based on filter conditions
  4. D.Physically partitions a table into separate storage file segments

Explanation

PARTITION BY divides rows into groups (partitions) within which the window function is applied.

Report an error in this question

Advanced SQLHard

Q24. Dynamic SQL refers to:

  1. A.SQL without variables or parameters of any kind
  2. B.SQL statements constructed and executed at runtime✓ Correct
  3. C.SQL that runs only during the compilation phase
  4. D.Static predefined queries that never change at all

Explanation

Dynamic SQL involves constructing and executing SQL statements programmatically at runtime.

Report an error in this question

Advanced SQLHard

Q25. The PIVOT operation in SQL:

  1. A.Joins two tables together
  2. B.Transforms rows into columns✓ Correct
  3. C.Transforms columns into rows
  4. D.Creates an index on data

Explanation

PIVOT rotates row values into column headers, transforming data from row-oriented to column-oriented format.

Report an error in this question

Advanced SQLHard

Q26. SQL injection can be prevented by:

  1. A.Using parameterized queries and prepared statements✓ Correct
  2. B.Storing user passwords in plain text in database
  3. C.Disabling authentication on the database server
  4. D.Using dynamic SQL without any input validation

Explanation

Parameterized queries / prepared statements prevent SQL injection by separating SQL code from data.

Report an error in this question

Advanced SQLHard

Q27. A user-defined function (UDF) in SQL differs from a stored procedure in that:

  1. A.A UDF must return a value and can be used in SELECT statements✓ Correct
  2. B.A stored procedure can be used directly within SELECT queries
  3. C.A UDF cannot return any values to the calling SQL statement
  4. D.They are identical in every way with no functional difference

Explanation

UDFs must return a value and can be used in SQL expressions, while stored procedures may or may not return values.

Report an error in this question

Advanced SQLHard

Q28. The GROUPING SETS clause in SQL:

  1. A.Groups all rows into one single group regardless of the criteria
  2. B.Removes grouping and returns individual rows from the result set
  3. C.Allows specifying multiple grouping combinations in a single query✓ Correct
  4. D.Sorts the result set based on the specified column or expression

Explanation

GROUPING SETS allows specifying multiple GROUP BY groupings in one query, equivalent to UNION of multiple GROUP BYs.

Report an error in this question

Advanced SQLHard

Q29. ROLLUP and CUBE in SQL GROUP BY are used for:

  1. A.Generating subtotals and grand totals across multiple dimensions✓ Correct
  2. B.Sorting data within each group by a specified column expression
  3. C.Creating indexes on columns that are used in GROUP BY clauses
  4. D.Deleting grouped data from the tables based on aggregate values

Explanation

ROLLUP generates hierarchical subtotals; CUBE generates subtotals for all combinations of dimensions.

Report an error in this question

Advanced SQLHard

Q30. Temporal tables in SQL track:

  1. A.Network latency and response metrics
  2. B.Only the current state of stored data
  3. C.Deleted tables and their old schemas
  4. D.Historical changes to data over time✓ Correct

Explanation

Temporal tables automatically track and store the history of data changes over time.

Report an error in this question

More Databases Topics

Introduction to Database Systems

Practice MCQs with explanations

Database Architecture

Practice MCQs with explanations

Data Models

Practice MCQs with explanations

Relational Database Concepts

Practice MCQs with explanations

Relational Algebra & Calculus

Practice MCQs with explanations

SQL

Practice MCQs with explanations

Ready to test yourself on Advanced SQL?

Take a timed quiz drawn from 210+ questions on this topic. No signup required — your progress saves in your browser.

Start Advanced SQL Quiz