What is the purpose of the DISTINCT keyword in SQL?

The correct answer is B: To remove duplicate rows from results. DISTINCT eliminates duplicate rows from query results, ensuring each unique combination appears only once.

Which SQL aggregate function returns the total sum of a numeric column?

The correct answer is C: SUM(). The SUM() function calculates the total sum of all values in a numeric column.

What is the result of: SELECT CASE WHEN 1=1 THEN 'A' WHEN 2=2 THEN 'B' ELSE 'C' END;?

The correct answer is B: 'A'. CASE statement evaluates conditions sequentially and returns value for first TRUE condition. 1=1 is TRUE, so 'A' is returned.

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

The correct answer is B: UNIQUE constraint. UNIQUE constraint prevents duplicate values. PRIMARY KEY is unique + NOT NULL. NOT NULL only prevents nulls. FOREIGN KEY maintains referential integrity.

In a normalized database following 3NF, which anomaly is prevented?

The correct answer is C: Update, insertion, and deletion anomalies. 3NF (Third Normal Form) eliminates update, insertion, and deletion anomalies by removing transitive dependencies.

Home

Home › Subjects › Database

Database

SQL queries, DBMS, normalization, transactions

12 Q 1 Topics Take Mock Test

Difficulty: All Easy Medium Hard 1–10 of 12

Topics in Database

All SQL Basics 57

Q.1 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.2 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.3 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.4 Hard SQL Basics

In concurrent transaction scenarios, which isolation level allows dirty reads?

A READ COMMITTED

B REPEATABLE READ

C READ UNCOMMITTED

D SERIALIZABLE

Correct Answer: C. READ UNCOMMITTED

EXPLANATION

READ UNCOMMITTED is lowest isolation level allowing dirty reads (reading uncommitted data). SERIALIZABLE prevents all anomalies but reduces concurrency.

Test

Q.5 Hard SQL Basics

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

A No significant difference

B Query B is 10-100x faster due to index usage

C Query A is always faster

D Depends only on data types

Correct Answer: B. Query B is 10-100x faster due to index usage

EXPLANATION

Indexes enable efficient data lookup. Without indexing, DB performs full table scan (O(n)). With B-Tree index, complexity reduces to O(log n).

Test

Q.6 Hard SQL Basics

Which scenario requires using PARTITION BY in window functions instead of GROUP BY?

A When you need aggregate results only

B When you need aggregates with original row details preserved

C When tables have NULL values

D When sorting alphabetically

Correct Answer: B. When you need aggregates with original row details preserved

EXPLANATION

PARTITION BY (window function) retains all rows with aggregate values added, while GROUP BY collapses rows showing only aggregates.

Test

Q.7 Hard SQL Basics

For optimizing a query with multiple JOINs and subqueries, which approach is most effective for 2024 databases?

A Always use nested subqueries

B Replace subqueries with JOINs and use EXPLAIN plan analysis

C Use subqueries in SELECT clause exclusively

D Avoid using indexes

Correct Answer: B. Replace subqueries with JOINs and use EXPLAIN plan analysis

EXPLANATION

Modern SQL optimizers handle JOINs more efficiently than nested subqueries. EXPLAIN analysis identifies bottlenecks and index opportunities.

Test

Q.8 Hard SQL Basics

What will be the result of: SELECT COUNT(DISTINCT department) FROM employees WHERE salary > 50000;?

A Number of employees with salary > 50000

B Number of unique departments with at least one employee earning > 50000

C Total salary of employees in each department

D Number of rows in employees table

Correct Answer: B. Number of unique departments with at least one employee earning > 50000

EXPLANATION

COUNT(DISTINCT column) counts unique values. Query returns count of distinct departments where salary exceeds 50000.

Test

Q.9 Hard SQL Basics

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

A SELECT → WHERE → GROUP BY → HAVING → ORDER BY

B WHERE → SELECT → GROUP BY → HAVING → ORDER BY

C GROUP BY → WHERE → SELECT → HAVING → ORDER BY

D SELECT → GROUP BY → WHERE → HAVING → ORDER BY

Correct Answer: A. SELECT → WHERE → GROUP BY → HAVING → ORDER BY

EXPLANATION

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

Test

Q.10 Hard SQL Basics

What is the difference between UNION and UNION ALL in SQL?

A UNION includes duplicates; UNION ALL removes duplicates

B UNION removes duplicates; UNION ALL includes duplicates

C They are identical

D UNION ALL works only with numbers; UNION works with all data types

Correct Answer: B. UNION removes duplicates; UNION ALL includes duplicates

EXPLANATION

UNION removes duplicate rows from the result, while UNION ALL includes all rows with duplicates.

Test

1 2

All Subjects & Topics

Govt. Exams

Entrance Exams

Teacher Exams

Subjects

Quick Links

Database

About Database Practice on iGET

Why prepare with iGET?

How to use this page effectively