Lab11 CC5

Lab11

create database lab11;
use lab11;

create table marinero (
	sid int,
	name varchar(50) not null,
	rating int not null,
	age numeric(4,2),
	primary key(sid)
);

create table bote(
	bid int,
	name varchar(50) not null,
	color varchar(50) not null,
	primary key(bid)
);

create table reserva (
	sid int,
	bid int,
	date date,
	primary key(sid, bid),
	foreign key(sid) references marinero(sid),
	foreign key(bid) references bote(bid)
);

insert into marinero(sid,name,rating,age) values (22,'Dustin',7,45);
insert into marinero(sid,name,rating,age) values (29,'Brutus',1,33);
insert into marinero(sid,name,rating,age) values (31,'Lubber',8,55.5);
insert into marinero(sid,name,rating,age) values (32,'Andy',8,25.5);
insert into marinero(sid,name,rating,age) values (58,'Rusty',10,35);
insert into marinero(sid,name,rating,age) values (64,'Horatio',7,35);
insert into marinero(sid,name,rating,age) values (71,'Zorba',10,16);
insert into marinero(sid,name,rating,age) values (74,'Horatio',9,35);
insert into marinero(sid,name,rating,age) values (85,'Art',3,25.5);
insert into marinero(sid,name,rating,age) values (95,'Bob',3,63.5);

insert into bote(bid, name, color) values(101,'Interlake','blue');
insert into bote(bid, name, color) values(102,'Interlake','red');
insert into bote(bid, name, color) values(103,'Clipper','green');
insert into bote(bid, name, color) values(104,'Marine','red');

insert into reserva(sid, bid,date) values(22,101,STR_TO_DATE('10/10/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(22,102,STR_TO_DATE('10/10/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(22,103,STR_TO_DATE('10/08/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(22,104,STR_TO_DATE('10/07/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(31,102,STR_TO_DATE('11/10/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(31,103,STR_TO_DATE('11/06/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(31,104,STR_TO_DATE('11/12/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(64,101,STR_TO_DATE('09/05/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(64,102,STR_TO_DATE('09/08/98', '%d/%m/%y'));
insert into reserva(sid, bid,date) values(74,103,STR_TO_DATE('09/08/98', '%d/%m/%y'));

#A
select 	sid,name,rating from marinero where age >= 18;

#B
select bid, name from bote where color not in('red', 'blue');

#C
select m.sid, m.name from marinero m, reserva r, bote b  where m.sid = r.sid and b.bid = r.bid and b.name = 'Interlake';

#D
select count(*) as marineros, sum(age) as sumatoria , avg(age) as promedio from marinero;

#E
select name, count(*) from marinero group by name;

#F
select m.* from marinero m, reserva r, bote b  where m.sid = r.sid and b.bid = r.bid and b.color = 'red'; 

#G 
select m.* from marinero m where age > (select avg(age) from marinero);

#H 
select b.* from bote b where (select count(*) from reserva r where b.bid = r.bid ) > 2;

#I
select m.* from marinero m where (select count(*) from reserva r where m.sid = r.sid ) = 0;

#J
select m.* from marinero m where not exists (select b.bid from bote b where not exists (select * from reserva r where r.bid = b.bid and r.sid = m.sid));