--Tao CSDL
CREATE DATABASE K11QLSV
ON
PRIMARY (
NAME = K11QLSV_data,
FILENAME = 'E:\Quan Ly Sinh Vien\K11QLSV.mdf',
SIZE = 3,
MAXSIZE = 100,
FILEGROWTH = 10 )
LOG ON (
NAME = K11QLSV_log,
FILENAME = 'E:\Quan Ly Sinh Vien\K11QLSV.ldf',
SIZE = 3,
MAXSIZE = 100,
FILEGROWTH = 10 )
-- Su dung CSDL
use K11QLSV
-- Tao bang Khoa
CREATE TABLE K11KHOA (
makhoa nchar(6) primary key not null,
tenkhoa nvarchar(30) not null,
diadiem nvarchar(30),
dienthoai nvarchar(20) )
-- Tao bang lop
CREATE TABLE K11LOP (
malop nchar(6) primary key not null,
tenlop nvarchar(30) not null,
namvao int,
makhoa nchar(6))
-- Tao bang Sinh vien
CREATE TABLE K11SINHVIEN (
masv nchar(10) primary key not null,
hodem nvarchar(30) not null,
ten nvarchar(15) not null,
gioitinh bit,
ngaysinh smalldatetime,
malop nchar(6))
-- Tao bang Mon hoc
CREATE TABLE K11MONHOC (
mamon nchar(6) primary key not null,
tenmon nvarchar(30),
sotinchi int )
-- Tao Sinhvien_Monhoc
CREATE TABLE K11SINHVIEN_MH (
mamon nchar(6) not null,
masv nchar(10) not null,
hocky int not null,
lanthi int not null,
diem decimal(4,2),
Constraint pk_sv_mh primary key(mamon,masv,hocky,lanthi))
-- Tao moi quan he giua bang
ALTER TABLE K11SINHVIEN_MH
ADD Constraint pk_MonHoc
Foreign key (mamon)
References K11MONHOC(mamon)
On delete cascade
On update cascade
--------------------------
ALTER TABLE K11SINHVIEN_MH
ADD Constraint pk_SINHVIEN
Foreign key (masv)
References K11SINHVIEN(masv)
On delete cascade
On update cascade
-------------------------
ALTER TABLE K11LOP
ADD Constraint pk_KHOA
Foreign key (makhoa)
References K11KHOA(makhoa)
On delete cascade
On update cascade
------------------------
ALTER TABLE K11SINHVIEN
ADD Constraint pk_LOP
Foreign key (malop)
References K11LOP(malop)
On delete cascade
On update cascade
--- b. bo xung cac rang buoc----
---gia tri mac dinh cho gioi tinh trong bang sv la false--
alter table SINHVIEN
add constraint pk_gioitinh
default (0) for gioitinh
ALTER TABLE K11LOP
ADD Constraint ck_namvao check ( namvao > 1990 )
---c.1 them du lieu vao bang khoa --------
INSERT INTO K11KHOA VALUES('K01',N'Công Nghệ Thông Tin', N'Tầng 2-A5-CS1',4349343 )
INSERT INTO K11KHOA ( makhoa, tenkhoa, diadiem) VALUES ( 'K02',N'Tự nhiên',N'Tầng 3-A2-CS1')
INSERT INTO K11KHOA ( makhoa, tenkhoa, diadiem)VALUES ( 'K03',N'Ngoại Ngữ',N'Tầng 3-A3-CS1')
-- c.2 Them du lieu vao bang lop--
INSERT INTO K11LOP VALUES('L01','SPTinK32', 2006,'K01')
INSERT INTO K11LOP VALUES('L02','SPTinK33', 2007,'K01')
INSERT INTO K11LOP VALUES('L03','CNTTK8', 2008,'K01')
INSERT INTO K11LOP VALUES('L04','CNTTK9', 2009,'K01')
INSERT INTO K11LOP VALUES('L05','CNTTK11', 2011,'K01')
INSERT INTO K11LOP VALUES('L06','SPTinK37', 2011,'K01')
INSERT INTO K11LOP VALUES('L07',N'SPToánK33', 2007,'K02')
INSERT INTO K11LOP VALUES('L08',N'SPAnhK34', 2008,'K03')
INSERT INTO K11LOP VALUES('L09',N'SPHóaK34', 2008,'K02')
--- c.3 then du lieu vao bang mon hoc--
INSERT INTO K11MONHOC VALUES( 'MH01', N'Tin học co sở',2)
INSERT INTO K11MONHOC VALUES( 'MH02', N'Ứng dụng CNTT',3)
INSERT INTO K11MONHOC VALUES( 'MH03', N'Toán rời rạc',3)
---c.4 them du lieu vao bang Sinh vien ---
INSERT INTO K11SINHVIEN VALUES(' SV01',N'Nguyen Huu', N'Hoang','True','3/2/1989 12:00:00 AM','L01')
INSERT INTO K11SINHVIEN VALUES(' SV02',N'Tran Ngoc', 'Minh','False','3/12/1989 12:00:00 AM','L02')
INSERT INTO K11SINHVIEN VALUES(' SV03', N'Nguyen Ngoc', N'Ngân','False','2/12/1989 12:00:00 AM','L01')
INSERT INTO K11SINHVIEN VALUES(' SV04', N'Hoang Van', N'Hung','True','2/2/1990 12:00:00 AM','L04')
INSERT INTO K11SINHVIEN VALUES(' SV05', N'Trih Thi', N'Ngoc','False','10/11/1990 12:00:00 AM','L08')
INSERT INTO K11SINHVIEN VALUES(' SV06', N'Ha Hai', N'Linh','True','2/12/1991 12:00:00 AM','L08')
INSERT INTO K11SINHVIEN VALUES(' SV07', N'Hoa Ha', N'Hang','False','2/1/1990 12:00:00 AM','L01')
INSERT INTO K11SINHVIEN VALUES(' SV08', N'Ha Thu', N'Huong','False','2/12/1990 12:00:00 AM','L06')
INSERT INTO K11SINHVIEN VALUES(' SV09', N'Nguyen Van', N'Linh','True','2/6/1989 12:00:00 AM','L05')
----c.5 them du lieu bang sinh vien-mon hoc
INSERT INTO K11SINHVIEN_MH VALUES('MH01',' SV01', 1, 1,3.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH01',' SV02', 1, 1,3.00)
INSERT INTO K11SINHVIEN_MH VALUES('MH01',' SV03', 1, 1,0.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH01',' SV03', 1, 2,1.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH02',' SV03', 2, 1,3.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH02',' SV06', 2, 1,0.00)
INSERT INTO K11SINHVIEN_MH VALUES('MH02',' SV06', 2, 2,0.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH02',' SV07', 2, 1,3.00)
INSERT INTO K11SINHVIEN_MH VALUES('MH03',' SV01', 1, 1,2.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH03',' SV04', 2, 1,1.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH03',' SV07', 1, 1,2.00)
INSERT INTO K11SINHVIEN_MH VALUES('MH03',' SV09', 2, 1,0.50)
INSERT INTO K11SINHVIEN_MH VALUES('MH03',' SV09', 2, 2,1.50)
--d. sinh vien 'SV03' chuyen tu L01 sang L02---
Update K11SINHVIEN
Set malop = 'L02'
where masv = 'SV03'
-- e. chuyen so dien thoai cua khoa CNTT thanh 0438373--
Update K11KHOA
Set dienthoai = 0438373
where tenkhoa = N'Công Nghệ Thông Tin'
--f. Sinh vien SV04 da bi dinh chi hoc, hay xoa thong tin ve sinh vien--
Delete from K11SINHVIEN
where masv = 'SV04'
--g. Xoa thong tin ve lop 'L04'--
Delete from K11LOP
where malop = 'L04'
---------- BAI 3 -------------------------
---a. Hien thi ma lop, ten lop, ten khoa, cuar tat ca cac lop--
SELECT malop,tenlop,tenkhoa
From K11KHOA,K11LOP
WHERE K11LOP.makhoa= K11KHOA.makhoa
--b. Hien thi thong tin chi tiet cua cac lop khoa CNTT---
Select malop,tenlop, namvao
From K11KHOA,K11LOp
Where ((K11LOP.makhoa= K11KHOA.makhoa)
and (tenkhoa= N'Công Nghệ thông tin'))
---c.Hien thi ho ten cac sinh vien co lop ten la "SPTinK32"---
Select masv,hodem,ten
From K11SINHVIEN, K11LOP
Where K11SINHVIEN.malop = K11LOP.malop and Tenlop ='SPTinK32'
--d. Dua ra ho ten cac sinh vien o khoa co ma khoa la 'K01'--
SELECT hodem,ten
FROM K11SINHVIEN,K11LOP
WHERE K11LOP.malop= K11SINHVIEN.malop and makhoa='K01'
--e.1 Hien thi thong tin cua tat ca cac sinh vien o khoa CNTT, trong do gioi tinh hien thi Nam
SELECT masv,hodem,ten,gioitinh,ngaysinh
FROM K11SINHVIEN,K11LOP
WHERE K11LOP.malop= K11SINHVIEN.malop and makhoa='K01'
and gioitinh = 'True'
--e.2 Hien thi thong tin cua tat ca cac sinh vien o khoa CNTT trong do gioi tinh hien thi Nam hoac nu---
--Hien thi thong tin cua tat ca cac sinh vien o khoa CNTT trong do gioi tinh hien thi Nam hoac nu---
SELECT masv, hodem, ten,
case gioitinh
when 'true' then 'Nam'
when 'false' then N'Nữ'
END
FROM SINHVIEN, LOP, KHOA
WHERE LOP.malop=SINHVIEN.malop and LOP.makhoa=KHOA.makhoa and tenkhoa=N'Công Nghệ Thông Tin'
--Hien thi thong tin cua tat ca cac sinh vien o khoa tn va nn trong do gioi tinh hien thi Nam hoac nu---
SELECT masv, hodem, ten,ngaysinh,
case gioitinh
when 'true' then 'Nam'
when 'false' then N'Nữ'
END
FROM SINHVIEN, LOP, KHOA
WHERE LOP.malop=SINHVIEN.malop and LOP.makhoa=KHOA.makhoa and (tenkhoa=N'Tự Nhiên' or tenkhoa=N'Ngoại Ngữ')
--g. Danh sach cac sinh vien co nam vao la 2007 cua khoa CNTT---
Select masv, hodem, ten, gioitinh, ngaysinh
From K11LOP, K11SINHVIEN
WHERE K11LOP.malop= K11SINHVIEN.malop and makhoa='K01'
and gioitinh = 'True' and namvao= 2007
--h. Danh sach sinh vien man co ten la LINH--
Select *
From K11SINHVIEN
Where gioitinh= 'True' and ten= 'Linh'
-- hien thi danh sach sinh vien neu Gioi Tinh la True(1) thi hien thi la Nam, con lai la Nu
SELECT masv,hodem,ten,gioitinh,ngaysinh,
Case gioitinh
when 'True' then 'Nam'
else N'Nữ'
End
FROM K11SINHVIEN
------------- BAI 4 ----------------------
--a. Cho biet tong so sinh vien co trong CSDL--
Select Count(masv) as N'Tổng số sinh viên'
from K11SINHVIEN
--b. tông so sinh vien khoa CNTT--
Select Count(masv) as N'Tổng số sinh viên khoa CNTT'
from K11SINHVIEN,K11LOP
where (K11LOP.malop = K11SINHVIEN.malop) and makhoa= 'K01'
-- c. cach 1 Cho biet so luong sinh vien theo tung khoa
Select Count(masv) as N'Tổng số sinh viên khoa CNTT'
from K11SINHVIEN,K11LOP
where (K11LOP.malop = K11SINHVIEN.malop) and makhoa= 'K01'
Select Count(masv) as N'Tổng số sinh viên khoa Tu Nhien'
from K11SINHVIEN,K11LOP
where (K11LOP.malop = K11SINHVIEN.malop) and makhoa= 'K02'
Select Count(masv) as N'Tổng số sinh viên khoa Ngoai Ngu'
from K11SINHVIEN,K11LOP
where (K11LOP.malop = K11SINHVIEN.malop) and makhoa= 'K03'
--c. cach 2 : cho biet so luong sinh vien theo tung khoa--
Select makhoa,Count(masv)as N'Tổng số sinh viên theo từng khoa'
From K11SINHVIEN, K11LOp
Where K11SINHVIEN.malop= K11LOP.malop
group by makhoa
-- d. Dua ra khoa co so luong sinh vien lon hon 3--
select makhoa, Count(masv) as N'Tổng số sinh viên'
from K11SINHVIEN, K11LOP
where K11SINHVIEN.malop = K11LOP.malop
group by makhoa
having COUNT(masv)>3
--e. Tong so sinh vien nam vao nam hoc 2009 co ten bat dau bang chu H--
Select COUNT(masv) as N'Tổng số sinh viên'
From K11SINHVIEN, K11LOP
Where K11SINHVIEN.malop =K11LOP.malop
and namvao = 2009 and gioitinh= 'True' and ten like 'H%'
-- f.Cho biet tong so sinh vien theo tung lop cua cac lop co nam vao la 2008--
Select tenlop, COUNT(masv)as N'Sĩ Số '
From K11SINHVIEN,K11LOP
Where K11SINHVIEN.malop = K11LOP.malop and namvao = 2008
Group by tenlop
--g. cho biet lop nao co so sinh vien dong nhat--
Select tenlop, COUNT(masv)as N'Sĩ Số'
From K11SINHVIEN,K11LOP
Where K11SINHVIEN.malop = K11LOP.malop
Group by tenlop
SELECT MAX(N'Sĩ Số') FROM
Bạn đang đọc truyện trên: Truyen2U.Pro