Skip to content

Instantly share code, notes, and snippets.

@Dalboz

Dalboz/videoclub.sql

Created September 29, 2020 15:02
Show Gist options
  • Save Dalboz/f7c10104a73414b89d449ff291c02368 to your computer and use it in GitHub Desktop.
Save Dalboz/f7c10104a73414b89d449ff291c02368 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
videoclub.sql
/* 1. Crear: base de datos y seleccionarla */
CREATE DATABASE IF NOT EXISTS videoclub;
USE videoclub;
SET character_set_server = utf8mb4;
SET collation_server = 'utf8mb4_general_ci';
/* 2. Crear tabla: socio */
CREATE TABLE socios (
codigo VARCHAR(10) NOT NULL,
nombre VARCHAR(20) NOT NULL,
apellidos VARCHAR(30) NOT NULL,
direccion VARCHAR(40) NOT NULL,
telefono INT UNSIGNED
);
/* 3. Crear tabla: alquileres */
CREATE TABLE alquileres (
cod_socio VARCHAR(10) NOT NULL,
cod_pelicula VARCHAR(10) NOT NULL,
fecha_alquiler DATE NOT NULL,
dias_alquiler INT UNSIGNED NOT NULL
);
/* 4. Crear tabla: peliculas */
CREATE TABLE peliculas (
codigo VARCHAR(10) NOT NULL,
titulo VARCHAR(60) NOT NULL,
estilo VARCHAR(40) NOT NULL
);
/* 5.1 Añadir claves primarias */
ALTER TABLE socios ADD PRIMARY KEY (codigo);
ALTER TABLE alquileres ADD PRIMARY KEY (cod_socio,cod_pelicula,fecha_alquiler);
ALTER TABLE peliculas ADD PRIMARY KEY (codigo);
/* 5.2 Añadir claves foraneas */
ALTER TABLE alquileres ADD FOREIGN KEY (cod_socio) REFERENCES socios(codigo);
ALTER TABLE alquileres ADD FOREIGN KEY (cod_pelicula) REFERENCES peliculas(codigo);
/* 6. Inicio: inserción de datos */
INSERT INTO socios VALUES
('C_1','Rosa','Pérez Pérez', 'Del Valle', 333444455),
('C_2','Enrique','García López', 'Del Valle', ''),
('C_3','Juan','Sánchez Gil', 'Paseo Recoletos', 8888888),
('C_4','Ana','Rodríguez Pla', 'De la Luz', 5555555);
INSERT INTO peliculas VALUES
('P_1','El rey león', 'Infantil'),
('P_2','Hotel Transilvania', 'Infantil'),
('P_3','La Naranja Mecánica','Terror'),
('P_4','El Jovencito Frankestein','Comedia');
INSERT INTO alquileres VALUES
('C_1','P_2','20190112', 3),
('C_2','P_2','20190212', 5),
('C_3','P_2','20191112', 2),
('C_1','P_3','20190301', 10),
('C_2','P_1','20190123', 4),
('C_2','P_4','20190317', 6);
/* Fin de definición de datos */
/* Inicio de consultas */
/* 7. Consulta: titulo, genero de peliculas por socios de la calle del valle */
SELECT
peliculas.titulo,
peliculas.estilo
FROM
alquileres
INNER JOIN socios ON socios.codigo = alquileres.cod_socio
INNER JOIN peliculas ON peliculas.codigo = alquileres.cod_pelicula
WHERE socios.direccion like '%del valle%'
GROUP BY peliculas.estilo;
/* 8. Consulta: listado con los alquileres (todos los datos) del socio Enrique García López */
SELECT
alquileres.cod_socio,
socios.nombre,
socios.apellidos,
alquileres.cod_pelicula,
peliculas.titulo,
alquileres.fecha_alquiler,
alquileres.dias_alquiler
FROM
alquileres
INNER JOIN peliculas ON peliculas.codigo = alquileres.cod_pelicula
INNER JOIN socios ON socios.codigo = alquileres.cod_socio
WHERE
CONCAT(socios.nombre, ' ', socios.apellidos) LIKE '%Enrique García López%';
/* 9. Consulta: listado de socios que han realizado alquileres de peliculas infantiles
El listado mostrará: Apellidos, nombre, y telefono ordenado alfabéticamente por apellidos y nombre */
SELECT DISTINCT
socios.apellidos,
socios.nombre,
socios.telefono
FROM
alquileres
INNER JOIN peliculas ON peliculas.codigo = alquileres.cod_pelicula
INNER JOIN socios ON socios.codigo = alquileres.cod_socio
WHERE
peliculas.estilo = 'Infantil'
ORDER BY socios.apellidos,socios.nombre ASC;
/* 10. Consulta: obtener el nombre completo de todos los socios que han alquilado la pelicula HOTEL TRANSILVANIA */
SELECT
CONCAT(
socios.nombre,
' ',
socios.apellidos
) AS 'Nombre completo'
FROM
alquileres
INNER JOIN peliculas ON peliculas.codigo = alquileres.cod_pelicula
INNER JOIN socios ON socios.codigo = alquileres.cod_socio
WHERE
peliculas.titulo LIKE '%HOTEL TRANSILVANIA%';
/* 11. Consulta: obtener el nombre completo de los socios que han alquilado peliculas por mas de 3 dias */
SELECT
CONCAT(
socios.nombre,
' ',
socios.apellidos
) AS 'Nombre completo'
FROM
socios
INNER JOIN(
SELECT
cod_socio
FROM
alquileres
WHERE
dias_alquiler > 3
GROUP BY
cod_socio
) AS p
ON
socios.codigo = p.cod_socio;
/* 12. Consulta: obtener un listado de socios (Apellido, Nombre y telefono) que no han alquilado ninguna pelicula */
SELECT
socios.apellidos,
socios.nombre,
socios.telefono
FROM
socios
WHERE
socios.codigo NOT IN(
SELECT
alquileres.cod_socio
FROM
alquileres
);
/* 13. Consulta: obtener el codigo y el titulo de las peliculas alquiladas por Enrique García López y Juan Sánchez Gil */
(
SELECT DISTINCT
alquileres.cod_pelicula,
peliculas.titulo
FROM
alquileres
INNER JOIN peliculas ON peliculas.codigo = alquileres.cod_pelicula
INNER JOIN socios ON socios.codigo = alquileres.cod_socio
WHERE
CONCAT(
socios.nombre,
' ',
socios.apellidos
) LIKE '%Enrique García López%' OR '%Juan Sánchez Gil%'
);
/* Fin de la actividad */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment