OUTRAGEOUS T-SQL

TABLE RELATIONSHIPS, STORE PROCEDURES, VIEWS; THIS IS NOT EVEN THE BEGINNING.

Originally Posted By myself on 1/9/2016 at http://rchannel.azurewebsites.net/ChannelR/Details/5

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 nullkeyword 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


F. FOREIGN KEY CONSTRAINT

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 keycolumn, 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:


J. JOIN /INNER JOIN CLAUSE

  • 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.