Outrageous T-SQL

Table relationships, store procedures, views; this is not even the beginning.  

Posted by Rishi Raj Gujadhur 1/9/2016

Table of content

 

Prerequisites

 
  • A solid understanding of intermediate level transact SQL programming.  You can learn about SQL intermediate programming using the following link: T-SQL_Intermediate
 
  • SQL Server Management Studio 2012 Express or SQL Server Management Studio 2014 Express. You can download one of the above software at Microsoft's website.
 

A. Update a table value


1. Type:
 
update tblEmployee
set FullName = 'James Bond'
where id=1;
 
update tblEmployee ----> We specify the update keyword followed by the table name of the table to modify.
 
set FullName = 'James Bond' ----> We specify the set keyword followed by the column name and its value to set.
 
where id=1 ----> The where clause is used to limit the amount of records modified. After all we don't want employees to be named James Bond. :)
 
2.Execute query.
 
Result: (1 row(s) affected)
 

B. User defined functions


In the transact SQL series we looked at aggregate functions and scalar functions .

Types of user defined functions:
 
  • Scalar functions (return 1 value)
  • Table functions (returns a list of records)
 

Creating a Scalar function:


1. Type:

Create function ReturnArea(@length int,@width int)
 
       Returns int
       as
       Begin
              Return @length * @width
       End
       Go
 
ReturnArea ----> Name of the function specified by its creator  

(@length int, @width int) ----> A function can take input from the user to do processing using it. Inputs of a function are called parameters.  

, ----> parameters are separated using commas  

@length ----> Name of the parameter using which the function would perform calculations.  

Returns int ----> the keyword specifies that the function would provide the user with a integer value  

Begin ----> Start doing calculations  

Return @length * @width ----> Calculation to do and value to return  

End ----> Stop doing calculations (stop using computer memory)  


2.Execute the query.

Executing the scalar function.
 
1.Type: 
 

select dbo.ReturnArea(2,6)
 
dbo. ----> accessing the functions stored in the database using the keyword dbo followed by .  

ReturnArea ----> Name of the function we want to access.  

(2,6) ----> length and width parameters of the function; please note the order in which parameters are supplied matters.  
  
2.Execute the query. 

Result: 12

Table functions

1. Type:

Create function GetEmployees()
returns table  
as
return
select * from tblEmployee
 
returns table ----> return one or many records
 
2. Execute the query.
 

C. Store procedure


1. Type

Create proc spGetEmp
as
select * from tblEmployee
go
 
proc ----> procedure

spGetEmp ----> procedure name (sp stands for stored procedure)

select * from tblEmployee ----> procedure to do

go ----> optional
 

2. Execute query.
 

Executing a the stored procedure 


1.Type:
 

Exec spGetEmp ----> Exec followed by the stored procedure name
 
2. Execute the query.


Creating  a parameterized Stored procedure

 
1. Type:
 

Create Proc spGetEmployee
@Name varchar(50)
as
select *
from tblEmployee
where FullName = @Name
 
@Name varchar(50) ----> input parameter containing a set of most 50 characters and at least 1 character.
 
2. Execute the query.

Executing a parameterized stored procedure

 
1. Type:

exec spGetEmployee 'Jhon Cena'
 
'Jhon Cena' ----> Argument passed to the method's name parameter  

2. Execute the query.
 
Results: 
 

D. Case statements
 

  • The CASE statement is used to provide if-then-else type of logic to SQL.
 
  • If A is not true then we evaluate B and if they are both false then C is selected automatically. However if A is true then C is disregarded.  

1. Type:
 
Select Case (FullName)
When 'James Bond' Then 'Ironman'
Else 'N/A'
End As 'Full_Name'
from tblEmployee
 
Case (FullName) ----> Column name those fields would be evaluated.

When the field of the column is equal to 'James Bond' temporarily Then change it to 'Ironman'  

Else 'N/A' ----> Else change the column's fields names to N/A.  

End As 'Full_Name' ----> Make column name more readable temporarily.  

2. Execute the query

Result:  
 

E. PRIMARY KEY Constraint


Short answer: Id int NOT NULL PRIMARY KEY
 
Long answer:
 
1. Type:
 
CREATE TABLE tblPersons
(
Id int NOT NULL PRIMARY KEY,
Name varchar(255),
Address varchar(255),
City varchar(255)
)
 
NOT NULL ----> A primary key column cannot contain NULL values (blanks). The not null keyword ensures that the table does not allow the insertion of null values.  

PRIMARY KEY ----> The PRIMARY KEY constraint uniquely identifies each record in a database table.  

Primary keys needs to contain UNIQUE values 2. Execute query

A FOREIGN KEY found in a table points to a PRIMARY KEY which is found in another table.  

1. Type:
 
CREATE TABLE tblOrders
(
  OrderId int NOT NULL PRIMARY KEY,
  OrderNo int,
  Person_Id int FOREIGN KEY REFERENCES tblPersons(Id)
)
 
Person_Id FOREIGN KEY REFERENCES tblPersons(Id) ----> The FOREIGN KEY (FK) from table Orders references the Primary key (PK) from the tblPerson table.  

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, since the value being inserted needs to be one of the values contained in the table's Primary Key column it references.  

2. Execute the query.

Diagram 1



The diagram 1 illustrates: the relationship between the tblOrders and the tblPersons. The OrderId column from the tblOrders  table; references the Id column from tblPerson table.
 

G. Auto Increment


1. Type:
 
CREATE TABLE tblProduct
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name varchar(255) NOT NULL,
Manufacturer varchar(255)
)
 
IDENTITY(1,1) ----> increment id by 1 every time, a new record is inserted.

The primary key field is created automatically every time a new record is inserted into the table.  
 
2. Execute query.
 
  • As shown below When inserting a new record into the "tblProduct" table, the value of the ID field is not required.
 
insert into tblProduct
(Name ,Manufacturer)
Values('Microsoft Windows 10','Microsoft')
 

H. Drop


Drop table


1.Type:

Drop table tblProduct
 
The Drop table keyword deletes the table those named is specified after the table keyword  

2.Execute the query.


Drop column


1. Type:

ALTER TABLE tblEmployee
Drop COLUMN age
 
Drop COLUMN age ----> column name of the column to delete
 
2. Execute the query.
  

I. Union All operator


1. Type:
 
select FullName as 'All_Names' from tblEmployee
union all
select Name from tblPersons
 
Union ----> Combine the fields having common data type from two or more tables together
 
2. Execute the query.

Result: 
  • Just like the Union All statement, the JOIN clause can combine rows from two or more tables, based on a common field between them.
 
The shaded part of Venn diagram below shows what gets Joined between 2 tables; "table1" and "table2"


 
1. Type:

SELECT tblOrders.OrderId,tblOrders.Person_Id, tblPersons.Name
FROM tblOrders
JOIN tblPersons
ON tblOrders.Person_Id=tblPersons.Id
 
tblOrders.OrderId  ----> the table names "tblOrders" and "tblPersons" are specified to differentiate between 2 similar column names from the 2 tables.   

JOIN tblPersons ----> tblPersons is the table to be joined with the table named tblOrders thus it is specified after the JOIN keyword  

ON tblOrders.Person_Id=tblPersons.Id ----> The specification of the foreign key and primary key relationship between the 2 tables is required.  

The JOIN clause returns all rows from two or more tables where a join condition is met.  

2. Execute the query.

Result:  
 

K. Views


1. Type:
 
Create view vGetEmp
as
Select * from tblEmployee 
 
Create view vGetEmp ----> View name.

Select * from tblEmployee ---->  Saving a query for later use.
 
2.  Execute query.
 

L. Default constraint


The DEFAULT constraint inserts a default value specified by its user into a specified column.
 
1. Type:
 
CREATE TABLE tblCars
(
CarId int Primary key identity(1,1) NOT NULL,
Manucfacturer varchar(50) DEFAULT 'Unknown'
)
 
DEFAULT 'Unknown' ----> The value 'Unknown' automatically inserted when if no other value is specified for the Manucfacturer column on record insertion.
                    
2. Execute the query.
 

M. Next steps

 
  • Feel free to practice what you learnt in this tutorial using SQL Server Management Server (SSMS).
 
  • Then you can try the Ultimate T-SQL tutorial using the following link: Ultimate T-SQL
 
Download link: T-SQL_Advance_Tutorial.docx