Hello I have a database in Microsoft SQL Server with tables relevant to a reservation system for a hotel/villa and need help creating a few queries to ontain relavant data:

To be able to find out a list of guests checking out on a specific date, grouped by villa type and providing a total number for that day (i.e. a count).

For that query I think I'd have to use 2 relevant tables a guest reservation and reservation table:

1. create table guest_reservation(
confirm_no int,
agent_id int,
g_name varchar (30),
g_phone varchar (10));

alter table guest_reservation
alter column confirm_no int
not null;

alter table guest_reservation
alter column agent_id int
not null;

alter table guest_reservation
alter column g_name varchar(30)
not null;

alter table guest_reservation
alter column g_phone varchar(10)
not null;

alter table guest_reservation
add constraint pk_guest_reservation primary key (confirm_no, agent_id, g_name, g_phone);

alter table guest_reservation
add constraint fk1_guest_reservation foreign key (confirm_no) references reservation
ON DELETE CASCADE;

alter table guest_reservation
add constraint fk2_guest_reservation foreign key (agent_ID) references agent
ON DELETE CASCADE;

alter table guest_reservation
add constraint fk3_guest_reservation foreign key (g_name, g_phone) references guest
ON DELETE CASCADE;

2. create table reservation(
confirm_no int,
credit_card_no char (16),
res_checkin_date datetime,
res_checkout_date datetime,
default_villa_type char (1),
price_plan char (1));

alter table reservation
alter column confirmation_no int
not null;

alter table reservation
add constraint pk_reservation primary key (confirmation_no);

alter table reservation
add constraint fk1_reservation foreign key (default_villa_type) references price_plan;

alter table reservation

add constraint fk2_reservation foreign key (price_plan) references price_plan;

I thought using a query like this would help, but it didn't seem to:

SELECT g_name, villa_type, COUNT(*) FROM guest_reservation, reservation
WHERE guest_reservation.confirm_no = reservation.confirm_no AND res_checkout_date = ‘insert date for when you would want to check out here’ GROUP BY villa_type;

Ideas/help?