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
)
)

 

Ejemplos sql agrupados

Estadísticas de países:


select country pais,count(amount),sum(amount) total, avg(amount) media,max(amount) máximo, min(amount) minimo, variance(amount),std(amount) from country join city using(country_id)
join address using(city_id)
join customer using(address_id)
join payment using(customer_id)
group by pais
order by media desc

Número de películas por actor:


SELECT first_name,last_name, count(film_id) total from actor left join film_actor using(actor_id)
left join film using(film_id)
group by first_name,last_name
order by first_name desc

Ciudades por país:

select country,count(city_id) total from country join city using(country_id)
group by country
order by total asc

Categorías con más de 60 películas:


select name,count(film_id) total from category join film_category using(category_id)
group by name
having total>=60

Actor que ha participado en más películas:

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

Total de categorías por actor (diferentes):


select distinct first_name,last_name,count(distinct 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)
group by actor_id

Películas alquiladas por clientes:

select first_name,last_name, count(film_id) total from film join inventory using(film_id)
join rental using(inventory_id)
join customer using(customer_id)
group by customer_id

Categoría más alquilada:

select name,count(customer_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
order by total desc
limit 1

 

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