> > Transact-SQL (T-SQL) MCQs > Basic Transact-SQL (T-SQL) MCQs

Basic Transact-SQL (T-SQL) MCQ

1. Which is the best approach to update the last name and email address of a student with ID 56295?

UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id='56295';

UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id=56295;

UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id=56295;

UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id='56295';

Answer

Correct Answer: UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id=56295;

Note: This Question is unanswered, help us to find answer for this one

2. Though not currently a requirement, what will a future release of SQL Server require of all SQL statements?Though not currently a requirement, what will a future release of SQL Server require of all SQL statements?

All statements must end with a semicolon.

All statements must operate on a table of data.

All statements must always be written in uppercase letters.

All statements must include more than one variable.

Answer

Correct Answer: All statements must end with a semicolon.

Note: This Question is unanswered, help us to find answer for this one

3. You need to create a simple database backup in the server's Z:\Backups directory. Which query should you use?

BACKUP MyDatabase TO LOCATION = 'Z:\Backups\MyDatabase.bak';

CREATE BACKUP (DATABASE = 'MyDatabase' TO DISK = 'Z:\Backups\MyDatabase. bak');

BACKUP DATABASE MyDatabase ON 'Z:\Backups\MyDatabase.bak';

BACKUP DATABASE MyDatabase TO DISK = 'z:\Backups\MyDatabase.bak';

Answer

Correct Answer: BACKUP DATABASE MyDatabase TO DISK = 'z:\Backups\MyDatabase.bak';

Note: This Question is unanswered, help us to find answer for this one

4. Your database currently has a table called Inventory in the Warehouse schema. You need to move the table to the Products schema. Which query accomplishes this goal?

ALTER SCHEMA Products TRANSFER Warehouse.Inventory;

ALTER TABLE Warehouse.Inventory TRANSFER Products.Inventory;

ALTER TABLE Warehouse.Inventory ADD SCHEMA Products;

ALTER SCHEMA Warehouse ADD TABLE Inventory;

Answer

Correct Answer: ALTER SCHEMA Products TRANSFER Warehouse.Inventory;

Note: This Question is unanswered, help us to find answer for this one

5. To combine the results of two or more SELECT statements, removing duplicates, which keyword can you use?

DEDUPE

SELECT

MERGE

UNION

Answer

Correct Answer: UNION

Note: This Question is unanswered, help us to find answer for this one

6. What is the result of this query? SELECT 123+'123' AS Result;

Error

'123''123'

123123

246

Answer

Correct Answer: 246

Note: This Question is unanswered, help us to find answer for this one

7. You would like to have a record added to a TableB every time a record is modified in TableA. What technique should you look at implementing?

You should create a DML trigger on the server.

You should create a DDL trigger on the database.

You should create a DML trigger on TableA.

You should create a DML trigger on TableB.

Answer

Correct Answer: You should create a DML trigger on TableA.

Note: This Question is unanswered, help us to find answer for this one

8. What is the result of an INNER JOIN between table1 and table2?

Only records that have corresponding entries in table1 and table2 are displayed.

No records from table1 are ever displayed.

All records from table1 are displayed, regardless of whether the records have a corresponding row in table2

Only records that have no corresponding records in table1 or table2 are displayed.

Answer

Correct Answer: Only records that have corresponding entries in table1 and table2 are displayed.

Note: This Question is unanswered, help us to find answer for this one

9. Given a table with the following structure, which query returns all student names with the highest grade? CREATE TABLE Students (StudentName varchar(50),Grade int );

SELECT StudentName FROM Students WHERE Grade = MAX(Grade);

SELECT TOP(1) StudentName FROM Students ORDER BY Grade;

SELECT TOP(1) WITH TIES StudentName FROM Students ORDER BY Grade DESC;

SELECT StudentName, MAX(Grade) FROM Students ORDER BY Grade DESC;

Answer

Correct Answer: SELECT TOP(1) WITH TIES StudentName FROM Students ORDER BY Grade DESC;

Note: This Question is unanswered, help us to find answer for this one

10. What does a RIGHT JOIN ensure?

That only records from the rightmost table will be displayed

That no records from the rightmost table are displayed if the records dont have corresponding records in the left table

That records from the rightmost table will be displayed only if the records have a corresponding value in the leftmost table

That all records from the rightmost table are represented in the result, even if there are no corresponding records in the left table

Answer

Correct Answer: That all records from the rightmost table are represented in the result, even if there are no corresponding records in the left table

Note: This Question is unanswered, help us to find answer for this one

11. How many bytes of storage does the int data type consume?

1 byte

2 bytes

4 bytes

8 bytes

Answer

Correct Answer: 4 bytes

Note: This Question is unanswered, help us to find answer for this one

12. When no join type between multiple tables in a query's FROM clause is specified, what type of join is assumed?

INNER

RIGHT

LEFT

FULL

Answer

Correct Answer: INNER

Note: This Question is unanswered, help us to find answer for this one

13. You need to write a query that returns all products that have a SerialNumber ending with "10_3". Which WHERE clause should you use to fill in the blank in this query?SELECT ProductID, ProductName, SerialNumber FROM Products______ ;

WHERE SerialNumer LIKE '%10_3'

WHERE SerialNumer LIKE ('%10'+'_'+'3')

WHERE SerialNumer LIKE '%10

WHERE SerialNumer LIKE '%10[_]3'

Answer

Correct Answer: WHERE SerialNumer LIKE '%10_3'

Note: This Question is unanswered, help us to find answer for this one

14. The result of a CROSS JOIN between a table with 4 rows, and one with 5 rows, will give with _ rows.

1024

Answer

Correct Answer: 20

Note: This Question is unanswered, help us to find answer for this one

15. Which statement creates a new database schema named Sales and establish Sharon as the owner?

ALTER USER Sharon WITH DEFAULT_SCHEMA = Sales;

ALTER USER Sharon SET SCHEMA Sales;

CREATE SCHEMA Sales SET OWNER Sharon;

CREATE SCHEMA Sales AUTHORIZATION Sharon;

Answer

Correct Answer: CREATE SCHEMA Sales AUTHORIZATION Sharon;

Note: This Question is unanswered, help us to find answer for this one

16. To select a random student from the table, which statement could you use?

SELECT TOP(1) first_name, last_name FROM Students ORDER BY NEWID();

SELECT TOP(1) RAND(first_name, last_name) FROM Student;

SELECT TOP(1) first_name, last_name FROM Student;

SELECT TOP(1) first_name, last_name FROM RAND(Student);

Answer

Correct Answer: SELECT TOP(1) first_name, last_name FROM Students ORDER BY NEWID();

Note: This Question is unanswered, help us to find answer for this one

17. What is the result of this query? SELECT 'abc\ def' AS Result;

abc\def

abcdef

error

abc def

Answer

Correct Answer: abcdef

Note: This Question is unanswered, help us to find answer for this one

18. Which data type will most efficiently store a person's age in years?

Float

Int

Tinyint

Bigint

Answer

Correct Answer: Tinyint

Note: This Question is unanswered, help us to find answer for this one

19. What is the result of this query? SELECT 1 / 2 AS Result;

0.5

Error

Answer

Correct Answer: 0

Note: This Question is unanswered, help us to find answer for this one

20. You need to remove all data from a table name Products. Which query fully logs the removal of each record?

TRUNCATE FROM Products *;

DELETE FROM Products;

DELETE * FROM Products;

TRUNCATE TABLE Products;

Answer

Correct Answer: DELETE FROM Products;

Note: This Question is unanswered, help us to find answer for this one

21. Which of these data types is an approximate numeric?

Real

Bit

Decimal

Numeric

Answer

Correct Answer: Real

Note: This Question is unanswered, help us to find answer for this one

22. Which is the best approach to update the last name of the student Donette Figgins to Smith

UPDATE Students SET last_name = 'Smith' WHERE email = 'dfiggins@rouxacademy.com';

UPDATE Students SET last_name = 'Figgins' WHERE email = 'dfiggins@rouxacademy.com';

UPDATE Students SET last_name = 'Figgins' WHERE last_name = 'Smith' AND first-name = 'Donette';

UPDATE Students SET last_name = 'Smith' WHERE last_name = 'Figgins' AND first-name = 'Donette';

Answer

Correct Answer: UPDATE Students SET last_name = 'Smith' WHERE last_name = 'Figgins' AND first-name = 'Donette';

Note: This Question is unanswered, help us to find answer for this one

23. What is the result of this statement?

SELECT FLOOR(-1234.321)

-1234.3

-1234

-1235

1234.321

Answer

Correct Answer: -1235

Note: This Question is unanswered, help us to find answer for this one

24. Which query shows the first name, department, and team of all students with the two lowest points?

SELECT LIMIT(2) first_name, department, team FROM Students ORDER BY points ASC;

SELECT TOP(2) first_name, deprtment, team FROM Students ORDER BY points DESC;

SELECT TOP(2) WITH TIES first_name, department, team FROM Students ORDER BY points;

SELECT BOTTOM(2) first_name, department, team FROM Students ORDER BY points ASC;

Answer

Correct Answer: SELECT TOP(2) WITH TIES first_name, department, team FROM Students ORDER BY points;

Note: This Question is unanswered, help us to find answer for this one

25. You need to write a query that returns all Employees that have a LastName starting with the letter A. Which WHERE clause should you use to fill in the blank in this query?

WHERE LastName = A*

WHERE LastName = LIKE '%A%'

WHERE LastName LIKE 'A%'

WHERE LastName IN ('A*')

Answer

Correct Answer: WHERE LastName LIKE 'A%'

Note: This Question is unanswered, help us to find answer for this one

26. What is an example of a DDL command in SQL?

TRUNCATE TABLE

DELETE

MERGE

DROP

Answer

Correct Answer: DROP

Note: This Question is unanswered, help us to find answer for this one

27. The keywords AND, IN, LIKE, and between all belong to a category called what?

Joining operations

Linking operations

Criteria operations

Logical operations

Answer

Correct Answer: Logical operations

Note: This Question is unanswered, help us to find answer for this one

28. Which answer is NOT a type of table index?

Nonclustered

Unique

Heap

Hash

Answer

Correct Answer: Heap

Note: This Question is unanswered, help us to find answer for this one

Transact-SQL (T-SQL) MCQs | Topic-wise

MCQ on Basic Transact-SQL (T-SQL)

General Transact-SQL (T-SQL) MCQs