【SQL Server】综合实验练习题目

use test
create table driver
(
did char(5) unique,
dname char(10),
dsex char(2) default '男',
dage int,
dtel char(30)
)

create table car 
(
cid char(5) unique,
cbrand char(10),
ctype char(10)
)

create table garage
(
gid char(5) unique ,
location char(10),
nums int
)

create table park
(
cid char(5),
gid char(5),
pdate time,
primary key (cid,gid),
foreign key(gid) references garage(gid)
)

create table rent
(
did char(5),
cid char(5),
rdate time,
rdays int,
rcost int,
primary key(did,cid)
)


insert into driver
values
('d002','张军','男',28,'13374339096'),
('d003','王玲丽','女',38,'13573200112'),
('d004','李建林','男',46,'15173160129'),
('d005','张一山','男',35,'18107310908'),
('d006','刘鲁','男',29,'13907445655')

insert into car
values ('c001','长安','小车'),
('c002','三菱','小车'),
('c003','大众','中巴车'),
('c004','东风','小车'),
('c005','长安','商务车'),
('c006','别克','商务车'),
('c007','长安','中巴车')

insert into garage
values ('g001','A栋B1楼',250),
 ('g002','A栋B2楼',200),
  ('g003','B栋B1楼',300),
   ('g004','B栋B2楼',150)

insert into park
values('c001','g001','2021/09/09'),
('c002','g004','2021/09/09'),
('c003','g001','2021/09/26'),
('c007','g001','2021/09/09'),
('c001','g002','2021/11/05')

insert into rent
values('d005','c002','2021/09/19',3,1200),
('d003','c005','2021/09/27',2,600),
('d001','c001','2021/10/01',7,2800),
('d002','c002','2021/10/22',5,1500),
('d005','c001','2021/11/12',6,3000)

select * from car

select dname,dsex,dtel
from driver inner join rent 
on driver.did=rent.did
inner join car on car.cid=rent.cid
where cbrand ='长安'

select dname,dsex,dtel
from driver ,car,rent
where cbrand ='长安' and car.cid=rent.cid and driver.did=rent.did 

select cid,sum(rcost) as cost
from rent
group by cid
having sum(rcost)>=3500

update  rent
set rcost=rcost*0.8
where rcost >=1300

select dname,ctype,rcost
from driver inner join rent on driver.did=rent.did
inner join car on car.cid=rent.cid
order by rcost desc

select car.cid ,count(rent.cid) as n
from  rent inner join car
on car.cid=rent.cid
where ctype='小车'
group by car.cid


create view panrkinfa(location,ctype,pdate)
as
select location ,ctype,pdate
from park inner join car on car.cid=park.cid
inner join garage on garage.gid=park.gid
where pdate = '2021/09/09'

create function c
(@n int)
returns @t table
(gid char(5),
location char(10),
nums int
)
as
begin
insert into @t
select gid,location,nums
from garage
where @n<nums
return
end
select * from c(200)

create trigger dmll on driver
after delete
as
select did from rent
where did in (select did from deleted)

delete from driver where did in(select driver.did from driver inner join 
 rent on rent.did=driver.did
 where cid <>'c002')