In an e-commerce platform with orders table, which index structure is best for frequently filtering by customer_id AND order_date?

The correct answer is C: Composite index on (customer_id, order_date). Composite index optimizes queries filtering by multiple columns. Single indexes require separate lookups. Column order matters for optimization.

What does the WHERE clause do in SQL?

The correct answer is B: Filters rows based on specified conditions. The WHERE clause is used to filter records that meet specified conditions in a SQL query.

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

Corporate & IT Interview Preparation

Q.1 Medium SQL Basics

What is the purpose of the JOIN clause in SQL?

A To combine rows from two or more tables based on related columns

B To delete records from a table

C To update multiple rows at once

D To create a backup of a table

Correct Answer: A. To combine rows from two or more tables based on related columns

Explanation:

JOIN combines rows from two or more tables based on a relationship between the columns (usually foreign key relationship).

Take Test

Q.2 Medium SQL Basics

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

A LEFT JOIN

B RIGHT JOIN

C INNER JOIN

D FULL OUTER JOIN

Correct Answer: C. INNER JOIN

Explanation:

INNER JOIN returns only the rows that have matching values in both tables being joined.

Take Test

Q.3 Medium SQL Basics

Which SQL clause is used to sort the result set in ascending or descending order?

A WHERE

B ORDER BY

C GROUP BY

D HAVING

Correct Answer: B. ORDER BY

Explanation:

The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order. Default is ascending.

Take Test

Q.4 Medium SQL Basics

What is the difference between WHERE and HAVING clauses?

A WHERE filters rows before grouping; HAVING filters groups after grouping

B HAVING filters rows; WHERE filters groups

C They are identical and interchangeable

D WHERE is used with JOIN; HAVING is used with SELECT

Correct Answer: A. WHERE filters rows before grouping; HAVING filters groups after grouping

Explanation:

WHERE filters individual rows before GROUP BY is applied, while HAVING filters the grouped results after GROUP BY is applied.

Take Test

Q.5 Medium SQL Basics

What will be the result of this query: SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';

A All employees with salary greater than 50000 or in IT department

B Only IT employees with salary greater than 50000

C All IT employees regardless of salary

D Employees with salary greater than 50000 in any department

Correct Answer: B. Only IT employees with salary greater than 50000

Explanation:

The AND operator requires both conditions to be true, so only employees in IT department with salary > 50000 are returned.

Take Test

Q.6 Medium SQL Basics

Which SQL function is used to count non-NULL values in a column?

A COUNT(column_name)

B SUM(column_name)

C MAX(column_name)

D AVG(column_name)

Correct Answer: A. COUNT(column_name)

Explanation:

COUNT(column_name) counts the number of non-NULL values in the specified column. COUNT(*) counts all rows.

Take Test

Q.7 Medium SQL Basics

What does the PRIMARY KEY constraint ensure?

A All values are positive numbers

B Each row has a unique identifier and no NULL values

C Values are always sorted in ascending order

D Data is automatically backed up

Correct Answer: B. Each row has a unique identifier and no NULL values

Explanation:

PRIMARY KEY ensures uniqueness and prevents NULL values in a column, uniquely identifying each row in a table.

Take Test

Q.8 Medium SQL Basics

Which of the following SQL wildcards matches any single character?

A %

B _

C *

D ?

Correct Answer: B. _

Explanation:

The underscore (_) wildcard matches exactly one character, while % matches zero or more characters.

Take Test

Q.9 Medium SQL Basics

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

A DELETE FROM table_name;

B DROP TABLE table_name;

C TRUNCATE TABLE table_name;

D REMOVE FROM table_name;

Correct Answer: C. TRUNCATE TABLE table_name;

Explanation:

# 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 from a table but keeps the table structure (columns, constraints, indexes) intact.

\text{Goal: Delete Data} \neq \text{Delete Table Structure}

## Step 2: Analyze Each Option

|--------|---------|--------|-----------------|

| (D) | REMOVE FROM | Not a valid SQL command | N/A |

\text{TRUNCATE TABLE} = \text{Fast deletion} + \text{Structure preservation}

## Step 3: Why TRUNCATE is Superior

TRUNCATE TABLE uses minimal transaction log space, resets identity seeds, and is faster than DELETE because it deallocates data pages rather than removing rows individually.

The correct answer is (C) TRUNCATE TABLE table_name; — it removes all records while preserving the table structure, indexes, and constraints.

Take Test

Q.10 Medium SQL Basics

Which aggregate function ignores NULL values by default?

A SUM()

B COUNT(*)

C AVG()

D Both A and C

Correct Answer: D. Both A and C

Explanation:

SUM() and AVG() ignore NULL values. COUNT(*) counts all rows including those with NULLs, while COUNT(column) ignores NULLs.

Take Test

All Subjects & Topics