create database schoolmanagementsystem
use schoolmanagementsystem
create table Users
(
ID int primary key,
Username varchar(22) not null,
Password varchar(22) not null
);
create table Area
(
ID int primary key,
Name varchar(25) not null
);
create table Subject
(
ID int primary key,
Name varchar(25) not null,
Abbreviation varchar(25) not null,
IDArea integer references Area(ID)
);
create table Level
(
ID integer primary key,
Name varchar(25) not null,
Principle varchar(25) not null
);
create table Grade
(
ID integer primary key IDENTITY,
Name varchar(25) not null,
IDLevel integer references Level(ID),
Observation int not null
);
create table StaffType
(
ID integer primary key,
Name varchar(25) not null
);
create table Staff
(
ID integer primary key,
IDStaffType int not null foreign key references StaffType(ID),
Name varchar(25) not null,
LastNameFather varchar(25) not null,
LastNameMother varchar(25) not null,
DateOfBirth varchar(25) not null,
PlaceOfBirth int not null,
Sex varchar(10) not null,
MobilePhone int not null,
Address varchar(30),
FatherName varchar(25) not null,
MotherName varchar(25) not null,
FatherContact int not null,
FatherPlaceOfWork varchar(30) not null,
MotherPlaceOfWork varchar(30) not null,
Salary int not null
);
create table GradeParalelo
(
ID integer primary key,
IDGrade integer references Grade(ID),
IDStaff integer references Staff(ID),
Name varchar(25) not null
);
create table Student
(
ID integer primary key,
IDGradeParalelo integer references GradeParalelo(ID),
Name varchar(25) not null,
FatherName varchar(25) not null,
MotherName varchar(25) not null,
DateOfBirth int not null,
PlaceOfBirth varchar(25) not null,
Sex varchar(25) not null,
MobilePhone int not null,
Address varchar(25) not null,
FatherMobilePhone int not null,
FatherProfession varchar(22) not null,
MotherProfession varchar(22) not null,
);
create table Attendance
(
ID integer primary key,
IDStudent integer references Student(ID),
Attended int not null,
Date int not null
);
create table SubjectGrade
(
ID integer primary key,
IDGrade integer references Grade(ID),
IDSubject integer references Subject(ID)
);
create table ScoreRecord
(
ID integer primary key,
IDSubject integer references Subject(ID),
IDStudent integer references Student(ID),
FirstTrimester integer,
SecondTrimester integer,
ThirdTrimester integer,
FinalGrade integer,
Year int not null
);
--Show tables
select * from Users
select * from Area
select * from Subject
select * from Level
select * from Grade
select * from StaffType
select * from Staff
select * from GradeParalelo
select * from Student
select * from Attendance
select * from SubjectGrade
select * from ScoreRecord
--Alter table
alter table StaffType
add salaries decimal(8,2)
--Drop table
drop table Attendance
alter table StaffType drop column salaries
--DROP DATABASE command
drop database schoolmanagementsystem;
--Data Insert
Insert Into Users
values( 01,'Zubair', 4011),
(02,'Sam', '8011'),
(03, 'Zannat', '6011');
Insert Into Area
values( 01,'Zubair'),
(02,'Sam'),
(03, 'Zannat');
Insert Into Subject
values( 101,'Bangla','ban',01),
(103,'English','eng',02),
(107, 'Mathmatics', 'math',03);
Insert Into Level
values( 01,'Zubair','Senior teacher'),
( 02,'Sam','teacher'),
( 03,'Zannat','staff');
Insert Into StaffType
values( 01,'Zubair'),
(02,'Sam'),
(03, 'Zannat');
--Data show
select * from Users
--Where condition
select * from Area where ID=02
--Like operators
--Whose name start with a
select Name
from Area
where Name LIKE 'a%'
--Whose name end with a
select Name
from Area
where Name LIKE '%a'
--In any position a
select Name
from Area
where Name LIKE '%a%'
--2nd position a
select Name
from Area
where Name LIKE '_a%'
--In operator
select Name
from Subject
where Name IN ('Bangla');
--Between operator
--Find out the ID which in between 101 and 102
select ID
from Subject
where ID between 101 and 102
--Distinct
--Find unique information
select distinct(Name) from Subject
--Vaving clause
--Find out number of ID for each id where number of stuff should be less than 5
select count(distinct ID), ID
from Subject
group by ID
having count(distinct ID)<5
--Update value in table
update Subject set Name='Bengal' where ID=101
update Subject set Name='Math' where ID=103
--Drop table
drop table Subject
--Join
--Inner join
select Area.Name,
Area.Id,
Subject.Name,
Subject.ID from Area inner join Subject
on Area.ID=Suject.ID
--Left join
select Level.Name,
Level.ID,
Users.ID,
Users.ID from Level left join Users
on Level.ID=Users.ID
--Right join
select Staff.Name,
Staff.ID,
Level.ID,
Level.Name
from Staff right join Level
on Staff.ID=eLevel.Name
--Full outer join
select Subject.Name,
Subject.ID,
Area.ID,
Area.ID from Subject full join Area
on Subject.ID=Area.ID
--Experiment 4 create and drop views
create view Addresses as select Name,Abbreviation
from Subject where ID=103;
drop view Addresses;
--Use stored procedure
create procedure ID as
select * from
Subject, Area
go;
exec ID;