Created
March 31, 2015 22:59
-
-
Save baquiax/4dc52d21452e24b8cf7a to your computer and use it in GitHub Desktop.
Lab11 CC5
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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)); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment