SQL Server Notes

1-

--------------------------- Comments -----------------------------

-- Single Line Comment

/*
Multi Line Comment
.....
*/
-- ===================================================================
--------------------------- Data Types ----------------------------
------------------- 1.Numeric Data Types
bit -- Boolean Value 0[false] : 1[true]
tinyint -- 1 Byte => -128:127 | 0:255 [Unsigned(Positive)]
smallint -- 2 Byte => -32768:32767 | 0:65555 [Unsigned]
int -- 4 Byte
bigint -- 8 Byte

------------------- 2.Fractions Data Types

smallmoney 4B.0000 -- 4 Numbers After Point
money 8B.0000 -- 4 Numbers After Point
real .0000000 -- 7 Numbers After Point
float .000000000000000 -- 15 Numbers After Point
dec -- Datatype and Make Valiadtion at The Same Time => Recommended
dec(5, 2) 124.22 18.1 12.2212 XXX 2.1234

------------------- 3.String Data Types

char(10) [Fixed Length Character] Ahmed 10 Ali 10
varchar(10) [Variable Length Character] Ahmed 5 Ali 3
nchar(10) [like char(), But With UniCode] ‫??? على‬
nvarchar(10) [like varchar(), But With UniCode] ‫على‬
nvarchar(max) [Up to 2GB]
varchar(max)

------------------- 4.DateTime Data Types

Date MM/dd/yyyy
Time hh:mm:ss.123 --Defualt=> Time(3)
Time(5) hh:mm:ss.12345
smalldatetime MM/dd/yyyy hh:mm:00
datetime MM/dd/yyyy hh:mm:ss.123
datetime2(4) MM/dd/yyyy hh:mm:ss.1234
datetimeoffset 11/23/2020 10:30 +2:00 Timezone

------------------- 5. Binary Data Types

binary 01110011 11110000
image

------------------- 6. Other Data Types

Xml
sql_variant -- Like Var In Javascript

==================================================================
--------------------------- Variables ----------------------------
-- 1. Global Variables
select @@Version
select @@ServerName

-- 2. Local Variables
declare @age int = 3

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 1

set @age = 55
print @age

2-

-- 1) DDL => Data Definition Language

--------------------------------------
-- 1. Create

Create Database CompanyCycle39

Use CompanyCycle39 -- to select database

-- to create table
create table Employee
(
SSN int primary key identity(1,1), -- to create id column
FName varchar(15) not null, -- to Constraint not null
Address varchar(20) default 'Cairo', -- to create default value
Salary Money, -- to create money column
BDate Date, -- to create date column
Gender char(1), -- to create column 1 char only >> F M
LName varchar(15),
SuperSSN int references Employee(SSN),
-- to create relation (column relation must be the same type primary key)
DNo int
)
----------------------------------------
create table Department
(
Number int primary key identity(10, 10),
Name varchar(15) not null,
StarteDate Date,
MGRSSN int references Employee(SSN)
)
----------------------------------------
create table DeptLocations
(
Number int references Department(Number),
Name varchar(15),
primary key(Number, Name)
)
----------------------------------------
create table Project
(
PNum int primary key identity,
PName varchar(20) not null,

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 2

Location varchar(20),
DNo int references Department(Number)
)
----------------------------------------
create table Dependent
(
Name varchar(20) not null,
Gender char(1),
BDate Date,
Relationship varchar(20),
ESSN int references Employee(SSN),
primary key(ESSN, Name)
)
----------------------------------------
create table Works_On
(
ESSN int references Employee(SSN),
PNo int references Project(PNum),
Hours tinyint ,
primary key(ESSN, PNo)
)

------------------------------------------------------------------
-- 2. ALter [Update]
-- to update table after create
Alter Table Employees
Add Test int

Alter Table Employees

Alter Column Test bigint -- to update anything on column

Alter Table Employees

Drop Column Test
-- Example :-
alter table Employees
add foreign key (DNo) references Department(Number)
------------------------------------------------------------------
-- 3. Drop [Remove]
drop table Employees
-- =========================================================================
-- 2) DML => Data Manpulation Language

-- 1. Insert

--------- 1.1 Simple Insert (Add Just Only One Row)

Insert Into Employees -- insert into table name or table name(columnName,columnName)

Values('Ahmed', 'Nasr', '02-22-1993', 'Alex', 'M', 8000, Null, Null)

Insert Into Employees(Salary, FName, BDate, Gender)

Values(4000, 'Mohamed', '03-22-1999', 'M')

-- 1. Identity Constraint
-- 2. Default Value
-- 3. Allow Null

--------- 1.2 Row Constructor Insert (Add More Than One Row)
Insert Into Employees
Values
('Mona', 'Nasr', '02-22-1963', 'Cairo', 'F', 8000, 1, Null),
('Amr', 'Ibrahim', '02-22-1963', 'Tanta', 'M', 8000, 1, Null),
('Aya', 'Ali', '02-22-1963', 'Giza', 'F', 8000, 1, Null),

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 3

('Mohamed', 'Amr', '02-22-1963', 'Mansoura', 'M', 8000, 1, Null)

--------------------------------------------------------------------------------------
-- 2. Update

Update Employees
Set EmpAddress = 'Dokki'
where Id = 1

Update Employees
Set FName = 'Hamada', LName = 'Hambozo'
where Id = 2

Update Employees
Set Salary += Salary * .1
where Salary <= 5000 and EmpAddress = 'Cairo'

--------------------------------------------------------------------------------------
-- 3. Delete

Delete From Employees

Where Id = 10

Delete From Employees

Where Id = 9
/*
=========================================================================
======================== DQL => Data Query Language =====================
*/

use Route

-- 3) DQL => Data Query Language

select *
from Student

select St_Fname +' '+ St_Lname FullName

from Student

select St_Fname +' '+ St_Lname [Full Name]

from Student

select [Full Name] = St_Fname +' '+ St_Lname

from Student

select *
from Student
where St_Age > 23

select *
from Student
where St_Age between 21 and 25

select *
from Student
where St_Address in ('Alex', 'Mansoura', 'Cairo')

select *
from Student

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 4

where St_Address not in ('Alex', 'Mansoura', 'Cairo')

Select *
from Student
where St_super is not Null

--------------------------
-- like With Some Patterns
/*
_ => one Char
% => Zero or More Chars

*/
select *
from Student
where St_Fname like '_A%' -- Na Fady Kamel Hassan Nada Nadia

/*

'a%h' ah aghjklh
'%a_' ak hjkak
'[ahm]%' amr hassan mohamed a
'[^ahm]%' Esraa Fatma Sara ‫اى حرف غير ال اي او االتش او االم‬
'[a-h]%' ‫الحروف من ال ايه الى االتش‬
'[^a-h]%' ‫الحروف الى مش من ال ايه الى االتش‬
'[346]%' ‫جملة تبدأ ب اي رقم من دول‬
'%[%]' ghjkl%
‫استخدمنا البراكتس عشان نقوله ان عالمة البيرسنت دي او _احنا عايزين نسلكتها النها محجوزة فى اللغة‬
'%[_]%' Ahmed_Ali _
'[_]%[_]' _Ahmed_

*/
select *
from Employee
where FName like '[^ahm]%'

-- Distinct -- ‫لو عندى اكتر من حاجة مكررة هستخدم ديستنكت لتفادى التكرار‬
select distinct FName
from Employee

--------------------------------- Order By ‫– للترتيب وعمل سورت‬-------------------------

select St_Id, St_Fname, St_Age
from Student
order by St_Fname -- ‫كدا برتبهم من ايه تو زد‬

select St_Id, St_Fname

from Student
order by St_Fname desc – ‫ديسك بترتبهم بطريقة عكسية من زد تو ايه‬

select St_Id, St_Fname, St_Lname

from Student
order by St_Fname, St_Lname desc –- ‫وممكن ارتبهم بنا ّء على اكثر من فيلد‬

select St_Id, St_Fname, St_Age

from Student
order by 1, 2 desc – ‫واحد تعبر عن اول فيلد انا مختاره في السليكت فوق واتنين عن تاني فيلد‬

select *
from Student
order by 1 , 2 – ‫كدا هيرتب بنا ّء علي اول وتانى فيلد عندى فى الجدول‬

-- ===========================================================

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 5

--------------------------- Joins -------------------------

-- 1. Cross join (Cartisian Product)

select S.St_Fname, D.Dept_Name
from Student S, Department D -- ANSI (Cartisian Product)

select S.St_Fname, D.Dept_Name

from Student S Cross Join Department D -- Microsoft (Cross Join)

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

-- 2. Inner Join (Equi Join)

-- Equi Join Syntax (ANSI)

select S.St_Fname, D.Dept_Name
from Student S, Department D
where D.Dept_Id = S.Dept_Id

select S.St_Fname , D.*

from Student S, Department D
where D.Dept_Id = S.Dept_Id

-- Inner Join Syntax (Microsoft)

select S.St_Fname, D.Dept_Name
from Student S inner join Department D
on D.Dept_Id= S.Dept_Id

---------------------------------------------------------
-- 3. Outer Join
-- 3.1 Left Outer Join
select S.St_Fname, D.Dept_Name
from Student S left outer join Department D
on D.Dept_Id= S.Dept_Id

-- 3.2 Right Outer Join

select S.St_Fname, D.Dept_Name
from Student S right outer join Department D
on D.Dept_Id= S.Dept_Id

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 6

-- 3.3 Full Outer Join
select S.St_Fname, D.Dept_Name
from Student S full outer join Department D
on D.Dept_Id = S.Dept_Id

3-

-- 4. Self Join
select S.St_Fname, Super.*
from Student S , Student Super
where Super.St_Id = S.St_Super

select S.St_Fname, Super.*

from Student S inner join Student Super
on Super.St_Id = S.St_Super

-- Multi Table Join

-- Equi Join Syntax
select S.St_Fname, Crs_Name, Grade
from Student S, Stud_Course SC, Course C
where S.St_Id = SC.St_Id and C.Crs_Id = SC.Crs_Id

-- Inner Join Syntax

select S.St_Fname, Crs_Name, Grade
from Student s inner join Stud_Course SC
on S.St_Id = SC.St_Id
inner join Course C
on C.Crs_Id = SC.Crs_Id
----------------------------------
-- Join + DML
-- Update

-- Updates Grades Of Student Who 're Living in Cairo

update SC
set grade += 10
from Student S inner join Stud_Course SC
on S.St_Id = SC.St_Id and St_Address = 'cairo'

-- ---------------------- delete by Join -----------------------

delete from SC
from Student S inner join Stud_Course SC
on S.St_Id = SC.St_Id and St_Address = 'cairo'

-- --------------------- insert by Join -----------------------

insert into Stud_Course (Crs_id,St_id,Grade)
values (100,6,70)
select S.St_Fname ,St_Address, Grade
from Student S inner join Stud_Course SC
on S.St_Id = SC.St_Id

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 7

where St_Address = 'cairo'
----------------------------------------------------------
-- =======================================================
--------------------- Built-in Functions -----------------
-- =======================================================

------------------- 1. Aggregate Functions ---------------

-- Return Just Only One Value (Scalar Functions) ‫بترجعلي قيمة واحدة فقط‬
-- That Value is Not Existed In Database ‫الناتج بيكون فيو فقط وغير موجود فى الداتا بيز‬

-- Count, Sum, Avg, Max, Min

select count(*)
from student

select count(St_Id)
from student

--The Count of Students That have Ages (Not Null)

-- ‫الناتج بتاع الكونت بيكون مجموع الريكوردات اللى لها قيمة فقط ومبيجبليش عدد النال‬
select count(st_age)
from student

select count(*) , count(st_id), count(st_lname), count(st_age)

from Student

select sum(salary)
from instructor

select avg(st_age)
from Student

select sum(st_age)/COUNT(*) –- ‫هنا هيجبلي المتوسط برضو‬

from Student

select sum(st_age)/COUNT(st_age) –- ‫هنا هيجبلي متوسط االعمار‬

from Student

select Max(Salary) as MaxSalary, Min(Salary) as MinSalary

from Instructor

-- Minimum Salary For Each Department

select Dept_Id, Min(Salary) as MininmumSalary
from Instructor
Group By Dept_Id

-- You Can't Group By With * or PK

-- We Grouping With Repeated Value Column

Select Dept_Id, St_Address, Count(St_Id) as NumberOfStudents

From Student
Group By Dept_Id, St_Address -- Will Group Here With Which Column?
-- If You Select Columns With Aggregate Functions,You Must Group By With The Same Columns

-- Get Number Of Student For Each Department [that has more than 3 students]
select S.Dept_Id, D.Dept_Name, Count(St_Id) as NumberOfStudents
from Student S, Department D
where D.Dept_Id = S.Dept_Id
group by S.Dept_Id , D.Dept_Name
having Count(St_Id) > 3

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 8

-- Get Number Of Student For Each Department [Need Join?]
select Dept_id, Count(St_Id) as NumberOfStudents
from Student
group by Dept_Id
where dept_id is not null

select S.Dept_id, Count(S.St_Id) as NumberOfStudents

from Student S, Department D
where D.Dept_Id = S.Dept_Id
group by S.Dept_Id

-- Get Sum Salary of Instructors For Each [Which has more than 3 Instructors]
select Dept_Id, Sum(Salary) as SumOfSalary
from Instructor
group by Dept_Id
having Count(Ins_Id) > 3

-- You Can't Use Agg Functions Inside ‫ داخل‬Where Clause (Not Valid)
-- Because Aggreagate Generate Groups That 'Having' Works With it
-- Where Works With Rows => in order to Make Filteration
select Sum(Salary)
from Instructor
where count(Ins_Id) < 100 -- Not Valid

-- You Can Use Having Without Group By Only In Case Of Selecting Just Agg Functions
-- ‫بنستخدم الهافنج مع الجروب باي ولو انا بسليكت اجريجيت فانكشن فقط فى الحالتين دول‬
-- ‫لو انا عايزاستخدم اجرجيت فانكشن ككونديشن مع الوير هعملها فى ساب كويري مثال تحت خالص‬
select Sum(Salary)
from Instructor
having count(Ins_Id) < 100 -- Valid

-- Group By With Self Join

select Super.St_FName, Count(Stud.St_Id)
from Student Stud, Student Super
where Super.St_Id = Stud.St_Super
group by Super.St_FName

------------------------------------------------------------
---------------------- 2. Null Functions -------------------
------- 1. IsNull
select st_Fname
from Student

select st_Fname
from Student
where St_Fname is not null

select isnull(St_Address, '')

from Student

select isnull(St_Address, 'Student Has No FName')

from Student

select isnull(st_Fname, St_Lname) as NewName

from Student

------- 2. Coalesce
select coalesce(st_Fname, St_Lname, St_Address, 'No Data')
from Student

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 9

---------------------------------------------------------
---------------------- 3. Casting Functions -------------

select St_Fname +' '+ St_Age

from student

------- 1. Convert [Convert From Any DateType To DateType]

select St_Fname +' '+ Convert(varchar(2), St_Age)
from student

select 'Student Name= ' + St_Fname + ' & Age= '+ Convert(varchar(2), St_Age)
from student

select IsNull(St_Fname,'')+' '+ Convert(varchar(2), IsNull(St_Age, 0))

from student

-- Concat => Convert All Values To String Even If Null Values (Empty String)
select Concat(St_Fname, ' ', St_Age)
from student

------- 2. Cast [Convert From Any DateType To DateType]

-- ‫الفرق بينها وبين الكونفرت هو السينتاكس‬
select cast(getdate() as varchar(50))

-- Convert fun >> Take Third Parameter If You Casting From Date To String
-- For Specifying The Date Format You Need
select convert(varchar(50),getdate(),101)
select convert(varchar(50),getdate(),102)
select convert(varchar(50),getdate(),110)
select convert(varchar(50),getdate(),111)

------- 3. Format [Convert From Date To String]

select format(getdate(),'dd-MM-yyyy')
select format(getdate(),'dddd MMMM yyyy')
select format(getdate(),'ddd MMM yy')
select format(getdate(),'dddd')
select format(getdate(),'MMMM')
select format(getdate(),'hh:mm:ss')
select format(getdate(),'hh tt')
select format(getdate(),'HH')
select format(getdate(),'dd MM yyyy hh:mm:ss')
select format(getdate(),'dd MM yyyy hh:mm:ss tt')
select format(getdate(),'dd')

---------------------------------------------------------
------------------- 4. DateTime Functions ---------------

select getdate()
select day(getdate())
select Month(GETDATE())
select eomonth(getdate()) -- end of month ‫هيجبلي اخر يوم فى الشهر الحالى‬
select eomonth('1/1/2000')
select format(eomonth(getdate()),'dd')
select format(eomonth(getdate()),'dddd')

---------------------------------------------------------
------------------- 5. String Functions -----------------

select lower(st_fname),upper(st_lname)
from Student

select substring(st_fname,1,3)

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 10

from Student

select len(st_fname),st_fname
from Student

---------------------------------------------------------
--------------------- 6. Math Functions -----------------

select power(2,2)

-- log sin cos tan

---------------------------------------------------------
--------------------- 7. System Functions ---------------

select db_name()

select suser_name()

select @@ServerName

==============================================================
---------------------- Sub Query -----------------------------
-- Output Of Sub Query[Inner] As Input To Another Query[Outer]
-- SubQuery Is Very Slow (Not Recommended Except Some Cases) ‫بطئ جدا‬
-- ‫استخدمه فى الحاالت اللى زي اللى تحت دي‬
-- ‫ لو انا عايز اعمل كوندشن باالجريجيت فانكشن باستخدام الوير‬-1
-- ‫ لو انا عايز اسليكت اجرجيجت فانكشن وانا مش مستخدم جروب باي‬-2
/*
select *
from student
where st_age > avg(st_age) => Not Valid
*/
-- ‫ لو انا عايز اعمل كوندشن باالجريجيت فانكشن باستخدام الوير‬-1
-- outer query
select *
from student
where st_age > (select avg(st_age) from student) -- inner query
-- ‫هو االول بينفذ االينر كويري وبعد كدا االوتر كويري‬
-- 23 just value
/* -- ‫ لو انا عايز اسليكت اجرجيجت فانكشن وانا مش مستخدم جروب باي‬-2
select *, count(st_id)
from student => Not Valid
*/
select *,(select count(st_id) from student) --14
from student

-- SubQuery Vs Join

-- Get Departments Names That Have Students

-- By Join
select distinct D.Dept_Name
from Department D inner join Student S
on D.Dept_Id = S.Dept_Id
-- By SubQuery
select dept_name
from Department
where Dept_Id in (select distinct(Dept_Id) from Student
where Dept_Id is not null
)

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 11

-- SubQuery With DML
--------- SubQuery With Delete

--Delete Students Grades Who Are Living in Cairo

delete from Stud_Course
where St_Id in (Select St_Id from Student
where St_Address = 'Cairo'
)
delete from SC
from Student S inner join Stud_Course SC
on S.St_Id = SC.St_Id
where S.St_Address = 'Cairo'

-- to Create New Table From join

select e.Fname + ' ' + e.Lname as fullName , d.Dname
into myNewTable -- this to create new table name table is myNewTable
from Employee e inner join Departments d
on e.Dno = d.Dnum

4-

------------------------- Top ----------------------------

-- First 5 Students From Table
select top(5)*
from student

select top(5)st_fname
from student

-- Last 5 Students From Table

select top(5)*
from student
order by st_id desc

-- Get The Maximum 2 Salaries From Instractors Table

select Max(Salary) -- to get max salary
from Instructor

select Max(Salary)
from Instructor
where Salary <> (Select Max(Salary) from Instructor)

select top(2)salary
from Instructor
order by Salary desc

-- Top With Ties, Must Make Order by

‫لو اخر قيمة في قيمة بعديها نفس القيمة هاتها معاك‬--
select top(5) st_age
from student
order by st_age desc

select top(5) with ties st_age

from student
order by st_age desc

-- Randomly Select
select newid() -- Return GUID Value (Global Universal ID)

select St_Fname, newid()

from Student

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 12

select top(3)*
from student
order by newid()
===============================================================
------------------- Ranking Function -----------------------
-- salary values -- 1000 1000 900 900 500 500
-- 1. Row_Number() -- 1 2 3 4 5 6
-- 2. Dense_Rank() -- 1 1 2 2 3 3
-- 3. Rank() -- 1 1 3 3 5 5

select Ins_Name, Salary,

Row_Number() over (Order by Salary desc) as RNumber,
Dense_Rank() over (Order by Salary desc) as DRank,
Rank() over (Order by Salary desc) as R
from Instructor

-- 1. Get The 2 Older Students at Students Table ‫طالب اتنين اكبر‬

-- Using Ranking
select *
from (select St_Fname, St_Age, Dept_Id,
Row_number() over(order by St_Age desc) as RN
from Student) as newtable
where RN <= 2

-- Using Top(Recommended)
Select top(2) St_Fname, St_Age, Dept_Id
from Student
Order By St_Age Desc

-- 2. Get The 5th Younger Student

-- Using Ranking (Recommended)

select * from
(select St_Fname, St_Age, Dept_Id,
row_number() over(order by St_age ) as RN
from Student where st_age is not null) as newtable
where RN = 5

-- Using Top
select top(1)* from
(select top(5)*
from Student
order by St_Age desc) as newTable
order by St_Age

-- 2. Get The Younger Student At Each Department

-- ‫قسم كل في طالب اصغر خامس على احصل‬
-- Using Ranking Only
select * from
(select Dept_Id, St_Fname, St_Age,
row_number() over(partition by Dept_id order by St_age desc) as RN
from Student) as newtable
where RN = 1

-- 4.NTile

-- We Have 15 Instructors, and We Need to Get The 5th Instructors

-- Who Take the lowest salary ‫ اقل‬5 ‫مرتب بياخدو‬
select *
from

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 13

(
select Ins_Name, Salary, Ntile(3) over(order by Salary) as G
from Instructor
) as newTable
where G = 3

===============================================================
---------------------------------------------------------
-- Execution Order
Select CONCAT(St_FName, ' ', St_Lname) as FullName
from Student
Where FullName = 'Ahmed Hassan' -- Not Valid

Select CONCAT(St_FName, ' ', St_Lname) as FullName

from Student
Where CONCAT(St_FName, ' ', St_Lname) = 'Ahmed Hassan'

Select *
from (Select CONCAT(St_FName, ' ', St_Lname) as FullName
from Student) as Newtable
Where FullName = 'Ahmed Hassan'

Select CONCAT(St_FName, ' ', St_Lname) as FullName

from Student
Order By FullName

--execution order
----from
----join
----on
----where
----group by
----having
----select
----order by
----top

===============================================================
---------------------------- Schema ---------------------

-- Schema Solved 3 Problems:

-- 1.You Can't Create Object With The Same Name
-- [Table, View, Index, Trigger, Stored Procedure, Rule]
-- 2. There Is No Logical Meaning (Logical Name)
-- 3. Permissions
select *
from Student

-- DBO [Default Schema] => Database Owner

select *
from ServerName.DBName.SchemaName.objectName

select *
from [DESKTOP-VF50P25].iti.dbo.student

select *
from Company_SD.dbo.Project

Create Schema HR

Create Schema Sales

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 14

Alter Schema HR
Transfer student

select * from Student -- not valid

select * from Hr.Student -- valid

Alter Schema HR
Transfer Department

Select *
from HR.Department

ALter Schema Dbo

Transfer HR.Department

======================================================
------------------------------------------------------
-- Union Family (union | union all | intersect | except)
-- Have 2 Conditions:
-- 1- The Same Datatype
-- 2- The Same Number Of Selected Columns

Select St_Id, St_FName from Student

--union all ‫الجدولين هاتلى‬
--union ‫المتشابه معادا الجدولين هاتلى‬
--intersect ‫الجدولين بين ما المتشابه هات‬
-- except ‫التانى الجدول مع المتشابه بإستثناء االوالنى الجدول هاتلى‬
Select Ins_Id, Ins_Name from Instructor

-- Example (Select The Student Names At All Route Branches)

===============================================================
---------------------------------------------------------------
-- DDL [Create, Alter, Drop, Select Into]
-- Create Physical Table ‫فعلى جدول‬

Select * into NewEmployees

From MyCompany.Dbo.Employee

-- Create Just The Structure Without Data

Select * into NewProjects
From MyCompany.Dbo.Project
Where 1 = 2

-- Take Just The Data Without Table Structure,

-- but 2 tables must have same structure (Insert Based On Select)
Insert Into NewProjects
Select * from MyCompany.Dbo.Project

=========================================================
---------------- User Defined Function ------------------

-- Any SQL Server Function must return value

-- Specify Type Of User Defined Function That U Want Based On The Type Of Return
-- User Defined Function Consist Of ‫من بتتكون‬
--- 1. Signature (Name, Parameters, ReturnType)
--- 2. Body
-- Body Of Function Must be Select Statement Or Insert Based On Select
-- May Take Parameters Or Not

=================================================================
----------------types Function ------------------

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 15

-- 1. Scalar Fun (Return One Value)
-- 2. Inline Table Function (Return Table)
-- 3. Multistatment Table Fuction

-- 1. Scalar Fun (Return One Value)

Create Function GetStudentNameByStudentId(@StId int)
returns varchar(20) -- Function Signature
begin
declare @StudentName varchar(20)
Select @StudentName = St_FName
from Student
where St_Id = @StId
return @StudentName
end

Select Dbo.GetStudentNameByStudentId(8)

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

Create or alter Function GetDepartmentManagerNameByDepartmentName(@DeptName varchar(20))

Returns varchar(20) -- Function Signature
begin
declare @MangerName varchar(20)
Select @MangerName = E.FName
From Employee E, Departments D
where E.SSN = D.MGRSSN and D.DName = @DeptName
return @MangerName
end

Select dbo.GetDepartmentManagerNameByDepartmentName('DP3')

=================================================================
-- 2. Inline Table Function (Return Table)

Create Function GetDepartmenInstructorsByDepartmentId(@DeptId int)

Returns Table -- Function Signature
as
Return
(
Select Ins_Id, Ins_Name, Dept_Id
from Instructor
Where Dept_Id = @DeptId
)

Select * from dbo.GetDepartmenInstructorsByDepartmentId(20)

=================================================================
-- 3. Multistatment Table Fuction
-- => Return Table With Logic [Declare, If, While] Inside Its Body

create or Alter Function GetStudentDataBasedPassedFormat(@Format varchar(20))

Returns @t table
(
StdId int,
StdName varchar(20)
)
With Encryption
as
Begin
if @format = 'first'
Insert Into @t
Select St_Id, St_FName
from Student

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 16

 else if @format = 'last'
Insert Into @t
Select St_Id, St_LName
from Student
else if @format = 'full'
Insert Into @t
Select St_Id, Concat(St_FName,' ', St_LName)
from Student

return
End

select * from dbo.GetStudentDataBasedPassedFormat('fullname')

select * from dbo.GetStudentDataBasedPassedFormat('FIRST')
5-

-------------------------------------------------------------
---------------------- Views --------------------------------

-- 1. Standard View (Contains Just Only One Select Statement)

Create View AlexStudentsView

as
Select St_Id, St_FName, St_Address
from Student
Where St_Address = 'Alex'

Select * from AlexStudentsView

Create View CairoStudentsView

as
Select St_Id, St_FName, St_Address
from Student
Where St_Address = 'Cairo'

Select * from CairoStudentsView

---------------------------------------------------------------
-- 2. Partitioned View (Contains More Than One Select Statement)

Create View CairoAlexStudentsView

as
Select * from CairoStudentsView
Union
Select * from AlexStudentsView

Select * from CairoAlexStudentsView

-- Hierarchy Of Database?
/*
Server Level => Databases
Database Level => Schemas
Schema Level => Database Objects (Table, View, SP, and etc)
Table Level => Columns, Constraints
*/

Alter Schema Dbo

Transfer HR.CairoAlexStudentsView

-- With Encryption >>

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 17
-- to privecy to hidden database name and table name with error

Create View StudentDepartmentDataView(StdId, StdName, DeptId, DeptName)

With Encryption
as
Select St_Id, St_FName, D.Dept_Id, D.Dept_Name
from Student S inner join Department D
on D.Dept_Id = S.Dept_Id

Select * from StudentDepartmentDataView

SP_HelpText 'GetStudentDataBasedPassedFormat'

Create View StudentGradesView (StdName, CrsName, StdGrade)

With Encryption
as
Select S.St_FName, C.Crs_Name, SC.Grade
from Student S, Stud_Course SC, Course C
where S.St_Id = SC.St_Id and C.Crs_Id = SC.Crs_Id

Select * from StudentGradesView

---------------------------------------------------------
-- View + DML
-- ‫اكتر وال واحد جدول كام من داتا بيجيب دا الفيو هل اسالة نفسي اسال‬
-- View => One Table
-- ‫كي البريمري و نال النوت الركوير الفيلدات من بالى اخد انسرت هعمل لو‬

Create View CairoStudentsView

as
Select St_Id, St_FName, St_Address
from Student
Where St_Address = 'Cairo'

Insert Into CairoStudentsView

Values(323234, 'Pola', 'Cairo')

update CairoStudentsView
set St_Fname = 'Omer'
where st_id = 22

delete from CairoStudentsView

where St_Id = 22

-- View => Multi Table

Alter View StudentDepartmentDataView(StdId, StdName, FK_DeptId, DeptId, DeptName)

With Encryption
as
Select St_Id, St_FName, S.Dept_Id, D.Dept_Id, D.Dept_Name
from Student S inner join Department D
on D.Dept_Id = S.Dept_Id

Select * from StudentDepartmentDataView

--- DELETE XXXXX ‫هينفع مش ممنوع‬
Delete From StudentDepartmentDataView
Where StdId = 1 -- Invalid cause relation ‫وتعال الريليشن امسح‬

-- Insert ‫ و‬Update
-- ‫جدول من اكتر من داتا بيجيب فيو على ابديت او انسيرت اعمل عشان‬
-- ‫التانى الجدول هحدد الكالم ونفس دا المثال زى في ابديت او هانسرت اللى احددالجدول محتاج هكون‬

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 18

-- ‫في وانسرت الطالب جدول فى الموجود كي الفورن فيلد اسليكت الزم يبقا معين قسم فى طالب اانسرت عايز لو‬

Insert Into StudentDepartmentDataView(StdId, StdName, FK_DeptId)

Values(9709, 'Ay 7aga', 1000) -- Invalid cause relation

Insert Into StudentDepartmentDataView(DeptId, DeptName)

Values(1000, 'Test')

-- view with Constraint

Alter View CairoStudentsView
With Encryption
as
Select St_Id, St_FName, St_Address
from Student
Where St_Address = 'Cairo' -- Make Check Constraint
With Check Option

Insert Into CairoStudentsView

Values(97397, 'Ronaldo', 'Cairo')

-- ====================================================================
--------------------- Relationship Rules -----------------------------
--- 1. Delete Rule

--- Before Delete Department No (40) With Its Instructors and Students
Delete From Department
Where Dept_Id = 40

-- Firstly, For Instructors

-- 1. Transfer Instructors Of Department No (40) to another Department
Update Instructor
Set Dept_Id = 10
Where Dept_Id = 40

-- 2. OR Transfer Instructors Of Department No (40) To No Department (Null)

Update Instructor
Set Dept_Id = Null
Where Dept_Id = 40

-- 3. OR Delete Instructors Of Department No (40) To No Department (Null)

Delete From Instructor
Where Dept_Id = 40

-- Secondly, For Students also The Same Idea

-- 2. Update Rule [The Same Idea Of Delete Rule]

-- another steps you can do this from wizerd

-- 1- open diagrams and click right click on line relation
-- properties and go to INSERT and UPDATE section to change constriant
-- No action == get error
-- cascade == do deleted all relation with table
-- setNull == set null value
-- setDefault == if you do default value it's get default value

-- ===================================================================
------------------------ Delete Vs Truncate -----------------------
Delete From Student

Truncate Table Student

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 19

-- ===================================================================
-------------------------------------------------------------------
------------------------ Stored Procedure / proc -------------------------
-- Benefits Of SP:
-- 1. Performance
-- 2. Security (Hide Meta Data)
-- 3. Network Wise
-- 4. Hide Business Rules
-- 5. Handle Errors (SP Contain DML)
-- 6. Accept Input And Out Parameteres => Make It More Flexbile

Create Procedure SP_GetStudentById @StdId int

as
Select *
from Student
Where St_Id = @StdId

-- There are two ways to get out

-- 1-
SP_GetStudentById 1
-- 2-
declare @X int = 1
exec HR.SP_GetStudentById @X

alter schema hr
transfer SP_GetStudentById

Delete From Topic

Where Top_Id = 1

create or Alter Proc SP_DeleteTopicById @TopicId int

With Encryption
as
Begin Try
Delete From Topic
Where Top_Id = @TopicId
End Try
Begin Catch
Select 'Error'
End Catch

SP_DeleteTopicById 6

Sp_HelpText 'SP_DeleteTopicById'

create or Alter Procedure SP_SumData1 @X int = 2, @Y varchar(10) = '8'

as
Select @X + @Y

SP_SumData 3,7 -- Passing Parameters by Position

SP_SumData @y=7,@x=3 -- Passing Parameters by name
SP_SumData 6 -- Default Values
SP_SumData -- Default Values

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 20

Create Proc SP_GetStudentByAddress @StdAddress varchar(20)
With Encryption
As
Select St_Id, St_FName, St_Address
From Student
Where St_Address = @StdAddress

SP_GetStudentByAddress 'Alex'

Create Table StudentsWithAddresss

(
StdId int Primary Key,
StdName varchar(20),
StdAddress varchar(20)
)

-- Insert Based On Execute

Insert Into StudentsWithAddresss
exec SP_GetStudentByAddress 'Alex'

---------------------------------------
-- Return Of SP

create or alter proc sp_getStNameAndAgeById @id int , @stName varchar(20) out,@stAge int out
with encryption as
select @stName = St_Fname , @stAge = St_Age
from Student
where St_Id = @id

declare @name varchar(20), @age int

exec sp_getStNameAndAgeById 2 , @name out, @age out
select @name , @age

-- input Output Parameter

create or alter proc sp_getStNameAndAgeByIdV2 @idAge int out, @stName varchar(20) out
with encryption as
select @stName = St_Fname , @idAge = St_Age -- [Output]
from Student
where St_Id = @idAge -- 1 [Input]

declare @id int = 1 , @name varchar(20)

exec sp_getStNameAndAgeById 2 , @name out, @id out
select @id as id , @name as name

-- ===============================================================================
-- Function | view | Procedure
-- select |select [prefer] |insert update delete select
-- select |select [prefer] |no select calling only
-- take parameter |no take parameter |take parameter [prefer]
-- object return table |no return table |object return table [prefer]
-- return one value |just display |return one value and multi value [‫]الداتا حسب‬
-- inside Fun no call Proc |just display |inside proc you can call function
6-

-- ====================================================================
--------------------------------------------------------------------
-- Types of SP (stored procedure)
---- 1. User defined
-- Sp_GetStudentNameById SP_Getdata SP_Sumdata
‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 21
---- 2. Built-In SP
Sp_helptext Sp_Rename
---- 3. Trigger (Special Stored Procedure)
----------- Can't Call
----------- Can't take parameters

--- Types of Triggers (Through Its Level)

---------- Server Level
---------- DB Level
---------- Table Level (Our Interest)
----------------- Actions In Table?(Insert Update Delete) [Events]

---------------------------------(select Truncate) Not Logged In Log File

Create or alter Trigger Tri01

on Student
after insert
as
Select 'Welcome To Route'
update Student set St_FName = 'Ali' where St_Id = 23723
update Student set create_date = CURRENT_TIMESTAMP where St_Id = 23723
-- to test
Insert Into Student(St_Id, St_FName , st_Age)
Values(23723, 'Amr' , 27)

Alter Schema HR
Transfer Student

create Trigger Tri02

on Student
after Update
as
Select GetDate(), SUser_Name()

update Student
set update_date = (Select update_date from inserted)
where St_Id in (Select St_Id from inserted)

-- to run
Update Student
Set St_Address = 'mansora'
Where St_Id = 23723

select * from Student

Drop Trigger HR.Tri02

Create Trigger Tri03

On HR.Student
Instead of Delete
as
Select 'U Can Not Delete From This Table'

-- to test
Delete From HR.Student
Where St_Id = 23723

create or Alter Trigger Tri04

on Department
instead of Delete, Insert, Update

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 22

as
Select 'You Can Not Do Any Operation On This Table ' + SUser_Name()

-- to test
Insert into Department(Dept_Id, Dept_Name)
Values(8232, 'Test')

-- Drop | Disable | Enable Trigger

Drop Trigger Tri04

Alter Table Student

Disable Trigger Tri02

Alter Table Student

Enable Trigger Tri02

----------------------------------------------------
-- When You Write Trigger, You Must Write Its Schema (Except Default [dbo])
-- Trigger Take By Default The Schema Of Its Table In Creation
-- When You Change The Schema Of Table, All Its Triggers Will Follow

Alter trigger HR.tri1

on student
after insert
as
Select 'Welcome To ITI'

--------------------------------------------
--------------------------------------------
-- The Most Important Point Of Trigger
-- 2 Tables: Inserted & Deleted Will Be Created With Each Fire Of Trigger
-- In Case Of Delete: Deleted Table Will Contain Deleted Values
-- In Case Of Insert: Inserted Table Will Contain Inserted Values
-- In Case Of Update: Deleted Table Will Contain Old Values
-- Inserted Table Will Contain New Values

Select * from inserted -- Error (Have No Meaning Without Trigger): Just Created at RunTime
select * from deleted -- Error (Have No Meaning Without Trigger): Just Created at RunTime

-- With Trigger
create trigger tri6
on course
after update
as
Select * from
select * from deleted

-- to test
update course
set Crs_Name='Cloud'
where crs_id=200

Create Trigger Tri06

on Course
Instead OF Delete
as
Select 'U Can Not Delete From This Table : ' + (Select Crs_Name from deleted)

Delete From Course

Where Crs_Id = 900

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 23

create or Alter Trigger HR.Tri07
on HR.Student
Instead OF Delete
as
if Format( GETDATE(), 'dddd') != 'Wednesday'
Delete From HR.Student
Where St_Id in (Select St_Id from deleted)

Delete from HR.Student

Where St_Id = 3242

-- ================================================================
----------------------------------------------------------------
----------------------------- Index -------- -------------------
create clustered index myindex
on student(st_fname) -- Not Valid [Table already has a clustered index on PK]

create nonclustered index myindex

on student(st_fname)

create nonclustered index myindex2

on student(dept_id)

-- Primary Key ---Constraint ---> Clustered Index

-- Unique Key ---Constraint ---> Nonclustered Index

create table test
(
X int primary key,
Y int unique,
Z int unique
)

create unique index i3

on student(st_age)
-- Will Make 2 Things If There is No Repeated Values
-- 1. Make Unique Constraint On St_Age
-- 2. Make Non-Clustered Index On St_Age

-- Clustered Index Vs Nonclustered Index

-- Just One ... Many Up To 999
-- Last Level Is The Actual Data ... PreLast Level Is Pointer To Actual Data
-- Faster ... Slower
-- PK => Clustered ... Unique => Nonclustered

-- How Can I Select The Columns To Make Indexes On It?

-- 1. Analysis
-- 2. Testing (Using SQL Server Profiler and Tuning Advisor)

Alter Schema dbo

Transfer HR.Instructor

Create View IndexedView

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 24

With SchemaBinding
As
Select S.St_Id, S.St_FName, D.Dept_Id, D.Dept_Name
from dbo.Student S, dbo.Department D
Where D.Dept_Id = S.Dept_Id

Create unique clustered Index ii

On IndexedView(St_Id)
-- ===================================================================
------------------------ Delete Vs Truncate -----------------------
Delete From Student

Truncate Table Student

-- =================================================================
-----------------------------------------------------------------
--------------------------- Transaction -------------------------
-- 1. Implicit Transaction (DML Query [Insert, Update, Delete])

Insert Into Student(St_Id, St_Fname)

Values (100, 'Ahmed'), (101, 'Amr')

Update Student
set St_Age = 30
where St_Age = 20

-- 2. Explicit Transaction (Set Of Implicit Transactions)

create table Parent
(
ParentId int primary key
)
create table Child
(
ChildId int primary key,
ParentId_FK int references Parent(ParentId)
)
insert into Parent values(1)
insert into Parent values(2)
insert into Parent values(3)

begin transaction
insert into Child values(1, 1)
insert into Child values(2, 10)
insert into Child values(3, 2)
commit tran

begin transaction
insert into Child values(4, 1)
insert into Child values(5, 10)
insert into Child values(6, 2)
rollback tran

begin try
begin transaction
insert into Child values(7, 1)
save transaction p01
insert into Child values(8, 2)
insert into Child values(9, 10)
insert into Child values(10, 3)

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 25

 commit tran
end try
begin catch
rollback tran p01
end catch
=================================================================
-----------------------------------------------------------------
--------------------------- DCL ---------------------------------
-- [Login] Server (Omar)
-- [User] DB ITI (Omar)
-- [Schema] HR [Department, Instructor]
-- Permissions Grant [select,insert] Deny [delete Update]

Create Schema HR

alter schema HR
transfer [dbo].[Instructor]

alter schema HR
transfer Department
=================================================================
-----------------------------------------------------------------
--------------------------- Backups -----------------------------

---- Backup Types

------- 1. Full
------- 2. Differential
------- 3. Transaction Log

Backup Database ITI

to Disk = 'D:\Route\Cycle 39\01 Database\Session 07\Demos\iti.bak'

 teeeeeeeeeeeestttttttttttttttt
create or alter Trigger Tri011 on Student
after Update as

select 'Done'
update Student
set update_date = (Select update_date from deleted)
, update_user = (Select update_user from deleted)

where St_Id in (Select St_Id from deleted

where update_user = 'DESKTOP-4EFPBS4\Ahmed_Mostafa')

-- to run
Update Student
Set St_Address = 'sss'
Where St_Id = 2

‫برجاء الدعاء لعمي‬ Ahmeed Mostafa - 01155023528 Page 26