Q.1 For a given date, say 1 Oct 2017, list all the coaches booked for that day,

Question

Q.1 For a given date, say 1 Oct 2017, list all the coaches booked for that day, the corresponding booked coaching timeslot and venue, and the name of the client. The output should be properly sorted.

Expecting answere in SQL namagement studio

create table coach (
coachId varchar (20) primary key ,
coachName varchar (20),
email varchar (20),
phone varchar (20),
address varchar (20),
postcode varchar (20)
);
create table Venue (
venueId varchar (20) primary key,
venueName varchar (20),
venneTime time,
telephone varchar (20),
postcode varchar (4) not null,
suburb varchar (20)
);
create table booking2 (
bookingId varchar (20) primary key ,
coachId varchar (20),
trainingId varchar (20),
bookingDate date,
clientId varchar (20),
venueId varchar (20),
BookingHour numeric,
constraint booking_fk FOREIGN KEY (coachId) REFERENCES coach(coachID),
constraint venue_fk FOREIGN KEY (venueId) REFERENCES Venue(venueId)
);

select * from Venue
select * from coach
Select * from booking2

insert into coach values ( 12, 'mark', 'mark@outlook.com', '12377978', '14 Jone St', '2060');
insert into coach values ( 13, 'Ricky', 'Ricky@live.com', '12345678', '18 Wiliam St', '2719');
insert into coach values ( 14, 'Shane', 'Shane@yahoo.com', '12300678', '28 Arid St', '2194');
insert into coach values ( 15, 'Brian', 'Brian@yahoo.com', '12300678', '27 rhodes Rd', '2943');

insert into Venue values ( 87, 'olympic Park', '10:30', '0362818391', '2134','Ryde' );
insert into Venue values ( 88, 'ymca pool', '10:00', '0357935678', '2144','Westmead' );
insert into Venue values ( 89, 'anthony court', '11:00', '0924826538', '2148','Auburn' );
insert into Venue values ( 90, 'olympic Park', '11:30', '0362818391', '2134','Ryde' );

insert into booking2 values ( 195, '12', '111', '10-OCT-01', '101', '87', '2' );
insert into booking2 values ( 196, '13', '222', '10-OCT-01', '102' ,'88', '1' );
insert into booking2 values ( 197 , '14', '333', '09-SEP-03', '101' ,'89', '1' );
insert into booking2 values ( 198 , '15', '444', '10-OCT-09' , '104' ,'90' , '2');

Explanation / Answer

SELECT c.coachId, c.coachName, c.coachName, c.phone, c.address, c.postcode, b.bookingId, b.bookingDate, b.BookingHour ,v.venueId, v.venueName, v.venneTime, v.telephone, v.postcode, v.suburb
FROM coach c
JOIN booking2 b
ON c.coachId = b.coachId
JOIN Venue v
ON b.venueId = v.venueId
WHERE b.bookingDate = '01-OCT-17'
ORDER BY c.coachId, b.bookingId, v.venueId ASC;

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.