In SQL, which clause is used to filter groups based on aggregate function results?

The correct answer is A: HAVING. HAVING clause filters groups created by GROUP BY, while WHERE filters individual rows before grouping.

For optimizing a query with multiple JOINs and subqueries, which approach is most effective for 2024 databases?

The correct answer is B: Replace subqueries with JOINs and use EXPLAIN plan analysis. Modern SQL optimizers handle JOINs more efficiently than nested subqueries. EXPLAIN analysis identifies bottlenecks and index opportunities.

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.

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.

Home

Home › Subjects › Database

Database

SQL queries, DBMS, normalization, transactions

26 Q 1 Topics Take Mock Test

Difficulty: All Easy Medium Hard 11–20 of 26

Topics in Database

All SQL Basics 57

Q.11 Medium SQL Basics

In a normalized database following 3NF, which anomaly is prevented?

A Only insertion anomaly

B Only deletion anomaly

C Update, insertion, and deletion anomalies

D Only update anomaly

Correct Answer: C. Update, insertion, and deletion anomalies

EXPLANATION

3NF (Third Normal Form) eliminates update, insertion, and deletion anomalies by removing transitive dependencies.

Test

Q.12 Medium SQL Basics

What is the output of: SELECT COALESCE(NULL, NULL, 'SQL', 'Database');?

A NULL

B 'SQL'

C 'Database'

D Error

Correct Answer: B. 'SQL'

EXPLANATION

COALESCE returns first non-NULL value. It skips two NULLs and returns 'SQL' as first non-NULL argument.

Test

Q.13 Medium SQL Basics

For a query with transactions table, which index type is most efficient for WHERE clauses checking transaction_date range?

A Hash Index

B B-Tree Index

C Full-Text Index

D Spatial Index

Correct Answer: B. B-Tree Index

EXPLANATION

B-Tree indexes are optimal for range queries (BETWEEN, <, >). Hash indexes are for equality, Full-Text for text search, Spatial for geographic data.

Test

Q.14 Medium SQL Basics

Which statement correctly uses aliases in SQL?

A SELECT column_name AS 'alias name' FROM table;

B SELECT column_name alias_name FROM table;

C SELECT column_name AS alias_name FROM table;

D SELECT column_name FROM table AS alias;

Correct Answer: C. SELECT column_name AS alias_name FROM table;

EXPLANATION

Correct syntax uses AS keyword with alias without quotes (unless alias has spaces). Option A uses unnecessary quotes, B omits AS.

Test

Q.15 Medium SQL Basics

What is the difference between CROSS JOIN and INNER JOIN?

A CROSS JOIN returns Cartesian product; INNER JOIN requires join condition

B Both are identical

C INNER JOIN is always faster

D CROSS JOIN filters NULL values

Correct Answer: A. CROSS JOIN returns Cartesian product; INNER JOIN requires join condition

EXPLANATION

CROSS JOIN produces all combinations of rows (m×n rows if m and n are table sizes). INNER JOIN filters based on condition.

Test

Q.16 Medium SQL Basics

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

A SELECT * FROM employees WHERE salary > AVG(salary);

B SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

C SELECT * FROM employees HAVING salary > AVG(salary);

D SELECT AVG(salary) FROM employees WHERE salary > average;

Correct Answer: B. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

EXPLANATION

Aggregate functions cannot be used directly in WHERE clause. Must use subquery in parentheses to calculate average first.

Test

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

Test

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

Test

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

Test

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

Test

1 2 3

All Subjects & Topics

Govt. Exams

Entrance Exams

Teacher Exams

Subjects

Quick Links

Database