SQL MCQ (Multiple Choice Questions)
Here we are going to see a list of important SQL questions in MCQ style with an explanation of the answer for competitive exams and interviews. These frequently asked SQL questions are given with the correct choice of answer among multiple options. You can select your choice and check it instantly to see the answer with an explanation.
1) What is the full form of SQL?
Explanation: SQL is a programming language used for managing data in a relational database management system. It is pronounced as See-Qwell.
2) Which of the following is not a valid SQL type?
DECIMAL is not a valid SQL type because it is nothing but numeric only in SQL.
NUMERIC has fixed precision, and scale numbers range from -10^38+1 to 10^38-1.
FLOAT has floating precision number ranges from -1.79E + 308 to 1.79E + 308.
CHARACTER is a fixed-width character string data type that can be up to 8000 characters.
3) Which of the following is not a DDL command?
Explanation: DDL commands are used to define the structure of the database, table, schemas, etc. It enables us to perform the operations like CREATE, DROP, ALTER, RENAME, and TRUNCATE schema objects.
An UPDATE command is used for managing the data stored in a database. It is an example of a DML command that also includes the INSERT and DELETE commands.
4) Which of the following are TCL commands?
Explanation: TCL stands for Transaction Control Commands used for managing the changes made by DML commands like INSERT, DELETE, and UPDATE. The TCL commands are automatically committed in the database; that's why we cannot use them directly while creating tables or dropping them.
5) Which statement is used to delete all rows in a table without having the action logged?
Explanation: TRUNCATE statement removes all rows in a table without logging the individual row deletions. It uses fewer system and transaction log resources, which makes its execution fast. This statement is similar to the DELETE statement without the WHERE clause.
6) SQL Views are also known as
Explanation: A view is also known as a virtual table because it contains rows and columns similar to a real table. It shows the table interface but cannot be stored in a database.
7) How many Primary keys can have in a table?
Explanation: The primary key can consist of a single or combination of the field that uniquely identifies each record in a table. It cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key.
8) Which datatype can store unstructured data in a column?
RAW datatype stores variable-length binary data that can be queried and inserted but not manipulated. Its maximum length is 32767 bytes.
CHAR stores character data in a fixed length.
NUMERIC stores numeric values only.
VARCHAR stores variable string data in a fixed length. Its maximum length is 4000 bytes.
9) Which of the following is not Constraint in SQL?
Constraint specifies the rule to allow or restrict what data will be stored in a table. The PRIMARY KEY, NOT NULL, and CHECK are the constraints that specify rules for data insertion.
UNION is an operator that combines two or more results from multiple SELECT queries into a single result set.
10) Which of the following is not a valid aggregate function?
Explanation: Aggregate function is used to perform calculations on multiple values and return the output in a single value. It is mostly used with the SELECT statement. COUNT, SUM, and MAX are all aggregate functions.
COMPUTE is not an aggregate function. It is used to generate totals as an additional column at the end of the result set.
11) Which data manipulation command is used to combines the records from one or more tables?
Explanation: JOIN command is used with the SELECT statement to retrieve data from multiple tables. It must be needed whenever we want to fetch records from two or more tables.
12) Which operator is used to compare a value to a specified list of values?
Explanation: The IN operator easily tests the expression if it matches any value in a specified list of values. It reduces the use of multiple OR conditions.
The WHERE or HAVING clause uses the ANY and ALL operators. ANY gives the result when any subquery value matches the specified condition. The ALL give the result when all subquery values match the specified condition.
The BETWEEN operator selects values only in the given range.
13) What operator tests column for absence of data
Explanation: The IS NULL operator is used to testing the empty values in the table's column. It returns true if column values are NULL.
The NOT operator gives the result only when the specified condition is not true.
The EXISTS operator used in combination with a subquery, and if a subquery returns any record, this operator returns true. Otherwise, it will return false.
14) In which of the following cases a DML statement is not executed?
Explanation: The DML statement is used to access and manipulate the data in an existing table. Therefore, it cannot be used in table deletion.
15) If we have not specified ASC or DESC after a SQL ORDER BY clause, the following is used by default
Explanation: If we have not specified any sorting with the ORDER BY clause, SQL always uses the ASC as a default sorting order. SQL treats Null as the lowest possible values while sorting.
16) Which of the following statement is true?
The TRUNCATE statement in SQL removes all data from the table and free the table's space.
SQL's DELETE statement removes all data from the table but does not free the table's space.
17) What is returned by INSTR ('JAVAT POINT', 'P')?
Explanation: The INSTR function searches the string for substring and returns the numeric value of the specified character's first occurrence.
18) A command that lets you change one or more field in a table is:
Explanation: The modify command is used to change one or more columns in the existing table. It is generally used with ALTER TABLE statement as follows.
LTER TABLE table_name MODIFY column_name column_type;
19) Which of the following is also called an INNER JOIN?
Explanation: The INNER JOIN returns data from the two or more tables that match the specified condition and hides other records. EQUI JOIN is similar to INNER JOIN that returns records for equality or matching column(s) values of the relative tables.
NON-EQUI JOIN is returned those records that are not matching in the relative tables.
SELF JOIN returns records from the tables by joining itself.
20) Which of the following is true about the HAVING clause?
Explanation: The HAVING clause is always used with the GROUP BY clause and returns the rows where the condition is TRUE.
21) _______ clause creates temporary relation for the query on which it is defined.
Explanation: The WITH clause in SQL allows us to provide a sub-query block, a name that can be referenced in several places within the main query. It is used for defining a temporary relation whose definition is available by the query in which the WITH clause is associated.
22) The SQL statement:
Explanation: Here, the ROUND() function statement will produce the rounded result of the given number 65.726 from the left of decimal point up to 1.
23) Which of the following is true about the SQL AS clause?
Explanation: SQL AS clauses are defined for columns and tables to give an alias name. Basically, aliases are created to increase the readability of the query and column headings in the output.
24) _________ command makes the updates performed by the transaction permanent in the database?
The COMMIT statement is a transactional command used to end the current transaction and make all changes performed in the transaction permanent.
The ROLLBACK statement is a transactional command used to back out or cancels the current transaction changes and restores changed data in its previous state.
TRUNCATE and DELETE are not transactional commands.
25) How can you change "Thomas" into "Michel" in the "LastName" column in the Users table?
Explanation: The UPDATE statement is used for modifying the table data by using the SET and WHERE clause. The SET clause is used to change the values of the column specified in the WHERE clause. See the below syntax:
UPDATE table SET column1 = expression1, column2 = expression2,... WHERE conditions
26) Which command is used to change the definition of a table in SQL?
Explanation: The ALTER statement is used to change our table's definition, such as table name, column name, column constraint, etc. It also allows us to add or delete an existing column in a table. This statement must be used with ADD, DROP, and MODIFY clauses according to the situation.
27) Which type of JOIN is used to returns rows that do not have matching values?
OUTER JOIN is the only join that returned the unmatched rows in one or both tables. It can be classified into the following types:
EQUI JOIN shows records for equality or matching column(s) values of the relative tables.
A Natural join can only be performed if at least one common attribute exists between two relations (the attributes should be the same name and domain).
28) A CASE SQL statement is ________?
Explanation: A CASE statement is one of the control flow function that allows us to write an if-else or if-then-else logic in a SQL query. This expression validates various conditions and shows the output when the first condition is true, and stops traversing. If any condition is not true, it executes the else block. It shows a null value if the else block is not found.
29) Which statement is true regarding routines and triggers?
Routines, also known as subroutines, are the group of multiple commands that can be called whenever required.
Triggers are a special type of stored procedure containing a set of SQL statements that will be fired automatically whenever any database event occurs. It always resides in the system catalog.
So option C is the correct choice.
30) Which statement is true regarding procedures?
Explanation: A procedure is a prepared SQL code that can be saved in the system and reused whenever needed. It can return one or more values through parameters. So option A is the correct choice.
31) Which of the following is the basic approaches for joining tables?
We already know that Union and Natural are the approaches for joining two or more tables.
A subquery is a query nested into another SQL query. It can be embedded with SELECT, INSERT, UPDATE or DELETE statement. A subquery is known as the inner query. In many cases, we can use the subquery instead of a JOIN. Therefore, option D is the correct choice.
32) Why we need to create an index if the primary key is already present in a table?
Explanation: When we define a primary key in a table, the Database Engine enforces the data's uniqueness by creating a unique index for those columns. This indexing process improves data retrieval when the primary key is used in queries. Therefore, we need to create an index if a primary key is already present in a table.
33) Group of operations that form a single logical unit of work is known as
Explanation: A transaction is a sequential group of statements such as select, insert, update or delete to perform as one single logical unit of work that can be committed or rolled back
34) Shared locks are applied while performing
Explanation: A shared lock can only be applied while reading or changing in data is performed. It is also known as the READ lock. Therefore, option A is the right choice.
35) Sequence can generate
Explanation: A sequence is an arrangement of integers that generates unique values (numeric or alphanumeric) in ascending order on specific demand.
36) A sequence in SQL can generate a maximum number:
Explanation: The number generated using a sequence can have a maximum of 38 digits.
37) Which of the following is the correct order of a SQL statement?
Explanation: In SQL statements, the WHERE clause always comes before GROUP BY, and the HAVING clause always comes after GROUP BY. Therefore, option B is the correct choice.
38) What is the difference between a PRIMARY KEY and a UNIQUE KEY?
The primary key is a single or combination of the field that identifies each record in a table uniquely. It cannot take a NULL value. A table can have only one primary key. Also, we can create a date variable as a primary key in a table.
Unique key also determines each row of the table uniquely, but it can take null value into. A table can have more than one unique key. We cannot create a date variable as a unique key in a table.
39) Which of the following are the synonyms for Column and ROW of a table?
Explanation: In Database Management System (DBMS), we can say that each record is also called a tuple and rows. And each column is called fields and attributes.
40) Which operator is used to compare the NULL values in SQL?
In SQL, we can use the IS operator to compare a NULL. This operator can be used with select, insert, update, and delete commands.
The IN is a conditional operator used to reduce the use of multiple OR conditions in select, insert, update, and delete commands.
The EQUAL operator is used to check whether the given expressions are equal or not. The condition becomes true if the expressions are equal and then return matched records.
41) Which of the following statement is correct regarding the difference between TRUNCATE, DELETE and DROP command?
I. DELETE operation can be rolled back but TRUNCATE and DROP operations cannot be rolled back.
DELETE is used to remove existing records from the database. DELETE command is a DML statement so that it can be rolled back.
DROP is used to delete the whole table, including its structure. DROP is a DDL command that lost the data permanently, and it cannot be rolled back.
TRUNCATE is used to delete the whole records, but it preserves the table's schema or structure. TRUNCATE is a DDL command, so it cannot be rolled back.
Hence, option A is the correct answer.
42) Which of the following options are correct regarding these three keys (Primary Key, Super Key, and Candidate Key) in a database?
I. Minimal super key is a candidate key
Explanation: Candidate key in SQL is a set of fields that identify each record in a table uniquely. It is a super key with no repeated fields that means the minimal super key is a candidate key. A table can contain multiple candidate keys, but it can have only a single primary key. Therefore option A is the correct choice.
43) When the wildcard in a WHERE clause is useful?
Explanation: The wildcard is a character used to search complex data from the database quickly. We can use it in conjunction with the LIKE or NOT LIKE comparison operators and the WHERE clause to find the result for a specified pattern. Hence, the wildcard is very useful when the exact match is not possible in the SELECT statement.
44) ______ is NOT a type of constraint in SQL language?
Explanation: ALTERNATE KEY is a key associated with one or more columns to identify each row in a table uniquely. If a table with one or more candidate keys, then the keys other than the primary key is an alternate key. Since the alternate key is a part of the candidate key, it is not a constraint in SQL. Hence, option D is the correct choice.
45) Find the cities name with the condition and temperature from table 'whether' where condition = sunny or cloudy but temperature >= 60.
Explanation: We know that the AND operator gives the output only when both the first conditions are true. In contrast, the OR operator gives the output when either the first condition OR the second condition is true. Hence the option C is the correct choice.
46) Which of the following statement is correct to display all the cities with the condition, temperature, and humidity whose humidity is in the range of 60 to 75 from the 'whether' table?
The BETWEEN is a conditional operator that is used to retrieve values from an expression within a range. It can be used with the SELECT, INSERT, UPDATE and DELETE statement.
The IN is a conditional operator used to reduce the use of multiple OR conditions in the SELECT, INSERT, UPDATE and DELETE statement.
Hence the option B is the correct choice.
47) ________ is a program that performs some common action on database data and also stored in the database.
Explanation: A stored procedure is a precompiled set of SQL commands that we can save in our database. It can be reused over and over again whenever we need to perform some common tasks.
A trigger is also the set of SQL commands that reside in a system catalog, but it will be invoked automatically in response to an event. It is a special kind of stored procedure.
A stored function is one of the types of stored programs used to encapsulate the common business rules or formulas reusable in stored programs. It returns a single value or sometimes does not produce any result.
Hence the option A is the correct choice.
48) Which statement is used to get all data from the student table whose name starts with p?
Explanation: The '%' symbol indicates zero or more characters next to where it will be used. And the _ symbol is used to match exactly one character. Therefore option B is the correct choice.
49) What is the advantage of the clustered index?
Explanation: A clustered index is actually a table where the data is stored in rows. It stores data in only one way based on the key values. It helps us to store data and indexes simultaneously but takes a long time to update the records. They are scan and index seek that minimizes the page transfer and maximizes the cache hits. Hence option C is the correct choice.
50) Evaluate the SQL statement:
Which of the following statement is correct?
Explanation: Option D is the correct choice because it is the example of an inline view, which is the subquery in the FROM clause of the main query.
51) Which of the following are the DATETIME data types that can be used in column definitions?
Answer: A, C
Options A and C are correct. It is because they are the DATETIME data types that can be used to specify column definitions.
Option B cannot be used to specify the column definitions because there are only INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data types.
Option D cannot be used to specify the column definitions because there are only TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types.
52) Which data dictionary table can be used to show the object privileges granted to the user on specific columns?
Explanation: The USER_COL_PRIVS data dictionary table will display the object privileges granted to the user on specific columns. The USER_TAB_PRIVS data dictionary table will display the object privileges granted to the user on the tables. Options A and B are not data dictionary.
53) Evaluate the SQL statement:
What will be displayed?
Explanation: This statement will give the result 0. A function MOD(1600, 10) returns 0 by calculating the modulus of 1600 when 1600 is divided by 10 until no further whole number can be produced. TRUNCATE(x, y) function truncates x to the decimal precision of y. Finally, the ROUND(x, y) function rounds x to the decimal precision of y. Hence option A is the correct choice.
54) What is the need for our query to execute successfully on an existing view?
Explanation: It is required to have a SELECT privilege to query on the existing view. Hence option B is the right choice.
55) Which of the following operator can be used with a multiple-row subquery?
Explanation: The multiple-row subqueries produces multiple rows of results. We can only use the NOT IN operator in SQL can only be used with multiple-row subqueries. And all other operators must be used with single row subquery only. Therefore, BETWEEN, '=', and '<>' operators work with single row subquery. Hence option C is the correct choice.
56) _______ is a constraint that can be defined only at the column level?
Explanation: The SQL NOT NULL constraint prevents inserting NULL values into the specified column. It enforces that a value must be defined for this column cannot accept a NULL value. Hence, we can say that the NOT NULL constraint can be defined only at the column level.