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

Reglas para la transformación del modelo E-R al modelo lógico

1.- Cada entidad se transforma en una tabla

2.- Cada tabla tiene una clave principal. Puede ser una clave natural o subrogada, pero aconsejamos poner una clave principal subrogada con idnombretabla

3.- Las relaciones se tranforman así:

1-N El campo clave de la tabla de la parte ‘1’ de la relación se pone como campo en la tabla de la parte ‘N’

N-N Se crea una tabla intermedia con los campos claves de las dos tablas como campos y opcionalmente (aunque yo lo recomiendo) un campo clave propìo.

4.- Se ponen los atributos con sus tipos donde corresponda, sea en la relación o sea en la tabla.

Más ejercicios modelo entidad relación

Acabas de empezar tu colección de películas y quieres hacer una base de datos para construir su ficha técnica. De cada película, necesitas su título, año, nacionalidad y algunos datos de su director: el nombre, la fecha de nacimiento y su país de origen. Además, quieres saber su idioma, si es en blanco y negro o en color, si tiene alguna restricción de edad, un resumen y poder poner tus propias observaciones.

La ficha técnica de cada película también debe incluir el reparto de actores, donde aparecerá su nombre, su nacionalidad y el nombre del personaje que tiene en la película.

 

 

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.

 

 

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.

 

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.

 

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