SKILL 2.2: QUERY DATA BY USING TABLE EXPRESSIONS Flashcards
Name T-SQL Table Expressions
- Derived Tables
- common table expressions (CTE)
- Views
- Inline table variables
Table expressions that are visible only to the statement that defines them?
- Derived Tables
2. Common table Expressiosn (CTE)
Which Table expressions are reusable and preserved in the database as an object
- Views
2. Inline table-valued functions
Table expressions- Which operation is not allowed and how can it be circumvented?
Their inner query can’t have an ORDER BY clause, Except when you use OFFSET-FETCH
(Table expressions)- Columns returned by the inner query must have:
names or use aliases, and they need to be unique
Derived Tables - Format
SELECT
*
FROM
(
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
) AS MyDerivedTable
WHERE
FilmRunTimeMinutes < 100
Derived tables - drawbacks
When nested they are too complicated,
If you want to join multiple instances of the same derived table you can’t. You have to duplicate code - and that generates errors
Common table Expression - Visibility
It is not visible to the outer query
Common table Expression and Derived table parts
- Inner query
- Name assigned to the query and its columns
- Outer query
Common table expression - Format
WITH
AS
(
)
;
Example:
WITH C AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products
)
SELECT categoryid, productid, productname, unitprice
FROM C
WHERE rownum <= 2;
Defining multiple CTEs
You don’t nest CTEs, if you need multiple CTEs you simply separate them by commas.
Each can refer to the previously defined CTEs and the outer query can refer to all of them
Defining multiple CTEs - Format
WITH C1 AS
(
SELECT …
FROM T1
WHERE …
),
C2 AS
(
SELECT
FROM C1
WHERE …
)
SELECT …
FROM C2
WHERE …;
Recursive CTE - Format
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname, 0 AS distance
FROM HR.Employees
WHERE empid = 9
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
FROM EmpsCTE AS S
JOIN HR.Employees AS M ON S.mgrid = M.empid
)
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;
Which table expressions are reusable?
Views and Inlined table-valued functions. They are created as objects in the database and thus can be reused.
You can also control access by assigning permissions
Difference between Views and inline table-valued functions?
Views don’t accept input parameters while inline table-valued functions do
View - format
CREATE VIEW Sales.RankedProducts
AS
SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products;
GO
Inline table-valued function - Format
CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE
AS
RETURN
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname, 0 AS distance
FROM HR.Employees
WHERE empid = @empid
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
FROM EmpsCTE AS S
JOIN HR.Employees AS M
ON S.mgrid = M.empid
)
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;
GO
With GROUPING SETS clause you can…
list all grouping sets that you want to define in the query
