Bai tap sql hot

Bài 1:
CREATE DATABASE QUANLYTAISAN
USE QUANLYTAISAN

create table dmphong
(map nvarchar(10)primary key,
tenp nvarchar(30)not null)

create table dmtaisan
(mats nvarchar(10)primary key,
tents nvarchar(30)not null,
donvi nvarchar(20),--DON VI TINH--
gia int)--DON GIA

create table phanphoi
(mats nvarchar(10)not null,
map nvarchar(10)not null,
soluong int,
ngaypp datetime,--NGAY PHAN PHOI TAI SAN--
ghichu nvarchar(20),
constraint kc primary key (mats,map),
constraint kn1 foreign key(mats)references dmtaisan(mats),
constraint kn2 foreign key (map)references dmphong(map) )


--2.TAO VIEW DE TONG HOP THONG TIN VE CAC TAI SAN CO DON GIA LON NHAT--
CREATE VIEW VD2
AS
SELECT * FROM DMTAISAN
WHERE GIA IN (SELECT MAX(GIA) FROM DMTAISAN)
--3.TAO VIEW DE TONG HOP THONG TIN VE NHUNG TAI SAN CHUA TUNG DUOC PHAN PHOI CHO CAC PHONG DE SU DUNG--
CREATE VIEW VD3
AS
SELECT * FROM DMTAISAN
WHERE MATS NOT IN (SELECT MATS FROM PHANPHOI)

--4.TAO THU TUC CO THAM SO LA @TENP DE DUA RA DANH SACH CAC TAI SAN DUOC PHAN PHOI VAO PHONG TREN--
CREATE PROC VD4
@TENP CHAR(30)
AS
SELECT * FROM DMTAISAN
WHERE MATS IN (SELECT MATS FROM PHANPHOI
WHERE MAP IN (SELECT MAP FROM DMPHONG
WHERE TENP=@TENP))
EXEC VD4 `LAN`
--5.TAO THU TUC CO THAM SO LA @TENTS DE DUA RA DANH SACH CAC PHONG DUOC PHAN PHOI TAI SAN TREN--
CREATE PROC VD5
@TENTS CHAR(20)
AS
SELECT DMTAISAN.*
FROM DMTAISAN,PHANPHOI
WHERE DMTAISAN.MATS=PHANPHOI.MATS AND TENTS=@TENTS
EXEC VD5 `SAT`
/*6.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG DMTAISAN.NEU DON GIA NHAP VAO NHO HON 0
THI IN RA MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY KHONG DUOC PHEP
NHAP VAO BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" */
CREATE TRIGGER TG6
ON DMTAISAN
FOR INSERT
AS
IF EXISTS (SELECT GIA FROM DMTAISAN
WHERE GIA<0)
BEGIN
PRINT `DU LIEU NHAP KHONG HOP LE`
ROLLBACK TRAN
END
ELSE PRINT `DU LIEU NHAP THANH CONG`
Bai 2 : CREATE DATABASE QUANLYDIEM
USE QUANLYDIEM
CREATE TABLE SINHVIEN
(
MASV CHAR(10) PRIMARY KEY,
HOTENSV CHAR(40) NOT NULL,
NGAYSINH SMALLDATETIME,
DIACHI CHAR(40),
DVHT INT --DON VI HOC TRINH--
)
CREATE TABLE MON
(
MAM CHAR(10) PRIMARY KEY,
TENM CHAR(30)
)
CREATE TABLE DIEM
(
MAM CHAR(10),
MASV CHAR(10),
PHACH CHAR(10), --SO PHACH--
DIEM FLOAT, --DIEM THI--
CONSTRAINT KC_DIEM PRIMARY KEY(MAM,MASV,PHACH),
CONSTRAINT KN_DIEM FOREIGN KEY (MAM) REFERENCES MON(MAM),
CONSTRAINT KN1_DIEM FOREIGN KEY (MASV) REFERENCES SINHVIEN(MASV)
)


/*2.TAP VIEW DE TONG HOP THONG TIN VE SINH VIEN CO DIEM THI CAO NHAT CUA MON "CSDL"*/
CREATE VIEW VD2
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEM IN (SELECT MAX(DIEM) FROM DIEM)
AND MAM IN (SELECT MAM FROM MON
WHERE TENM=`CSDL`))
/*3.TAO VIEW DE TONG HOP THONG TIN VE SINH VIEN CO DIA CHI O "THAI NGUYEN"*/
CREATE VIEW VD3
AS
SELECT * FROM SINHVIEN
WHERE DIACHI=`THAI NGUYEN`
/*4.TAO VIEW CHO BIET THONG TIN VE CAC SINH VIEN SINH TRUOC NAM 1980*/
CREATE VIEW VD4
AS
SELECT * FROM SINHVIEN
WHERE YEAR(NGAYSINH)<1980
/*5.TAO THU TUC CO THAM SO DAU VAO LA @TENM DE DUA RA DANH SACH CAC SINH VIEN CO DIEM THI
CHUA DAT CUA MON HOC TREN*/
ALTER PROC VD5
@TENM CHAR(30)
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEM<5 and mam in (select from mon
WHERE TENM=@TENM))
EXEC VD5 `JAVA`
/*6.TAO THU TUC CO THAM SO DAU VAO @DIEM DE DUA RA DANH SACH CAC SINH VIEN CO DIEM THI
THAP HON DIEM TREN CUA MON HOC