I need a little help, Basically, I am creating a database for Hotel Management, Please take a look at my Queries. So now, in the end, I want to take a total for availed Services that's where I'm stuck. Can anyone here help me out with this and please let me know if I'm wrong somewhere because I'm still learning Microsoft SQL. Thanks in Advance
Create Database CustRoomBooking
use CustRoomBooking
drop table RoomsTypes
CREATE TABLE RoomTypes
(
ID int Not NULL,
type varchar(50) not NULL,
PRIMARY KEY (ID)
)
CREATE TABLE Pricing
(
service_ID int not NULL,
price int not null,
PRIMARY KEY(Service_ID)
)
drop table Rooms
create Table Rooms
(
ID int Not NULL,
floor int not NULL,
bed int not NULL,
roomType int not NULL,
roomPrice int not NULL,
PRIMARY KEY (ID),
FOREIGN KEY (roomType) REFERENCES RoomTypes(ID),
FOREIGN KEY (roomPrice) REFERENCES Pricing(Service_ID)
)
Drop Table Customers
create Table Customers
(
ID int Not NULL,
name varchar(100) not NULL,
cnic varchar (13) not null,
age int not null,
phone varchar(11) not null,
gender varchar(6) not null,
PRIMARY KEY (ID)
)
drop table Booking
Create Table Booking
(
ID int not NULL,
room_ID int not NULL,
cust_ID int not NULL,
checkIN date not NULL,
checkOUT date not NULL,
booking_status varchar(100) not NULL,
booking_date date not NULL,
PRIMARY KEY (ID),
FOREIGN KEY (room_ID) REFERENCES Rooms(ID),
FOREIGN KEY (cust_ID) REFERENCES Customers(ID)
)
drop table Billing
Create Table Billing
(
ID int NOT NULL,
room_ID int not NULL,
service_price int not NULL,
cust_ID int not NULL,
credit_card varchar(20) not NULL,
payment_date Date not NULL,
status varchar(50) not NULL,
FOREIGN KEY (room_ID) REFERENCES Rooms(ID),
FOREIGN KEY (service_price) REFERENCES Pricing(service_ID),
FOREIGN KEY (cust_ID) REFERENCES Customers(ID)
)
CREATE TABLE Services
(
ID int not null,
service varchar (100),
price int not NULL,
PRIMARY KEY(ID),
FOREIGN KEY(price) REFERENCES Pricing(service_ID)
)
CREATE TABLE ServicesAvailed
(
ID int not null,
cust_ID int not NULL,
service int not NULL,
price int not NULL,
PRIMARY KEY(ID),
FOREIGN KEY(cust_ID) REFERENCES Customers(ID),
FOREIGN KEY(service) REFERENCES Services(ID),
FOREIGN KEY(price) REFERENCES Pricing(service_ID)
)
insert into ServicesAvailed
values
(1,1,1,1),
(2,1,2,2)
insert into RoomTypes
Values
(1,'Standard'),
(2,'Moderate '),
(3,'Deluxe ')
insert into Services
Values
(1,'Laundry',1),
(2,'Airport Transport',2),
(3,'Standard Room',3),
(4,'Moderate Room',4),
(5,'Deluxe Room',5)
insert into Pricing
Values
(1,1000),
(2,2000),
(3,2000),
(4,4000),
(5,6000)
insert into Rooms
Values
(1,0,2,1,3),
(2,0,2,1,3),
(3,1,3,2,4),
(4,1,3,2,4),
(5,2,4,3,5),
(6,2,4,3,5)
insert into Customers
Values
(1,'Hamza','12345',20,'98765','Male'),
(2,'Ahmed','12345',20,'98765','Male'),
(3,'Irfan','12345',20,'98765','Male')
Insert Into Booking
Values
(1,1,1,'2020-12-31','2021-1-1','Booked','2020-12-31'),
(2,2,2,'2020-12-31','2021-1-1','Booked','2020-12-31')
Insert Into Billing
Values
(1,1,1,1,'5657446546','2020-12-31','Paid'),
(2,2,2,2,'5657446546','2020-12-31','Paid')
select * from Billing
select * from Booking
select * from Customers
select * from RoomTypes
select * from Rooms
select * from RoomPrices
select * from Booking
select * from Billing
select r.ID as RoomNumber,r.floor,r.bed,rt.*,p.price from Rooms r
inner join RoomTypes rt on r.roomType=rt.ID
inner join Pricing p on r.roomPrice=p.service_ID
select b.ID as BookingID,c.ID as CustomerID,c.name,b.booking_date,b.booking_status,b.checkIN,b.checkOUT, r.ID as roomID,r.floor,r.bed,rt.type,rp.price from Booking b
inner join Customers c on b.cust_ID=c.ID
inner join Rooms r on b.room_ID=r.ID
inner join RoomTypes rt ON r.roomType=rt.ID
inner join Pricing rp ON r.roomPrice= rp.service_ID
where c.name='Hamza'
select * from Billing
select c.ID as CustomerID,c.name,r.ID as roomID,r.floor,r.bed,rt.type,rp.price from Billing b
inner join Customers c on b.cust_ID=c.ID
inner join Rooms r on b.room_ID=r.ID
inner join RoomTypes rt ON r.roomType=rt.ID
inner join Pricing rp ON r.roomPrice= rp.service_ID
select sa.ID as NumberOFService,c.ID as CustomerID,c.name,s.service,p.price from ServicesAvailed sa
inner join Customers c on sa.cust_ID=c.ID
inner join Services s on sa.service=s.ID
inner join Pricing p on sa.price=p.service_ID