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')
|
