Sql Training

July 20, 2022

Sql Training - 11

Table Variable

A Table Variable is a variable that can store the complete table of the data inside it. It is similar to a Table Variable but as I said a Table Variable is a variable. So how do we declare a variable in SQL? Using the @ symbol. The same is true for a Table Variable. so the syntax of the Table Variable is as follows:

Declare @TempTable TABLE(      

id int,    

Name varchar(20)      

)

insert into @TempTable values(1,'Sourabh Somani')  

insert into @TempTable values(2,'Shaili Dashora')  

insert into @TempTable values(3,'Divya Sharma')  

insert into @TempTable values(4,'Swati Soni')  

Select * from @TempTable  

--------------------

Temporary Tables are most likely as Permanent Tables. Temporary Tables are Created in TempDB and are automatically deleted as soon as the last connection is terminated. Temporary Tables helps us to store and process intermediate results. Temporary tables are very useful when we need to store temporary data. The Syntax to create a Temporary Table is given below:

To Create Temporary Table:

CREATE TABLE #EmpDetails (id INT, name VARCHAR(25))

To Insert Values Into Temporary Table:

INSERT INTO #EmpDetails VALUES (01, 'Lalit'), (02, 'Atharva')

To Select Values from Temporary Table:

SELECT * FROM #EmpDetails

-----------------------

Important Points about Table Variables
The same as a temporary table.
Single query cannot be executed.
When we want to perform a few operations then use a Table Variable otherwise if it is a huge amount of data operation then use a temporary table.
Commit and Rollback (Transaction) cannot be possible with Table Variables so if you want to perform a transaction operation then always go with temporary tables.
What are scalar functions
SQL Server scalar function takes one or more parameters and returns a single value.
The scalar functions help you simplify your code. For example, you may have a complex calculation that appears in many queries. Instead of including the formula in every query, you can create a scalar function that encapsulates the formula and uses it in each query.
Creating a scalar function
To create a scalar function, you use the CREATE FUNCTION statement as follows:
CREATE FUNCTION [schema_name.]function_name (parameter_list)
RETURNS data_type AS
BEGIN
    statements
    RETURN value
END
Code language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify the name of the function after the CREATE FUNCTION keywords. The schema name is optional. If you don’t explicitly specify it, SQL Server uses dbo by default.
Second, specify a list of parameters surrounded by parentheses after the function name.
Third, specify the data type of the return value in the RETURNS statement.
Finally, include a RETURN statement to return a value inside the body of the function.
The following example creates a function that calculates the net sales based on the quantity, list price, and discount:
CREATE FUNCTION sales.udfNetSale(
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS 
BEGIN
    RETURN @quantity * @list_price * (1 - @discount);
END;
Code language: SQL (Structured Query Language) (sql)

SELECT  
    dbo.udfNetSale(10,100,0.1);


Later on, we can use this to calculate net sales of any sales order in the order_items from the sample database.
After creating the scalar function, you can find it under Programmability > Functions > Scalar-valued Functions as shown in the following picture:
Calling a scalar function
You call a scalar function like a built-in function. For example, the following statement demonstrates how to call the udfNetSale function:
SELECT 
    sales.udfNetSale(10,100,0.1) net_sale;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
The following example illustrates how to use the sales.udfNetSale function to get the net sales of the sales orders in the order_items table:
SELECT 
    order_id, 
    SUM(sales.udfNetSale(quantity, list_price, discount)) net_amount
FROM 
    sales.order_items
GROUP BY 
    order_id
ORDER BY
    net_amount DESC;
Code language: SQL (Structured Query Language) (sql)
The following picture shows the partial output:

Modifying a scalar function
To modify a scalar function, you use the ALTER instead of the CREATE keyword. The rest statements remain the same:
ALTER FUNCTION [schema_name.]function_name (parameter_list)
    RETURN data_type AS
    BEGIN
        statements
        RETURN value
    END
Code language: SQL (Structured Query Language) (sql)
Note that you can use the CREATE OR ALTER statement to create a user-defined function if it does not exist or to modify an existing scalar function:
CREATE OR ALTER FUNCTION [schema_name.]function_name (parameter_list)
        RETURN data_type AS
        BEGIN
            statements
            RETURN value
        END
Code language: SQL (Structured Query Language) (sql)
Removing a scalar function
To remove an existing scalar function, you use the DROP FUNCTION statement:
DROP FUNCTION [schema_name.]function_name;
Code language: SQL (Structured Query Language) (sql)
For example, to remove the sales.udfNetSale function, you use the following statement:
DROP FUNCTION sales.udfNetSale;
Code language: SQL (Structured Query Language) (sql)
SQL Server scalar function notes
The following are some key takeaway of the scalar functions:
Scalar functions can be used almost anywhere in T-SQL statements.
Scalar functions accept one or more parameters but return only one value, therefore, they must include a RETURN statement.
Scalar functions can use logic such as IF blocks or WHILE loops.
Scalar functions cannot update data. They can access data but this is not a good practice.
Scalar functions can call other functions
What is a table-valued function in SQL Server
A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.
Creating a table-valued function
The following statement example creates a table-valued function that returns a list of products including product name, model year and the list price for a specific model year:
CREATE FUNCTION udfProductInYear (
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;
Code language: SQL (Structured Query Language) (sql)
The syntax is similar to the one that creates a user-defined function.
The RETURNS TABLE specifies that the function will return a table. As you can see, there is no BEGIN...END statement. The statement simply queries data from the production.products table.
The udfProductInYear function accepts one parameter named @model_year of type INT. It returns the products whose model years equal @model_year parameter.
Once the table-valued function is created, you can find it under Programmability > Functions > Table-valued Functions as shown in the following picture:
The function above returns the result set of a single SELECT statement, therefore, it is also known as an inline table-valued function.
Executing a table-valued function
To execute a table-valued function, you use it in the FROM clause of the SELECT statement:
SELECT 
    * 
FROM 
    udfProductInYear(2017);
Code language: SQL (Structured Query Language) (sql)
In this example, we selected the products whose model year is 2017.
You can also specify which columns to be returned from the table-valued function as follows:
SELECT 
    product_name,
    list_price
FROM 
    udfProductInYear(2018);
Code language: SQL (Structured Query Language) (sql)
Here is the partial output:


Management of Schema Objects 
Add, Modify and Drop a Column
Introduction to SQL Server ALTER TABLE DROP COLUMN
Sometimes, you need to remove one or more unused or obsolete columns from a table. To do this, you use the ALTER TABLE DROP COLUMN statement as follows:
ALTER TABLE table_name
DROP COLUMN column_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify the name of the table from which you want to delete the column.
Second, specify the name of the column that you want to delete.
If the column that you want to delete has a CHECK constraint, you must delete the constraint first before removing the column. Also, SQL Server does not allow you to delete a column that has a PRIMARY KEY or a FOREIGN KEY constraint.
If you want to delete multiple columns at once, you use the following syntax:
ALTER TABLE table_name
DROP COLUMN column_name_1, column_name_2,...;
Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify columns that you want to drop as a list of comma-separated columns in the DROP COLUMN clause.
SQL Server ALTER TABLE DROP COLUMN examples
Let’s create a new table named sales.price_lists for the demonstration.
CREATE TABLE sales.price_lists(
    product_id int,
    valid_from DATE,
    price DEC(10,2) NOT NULL CONSTRAINT ck_positive_price CHECK(price >= 0),
    discount DEC(10,2) NOT NULL,
    surcharge DEC(10,2) NOT NULL,
    note VARCHAR(255),
    PRIMARY KEY(product_id, valid_from)
); 
Code language: SQL (Structured Query Language) (sql)
The following statement drops the note column from the price_lists table:
ALTER TABLE sales.price_lists
DROP COLUMN note;
Code language: SQL (Structured Query Language) (sql)
The price column has a CHECK constraint, therefore, you cannot delete it. If you try to execute the following statement, you will get an error:
ALTER TABLE sales.price_lists
DROP COLUMN price;
Code language: SQL (Structured Query Language) (sql)
Here is the error message:
The object 'ck_positive_price' is dependent on column 'price'.
Code language: SQL (Structured Query Language) (sql)
To drop the price column, first, delete its CHECK constraint:
ALTER TABLE sales.price_lists
DROP CONSTRAINT ck_positive_price;
Code language: SQL (Structured Query Language) (sql)
And then, delete the price column:
ALTER TABLE sales.price_lists
DROP COLUMN price;
Code language: SQL (Structured Query Language) (sql)
The following example deletes two columns discount and surcharge at once:
ALTER TABLE sales.price_lists
DROP COLUMN discount, surcharge;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the SQL Server ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.

Creating a simple stored procedure
The following SELECT statement returns a list of products from the products table in the BikeStores sample database:
SELECT 
	product_name, 
	list_price
FROM 
	production.products
ORDER BY 
	product_name;
Code language: SQL (Structured Query Language) (sql)
To create a stored procedure that wraps this query, you use the CREATE PROCEDURE statement as follows:
CREATE PROCEDURE uspProductList
AS
BEGIN
    SELECT 
        product_name, 
        list_price
    FROM 
        production.products
    ORDER BY 
        product_name;
END;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
The uspProductList is the name of the stored procedure.
The AS keyword separates the heading and the body of the stored procedure.
If the stored procedure has one statement, the BEGIN and END keywords surrounding the statement are optional. However, it is a good practice to include them to make the code clear.
Note that in addition to the CREATE PROCEDURE keywords, you can use the CREATE PROC keywords to make the statement shorter.
To compile this stored procedure, you execute it as a normal SQL statement in SQL Server Management Studio as shown in the following picture:
If everything is correct, then you will see the following message:
Commands completed successfully.
Code language: SQL (Structured Query Language) (sql)
It means that the stored procedure has been successfully compiled and saved into the database catalog.
You can find the stored procedure in the Object Explorer, under Programmability > Stored Procedures as shown in the following picture:
Sometimes, you need to click the Refresh button to manually update the database objects in the Object Explorer.
Executing a stored procedure
To execute a stored procedure, you use the EXECUTE or EXEC statement followed by the name of the stored procedure:
EXECUTE sp_name;
Code language: SQL (Structured Query Language) (sql)
Or
EXEC sp_name;
Code language: SQL (Structured Query Language) (sql)
where sp_name is the name of the stored procedure that you want to execute.
For example, to execute the uspProductList stored procedure, you use the following statement:
EXEC uspProductList;
Code language: SQL (Structured Query Language) (sql)
The stored procedure returns the following output:
Modifying a stored procedure
To modify an existing stored procedure, you use the ALTER PROCEDURE statement.
First, open the stored procedure to view its contents by right-clicking the stored procedure name and select Modify menu item:
Second, change the body of the stored procedure by sorting the products by list prices instead of product names:
 ALTER PROCEDURE uspProductList
    AS
    BEGIN
        SELECT 
            product_name, 
            list_price
        FROM 
            production.products
        ORDER BY 
            list_price 
    END;
Code language: SQL (Structured Query Language) (sql)
Third, click the Execute button, SQL Server modifies the stored procedure and returns the following output:
Commands completed successfully.
Code language: SQL (Structured Query Language) (sql)
Now, if you execute the stored procedure again, you will see the changes taking effect:
EXEC uspProductList;
Code language: SQL (Structured Query Language) (sql)
The following shows the partial output:

Deleting a stored procedure
To delete a stored procedure, you use the DROP PROCEDURE or DROP PROC statement:
DROP PROCEDURE sp_name;
Code language: SQL (Structured Query Language) (sql)
or
DROP PROC sp_name;    
Code language: SQL (Structured Query Language) (sql)
where sp_name is the name of the stored procedure that you want to delete.
For example, to remove the uspProductList stored procedure, you execute the following statement:
DROP PROCEDURE uspProductList;

Creating a stored procedure with one parameter
The following query returns a product list from the products table in the sample database:
SELECT
    product_name,
    list_price
FROM 
    production.products
ORDER BY
    list_price;
Code language: SQL (Structured Query Language) (sql)
You can create a stored procedure that wraps this query using the CREATE PROCEDURE statement:
CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)
However, this time we can add a parameter to the stored procedure to find the products whose list prices are greater than an input price:
ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)
In this example:
First, we added a parameter named @min_list_price to the uspFindProducts stored procedure. Every parameter must start with the @ sign. The AS DECIMAL keywords specify the data type of the @min_list_price parameter. The parameter must be surrounded by the opening and closing brackets.
Second, we used @min_list_price parameter in the WHERE clause of the SELECT statement to filter only the products whose list prices are greater than or equal to the @min_list_price.
Executing a stored procedure with one parameter
To execute the uspFindProducts stored procedure, you pass an argument to it as follows:
EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)


Creating a stored procedure with multiple parameters
Stored procedures can take one or more parameters. The parameters are separated by commas.
The following statement modifies the uspFindProducts stored procedure by adding one more parameter named @max_list_price to it:
ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)
Once the stored procedure is modified successfully, you can execute it by passing two arguments, one for @min_list_price and the other for @max_list_price:
EXECUTE uspFindProducts 900, 1000;
Code language: SQL (Structured Query Language) (sql)
The following shows the output:
Using named parameters
In case stored procedures have multiple parameters, it is better and more clear to execute the stored procedures using named parameters.
For example, the following statement executes the uspFindProducts stored procedure using the named parameters @min_list_priceand @max_list_price:
EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000;
Code language: SQL (Structured Query Language) (sql)
The result of the stored procedure is the same however the statement is more obvious.
Creating text parameters
The following statement adds the @name parameter as a character string parameter to the stored procedure.
ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)
In the WHERE clause of the SELECT statement, we added the following condition:
product_name LIKE '%' + @name + '%'
Code language: SQL (Structured Query Language) (sql)
By doing this, the stored procedure returns the products whose list prices are in the range of min and max list prices and the product names also contain a piece of text that you pass in.
Once the stored procedure is altered successfully, you can execute it as follows:
EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000,
    @name = 'Trek';
Code language: SQL (Structured Query Language) (sql)
In this statement, we used the uspFindProducts stored procedure to find the product whose list prices are in the range of 900 and 1,000 and their names contain the word Trek.
The following picture shows the output:

Creating output parameters
To create an output parameter for a stored procedure, you use the following syntax:
parameter_name data_type OUTPUT
Code language: SQL (Structured Query Language) (sql)
A stored procedure can have many output parameters. In addition, the output parameters can be in any valid data type e.g., integer, date, and varying character.
For example, the following stored procedure finds products by model year and returns the number of products via the @product_count output parameter:
CREATE PROCEDURE uspFindProductByModel (
    @model_year SMALLINT,
    @product_count INT OUTPUT
) AS
BEGIN
    SELECT 
        product_name,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

    SELECT @product_count = @@ROWCOUNT;
END;
Code language: SQL (Structured Query Language) (sql)
In this stored procedure:
First, we created an output parameter named @product_count to store the number of products found:
@product_count INT OUTPUT
Code language: SQL (Structured Query Language) (sql)
Second, after the SELECT statement, we assigned the number of rows returned by the query(@@ROWCOUNT) to the @product_count parameter.
Note that the @@ROWCOUNT is a system variable that returns the number of rows read by the previous statement.
Once you execute the CREATE PROCEDURE statement above, the uspFindProductByModel stored procedure is compiled and saved in the database catalog.
If everything is fine, SQL Server issues the following output:
Commands completed successfully.
Code language: SQL (Structured Query Language) (sql)
Calling stored procedures with output parameters
To call a stored procedure with output parameters, you follow these steps:
First, declare variables to hold the values returned by the output parameters
Second, use these variables in the stored procedure call.
For example, the following statement executes the uspFindProductByModel stored procedure:
DECLARE @count INT;

EXEC uspFindProductByModel
    @model_year = 2018,
    @product_count = @count OUTPUT;

SELECT @count AS 'Number of products found';
Code language: SQL (Structured Query Language) (sql)
The following picture shows the output:
In this example:
First, declare the @count variable to hold the the value of the output parameter of the stored procedure:
DECLARE @count INT;
Code language: SQL (Structured Query Language) (sql)
Then, execute the uspFindProductByModel stored procedure and passing the parameters:
EXEC uspFindProductByModel 
     @model_year = 2018, 
     @product_count = @count OUTPUT;
Code language: SQL (Structured Query Language) (sql)
In this statement, the model_year is 2018 and the @count variable assigns the value of the output parameter @product_count.
You can call the uspFindProductByModel stored procedure as follows:
EXEC uspFindProductByModel 2018, @count OUTPUT;
Code language: SQL (Structured Query Language) (sql)
Note that if you forget the OUTPUT keyword after the @count variable, the @count variable will be NULL.
Finally, show the value of the @count variable:
SELECT @count AS 'Number of products found';

Manage Objects with Data Dictionary Views 
Explain the data dictionary 
Use the Dictionary Views 
USER_OBJECTS and ALL_OBJECTS Views 
Table and Column Information 
Query the dictionary views for constraint information 
Query the dictionary views for view, sequence, index, and synonym information Add a comment to a table 
Query the dictionary views for comment information 
Manipulate Large Data Sets
Use Subqueries to Manipulate Data 
Retrieve Data Using a Subquery as Source 
Insert Using a Subquery as a Target 
Usage of the WITH CHECK OPTION Keyword on DML Statements List the types of Multitable INSERT Statements 
Use Multitable INSERT Statements 
Merge rows in a table 
Track Changes in Data over a period of time 
---------
Create, maintain, and use sequences 
Create private and public synonyms 
Control User Access 
Differentiate system privileges from object privileges Create Users 
Grant System Privileges 
Create and Grant Privileges to a Role 
Change Your Password 
Grant Object Privileges 
How to pass on privileges? 
Revoke Object Privileges 


     ------------

Data Management in Different Time Zones 
Time Zones 
CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP þÿCompare Date and Time in a Session  s Time Zone DBTIMEZONE and SESSIONTIMEZONE 
Difference between DATE and TIMESTAMP 
INTERVAL Data Types 
Use EXTRACT, TZ_OFFSET, and FROM_TZ 
Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
Retrieve Data Using Sub-queries 
Multiple-Column Subqueries 
Pairwise and Nonpairwise Comparison 
Scalar Subquery Expressions 
Solve problems with Correlated Subqueries 
Update and Delete Rows Using Correlated Subqueries The EXISTS and NOT EXISTS operators 
Invoke the WITH clause 
The Recursive WITH clause 
Regular Expression Support 
Use the Regular Expressions Functions and Conditions in SQL Use Meta Characters with Regular Expressions 
Perform a Basic Search using the REGEXP_LIKE function Find patterns using the REGEXP_INSTR function Extract Substrings using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE function Usage of Sub-Expressions with Regular Expression Support
Implement the REGEXP_COUNT function

---------------------------
Perform Flashback Operations 
Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP Query External Tables