(1) List all the coaches and their respective total number of coaching hours tha

Question

(1) List all the coaches and their respective total number of coaching hours that are ever booked for.

Expecting answere in SQL management Studio, if need to do any changes with table can be done but result should come from one table it should be from two table

create table coach (

coachId varchar (20) primary key ,
coachName char (20),
email varchar (20),
phone varchar (10),
address varchar (20),
postcode varchar (4)
);

create table booking2 (
bookingId varchar (20) primary key ,
coachId varchar (20),
trainingId varchar (20),
bookingDate datetime,
clientId varchar (20),
venueId varchar (20),
BookingHour time,
constraint booking_fk FOREIGN KEY (coachId) REFERENCES coach,
);
insert into coach values ( 11, 'mark', 'mark@outlook.com', '12377978', '14/goulbane', '3022');
insert into coach values ( 22, 'john', 'john@live.com', '12345678', '15/goulbane', '3023');
insert into coach values ( 33, 'micky', 'micky@yahoo.com', '12300678', '16/goulbane', '3024');

insert into booking2 values ( 19, '22', '111', '13-DEC-03', '101', '101', '4' );
insert into booking2 values ( 20, '33', '222', '13-NOV-03', '102' ,'101', '3' );
insert into booking2 values ( 21 , '22', '333', '15-DEC-16', '101' ,'101', '5' );
insert into booking2 values ( 22 , '11', '444', '18-OCT-02' , '104' ,'101' , '6');

Explanation / Answer

This is based on Oracle DB , since i dont have SQL MS with me, but wont be an issue.

Here is the DDL statement( made few changes):

create table coach (
coachId varchar (20) primary key ,
coachName varchar (20),
email varchar (20),
phone varchar (10),
address varchar (20),
postcode varchar (4)
);

Changed coachName from Char to varchar.

create table booking2 (
bookingId varchar (20) primary key ,
coachId varchar (20),
trainingId varchar (20),
bookingDate date,
clientId varchar (20),
venueId varchar (20),
BookingHour number,
constraint booking_fk FOREIGN KEY (coachId) REFERENCES coach(coachID)
);

Changed the BookingHour datatype from time to number and FK constraint sytax was incomplete, changed that too.

Query:

This lists all the coach's with there respective Booking Hour. The two tables are joined based on coachID. Since we are using aggrate function, we need to provide group by function.

select c.coachid,c.coachName,sum(bk.bookingHour)
from coach c, booking2 bk
where
c.coachid = bk.coachid
group by
c.coachid,c.coachName;

OUTPUT:

CoachID Name SUM

22 john 9
11 mark 6
33 micky 3

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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