· 8 years ago · May 20, 2018, 07:24 PM
1--student id: 0951024
2--student name: tran quynh nhu
3
4use carclub
5--query 1
6select m1.FullName,m1.Address,m1.Email from MEMBER m1
7where not exists(
8 select c.Model
9 from CAR c
10 where c.Holder=m1.MID and c.Model in(
11 select c1.Model
12 from RENTAL r join CAR c1 on r.CarID=c1.CID
13 where r.Customer=m1.MID))
14--query 2
15select e.FullName,e.Address,e.Email,a.sumrent,b.sumprice
16from MEMBER e left join (
17 select r1.Customer,SUM(r1.Amount)as sumrent
18 from RENTAL r1
19 group by customer)a on a.Customer=e.MID left join (
20 select c1.holder, SUM(r.Amount)as sumprice
21 from CAR c1 join RENTAL r on c1.CID=r.CarID
22 group by Holder)b on b.Holder=e.MID
23
24
25--function1
26create function turnover (@model char(3), @month int, @year int)
27returns table
28as
29 return(
30 select c.model,sum(r.Amount) as Turnover
31 from CAR c left join RENTAL r on r.CarID=c.cid
32 where c.model=@model and month(r.duedate)=@month and year(r.duedate)=@year
33 group by c.model
34 )
35
36select * from turnover('M02',11,2011)
37
38--store procedure
39
40--check good status
41create function CheckCar(@carid char(10))
42returns int
43as
44 begin
45 declare @res int
46 select @res=count(*)
47 from car
48 where cid=@carid and Status='Good'
49 if (@res!=0)
50 set @res=1
51 return @res
52 end
53--rent if car is available
54create function CheckCar(@carid char(10),@date datetime)
55returns int
56as
57 begin
58 declare @res int
59 select @res=count(*)
60 from rental
61 where carid=@carid and duedate>@date
62 if (@res=0)
63 set @res=1
64 else
65 set @res=0
66 return @res
67 end
68
69--check 5 cars
70create function CheckNCar(@cusid int)
71returns int
72as
73 begin
74 declare @res int
75 select @res=count(*)
76 from rental
77 where @cusid=customer
78 if (@res>5)
79 set @res=0
80 else
81 set @res=1
82 return @res
83 end
84
85create procedure makerent(@cusID int,@carID char(10))
86as
87 begin
88 declare @res int
89
90 end
91
92 select * from RENTAL
93 select * from CAR
94
95--trigger 1
96--b.trigger
97CREATE TRIGGER trg_rent
98ON RENTAL
99AFTER INSERT, UPDATE
100AS
101 BEGIN
102 declare @c int
103 declare @cusID int
104 declare @carID char (10)
105 select @cusID=Customer , @carID=CarID from inserted
106 select @c=count(*)
107 from MEMBER m join CAR c on m.MID=c.Holder
108 where m.MID=@cusID and c.CID=@carID
109 if @c!=0
110 rollback transaction
111 END
112insert into RENTAL values(5, 1000, '52L-14314', '11/18/2011', '11/20/2011', 70, 210)