SQL Subquery And Transactions MCQ
Subquery And Transactions MCQs : This section focuses on the "Subquery And Transactions" of the SQL. These Multiple Choice Questions (mcq) should be practiced to improve the SQL skills required for various interviews (campus interview, walk-in interview, company interview), placement, entrance exam and other competitive examinations.
1. Which of the following statement is FALSE about transaction?
A. Once the data is committed it can be rolled back
B. All operations within the transaction must all succeed or fail
C. Transaction includes at least one statement
D. Transaction changes the database from one consistent state to another
View Answer
Ans : A
Explanation: Once the data is committed it can be rolled back statement is FALSE about transaction.
2. Which of the following statements are TRUE regarding subqueries?
A. A subquery can retrieve zero or more rows
B. A subquery can appear on either side of a comparison operator
C. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement
D. Both A and B
View Answer
Ans : D
Explanation: Both A and B statements are TRUE regarding subqueries.
3. Which of the following statements are False regarding subqueries?
A. Only two subqueries can be placed at one level
B. A subquery can appear on either side of a comparison operator
C. Both A and B
D. None of the above
View Answer
Ans : A
Explanation: Only two subqueries can be placed at one level statements are False regarding subqueries.
4. Which of the following statement(s) is TRUE regarding subqueries?
A. Inner queries in WHERE clause can contain ORDER BY
B. Outer query and inner query can get data from different tables
C. Outer query and inner query must get data from the same table
D. Inner queries cannot contain GROUP BY clause
View Answer
Ans : B
Explanation: Outer query and inner query can get data from different tables statement(s) is TRUE regarding subqueries.
5. Which of the following statements does not change the state of the database?
A. Where
B. Select
C. Insert
D. Drop
View Answer
Ans : B
Explanation: Select statements does not change the state of the database.
6. Which ACID property specifies that once the transaction is committed, its permanent even in case of power failures?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
View Answer
Ans : D
Explanation: Durability property specifies that once the transaction is committed, its permanent even in case of power failures
7. What is TRUE regarding Independent Subquery?
A. Equality operator can only be used if subquery returns only one row.
B. IN operator can be used if subquery returns multiple rows.
C. Both A and B
D. None of the above
View Answer
Ans : C
Explanation: Both A and B TRUE regarding Independent Subquery
8. Where subqueries can not be used?
A. Field names in the SELECT statement
B. The WHERE clause only in the SELECT statement
C. The WHERE clause in SELECT as well as all DML statements
D. The FROM clause in the SELECT statement
View Answer
Ans : B
Explanation: The WHERE clause only in the SELECT statement.
9. Independent sub query is the one where
A. the inner and the outer query are from two different tables
B. the inner query has no reference to the outer query
C. the inner and outer queries are for the same table but with different condition
D. None of the above
View Answer
Ans : B
Explanation: Independent sub query is the one where the inner query has no reference to the outer query.
10. Select * from student join takes using (ID); The above query is equivalent to ____________
A. Select * from student inner join takes using (ID);
B. Select * from student outer join takes using (ID);
C. Select * from student left outer join takes using (ID);
D. All of the mentioned
View Answer
Ans : A
Explanation: Join can be replaced by inner join.
11. What is a subquery?
A. A subquery is a select-from-where expression that is nested within another query
B. A subquery is any query that is nested within another query
C. A subquery is a relation that is externally specified which can be used to handle data in queries
D. A subquery is a condition that excludes all the invalid tuples from the database
View Answer
Ans : A
Explanation: A subquery is a select-from-where expression that is nested within another query. Common uses for sub-queries are to perform tests for set membership, make set comparisons etc.
12. The ______ construct returns true if a given tuple is present in the subquery.
A. not exists
B. present
C. not present
D. exists
View Answer
Ans : D
Explanation: The exists construct returns true if a given tuple is present in the subquery. The not exists construct gives true if a given tuple is not present in the subquery.
13. What is a correlated sub-query?
A. An independent query that uses the correlation name of another independent query.
B. A sub-query that uses the correlation name of an outer query
C. A sub-query that substitutes the names of the outer query
D. A sub-query that does not depend on its outer query's correlation names
View Answer
Ans : B
Explanation: A correlated sub-query is the one that uses the correlation name of an outer query.
14. SQL subqueries that can occur wherever a value is permitted provided the subquery gives only one tuple with a single attribute are called _________
A. Exact Subqueries
B. Vector Subqueries
C. Positive Subqueries
D. Scalar Subqueries
View Answer
Ans : D
Explanation: SQL subqueries that can occur wherever a value is permitted provided the subquery gives only one tuple with a single attribute are called Scalar subqueries. Scalar Subqueries can be used in the SQL update statement when they are used under the set clause.
15. Which of the following systems is responsible for ensuring isolation?
A. Recovery system
B. Atomic system
C. Concurrency control system
D. Compiler system
View Answer
Ans : C
Explanation: The concurrency control system is responsible for ensuring isolation in a database system.
16. A transaction that has not been completed successfully is called as _______
A. Compensating transaction
B. Aborted transaction
C. Active transaction
D. Partially committed transaction
View Answer
Ans : B
Explanation: Aborted transaction is a state after the transaction has been rolled back and the database has been restored to the state prior to the transaction.
17. Which of the following is not a transaction state?
A. Active
B. Partially committed
C. Failed
D. Compensated
View Answer
Ans : D
Explanation: Compensated is not a transaction state. But active, partially committed and failed are different states of a transaction.
18. The scheme that controls the interaction between executing transactions is called as _____
A. Concurrency control scheme
B. Multiprogramming scheme
C. Serialization scheme
D. Schedule scheme
View Answer
Ans : A
Explanation: The scheme that controls the interaction between executing transactions is called as concurrency control scheme.
19. Which of the following operators cannot be used in a sub-query?
A. AND
B. <
C. >
D. <>
View Answer
Ans : A
Explanation: Single-row operators include =, >, <, >=, <=, and <>. Multi-row operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.
20. Consider two statements about outer and inner queries in context of SQL sub-queries?
i. The inner queries can get data from only one table
ii. The inner queries can get data from more than one table Which of the above statements are true?
A. Only i
B. Only ii
C. Both i and ii
D. None of the above
View Answer
Ans : B
Explanation: Sub-queries can fetch data from more than one table.
Also check :
Discussion