Categoría: BD

Ejemplos subqueries

Pagos por encima de la media:


select * from payment where amount>(
select avg(amount) from payment
)

Películas de la categoría 1:


select distinct first_name,last_name from actor join film_actor using(actor_id) where film_id in
(
select film_id from film_category where category_id=1
)

Películas con el mayor coste de reemplazo:


select * from film where replacement_cost=(
select max(replacement_cost) from film
)

Películas de la categoría con más alquileres:


select * from film join film_category using(film_id)
where category_id in (
select category_id from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
having count(rental_id)=(
select max(total) from (
select count(rental_id) total from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
) alias_falso
)
)

 

Categorías – países

SELECT
country, 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
inventory ON film.film_id = inventory.film_id
JOIN
store ON inventory.store_id = store.store_id
JOIN
customer ON store.store_id = customer.store_id
JOIN
address ON address.address_id = customer.address_id
JOIN
city ON city.city_id = address.city_id
JOIN
country ON country.country_id = city.country_id

SQL sentencias para construir base de datos

CREATE DATABASE databasename;

DROP DATABASE databasename;

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

DROP TABLE table_name;

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
….
);

CREATE TABLE `indices`.`asda` (
`idasda` INT NOT NULL AUTO_INCREMENT,
`asdacol` VARCHAR(45) NULL,
PRIMARY KEY (`idasda`));

CREATE TABLE `otraprueba`.`prueba` (
`idprueba` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
PRIMARY KEY (`idprueba`),
INDEX `ix_nombre` (`nombre` ASC));

ALTER TABLE `indices`.`asda`
ADD INDEX `ix_nombre` (`asdacol` ASC);

CREATE TABLE `otraprueba`.`prueba` (
`idprueba` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
`idforeign` INT NULL,
PRIMARY KEY (`idprueba`),
INDEX `ix_nombre` (`nombre` ASC),
INDEX `fk_otra_idx` (`idforeign` ASC),
CONSTRAINT `fk_otra`
FOREIGN KEY (`idforeign`)
REFERENCES `otraprueba`.`tabla1` (`idtabla1`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

Ejemplos create view

Primero creamos una vista base:

create view country_payment as

select country, payment.* from country
join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)

Después nos basamos en esta para crear las siguientes:

create view country_total as

select country, sum(amount) total from country_payment
group by country


create view country_month as

select country, month(payment_date) month, count(payment_id) total from country_payment
group by country, month

Ejercicios Base de datos

Total de pagos por países

select country, sum(amount) total from
country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)
group by country

Películas con más de diez actores

select title, count(actor_id) actores
from film join film_actor using (film_id)
group by title
having actores>10

Actor que ha trabajado en más películas

select first_name, last_name, count(film_id) peliculas
from actor join film_actor using(actor_id)
group by first_name, last_name
order by peliculas desc
limit 0,1

Actores que han hecho películas de acción

select distinct first_name, last_name,name
from actor join film_actor using(actor_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
where name=’Action’
order by first_name,last_name

Países con más de 50 clientes

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