MS SQL Server - 28 - Table Expression MCQ

28 – Table Expression MCQ

Best of luck!

1 / 25

1. What is a Table Expression in SQL Server?

A virtual table that can be used within a query

A subquery

A stored procedure

A temporary table

2 / 25

2. Which SQL statement is used to create a derived table in SQL Server?

SELECT INTO

CREATE DERIVED TABLE

DECLARE TABLE

CREATE TABLE

3 / 25

3. In SQL Server, what is the primary purpose of a derived table?

To enforce data integrity

To create a temporary result set

To store data permanently

To improve query performance

4 / 25

4. Which of the following is true about Derived Tables in SQL Server?

They can be indexed.

They are always explicitly created.

They are physical tables stored on disk.

They are automatically dropped after use.

5 / 25

5. What is the scope of a Derived Table in SQL Server?

Available within the current session only

Available to all databases

Available to all sessions

Available to all users

6 / 25

6. Which keyword is used to define a Common Table Expression (CTE) in SQL Server?

CREATE

WITH

SELECT

7 / 25

7. In SQL Server, what is the primary benefit of using a CTE over a derived table?

CTEs can be used in more complex queries.

CTEs provide better query readability and maintainability.

CTEs are automatically dropped after use.

CTEs can be indexed.

8 / 25

8. Which of the following SQL statements is used to refer to a CTE within a query?

USING

WITH

SELECT

FROM

9 / 25

9. In SQL Server, what is a Recursive CTE used for?

To improve query performance

To create temporary tables

To solve problems that involve hierarchical or recursive data

To enforce data integrity constraints

10 / 25

10. What is a Self-referencing CTE in SQL Server?

A CTE that contains a subquery

A CTE that is defined using the UNION operator

A CTE that refers to itself within its definition

A CTE that cannot be referenced by other queries

11 / 25

11. In SQL Server, how is a CTE terminated?

With a semicolon (;)

With the BEGIN keyword

With a closing parenthesis

With the END keyword

12 / 25

12. Which SQL keyword is used to define the anchor member in a Recursive CTE in SQL Server?

RECURSIVE

START

UNION

ANCHOR

13 / 25

13. What is the result of a query that references a CTE before its definition in SQL Server?

An error is thrown.

The query is executed successfully.

The query runs, but the CTE is empty.

The CTE is automatically created.

14 / 25

14. In SQL Server, which of the following is a valid use case for a CTE?

Storing data permanently

Creating temporary tables

Simplifying complex queries

Enforcing primary key constraints

15 / 25

15. What is the maximum recursion level supported by a Recursive CTE in SQL Server by default?

100

32767

There is no maximum limit.

256

16 / 25

16. In SQL Server, what does the keyword “RECURSIVE” specify in a CTE?

The anchor member

The termination condition

The recursive member

The maximum recursion level

17 / 25

17. In a Recursive CTE in SQL Server, what is the anchor member?

The member that defines the termination condition

The member that references other tables

The member that contains the recursive logic

The first member that starts the recursion

18 / 25

18. In SQL Server, what is the main difference between a CTE and a derived table?

CTEs can be indexed, while derived tables cannot.

CTEs are always explicitly created, while derived tables are implicit.

CTEs cannot be referenced by other queries, while derived tables can.

CTEs are stored on disk, while derived tables are in-memory.

19 / 25

19. What is the primary purpose of a derived table in SQL Server?

To enforce data integrity

To improve query performance

To store data permanently

To create a temporary result set

20 / 25

20. In SQL Server, which clause is used to define the recursive part of a Recursive CTE?

RECURSIVE

RECURSION

UNION

ITERATE

21 / 25

21. In SQL Server, how is the result set of a CTE referenced within the main query?

Using the keyword RESULT

Using the CTE name

Using the SELECT statement

Using the FROM clause

22 / 25

22. What is the main advantage of using a Common Table Expression (CTE) over a subquery or derived table in SQL Server?

CTEs provide better query readability and maintainability.

CTEs can be indexed.

CTEs are automatically dropped after use.

CTEs are more memory-efficient.

23 / 25

23. In SQL Server, what is the result of a query that references a CTE multiple times within the same query?

Each reference to the CTE creates a new instance.

An error is thrown.

The CTE is automatically created.

The query runs, but the CTE is empty.

24 / 25

24. Which SQL keyword is used to declare a Common Table Expression (CTE) in SQL Server?

SELECT

WITH

CREATE

25 / 25

25. In SQL Server, what is the purpose of the recursive member in a Recursive CTE?

To perform calculations on columns

To define the termination condition

To start the recursion

To create a temporary table

Your score is

MS SQL Server – 28 – Table Expression MCQ