Categoría: BD

Ejercicios SQL

Actores con ‘ll’ en el apellido o en el nombre
Actores con id>=100 y menor igual que 200

Una de las dos anteriores o las dos
Insertar actor ‘Juan’ ‘Pérez’
Insertar actor ‘Rosa’ ‘Pi’

Actualizar el nombre del actor con id 10 a ‘PEPE’

Actualizar el nombre de los actores con el id > 200 a ‘Actor’ + id

Borrar los actores con id > 200

select * from sakila.actor
where first_name like '%ll%' or last_name like '%ll%';

select * from sakila.actor
where actor_id between 100 and 200;

select * from sakila.actor
where first_name like '%ll%' or last_name like '%ll%' or actor_id between 100 and 200;

insert into sakila.actor (first_name,last_name)
values ('Juan','Pérez');

insert into sakila.actor (first_name,last_name)
values ('Rosa','Pi');

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

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

delete from sakila.actor
where actor_id>200;

Ejercicio ong

 


DROP TABLE IF EXISTS `poblacion`;

CREATE TABLE `poblacion` (
 `idpoblacion` int(11) NOT NULL AUTO_INCREMENT,
 `nombre` varchar(45) DEFAULT NULL,
 `habitantes` int(11) DEFAULT NULL,
 `pais` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idpoblacion`),
 KEY `ix_nombre` (`nombre`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `sede`;

CREATE TABLE `sede` (
 `idsede` int(11) NOT NULL AUTO_INCREMENT,
 `ciudad` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
 `pais` varchar(45) DEFAULT NULL,
 `direccion` varchar(45) DEFAULT NULL,
 `telefono` varchar(45) DEFAULT NULL,
 `director` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idsede`),
 KEY `ix_ciudad` (`ciudad`),
 KEY `ix_pais` (`pais`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `proyecto`;

CREATE TABLE `proyecto` (
 `idproyecto` int(11) NOT NULL AUTO_INCREMENT,
 `idsede` int(11) DEFAULT NULL,
 `titulo` varchar(45) DEFAULT NULL,
 `fini` date DEFAULT NULL,
 `ffin` date DEFAULT NULL,
 `presupuesto` decimal(10,2) DEFAULT NULL,
 `responsable` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idproyecto`),
 KEY `fk_sede_idx` (`idsede`),
 KEY `ix_titulo` (`titulo`),
 CONSTRAINT `fk_sede` FOREIGN KEY (`idsede`) REFERENCES `sede` (`idsede`) ON DELETE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;




DROP TABLE IF EXISTS `actuacion`;

CREATE TABLE `actuacion` (
 `idactuacion` int(11) NOT NULL AUTO_INCREMENT,
 `idproyecto` int(11) NOT NULL,
 `idpoblacion` int(11) DEFAULT NULL,
 `inversion` decimal(10,2) DEFAULT NULL,
 `descripcion` text,
 PRIMARY KEY (`idactuacion`),
 KEY `fk_proyecto` (`idproyecto`),
 KEY `fk_poblacion_idx` (`idpoblacion`),
 CONSTRAINT `fk_poblacion` FOREIGN KEY (`idpoblacion`) REFERENCES `poblacion` (`idpoblacion`),
 CONSTRAINT `fk_proyecto` FOREIGN KEY (`idproyecto`) REFERENCES `proyecto` (`idproyecto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ejemplos BD

En un centro de salud un médico atiende a varios pacientes y cada paciente está asignado a un solo médico. Cada médico pasa consulta en una o varias salas en horario distinto. En cada sala pasan consulta varios médicos.

a. Del médico interesan conocer sus datos personales y el año en que se colegio.

b. De la sala interesa conocer su ubicación, además se identifica por un nº de sala

c. Del paciente, además de sus datos personales, se desea conocer su historial médico.

d. Se desea saber en qué sala pasa consulta cada médico en cada momento.

 

Se desea diseñar una BD para una Universidad que contenga información sobre carreras y asignaturas que se pueden estudiar. Además se incluirá la información de los alumnos matriculados en las correspondientes asignaturas y las calificaciones que obtienen en las asignaturas en las que están matriculados. Nota: suponer que una asignatura solo puede pertenecer a una carrera.

 

 

En una autoescuela hay varios profesores y varios coches de prácticas. Se quiere guardar información respecto a los alumnos que se matriculan, el profesor que se les asigna y el coche que conducirán. Cada alumno da clase con un profesor en un coche, el alumno siempre va a conducir el mismo coche y con el mismo profesor.

 

Se desea diseñar una base de datos para almacenar y gestionar la información empleada por una empresa dedicada a la venta de automóviles, teniendo en cuenta los siguientes aspectos La empresa dispone de una serie
de coches para su venta. Se necesita conocer la matricula, marca y modelo, el color y el precio de venta de cada coche.Los datos que interesa conocer de cada cliente son el NIF, nombre, dirección, ciudad y numero de teléfono: ademas, los clientes se diferencian por un código interno de la empresa.  Un cliente puede comprar tantos coches como desee a la empresa. Un coche determinado solo puede ser comprado por un único cliente.

 

 

En la biblioteca del centro se manejan fichas de autores y libros. En la ficha de cada autor se tiene el código de autor y el nombre. De cada libro se guarda el código. titulo, ISBN, editorial y numero de pagina. Un autor puede escribir varios libros, y un libro puede ser escrito por varios autores. Un libro esta formado por ejemplares.
Cada ejemplar tiene un código y una localización Un libro tiene muchos ejemplares y un ejemplar pertenece solo a un libro. Los usuarios de la biblioteca del centro también disponen de ficha en la biblioteca y sacan ejemplares
de ella. De cada usuario se guarda el código, nombre. dirección y teléfono. Los ejemplares son prestados a los usuarios. Un usuario puede tomar prestados varios ejemplares, y un ejemplar puede ser prestado a varios usuarios. De cada prestamos interesa guardar la fecha de préstamo y la fecha de devolución.

Crear funciones

Ejemplos:

CREATE DEFINER=`root`@`localhost` 
FUNCTION `inventory_in_stock`(p_inventory_id INT)
 RETURNS tinyint(1)
 READS SQL DATA
BEGIN
 DECLARE v_rentals INT;
 DECLARE v_out INT;
 SELECT COUNT(*) INTO v_rentals
 FROM rental
 WHERE inventory_id = p_inventory_id;

 IF v_rentals = 0 THEN
 RETURN TRUE;
 END IF;

 SELECT COUNT(rental_id) INTO v_out
 FROM inventory LEFT JOIN rental USING(inventory_id)
 WHERE inventory.inventory_id = p_inventory_id
 AND rental.return_date IS NULL;

 IF v_out > 0 THEN
 RETURN FALSE;
 ELSE
 RETURN TRUE;
 END IF;
END

select inventory_in_stock(30)

CREATE DEFINER=`root`@`localhost`
 FUNCTION `clientes_por_pais`
 (p_country varchar(50)) RETURNS int(11)
BEGIN
declare total int;

SELECT count(customer_id) into total
 FROM sakila.country
left join city using (country_id)
left join address using (city_id)
left join customer using (address_id)
where country=p_country
group by country;

RETURN total;
END

select clientes_por_pais('Algeria')

CREATE DEFINER=`root`@`localhost` FUNCTION `peliculas_por_actor`(p_actor_id int) RETURNS int(11)
BEGIN

declare total int;

select count(film_id) into total from film join
film_actor using (film_id)
join actor using(actor_id)
where actor.actor_id=p_actor_id;

RETURN total;
END

select peliculas_por_actor(1)

CREATE DEFINER=`root`@`localhost` 
FUNCTION `total_ventas`() RETURNS decimal(10,2)
BEGIN
declare total decimal(10,2);
select sum(amount) into total from payment;
RETURN total;
END