Categoría: BD

Consultas crear vistas

create view customer_over_avg as
SELECT first_name, last_name, COUNT(*) AS rental_count
FROM rental join customer on rental.customer_id=customer.customer_id
GROUP BY customer.customer_id
HAVING rental_count > (SELECT AVG(rental_count) FROM rental_client);

create view filmografia as
select name, film.*,first_name, last_name from category join film_category on category.category_id=film_category.category_id
join film on film.film_id=film_category.film_id
join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id

Soluciones sql

insert into actor (first_name, last_name)
values ('juan','perez');

insert into actor (first_name, last_name)
values ('rosa','pi');

update actor set first_name='PEPE'
where actor_id=10;

update actor set first_name=concat('Actor',actor_id)
where actor_id>200;

delete from actor where actor_id>200;

select country, customer.* from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country like 'A%';

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country like 'a%';

select distinct first_name,last_name from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where length>140;

select distinct name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where rating='r';

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
having total>20;

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country ='spain' or country='argentina';

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country in ('spain','argentina');

select title, name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where name in ('children','family');

select first_name, last_name from actor
where first_name like '%x%' or last_name like '%x%';

select * from address where district='california' and phone like '%274%';

Ejemplos agregados

-- El total de ciudades: contar 

select country,count(city) total
from country join city on country.country_id=city.country_id
group by country;

-- Total de importe de clientes

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id;

-- en la consulta anterior los clientes que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id
having total>100;

-- Los clientes cuyo nombre empieza por 'a' que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
where first_name like 'a%'
group by customer.customer_id
having total>100 and media>4;

-- ¿Cuantos países tienen más de 50 clientes?
-- Nombre del país y número de clientes (count)

select country, count(customer_id) total from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
group by country.country_id
having total>50

Ejemplos select

-- Para cada país sus ciudades
-- join me está diciendo que relaciono las dos tablas
-- on me dirá mediante que campos están relacionados
select country,city from country join city on country.country_id=city.country_id;

select country,city from country co join city ci on co.country_id=ci.country_id;

-- Solo vale para mysql y el campo se tiene que llamar igual
select country,city from country join city using(country_id);

select country,city,first_name,last_name 
from country join city on country.country_id=city.city_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id;

select name,title from category join film_category 
on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id;

-- left join nos dice que devolvamos los valores de la tabla de la izquierda
-- Aunque no tengamos valores relacionados

select name,title from category left join film_category 
on category.category_id=film_category.category_id
left join film on film_category.film_id=film.film_id;

-- right join nos dice que devolvamos los valores de la tabla de la derecha
-- Aunque no tengamos valores relacionados

select name,title from category right join film_category 
on category.category_id=film_category.category_id
right join film on film_category.film_id=film.film_id

Insert de revistas

INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature’, ‘1234-5678’, 1, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Science’, ‘5678-1234’, 2, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The New England Journal of Medicine’, ‘8765-4321’, 3, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The Lancet’, ‘4321-8765’, 4, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Cell’, ‘9876-5432’, 5, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature Reviews Immunology’, ‘5432-9876’, 6, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Journal of the American Medical Association’, ‘2345-6789’, 7, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature Genetics’, ‘6789-2345’, 8, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The Astrophysical Journal’, ‘3456-7890’, 9, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘IEEE Transactions on Neural Networks and Learning Systems’, ‘7890-3456’, 10, 2023);

Catálogo revistas

DROP TABLE IF EXISTS `revista` ;

CREATE TABLE IF NOT EXISTS `revista` (
`idrevista` INT NOT NULL AUTO_INCREMENT,
`titulo` VARCHAR(100) NOT NULL,
`issn` CHAR(9) NOT NULL,
`numero` INT NOT NULL,
`anyo` YEAR NOT NULL,
PRIMARY KEY (`idrevista`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `articulo` ;

CREATE TABLE IF NOT EXISTS `articulo` (
`idarticulo` INT NOT NULL AUTO_INCREMENT,
`titulo` VARCHAR(100) NOT NULL,
`inicio` INT NOT NULL,
`final` INT NOT NULL,
`idrevista` INT NOT NULL,
PRIMARY KEY (`idarticulo`),
CONSTRAINT `idrevista`
FOREIGN KEY (`idrevista`)
REFERENCES `revista` (`idrevista`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idrevista_idx` ON `articulo` (`idrevista` ASC) VISIBLE;

DROP TABLE IF EXISTS `autor` ;

CREATE TABLE IF NOT EXISTS `autor` (
`idautor` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(100) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`pais` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idautor`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `articulo_autor` ;

CREATE TABLE IF NOT EXISTS `articulo_autor` (
`idarticulo_autor` INT NOT NULL AUTO_INCREMENT,
`idautor` INT NOT NULL,
`idarticulo` INT NOT NULL,
`posicion` INT NOT NULL,
PRIMARY KEY (`idarticulo_autor`),
CONSTRAINT `idarticulo`
FOREIGN KEY (`idarticulo`)
REFERENCES `articulo` (`idarticulo`),
CONSTRAINT `idautor`
FOREIGN KEY (`idautor`)
REFERENCES `autor` (`idautor`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idarticulo_idx` ON `articulo_autor` (`idarticulo` ASC) VISIBLE;

CREATE INDEX `idautor_idx` ON `articulo_autor` (`idautor` ASC) VISIBLE;

Modelos ER

Se desea crear una base de datos que contenga información sobre las revistas a las que estás suscrito o compras habitualmente. De cada revista, se pide su título, el ISSN (un código que identifica a la publicación), el número y el año de publicación. También se desea almacenar información de cada uno de los artículos publicados: el título, la página de inicio y la página de fin. Se asume que no hay dos artículos con el mismo título.
Cada artículo puede estar escrito por varios autores, de quienes interesa conocer su nombre, una dirección de correo electrónico y su país de nacimiento, así como un número que indique la posición en la que aparece en cada artículo: un 1 si es el primer autor, un 2 si aparece en segundo lugar, etc.
revista 1—–N   articulo N–|–N     autor
id int id int posicion id
titulo varchar(100) titulo nombre
issn char(9) inicio mail
numero int fin pais
anyo  year
Un parque zoológico quiere construir una BD para organizar las especies que posee y los distintos itinerarios para visitar el parque. La información se estructura de la siguiente forma. De las especies, se desea conocer su nombre común y su nombre científico, así como una descripción general y una fotografía. Cada especie puede vivir en distintos hábitats naturales, definidos por su nombre, clima y vegetación predominante. Cada especie tiene asociado un índice de vulnerabilidad dentro de cada hábitat, que mide el riesgo de extinción de la especie en el dicho hábitat. Para organizar las visitas, y en función de los hábitats que desee recorrer un visitante, el parque le ofrece una serie de recorridos por los hábitats, que se identifican por su código y se caracterizan por su duración estimada, longitud y número máximo de visitantes permitidos. Un hábitat sólo puede formar parte de un itinerario.
especie N——-|—–N habitat N—1 itinerario
id vulnerabil. id id
nombrecomun nombre codigo
nombrecientifico clima duracion
descripcion vegetacion longitud
fotografia capacidad
Una empresa vende productos a varios clientes. Se necesita conocer los  datos personales de los clientes (nombre, apellidos, dni, dirección y  fecha de nacimiento). Cada producto tiene un nombre y un código, así  como un precio unitario. Un cliente puede comprar varios productos a la  empresa, y un mismo producto puede ser comprado por varios clientes.
Los productos son suministrados por diferentes proveedores. Se debe  tener en cuenta que un producto sólo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el NIF, nombre y dirección.
cliente N—-|—-N producto N—-1 proveedor
id precio id id
nombre cant. nombre nif
apellidos codigo nombre
dni precio direccion
direccion
fechanacimiento
Una compañía aérea necesita una base de datos para registrar la información de sus vuelos. Los vuelos están caracterizados por un Id, la fecha y los aeropuertos de origen y destino. Cada vuelo es realizado por un avión. Los aviones tienen una matrícula que los identifica, el fabricante, un modelo e información sobre su capacidad (número máximo de pasajeros) y autonomía de vuelo (en horas). La tripulación asignada al vuelo está formada por el personal de la propia compañía. De cada trabajador se conoce su id, su nombre y su categoría profesional, así como el puesto que ocupa en cada vuelo en particular.
Por último, para cada vuelo, se almacena la lista completa de pasajeros, con su dni, el nombre, el asiento que ocupa y su clase (turista, primera o business).
asiento,clase
|–N—————|————–N–|
avion 1—N vuelo N-|–N trajadores pasajeros
id id puesto id id
matricula fecha nombre dni
fabricante origen categoria nombre
modelo destino
capacidad
autonomia
Una ONG desea elaborar una base de datos para llevar el seguimiento de todos sus proyectos. Tiene diversas sedes en varios países que se encargan de gestionar y coordinar los proyectos de ese país, cada uno de los cuales puede afectar a una o varias poblaciones.
Sobre la sedes se desea mantener un identificador, la ciudad y país en el que se encuentra, junto con su dirección, un teléfono de contacto y el nombre del director. Cada sede gestiona un conjunto de proyectos, con un código, un título, fechas de inicio y finalización, el presupuesto asignado y el nombre del responsable.
De cada proyecto es necesario conocer qué actuaciones se realizan en cada población, almacenando el nombre, país y nº de habitantes y un identificador para diferenciarlas. Además se desea la inversión del proyecto que corresponde a la población y una pequeña descripción de la actuación.
sede 1—N proyecto 1—N  actuacion N–1 poblacion
id id id id
ciudad codigo inversion nombre
pais titulo descripcion pais
direccion inicio habitantes
telefono fin
director presupuesto
responsable

Ejercicios consultas sql

Buscar todos los clientes (customer) de paises que empiecen por ‘A’
Buscar todos los actores que hayan trabajado en películas de una longitud mayor de 140
Categorías con películas de rating ‘R’ (solo el nombre)

Ventas totales por empleado
Películas en las que han trabajado más de 10 actores
El título de la película que más se ha alquilado (en número de alquileres)
El título de la película que más dinero ha dado (en suma de importe)
Los 5 actores que han trabajado en menos películas

La referencia para los clientes es las dos primeras letras de su nombre y las dos primeras letras de su apellido. Hacer una consulta que nos muestre el id del cliente y esa referencia.
Quiero ver todos los pagos que se han hecho un viernes.
Las películas cuyo título tiene una longitud entre 10 y 15 caracteres.

Clientes que han hecho alquileres por encima de la media.
Clientes que no han alquilado documentales (‘documentary’)

select first_name, last_name from customer
where customer_id not in(
select customer_id from customer join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id) 
join film_category using(film_id)
join category using(category_id)
where name = 'Documentary'
)