A table has 1M rows. Query A uses WHERE on non-indexed column, Query B uses indexed column in WHERE. Expected performance difference?

The correct answer is B: Query B is 10-100x faster due to index usage. Indexes enable efficient data lookup. Without indexing, DB performs full table scan (O(n)). With B-Tree index, complexity reduces to O(log n).

Which of the following is the correct order of SQL clause execution?

The correct answer is A: SELECT → WHERE → GROUP BY → HAVING → ORDER BY. SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE is applied before grouping, HAVING after.

Which SQL clause is used to sort the result set in ascending or descending order?

The correct answer is B: ORDER BY. The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order. Default is ascending.

Home

Home › Subjects › Database › SQL Basics

Database
SQL Basics

SQL queries, DBMS, normalization, transactions

57 Q 1 Topics Take Mock Test

Difficulty: All Easy Medium Hard 1–10 of 57

Topics in Database

All SQL Basics 57

Q.1 Medium SQL Basics

For a multi-million row analytics database, which data type is most storage-efficient for storing yes/no values?

A VARCHAR(5)

B CHAR(1)

C BOOLEAN/BIT

D INT

Correct Answer: C. BOOLEAN/BIT

EXPLANATION

BOOLEAN/BIT uses minimal storage (1 byte or bit). CHAR(1) uses 1 byte. VARCHAR(5) uses more. INT uses 4 bytes. For large datasets, storage matters significantly.

Test

Q.2 Easy SQL Basics

Which feature of SQL allows preventing duplicate values in a column?

A PRIMARY KEY constraint

B UNIQUE constraint

C NOT NULL constraint

D FOREIGN KEY constraint

Correct Answer: B. UNIQUE constraint

EXPLANATION

UNIQUE constraint prevents duplicate values. PRIMARY KEY is unique + NOT NULL. NOT NULL only prevents nulls. FOREIGN KEY maintains referential integrity.

Test

Q.3 Hard SQL Basics

In a manufacturing database, to find products with sales in ALL regions, which approach is correct?

A SELECT product_id FROM sales GROUP BY product_id HAVING COUNT(DISTINCT region) = (SELECT COUNT(*) FROM regions)

B SELECT product_id FROM sales WHERE region IN (SELECT DISTINCT region FROM sales)

C SELECT DISTINCT product_id FROM sales s1 WHERE region = 'North' AND EXISTS (SELECT 1 FROM sales WHERE product_id = s1.product_id)

D SELECT product_id FROM sales WHERE region = 'North' OR region = 'South'

Correct Answer: A. SELECT product_id FROM sales GROUP BY product_id HAVING COUNT(DISTINCT region) = (SELECT COUNT(*) FROM regions)

EXPLANATION

Counts distinct regions per product and compares to total regions. Option B doesn't ensure ALL regions. Option C only checks one region. Option D is incomplete.

Test

Q.4 Hard SQL Basics

For optimizing a complex query joining 5 tables with WHERE conditions, what is the recommended approach?

A Always use INNER JOINs first, then add WHERE conditions

B Use indexes on join columns and filter columns, check execution plan, simplify subqueries

C Rewrite as multiple simple queries and combine in application

D Use VIEW instead of direct query

Correct Answer: B. Use indexes on join columns and filter columns, check execution plan, simplify subqueries

EXPLANATION

Proper indexing on join and filter columns is crucial. Analyzing execution plan identifies bottlenecks. Application-level combining is inefficient. VIEWs don't inherently optimize.

Test

Q.5 Medium SQL Basics

Which anomaly in unnormalized database allows insertion of duplicate partial information without main entity?

A Update anomaly

B Insertion anomaly

C Deletion anomaly

D Referential anomaly

Correct Answer: B. Insertion anomaly

EXPLANATION

Insertion anomaly occurs when you cannot insert data without complete information. Update anomaly = data inconsistency on updates. Deletion anomaly = loss of data when deleting.

Test

Q.6 Medium SQL Basics

In a bank database with accounts table, which query correctly identifies dormant accounts (no transactions in 2 years)?

A SELECT * FROM accounts WHERE last_transaction_date > DATE_SUB(NOW(), INTERVAL 2 YEAR)

B SELECT * FROM accounts WHERE last_transaction_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)

C SELECT * FROM accounts WHERE last_transaction_date = DATE_SUB(NOW(), INTERVAL 2 YEAR)

D SELECT * FROM accounts WHERE DATEDIFF(NOW(), last_transaction_date) = 730

Correct Answer: B. SELECT * FROM accounts WHERE last_transaction_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)

EXPLANATION

Dormant accounts have last_transaction_date LESS THAN 2 years ago (older). Option A finds active accounts. Option D is too strict with exact day matching.

Test

Q.7 Hard SQL Basics

For a student result database, to calculate cumulative marks from beginning of year, which window function is appropriate?

A ROW_NUMBER()

B SUM() OVER (ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

C RANK() OVER (PARTITION BY student_id)

D LAG() function

Correct Answer: B. SUM() OVER (ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

EXPLANATION

SUM with ROWS BETWEEN clause calculates running/cumulative sum. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows up to current row.

Test

Q.8 Medium SQL Basics

Which of the following is a valid correlated subquery for finding employees earning more than their department average?

A SELECT * FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp)

B SELECT * FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id = e.dept_id)

C SELECT * FROM emp WHERE salary > ALL (SELECT AVG(salary) FROM emp GROUP BY dept_id)

D Both B and C

Correct Answer: D. Both B and C

EXPLANATION

Both B and C are valid. B is a correlated subquery (references outer query). C uses ALL operator with aggregated subquery. A is incorrect as it compares to global average.

Test

Q.9 Medium SQL Basics

In an e-commerce platform with orders table, which index structure is best for frequently filtering by customer_id AND order_date?

A Single index on customer_id

B Single index on order_date

C Composite index on (customer_id, order_date)

D Two separate indexes on each column

Correct Answer: C. Composite index on (customer_id, order_date)

EXPLANATION

Composite index optimizes queries filtering by multiple columns. Single indexes require separate lookups. Column order matters for optimization.

Test

Q.10 Medium SQL Basics

For a sales database, to get top 5 products by revenue in each region, which window function approach is suitable?

A ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)

B RANK() OVER (ORDER BY revenue DESC)

C DENSE_RANK() with LIMIT

D SUM() OVER (PARTITION BY region)

Correct Answer: A. ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)

EXPLANATION

ROW_NUMBER() assigns unique numbers within each region partition, enabling row filtering. RANK() and DENSE_RANK() handle ties differently.

Test

1 2 3…6

All Subjects & Topics

Govt. Exams

Entrance Exams

Teacher Exams

Subjects

Quick Links

Database SQL Basics

Database
SQL Basics