Advanced SQL- LAB 4
Subqueries,
DCL and TCL
Statements
Variables,
Selection, and
Iteration
Stored
Procedures
Tables and
Functions
Error
Handling
Using DDL Statements The Knowledge Academy
Table of Contents
1. Overview .................................................................................................................................... 2
2. The Lab Contents ....................................................................................................................... 2
3. Scalar Functions ......................................................................................................................... 3
3.1 UCASE() Function ................................................................................................................. 3
3.2 LCASE() Function .................................................................................................................. 3
3.3. MID() Function .................................................................................................................... 4
3.4 ROUND() Function................................................................................................................ 4
3.5 LEN() Function ...................................................................................................................... 5
3.6 NOW() Function ................................................................................................................... 5
3.7 FORMAT() Function .............................................................................................................. 6
4. Temporary Tables ...................................................................................................................... 6
4.1 Local Temporary Tables ....................................................................................................... 6
4.2 Global Temporary Tables ..................................................................................................... 7
5. Table Variables ........................................................................................................................... 9
5.1 Update the table variable .................................................................................................... 9
5.2 Delete the table variable.................................................................................................... 10
6. Table Valued Function ............................................................................................................. 12
6.1 In-line Table-Valued Function ............................................................................................ 12
6.1.1 Modifying an In-line Table Valued Function: .................................................................. 13
6.2 Multi-Statement Table-Valued Function ........................................................................... 14
7. Derived Table and CTEs............................................................................................................ 15
7.1 Derived Table ..................................................................................................................... 15
7.2 Common Table Expressions (CTEs) .................................................................................... 16
7.2.1 Creating a Nonrecursive CTE........................................................................................... 16
7.2.2 Creating a Recursive CTE................................................................................................. 16
Page | 1
Using DDL Statements The Knowledge Academy
1. Overview
Advanced SQL Lab 4 introduces user about the usage of different
functions and tables in the SQL Server environment. These include using
scalar functions, creating temporary table and table variables, etc.
2. The Lab Contents
Tasks
Scalar Functions
Temporary Table
Table Variable
Table Values Functions
Derived Tables and CTEs
Page | 2
Using DDL Statements The Knowledge Academy
3. Scalar Functions
SQL scalar functions return a single value based on the input value. Here
are some scalar functions.
3.1 UCASE() Function
This function is used to convert the value of string column to uppercase
characters. SQL query for using UCASE is:
SELECT UPPER (CustomerName)
As Uppercase
FROM Customer;
Figure 1. UCASE Function
3.2 LCASE() Function
This function is used to convert value of string columns to Lowercase
characters. SQL query for converting a string value to LCASE is:
SELECT LOWER (CustomerName)
As Lowercase
FROM Customer;
Figure 2. LCASE Function
Page | 3
Using DDL Statements The Knowledge Academy
3.3. MID() Function
MID () is used to extract substrings from column values of string type in
a table. SQL query for MID function is:
SELECT Substring (CustomerName, 2, 2)
As Substring
FROM Customer
Figure 3. MID Function
3.4 ROUND() Function
This function is used to round a numeric field to a number of nearest
integer. SQL query for ROUND function is:
Select Round (Product_Amount, 2)
As [Rounded Amount]
From Order_details
Figure 4. ROUND Function
Page | 4
Using DDL Statements The Knowledge Academy
3.5 LEN() Function
This function returns the length of the value in the text field. In the
following query, we are fetching the length of names of cities from
Customer table:
Select LEN (City)
As LEN
From Customer
Figure 5. LEN Function
3.6 NOW() Function
This function returns the current system date and time. In the following
query, we are fetching current system date and time:
Select GETDATE ()
From Orders
Figure 6. NOW Function
Page | 5
Using DDL Statements The Knowledge Academy
3.7 FORMAT() Function
This function is used to format how a field is to be displayed. In the
following query, we are using the FORMAT function to format the current
date in ‘en-gb’ form:
Select FORMAT (OrderDate, ‘d’, ‘en-gb’)
As Date
From Orders
Figure 7. FORMAT Function
4. Temporary Tables
It stores a subset of data from an ordinary table for a specified period.
They are especially beneficial when a user has a large number of records
in the table, and user frequently need to interact with the small subset of
those records. It has two types: local temporary tables and global
temporary tables.
4.1 Local Temporary Tables
These tables are only accessible to the current connection to the database
for the current user. To use the local temporary tables, we need to create
it first, then insert the data into it and after that we can retrieve the
information from the temporary table.
Page | 6
Using DDL Statements The Knowledge Academy
Run the given script to create the local temporary table:
Create Table #CustCountry
(CustomerId numeric (4, 0),
CustomerName varchar (30),
Country varchar (30))
Inserting into Local Temporary Table:
Select CustomerId, CustomerName, Country into
#CustCountry from Customer
Retrieving Data from Local Temporary Table:
Select * from #CustCountry
Figure 8. Local Temporary Table
4.2 Global Temporary Tables
These tables are accessible to any connection once generated. These
tables are dropped when the current user disconnected. Here we are
creating global temporary tables and using it to retrieve the information:
Page | 7
Using DDL Statements The Knowledge Academy
Run the given script to create the global temporary table:
Create Table ##Cities
(City varchar (30))
Inserting rows in the global temporary table:
INSERT INTO ##Cities Select Distinct City from Customer
Retrieving Data:
Select * from ##Cities
Figure 9. Global Temporary Table
Page | 8
Using DDL Statements The Knowledge Academy
5. Table Variables
Table variable are introduced by Microsoft as an alternative to using
temporary tables. Use the given snippet to declare the table variable:
Declare @ProductRevenue TABLE
(
ProductId Numeric (4, 0),
TotalAmount Numeric (18, 0)
)
Begin
Insert into @ProductRevenue
Select ProductId, Sum (Product_Amount) from
Order_Details group by ProductId
Select * from @ProductRevenue
End
Figure 10. Declaring a Table Variable
5.1 Update the table variable
To update the table variable, run the given command:
Declare @ProductRevenue TABLE
(
ProductId Numeric (4, 0),
Page | 9
Using DDL Statements The Knowledge Academy
TotalAmount Numeric (18, 0)
)
Begin
Insert into @ProductRevenue
Select ProductId, Sum (Product_Amount) from
Order_Details group by ProductId
Select * from @ProductRevenue
UPDATE @ProductRevenue
SET TotalAmount=TotalAmount+ 1500
WHERE ProductID = 9374
Select * from @ProductRevenue
End
Figure 11. Updating the Table Variable
5.2 Delete the table variable
To delete the table variable, run the given command:
Declare @ProductRevenue TABLE
(
ProductId Numeric (4, 0),
Page | 10
Using DDL Statements The Knowledge Academy
TotalAmount Numeric (18, 0)
)
Begin
Insert into @ProductRevenue
Select ProductId, Sum (Product_Amount) from
Order_Details group by ProductId
Select * from @ProductRevenue
UPDATE @ProductRevenue
SET TotalAmount=TotalAmount+ 1500
WHERE ProductID = 9374
Select * from @ProductRevenue
Delete from @ProductRevenue where ProductId=9374
Select * from @ProductRevenue
End
Figure 12. Delete the Table Variable
Page | 11
Using DDL Statements The Knowledge Academy
6. Table Valued Function
A table-valued function returns the data in the form of table.
6.1 In-line Table-Valued Function
Creating table valued function using In-line table-valued function
Create Function CustFunc ()
Returns @tabCust Table
(CustomerId numeric (4, 0),
CustomerName varchar (30),
City varchar (30),
Country varchar (30),
PostalCode Varchar (10)
)
As
Begin
INSERT INTO @tabCust SELECT * from Customer
WHERE CustomerId > 5602
Return
End
Retrieving Data from in-line table valued functions:
Select * from CustFunc ()
Page | 12
Using DDL Statements The Knowledge Academy
Figure 13. In-line Table-Valued Function
6.1.1 Modifying an In-line Table Valued Function:
Alter Function CustFunc ()
Returns @tabCust Table
(CustomerId numeric (4, 0),
CustomerName varchar (30),
Country varchar (30)
)
As
Begin
INSERT INTO @tabCust SELECT CustomerId,
CustomerName, Country from Customer
WHERE CustomerId > 5602
Return
End
Retrieving Data from in-line table valued functions:
Select * from CustFunc ()
Page | 13
Using DDL Statements The Knowledge Academy
Figure 14. Modify an In-line Table-Valued Function
6.2 Multi-Statement Table-Valued Function
Multi-statement Table-valued Function is used to create a more complex
function that can use the loops, conditions, etc.
Creating Multi-Statement table valued function:
Create FUNCTION GetCities (@Country varchar (30))
RETURNS @myRecords TABLE
(
CustomerlName varchar (30),
City varchar (30)
)
AS
Begin
If @Country='Australia'
INSERT INTO @myRecords SELECT CustomerName,
City from Customer
WHERE Country ='Australia'
Return
End
Page | 14
Using DDL Statements The Knowledge Academy
Retrieving Data:
Select * from GetCities ('Australia')
Figure 15. Multi-Statement Table Valued Function
7. Derived Table and CTEs
7.1 Derived Table
A derived table is a way to create a temporary set of records which can be
used within another query in SQL.
Here is an example of Derived Table:
Select Top 5 CustomerName, City
From
(Select * From Customer) as Cust
Where Country = 'England'
Figure 16. Derived Table
Page | 15
Using DDL Statements The Knowledge Academy
7.2 Common Table Expressions (CTEs)
CTE are defined within the statement by using WITH operator. It defines
a temporary result set which user can then use in the SELECT statement.
7.2.1 Creating a Nonrecursive CTE
A nonrecursive CTE does not reference itself within the CTE. In the
following example, creating a CTE named OrderCust.
With
OrderCust (CustId, Ord_Date)
As
(Select CustId, OrderDate from Orders)
Select Customername, OC.Ord_Date
From Customer
INNER JOIN OrderCust as OC
On Customer.CustomerId = OC.CustId
Figure 17. Nonrecursive CTE
7.2.2 Creating a Recursive CTE
Recursive CTE references itself within the CTE. In the following example,
creating a recursive CTE named UserCTE:
WITH UserCTE AS (
SELECT CustomerId, CustomerName, City, 0 as steps
Page | 16
Using DDL Statements The Knowledge Academy
From dbo.Customer
Where CustomerId < 5604
UNION ALL
Select ng.CustomerId, ng.CustomerName, ng.city, us.steps
+1 AS steps
From UserCTE us
INNER JOIN dbo.Customer ng
ON us.CustomerId = ng.CustomerId)
Select * from UserCTE OPTION (MAXRECURSION 4)
Figure 18. Recursive CTE
===================== End of Lab Session 4 =======================
Page | 17
