Hi, Well come to Fahad Hussain Free Computer Education Here you can learn Complete computer Science, IT related course absolutely Free! As we know SQL is one of the powerful DataBase Language (query base). In the series of Video you will learn DBMS from Scratch using SQL... The Whole course combine the concept of theory and Practical! for Further Assistance and code visit: https://fahadhussaincs.blogspot.com/ For Complete course YouTube Channel: https://www.youtube.com/channel/UCapJ...
Tutorial No. 01:
Click to WATCH the Series of Videos
Introduction to DBMS explained!
Tutorial No. 02 & 03:
Click to WATCH the Series of Videos
-- Creation of DB
create database FahadHussain
--How can we use DB
use FahadHussain
--Creation of Table in DB
create table fahadtbl
(ID int, Name nvarchar(50), Salary int, Addresss nvarchar(100))
--Select the table all record(*)
select * from fahadtbl
--Add/insert record into table
insert into fahadtbl values(1,'Ali',55000,'North Karachi')
insert into fahadtbl values(2,'Lara',75000,'North Karachi')
insert into fahadtbl values(3,'John',65000,'North Karachi')
insert into fahadtbl values(4,'Bob',59000,'North Karachi')
--Delete all record from table (where Clause)
delete from fahadtbl where ID=2
/*
Multi
line
Comments
*/
Tutorial No. 04:
Click to WATCH the Series of Videos
--insert another query
insert into fahadtbl(ID,Name) values(5,'Eve')
insert into fahadtbl(ID, Salary, Addresss) values(6,45000,'Karachi')
-- Update Query
update fahadtbl
set Name = 'Raheel'
where ID =1
update fahadtbl
set Name = 'Adam'
where Salary =75000
select * from fahadtbl
Tutorial No. 05:
Click to WATCH the Series of Videos
--Where Clause
select * from fahadtbl where ID=1
select ID, Name from fahadtbl where ID=1
select ID, Name,Salary from fahadtbl where ID=1
--Operators ( AND, NOT ,OR) WITH WHERE CLAUSE
select * from fahadtbl where Name= 'Raheel' AND Salary =55000
select * from fahadtbl where Name= 'Rahel' AND Salary =55000
select * from fahadtbl where Name= 'Rael' OR Salary =55000
select * from fahadtbl where Name= 'Rahel' OR Salary =5000
select * from fahadtbl where NOT Name='Raheel'
-- Order by (sorting) asc/desc
select * from fahadtbl
order by Salary asc
Tutorial No. 06:
Click to WATCH the Series of Videos
--is null / is not null
select * from fahadtbl
where Salary is null
select * from fahadtbl
where Salary is not null
select * from fahadtbl
where (Salary is null OR Name is not null)
Tutorial No. 07:
Click to WATCH the Series of Videos
-- Alter (add)
alter table fahadtbl
add UC_Number nvarchar(50)
insert into fahadtbl values(7,'John',69000,'North Karachi','UC-5')
--Alter (data types Change)
alter table fahadtbl
alter column UC_Number varchar(50)
-- Alter drop (delete)
alter table fahadtbl
drop column UC_Number
Tutorial No. 08:
Click to WATCH the Series of Videos
--Data types in Sql
--create table fahadtbl
--(ID tinyint, Name char(50), Salary int, Addresss nvarchar(100))
declare @aa tinyint
set @aa = 255
select @aa
declare @bb smallint
set @bb = -400
select @bb
declare @cc int
set @cc = -5260
select @cc
declare @dd bigint
set @dd = -5260
select @dd
declare @aa bit
set @aa = null
select @aa
declare @aa decimal(7,2)
set @aa = 255.121239
select @aa
declare @aa numeric(7,2)
set @aa = 255.129
select @aa
declare @aa nvarchar(77)
set @aa = N'????'
select @aa
declare @aa char(77)
set @aa = 'asdfasd'
select @aa
declare @aa nchar(77)
set @aa = '????'
select @aa
declare @aa varchar(77)
set @aa = 'asdf asdf asdf '
select @aa
declare @aa nvarchar(77)
set @aa = N'????'
select @aa
declare @aa money
set @aa = 25678567856785.129
select @aa
declare @aa smallmoney
set @aa = 214112
select @aa
declare @aa Date
set @aa = getDate()
select @aa
declare @aa Date
set @aa = '12-12-2009'
select @aa
declare @aa time
set @aa = '12:12:12'
select @aa
declare @ab float
set @ab = 12.12123123123
select @ab
declare @ab real
set @ab = 12.12123123123
select @ab
Tutorial No. 09:
Click to WATCH the Series of Videos
--SQL constraints are used to specify rules for data in a table.
--PK, identity (auto_increment), FK, unique, not null, default,
create table aa
(ID int primary key ,
Name nvarchar(50) Not null,
RollNo int unique,
Date date default getdate(),
Serial_Number int identity(1,1))
select * from aa
insert into aa values(1, 'ali',123,'11-11-2000')
insert into aa(ID,Name,RollNo) values(2, 'Rashid',456)
Tutorial No. 10:
Click to WATCH the Series of Videos
--Operator in SQL
--Arithmetic operators
select 10 + 10
select 10 - 10
select 10 * 10
select 10 / 10
select 10 % 10
--SQL Comparison Operators
declare @a int
set @a =10
select @a where 10 <> 20
--Command Operator a=a+10 (a+=10)
declare @b int
set @b = 10
set @b +=10 -- = 10+10
select @b
--Bitwise operator
select 1 & 2
select 1 | 2
select 1 ^ 2
select ~2
Tutorial No. 11:
Click to WATCH the Series of Videos
-- Max, Min and top function of SQL
select * from fahadtbl
select max(Salary) from fahadtbl
select min(Salary) from fahadtbl
select * from fahadtbl
select TOP 3 Salary from fahadtbl
Tutorial No. 12:
Click to WATCH the Series of Videos
--AGGREGATE FUNCTION OF SQL
select * from fahadtbl
select count(Name) from fahadtbl
select avg(Salary) from fahadtbl
select SUM(Salary) from fahadtbl
-- SubQuery
select Salary from fahadtbl --outer query
where Salary >= (select max(salary) from fahadtbl) --inner query
select max(salary) from fahadtbl
where Salary < (select max(salary) from fahadtbl)
Tutorial No. 14:
Click to WATCH the Series of Videos
Create table tblDepartment
(
ID int primary key,
DepartmentName nvarchar(50),
Location nvarchar(50),
DepartmentHead nvarchar(50)
)
Insert into tblDepartment values (1, 'FINANCE', 'Karachi', 'Haris')
Insert into tblDepartment values (2, 'PRODUCTION', 'Lahore', 'Laraib')
Insert into tblDepartment values (3, 'HR', 'Multan', 'Junaid')
Insert into tblDepartment values (4, 'UNKNOWN', 'Sakkar', 'Ahmed')
Create table tblEmployee
(
ID int primary key,
Name nvarchar(50),
Gender nvarchar(50),
Salary int,
--DepartmentId int foreign key references tblDepartment(Id)
)
Insert into tblEmployee values (1, 'Tom', 'Male', 4000, 1)
Insert into tblEmployee values (2, 'Pam', 'Female', 3000, 3)
Insert into tblEmployee values (3, 'John', 'Male', 3500, 1)
Insert into tblEmployee values (4, 'Sam', 'Male', 4500, 2)
Insert into tblEmployee values (5, 'Todd', 'Male', 2800, 2)
Insert into tblEmployee values (6, 'Ben', 'Male', 7000, 1)
Insert into tblEmployee values (7, 'Sara', 'Female', 4800, 3)
Insert into tblEmployee values (8, 'Valarie', 'Female', 5500, 1)
Insert into tblEmployee values (9, 'James', 'Male', 6500, NULL)
--JOIN OR INNER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
--LEFT JOIN OR LEFT OUTER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
--RIGHT JOIN OR RIGHT OUTER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
--FULL JOIN OR FULL OUTER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
Tutorial No. 15:
Click to WATCH the Series of Videos
//SELF JOIN:
CREATE TABLE employee123
(emp_id int primary key,
emp_name varchar(20) NULL,
dt_of_join date NULL,
emp_supv int NULL,
CONSTRAINT emp_supv FOREIGN KEY(emp_supv) REFERENCES employee12(emp_id))
Insert into employee12 values (1, 'Tom', '1-JAN-2000', NULL)
Insert into employee12 values (2, 'Pam', '1-FEB-2000', 1)
Insert into employee12 values (3, 'John','1-DEC-2000', 2)
Insert into employee12 values (4, 'Sam','1-MAR-2000', 2)
Insert into employee12 values (5, 'Todd','1-JUN-2000', 1)
Insert into employee12 values (6, 'Ben','1-JUL-2000' , 4)
Insert into employee12 values (7, 'Sara','1-MAY-2000', 5)
select * from employee12
SELECT a.emp_id AS "Emp_ID",a.emp_name AS "Employee Name",
b.emp_id AS "Supervisor ID",b.emp_name AS "Supervisor Name"
FROM employee12 a, employee12 b
WHERE a.emp_supv = b.emp_id;
Tutorial No. 16:
Click to WATCH the Series of Videos
create database UnionDB
use UnionDB
create table Emp
(ID int primary key identity(1,1), Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Emp values('Laraib',55000,'pak')
insert into Emp values('Haris',53000,'ind')
insert into Emp values('Jazz',7500,'agh')
insert into Emp values('Hamjoo',85000,'china')
insert into Emp values('Eve',15000,'Bag')
insert into Emp values('Kajul',7000,'Bag')
insert into Emp values('Hamza',95000,'china')
insert into Emp values('lara',47000,'Bag')
create table Tem
(ID int primary key identity(1,1), Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Tem values('Muz',55000,'pak')
insert into Tem values('Jazz',55000,'ind')
insert into Tem values('Telenor',55000,'agh')
insert into Tem values('Kjo',55000,'china')
insert into Tem values('Lolo',55000,'Bag')
select * from Emp
union
select * from Tem
select * from Emp
union all
select * from Tem
select * from Emp
select COUNT(ID), country
from Emp
group by Country
Tutorial No. 17:
Click to WATCH the Series of Videos
select COUNT(ID), country
from Emp
group by Country
having COUNT(ID) >=2
Tutorial No. 18:
Click to WATCH the Series of Videos
declare @ID int
set @ID =5
select
case @ID
when 1 then 'Value is 1'
when 2 then'Value is 2'
when 3 then 'Value is 3'
else 'None'
end
select * from Emp
select
CASE
when Salary >=2000 and Salary <=10000 then 'Manager'
when Salary >=11000 and Salary <=20000 then 'Senior Manager'
else 'Director'
End as Designation from Emp
Tutorial No. 19:
Click to WATCH the Series of Videos
create table Emp1
(ID int , Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Emp1 values(1,'Laraib',55000,'pak')
insert into Emp1 values(2,'Haris',53000,'ind')
insert into Emp1 values(3,'Jazz',7500,'agh')
insert into Emp1 values(4,'Hamjoo',85000,'china')
insert into Emp1 values(5,'Eve',15000,'Bag')
insert into Emp1 values(6,'Kajul',7000,'Bag')
insert into Emp1 values(7,'Hamza',95000,'china')
insert into Emp1 values(8,'lara',47000,'Bag')
create table Tem1
(ID int, Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Tem1 values(1,'Muz',55000,'pak')
insert into Tem1 values(2,'Jazz',55000,'ind')
insert into Tem1 values(3,'Telenor',55000,'agh')
insert into Tem1 values(4,'Kjo',55000,'china')
insert into Tem1 values(5,'Lolo',55000,'Bag')
select * from Emp1
select * from Tem1
insert into Tem1 select * from Emp1
insert into Tem1(__,__,_) select * from Emp1
insert into Tem1(ID,Name) select * from Emp1 where ID=1
Tutorial No. 20:
Click to WATCH the Series of Videos
--Department Table
CREATE TABLE Department
(
ID int PRIMARY KEY, --primary key
Name nvarchar (50) NOT NULL,
Address nvarchar (50) NOT NULL
)
insert into Department values(1,'IT','Karachi')
insert into Department values(2,'HR','Lahore')
insert into Department values(3,'Marketing','Multan')
--Student Table
CREATE TABLE Student
(
ID int PRIMARY KEY, --primary key
RollNo varchar(10) NOT NULL,
Name nvarchar(50) NOT NULL,
EnrollNo nvarchar(50) UNIQUE, --unique key
Address nvarchar(200) NOT NULL,
DeptID int FOREIGN KEY REFERENCES Department(ID) --foreign key
)
insert into Student values(1,123,'Rashid','Student159','North Karachi',2)
insert into Student values(2,148,'Ali','Student1239','North',1)
insert into Student values(3,423,'Sara','Student1439','North Nazi',3)
CREATE TABLE userdata (
userid INT,
userdataid INT,
info char(200),
primary key (userid, userdataid)
);
select * from userdata
insert into userdata values(1,2,'Yes Sold')
Tutorial No. 21:
Click to WATCH the Series of Videos
create database Cart1
use Cart1
-- 1 to 1 relationship
CREATE TABLE Person
(
Pk_Person_Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);
CREATE TABLE PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);
INSERT INTO Person VALUES ('Niraj','v.a@emails.com');
INSERT INTO Person VALUES ('Vishwanath','v.v@emails.com');
INSERT INTO Person VALUES ('Chetan','c.v@emails.com');
INSERT INTO PassportDetails VALUES (101, 'C3031R33', 1);
INSERT INTO PassportDetails VALUES (102, 'VRDK5695', 2);
INSERT INTO PassportDetails VALUES (103, 'A4DEK33D', 3);
INSERT INTO PassportDetails VALUES (104, 'A4DEK33D', 3);
SELECT * FROM Person
SELECT * FROM PassportDetails;
-- 1 to many relationship
CREATE TABLE Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(255),
ISBN VARCHAR(255)
);
CREATE TABLE Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName VARCHAR(255),
MobileNo CHAR(10),
Fk_Book_Id INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);
INSERT INTO Book VALUES (1, 'Let is Snow', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'Three Cups of Tea','ISBN638242');
INSERT INTO Author VALUES(100,'John Green','30303',1);
INSERT INTO Author VALUES(101,'Maureen Johnson','4343',1);
INSERT INTO Author VALUES(102,'Lauren Myracle','76665',1);
INSERT INTO Author VALUES(103,'Greg Mortenson','6434',2);
INSERT INTO Author VALUES(104,'David Oliver Relin','72322',2);
SELECT * FROM Book;
SELECT * FROM Author;
-- Many to 1 relationship
inverse to many to 1...
-- Many to Manay relationship
CREATE TABLE Class(
ID int PRIMARY KEY,
Title varchar(30),
Instructor varchar(30),
Day varchar(15),
Time varchar(10)
);
insert into Class values(1,'Class 1','Rizwan','Tuesday','10:10:10')
insert into Class values(2,'Class 2','Haris','Firday','12:10:10')
insert into Class values(3,'Class 3','Rajo','Saturday','09:10:10')
CREATE TABLE Student(
ID int PRIMARY KEY,
Name varchar(35),
Major varchar(35),
ClassYear varchar(10),
Status varchar(10)
);
insert into Student values(1,'Lara','CS','2000','yes')
insert into Student values(2,'Junaid','CS','2001','yes')
insert into Student values(3,'Aslam','CS','2002','No')
CREATE TABLE ClassStudentRelation(
ID int NOT NULL,
StudentAddress varchar(14) NOT NULL,
ClassID int foreign key references Class(ID),
StudentID int foreign key references Student(ID)
);
insert into ClassStudentRelation values(1,'Karachi',1,2)
insert into ClassStudentRelation values(2,'Lahore',2,3)
insert into ClassStudentRelation values(3,'Multan',1,1)
Tutorial No. 22:
Click to WATCH the Series of Videos
--Store Procedure in SQL
select * from Emp
Create procedure SPemp
as
begin
select EName, job from Emp where EmpNo=1001
end
exec SPemp
execute SPemp
drop procedure SPemp
Create procedure SPempPara
@EmpNo int,
@Ename nvarchar(50)
as
begin
select * from Emp where EmpNo=@EmpNo and Ename=@Ename
end
exec SPempPara 1001,'Husaam'
Tutorial No. 01:
Click to WATCH the Series of Videos
Introduction to DBMS explained!
Tutorial No. 02 & 03:
Click to WATCH the Series of Videos
-- Creation of DB
create database FahadHussain
--How can we use DB
use FahadHussain
--Creation of Table in DB
create table fahadtbl
(ID int, Name nvarchar(50), Salary int, Addresss nvarchar(100))
--Select the table all record(*)
select * from fahadtbl
--Add/insert record into table
insert into fahadtbl values(1,'Ali',55000,'North Karachi')
insert into fahadtbl values(2,'Lara',75000,'North Karachi')
insert into fahadtbl values(3,'John',65000,'North Karachi')
insert into fahadtbl values(4,'Bob',59000,'North Karachi')
--Delete all record from table (where Clause)
delete from fahadtbl where ID=2
/*
Multi
line
Comments
*/
Tutorial No. 04:
Click to WATCH the Series of Videos
--insert another query
insert into fahadtbl(ID,Name) values(5,'Eve')
insert into fahadtbl(ID, Salary, Addresss) values(6,45000,'Karachi')
-- Update Query
update fahadtbl
set Name = 'Raheel'
where ID =1
update fahadtbl
set Name = 'Adam'
where Salary =75000
select * from fahadtbl
Tutorial No. 05:
Click to WATCH the Series of Videos
--Where Clause
select * from fahadtbl where ID=1
select ID, Name from fahadtbl where ID=1
select ID, Name,Salary from fahadtbl where ID=1
--Operators ( AND, NOT ,OR) WITH WHERE CLAUSE
select * from fahadtbl where Name= 'Raheel' AND Salary =55000
select * from fahadtbl where Name= 'Rahel' AND Salary =55000
select * from fahadtbl where Name= 'Rael' OR Salary =55000
select * from fahadtbl where Name= 'Rahel' OR Salary =5000
select * from fahadtbl where NOT Name='Raheel'
-- Order by (sorting) asc/desc
select * from fahadtbl
order by Salary asc
Click to WATCH the Series of Videos
--is null / is not null
select * from fahadtbl
where Salary is null
select * from fahadtbl
where Salary is not null
select * from fahadtbl
where (Salary is null OR Name is not null)
Tutorial No. 07:
Click to WATCH the Series of Videos
-- Alter (add)
alter table fahadtbl
add UC_Number nvarchar(50)
insert into fahadtbl values(7,'John',69000,'North Karachi','UC-5')
--Alter (data types Change)
alter table fahadtbl
alter column UC_Number varchar(50)
-- Alter drop (delete)
alter table fahadtbl
drop column UC_Number
Tutorial No. 08:
Click to WATCH the Series of Videos
--create table fahadtbl
--(ID tinyint, Name char(50), Salary int, Addresss nvarchar(100))
declare @aa tinyint
set @aa = 255
select @aa
declare @bb smallint
set @bb = -400
select @bb
declare @cc int
set @cc = -5260
select @cc
declare @dd bigint
set @dd = -5260
select @dd
declare @aa bit
set @aa = null
select @aa
declare @aa decimal(7,2)
set @aa = 255.121239
select @aa
declare @aa numeric(7,2)
set @aa = 255.129
select @aa
declare @aa nvarchar(77)
set @aa = N'????'
select @aa
declare @aa char(77)
set @aa = 'asdfasd'
select @aa
declare @aa nchar(77)
set @aa = '????'
select @aa
declare @aa varchar(77)
set @aa = 'asdf asdf asdf '
select @aa
declare @aa nvarchar(77)
set @aa = N'????'
select @aa
declare @aa money
set @aa = 25678567856785.129
select @aa
declare @aa smallmoney
set @aa = 214112
select @aa
declare @aa Date
set @aa = getDate()
select @aa
declare @aa Date
set @aa = '12-12-2009'
select @aa
declare @aa time
set @aa = '12:12:12'
select @aa
declare @ab float
set @ab = 12.12123123123
select @ab
declare @ab real
set @ab = 12.12123123123
select @ab
Tutorial No. 09:
Click to WATCH the Series of Videos
--SQL constraints are used to specify rules for data in a table.
--PK, identity (auto_increment), FK, unique, not null, default,
create table aa
(ID int primary key ,
Name nvarchar(50) Not null,
RollNo int unique,
Date date default getdate(),
Serial_Number int identity(1,1))
select * from aa
insert into aa values(1, 'ali',123,'11-11-2000')
insert into aa(ID,Name,RollNo) values(2, 'Rashid',456)
Tutorial No. 10:
Click to WATCH the Series of Videos
--Operator in SQL
--Arithmetic operators
select 10 + 10
select 10 - 10
select 10 * 10
select 10 / 10
select 10 % 10
--SQL Comparison Operators
declare @a int
set @a =10
select @a where 10 <> 20
--Command Operator a=a+10 (a+=10)
declare @b int
set @b = 10
set @b +=10 -- = 10+10
select @b
--Bitwise operator
select 1 & 2
select 1 | 2
select 1 ^ 2
select ~2
Tutorial No. 11:
Click to WATCH the Series of Videos
-- Max, Min and top function of SQL
select * from fahadtbl
select max(Salary) from fahadtbl
select min(Salary) from fahadtbl
select * from fahadtbl
select TOP 3 Salary from fahadtbl
Tutorial No. 12:
Click to WATCH the Series of Videos
--AGGREGATE FUNCTION OF SQL
select * from fahadtbl
select count(Name) from fahadtbl
select avg(Salary) from fahadtbl
select SUM(Salary) from fahadtbl
select Salary from fahadtbl --outer query
where Salary >= (select max(salary) from fahadtbl) --inner query
select max(salary) from fahadtbl
where Salary < (select max(salary) from fahadtbl)
Tutorial No. 14:
Click to WATCH the Series of Videos
Create table tblDepartment
(
ID int primary key,
DepartmentName nvarchar(50),
Location nvarchar(50),
DepartmentHead nvarchar(50)
)
Insert into tblDepartment values (1, 'FINANCE', 'Karachi', 'Haris')
Insert into tblDepartment values (2, 'PRODUCTION', 'Lahore', 'Laraib')
Insert into tblDepartment values (3, 'HR', 'Multan', 'Junaid')
Insert into tblDepartment values (4, 'UNKNOWN', 'Sakkar', 'Ahmed')
Create table tblEmployee
(
ID int primary key,
Name nvarchar(50),
Gender nvarchar(50),
Salary int,
--DepartmentId int foreign key references tblDepartment(Id)
)
Insert into tblEmployee values (1, 'Tom', 'Male', 4000, 1)
Insert into tblEmployee values (2, 'Pam', 'Female', 3000, 3)
Insert into tblEmployee values (3, 'John', 'Male', 3500, 1)
Insert into tblEmployee values (4, 'Sam', 'Male', 4500, 2)
Insert into tblEmployee values (5, 'Todd', 'Male', 2800, 2)
Insert into tblEmployee values (6, 'Ben', 'Male', 7000, 1)
Insert into tblEmployee values (7, 'Sara', 'Female', 4800, 3)
Insert into tblEmployee values (8, 'Valarie', 'Female', 5500, 1)
Insert into tblEmployee values (9, 'James', 'Male', 6500, NULL)
--JOIN OR INNER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
--LEFT JOIN OR LEFT OUTER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
--RIGHT JOIN OR RIGHT OUTER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
--FULL JOIN OR FULL OUTER JOIN
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id
Tutorial No. 15:
Click to WATCH the Series of Videos
//SELF JOIN:
CREATE TABLE employee123
(emp_id int primary key,
emp_name varchar(20) NULL,
dt_of_join date NULL,
emp_supv int NULL,
CONSTRAINT emp_supv FOREIGN KEY(emp_supv) REFERENCES employee12(emp_id))
Insert into employee12 values (1, 'Tom', '1-JAN-2000', NULL)
Insert into employee12 values (2, 'Pam', '1-FEB-2000', 1)
Insert into employee12 values (3, 'John','1-DEC-2000', 2)
Insert into employee12 values (4, 'Sam','1-MAR-2000', 2)
Insert into employee12 values (5, 'Todd','1-JUN-2000', 1)
Insert into employee12 values (6, 'Ben','1-JUL-2000' , 4)
Insert into employee12 values (7, 'Sara','1-MAY-2000', 5)
select * from employee12
SELECT a.emp_id AS "Emp_ID",a.emp_name AS "Employee Name",
b.emp_id AS "Supervisor ID",b.emp_name AS "Supervisor Name"
FROM employee12 a, employee12 b
WHERE a.emp_supv = b.emp_id;
Tutorial No. 16:
Click to WATCH the Series of Videos
create database UnionDB
use UnionDB
create table Emp
(ID int primary key identity(1,1), Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Emp values('Laraib',55000,'pak')
insert into Emp values('Haris',53000,'ind')
insert into Emp values('Jazz',7500,'agh')
insert into Emp values('Hamjoo',85000,'china')
insert into Emp values('Eve',15000,'Bag')
insert into Emp values('Kajul',7000,'Bag')
insert into Emp values('Hamza',95000,'china')
insert into Emp values('lara',47000,'Bag')
create table Tem
(ID int primary key identity(1,1), Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Tem values('Muz',55000,'pak')
insert into Tem values('Jazz',55000,'ind')
insert into Tem values('Telenor',55000,'agh')
insert into Tem values('Kjo',55000,'china')
insert into Tem values('Lolo',55000,'Bag')
select * from Emp
union
select * from Tem
select * from Emp
union all
select * from Tem
select * from Emp
select COUNT(ID), country
from Emp
group by Country
Tutorial No. 17:
Click to WATCH the Series of Videos
select COUNT(ID), country
from Emp
group by Country
having COUNT(ID) >=2
Tutorial No. 18:
Click to WATCH the Series of Videos
declare @ID int
set @ID =5
select
case @ID
when 1 then 'Value is 1'
when 2 then'Value is 2'
when 3 then 'Value is 3'
else 'None'
end
select * from Emp
select
CASE
when Salary >=2000 and Salary <=10000 then 'Manager'
when Salary >=11000 and Salary <=20000 then 'Senior Manager'
else 'Director'
End as Designation from Emp
Tutorial No. 19:
Click to WATCH the Series of Videos
create table Emp1
(ID int , Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Emp1 values(1,'Laraib',55000,'pak')
insert into Emp1 values(2,'Haris',53000,'ind')
insert into Emp1 values(3,'Jazz',7500,'agh')
insert into Emp1 values(4,'Hamjoo',85000,'china')
insert into Emp1 values(5,'Eve',15000,'Bag')
insert into Emp1 values(6,'Kajul',7000,'Bag')
insert into Emp1 values(7,'Hamza',95000,'china')
insert into Emp1 values(8,'lara',47000,'Bag')
create table Tem1
(ID int, Name nvarchar(50),
Salary int, Country nvarchar(50))
insert into Tem1 values(1,'Muz',55000,'pak')
insert into Tem1 values(2,'Jazz',55000,'ind')
insert into Tem1 values(3,'Telenor',55000,'agh')
insert into Tem1 values(4,'Kjo',55000,'china')
insert into Tem1 values(5,'Lolo',55000,'Bag')
select * from Emp1
select * from Tem1
insert into Tem1 select * from Emp1
insert into Tem1(__,__,_) select * from Emp1
insert into Tem1(ID,Name) select * from Emp1 where ID=1
Click to WATCH the Series of Videos
--Department Table
CREATE TABLE Department
(
ID int PRIMARY KEY, --primary key
Name nvarchar (50) NOT NULL,
Address nvarchar (50) NOT NULL
)
insert into Department values(1,'IT','Karachi')
insert into Department values(2,'HR','Lahore')
insert into Department values(3,'Marketing','Multan')
--Student Table
CREATE TABLE Student
(
ID int PRIMARY KEY, --primary key
RollNo varchar(10) NOT NULL,
Name nvarchar(50) NOT NULL,
EnrollNo nvarchar(50) UNIQUE, --unique key
Address nvarchar(200) NOT NULL,
DeptID int FOREIGN KEY REFERENCES Department(ID) --foreign key
)
insert into Student values(1,123,'Rashid','Student159','North Karachi',2)
insert into Student values(2,148,'Ali','Student1239','North',1)
insert into Student values(3,423,'Sara','Student1439','North Nazi',3)
CREATE TABLE userdata (
userid INT,
userdataid INT,
info char(200),
primary key (userid, userdataid)
);
select * from userdata
insert into userdata values(1,2,'Yes Sold')
Tutorial No. 21:
Click to WATCH the Series of Videos
create database Cart1
use Cart1
-- 1 to 1 relationship
CREATE TABLE Person
(
Pk_Person_Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);
CREATE TABLE PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);
INSERT INTO Person VALUES ('Niraj','v.a@emails.com');
INSERT INTO Person VALUES ('Vishwanath','v.v@emails.com');
INSERT INTO Person VALUES ('Chetan','c.v@emails.com');
INSERT INTO PassportDetails VALUES (101, 'C3031R33', 1);
INSERT INTO PassportDetails VALUES (102, 'VRDK5695', 2);
INSERT INTO PassportDetails VALUES (103, 'A4DEK33D', 3);
INSERT INTO PassportDetails VALUES (104, 'A4DEK33D', 3);
SELECT * FROM Person
SELECT * FROM PassportDetails;
-- 1 to many relationship
CREATE TABLE Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(255),
ISBN VARCHAR(255)
);
CREATE TABLE Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName VARCHAR(255),
MobileNo CHAR(10),
Fk_Book_Id INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);
INSERT INTO Book VALUES (1, 'Let is Snow', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'Three Cups of Tea','ISBN638242');
INSERT INTO Author VALUES(100,'John Green','30303',1);
INSERT INTO Author VALUES(101,'Maureen Johnson','4343',1);
INSERT INTO Author VALUES(102,'Lauren Myracle','76665',1);
INSERT INTO Author VALUES(103,'Greg Mortenson','6434',2);
INSERT INTO Author VALUES(104,'David Oliver Relin','72322',2);
SELECT * FROM Book;
SELECT * FROM Author;
-- Many to 1 relationship
inverse to many to 1...
-- Many to Manay relationship
CREATE TABLE Class(
ID int PRIMARY KEY,
Title varchar(30),
Instructor varchar(30),
Day varchar(15),
Time varchar(10)
);
insert into Class values(1,'Class 1','Rizwan','Tuesday','10:10:10')
insert into Class values(2,'Class 2','Haris','Firday','12:10:10')
insert into Class values(3,'Class 3','Rajo','Saturday','09:10:10')
CREATE TABLE Student(
ID int PRIMARY KEY,
Name varchar(35),
Major varchar(35),
ClassYear varchar(10),
Status varchar(10)
);
insert into Student values(1,'Lara','CS','2000','yes')
insert into Student values(2,'Junaid','CS','2001','yes')
insert into Student values(3,'Aslam','CS','2002','No')
CREATE TABLE ClassStudentRelation(
ID int NOT NULL,
StudentAddress varchar(14) NOT NULL,
ClassID int foreign key references Class(ID),
StudentID int foreign key references Student(ID)
);
insert into ClassStudentRelation values(1,'Karachi',1,2)
insert into ClassStudentRelation values(2,'Lahore',2,3)
insert into ClassStudentRelation values(3,'Multan',1,1)
Tutorial No. 22:
Click to WATCH the Series of Videos
--Store Procedure in SQL
select * from Emp
Create procedure SPemp
as
begin
select EName, job from Emp where EmpNo=1001
end
exec SPemp
execute SPemp
drop procedure SPemp
Create procedure SPempPara
@EmpNo int,
@Ename nvarchar(50)
as
begin
select * from Emp where EmpNo=@EmpNo and Ename=@Ename
end
exec SPempPara 1001,'Husaam'
its better if you add a little bit explanation with every query
ReplyDeleteSir query m boht errors hain
ReplyDelete