Cách tạo BẢNG,RÀNG BUỘC,+ Bài 7 Hoàn chỉnh ^^!

----------------------------------------------------------------------------------------------
DDL- Data Define Language (Ngon Ngu Dinh Nghia Du Lieu)
----------------------------------------------------------------------------------------------
--Tao CSDL.
create database SQL7
--Su Dung CSDL SQL7
Drop database SQL7
-- Xoa CSDL SQL7
use SQL7
----------------------------------------------------------------------------------------------
--Tao bang.
--Tao table SINHVIEN ( co 3 cach)
--CACH 1:tao rang buoc truc tiep
Create table SINHVIEN
( MaSV char(5) PRIMARY KEY,--->Ghi truc tiep rang buoc tai moi cot
Ten char(10),
Nam int,
Khoa char(10)
)
--CACH 2 Tao rang buoc rieng biet o trong table
create table SINHVIEN
( MaSV char(5),
Ten char(10),
Nam int,
Khoa char(10),
PRIMARY KEY (MaSV)--> Rang buoc ghi phia duoi
)
--CACH 3 Tao cac cot trong table rieng
-- sau do dung lenh ALTER de sua bang
Create table SINHVIEN
( MaSV char(5) NOT NULL,-->>>>>>Chu y fai co DK NOT NULL
Ten char(10),
Nam int,
Khoa char(10)
)
Alter table SINHVIEN ADD--->Lenh them rang buoc
primary key (MaSv)
----------------------------------------------------------------------------------------------
--Tao bang MONHOC
--Dung Cach 1
Create table MONHOC
( MaMH char (5) Primary key,--<<<<<<
TenMH char(20),
Tinchi int,
Khoa char(10)
)
--Dung Cach 2
Create table MONHOC
( MaMH char (5),
TenMH char(20),
Tinchi int,
Khoa char(10),
PRIMARY KEY (MaMH) --<<<<<<
)
--Dung Cach 3
Create table MONHOC
( MaMH char (5) NOT NULL,
TenMH char(20),
Tinchi int,
Khoa char(10)
)
alter table MONHOC --<<<<<<<
ADD Primary key (MaMH)
----------------------------------------------------------------------------------------------
--Tao Bang KHOAHOC
--Dung cach 1:
Create table KHOAHOC
( MaKH char (5) Primary key ,
MaMH char (5) references MONHOC(MaMH),
Hocky int,
Nam int,
GV char (10)
)
---Dung cach 2
Create table KHOAHOC
( MaKH char(5),
MaMH char (5),
Hocky int,
Nam int,
GV char(10),
PRIMARY KEY (MaKH),
FOREIGN KEY (MaMH) REFERENCES MONHOC(MaMH)
)
--Dung cach 3
Create table KHOAHOC
( MaKH char(5) NOT NULL,
MaMH char(5),
Hocky int,
Nam int,
GV char(10)
)
Alter table KHOAHOC
ADD Primary key(MaKH),
Foreign key(MaMH) references MONHOC(MaMH)
----------------------------------------------------------------------------------------------
--Tao bang KETQUA
--Ko dung cach 1 duoc vi co toi 2 khoa chinh
--Cach 2
Create table KETQUA
( MaSV char(5),
MaKH char(5) ,
Diem int,
Primary key (MaSV,MaKH),
Foreign key (MaSV) references SINHVIEN(MaSV),
Foreign key (MaKH) references KHOAHOC(MaKH)
)
--Cach 3
Create table KETQUA
( MaSV char(5)NOT NULL,
MaKH char (5)NOT NULL,
Diem int
)
Alter table KETQUA
ADD Primary key (MaSV,MaKH),
Foreign key (MaSV) references SINHVIEN(MaSV),
Foreign key (MaKH) references KHOAHOC(MaKH)
----------------------------------------------------------------------------------------------
--Tao bang DIEUKIEN
--Cach 2
create table DIEUKIEN
( MaMH char(5),
MaMHtruoc char(5),
Primary key (MaMH,MaMHtruoc),
Foreign key (MaMH) references MONHOC(MaMH),
Foreign key (MaMHtruoc) references MONHOC(MaMH)
)
--Cach 3
create table DIEUKIEN
( MaMH char(5)NOT NULL,
MaMHtruoc char(5)NOT NULL
)
Alter table DIEUKIEN
ADD Primary key (MaMH,MaMHtruoc),
Foreign key (MaMH) references MONHOC(MaMH),
Foreign key (MaMHtruoc) references MONHOC(MaMH)
---------------------------------------------------------------------------------------------
--Xoa Bang
Drop table SINHVIEN,KETQUA,KHOAHOC,MONHOC,DIEUKIEN
--Sua bang Alter table
Alter table KETQUA
ADD Check (diem>0 or diem >10)
---------------------------------------------------------------------------------------------
DML :Data Manipulation Language (Ngon Ngu Thao tac tren Du lieu)
---------------------------------------------------------------------------------------------
--Tao noi dung.
--SINHVIEN.
insert into SINHVIEN
values (17,'Son',1,'CNTT')
insert into SINHVIEN(Ten,Khoa,Nam,MaSV)
values ('Bao','CNTT',2,8)
insert into SINHVIEN
values (25,'Nam',2,'TOAN')
--MonHoc.
insert into MONHOC
values (1310,'Nhap Mon Tin Hoc',4,'CNTT')
insert into MONHOC
values (3320,'Cau Truc Du Lieu',4,'CNTT')
insert into MONHOC
values (2410,'Toan Roi Rac',3,'Toan')
insert into MONHOC
values (3380,'Co So Du Lieu',3,'CNTT')
--KHOAHOC.
insert into KHOAHOC
values (85,2410,1,86,'Kim')
insert into KHOAHOC
values (92,1310,1,86,'An')
insert into KHOAHOC
values (102,3320,2,87,'Nien')
insert into KHOAHOC
values (112,2410,1,87,'Chan')
insert into KHOAHOC
values (119,1310,1,87,'An')
insert into KHOAHOC
values (135,3380,1,87,'Son')
--KETQUA.
insert KETQUA
values(17,112,8)
insert KETQUA
values(17,119,6)
insert KETQUA
values(8,85,10)
insert KETQUA
values(8,92,10)
insert KETQUA
values(8,102,8)
insert KETQUA
values(8,135,10)
--DIEUKIEN.
insert DIEUKIEN
values (3380,3320)
insert DIEUKIEN
values (3380,2410)
insert DIEUKIEN
values (3320,1310)
----------------------------------------------------------------------------------------------
SQL
----------------------------------------------------------------------------------------------
--Cau 1: Them vao SINHVIEN bo(35, ‘Hung’, 2,'CNTT').
insert SINHVIEN
values (35,'Hung',2,'CNTT')
--Cau 2: Them vao KETQUA hai bo (35, 102, 7) va (35, 135, 9).
insert KETQUA
values (35,102,7)
insert KETQUA
values (35,135,9)
--Cau 3: Sua bo (8, 102, 8) thanh (8, 102, 9) trong bang KETQUA.
update KETQUA
set Diem = 9
where MaSV = 8 and MaKH = 102
--Cau 4: Xoa bo (35, 'Hung',2,'CNTT') trong bang SINHVIEN.
delete from SINHVIEN
where MaSV = 35 and Ten ='Hung'
--Cau 5: Liet ke ten cac sinh vien.
select Ten
from SINHVIEN
--Cau 6: Liet ke cac mon hoc va so tin chi.
select TenMH,TinChi
from MONHOC
--Cau 7: Cho biet ket qua hoc tap cua Sinh vien co ma so la 8
select Diem as KetQuaSinhVienMaSo8
from KETQUA
where MaSV=8
--Cau 8: Cho biet ten Sinh Vien,ten Mon hoc va diem cua tung SV
select sv.Ten,mh.TenMH,kq.Diem
from SINHVIEN as sv
join KETQUA as kq on kq.MaSV = sv.MaSV
join KHOAHOC as kh on kq.MaKH = kh.MaKH
join MONHOC as mh on kh.MaMH = mh.MaMH
--OR
select sv.Ten,kq.Diem,mh.TenMH
from SINHVIEN sv,KETQUA kq,KHOAHOC kh,MONHOC mh
where kq.MaSV = sv.MaSV and kq.MaKH = kh.MaKH and kh.MaMH =mh.MaMH
--Cau 9: Cho biet cac ma so mon hoc va TEN MON HOC phai hoc ngay truoc mon co ma so 3320
select mh.MaMH,mh.TenMH
from MONHOC as mh join DIEUKIEN as dk
on dk.mamhtruoc = mh.mamh
where dk.MaMH = 3320
--OR
select mh.MaMH,mh.TenMH
from MONHOC mh,DIEUKIEN dk
where dk.mamhtruoc = mh.mamh and dk.mamh = 3320
--OR
select MaMH,TenMH
from MONHOC
where mamh = (select mamhtruoc
from DIEUKIEN
where mamh = 3320)
--Cau 10:Cho biet cac ma so mon hoc va ten mon hoc phai hoc sau mon co ma so 3320
select mh.MaMH,mh.TenMH
from MONHOC as mh join DIEUKIEN as dk
on dk.mamh = mh.mamh
where dk.MaMHtruoc = 3320
--OR
select mh.MaMH,mh.TenMH
from MONHOC mh,DIEUKIEN dk
where dk.mamh = mh.mamh and dk.mamhtruoc = 3320
--OR
select MaMH,TenMH
from MONHOC
where mamh = (select mamh
from DIEUKIEN
where mamhtruoc = 3320)
--Cau 11: Cho biet ten sinh vien va cac mon hoc co ket qua tren 7
select sv.Ten,mh.TenMH,kq.Diem
from SINHVIEN as sv
join KETQUA as kq on kq.MaSV = sv.MaSV
join KHOAHOC as kh on kq.MaKH = kh.MaKH
join MONHOC as mh on kh.MaMH = mh.MaMH
where kq.diem >7
--OR
select sv.Ten,mh.tenMH,kq.Diem
from SINHVIEN sv,KETQUA kq,MONHOC mh,KHOAHOC kh
where sv.MaSV = kq.MaSV
and kq.MaKH = kh.MaKH
and kh.MaMH = mh.MaMH
and kq.diem > 7
--Cau 12: Cho biet Ten sinh vien thuoc ve khoa co phu trach mon hoc Toan Roi rac
select Ten
from SINHVIEN
where Khoa = (select Khoa
from MONHOC
where TenMH = 'Toan Roi Rac')
--Cau 13: Cho biet ten cac mon hoc ngay truoc mon “Co So Du Lieu”
select mh.TenMH,mh.MaMH
from MONHOC mh join DIEUKIEN dk on mh.MaMH = dk.MaMHtruoc
where dk.mamh = (select MaMH
from MONHOC
where TenMH = 'Co So Du Lieu')
--OR
select mh.TenMH,mh.MaMH
from DIEUKIEN dk,MONHOC mh
where dk.mamhtruoc = mh.mamh
and dk.mamh IN (select MaMH
from MONHOC
where TenMH = 'Co So Du Lieu')
--Cau 14: Cho biet ten cac mon hoc lien sau mon "Cau truc Du lieu"
select mh.TenMH,mh.MaMH
from MONHOC mh join DIEUKIEN dk on mh.MaMH = dk.MaMH
where dk.mamhtruoc = (select MaMH
from MONHOC
where TenMH = 'Cau Truc Du Lieu')
--OR
select mh.MaMH,mh.TenMH
from MONHOC mh,DIEUKIEN dk
where dk.mamh = mh.mamh
and dk.mamhtruoc IN(select mamh
from MONHOC
where Tenmh = 'Cau truc Du lieu')
--Cau 15: Tinh diem trung binh cua moi sinh vien
select avg(diem) AS DiemTrungbinhmoiSV
from KETQUA
--Cau 16: Liet ke ten Sinh vien va tinh diem trung binh cua sinh vien do
select sv.Ten,avg(kq.diem) as DiemTB
from SINHVIEN sv join KETQUA kq on sv.MaSV = kq.MaSV
group by sv.Ten,kq.MaSV
--OR
select sv.Ten,kq.MaSV,avg(kq.diem) as Diemtrungbinh
from SINHVIEN sv,KETQUA kq
where (kq.masv = sv.masv)
group by sv.Ten,kq.MaSV
--Cau 17: Tinh Tong so khoa hoc va tong so diem cua tung sinh vien
select sv.Ten,Count(*)AS TongKhoahoc,Sum(kq.diem) as Tongsodiem
from SINHVIEN sv
join KETQUA kq on sv.MaSV = kq.MaSV
join KHOAHOC kh on kq.MaKH = kh.maKH
group by sv.Ten,kq.MaSV
--OR
select sv.Ten,count(kh.MaKH) As SoKH,Sum(kq.diem) as Tongdiem
from KETQUA kq,KHOAHOC kh,SINHVIEN sv
where kq.MaKH = kh.MaKH and kq.MaSV = sv.MaSV
group by sv.Ten,kq.MaSV
--Cau 18: Cho Biet Ten sinh vien va diem trung binh cua sinh vien do trong tung hoc ky
-- tung nam hoc.
select sv.Ten,kh.Hocky,kh.Nam,avg(kq.diem) as DTBinh
from SINHVIEN sv
join KETQUA kq on sv.masv = kq.masv
join KHOAHOC kh on kh.makh = kq.makh
group by sv.Ten,kh.Hocky,Kh.Nam
--Cau 19: Cho Biet ten sinh vien dat diem cao nhat
select distinct sv.Ten,kq.Diem
from SINHVIEN sv join KETQUA kq on sv.masv = kq.masv
where kq.diem >= all (select diem
from KETQUA)
--OR
select distinct sv.Ten,kq.Diem
from SINHVIEN sv join KETQUA kq on sv.masv = kq.masv
where kq.diem = (select MAX(diem)
from KETQUA)
--Cau 20: CHo biet Ten sinh vien co diem trung binh nho hon hoc bang 7
select sv.Ten,avg(kq.diem)As Diemtb
from SINHVIEN sv join KETQUA kq on sv.masv = kq.masv
group by sv.Ten
having avg(kq.diem) < = 7
----OR
select sv.Ten,avg(kq.diem)As Diemtb
from SINHVIEN sv, KETQUA kq
where sv.masv = kq.masv
group by sv.Ten
having avg(kq.diem) < = 7
--Cau 21:Cho biet ten sinh vien tham gia tat ca cac mon hoc
select sv.Ten,count (*) as SoluongMH
from SINHVIEN as sv
join KETQUA as kq on kq.MaSV = sv.MaSV
join KHOAHOC as kh on kq.MaKH = kh.MaKH
join MONHOC as mh on kh.MaMH = mh.MaMH
group by sv.Ten
having count (*) = (select Count(*)
from MONHOC)
---------------------------------------+THE END+--------------------------------------------