Microsoft SQL Server - Lesson 29: Composite Techniques of Creating Tables

	Composite Techniques of Creating Tables

Fundamentals of Common Table Expresions (CTE)

Introduction

A common table expression, or CTE, is a temporary selection or other query operation of records from one or more tables. You use it to get an idea of what the query operation would produce if performed on a table. You can generate a CTE to create, select, merge, or delete records.

There are two types of common table expressions: recursive and non-recursive.

Practical Learning: Introducing Common Table Expressions

Start the computer and log in
Launch Microsoft SQL Server and click Connect
On the Standard toolbar, click the New Query button

To create a new database, type the following:

USE master;
GO

-- Create a new database for a hotel business
IF  EXISTS(SELECT name 
	   FROM sys.databases 
 	   WHERE name = N'CeilInn2'
)
DROP DATABASE CeilInn2
GO

CREATE DATABASE CeilInn2
GO

USE CeilInn2;
GO

CREATE TABLE SleepingRooms (
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit default 0
);
GO

CREATE TABLE ConferenceRooms (
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Conference',
    BedType nvarchar(40),
    Rate money default 75.85,
    Available bit default 0
);
GO

INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'106', default, 65.95, 1),
      (N'107', N'Queen', 65.95, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO

INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
VALUES(N'C-120', 525.00, 1);
GO

INSERT INTO SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
VALUES(N'116', N'Studio', N'King', 112.95, 0);
GO

INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
VALUES(N'C-122', 450.00, 1);
GO

INSERT INTO SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
VALUES(N'202', N'Studio', N'King', 105.95, 1),
      (N'203', N'Studio', default, 102.50, 1);
GO

INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'204', N'Double', 115.95, 1),
      (N'205', default, 82.95, 0),
      (N'206', N'King', 98.50, 1)
GO

INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
VALUES(N'C-302', 650.00, 1);
GO

Press F5 to execute

Creating a Common Table Expression

The formula to create a common table expression (CTE) is:

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

You start with the WITH keyword followed by a name for the temporary set. The name must be different from any table that will be used in the CTE's expression. Later, we will see the parameters you can add after the name. After the name, type AS followed by parentheses. In the parentheses, create a simple or composite SELECT expression.

After the code that defines the CTE, that is, after the AS(CTE_query_definition) expression, create a SELECT statement that will produce the results.

Practical Learning: Creating a Common Table Expression

Delete everything in the Query window and replace it with:

USE CeilInn2;
GO

WITH BedRooms AS
(
    SELECT * FROM SleepingRooms
)

SELECT * FROM BedRooms;
GO

Press F5 to execute

To see the results of both tables, change the statement as follows:

USE CeilInn2;
GO

WITH BedRooms AS
(
    SELECT * FROM SleepingRooms
)

SELECT * FROM BedRooms;
SELECT * FROM SleepingRooms;
GO

Press F5 to execute

To see a list of only available rooms from the CTE, change the statement as follows:

USE CeilInn2;
GO

WITH BedRooms AS
(
    SELECT * FROM SleepingRooms WHERE Available = 1
)
SELECT * FROM BedRooms
GO

Press F5 to see the result

A CTE With Parameters

To make sure you can externally control the results of a CTE, you can pass a type of parameter to it. To do this, after the name of the CTE and before the AS operator, add the parentheses and pass one or more parameters, each represented by a name. The names of parameters must be the exact same names of columns of the table(s) from which the CTE's statement will be based. The number of columns must be the same as the number of columns that will be involved in the final SELECT statement.

In the body of the CTE, use the parameter(s) as you wish. For example, you can involve the parameter(s) in a condition in the CTE.

Practical Learning: Passing Parameters to a CTE

To pass some parameters to the CTE, delete everything in the Query window and replace it with:

USE CeilInn2;
GO

WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS
(
	SELECT RoomNumber, RoomType, BedType, Rate, Available
	FROM SleepingRooms
	WHERE BedType = N'Queen'
)
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO

Press F5 to execute

To see a list of only available rooms from the CTE, change the statement as follows:

USE CeilInn2;
GO

WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS
(
    SELECT RoomNumber, RoomType, BedType, Rate, Available
    FROM SleepingRooms
    WHERE BedType = N'Queen'
)
SELECT RoomNumber, RoomType, Rate, Available
FROM BedRooms
WHERE Available = 1;
GO

Press F5 to see the result

Topics on Common Table Expresions

Using Conditions

In all of our SELECT expressions so far, considered all records of the intended table. In some cases, you can set a condition that would select only some records. You can use the condition in one or both SELECT statements.

Recursive Common Table Expressions

A recursive common table expression is a CTE that can contain more than one SELECT statement. In the body of the CTE, you can create as many SELECT statements as you want but those statements must be joined. To join them, you can use a UNION, UNION ALL, or MERGE operator.

Practical Learning: Creating a Recursive CTE

Change the CTE's code as follows:

USE CeilInn2;
GO

WITH HotelRooms
AS
(
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
)
SELECT * FROM HotelRooms;
GO

Press F5 to execute

To see a list of all available rooms, change the statement as follows:

USE CeilInn2;
GO

WITH HotelRooms
AS
(
    SELECT * FROM SleepingRooms
    UNION
    SELECT * FROM ConferenceRooms
)
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms
WHERE Available = 1;
GO

Press F5 to execute

Non-Recursive Common Table Expressions

A non-recursive common table expression is a CTE that can be followed by only one SELECT, INSERT, UPDATE, or DELETE statement that involves a column from inside the CTE's body.

Topics on Common Table Expresions

In our recursive CTE expressions so far, considered all records of the intended table. In some cases, you can set a condition that would select only some records before merging them. You can use the condition in one or both SELECT statements.

Fundamentals of Table-Valued Functions

Introduction

All of the functions we have created so far returned 0 or a single value that was one of the data types supported by Transact-SQL. A table-valued function is a function that returns a table. A multi-statement table-valued function is a function that creates a table, optionally fills it up with the desired records, and then returns the table.

Creating a Table-Valued Function

To create a function that returns a table, you have various options. In the Object Explorer, expand the database that will own the function, expand the Programmability node, and expand Functions. Right-click Table-Valued Functions and click New Multi-Statement Table-Valued Function...

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

As an alternative, open a new Query window. In the Templates explorer, expand Function, drag Create Multi-Statement Function (New Menu):

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

The primary formula to create a multi-statement table-valued function is:

CREATE FUNCTION FunctionName()
RETURNS @TableName TABLE (Table's Columns)
AS
BEGIN
    RETURN;
END;

You start with the CREATE FUNCTION expression followed by a name for the function and its parentheses. As seen already, after the parentheses, type the RETURNS keyword. Since you are actually creating a table, follow the RETURNS keyword with the @ operator, followed by the desired name of the table, followed by the TABLE keyword, and its parentheses.

Create the columns of the table in the parentheses. Each column is created using the formula:

ColumnName DataType Options

Each column follows the necessary options: name, data type, optional primary key, optional nullity, optional unique, optional check, and/or optional expression.

After the parentheses that contain the columns, type AS, BEGIN, RETURN and END, preferably on different lines for better readability. Here is an example that creates a function that internally creates a table:

USE Exercise;
GO

CREATE FUNCTION GetStates()
RETURNS @States TABLE
(
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    RETURN;
END;
GO

Data Entry on a Table-Valued Function

When creating the function, if you want to add records to the table, before the RETURN keyword, use the INSERT or INSERT INTO statement(s) as necessary. Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetStates()
RETURNS @States TABLE
(
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES(N'DC', N'District of Columbia'),
			      (N'MD', N'Maryland'),
			      (N'PA', N'Pennsylvania'),
			      (N'VA', N'Virginia'),
			      (N'WV', N'West Virginia');
    RETURN;
END;
GO

Calling a Multi-Statement Table-Valued Function

After creating a multi-statement table-valued function, it is represented in the Table-Valued Functions node of the Object Explorer. To call the function, use a SELECT statement. To indicate that you want all records of the table, you can use the * operator. Here is an example:

USE Exercise;
GO

SELECT * FROM GetStates();
GO

This would produce:

Multi-Select Table-Valued Function

A multi-statement table-valued function is a type of table. It simply makes it easy to access the records of a table with one call. Because it is a table-type, you can access one, some, or all of its fields. To specify the fields you want to get, create a SELECT statement that holds the list of columns. Here is an example:

USE Exercise;
GO
SELECT LongName FROM GetStates();
GO

This would produce:

Inline Table-Valued Function

Of course you can also set conditions that the SELECT statement must follow. In the same way, you can include any of the statements we have already seen, or that we will see in other lessons, in the creation or call of an inline table-valued function.

Passing Arguments to a Multi-Statement Table-Valued Function

In its formula, we saw that a table-valued function had parentheses. In the parentheses, you can specify one or more arguments. Each argument is in the form of @, followed by a name, and a data type. Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40))
RETURNS @States TABLE
(
    RegionName nvarchar(40),
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES(N'New England', N'CT', N'Connecticut'),
			      (N'New England', N'ME', N'Maine'),
			      (N'South Atlantic', N'DC', N'District of Columbia'),
			      (N'South Atlantic', N'MD', N'Maryland'),
			      (N'South Atlantic', N'PA', N'Pennsylvania'),
			      (N'South Atlantic', N'VA', N'Virginia'),
			      (N'South Atlantic', N'WV', N'West Virginia');
    RETURN;
END;
GO

When calling the function, you must provide a value for the argument(s). Here is an example:

USE Exercise;
GO

DECLARE @Region nvarchar(40);
SET @Region = N'South Atlantic';
SELECT ShortName, LongName, RegionName FROM GetStates(@Region)
WHERE RegionName = @Region;
GO

The argument of the function is created using any rule you know about parameters. For example, it can be created with a default value. Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40) = N'All')
RETURNS @States TABLE
(
    RegionName nvarchar(40),
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES
	(N'New England', N'CT', N'Connecticut'),
	(N'New England', N'ME', N'Maine'),
	(N'New England', N'MA', N'Massachusetts'), 
	(N'New England', N'NH', N'New Hampshire'),
	(N'New England', N'RI', N'Rhode Island'), 
	(N'New England', N'VT', N'Vermont'),
	(N'Mid-Atlantic', N'NJ', N'New Jersey'), 
	(N'Mid-Atlantic', N'NY', N'New York'),
	(N'Mid-Atlantic', N'PA', N'Pennsylvania'), 
	(N'South Atlantic', N'DE', N'Delaware'),
	(N'South Atlantic', N'DC', N'District of Columbia'),
	(N'South Atlantic', N'FL', N'Florida'),	
	(N'South Atlantic', N'GA', N'Georgia'),
	(N'South Atlantic', N'MD', N'Maryland'),
	(N'South Atlantic', N'PA', N'Pennsylvania'), 
	(N'South Atlantic', N'VA', N'Virginia'),
	(N'South Atlantic', N'WV', N'West Virginia'),
	(N'All', N'AL', N'Alabama'), (N'All', N'AK', N'Alaska'), 
	(N'All', N'AZ', N'Arizona'),
	(N'All', N'AR', N'Arkansas'), (N'All', N'CA', N'California'),
	(N'All', N'CO', N'Colorado'), (N'All', N'CT', N'Connecticut'), 
	(N'All', N'DE', N'Delaware'), 
	(N'All', N'DC', N'District of Columbia'),
	(N'All', N'FL', N'Florida'), (N'All', N'GA', N'Georgia'), 
	(N'All', N'HI', N'Hawaii'),
	(N'All', N'ID', N'Idaho'), (N'All', N'IL', N'Illinois'), 
	(N'All', N'IN', N'Indiana'),
	(N'All', N'IA', N'Iowa'), (N'All', N'KS', N'Kansas'), 
	(N'All', N'KY', N'Kentucky'),
	(N'All', N'LA', N'Louisiana'), (N'All', N'ME', N'Maine'), 
	(N'All', N'MD', N'Maryland'),
	(N'All', N'MA', N'Massachusetts'), (N'All', N'MI', N'Michigan'),
	(N'All', N'MN', N'Minnesota'), (N'All', N'MS', N'Mississippi'),
	(N'All', N'MO', N'Missouri'), (N'All', N'MT', N'Montana'), 
	(N'All', N'NE', N'Nebraska'),
	(N'All', N'NV', N'Nevada'), (N'All', N'NH', N'New Hampshire'),
	(N'All', N'NJ', N'New Jersey'),	(N'All', N'NM', N'New Mexico'),
	(N'All', N'NY', N'New York'), (N'All', N'NC', N'North Carolina'),
	(N'All', N'ND', N'North Dakota'), (N'All', N'OH', N'Ohio'),
	(N'All', N'OK', N'Oklahoma'), (N'All', N'OR', N'Oregon'),
	(N'All', N'PA', N'Pennsylvania'), 
	(N'All', N'RI', N'Rhode Island'),
	(N'All', N'SC', N'South Carolina'), 
	(N'All', N'SD', N'South Dakota'),
	(N'All', N'TN', N'Tennessee'), (N'All', N'TX', N'Texas'), 
	(N'All', N'UT', N'Utah'),
	(N'All', N'VT', N'Vermont'), (N'All', N'VA', N'Virginia'),
	(N'All', N'WA', N'Washington'),	
	(N'All', N'WV', N'West Virginia'),
	(N'All', N'WI', N'Wisconsin'), (N'All', N'WY', N'Wyoming');
    RETURN;
END;
GO

When calling the function, you can omit the value of the argument and type DEFAULT in its place holder:

USE Exercise;
GO

SELECT RegionName, ShortName, LongName FROM GetStates(DEFAULT);
GO

Otherwise, you can call the function and pass a value of your choice.

Table-Valued Functions and Conditions

When calling a multi-statement table-valued function, you can use a condition to restrict the list of records it produces. For example, you can use a WHERE condition to control the result. Here is an example:

USE Exercise;
GO

DECLARE @Region nvarchar(40);
SET @Region = N'South Atlantic';
SELECT LongName, ShortName FROM GetStates(@Region)
WHERE RegionName = @Region;
GO

This would produce:

Multi-Statement Table-Valued Functions

In the same way, you can use CASE, LIKE, BETWEEN, etc to refine the result.

Inline Table-Valued Functions

Introduction

Consider the following Employees table and its records:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15),
      (N'720947', N'Paul Handsome', N'20000802', 36.05)
GO
INSERT INTO Employees(EmployeeNumber, EmployeeName, HourlySalary)
VALUES(N'247903', N'Gina Palau', 18.85);
GO
INSERT INTO Employees
VALUES(N'595002', N'John Meah', N'20000212', 32.25);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112');
GO
INSERT INTO Employees
VALUES(N'928375', N'Chuck Stansil', N'20080628', 20.18),
      (N'792764', N'Orlando Perez', N'20000616', 12.95);
GO

When you want to retrieve the records held by that table, you can write a SELECT statement made of too many sections. Imagine you plan to get the records many times, over and over. Instead of creating a new SELECT statement every time, you can create a function that gives you access to the records.

An inline table-valued function is a function that produces the records from a function using a SELECT statement. As opposed to an inline table-valued function, you cannot (directly) include a SELECT statement in a multi-statement table-valued function. The solution is to create the function as inline.

Creating an Inline Function

To create an inline table-valued function, you have various options. To have code generated for you:

In the Object Explorer, expand the database that will own the function. Expand Programmability. Expand Functions. Right-click Table-Valued Functions and click New Inline Table-Valued Function...

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(	
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
	<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
	SELECT 0
)
GO

In the Template Explorer, expand the Function node. Right-click Create Inline Function (New Menu) and click either Open or Edit
Open an empty Query window. In the Template Explorer, expand the Function node. Drag Create Inline Function (New Menu) and drop it in the Query window

Once the skeleton code has been generated for you, you can edit and customize it.

The basic formula to create an inline function is:

CREATE FUNCTION FunctionName(Argument(s))
RETURNS TABLE 
AS
RETURN
    SELECT 0

You start with the CREATE FUNCTION expression followed by a name for the function and its arguments. If the function takes one or more arguments, enter it(them) in the parentheses. If there is no argument, leave the parentheses empty,

The closing parenthesis is followed by the RETURNS TABLE expression. The TABLE keyword indicates that the function will return a list of columns and their records. This is followed by the AS and the RETURN keywords.

After the RETURN keyword, if necessary, declare some variable(s) and do what you want in the body of the function. Before the end of the function, you must create a SELECT statement that returns a table-type.

Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetEmployees()
RETURNS TABLE 
AS
RETURN 
    SELECT * FROM Employees;
GO

Calling an Inline Table-Valued Function

After creating an inline table-valued function, you can access its records. To call such a function, you use a SELECT statement. For example, to indicate that you want all records of the table, you can use the * operator. Here is an example:

USE Exercise;
GO

SELECT * FROM GetEmployees();
GO

This would produce:

Inline Table-Valued Function

Because an inline table-valued function is a type of table, you can access one, some, or all of its fields using a SELECT statement. Here is an example:

SELECT EmployeeName, HourlySalary FROM GetEmployees();

Of course, you can also set a condition that the SELECT statement must follow. Here is an example:

SELECT EmployeeNumber, EmployeeName  FROM GetEmployees()
WHERE HourlySalary >= 20.00;

In the same way, you can include any of the statements we have already seen, or that we will see in other lessons, in the creation or call of an inline table-valued function.

Practical Learning: Closing Microsoft SQL Server

Close the Query window
When asked whether you want to save, click No
Close Microsoft SQL Server

Exercises

Lesson Summary Questions

Imagine you have a table as follows:

CREATE TABLE Rooms
(
    RoomNumber nchar(10) not null,
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit
);
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', default, 80.25, 0),
      (N'105', N'King', 95.50, 1),
      (N'108', N'King', 92.50, 1),
      (N'109', default, 68.95, 0),
      (N'110', default, 74.95, 1);
GO

Which of the following is a valid common table expression?

WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS
(
	SELECT RoomNumber, RoomType, BedType, Rate, Available
	FROM Rooms
	WHERE BedType = N'Queen'
)
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO

WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
BEGIN
	SELECT RoomNumber, RoomType, BedType, Rate, Available
	FROM Rooms
	WHERE BedType = N'Queen'
END
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO

CREATE BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS TABLE
BEGIN
	SELECT RoomNumber, RoomType, BedType, Rate, Available
	FROM Rooms
	WHERE BedType = N'Queen'
END
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO

WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS CTE
BEGIN
	SELECT RoomNumber, RoomType, BedType, Rate, Available
	FROM Rooms
	WHERE BedType = N'Queen'
END
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO

WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
SET SELECT RoomNumber, RoomType, BedType, Rate, Available
FROM Rooms
WHERE BedType = N'Queen';
GO
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO

What is the basic formula to create a table-valued functions?

CREATE FUNCTION FunctionName()
RETURNS @TableName(Table's Columns)
BEGIN
    RETURN AS TABLE;
END;

CREATE FunctionName() AS FUNCTION
RETURN @TableName TABLE (Table's Columns)
AS
BEGIN
    RETURNS;
END;

CREATE FUNCTION FunctionName()
RETURNS @TableName TABLE (Table's Columns)
AS
BEGIN
    RETURN;
END;

CREATE FunctionName() AS FUNCTION
RETURNS @TableName TABLE (Table's Columns)
BEGIN
    RETURN;
END;

CREATE FUNCTION FunctionName(Table's Columns) AS TABLE
AS
BEGIN
    RETURN;
END;

Answers

Right Answer
Wrong Answer
Wrong Answer
Wrong Answer
Wrong Answer

Answers

Wrong Answer
Wrong Answer
Right Answer
Wrong Answer
Wrong Answer