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'