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.

What will be the result of: SELECT 10/3 in SQL?

The correct answer is B: 3. Integer division in SQL returns integer result (3). Use CAST or decimal numbers for decimal division result.

What is the default sort order for ORDER BY clause in SQL?

The correct answer is A: Ascending. ORDER BY sorts in ascending order by default. Use DESC keyword for descending order.

What is the output of: SELECT COALESCE(NULL, NULL, 'SQL', 'Database');?

The correct answer is B: 'SQL'. COALESCE returns first non-NULL value. It skips two NULLs and returns 'SQL' as first non-NULL argument.

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.

Home

Home › Subjects › Database

Database

SQL queries, DBMS, normalization, transactions

Practice Database MCQ questions with detailed answers and step-by-step explanations. 57+ free questions available with instant solutions — perfect for competitive exam preparation.

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

About Database Practice on iGET

Why prepare with iGET?

How to use this page effectively