DBMS/SQL Tutorial

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


Tutorial No. 13:
Click to WATCH the Series of Videos

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

2 comments:

Fell free to write your query in comment. Your Comments will be fully encouraged.