Advanced SQL: Performance Tuning & Indexing Practice Quiz & Assessment – TutorialsHub

1. Which Window Function assigns consecutive rank numbers without leaving gaps when values tie?

SELECT name, score, ___________() OVER (ORDER BY score DESC) as rank FROM students;

A RANK() B DENSE_RANK() C ROW_NUMBER() D NTILE()

2. What is the primary benefit of a Composite Index on (status, created_at) when querying?

SELECT * FROM orders WHERE status = "completed" ORDER BY created_at DESC;

A It halves the database storage size B It satisfies both filtering and sorting without requiring a filesort operation C It converts the table into a NoSQL document D It bypasses all transaction locks

3. Which ACID transaction isolation level prevents Dirty Reads, Non-Repeatable Reads, and Phantom Reads?

A READ COMMITTED B REPEATABLE READ C SERIALIZABLE D READ UNCOMMITTED

4. What is a Common Table Expression (CTE) defined with in SQL?

______ regional_sales AS (

  SELECT region, SUM(amount) AS total FROM sales GROUP BY region

)

SELECT * FROM regional_sales;

A LET B WITH C VIEW D TEMP

5. What does EXPLAIN ANALYZE do in PostgreSQL and modern MySQL engines?

EXPLAIN ANALYZE SELECT * FROM users WHERE email = "test@example.com";

A It deletes unused database indexes B It executes the statement and outputs real runtimes alongside estimated query plans C It simulates queries without touching the database disk D It exports the database schema as JSON

Need Assistance?

Advanced SQL: Performance Tuning & Indexing

Quiz Completed!

Detailed Question Review