Advanced Database Training
1. Draw ER diagram and Convert ER diagram to table
Ex1
create database ABC
use ABC
create table Dept (dcod varchar (20) primary key ,dname varchar (20), dhead
varchar (20))
create table Tech (Tcod varchar (20) primary key ,TFname varchar (20),TLname
varchar (20), TDOB int,Tsex varchar (5),dcod varchar (20) foreign key references
Dept)
create table Student (Scod varchar (20) primary key ,SFname varchar (20),SLname
varchar (20), Smname int,Tcod varchar (20) foreign key references Tech,Dcod
varchar (20)foreign key references Dept)
create table Course (Cgrade varchar (20) primary key ,Cname varchar (20), CChr
int,dcod varchar (20) foreign key references Dept)
create table Dependant (Dname varchar (20), DRship varchar (20),Scod varchar (20)
foreign key references Student)
create table Deptlocation (Dlocation varchar (20), dcod varchar (20) foreign key
references Dept)
create table Tech_Edu (Dname varchar (20), Tinstitut varchar (20), Tyear varchar
(20),Tsepcifecation varchar (20),Tcod varchar (20) foreign key references Tech)
create table Tech_Course (TCid varchar (20) primary key, B_no varchar (20),
Room_no varchar (20),Tcod varchar (20) foreign key references Tech,Cgrade varchar
(20)foreign key references Course)
create table Stud_Course (SCid varchar (20) primary key, A_Year varchar (20),
grade varchar (20),smister varchar (20),Scod varchar (20) foreign key references
Student,Cgrade varchar (20)foreign key references Course)
Alter table Dept add offece varchar (20)
select * from Dept
alter table Dept drop column dhead
Sp_Rename 'department','Dept'
Sp_Rename 'department.office','department.Offece'
Alter table Dept alter column dname varchar (70)
Alter table Dept alter column dname varchar (20)
insert into Dept (dcod,dname,dhead) values('d001','ICT', 'Alemu')
insert into Dept values('d002','database','', 'chala')
insert into Dept (dcod,dname,dhead) values('d003','computer', 'Olana')
insert into Dept (dcod,dname,dhead) values('d004','ICT', 'Gemechu')
select * from course
insert into course (cgrade,cname,CChr, dcod) values('A','network',4, 'd001')
insert into course (cgrade,cname,CChr, dcod) values('B','network',4, 'd001')
insert into course (cgrade,cname,CChr, dcod) values('C','network',4, 'd001')
update dept set dhead= 'instractor' where dcod='d001'
delete from Dept where dcod= 'd004'
create table ftest(id int primary key, name varchar(30), salary decimal (10,2)
default 5000, time date)
select * from ftest
truncate table ftest
insert into ftest values(001,'Alemaz', '5000')
insert into ftest values(002,'Elyas', '10000')
insert into ftest values(004,'chala', '20000')
insert into ftest values(005,'chuna', '12000')
Ex2
create database Test1
use Test1
create table Student1 (id varchar (20) primary key ,name varchar (20), Amaric
int, English int,maths int, Physics int, Adress varchar (20))
create table Techear1 (id int primary key ,name varchar (20),sex varchar(20), Age
int,salary decimal(10,2), Adress varchar (20))
select * from Student1
insert into Student1 values(001,'Abebe',75,61,87,56, '4 kil')
insert into Student1 values(002,'feven',89,61,87,56, '5 kil')
insert into Student1 values(003,'Elyas',89,61,87,56, '6 kil')
insert into Student1 values(004,'chala',56,61,87,56, 'pissa')
insert into Student1 values(005,'Yonas',45,78,87,56, 'bole')
select * from Techear1
insert into Techear1 values(001,'Abebe','M',20,2000, '4 kil')
insert into Techear1 values(002,'Alsa','M',16,4000, '5 kil')
insert into Techear1 values(003,'Amanu','M',12,10000, '6 kil')
insert into Techear1 values(004,'Alazare','M',50,2000, 'Bole')
update Techear1 set Adress = '6 killo' where id=003
select id, name, Amaric, English, maths, physics, Adress,
(Amaric+English+maths+physics) As Sum,
(Amaric+English+maths+physics)/4 As avg,rank () over (order by
Amaric+English+maths+physics desc) As rank,
(select min(cal) from (values (Amaric),(English),(maths),(physics))X(cal)) As
min,
(select max(cal) from (values (Amaric),(English),(maths),(physics))X(cal)) As
max,
case when (Amaric+English+maths+physics)/4>=90 then'Exellent'
when (Amaric+English+maths+physics)/4>=80 then'V Good'
when (Amaric+English+maths+physics)/4>=70 then'Good' else 'poor' end As Grade
from Student1
select min (Amaric) from Student1
select count (*) from Student1 where (Amaric+English+maths+physics)/4>=70 group
by Amaric
select name from Student1 where (Amaric+English+maths+physics)/4>=70 group by
name
select id, name from Student1 group by id, name having name='abebe'
select id, amaric, maths, name from Student1 where Amaric>=70 and maths<=70
select * from Techear1 where name like '%A%'
select * from Student1 where Amaric between 75 and 90
select * from Student1 where adress in('4 kil', '5 kil', 'pissa', 'bole')
select * from Techear1 where name like '%A'
select * from Student1,Techear1 where Student1.id=Techear1.id
select * from Student1 where id in(select id from Student1 where Amaric>70)
select id from Student1 where Amaric>70
insert into Techear1 select *from Student1 where id in(select id from Student1)
insert into Techear1 select *from Student1
update Student1 set English =0.5*English where maths in(select maths from
Student where maths>=70
delete from Student1 where Physics in(select Physics from Techear1 where
Physics>=70)
EX3
create database Test2
use Test2
create table Dept (Did varchar (20) primary key ,dname varchar (20), budget
varchar (20))
create table employee (Eid int primary key ,Ename varchar (20) ,salary money, Did
varchar (20) foreign key references Dept )
create table Dept1 (Did varchar (20) primary key ,dname varchar (20), budget
varchar(20))
select * from Dept
insert into Dept values('001','Abebe','4999')
insert into Dept values('002','feven','7777777')
insert into Dept values('003','Elyas', '677777')
insert into Dept values('004','chala', '7777777')
insert into Dept values('005','Yonas', '44555')
insert into Dept values('006','tola', '99999')
insert into Dept values('007','Chuna', '2000000')
select * from Dept1
insert into Dept1 values('001','Abebe','4999')
insert into Dept1 values('002','feven','7777777')
insert into Dept1 values('003','Elyas', '677777')
select * from employee
insert into employee values(001,'Abebe',2000,'001')
insert into employee values(002,'Alsa',4000,'002')
insert into employee values(003,'Amanu',10000,'004')
insert into employee values(004,'Alazare',2000,'005')
insert into employee values(005,'Alazare',2000,'001')
select * from Dept where Did ='001'
select * from Dept where not Did ='004'
select * from Dept where Did ='004' and budget>=4000
select * from Dept where Did ='004' or budget>=4000
select * from Dept union select * from Dept1
select * from Dept union all select * from Dept1
select * from Dept intersect select * from Dept1
select * from Dept except select * from Dept1
select * from Dept cross join Dept1
select * from Dept, Dept1
EX4
create database garent
use garent
create login login1 with password ='1234'
create login login2 with password ='1234'
create user user1 for login login1
create user user2 for login login2
create role Studgroup
exec SP_addrolemember'Studgroup','user1'
exec SP_addrolemember'Studgroup','user2'
create table Student (Sid varchar (20) ,name varchar (20), sex varchar (20))
grant select, insert,update on Student To user1 with grant option
use garent
grant select, insert,update on Student To user1 with grant option
insert into Student values('002','Sosi', 'F')
select * from Student
update Student set name ='up name'where Sid=001
delete Student from Student where Sid=002
truncate table Student
grant select, insert,update on Student To user2
revoke update on Student to user2 cascade
use garent
grant select, insert,update on Student To user1 with grant option
insert into Student values('002','Sosi', 'F')
select * from Student
update Student set name ='up name'where Sid=001
delete Student from Student where Sid=001
truncate table Student
EX5
create database Ex2
use Ex2
create table Student (Sid int not null primary key ,name varchar (20), sex
varchar (20), Age int)
create table Course (C_code int not null primary key ,C_name varchar (20))
create table G_report (Sid int not null,C_code int not null, Grade varchar (20)
foreign key (Sid) references Student(Sid), foreign key (C_code) references
Course(C_code))
select * from Student
insert into Student values(001,'Abebe','M', 20)
insert into Student values(002,'Aster','F', 30)
insert into Student values(003,'Chaltu','F', 23)
insert into Student values(004,'Chuna','M', 43)
insert into Student values(005,'Yaya','M', 34)
select * from Course
insert into Course values(101,'network')
insert into Course values(102,'database')
insert into Course values(103,'it')
insert into Course values(104,'ict')
select * from G_report
insert into G_report values(005,101,'A')
insert into G_report values(003,104,'B')
insert into G_report values(004,102,'C')
insert into G_report values(002,104,'B')
select * from Student left join G_report on Student.Sid=G_report.Sid
select * from Student right join G_report on Student.Sid=G_report.Sid
select * from G_report inner join Student on G_report.Sid=Student.Sid
select * from G_report full join Student on G_report.Sid=Student.Sid
select * from Student, Course [G_report]
begin tran
insert into Student values(00101,'Abebe','M', 20)
commit
begin tran
update Student set name='rt' where Sid=007
delete from Student where Sid=001
rollback
begin transaction
insert into Student values(001,'Abebe','M', 20)
save transaction sp1
begin transaction
delete from Student where Sid=001
save transaction sp2
begin transaction
update Student set Age =80 where Sid=002
save transaction sp3
rollback transaction sp2
commit transaction sp1
EX6
create database lastlab
use lastlab
create table Dept (Did varchar (20) primary key ,dname varchar (20), budget
varchar (20))
create table employee (Eid int primary key ,Ename varchar (20) ,salary money, Did
varchar (20) foreign key references Dept )
create table Dept1 (Did varchar (20) primary key ,dname varchar (20), budget
varchar (20))
backup database lastlab to disk ='D:\YM'
create database lastlab1
use lastlab1
drop database lastlab
restore database lastlab from Disck='D:\YM'
