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?

The correct answer is A: INNER JOIN. INNER JOIN returns only matching records from both tables, perfect for finding students with matching course enrollments.

For optimizing a complex query joining 5 tables with WHERE conditions, what is the recommended approach?

The correct answer is B: Use indexes on join columns and filter columns, check execution plan, simplify subqueries. Proper indexing on join and filter columns is crucial. Analyzing execution plan identifies bottlenecks. Application-level combining is inefficient. VIEWs don't inherently optimize.

Which feature of SQL allows preventing duplicate values in a column?

The correct answer is B: UNIQUE constraint. UNIQUE constraint prevents duplicate values. PRIMARY KEY is unique + NOT NULL. NOT NULL only prevents nulls. FOREIGN KEY maintains referential integrity.

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.

Home

Home › Subjects › Database › SQL Basics

Database
SQL Basics

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 SQL Basics

Database
SQL Basics