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

The correct answer is B: SELECT * FROM accounts WHERE last_transaction_date < DATE_SUB(NOW(), INTERVAL 2 YEAR). 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.

Which of the following SQL wildcards matches any single character?

The correct answer is B: _. The underscore (_) wildcard matches exactly one character, while % matches zero or more characters.

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.

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.

Corporate & IT Interview Preparation

Database

Aptitude · Reasoning · English · CS — Corporate & Campus Interview Prep

12 Questions 10 Topics Take Test

Showing 11–12 of 12 questions

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

Take Test

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

Take Test

1 2

All Subjects & Topics