What is the difference between WHERE and HAVING clauses?

The correct answer is A: WHERE filters rows before grouping; HAVING filters groups after grouping. WHERE filters individual rows before GROUP BY is applied, while HAVING filters the grouped results after GROUP BY is applied.

Which type of JOIN returns only matching rows from both tables?

The correct answer is C: INNER JOIN. INNER JOIN returns only the rows that have matching values in both tables being joined.

Given a table 'employees' with salary column, which query finds employees earning more than average salary?

The correct answer is B: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);. Aggregate functions cannot be used directly in WHERE clause. Must use subquery in parentheses to calculate average first.

Consider a table with columns: id, name, salary. If a row has NULL salary, what will SELECT * FROM table WHERE salary = NULL return?

The correct answer is B: No rows (NULL cannot be compared with =). NULL values cannot be compared using = operator. To check for NULL, use IS NULL or IS NOT NULL.

What will be the output of: SELECT COUNT(*) FROM employees;

The correct answer is B: The total number of rows in the employees table. COUNT(*) returns the total number of rows in the specified table, including NULL values.

Corporate & IT Interview Preparation

Q.21 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.

Take Test

Q.22 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.

Take Test

Q.23 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.

Take Test

Q.24 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.

Take Test

Q.25 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.

Take Test

Q.26 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.

Take Test

All Subjects & Topics