[Writing Transact-SQL Statements tutorial](https://docs.microsoft.com/en-us/sql/t-sql/tutorial-writing-transact-sql-statements?view=sql-server-ver15)

WritingTransact-SQLStatementsTutorial.sql

/*============================================================================*/
/*               Lesson 1: Create & query database objects                    */
/*============================================================================*/
/*Create a database*/
CREATE DATABASE TestData  
GO  
/*Switch the Query Editor connection to the TestData database*/
USE TestData  
GO
/*Create the table*/
CREATE TABLE dbo.Products  
   (ProductID int PRIMARY KEY NOT NULL,  
   ProductName varchar(25) NOT NULL,  
   Price money NULL,  
   ProductDescription text NULL)  
GO
/*Insert data into a table*/
-- Standard syntax  
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)  
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')  
GO
-- Changing the order of the columns  
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)  
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')  
GO
-- Skipping the column list, but keeping the values in order  
INSERT dbo.Products  
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')  
GO
-- Dropping the optional dbo and dropping the ProductDescription column  
INSERT Products (ProductID, ProductName, Price)  
    VALUES (3000, '3mm Bracket', .52)  
GO
/*Update the products table*/
UPDATE dbo.Products  
    SET ProductName = 'Flat Head Screwdriver'  
    WHERE ProductID = 50  
GO
/*Read the data in a table*/
-- The basic syntax for reading data from a single table  
SELECT ProductID, ProductName, Price, ProductDescription  
    FROM dbo.Products  
GO
-- Returns all columns in the table  
-- Does not use the optional schema, dbo  
SELECT * FROM Products  
GO
-- Returns only two of the columns from the table  
SELECT ProductName, Price  
    FROM dbo.Products  
GO
-- Returns only two of the records in the table  
SELECT ProductID, ProductName, Price, ProductDescription  
    FROM dbo.Products  
    WHERE ProductID < 60  
GO
-- Returns ProductName and the Price including a 7% tax  
-- Provides the name CustomerPays for the calculated column  
SELECT ProductName, Price * 1.07 AS CustomerPays  
    FROM dbo.Products  
GO
/*Create a view*/
CREATE VIEW vw_Names  
   AS  
   SELECT ProductName, Price FROM Products;  
GO
/*Test the view*/
SELECT * FROM vw_Names;  
GO
/*Create a stored procedure*/
CREATE PROCEDURE pr_Names @VarPrice money  
   AS  
   BEGIN  
      -- The print statement returns text to the user  
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));  
      -- A second statement starts here  
      SELECT ProductName, Price FROM vw_Names  
            WHERE Price < @varPrice;  
   END  
GO
/*Test the stored procedure*/
EXECUTE pr_Names 10.00;  
GO
/*============================================================================*/
/*           Lesson 2: Configure permissions on database objects              */
/*============================================================================*/
/*Create a SQL login*/
CREATE LOGIN [computer_name\Test]  
    FROM WINDOWS  
    WITH DEFAULT_DATABASE = [TestData];  
GO
/*Create a user in a database*/
USE [TestData];  
GO  
CREATE USER [Test] FOR LOGIN [computer_name\Test];  
GO
/*Grant permission to stored procedure*/
GRANT EXECUTE ON pr_Names TO Test;  
GO
/*============================================================================*/
/*                  Lesson 3: Delete database objects                         */
/*============================================================================*/
USE TestData;  
GO
/*Revoke stored procedure permissions*/
REVOKE EXECUTE ON pr_Names FROM Test;  
GO
/*Drop permissions*/
DROP USER Test;  
GO
DROP LOGIN [computer_name\Test];  
GO
DROP PROC pr_Names;  
GO
DROP VIEW vw_Names;  
GO
/*Delete table*/
DELETE FROM Products;  
GO
DROP TABLE Products;  
GO
/*Remove database*/
USE MASTER;  
GO  
DROP DATABASE TestData;  
GO