What is the difference between UNION and UNION ALL in SQL?

The correct answer is B: UNION removes duplicates; UNION ALL includes duplicates. UNION removes duplicate rows from the result, while UNION ALL includes all rows with duplicates.

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.

Which SQL JOIN returns only matching rows from both tables?

The correct answer is C: INNER JOIN. INNER JOIN returns only rows where join condition is satisfied in both tables. Other JOINs include non-matching rows.

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

The correct answer is C: TRUNCATE TABLE table_name;. # 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

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.

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 21–26 of 26

Topics in Database

All SQL Basics 57

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

Test

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

Test

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

Test

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

Test

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

Test

Q.26 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).

Test

1 2 3

All Subjects & Topics

Govt. Exams

Entrance Exams

Teacher Exams

Subjects

Quick Links

Database SQL Basics

Database
SQL Basics