https://www.tutorialesprogramacionya.com/mysqlya/index.php?inicio=0
https://www3.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html
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
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);
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
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