SQL Multiple Choice Questions
SQL 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?
Structured Query List
Structure Query Language
Sample Query Language
None of these.
Answer: B
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?
FLOAT
NUMERIC
DECIMAL
CHARACTER
Answer: C
Explanation:
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?
TRUNCATE
ALTER
CREATE
UPDATE
Answer: D
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?
COMMIT and ROLLBACK
UPDATE and TRUNCATE
SELECT and INSERT
GRANT and REVOKE
Answer: A
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?
DELETE
REMOVE
DROP
TRUNCATE
Answer: D
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
Simple tables
Virtual tables
Complex tables
Actual Tables
Answer: B
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?
Only 1
Only 2
Depends on no of Columns
Depends on DBA
Answer: A
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?
CHAR
RAW
NUMERIC
VARCHAR
Answer: B
Explanation:
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?
Primary Key
Not Null
Check
Union
Answer: D
Explanation:
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?
COUNT
COMPUTE
SUM
MAX
Answer: B
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?
SELECT
PROJECT
JOIN
PRODUCT
Answer: C
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?
ANY
BETWEEN
ALL
IN
Answer: D
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
NOT Operator
Exists Operator
IS NULL Operator
None of the above
Answer: C
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?
When existing rows are modified.
When a table is deleted.
When some rows are deleted.
All of the above
Answer: B
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
DESC
ASC
There is no default value
None of the mentioned
Answer: B
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?
TRUNCATE free the table space while DELETE does not.
Both TRUNCATE and DELETE statements free the table's space.
Both TRUNCATE and DELETE statement does not free the table's space.
DELETE free the table space while TRUNCATE does not.
Answer: A
Explanation:
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')?
6
7
POINT
JAVAT
Answer: B
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:
INSERT
MODIFY
LOOK-UP
All of the above
Answer: B
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?
SELF JOIN
EQUI JOIN
NON-EQUI JOIN
None of the above
Answer: B
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?
Similar to the WHERE clause but is used for columns rather than groups.
Similar to WHERE clause but is used for rows rather than columns.
Similar to WHERE clause but is used for groups rather than rows.
Acts exactly like a WHERE clause.
Answer: C
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.
WITH
FROM
WHERE
SELECT
Answer: A
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:
SELECT ROUND (65.726, -1) FROM DUAL; SELECT ROUND (65.726, -1) FROM DUAL;
Prints:
is illegal
garbage
726
70
Answer: D
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?
The AS clause in SQL is used to change the column name in the output or assign a name to a derived column.
The SQL AS clause can only be used with the JOIN clause.
The AS clause in SQL is used to defines a search condition.
All of the mentioned
Answer: A
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?
ROLLBACK
COMMIT
TRUNCATE
DELETE
Answer: B
Explanation:
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?
UPDATE User SET LastName = 'Thomas' INTO LastName = 'Michel'
MODIFY Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
MODIFY Users SET LastName = 'Thomas' INTO LastName = 'Michel'
UPDATE Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
Answer: D
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?
CREATE
UPDATE
ALTER
SELECT
Answer: C
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?
Natural JOIN
Outer JOIN
EQUI JOIN
All of the above
Answer: B
Explanation:
OUTER JOIN is the only join that returned the unmatched rows in one or both tables. It can be classified into the following types:
LEFT JOIN that shows only the unmatched rows from the left table.
RIGHT JOIN that shows only the unmatched rows from the right table.
FULL OUTER JOIN that shows the unmatched rows from both tables.
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 ________?
A way to establish a loop in SQL.
A way to establish an IF-THEN-ELSE in SQL
A way to establish a data definition in SQL
All of the above.
Answer: B
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?
Both run automatically.
Both are stored in the database.
Both consist of procedural code.
Both have to be called to operate.
Answer: C
Explanation:
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?
They include procedural and SQL statements.
They work similarly to the functions.
It does not need unique names.
It cannot be created with SQL statements.
Answer: A
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?
Union JOIN
Natural JOIN
Subqueries
All of the above
Answer: D
Explanation:
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?
Index improves the speed of data retrieval operations on a table.
Indexes are special lookup tables that will be used by the database search engine.
Indexes are synonyms of a column in a table.
All of the above
Answer: A
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
View
Network
Unit
Transaction
Answer: D
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
Read operations
Write operations
A & B both
None of the above
Answer: A
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
Numeric value
Alphanumeric value
A & B both
None of the above
Answer: C
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:
39 digits
38 digits
40 digits
37 digits
Answer: B
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?
SELECT, GROUP BY, WHERE, HAVING
SELECT, WHERE, GROUP BY, HAVING
SELECT, HAVING, WHERE, GROUP BY
SELECT, WHERE, HAVING, GROUP BY
Answer: B
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?
Primary key can store null value, whereas a unique key cannot store null value.
We can have only one primary key in a table while we can have multiple unique keys
Primary key cannot be a date variable whereas unique key can be
None of these
Answer: B
Explanation:
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?
Row = [Tuple, Record]
Column = [Field, Attribute]
Row = [Tuple, Attribute]
Columns = [Field, Record]
1 and 2
3 and 4
Only 1
Only 2
Answer: A
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?
Equal
IN
IS
None of Above
Answer: C
Explanation:
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.
II. TRUNCATE and DROP operations can be rolled back but DELETE operations cannot be rolled back.
III. DELETE is an example of DML, but TRUNCATE and DROP are examples of DDL.
IV. All are an example of DDL.
I and III
II and III
II and IV
II and IV
Answer: A
Explanation:
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
II. Only one candidate key can be a primary key
III. All super keys can be a candidate key
IV. We cannot find a primary key from the candidate key
I and II
II and III
I and III
II and IV
Answer: A
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?
When an exact match is required in a SELECT statement.
When an exact match is not possible in a SELECT statement.
When an exact match is required in a CREATE statement.
When an exact match is not possible in a CREATE statement.
Answer: B
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?
FOREIGN KEY
PRIMARY KEY
UNIQUE
ALTERNATE KEY
Answer: D
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.
SELECT city, temperature, condition FROM weather WHERE condition = 'cloudy' AND condition = 'sunny' OR temperature >= 60
SELECT city, temperature, condition FROM weather WHERE condition = 'cloudy' OR condition = 'sunny' OR temperature >= 60
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny' OR condition = 'cloudy' AND temperature >= 60
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny' AND condition = 'cloudy' AND temperature >= 60
Answer: C
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?
SELECT * FROM weather WHERE humidity IN (60 to 75)
SELECT * FROM weather WHERE humidity BETWEEN 60 AND 75
SELECT * FROM weather WHERE humidity NOT IN (60 AND 75)
SELECT * FROM weather WHERE humidity NOT BETWEEN 60 AND 75
Answer: B
Explanation:
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.
Stored Procedure
Trigger
Stored Function
None of the above
Answer: A
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?
SELECT * FROM student WHERE name LIKE '%p%';
SELECT * FROM student WHERE name LIKE 'p%';
SELECT * FROM student WHERE name LIKE '_p%';
SELECT * FROM student WHERE name LIKE '%p';
Answer: B
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?
It is fast to update the records.
It does not need extra work for SQL queries.
It minimizes the page transfer and maximizes the cache hits.
None of the above is correct.
Answer: C
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:
SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a,
(SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b
WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal; SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
Which of the following statement is correct?
The statement gives an error at line 1.
The statement gives an error at line 6.
The statement produces the employee name, salary, department ID, and maximum salary earned in the employee department for all departments that pay less salary than the maximum salary paid in the company.
The statement produces the employee name, salary, department ID, and maximum salary earned in the employee department for all employees who earn less than the maximum salary in their department.
Answer: D
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?
TIMESTAMP
INTERVAL MONTH TO DAY
INTERVAL YEAR TO MONTH
TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C
Explanation:
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?
USER_TAB_PRIVS_MADE
USER_COL_PRIVS_MADE
USER_TAB_PRIVS
USER_COL_PRIVS
Answer: D
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:
SELECT ROUND (TRUNCATE (MOD (1600, 10), -1), 2) FROM dual; SELECT ROUND (TRUNCATE (MOD (1600, 10), -1), 2) FROM dual;
What will be displayed?
0
1
00
An error statement
Answer: A
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?
The specified table must contain data.
We must have a SELECT privilege on the view.
We should have a SELECT privilege only on the specified table.
The specified table must be in the same database or schema.
Answer: B
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?
=
BETWEEN
NOT IN
<>
Answer: C
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?
UNIQUE
NOT NULL
CHECK
PRIMARY KEY
Answer: B
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.
Comments
Post a Comment