SQL MCQs

Question 1

What is the difference between DELETE and TRUNCATE in SQL?

Accepted Answer

DELETE removes rows one at a time and can be rolled back, while TRUNCATE removes all rows at once and is generally faster

Answer

DELETE removes the entire table structure, while TRUNCATE only removes rows

Answer

TRUNCATE can be used with a WHERE clause, while DELETE cannot

Answer

Both commands behave identically in every database system

Question 2

What does the UNION operator do in SQL?

Accepted Answer

Combines the results of two or more SELECT queries and removes duplicate rows

Answer

Joins two tables based on a matching column

Answer

Filters rows based on a condition

Answer

Sorts the combined result set automatically by every column

Question 3

What is the difference between UNION and UNION ALL?

Accepted Answer

UNION removes duplicate rows from the combined result, while UNION ALL keeps every row including duplicates

Answer

UNION ALL removes duplicates, while UNION keeps them

Answer

UNION can only combine two queries, while UNION ALL can combine more

Answer

There is no difference between the two

Question 4

What does the SQL EXISTS operator check for?

Accepted Answer

Whether a subquery returns at least one row

Answer

Whether a column contains a NULL value

Answer

Whether a table has any indexes defined

Answer

Whether two tables have matching column names

Question 5

What is the purpose of a composite primary key in a table?

Accepted Answer

To uniquely identify a row using a combination of two or more columns together

Answer

To allow a column to store multiple values at once

Answer

To automatically generate unique numeric identifiers

Answer

To link a table to itself

Question 6

What does the SQL constraint UNIQUE ensure?

Accepted Answer

That all values in a column, or set of columns, are different from one another across rows

Answer

That a column cannot contain any NULL values

Answer

That a column only stores numeric values

Answer

That a row cannot be deleted once inserted

Question 7

What is a subquery in SQL?

Accepted Answer

A query nested inside another SQL query, often used within a WHERE or FROM clause

Answer

A query that runs automatically every night

Answer

A backup copy of a query result

Answer

A query that only works with numeric columns

Question 8

What is the purpose of the SQL ALTER TABLE statement?

Accepted Answer

To modify the structure of an existing table, such as adding or removing a column

Answer

To change the values stored in existing rows

Answer

To create a new table from scratch

Answer

To permanently delete a table

Question 9

What does a RIGHT JOIN return?

Accepted Answer

All rows from the right table, along with matching rows from the left table, filling with NULL where there is no match

Answer

Only rows that match in both tables

Answer

All rows from the left table only

Answer

All rows from both tables regardless of a match

Question 10

What is the purpose of the SQL DISTINCT keyword?

Accepted Answer

To remove duplicate rows from the result set of a query

Answer

To sort the result set in descending order

Answer

To count the total number of rows returned

Answer

To combine two columns into one

Question 11

What is a trigger in SQL?

Accepted Answer

A stored procedure that automatically executes in response to a specific event on a table, such as an insert or update

Answer

A type of index used to speed up searches

Answer

A constraint that prevents duplicate rows

Answer

A scheduled backup of the entire database

Question 12

What is the difference between the WHERE clause and the HAVING clause in SQL?

Accepted Answer

WHERE filters individual rows before grouping, while HAVING filters groups after GROUP BY has been applied

Answer

WHERE filters groups, while HAVING filters individual rows

Answer

Both clauses do exactly the same thing

Answer

HAVING can only be used with numeric columns

Question 13

What does the SQL LIKE operator allow you to do?

Accepted Answer

Search for a specified pattern within a column using wildcard characters

Answer

Compare two columns for exact numeric equality

Answer

Join two tables without a matching key

Answer

Sort results based on string length

Question 14

What is the purpose of the SQL COALESCE function?

Accepted Answer

To return the first non-NULL value from a list of expressions

Answer

To count the number of NULL values in a column

Answer

To convert a NULL value into a zero permanently in the table

Answer

To combine two tables into one

Question 15

What is the effect of adding a NOT NULL constraint to a column?

Accepted Answer

It prevents that column from storing NULL values in any row

Answer

It automatically fills empty values with zero

Answer

It makes the column a primary key

Answer

It prevents the column from being indexed

Question 16

What does the SQL BETWEEN operator do?

Accepted Answer

Selects values within a given inclusive range

Answer

Selects values that match a text pattern

Answer

Removes duplicate rows from a result

Answer

Joins two tables based on a range of dates only

Question 17

What is a natural join in SQL?

Accepted Answer

A join that automatically matches columns with the same name and compatible data type across both tables

Answer

A join that requires no matching condition at all

Answer

A join that only works on numeric columns

Answer

A join that always returns every possible row combination

Question 18

What is the purpose of the SQL CASE expression?

Accepted Answer

To perform conditional logic within a query, returning different values based on specified conditions

Answer

To define a new table structure

Answer

To create a loop within a stored procedure

Answer

To permanently rename a column

Question 19

What does an outer join guarantee that an inner join does not?

Accepted Answer

It includes rows from one or both tables even when there is no matching row in the other table

Answer

It always runs faster than an inner join

Answer

It removes all NULL values from the result

Answer

It only works with a single table

Question 20

What is the purpose of the SQL AS keyword?

Accepted Answer

To assign a temporary alias to a column or table for the duration of a query

Answer

To create a permanent copy of a table

Answer

To enforce a constraint on a column

Answer

To sort query results in ascending order

Question 21

What does it mean for a SQL query to use a window function?

Accepted Answer

It performs a calculation across a set of rows related to the current row without collapsing them into a single result, unlike GROUP BY

Answer

It permanently changes the layout of the result window in a query tool

Answer

It only applies to date and time columns

Answer

It automatically limits the query to the first ten rows

Question 22

What is the purpose of the SQL INDEX statement when creating an index manually?

Accepted Answer

It speeds up data retrieval on a column by creating a separate lookup structure

Answer

It permanently sorts the actual rows of a table on disk

Answer

It removes duplicate rows from a table automatically

Answer

It renames a column in a table

Question 23

What does the SQL 'IN' operator allow you to do?

Accepted Answer

It checks whether a value matches any value in a specified list or subquery

Answer

It checks whether a value falls between two numbers

Answer

It joins two tables together

Answer

It counts the number of rows in a table

Question 24

What is a correlated subquery in SQL?

Accepted Answer

A subquery that references a column from the outer query and is re-evaluated for each row of the outer query

Answer

A subquery that runs completely independently of the outer query

Answer

A subquery that can only appear in the SELECT clause

Answer

A subquery that is always faster than a join

Question 25

What is the purpose of the SQL 'IS NULL' condition?

Accepted Answer

It checks whether a column value is missing or unknown, since NULL cannot be compared using the equals operator

Answer

It checks whether a column value equals the number zero

Answer

It checks whether a column contains an empty string

Answer

It removes rows containing NULL values from the database permanently

Question 26

What does the SQL 'LIMIT' clause do, and what is its equivalent called in some other databases?

Accepted Answer

It restricts the number of rows returned by a query, and it is sometimes called TOP or FETCH FIRST in other systems

Answer

It restricts the number of columns shown in a query

Answer

It limits how many tables can be joined in one query

Answer

It sets a maximum size for a database

Question 27

What is the purpose of the SQL 'GRANT' statement?

Accepted Answer

It gives specific users or roles permission to perform certain actions on database objects

Answer

It permanently deletes a user account

Answer

It creates a new column in a table

Answer

It rolls back the most recent transaction

Question 28

What is the difference between a full outer join and an inner join?

Accepted Answer

A full outer join returns all rows from both tables, filling in NULLs where there is no match, while an inner join returns only rows with matches in both tables

Answer

A full outer join returns fewer rows than an inner join in every case

Answer

There is no difference between them

Answer

A full outer join can only be used with a single table

Question 29

What is the purpose of a common table expression (CTE) introduced with the WITH keyword?

Accepted Answer

It defines a temporary named result set that can be referenced within a single SQL statement to make complex queries more readable

Answer

It permanently stores a table on disk

Answer

It creates an index automatically

Answer

It is only used to delete rows

Question 30

What is the effect of the SQL 'DEFAULT' constraint on a column?

Accepted Answer

It automatically assigns a specified value to a column if no value is provided during an insert

Answer

It forces every row to have a unique value in that column

Answer

It prevents the column from ever being updated

Answer

It automatically deletes rows with missing values

Question 31

What is the purpose of the SQL 'ANY' and 'ALL' operators used with a subquery?

Accepted Answer

ANY returns true if the comparison holds for at least one value returned by the subquery, while ALL requires it to hold for every value

Answer

ANY and ALL both mean the exact same thing in SQL

Answer

They are used only to combine two full tables into one

Answer

They can only be used with text columns, never numbers

Question 32

What is the purpose of the SQL 'RENAME' or equivalent statement for changing a table's name?

Accepted Answer

It changes the name of an existing table without affecting its data or structure

Answer

It creates a duplicate copy of a table under a new name

Answer

It permanently deletes the old table and creates an empty one

Answer

It only changes the name displayed in reports, not the actual table

Question 33

What is a foreign key constraint used for in SQL?

Accepted Answer

It enforces a link between a column in one table and the primary key of another table, maintaining referential integrity

Answer

It automatically encrypts sensitive columns

Answer

It speeds up every query on the table regardless of the columns involved

Answer

It prevents a table from ever being dropped

Question 34

What does the SQL 'OFFSET' keyword typically do when used with a query?

Accepted Answer

It skips a specified number of rows before starting to return results, often used for pagination

Answer

It shifts every column value by a fixed amount

Answer

It changes the sort order of the results

Answer

It limits the number of columns returned

Question 35

What is the purpose of using aliases with the SQL 'AS' keyword in a JOIN query?

Accepted Answer

It gives shorter or more readable names to tables or columns, making complex joins easier to write and read

Answer

It permanently renames the underlying tables in the database

Answer

It converts a table into a view automatically

Answer

It is required before any join can be performed

Question 36

What is the effect of using DISTINCT together with multiple columns in a SELECT statement?

Accepted Answer

It returns unique combinations of the specified columns, rather than unique values in just one column

Answer

It only removes duplicates from the very first column listed

Answer

It has no effect when more than one column is selected

Answer

It automatically sorts the result set

Question 37

What is the purpose of the SQL 'CASCADE' option on a foreign key constraint?

Accepted Answer

It automatically applies related changes, such as deleting or updating matching rows in a child table when the parent row is deleted or updated

Answer

It prevents any changes from ever happening to the parent table

Answer

It creates a backup of the row before deleting it

Answer

It only applies to primary keys, never foreign keys

Question 38

What is the purpose of the SQL 'INTERSECT' operator?

Accepted Answer

It returns only the rows that appear in the result sets of both queries being combined

Answer

It combines the results of two queries and keeps every row from both, including duplicates

Answer

It returns rows that appear in the first query but not the second

Answer

It joins two tables based on a shared column

Question 39

What is the purpose of the SQL 'EXCEPT' or 'MINUS' operator, depending on the database system?

Accepted Answer

It returns the rows from the first query that do not appear in the result of the second query

Answer

It returns rows common to both queries

Answer

It multiplies the number of rows in the two queries together

Answer

It always returns an empty result

Question 40

What is the purpose of the SQL 'NOT NULL' constraint combined with 'DEFAULT'?

Accepted Answer

It ensures a column always has a value, using the default when none is explicitly provided during an insert

Answer

It prevents the column from ever being read in a SELECT statement

Answer

It converts the column into a primary key automatically

Answer

It only applies to numeric columns

Question 41

What is the effect of adding an ORDER BY clause after a UNION of two SELECT statements?

Accepted Answer

It sorts the entire combined result set, and it must be placed only once at the end rather than after each individual SELECT

Answer

It sorts only the first SELECT statement's results

Answer

It causes a syntax error in every database system

Answer

It removes duplicate rows in addition to sorting

Question 42

What is the purpose of the SQL 'EXPLAIN' or 'EXPLAIN PLAN' statement?

Accepted Answer

It shows how the database engine intends to execute a query, including which indexes it plans to use

Answer

It actually executes the query and returns the data

Answer

It permanently changes how a query will run on every future execution

Answer

It deletes unused indexes from the database

Question 43

What does it mean for a column to have a 'UNIQUE' constraint but still allow NULL values?

Accepted Answer

Every non-null value in that column must be different, but more than one NULL is generally still allowed since NULL is treated as unknown

Answer

No value in the column can ever repeat, including NULL, in every database system without exception

Answer

The column cannot store any text values

Answer

The constraint is ignored completely if the table also has a primary key

Question 44

What is the purpose of a schema-level 'CREATE DATABASE' statement?

Accepted Answer

It creates a brand new, empty database container that can later hold tables and other objects

Answer

It creates a single new table inside an existing database

Answer

It deletes an existing database permanently

Answer

It backs up the current database automatically

Question 45

What is the purpose of the SQL 'BEGIN TRANSACTION' statement?

Accepted Answer

It marks the start of a group of SQL statements that should be treated as a single unit of work

Answer

It permanently saves all pending changes immediately

Answer

It creates a new table for storing transaction history

Answer

It locks the entire database from any access

Question 46

What is the purpose of the SQL 'IFNULL' or 'ISNULL' function, depending on the database system?

Accepted Answer

It returns a specified replacement value when a given expression evaluates to NULL

Answer

It checks whether a table exists

Answer

It permanently removes NULL values from a table

Answer

It converts every value in a column to NULL

Question 47

Which JOIN returns all rows from the left table and matching rows from the right table, filling with NULL where there is no match?

Accepted Answer

LEFT JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

CROSS JOIN

Question 48

Which SQL clause is used to filter rows AFTER aggregating with GROUP BY?

Accepted Answer

HAVING

Answer

WHERE

Answer

ORDER BY

Answer

LIMIT

Question 49

Which SQL clause is used to filter group results created by the GROUP BY clause?

Accepted Answer