DB - Day3

Joins

Examples will be provided on this two tables Student Table

Sid	Sname	did
1	ahmed	10
2	khalid	10
3	eman	20
4	omar	NULL

Department Table

Did	Dname
10	SD
20	HR
30	IS
40	Admin

Type Of Joins

Cross Join

Cartesian Product

SELECT Sname,Dname
FROM Student,Department

SELECT Sname,Dname
FROM student CROSS JOIN Department

Sname	Dname
ahmed	SD
khalid	SD
eman	SD
omar	SD
ahmed	HR
khalid	HR
eman	HR
omar	HR
ahmed	IS
khalid	IS
eman	IS
omar	IS
ahmed	Admin
khalid	Admin
eman	Admin
omar	Admin

Inner Join

Equi Join Only Matched Values No null Values from any table FK =PK

SELECT Sname,Dname
FROM Student,Department
WHERE Department.Did =Student.Sid

These two queries are the same

SELECT Sname,Dname
FROM Student S INNER JOIN Department D
ON D.Did =S.Sid

Sname	Dname
ahmed	SD
khalid	SD
eman	HR

Outer Join

Left Outer Join If You want every row from the left table even if there no matched values

SELECT Sname,Dname
FROM Student S LEFT OUTER JOIN Department D
ON S.Did = D.Did

Sname	Dname
ahmed	SD
khalid	SD
eman	HR
Omar	NULL

Right Outer Join If You want every row from the right table even if there no matched values

SELECT Sname,Dname
FROM Student S RIGHT OUTER JOIN Department D
ON S.Did = D.Did

Sname	Dname
ahmed	SD
khalid	SD
eman	HR
NULL	IS
NULL	ADMIN

Full Outer Join If You want every row from the right and left tables even if there no matched values its combination of Left And Right Outer Join

SELECT Sname,Dname
FROM Student S FULL OUTER JOIN Department D
ON S.Did = D.Did

Sname	Dname
ahmed	SD
khalid	SD
eman	HR
omar	NULL
NULL	IS
NULL	ADMIN

Self Join

Unary Relation Primary Key and foreign key is in the same table

eid	ename	superid
1	ahmed	NULL
2	eman	1
3	khalid	1
4	nada	2
5	ali	2

we want a query to know the employee and his supervisor name

SELECT X.ename AS Empname ,Y.ename AS Supername
FROM Employee X ,Employee Y
WHERE Y.eid = X.superid

Empname	Supername
eman	ahmed
khalid	ahmed
nada	eman
ali	eman

Examples on Database

TRY IT YOURSELF Cross Join

use ITI 
SELECT st_name ,dept_name
FROM Student,Department

Inner Join

use ITI 
SELECT st_name ,dept_name
FROM Student S ,Department D
WHERE D.Deopt_Id = S.Dept_Id

Display student name and department for students from alex

use ITI 
SELECT st_name ,dept_name
FROM Student S ,Department D
WHERE D.Deopt_Id = S.Dept_Id
AND St_Address ='alex'

Display student name and all department data for the student

use ITI 
SELECT st_name ,D.*
FROM Student S ,Department D
WHERE D.Deopt_Id = S.Dept_Id

Display student name and department for student for students ages between 20 and 25 then order the result by Department name

use ITI 
SELECT st_name ,dept_name
FROM Student S ,Department D
WHERE D.Deopt_Id = S.Dept_Id 
AND St_age BETWEEN 20 AND 25
ORDER BY Dept_Name

---

Outer Join

Display all Student even it no department match

use ITI 
SELECT st_name ,dept_name
FROM Student S LEFT OUTER JOIN Department D
WHERE D.Deopt_Id = S.Dept_Id

use ITI 
SELECT st_name ,dept_name
FROM Student S Right OUTER JOIN Department D
WHERE D.Deopt_Id = S.Dept_Id

use ITI 
SELECT st_name ,dept_name
FROM Student S FULL OUTER JOIN Department D
WHERE D.Deopt_Id = S.Dept_Id

---

Self Join

Student Name and his Supervisor

SELECT B.St_name AS StudName, A.st_name AS SuperName
FROM Student A, Student B
--(A,PK,Parent,Supervisor) -- (B,FK,Child,Student)
WHERE A.St_Id = B.St_Super

Display Supervisors Name

SELECT DISTINCT A.st_name AS SuperName
FROM Student A, Student B
WHERE A.St_Id = B.St_Super

Display all students even if they have no super

SELECT DISTINCT A.st_name AS SuperName
FROM Student A LEFT OUTER JOIN Student B
WHERE A.St_Id = B.St_Super

---

Joins With More than Two Tables

Display Student name and his courses and grades

SELECT 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

The same output as the last query

SELECT 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

Joining four Tables

SELECT 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
INNER JOIN 
	Department D
ON D.Dept_Id = S.Dept_Id

---

Join With DML

Joining two table and update grades based on condition in another table

UPDATE Stud_Course
	SET grade +=10
FROM Student s ,Stud_Course SC
WHERE S.St_Id = sc.ST_Id AND St_address ='alex'

---

Built In Functions

ISNull

Replace null values with other value

SELECT isnull(St_fname,'student has no name')
FROM Student

Coalesce

Replace null values with any of multiple replacement values if first name is null display last name and if its null display address and if null display "Student has no name"

SELECT coalesce(St_fname,st_lname,st_address,
				'student has no name')
FROM Student

---

Autoincrement ID

Auto increment For Id

CREATE TABLE Test2(
id INT PRIMARY KEY IDENTITY (1,1),
ename VARCHAR(20)
)

INSERT INTO Test2 VALUES ('ahmed')

reset identity

dbcc check_ident('test',reseed,0)

CREATE TABLE Test3(
id INT IDENTITY ,
ssn INT PRIMARY KEY,
ename VARCHAR(20)
)

INSERT INTO Test3 VALUES (1234,'ahmed')
INSERT INTO Test3 VALUES (4564,'mohamed')
INSERT INTO Test3 VALUES (1234,'salem')

---

Like Statement

SELECT * 
FROM Student
WHERE St_fname ='ahmed'

---

• _ is one character

• % is Zero or more character Names Of student there name starts with "a"

SELECT * 
FROM Student
WHERE St_fname LIKE 'a%'

Names Of student there name ends with "a"

SELECT * 
FROM Student
WHERE St_fname LIKE '%a'

Some Patterns

• a%h starts with a and ends with h

• %d_ the second last char is d

• ___ three chars word

• _m__ four chars word the second char is m

• ___% at least four char word

• ahm% word starts with ahm

• [ahm]% word starts with a or h or m

• [a-h]% word starts with range from a to h

• [(am)(gh)]% word starts with "am" or "gh"

• %[%] string that ends with %

• %[_]% words contains __

---

Database Integrity

	Domain Integrity	Entity Integrity	refrential Integrity
	Range of values	Uniqueness	Relationship
DB Constraints	Data Types	Primary Key constraint
	(tiny int => 1byte)	unique constraint => multi columns with unique constraints and allow one NULL	Foreign Key Constraints
	Default values 'cairo'		Custom Constraints like Stored Procedures
	allow NULL or NOT NULL
	Check(Constraint) when Creating tables
-----------	---------------------	----------------------	-----------------
DB Objects
	Rule	Index	Trigger
	Trigger	Trigger

Types of constraints

• Primary Key constraint

• unique constraint

• Check(Constraint)

• Foreign Key Constraints

• Custom Constraints like Stored Procedures

---

Constraints

CREATE TABLE Employee(
eid INT PRIMARY KEY IDENTITY,
ename VARCHAR(20),
eadd VARCHAR(20) DEFAULT 'cairo',
hiredate DATE DEFAULT getdate(),
sal INT ,
overtime INT,
--computed and saved
netsal AS(isnull(sal,0)+isnull(overtime,0)) PRESISTED, 
bd DATE,
--computed
age  AS (YEAR(getdate())-Year(bd)),
gender VARCHAR(1),
hour_rate INT NOT NULL,
dnum INT,
)

CREATE TABLE Employee(
eid INT IDENTITY,
ename VARCHAR(20),
eadd VARCHAR(20) DEFAULT 'cairo',
hiredate DATE DEFAULT getdate(),
sal INT ,
overtime INT,
--computed and saved
netsal AS(isnull(sal,0)+isnull(overtime,0)) PRESISTED, 
bd DATE,
--computed
age AS (YEAR(getdate())-Year(bd)),
gender VARCHAR(1),
hour_rate INT NOT NULL,
dnum INT,
--FOR composite primary key
CONSTRAINT C1 PRIMARY KEY(eid,ename),
CONSTRAINT C2 UNIQUE(sal),
CONSTRAINT C3 CHECK(sal>1000)
CONSTRAINT C4 CHECK(overtime BETWEEN 100 AND 500)
CONSTRAINT C5 CHECK (gender IN('M','F'))
CONSTRAINT C6 FOREIGN KEY(dnum) REFERENCES depts(did)
	ON DELETE SET NULL ON UPDATE CASCADE
)

---