Ejemplo de creación de Base de datos
-- base de datos slcent5
DROP DATABASE IF EXISTS slcent5;
CREATE DATABASE slcent5;
USE slcent5;
CREATE TABLE alumnos (
dni CHAR(12) NOT NULL PRIMARY KEY,
apellidos VARCHAR(60) NOT NULL,
nombre VARCHAR (30) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
ciclofp enum('DAM', 'DAW', 'ASIR', 'SMR', 'otros') NOT NULL,
sexo enum('H','M') NOT NULL,
fecha_nac DATE
)engine Innodb;
CREATE TABLE aulas(
codigoa CHAR(3) NOT NULL PRIMARY KEY,
metroscua tinyint UNSIGNED,
aforo tinyint CHECK(aforo >0 AND aforo<=40) NOT NULL DEFAULT 30
)engine Innodb;
CREATE TABLE ponentes(
nif CHAR(9) NOT NULL PRIMARY KEY,
nombre VARCHAR(70) NOT NULL,
telefono CHAR(14) NOT NULL UNIQUE,
colabora CHAR(9),
FOREIGN KEY fk_pon_col (colabora) REFERENCES ponentes(nif)
ON DELETE SET NULL
ON UPDATE cascade
)engine Innodb;
CREATE TABLE especialidad(
id tinyint AUTO_INCREMENT PRIMARY KEY, -- clave primaria más ligera que codigop+especialidad
nif CHAR(9) NOT NULL,
especialidad VARCHAR(50) NOT NULL,
UNIQUE uq_espec_nif_espec (nif, especialidad),
FOREIGN KEY fk_esp_nif (nif) REFERENCES ponentes(nif)
ON DELETE cascade
ON UPDATE cascade
)engine Innodb;
CREATE TABLE taller (
codigot tinyint AUTO_INCREMENT PRIMARY KEY,
titulo VARCHAR(60) NOT NULL UNIQUE,
minutos tinyint UNSIGNED DEFAULT 30,
fecha DATE,
hora_inicio TIME,
estrellas tinyint UNSIGNED NOT NULL DEFAULT 0,
nifp CHAR(9) NOT NULL,
aula CHAR(3),
FOREIGN KEY fk_talle_codp (nifp) REFERENCES ponentes(nif)
ON DELETE RESTRICT
ON UPDATE cascade,
FOREIGN KEY fk_talle_aul (aula) REFERENCES aulas(codigoa)
ON DELETE SET NULL
ON UPDATE cascade
)engine Innodb;
CREATE TABLE inscribe(
dni CHAR(12) NOT NULL,
codigot tinyint,
fecha_inscri DATE,
PRIMARY KEY(dni,codigot),
FOREIGN KEY fk_ins_dni (dni) REFERENCES alumnos(dni)
ON DELETE cascade
ON UPDATE cascade,
FOREIGN KEY fk_ins_cod(codigot) REFERENCES taller(codigot)
ON DELETE RESTRICT
ON UPDATE cascade
)engine Innodb;
CREATE TABLE votan (
dni CHAR(12) NOT NULL,
codigot tinyint,
fecha_hora datetime NOT NULL,
votos tinyint CHECK(votos >=0 AND votos <=10),
PRIMARY KEY (dni, codigot),
FOREIGN KEY fk_vot_dni (dni) REFERENCES alumnos(dni)
ON DELETE cascade
ON UPDATE cascade,
FOREIGN KEY fk_vot_cod(codigot) REFERENCES taller(codigot)
ON DELETE RESTRICT
ON UPDATE cascade
) engine Innodb;
USE slcent5;
-- datos ALUMNOS
INSERT INTO alumnos (dni, apellidos, nombre, email, ciclofp,sexo, fecha_nac) VALUES
('10101010P', 'Cruz García', 'Jaime', 'josecruz@gmail.com', 'DAM','H', '1992-11-18'),
('20202020Q', 'Sanz Mata', 'Laura', 'laura.mata@gmail.com', 'DAM','M', '2000-09-28'),
('30303030R', 'Sanz Rojo', 'Marta', 'marta.sanz@gmail.com', 'DAW','M', '2002-06-10'),
('40404040D', 'Cruz Castro', 'Carlos', 'carlos.cruz@hotmail.com', 'ASIR','H', '2015-04-20'),
('50505050V', 'López Sierra', 'Javier', 'javier.sierra@hotmail.com', 'ASIR','H', '2011-05-03'),
('60606060W', 'Granados Gil', 'Celia', 'celia.granados@gmail.com', 'DAW','M', '2002-12-28'),
('70707070X', 'López Lara', 'Sofía', 'sofia.lopez@hotmail.com', 'DAM','M', '2001-05-03'),
('80808080H', 'Aranda Clavijo', 'Juan', 'juan.aranda@gmail.com', 'ASIR','H', '1999-01-13'),
('90909090A', 'Bernal Navarro', 'Antonio', 'antonio.bernal@hotmail.com', 'DAW','H', '2002-07-14')
;
-- datos AULAS
INSERT INTO aulas(codigoa, metroscua,aforo) VALUES
('107',25,40), ('108',26,30), ('110',25,35),
('207',26,40), ('209',25,35), ('210',15,20);
-- datos PONENTES
INSERT INTO ponentes(nif, nombre, telefono, colabora) VALUES
('12345678Z', 'José Lara Bermejo', '636454545',NULL),
('11223344B', 'José Piedra Losilla', '666445533',NULL),
('23456789D', 'Antonio Torres Granados', '633554422','12345678Z'),
('44332211X', 'Víctor Castelo Gil', '655112233','11223344B'),
('98765432M', 'Alejandra Quero Monedero', '644557788','23456789D');
-- datos especialidad
INSERT INTO especialidad (nif, especialidad) VALUES
('12345678Z', 'Ciberseguridad'),
('12345678Z', 'Software documental'),
('12345678Z', 'Interfaces/Interación web 2.0'),
('12345678Z', 'Programación multimedia'),
('11223344B', 'Inteligencia Artificial'),
('11223344B', 'Visión Artificial'),
('11223344B', 'Robótica'),
('23456789D', 'Bases de Datos'),
('98765432M', 'Big Data');
-- datos TALLER
INSERT INTO taller (titulo, minutos, fecha, hora_inicio, estrellas,nifp, aula) VALUES
('Deep Learning con IA', 60, '2024-02-08', '16:30:00',0,'11223344B','207'),
('Generar contenido multimedia mediante IA', 60, '2024-02-08', '17:30:00',0,'98765432M','209'),
('IA en la Web', 120, '2024-02-08', '19:00:00',0,'23456789D','107'),
('Python y videojuegos retro', 120, '2024-02-09', '18:00:00',0,'44332211X','108'),
('Programando en Android/Kotlin', 60, '2024-02-09','17:00:00',0,'44332211X','207'),
('Caso práctico QRadar', 120, '2024-02-09', '18:00:00',0,'12345678Z','107'),
('Análisis de Datos con ELK', 60, '2024-02-07', '19:00:00',0,'11223344B','107')
;
-- datos inscribe
INSERT INTO inscribe (dni, codigot, fecha_inscri) VALUES
('10101010P',1, '2023-11-14'),('10101010P',3,'2023-11-16'),('10101010P',4,'2023-11-16'),
('20202020Q',1, '2023-11-17'), ('20202020Q',3, '2023-11-17'),
('20202020Q',4, '2023-11-18'),('20202020Q',5, '2023-11-18'), ('20202020Q',6, '2023-11-18'),
('40404040D',1, '2023-12-10'), ('40404040D',5, '2023-12-10'), ('40404040D',6, '2023-12-10'),
('70707070X',1, '2023-12-10'), ('70707070X',2, '2023-12-10'), ('70707070X',4, '2023-12-10'),
('60606060W',1, '2023-12-15'), ('60606060W',4, '2023-12-15'), ('60606060W',5, '2023-12-15'),
('90909090A',4, '2023-12-15'), ('90909090A',5, '2023-12-15'), ('90909090A',6, '2023-12-15'),
('50505050V',1, '2023-12-17'), ('50505050V',4, '2023-12-17'),('50505050V',5, '2023-12-17')
;
-- datos votan
INSERT INTO votan (dni, codigot, fecha_hora, votos) VALUES
('10101010P',1, '2024-02-12 13:00:00', 8), ('10101010P',4, '2024-02-12 13:05:00', 5),
('20202020Q',1, '2024-02-15 11:00:00', 6), ('20202020Q',5, '2024-02-17 13:00:00', 6),
('40404040D',1, '2024-02-17 12:00:00', 7), ('40404040D',6, '2024-02-17 12:00:00', 9),
('60606060W',1, '2024-02-17 12:00:00', 7), ('60606060W',5, '2024-02-17 12:00:00', 8),
('90909090A',4, '2024-02-18 12:00:00', 6), ('90909090A',6, '2024-02-18 12:00:00', 10);
commit;
DROP DATABASE IF EXISTS slcent5;
CREATE DATABASE slcent5;
USE slcent5;
CREATE TABLE alumnos (
dni CHAR(12) NOT NULL PRIMARY KEY,
apellidos VARCHAR(60) NOT NULL,
nombre VARCHAR (30) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
ciclofp enum('DAM', 'DAW', 'ASIR', 'SMR', 'otros') NOT NULL,
sexo enum('H','M') NOT NULL,
fecha_nac DATE
)engine Innodb;
CREATE TABLE aulas(
codigoa CHAR(3) NOT NULL PRIMARY KEY,
metroscua tinyint UNSIGNED,
aforo tinyint CHECK(aforo >0 AND aforo<=40) NOT NULL DEFAULT 30
)engine Innodb;
CREATE TABLE ponentes(
nif CHAR(9) NOT NULL PRIMARY KEY,
nombre VARCHAR(70) NOT NULL,
telefono CHAR(14) NOT NULL UNIQUE,
colabora CHAR(9),
FOREIGN KEY fk_pon_col (colabora) REFERENCES ponentes(nif)
ON DELETE SET NULL
ON UPDATE cascade
)engine Innodb;
CREATE TABLE especialidad(
id tinyint AUTO_INCREMENT PRIMARY KEY, -- clave primaria más ligera que codigop+especialidad
nif CHAR(9) NOT NULL,
especialidad VARCHAR(50) NOT NULL,
UNIQUE uq_espec_nif_espec (nif, especialidad),
FOREIGN KEY fk_esp_nif (nif) REFERENCES ponentes(nif)
ON DELETE cascade
ON UPDATE cascade
)engine Innodb;
CREATE TABLE taller (
codigot tinyint AUTO_INCREMENT PRIMARY KEY,
titulo VARCHAR(60) NOT NULL UNIQUE,
minutos tinyint UNSIGNED DEFAULT 30,
fecha DATE,
hora_inicio TIME,
estrellas tinyint UNSIGNED NOT NULL DEFAULT 0,
nifp CHAR(9) NOT NULL,
aula CHAR(3),
FOREIGN KEY fk_talle_codp (nifp) REFERENCES ponentes(nif)
ON DELETE RESTRICT
ON UPDATE cascade,
FOREIGN KEY fk_talle_aul (aula) REFERENCES aulas(codigoa)
ON DELETE SET NULL
ON UPDATE cascade
)engine Innodb;
CREATE TABLE inscribe(
dni CHAR(12) NOT NULL,
codigot tinyint,
fecha_inscri DATE,
PRIMARY KEY(dni,codigot),
FOREIGN KEY fk_ins_dni (dni) REFERENCES alumnos(dni)
ON DELETE cascade
ON UPDATE cascade,
FOREIGN KEY fk_ins_cod(codigot) REFERENCES taller(codigot)
ON DELETE RESTRICT
ON UPDATE cascade
)engine Innodb;
CREATE TABLE votan (
dni CHAR(12) NOT NULL,
codigot tinyint,
fecha_hora datetime NOT NULL,
votos tinyint CHECK(votos >=0 AND votos <=10),
PRIMARY KEY (dni, codigot),
FOREIGN KEY fk_vot_dni (dni) REFERENCES alumnos(dni)
ON DELETE cascade
ON UPDATE cascade,
FOREIGN KEY fk_vot_cod(codigot) REFERENCES taller(codigot)
ON DELETE RESTRICT
ON UPDATE cascade
) engine Innodb;
USE slcent5;
-- datos ALUMNOS
INSERT INTO alumnos (dni, apellidos, nombre, email, ciclofp,sexo, fecha_nac) VALUES
('10101010P', 'Cruz García', 'Jaime', 'josecruz@gmail.com', 'DAM','H', '1992-11-18'),
('20202020Q', 'Sanz Mata', 'Laura', 'laura.mata@gmail.com', 'DAM','M', '2000-09-28'),
('30303030R', 'Sanz Rojo', 'Marta', 'marta.sanz@gmail.com', 'DAW','M', '2002-06-10'),
('40404040D', 'Cruz Castro', 'Carlos', 'carlos.cruz@hotmail.com', 'ASIR','H', '2015-04-20'),
('50505050V', 'López Sierra', 'Javier', 'javier.sierra@hotmail.com', 'ASIR','H', '2011-05-03'),
('60606060W', 'Granados Gil', 'Celia', 'celia.granados@gmail.com', 'DAW','M', '2002-12-28'),
('70707070X', 'López Lara', 'Sofía', 'sofia.lopez@hotmail.com', 'DAM','M', '2001-05-03'),
('80808080H', 'Aranda Clavijo', 'Juan', 'juan.aranda@gmail.com', 'ASIR','H', '1999-01-13'),
('90909090A', 'Bernal Navarro', 'Antonio', 'antonio.bernal@hotmail.com', 'DAW','H', '2002-07-14')
;
-- datos AULAS
INSERT INTO aulas(codigoa, metroscua,aforo) VALUES
('107',25,40), ('108',26,30), ('110',25,35),
('207',26,40), ('209',25,35), ('210',15,20);
-- datos PONENTES
INSERT INTO ponentes(nif, nombre, telefono, colabora) VALUES
('12345678Z', 'José Lara Bermejo', '636454545',NULL),
('11223344B', 'José Piedra Losilla', '666445533',NULL),
('23456789D', 'Antonio Torres Granados', '633554422','12345678Z'),
('44332211X', 'Víctor Castelo Gil', '655112233','11223344B'),
('98765432M', 'Alejandra Quero Monedero', '644557788','23456789D');
-- datos especialidad
INSERT INTO especialidad (nif, especialidad) VALUES
('12345678Z', 'Ciberseguridad'),
('12345678Z', 'Software documental'),
('12345678Z', 'Interfaces/Interación web 2.0'),
('12345678Z', 'Programación multimedia'),
('11223344B', 'Inteligencia Artificial'),
('11223344B', 'Visión Artificial'),
('11223344B', 'Robótica'),
('23456789D', 'Bases de Datos'),
('98765432M', 'Big Data');
-- datos TALLER
INSERT INTO taller (titulo, minutos, fecha, hora_inicio, estrellas,nifp, aula) VALUES
('Deep Learning con IA', 60, '2024-02-08', '16:30:00',0,'11223344B','207'),
('Generar contenido multimedia mediante IA', 60, '2024-02-08', '17:30:00',0,'98765432M','209'),
('IA en la Web', 120, '2024-02-08', '19:00:00',0,'23456789D','107'),
('Python y videojuegos retro', 120, '2024-02-09', '18:00:00',0,'44332211X','108'),
('Programando en Android/Kotlin', 60, '2024-02-09','17:00:00',0,'44332211X','207'),
('Caso práctico QRadar', 120, '2024-02-09', '18:00:00',0,'12345678Z','107'),
('Análisis de Datos con ELK', 60, '2024-02-07', '19:00:00',0,'11223344B','107')
;
-- datos inscribe
INSERT INTO inscribe (dni, codigot, fecha_inscri) VALUES
('10101010P',1, '2023-11-14'),('10101010P',3,'2023-11-16'),('10101010P',4,'2023-11-16'),
('20202020Q',1, '2023-11-17'), ('20202020Q',3, '2023-11-17'),
('20202020Q',4, '2023-11-18'),('20202020Q',5, '2023-11-18'), ('20202020Q',6, '2023-11-18'),
('40404040D',1, '2023-12-10'), ('40404040D',5, '2023-12-10'), ('40404040D',6, '2023-12-10'),
('70707070X',1, '2023-12-10'), ('70707070X',2, '2023-12-10'), ('70707070X',4, '2023-12-10'),
('60606060W',1, '2023-12-15'), ('60606060W',4, '2023-12-15'), ('60606060W',5, '2023-12-15'),
('90909090A',4, '2023-12-15'), ('90909090A',5, '2023-12-15'), ('90909090A',6, '2023-12-15'),
('50505050V',1, '2023-12-17'), ('50505050V',4, '2023-12-17'),('50505050V',5, '2023-12-17')
;
-- datos votan
INSERT INTO votan (dni, codigot, fecha_hora, votos) VALUES
('10101010P',1, '2024-02-12 13:00:00', 8), ('10101010P',4, '2024-02-12 13:05:00', 5),
('20202020Q',1, '2024-02-15 11:00:00', 6), ('20202020Q',5, '2024-02-17 13:00:00', 6),
('40404040D',1, '2024-02-17 12:00:00', 7), ('40404040D',6, '2024-02-17 12:00:00', 9),
('60606060W',1, '2024-02-17 12:00:00', 7), ('60606060W',5, '2024-02-17 12:00:00', 8),
('90909090A',4, '2024-02-18 12:00:00', 6), ('90909090A',6, '2024-02-18 12:00:00', 10);
commit;
Ejemplo1: consultas con explicación
--1 Muestra el username, email y el fecha_registro de todos los oyentes registrados después del año 2022. (Usa la función YEAR() si es necesario). --
SELECT username, email, fecha_registro FROM OYENTE
WHERE YEAR (fecha_registro) > 2022
--2 Lista Muestra el 'nombre - descripción' en una misma columna y la categoria padre, de todos las categorias que tienen categoria padre. Ordena los resultados por nombre categoria.(Usa la función CONCAT() para concatenar 'nombre - descripcion'. Pon alias de columna donde proceda para mayor legibilidad). --
SELECT CONCAT(nombre,' - ' , descripcion) AS 'Nombre y Descripción', categoria_padre FROM CATEGORIA
WHERE categoria_padre IS NOT NULL
ORDER BY categoria_padre;
--3 Listado nombre, descripcion de aquellos programas que tengan una duración de una hora. Solo puedes utilizar la funcion HOUR(). --
SELECT nombre, descripcion FROM PROGRAMA
WHERE HOUR(h_fin) - HOUR(h_inicio) = 1;
--4 Listado con todos los datos de los programas que su nombre contiene la palabra 'Tech' en su nombre y el programa termine antes de las 2:00pm. --
SELECT nombre, descripcion, h_fin FROM PROGRAMA
WHERE nombre LIKE '%Tech%' AND HOUR(h_fin) < 14;
--5 Lista el título de las notas, indicando su resumen y el nombre de la categoria a la que pertenece y cuyo título termine con las palabras 'expertos' o 'deportivo'. Pon alias de columna dónde sea necesario. Ordena el listado por nombre de categoria descendentemente. --
SELECT n.titulo, n.resumen, cat.nombre FROM NOTA n JOIN NOTA_POSEE_CATEGORIA np
ON n.titulo=np.titulo_nota INNER JOIN CATEGORIA cat ON np.nombre_cat=cat.nombre
WHERE n.titulo LIKE '%expertos' OR n.titulo LIKE '%deportivo'
ORDER BY nombre DESC;
--6 Lista el título, contenido de las notas, junto al programa que pertenece, hora de inicio y nombre del conductor del programa. Ordena el listado alfabéticamente por título y pon alias de columna dónde sea necesario. --
SELECT n.titulo, n.contenido AS 'Contenido Notas', n.nombre_prog AS 'Nombre Programa', p.h_inicio AS 'Hora Inicio', p.h_fin AS 'Hora Fin', c.conductor FROM NOTA n JOIN PROGRAMA p
ON n.nombre_prog=p.nombre
JOIN CONDUCTOR c ON p.nombre=c.nombre_pro
ORDER BY titulo;
--7 Lista el username y email de los oyentes que hayan realizado comentarios, indicando además el texto del comentario, el título de la nota a la que pertenece ese comentario y el nombre del programa de dicha nota. Ordena el listado alfabéticamente por nombre de programa. --
SELECT o.username, o.email , c.texto, c.titulo_nota AS "Titulo Nota", n.nombre_prog AS "Nombre Programa" FROM OYENTE o JOIN COMENTARIO c
ON o.email=c.email_oyente JOIN NOTA n ON c.titulo_nota=n.titulo
WHERE c.texto IS NOT NULL
ORDER BY nombre_prog;
--8 Listado con el total de comenterios que hace cada oyente. En el listado debe aparecer el username, el email y el total de comentarios del oyente. --
SELECT o.username, o.email, (SELECT COUNT(c.texto) FROM COMENTARIO c WHERE o.email=c.email_oyente) FROM OYENTE o JOIN COMENTARIO c
ON o.email=c.email_oyente;
--9 Listado de categorias y el total de notas que tiene cada categoria. Ordena por el total de categorias, de más a menos y pon álias de columna dónde proceda.
SELECT c.nombre, (SELECT COUNT(npc.titulo_nota) FROM NOTA_POSEE_CATEGORIA npc WHERE npc.nombre_cat=c.nombre) AS "Numero notas"
FROM CATEGORIA c;
-- A continuación haz una versión b) filtrando para que el listado solo muestre las categorias con un total de 2 notas o más asociadas a la categoria. Ordena de menos a más categorías. --
SELECT c.nombre, (SELECT COUNT(npc.titulo_nota) FROM NOTA_POSEE_CATEGORIA npc WHERE npc.nombre_cat=c.nombre) AS "Numero notas"
FROM CATEGORIA c
WHERE (SELECT COUNT(npc.titulo_nota) FROM NOTA_POSEE_CATEGORIA npc WHERE npc.nombre_cat=c.nombre)>=2;
--10 Para cada nota, lista el programa al que pertence y el total de conductores por cada programa.
SELECT n.titulo AS "Titulo Nota", n.nombre_prog AS "Nombre Programa",(SELECT COUNT(c.conductor) FROM CONDUCTOR c WHERE c.nombre_pro=p.nombre) AS "Numero Conductores"
FROM NOTA n JOIN PROGRAMA p ON n.nombre_prog=p.nombre;
-- Limita el listado para mostrar dos de los talleres con más colaboradores en total. --
SELECT n.titulo AS "Titulo Nota", n.nombre_prog AS "Nombre Programa",(SELECT COUNT(c.conductor) FROM CONDUCTOR c WHERE c.nombre_pro=p.nombre) AS "Numero Conductores"
FROM NOTA n JOIN PROGRAMA p ON n.nombre_prog=p.nombre LIMIT 2;
SELECT username, email, fecha_registro FROM OYENTE
WHERE YEAR (fecha_registro) > 2022
--2 Lista Muestra el 'nombre - descripción' en una misma columna y la categoria padre, de todos las categorias que tienen categoria padre. Ordena los resultados por nombre categoria.(Usa la función CONCAT() para concatenar 'nombre - descripcion'. Pon alias de columna donde proceda para mayor legibilidad). --
SELECT CONCAT(nombre,' - ' , descripcion) AS 'Nombre y Descripción', categoria_padre FROM CATEGORIA
WHERE categoria_padre IS NOT NULL
ORDER BY categoria_padre;
--3 Listado nombre, descripcion de aquellos programas que tengan una duración de una hora. Solo puedes utilizar la funcion HOUR(). --
SELECT nombre, descripcion FROM PROGRAMA
WHERE HOUR(h_fin) - HOUR(h_inicio) = 1;
--4 Listado con todos los datos de los programas que su nombre contiene la palabra 'Tech' en su nombre y el programa termine antes de las 2:00pm. --
SELECT nombre, descripcion, h_fin FROM PROGRAMA
WHERE nombre LIKE '%Tech%' AND HOUR(h_fin) < 14;
--5 Lista el título de las notas, indicando su resumen y el nombre de la categoria a la que pertenece y cuyo título termine con las palabras 'expertos' o 'deportivo'. Pon alias de columna dónde sea necesario. Ordena el listado por nombre de categoria descendentemente. --
SELECT n.titulo, n.resumen, cat.nombre FROM NOTA n JOIN NOTA_POSEE_CATEGORIA np
ON n.titulo=np.titulo_nota INNER JOIN CATEGORIA cat ON np.nombre_cat=cat.nombre
WHERE n.titulo LIKE '%expertos' OR n.titulo LIKE '%deportivo'
ORDER BY nombre DESC;
--6 Lista el título, contenido de las notas, junto al programa que pertenece, hora de inicio y nombre del conductor del programa. Ordena el listado alfabéticamente por título y pon alias de columna dónde sea necesario. --
SELECT n.titulo, n.contenido AS 'Contenido Notas', n.nombre_prog AS 'Nombre Programa', p.h_inicio AS 'Hora Inicio', p.h_fin AS 'Hora Fin', c.conductor FROM NOTA n JOIN PROGRAMA p
ON n.nombre_prog=p.nombre
JOIN CONDUCTOR c ON p.nombre=c.nombre_pro
ORDER BY titulo;
--7 Lista el username y email de los oyentes que hayan realizado comentarios, indicando además el texto del comentario, el título de la nota a la que pertenece ese comentario y el nombre del programa de dicha nota. Ordena el listado alfabéticamente por nombre de programa. --
SELECT o.username, o.email , c.texto, c.titulo_nota AS "Titulo Nota", n.nombre_prog AS "Nombre Programa" FROM OYENTE o JOIN COMENTARIO c
ON o.email=c.email_oyente JOIN NOTA n ON c.titulo_nota=n.titulo
WHERE c.texto IS NOT NULL
ORDER BY nombre_prog;
--8 Listado con el total de comenterios que hace cada oyente. En el listado debe aparecer el username, el email y el total de comentarios del oyente. --
SELECT o.username, o.email, (SELECT COUNT(c.texto) FROM COMENTARIO c WHERE o.email=c.email_oyente) FROM OYENTE o JOIN COMENTARIO c
ON o.email=c.email_oyente;
--9 Listado de categorias y el total de notas que tiene cada categoria. Ordena por el total de categorias, de más a menos y pon álias de columna dónde proceda.
SELECT c.nombre, (SELECT COUNT(npc.titulo_nota) FROM NOTA_POSEE_CATEGORIA npc WHERE npc.nombre_cat=c.nombre) AS "Numero notas"
FROM CATEGORIA c;
-- A continuación haz una versión b) filtrando para que el listado solo muestre las categorias con un total de 2 notas o más asociadas a la categoria. Ordena de menos a más categorías. --
SELECT c.nombre, (SELECT COUNT(npc.titulo_nota) FROM NOTA_POSEE_CATEGORIA npc WHERE npc.nombre_cat=c.nombre) AS "Numero notas"
FROM CATEGORIA c
WHERE (SELECT COUNT(npc.titulo_nota) FROM NOTA_POSEE_CATEGORIA npc WHERE npc.nombre_cat=c.nombre)>=2;
--10 Para cada nota, lista el programa al que pertence y el total de conductores por cada programa.
SELECT n.titulo AS "Titulo Nota", n.nombre_prog AS "Nombre Programa",(SELECT COUNT(c.conductor) FROM CONDUCTOR c WHERE c.nombre_pro=p.nombre) AS "Numero Conductores"
FROM NOTA n JOIN PROGRAMA p ON n.nombre_prog=p.nombre;
-- Limita el listado para mostrar dos de los talleres con más colaboradores en total. --
SELECT n.titulo AS "Titulo Nota", n.nombre_prog AS "Nombre Programa",(SELECT COUNT(c.conductor) FROM CONDUCTOR c WHERE c.nombre_pro=p.nombre) AS "Numero Conductores"
FROM NOTA n JOIN PROGRAMA p ON n.nombre_prog=p.nombre LIMIT 2;
