Lab 09 CCV

lab09

CREATE DATABASE if not exists lab9;
USE lab9;

create table if not exists estudiante (
	carne SMALLINT NOT NULL,
	nombre varchar(100),
	especialidad varchar(100),
	ciclo varchar(10),
	edad TINYINT,
	primary key (carne)
) ENGINE = InnoDB;

create table if not exists curso (
	curso varchar(8) NOT NULL,
	nombre varchar(100),
	horario varchar(10),
	aula varchar(10),
	primary key(curso)
) ENGINE = InnoDB;

create table if not exists asignaciones(
	carne SMALLINT ,
	curso varchar(8),
	puesto TINYINT,
	primary key (carne, curso),
	foreign key (carne) references estudiante(carne),
	foreign key (curso) references curso(curso)
) ENGINE = InnoDB;

INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (100, 'Juarez', 'Historia', '5o', 21);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (150, 'Cruz', 'Contabilidad', '3o', 19);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (200, 'Batres', 'Matematica', '5o', 30);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (250, 'Garcia', 'Contabilidad', '1o' , 30);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (300, 'Batres', 'Contabilidad', '7o', 30);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (350, 'Rodas', 'Matematica','1o', 20);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (400, 'Rios', 'Contabilidad', '5o', 28);
INSERT INTO estudiante(carne, nombre, especialidad, ciclo, edad) VALUES (450, 'Juarez', 'Historia', '3o', 24);

INSERT INTO curso(curso, nombre, horario, aula) VALUES('CCV', 'Ciencias de la Computacion V', 'LJ9', 'A101');
INSERT INTO curso(curso, nombre, horario, aula) VALUES('CCIII', 'Ciencias de la Computacion III', 'MJS3', 'A101');
INSERT INTO curso(curso, nombre, horario, aula) VALUES('CCI', 'Ciencias de la Computacion I', 'LMV8', 'D303');
INSERT INTO curso(curso, nombre, horario, aula) VALUES('MV', 'Matematica V', 'LMJ3', 'E604');
INSERT INTO curso(curso, nombre, horario, aula) VALUES('MIII', 'Matematica III', 'MJV12', 'A401');

INSERT INTO asignaciones(carne, curso, puesto) VALUES(100, 'CCIII', 1);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(150, 'CCV', 1);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(200, 'CCIII', 2);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(200, 'MIII', 1);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(300, 'MV', 1);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(400, 'CCV', 2);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(400, 'CCI', 1);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(400, 'MIII', 2);
INSERT INTO asignaciones(carne, curso, puesto) VALUES(450, 'CCV', 3);


#QUERIES

select * from estudiante;
select e.nombre, e.especialidad from estudiante e;
select e.carne, e.nombre, e.ciclo from estudiante e order by e.ciclo;
select distinct e.especialidad from estudiante e;
select e.nombre, e.ciclo from estudiante e where e.especialidad = 'Matematica';
select e.nombre, e.edad from estudiante e where e.especialidad = 'Historia' and e.ciclo = '5o';
select e.nombre, e.carne from estudiante e where e.ciclo = '7o' or e.edad > 21;
select e.nombre from estudiante e where e.especialidad in ('Matematica', 'Contabilidad');
select e.nombre from estudiante e where e.clilo not in ('5o','7o');
select e.nombre, e.especialidad, e.edad from estudiante e where e.ciclo = '3o' order by e.nombre;
select e.nombre, e.edad from estudiante e where e.ciclo in ('3o','5o', '7o') order by e.especialidad, e.edad desc;
select e.nombre, e.carne from estudiante e where e.nombre like 'B%' or e.nombre like 'R%';
select e.nombre, e.carne, e.especialidad, count(*) as cursos from estudiante e, asignaciones a where e.carne = a.carne group by e.nombre, e.carne, e.especialidad;
select c.nombre, e.nombre, e.carne from estudiante e, asignaciones a, curso c where e.carne = a.carne and a.puesto = 1 and a.curso = c.curso;
select c.nombre, c.horario, c.aula, count(*) as estudiantes from curso c, asignaciones a where c.curso = a.curso group by c.nombre, c.horario, c.aula;
select count(carne) as nEstudiantes from estudiante;
select count(*) as nEspecialidades from (select distinct especialidad from estudiante) as especialidades;
select c.nombre, c.horario, c.aula, count(*) from curso c , asignaciones a where c.curso = a.curso group by c.nombre, c.horario, c.aula having count(*) = (select max(count) from (select count(*) as count from asignaciones group by curso) as all2); 
select e.especialidad, count(*) from estudiante e group by e.especialidad;
select e.especialidad, count(*) from estudiante e group by e.especialidad having count(*) > 2;
select e.especialidad, avg(e.edad) from estudiante e , asignaciones a where e.carne = a.carne and a.curso in (select curso from (select a2.curso, count(*) from asignaciones a2 group by a2.curso having count(*) > 1) a1) group by e.especialidad;
select e.nombre, e.carne , c.horario, c.aula, e.ciclo from estudiante e, curso c, asignaciones a where e.carne = a.carne and a.curso = c.curso and e.ciclo in ('5o','3o') order by e.ciclo, e.nombre;
select distinct c.nombre, (select min(e.edad) from asignaciones a, estudiante e where a.carne = e.carne and c.curso = a.curso) from curso c;
select e.nombre, e.carne from estudiante e, curso c, asignaciones a where e.carne = a.carne and a.curso = c.curso and c.aula = 'A101';
select c.horario from estudiante e, curso c, asignaciones a where e.carne = a.carne and a.curso = c.curso and e.especialidad = 'Historia';