Transact-SQL is Microsoft’s and Sybase’s proprietary extension to the SQL used to interact with relational databases.
Transact-SQL (T-SQL) MCQs: This section contains multiple-choice questions and answers on the various topics of Transact-SQL (T-SQL). Practice these MCQs to test and enhance your skills on Transact-SQL (T-SQL).
List of Transact-SQL (T-SQL) MCQs
1. What is the full form of T-SQL?
- Transact Structured Query Language
- Transaction Structure Query Language
- Transcript Structure Query Language
Answer: A) Transact Structured Query Language
Explanation:
T-SQL stands for Transact Structured Query Language.
2. Which company owns T-SQL?
- IBM
- Microsoft
- Oracle
- SAP
Answer: B) Microsoft
Explanation:
T-SQL is owned by Microsoft.
3. What is T-SQL?
- T-SQL is a language that is commonly used as an object mapper.
- T-SQL is a language that is greatly used in the analysis and search engine sector.
- T-SQL is a language that is the extension of normal extension.
Answer: B) T-SQL is a language that is the extension of normal extension.
Explanation:
T-SQL is a language that is the extension of normal extension.
4. Is T-SQL a nonprocedural language?
- Yes
- No
Answer: B) NO
Explanation:
No, T-SQL is a procedural language.
5. How many types of functions are there in T-SQL?
- 2
- 3
- 4
- 5
Answer: C) 4
Explanation:
There are four types of functions in T-SQL: Aggregate functions, Ranking functions, Row set functions, Scalar functions
6. How many data types and categories are available in T-SQL?
- 8
- 9
- 4
- 7
Answer: D) 7
Explanation:
There are seven categories of data types in T-SQL: Exact Numeric Types, Numeric Type, Date and Time types, Unicode Character String, Binary Strings, and Character Strings.
7. Which of the following commands is used if you want to see the column definition of a particular table?
- Show _column table name
- Column table name
- Exec sp_column table name
- Sp_column table name
Answer: C) Exec sp_column table name
Explanation:
Exec sp_column table name is the command we use when we want to see the columns definition of a particular table.
8. Which of the following commands is used if you want to delete the table?
- DROP TABLE
- DROP TABLE table_name
- DELETE TABLE
- DELETE TABLE table_name
Answer: B) DROP TABLE table_name
Explanation:
DROP TABLE table_name is the command we use if you want to delete the table.
9. Which of the following commands is used if you want to delete the row from a table?
- DROP FROM table_name where condition
- DROP FROM TABLE table_name
- DELETE FROM TABLE table_name
- DELETE FROM table_name where condition
Answer: D) DELETE FROM table_name where condition
Explanation:
DELETE FROM table_name where condition command is used if you want to delete the row from a table.
10. What will be the output of the below LIKE query?
WHERE SALARY LIKE ‘9000%’
- It will find any value that starts with 9000
- It will find any value that ends with 9000
- It will find any value whose range is between 9000
- It will find the value where salary is less than 9000
Answer: A) It will find any value that starts with 9000
Explanation:
It will find any value that starts with 9000.
11. What will be the output of the below LIKE query?
WHERE SALARY LIKE ‘%9000%’
- It will find any value that starts with 9000
- It will find any value that ends with 9000
- It will find any value that has 9000 at any position.
- It will find the value where salary is less than 9000
Answer: C) It will find any value that has 9000 at any position.
Explanation:
It will find any value that has 9000 at any position.
12. What will be the output of the below LIKE query?
WHERE SALARY LIKE ‘8_%_%.’
- It will find any value that starts with 8 and has more than 3 characters in length
- It will find any value that starts with 8 and has only 2 characters in length
- It will find any value that starts with 8 and have at least 1 character in length
- It will find any value that starts with 8 and has at least 3 characters in length.
Answer: D) It will find any value that starts with 8 and has at least 3 characters in length.
Explanation:
It finds any value that starts with 8 and has at least 3 characters in length.
13. What will be the output of the below LIKE query?
WHERE SALARY LIKE ‘_10%8’
- It will find any value that has a 10 at the starting position and ends with 8.
- It will find any value that has a 10 in the second position and ends with 8.
- It will find any value that has a 10 in the third position and also has 8.
- It will find any value that has a 10 in the second position and starts with 8.
Answer: B) It will find any value that has a 10 in the second position and ends with 8.
Explanation:
It will find any value that has a 10 in the second position and ends with 8.
14. In T-SQL if we use order-by clause then by default in which order the value is sorted?
- Ascending order
- Descending order
Answer: A) Ascending order
Explanation:
ORDER BY sorts the data in ascending order by default.
15. In T-SQL which keyword is used to sort the data in descending order?
- DES
- DEC
- DESC
- DESCORDER
Answer: C) DESC
Explanation:
DESC is the keyword used in T-SQL to sort the data in descending order.
16. In T-SQL which keyword is used to sort the data in ascending order?
- AES
- AEC
- AESC
- ASC
Answer: D) ASC
Explanation:
ASC is the keyword used in T-SQL to sort the data in ascending order.
17. In T-SQL Pivot and Unpivot are the ____.
- Arithmetic Operators.
- Relational Operators.
- Logical Operators.
Answer: B) Relational Operators.
Explanation:
In T-SQL Pivot and Unpivot are the relational operators.
18. Among PIVOT operators and UNPIVOT operators, which type of operator in T-SQL converts the row data into column data?
- PIVOT Operators.
- UNPIVOT Operators.
Answer: A) PIVOT Operators.
Explanation:
PIVOT operator is the type of operator in T-SQL which converts the row data into column data.
19. Among PIVOT operators and UNPIVOT operators, which type of operator in T-SQL converts the column-based data into row-based data and vice versa?
- PIVOT Operators.
- UNPIVOT Operators.
Answer: B) UNPIVOT Operators.
Explanation:
UNPIVOT operators are the type of operator in T-SQL which Converts column-based data into row-based data and vice versa.
20. Which of the following keywords in T-SQL eliminate duplicate records?
- UNIQUE
- DISTINCT
- DISCRETE
Answer: B) DISTINCT
Explanation:
DISTINCT keyword in T-SQL is used to eliminate duplicate records from the table.
21. Which of the following is the correct syntax to use the DISTINCT keyword?
- SELECT DISTINCT column N FROM table_name WHERE [condition]
- SELECT column N FROM table_name WHERE [condition]=DISTINCT
- SELECT column N DISTINCT FROM table_name WHERE [condition]
Answer: A) SELECT DISTINCT column N FROM table_name WHERE [condition]
Explanation:
SELECT DISTINCT column N FROM table_name WHERE [condition], is the correct syntax to use DISTINCT keyword in T-SQL.
22. Which of the following joins returns rows when there is a match in the tables?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
- CARTESIAN JOIN
Answer: A) INNER JOIN
Explanation:
INNER JOIN is a type of join that returns the rows as a result when there is a match in the tables.
23. Which of the following types of joins selects all the rows from the right table if there are no matches found in the left table?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
- CARTESIAN JOIN
Answer: C) RIGHT JOIN
Explanation:
RIGHT JOIN is a type of join which selects all the rows from the right table if there are no matches found in the left table.
24. Which of the following types of joins involves joining every row from one table to every row from another table?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
- CARTESIAN JOIN
Answer: F) CARTESIAN JOIN
Explanation:
CARTESIAN JOIN involves joining every row from one table to every row from another table.
25. Which of the following types of joins is an intersection of two copies of the same table?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
- CARTESIAN JOIN
Answer: E) SELF JOIN
Explanation:
SELF JOIN is an intersection of two copies of the same table.
26. In Transact SQL, ____are used to save time by avoiding writing the code repeatedly.
- Query
- Objects
- Stored procedures
- Processes
Answer: C) Stored procedures
Explanation:
In Transact SQL, stored procedures are used to save time by avoiding writing the code repeatedly.
27. How many types of parameters are there in the stored procedure?
- 3
- 2
- 4
- 5
Answer: A) 3
Explanation:
There are three types of parameters in the stored procedure: IN, OUT, IN OUT.
28. In the declaration_section of the procedure is where we declare ____?
- Global variable
- Local variable
Answer: B) Local variable
Explanation:
In the declaration_section of the procedure is where we declare the LOCAL variable.
29. In which section of the procedure, do we enter the code for the procedure?
- Executable section
- Declaration section
Answer: A) Executable section
Explanation:
In the executable section of the procedure, we enter the code for the procedure.
30. Is enclosing the query with parenthesis important while writing the subquery inside a query?
- Yes
- No
Answer: B) NO
Explanation:
While writing a subquery inside a query then a subquery must be enclosed in parenthesis.
31. In contrast to the database, the ____ represents a unit of work.
- Pipeline
- Stored procedure
- Transactions
Answer: C) Transactions
Explanation:
In contrast to the database, the Transaction represents a unit of work.
32. How many properties of transactions are there?
- 2
- 3
- 5
- 4
Answer: D) 4
Explanation:
The transaction has four properties, which are referred to as ACID properties-
Atomicity, consistency, Isolation, Durability.
33. In which of the following ACID properties, successful completion of the work unit’s operations is ensured and also focuses on the principle where either all operations are successful or none?
- Atomicity
- Consistency
- Isolation
- Durability.
Answer: A) Atomicity
Explanation:
In the atomicity property, successful completion of the work unit’s operations is ensured and it also focuses on the principle where either all operations are successful or none.
34. Which of the following ACID properties ensures that every transaction is unique and every operation works transparently?
- Atomicity
- Consistency
- Isolation
- Durability.
Answer: C) Isolation
Explanation:
The isolation property ensures that every transaction is unique and every operation works transparently.
35. Which of the following ACID properties states that upon committing a transaction, the database will change state properly?
- Atomicity
- Consistency
- Isolation
- Durability.
Answer: B) Consistency
Explanation:
The consistency property states that upon committing a transaction, the database will change state properly.
36. Which of the following ACID properties ensures that committed transactions will be properly executed if the system fails?
- Atomicity
- Consistency
- Isolation
- Durability.
Answer: D) Durability
Explanation:
The durability property ensures that committed transactions will be properly executed if the system fails.
37. Which of the following commands is used to save the changes in the transaction?
- COMMIT
- ROLLBACK
- SAVEPOINT
- Set Transport
Answer: A) COMMIT
Explanation:
Commit command in the transaction is used to save the changes in the transaction.
38. Which of the following commands is used to return a name on the transaction?
- COMMIT
- ROLLBACK
- SAVEPOINT
- Set Transport
Answer: D) Set Transport
Explanation:
Set transport command is used to return a name on the transaction.
39. Which of the following commands is used to retrieve the changes?
- COMMIT
- ROLLBACK
- SAVEPOINT
- Set Transport
Answer: B) ROLLBACK
Explanation:
The ROLLBACK command is used to retrieve the changes in the transaction.
40. Which of the following commands helps you to generate the collection of transactions with the help of the ROLLBACK command?
- COMMIT
- ROLLBACK
- SAVEPOINT
- Set Transport
Answer: C) SAVEPOINT
Explanation:
Savepoint command helps you to generate the collection of transactions with the help of the ROLLBACK command.
41. Can you use transactional commands with DDL commands?
- Yes
- No
Answer: B) NO
Explanation:
DDL commands are commonly known as data definition commands which include commands like create, drop or alter, and transactions commands cannot be used with these types of commands but can be used with the help of DML commands i.e. data manipulation language.
42. Which of the following keywords is used to initiate the transaction?
- BEGIN
- START
- INITIATE
- ACTIVATE
Answer: A) BEGIN
Explanation:
BEGIN keyword is used before every transaction.
43. Which of the following commands is known as Transactional Command?
- COMMIT
- ROLLBACK
- SAVEPOINT
- Set Transport
Answer: A) COMMIT
Explanation:
COMMIT command is known as Transactional Command.
44. Which of the following is the correct syntax to create an index?
- CREATE INDEX index_name ON table_name
- CREATE index_name ON table_name
- CREATE INDEX table_name
- Create INDEX on table name/ index name
Answer: A) CREATE INDEX index_name ON table_name
Explanation:
CREATE INDEX index_name ON table_name, is the correct syntax to create an index.
45. How many types of indexes does SQL provide?
- 3
- 4
- 2
- 5
Answer: C) 2
Explanation:
Two types of indexing are there in SQL: Clustered and Non-clustered.
46. Which type of indexing creates a physical sorting order of rows?
- Clustered
- Non-clustered.
Answer: A) Clustered
Explanation:
Clustered type of indexing creates a physical sorting order of rows.
47. Which type of index is kept in one place and table data is stored in another place?
- Clustered
- Non-clustered.
Answer: B) Non-clustered.
Explanation:
Non-Clustered type of index is kept in one place and table data is stored in another place.
48. Do indexes increase the database performance?
- Yes
- No
Answer: A) yes
Explanation:
In general, indexes help us to increase database performance.
49. Will it be efficient to use indexing on small tables?
- Yes
- No
Answer: B) NO
Explanation:
It is suggested to use indexing only for long tables.
50. Which of the following functions is used to generate the square root of the number?
- SQT
- SQRT
- SQUT
- SQR
Answer: B) SQRT
Explanation:
SQRT function is used to generate the square root of the number.
51. Which of the following functions is used to generate the random number?
- RANDOM
- RANDOM_NUMBER
- RAND
- RANDN
Answer: C) RAND
Explanation:
RAND function is used to generate the random number.
52. Select LEFT(‘HELLO’, 3)
What will be the output of the above code?
- HELL
- ELL
- OLL
- LLO
Answer: A) HELL
Explanation:
LEFT function returns us the left part of the string, according to the specified number of characters, so the output would be HELL
53. Select RIGHT(‘HELLO’, 3)
What will be the output of the above code?
- HELL
- ELL
- OLL
- LLO
Answer: D) LLO
Explanation:
RIGHT function returns us the RIGHT part of the string, according to the specified number of characters, so the output would be LLO.
54. Which of the following functions eliminate the leading blanks?
- TRIM
- LTRIM
- ETRIM
- RTRIM
Answer: B) LTRIM
Explanation:
LTRIM function removes the leading blanks from the given string.
55. Which of the following functions eliminate the blanks which are there after the given string?
- TRIM
- LTRIM
- ETRIM
- RTRIM
Answer: D) RTRIM
Explanation:
RTRIM function eliminates the blanks which are there after the given string.
56. Which of the following functions will help you to repeat the string multiple times?
- REPEAT
- REPLACE
- REPLICATE
- DUPLICATE
Answer: C) REPLICATE
Explanation:
The REPLICATE function will help you to repeat the string multiple times.
57. What is the full form of PL/SQL?
- Process language extension SQL
- Procedural language extension SQL
- Practical language extension SQL
- Progression language extension SQL
Answer: B) Procedural language extension SQL
Explanation:
PL/SQL stands for Procedural language extension SQL.
58. PL/SQL is used to ____?
- Create applications
- Manipulate the data
Answer: A) Create applications
Explanation:
PL/SQL is used to create applications.
59. Which company owns PL/SQL?
- IBM
- Microsoft
- Oracle
- SAP
Answer: C) Oracle
Explanation:
PL/SQL is owned by Oracle.
60. Does PL/SQL use the concept of OOPS?
- Yes
- No
Answer: A) YES
Explanation:
PL/SQL uses the OPPs concepts like data encapsulation, information hiding, and function overloading.