Untitled

Cau 4 ngay 3/10

a

CREATE database QLPHONG;

CREATE TABLE hotel
(
    idKS VARCHAR(20),
    tenKS varchar(50),
    diachi varchar(100),
    
    primary key (idKS)
);

CREATE TABLE phong
(
    idPhong varchar(20),
    idKS varchar(20),
    loaiP varchar(100),
    giaP float(30),
	
    PRIMARY key (idPhong),
    foreign key (idKS) REFERENCES hotel(idKS)
);
    
create table khach
(
    idKH varchar(20),
    hoten varchar(100),
    sdt varchar(20),
    
    primary key (idKH)
);

create table datphong
(
    idKS varchar(20),
    idKH varchar(20),
    
    ngaynhan date,
    ngaytra  date,
    
    idPhong varchar(20),
   	
    primary key (idKS, idKH),
    foreign key (idKS) references hotel(idKS), 
    foreign key (idKH) references khach(idKH),
    foreign key (idPhong) references phong(idPhong)
);

b
insert into hotel (idKS, tenKS, diachi)
values ('V0001', 'Villa', 'Tran Duy Hung Ha Noi')

select khach.idKH, khach.hoten, khach.sdt
from khach
join datphong on khach.idKH = datphong.idKH
join hotel on hotel.idKS = datphong.idKS
where hotel.tenKS = "Villa"
AND (datphong.ngaytra > "5/4/2024" OR datphong.ngaytra = NULL)

c

select phong.idPhong, phong.idKS, loaiP, giaP
from hotel join phong on hotel.idKS = phong.idKS
where phong.giaP > 5000000
group by phong.idKS