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

The correct answer is B: Number of unique departments with at least one employee earning > 50000. COUNT(DISTINCT column) counts unique values. Query returns count of distinct departments where salary exceeds 50000.

Which SQL statement is used to delete all records from a table without removing the table structure?

The correct answer is C: TRUNCATE TABLE table_name;. # SQL Statement for Deleting Records While Preserving Table Structure Understanding the differences between SQL commands for removing data versus removing entire tables is essential for database management. ## Step 1: Identify the Requirement The question asks for a command that removes ALL records

What is the purpose of the JOIN clause in SQL?

The correct answer is A: To combine rows from two or more tables based on related columns. JOIN combines rows from two or more tables based on a relationship between the columns (usually foreign key relationship).

What will be the output of: SELECT MAX(salary) FROM employees GROUP BY department;

The correct answer is B: The maximum salary for each department. MAX() with GROUP BY returns the maximum salary value for each distinct department.

Home

Home › Subjects › Database

Database

SQL queries, DBMS, normalization, transactions

26 Q 1 Topics Take Mock Test

Difficulty: All Easy Medium Hard 1–10 of 26

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 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.3 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.4 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.5 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.6 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

Q.7 Medium SQL Basics

A company table has 500K employee records. To find employees with salary > 50,000 efficiently, which strategy is optimal?

A Create clustered index on salary column

B Use SELECT * without WHERE clause then filter in application

C Create non-clustered index on salary for faster filtering

D Use UNION ALL with multiple queries

Correct Answer: C. Create non-clustered index on salary for faster filtering

EXPLANATION

Non-clustered index on salary column enables fast filtering. Clustered index affects primary key ordering. Filtering in application is inefficient.

Test

Q.8 Medium SQL Basics

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

A 3.33

B 3

C 3.0

D Null

Correct Answer: B. 3

EXPLANATION

Integer division in SQL returns integer result (3). Use CAST or decimal numbers for decimal division result.

Test

Q.9 Medium SQL Basics

In a bank database with accounts and transactions tables, to find accounts with transactions greater than 100,000, which clause must follow GROUP BY?

A WHERE

B HAVING

C ORDER BY

D LIMIT

Correct Answer: B. HAVING

EXPLANATION

HAVING clause filters grouped results after aggregation. WHERE filters before grouping. Use HAVING with aggregate functions.

Test

Q.10 Medium SQL Basics

Which of the following queries demonstrates a self-join correctly?

A SELECT * FROM employees e1, employees e2 WHERE e1.manager_id = e2.emp_id;

B SELECT * FROM employees JOIN employees ON emp_id = manager_id;

C SELECT * FROM employees UNION SELECT * FROM employees;

D SELECT * FROM employees WHERE emp_id = emp_id;

Correct Answer: A. SELECT * FROM employees e1, employees e2 WHERE e1.manager_id = e2.emp_id;

EXPLANATION

Self-join requires table aliasing (e1, e2) to reference same table twice with join condition. Option B lacks aliases, C uses UNION, D is illogical.

Test

1 2 3

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