aeZA0J36

1--1
2CREATE DATABASE VNTours;
3USE VNTours;
4
5--2
6CREATE TABLE Tours(
7	TourID INT IDENTITY PRIMARY KEY,
8	TourName NVARCHAR(200) NOT NULL,
9	Days TINYINT,
10	Nights TINYINT,
11	Image VARCHAR(150),
12	Description NVARCHAR(500),
13	Price MONEY CHECK (Price > 0)
14);
15
16CREATE TABLE TourDetails(
17	TourDetailID INT IDENTITY PRIMARY KEY,
18	TourID INT FOREIGN KEY REFERENCES Tours(TourID),
19	Day TINYINT,
20	Place VARCHAR(100) NOT NULL,
21	Detail NVARCHAR(200),
22	Vehicle NVARCHAR(100)
23);
24
25--3
26INSERT INTO Tours(TourName, Days, Nights, Image, Description, Price)
27VALUES 
28(N'Ha Long', 2, 1, 'images/halong.jpg', N'Hạ Long 2 ngày 1 đêm', 1000000),
29(N'Đà Lạt', 7, 7, 'images/dalat.jpg', N'Đà Lạt 1 tuần', 10000000),
30(N'Đà Nẵng', 2, 1, 'images/danang.jpg', N'ĐN 2 ngày 1 đêm', 1000000);
31
32INSERT INTO TourDetails(TourID, Day, Place, Detail, Vehicle)
33VALUES (1, 1, N'Địa điểm #1', N'Vui chơi ở địa điểm 1', N'2 chân'),
34(1, 2, N'Địa điểm #2', N'Nghỉ ngơi ở địa điểm 2', N'Limo'),
35(1, 2, N'Địa điểm #3', N'Ăn chơi ở địa điểm 3', N'Limo');
36
37--4
38SELECT * FROM Tours WHERE TourName LIKE '%Ha Long%' OR Description LIKE '%Ha Long%';
39
40--5
41SELECT * FROM Tours WHERE (Days < 3) AND (Nights < 3) AND (Price < 10000000);
42
43--6
44CREATE INDEX IX_TourName ON Tours(TourName);
45
46--7
47CREATE VIEW v_TourInfo AS 
48SELECT TourName, Price, Day, Place, Detail, Vehicle
49FROM Tours, TourDetails
50WHERE Tours.TourID = TourDetails.TourID;
51
52--8
53CREATE PROCEDURE sp_FindTourByName @keyword NVARCHAR(50)
54AS 
55SELECT * FROM Tours WHERE (TourName LIKE '%' + @keyword + '%');
56
57--EXEC sp_FindTourByName N'Đà';
58
59--9
60CREATE TRIGGER tg_RemoveTour
61ON TourDetails
62INSTEAD OF DELETE 
63AS BEGIN
64	DECLARE @ToDeleteTourID AS INT = (SELECT TourID FROM deleted);
65	IF EXISTS (SELECT * FROM Tours WHERE TourID=@ToDeleteTourID)
66		ROLLBACK TRANSACTION;
67END
68