Table Expressions

Question 1

What is special about the table expressions stored as database objects?

Answer 1

1. They can be re-used
2. Access to the objects can be controlled with permissions

SQL Server 70-461 04-02a

Question 2

What are the four types of table expressions in T-SQL?

Answer 2

1. Derived Tables
2. Common Table Expressions (CTEs)
3. Views
4. Inline Table-Valued Functions

SQL Server 70-461 04-02a

Question 3

Of the four table expressions, which are only available in the statement that defines them and which are preserved as database objects?

Answer 3

Only available to statement that defines them
- Derived Tables
- Common Table Expressions (CTEs)

Preserved as database objects
- Views
- Inline Table-Valued Functions

SQL Server 70-461 04-02a

Question 4

If ORDER BY is used in a table expression to facilitate TOP or OFFSET-FETCH, what happens to the outer query order?

Answer 4

There is no guarantee of the order the outer query will have unless it has its own ORDER BY clause

SQL Server 70-461 04-02a

Question 5

Describe in words where you place a Derived Table in a query and what its structure is like.

Answer 5

1. Define the query that creates the Derived Table in parenthesis, like you would a subquery
2. Place it in the FROM clause of the outer query
3. Give the Derived Table a name, just like doing an alias, after the parenthesis.

SQL Server 70-461 04-02a

Question 6

What is the general structure of a Derived Table?

Answer 6

SELECT * 
FROM (
Derived Table
) AS Derived_Table_Name

This is a very simple example where the outer select query selects all records from the Derived Table. Often you will see the derived table joined to another table.

SQL Server 70-461 04-02a

Question 7

Can you refer to an alias that was created in a Derived Table from the WHERE clause of the outer query?

Answer 7

Yes. Since the Derived Table is in the FROM clause and the FROM clause is processed before the WHERE clause.

SQL Server 70-461 04-02a

Question 8

What are the two ways to alias columns in a Derived Table?

Answer 8

• Inline and External
• Inline is like Normal Example: column AS alias
• External comes after the Derived Tables name. You must list all columns in the Derived Table, regardless of whether you want an alias or not, separated by commas between parentheses after the Derived Table’s name.

SQL Server 70-461 04-02a

Question 9

What does the external form of column aliasing for Derived Tables look like?

Answer 9

FROM (DERIVED TABLE) AS D(col1, col2, col3)

SQL Server 70-461 04-02a

Question 10

What is a CTE?

Answer 10

One of the four types of table expressions. CTE stands for Common Table Expression.

SQL Server 70-461 04-02a

Question 11

How does the layout of a CTE differ from a derived table?

Answer 11

• The entire definition of the CTE happens from start to finish before the outer query executes.
• A Derived Table is in the middle of the outer query.

SQL Server 70-461 04-02a

Question 12

How is a CTE laid out?

Answer 12

It goes before main select statement

WITH <CTE_NAME> AS 
(
<inner_query>
)
<outer_query>

In the code above, this is the CTE:
WITH <CTE_NAME> AS
(
<inner\_query\>
)</CTE_NAME>

SQL Server 70-461 04-02a

Question 13

How long do CTEs last?

Answer 13

• As soon as the outer query runs that was below the CTEs, they are no longer available.
• They are only available to the statement that defined them, not all statements in the session. So from WITH to the outer query end.

SQL Server 70-461 04-02a

Question 14

What would the layout be if using more than one CTE?

Answer 14

Separate by commas

WITH <CTE_NAME> AS 
(
<inner_query>
), --First CTE
<CTE_NAME> AS 
(
<inner_query2>
) --Second CTE

<outer_query>

Just writing a bunch of extra words so the code will left justify :)

SQL Server 70-461 04-02a

Question 15

Example of the same Derived Table that is referred to multiple times.

Answer 15

FROM 
(
Derived Table
) AS D1
LEFT OUTER JOIN
(
same exact Derived Table as above, code fully written out 
) AS D2 --Give it a different name, such as D2

Same exact Derived Table used twice. Has to be fully written out each time.

SQL Server 70-461 04-02a

Question 16

How many queries does a recursive CTE have?

Answer 16

• At least two (more are possible)
• At least one query known as the anchor member
• At least one query known as the recursive member

SQL Server 70-461 04-02a

Question 17

How many times is an anchor member invoked in a recursive CTE?

Answer 17

Once

SQL Server 70-461 04-02a

Question 18

What does the reference to the recursive CTE name represent the first time the recursive member is invoked?

Answer 18

The result set that the anchor member returned.

SQL Server 70-461 04-02a

Question 19

What does the reference to the recursive CTE name represent each time the recursive member is invoked after the first time?

Answer 19

The result set returned by the previous invocation of the recursive member.

SQL Server 70-461 04-02a

Question 20

What are the different forms of results a subquery can return?

Answer 20

1. Scalar
2. Multi-Valued
3. Table-Valued

SQL Server 70-461 04-02a

Question 21

How does EXISTS work with a correlated subquery?

Answer 21

It accepts a subquery as an input and returns:
- true when the subquery returns at least one row
- false otherwise.

EXISTS only returns True or False. It doesn’t return results.

SQL Server 70-461 04-02a

Question 22

How many downsides are there to a Derived Table?

Answer 22

2

SQL Server 70-461 04-02a

Question 23

What is the general form of a Recursive CTE?

Answer 23

WITH <CTE_NAME> AS
(
<anchor_member>
UNION ALL
<recursive_member>
)
<outer_query_against_CTE>

Just writing words so the code will left justify

SQL Server 70-461 04-02a

Question 24

What is a correlated subquery?

Answer 24

• A subquery that has a reference to a column from the outer query.
• An example is a predicate in the subquery such as WHERE p2.categoryid=P1.categoryid

from subquery
p2.categoryid

from outer query
P1.categoryid

correlation
p2.categoryid=P1.categoryid

SQL Server 70-461 04-02a

Question 25

What must the anchor and recursive members have in common?

Answer 25

They must be compatible in terms of 1. number of columns returned 2. data types of corresponding columns ## Footnote SQL Server 70-461 04-02a

Question 26

What is a Derived Table?

Answer 26

- One of the types of table expressions - It closely resembles a subquery but returns an entire table result I believe this not to mean it returns from one table but that it can return multiple rows and columns ## Footnote SQL Server 70-461 04-02a

Question 27

What does an example of a Recursive CTE look like?

Answer 27

``` WITH EmpsCTE AS ( -- Anchor Member SELECT empid, mgrid, firstname, lastname FROM HR.Employees WHERE empid=2 UNION ALL --Recursive Member SELECT c.empid, c.mgrid, c.firstname, c.lastname FROM EmpsCTE AS P JOIN HR.Employees AS C ON C.mgrid=P.empid ) --Outer query against CTE SELECT empid, mgrid, firstname, lastname FROM EmpsCTE ``` ## Footnote SQL Server 70-461 04-02a

Question 28

How many times is the recursive member invoked?

Answer 28

Until it returns an empty set or a limit is exceeded ## Footnote SQL Server 70-461 04-02a

Question 29

What does the reference to the recursive CTE name in the outer query represent?

Answer 29

The final result set from the anchor member and all invocations of the recursive member. ## Footnote SQL Server 70-461 04-02a

Question 30

What is a table expression?

Answer 30

A named query that represents a valid relational table ## Footnote SQL Server 70-461 04-02a

Question 31

How does a CTE work compared to a Derived Table?

Answer 31

Very similarly in that they both return a table result ## Footnote SQL Server 70-461 04-02a

Question 32

What are the characteristics of a table expression?

Answer 32

1. It returns a relational result set 2. It is named 3. It is queried from an outer query 4. Because it has to be relational, it cannot have an ORDER BY clause unless TOP or OFFSET-FETCH are used. It's more of a filter specification when used with TOP and OFFSET-FETCH vs an order specification ## Footnote SQL Server 70-461 04-02a

Question 33

What is the first downside to a derived table and why does it occur?

Answer 33

- If you need to refer to one derived table from another, you have to nest the derived tables. This makes the code harder to read. - This occurs because a derived table is defined in the FROM clause of the outer query and not separately ## Footnote SQL Server 70-461 04-02a

Question 34

What is the second downside to a Derived Table?

Answer 34

- If you need to refer to multiple instances of the same Derived Table you can't do it by referring to the Derived Table name, you have to fully write out the code for the Derived Table every time. - This is because Derived Tables are defined in the FROM clause of the outer query. Since everything in the same logical query processing phase is evaluated at the same time conceptually, the Derived Table does not exist yet to other elements in the FROM clause. ## Footnote SQL Server 70-461 04-02a

Question 35

What are the main benefits of CTEs? Which ones are different from Derived Tables?

Answer 35

1. The entire CTE is defined from start to finish *before* the outer query. Much easier to read. 2. No need to nest multiple CTEs, just separate with commas. 3. The current CTE can refer to the one(s) above it, if there are multiple CTE's. 4. The outer query can refer to any of the CTEs 5. You can refer to multiple instances of the same CTE in the outer query ## Footnote SQL Server 70-461 04-02a

Question 36

What is an anchor member?

Answer 36

A query that returns a valid relational result table. ## Footnote SQL Server 70-461 04-02a

Question 37

What is a recursive member?

Answer 37

A query that has a reference to the recursive CTE name ## Footnote SQL Server 70-461 04-02a

Question 38

What is the main difference between a view and an Inline Table-Valued Function?

Answer 38

A view does not accept input parameters. An Inline Table-Valued Function does. ## Footnote SQL Server 70-461 04-02b to 04-03

Question 39

How do you reference an existing Inline Table-Valued Function from a query?

Answer 39

Use the name of the Inline Table-Valued Function and supply a variable. ``` SELECT orderid, custid FROM dbo.GetCustOrders(1) AS O ``` The Inline Table-Valued Function in the code above is **dbo.GetCustOrders(1)** ## Footnote SQL Server 70-461 04-02b to 04-03

Question 40

How is the general structure for OUTER APPLY statement different than CROSS APPLY?

Answer 40

It is exactly the same except you use the words OUTER APPLY instead of CROSS APPLY. ## Footnote SQL Server 70-461 04-02b to 04-03

Question 41

What is the difference between the result of T1 CROSS APPLY T2 AND T1 CROSS JOIN T2 when the right table has no correlation to the left?

Answer 41

None. With no correlation when using CROSS APPLY it acts just like CROSS JOIN. ## Footnote SQL Server 70-461 04-02b to 04-03

Question 42

What are the two types of APPLY operators?

Answer 42

1. CROSS APPLY 2. OUTER APPLY ## Footnote SQL Server 70-461 04-02b to 04-03

Question 43

What is the general structure of a Inline Table-Valued Function?

Answer 43

``` ; GO CREATE FUNCTION function_name ( @variable_name AS variable_data_type ) RETURNS TABLE AS RETURN ; GO ``` ## Footnote SQL Server 70-461 04-02b to 04-03

Question 44

An example of an Inline Table-Valued Function for reference.

Answer 44

``` --Drop Function If OBJECT_ID('dbo.GetCustOrders') IS NOT NULL DROP FUNCTION dbo.GetCustOrders; GO --Create Function Create FUNCTION dbo.GetCustOrders (@cid AS INT) RETURNS TABLE AS RETURN SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry FROM sales.orders WHERE custid=@cid GO ``` ## Footnote SQL Server 70-461 04-02b to 04-03

Question 45

How does CROSS APPLY work?

Answer 45

- The right table expression is applied to each row on the left. - If the right table expression returns on empty set for a left row then that left row is not included. ## Footnote SQL Server 70-461 04-02b to 04-03

Question 46

How does the APPLY operator work?

Answer 46

It operates on two tables. The right table is typically a Derived Table or Inline Table-Valued Function. The right table expression can have a reference to an element from the left. So conceptually, the right table expression is evaluated separately for each left row. ## Footnote SQL Server 70-461 04-02b to 04-03

Question 47

Diagram of a CROSS APPLY for reference.

Answer 47

## Footnote SQL Server 70-461 04-02b to 04-03

Question 48

General form of CROSS APPLY using a Derived Table for the right side.

Answer 48

``` SELECT FROM left_table_name CROSSAPPLY ( Derived Table with reference to a column in the left table such as WHERE ltable.id=rtable.id ) AS derived_table_name ``` ## Footnote SQL Server 70-461 04-02b to 04-03

Question 49

Example of a CROSS APPLY using a Derived Table

Answer 49

Returns the three most recent orders for each customer ``` SELECT c.custid, A.orderid, A.orderdate FROM sales.customers ASC CROSS APPLY ( SELECT TOP(3) orderid, empid, orderdate, requireddate FROM sales.orders AS O WHERE O.custid=C.custid ORDER BY orderdate DESC, orderid DESC ) AS A ``` In the code above: **A.orderid, A.orderdate** is from the Derived Table **C.custid** correlates to the left table ## Footnote SQL Server 70-461 04-02b to 04-03

Question 50

General form of CROSS APPLY using an Inline Table-Valued Function for the right side.

Answer 50

``` SELECT FROM left_table_name CROSS APPLY inline_table_valued_function AS alias_for_inline_table_value_function ``` ## Footnote SQL Server 70-461 04-02b to 04-03

Question 51

Example of a CROSS APPLY using an Inline Table-Valued Function for the right side.

Answer 51

``` SELECT C.custid, C.companyname, A.orderid, A.empid, A.orderdate, A.requireddate FROM sales.customers ASC CROSS APPLY dbo.TOP ORDERS(C.custid, 3) AS A ``` In the code above: - **A.orderid, A.empid, A.orderdate, A.requireddate** is from the Inline Table-Valued Function - **dbo.TOPORDERS(C.custid, 3)** is an Inline Table-Valued Function - **(C.custid, 3)** are variables that are supplied to the function ## Footnote SQL Server 70-461 04-02b to 04-03

Question 52

How does OUTER APPLY work?

Answer 52

- The same as CROSS APPLY except if the right table expression returns an empty set for a left row, the left row is still included and nulls are used as placeholders for the result columns of the right side. - Basically all rows on the left side are preserved. ## Footnote SQL Server 70-461 04-02b to 04-03

Question 53

Diagram of an OUTER APPLY for reference.

Answer 53

## Footnote SQL Server 70-461 04-02b to 04-03

Question 54

What is the difference between APPLY and JOIN operators?

Answer 54

- With a JOIN operator, both inputs represent static relations. - With APPLY, the left side is a static relation, but the ight side can be a table expression with correlations to elements from the left table. ## Footnote SQL Server 70-461 04-02b to 04-03

Interview Prep - SQL (26 decks)

• Foundations of Relational SQL
• Logical and Physical Query Processing
• Identifiers
• Alias
• SELECT
• FROM Clause
• JOINS
• Filtering Data
• Sorting Data
• Keys
• Data Types
• Literals
• Date and Time Functions
• Cast, Convert and Parse
• TOP and OFFSET-FETCH
• Subquery
• Table Expressions
• Views
• UNION, UNION ALL, EXCEPT, INTERSECT
• Grouped Queries
• Pivot and UnPivot
• Window Functions
• Tables and Schemas
• Data Integrity Enforcement, Constraints and Indexes
• Synonyms
• INSERT and SELECT INTO

• Key Links

  • Pricing
  • Corporate Training
  • Teachers & Schools
  • iOS App
  • Android App
  • Help Center

• Subjects

  • Medical & Nursing
  • Law Education
  • Foreign Languages
  • All Subjects A-Z
  • All Certified Classes

• Company

  • About Us
  • Earn Money!
  • Academy
  • Swag Shop
  • Contact
  • Terms
  • Podcasts
  • Careers

• Find Us

  • 
  • 
  • 
  • 
  • 
  • 
  • 
  

  

  

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions