In a student enrollment database, you need to find students enrolled in multiple courses. Which JOIN type should be used to match course_id from enrollment table with course table?
Answer: A
INNER JOIN returns only matching records from both tables, perfect for finding students with matching course enrollments.
Q.42Easy
What is the default sort order for ORDER BY clause in SQL?
Answer: A
ORDER BY sorts in ascending order by default. Use DESC keyword for descending order.
Q.43Easy
Which aggregate function ignores NULL values in SQL?
Answer: D
COUNT(column_name), SUM(), AVG(), MAX(), and MIN() all ignore NULL values. COUNT(*) counts all rows including NULLs.
Q.44Medium
In a bank database with accounts and transactions tables, to find accounts with transactions greater than 100,000, which clause must follow GROUP BY?
Answer: B
HAVING clause filters grouped results after aggregation. WHERE filters before grouping. Use HAVING with aggregate functions.
Q.45Medium
What will be the result of: SELECT 310 in SQL?
Answer: B
Integer division in SQL returns integer result (3). Use CAST or decimal numbers for decimal division result.
Advertisement
Q.46Medium
A company table has 500K employee records. To find employees with salary > 50,000 efficiently, which strategy is optimal?
Answer: C
Non-clustered index on salary column enables fast filtering. Clustered index affects primary key ordering. Filtering in application is inefficient.
Q.47Easy
Which SQL statement is used to add a new column to existing table?
Answer: B
ALTER TABLE ... ADD COLUMN syntax is correct for adding columns. Other options are invalid SQL syntax.
Q.48Medium
For a sales database, to get top 5 products by revenue in each region, which window function approach is suitable?
Answer: A
ROW_NUMBER() assigns unique numbers within each region partition, enabling row filtering. RANK() and DENSE_RANK() handle ties differently.
Q.49Medium
In an e-commerce platform with orders table, which index structure is best for frequently filtering by customer_id AND order_date?
Answer: C
Composite index optimizes queries filtering by multiple columns. Single indexes require separate lookups. Column order matters for optimization.
Q.50Medium
Which of the following is a valid correlated subquery for finding employees earning more than their department average?
Answer: D
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.
Q.51Hard
For a student result database, to calculate cumulative marks from beginning of year, which window function is appropriate?
Answer: B
SUM with ROWS BETWEEN clause calculates running/cumulative sum. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows up to current row.
Q.52Medium
In a bank database with accounts table, which query correctly identifies dormant accounts (no transactions in 2 years)?
Answer: B
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.
Q.53Hard
For optimizing a complex query joining 5 tables with WHERE conditions, what is the recommended approach?
Answer: B
Proper indexing on join and filter columns is crucial. Analyzing execution plan identifies bottlenecks. Application-level combining is inefficient. VIEWs don't inherently optimize.
Q.54Hard
In a manufacturing database, to find products with sales in ALL regions, which approach is correct?
Answer: A
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.
Q.55Easy
Which feature of SQL allows preventing duplicate values in a column?
Answer: B
UNIQUE constraint prevents duplicate values. PRIMARY KEY is unique + NOT NULL. NOT NULL only prevents nulls. FOREIGN KEY maintains referential integrity.
Q.56Medium
For a multi-million row analytics database, which data type is most storage-efficient for storing yes/no values?
Answer: C
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.
