Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Table Expressions > Flashcards

Table Expressions Flashcards

1
Q

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

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A
  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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the general structure of a Derived Table?

A 
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is a CTE?

A

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How is a CTE laid out?

A

It goes before main select statement
~~~
WITH <CTE_NAME> AS
(</CTE_NAME>

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

## Footnote

SQL Server 70-461 04-02a
</CTE_NAME></outer_query></inner_query>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How long do CTEs last?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

Separate by commas
~~~
WITH <CTE_NAME> AS
(</CTE_NAME>

<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 :)

## Footnote

SQL Server 70-461 04-02a
</outer_query></inner_query2></CTE_NAME></inner_query>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A 
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How many queries does a recursive CTE have?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

Once

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

The result set that the anchor member returned.

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

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

SQL Server 70-461 04-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How does EXISTS work with a correlated subquery?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

How many downsides are there to a Derived Table?

A

2

SQL Server 70-461 04-02a

23
Q

What is the general form of a Recursive CTE?

A 
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

24
Q

What is a correlated subquery?

A
  • 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

25
Q

What must the anchor and recursive members have in common?

A

They must be compatible in terms of
1. number of columns returned
2. data types of corresponding columns

SQL Server 70-461 04-02a

26
Q

What is a Derived Table?

A
  • 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

SQL Server 70-461 04-02a

27
Q

What does an example of a Recursive CTE look like?

A 
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

SQL Server 70-461 04-02a

28
Q

How many times is the recursive member invoked?

A

Until it returns an empty set or a limit is exceeded

SQL Server 70-461 04-02a

29
Q

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

A

The final result set from the anchor member and all invocations of the recursive member.

SQL Server 70-461 04-02a

30
Q

What is a table expression?

A

A named query that represents a valid relational table

SQL Server 70-461 04-02a

31
Q

How does a CTE work compared to a Derived Table?

A

Very similarly in that they both return a table result

SQL Server 70-461 04-02a

32
Q

What are the characteristics of a table expression?

A
  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

SQL Server 70-461 04-02a

33
Q

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

A
  • 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

SQL Server 70-461 04-02a

34
Q

What is the second downside to a Derived Table?

A
  • 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.

SQL Server 70-461 04-02a

35
Q

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

A
  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

SQL Server 70-461 04-02a

36
Q

What is an anchor member?

A

A query that returns a valid relational result table.

SQL Server 70-461 04-02a

37
Q

What is a recursive member?

A

A query that has a reference to the recursive CTE name

SQL Server 70-461 04-02a

38
Q

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

A

A view does not accept input parameters. An Inline Table-Valued Function does.

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

39
Q

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

A

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)

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

40
Q

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

A

It is exactly the same except you use the words OUTER APPLY instead of CROSS APPLY.

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

41
Q

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?

A

None. With no correlation when using CROSS APPLY it acts just like CROSS JOIN.

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

42
Q

What are the two types of APPLY operators?

A
  1. CROSS APPLY
  2. OUTER APPLY

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

43
Q

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

A 
<statement to drop function if it already exists>;
GO
CREATE FUNCTION function_name
(
@variable_name AS variable_data_type
)
RETURNS TABLE
AS
RETURN
<select statement using the variable>;
GO

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

44
Q

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

A 
--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

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

45
Q

How does CROSS APPLY work?

A
  • 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.

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

46
Q

How does the APPLY operator work?

A

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.

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

47
Q

Diagram of a CROSS APPLY for reference.

A

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

48
Q

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

A 
SELECT <columns from left and right tables>
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

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

49
Q

Example of a CROSS APPLY using a Derived Table

A

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

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

50
Q

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

A 
SELECT <columns from left and right tables>
FROM left_table_name
CROSS APPLY inline_table_valued_function
AS alias_for_inline_table_value_function

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

51
Q

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

A 
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

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

52
Q

How does OUTER APPLY work?

A
  • 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.

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

53
Q

Diagram of an OUTER APPLY for reference.

A

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

54
Q

What is the difference between APPLY and JOIN operators?

A
  • 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.

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

Interview Prep - SQL (26 decks)

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