EJERCICIO 33.

AGENDA
Se dispone de la tabla AGENDA con los datos de aquellos empleados que trabajan en
una serie de hospitales.

CREATE DATABASE Agenda;

USE Agenda;

create table personas

(
cod_hospital int(3),
dni int(8) primary key,
apellidos varchar(50),
funcion varchar(30),
salario float,
localidad varchar(20)
);
insert into personas values (1,12345678,'García Hernández,
Eladio','CONSERJE',1200,'LORCA');
insert into personas values (1,87654321,'Fuentes Bermejo,
Carlos','DIRECTOR',2000,'MURCIA');
insert into personas values (2,55544433,'González Marín,
Alicia','CONSERJE',1200,'MURCIA');
insert into personas values (1,66655544,'Castillo Montes,
Pedro','MEDICO',1700,'MURCIA');
insert into personas values (2,22233322,'Tristán García,
Ana','MEDICO',1900,'MURCIA');
insert into personas values (3,55544411,'Ruiz Hernández,
Caridad','MEDICO',1900,'LORCA');
insert into personas values (3,99988333,'Serrano Díaz,
Alejandro','DIRECTOR',2400,'CARTAGENA');
insert into personas values (4,33222111,'Mesa del Castillo,
Juan','MEDICO',2200,'LORCA');
insert into personas values (2,22233333,'Martínez Molina,
Andrés','MEDICO',1600,'CARTAGENA');
insert into personas values (4,55544412,'Jiménez Jiménez,
Dolores','CONSERJE',1200,'MURCIA');
insert into personas values (4,22233311,'Martínez Molina,
Gloria','MEDICO',1600,'MURCIA');

a) Mostrar los apellidos de las personas que vivan en MURCIA o LORCA.

SELECT apellidos
FROM personas
WHERE localidad = 'lorca' OR localidad = 'murcia';

SELECT apellidos
FROM personas
WHERE localidad IN ('lorca', 'murcia');
b) Mostrar los datos de las personas que vivan en MURCIA, tengan un salario superior
a los 1500 euros y sean DIRECTORES.
SELECT *
FROM personas
WHERE localidad = 'murcia'
AND salario > 1500
 AND funcion = 'director';
c) Seleccionar aquellas personas cuyo apellido comience por M.
SELECT apellidos
FROM personas
WHERE apellidos LIKE 'M%';
d) Mostrar aquellas personas que tengan un salario entre 1500 y 2000 euros.
SELECT *
FROM personas
WHERE salario BETWEEN 1500 AND 2000;

SELECT *
FROM personas
WHERE salario >= 1500 AND salario <= 2000;
e) Obtén los apellidos en mayúsculas de las personas que trabajen en el hospital
número 1.
SELECT upper(apellidos)
FROM personas
WHERE cod_hospital = 1;
f) Obtener los apellidos y localidad en minúscula de todas aquellas personas que no
trabajen en el hospital número 1.
SELECT apellidos, lower(localidad)
FROM personas
WHERE cod_hospital <> 1;
g) Visualizar los datos de aquellas personas que no trabajen en el hospital número 2 y
que sean de MURCIA.
SELECT *
FROM personas
WHERE cod_hospital <> 2 AND localidad = 'murcia';
h) Mostrar los datos de todas las localidades que hay en la tabla personas sin
repeticiones.
SELECT DISTINCT localidad
FROM personas;
i) Mostrar los datos de las personas cuya función sea MÉDICO ordenados por
apellidos descendentemente.
 SELECT *
FROM personas
WHERE funcion = 'medico'
ORDER BY apellidos DESC;

EJERCICIO 34. EMPLEADOS

Dadas las siguientes tablas piensa sin crear la tabla en SQL cómo realizarías las
siguientes consultas:

CREATE DATABASE Empleados;

USE Empleados;

create table depart

(
dept_no int(2) primary key,
dnombre varchar(30) not null,
loc varchar(20) not null
);
create table emple
(
emp_no int(4) primary key,
apellido varchar(30) not null,
oficio varchar(30) not null,
dir int(4),
fecha_alt date not null,
salario int(4) not null,
comision int(4),
dept_no int(2) not null,
constraint fk1 foreign key (dept_no) references depart(dept_no) on delete cascade on
update cascade,
constraint fk2 foreign key (dir) references emple(emp_no) on delete set null on update
cascade
);

insert into depart values

(10,'CONTABILIDAD','SEVILLA'),
(20,'INVESTIGACION','MADRID'),
(30,'VENTAS','BARCELONA'),
(40,'PRODUCCION','BILBAO');

insert into emple values

(7839,'REY','PRESIDENTE',NULL,'1991-11-17',4100,NULL,10),
(7698,'NEGRO','DIRECTOR',7839,'1991-05-01',3005,NULL,30),
(7782,'CEREZO','DIRECTOR',7839,'1991-06-09',2885,NULL,10),
(7499,'ARROYO','VENDEDOR',7698,'1990-02-20',1500,390,30),
(7521,'SALA','VENDEDOR',7698,'1991-02-22',1625,650,30),
(7654,'MARTIN','VENDEDOR',7698,'1991-09-29',1600,1020,30),
(7844,'TOVAR','VENDEDOR',7698,'1991-09-08',1350,0,30),
(7900,'JIMENO','EMPLEADO',7698,'1991-12-03',1335,NULL,30),
(7566,'JIMENEZ','DIRECTOR',7839,'1991-04-02',2900,NULL,20),
(7788,'GIL','ANALISTA',7566,'1991-11-09',3000,NULL,20),
(7902,'FERNANDEZ','ANALISTA',7566,'1991-12-03',3000,NULL,20),
(7369,'SANCHEZ','EMPLEADO',7902,'1990-12-17',1040,NULL,20),
(7876,'ALONSO','EMPLEADO',7788,'1991-09-23',1430,NULL,20),
(7934,'MUÑOZ','EMPLEADO',7782,'1992-01-23',1690,NULL,10);

a) Mostrar el apellido, oficio y número de departamento de cada empleado.

SELECT apellido, oficio, dept_no
FROM emple;
b) Mostrar el número, nombre y localización de cada departamento.
SELECT dept_no, dnombre, loc
FROM depart;
c) Datos de los empleados ordenados por número de departamento
descendentemente y dentro de cada departamento ordenados por apellido
ascendentemente.
SELECT *
FROM emple
ORDER BY dept_no DESC, apellido;
d) Mostrar los empleados que tengan un salario mayor que 2000 o que pertenezcan al
departamento número 20.
SELECT *
FROM emple
WHERE salario > 2000 OR dept_no = 20;
e) Seleccionar de la tabla EMPLE los empleados cuyo apellido termine por 'Z'.
SELECT *
FROM emple
WHERE apellido LIKE '%Z';
f) Número y apellidos de los empleados cuyo apellido termine por 'Z' y tengan un
salario superior a 2000.
SELECT emp_no, apellido
FROM emple
WHERE apellido LIKE '%Z' AND salario > 2000;
g) Datos de los departamentos cuya localización empiece por 'B'.
SELECT *
FROM depart
 WHERE loc LIKE 'B%';
h) Seleccionar el apellido, salario y número de departamento de los empleados cuyo
salario sea mayor que 2000 en los departamentos 10 ó 30.
SELECT apellido, salario, dept_no
FROM emple
WHERE salario > 2000 AND (dept_no = 10 OR dept_no = 30);

SELECT apellido, salario, dept_no

FROM emple
WHERE salario > 2000 AND dept_no IN (10, 30);
i) En una consulta concatena el apellido de cada empleado con su oficio.
SELECT concat(apellido, ' ', oficio) empleado_oficio
FROM emple
ORDER BY 1;
j) Mostrar el apellido y la longitud del apellido (función LENGTH) de todos los
empleados, ordenados por la longitud de los apellidos de los empleados
descendentemente.
SELECT apellido, length(apellido)
FROM emple
ORDER BY length(apellido) DESC;

SELECT apellido, length(apellido) largo

FROM emple
ORDER BY 2 DESC;
k) Obtener el año de contratación de todos los empleados (función YEAR).
SELECT DISTINCT year(fecha_alt) año
FROM emple;
 EJERCICIO 35. TIENDA DE INFORMÁTICA
Dadas las siguientes tablas

CREATE DATABASE Tienda;

USE Tienda;

CREATE TABLE fabricante

(
codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL
);

CREATE TABLE producto

(
codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
precio DOUBLE NOT NULL,
codigo_fabricante INT UNSIGNED NOT NULL,
FOREIGN KEY (codigo_fabricante) REFERENCES
fabricante(codigo)
);

insert into fabricante(nombre) values

('ACER'),
('ASUS'),
('CORSAIR'),
('DELL'),
('HP'),
('LENOVO');

insert into producto(nombre, precio, codigo_fabricante) values

('Portatil', 625.99, 1),
('RAM', 59.99, 2),
('Disco', 78.75, 3),
('Placa base', 201, 4),
('Sobremesa', 549.99, 5),
('Tablet', 110, 6),
('Smartphone', 999, 1),
('Raton', 30, 2),
('Teclado', 61.25 , 3),
('Monitor', 119.99, 4),
('Impresora', 85.99, 5),
('Camara', 63, 6);

Escribe cómo realizarías las siguientes consultas:

a) Obtener los nombres y los precios de los productos de la tienda.
SELECT nombre, precio FROM producto;
b) Obtener el nombre de los productos cuyo precio sea menor o igual a 200€.
SELECT Nombre FROM producto WHERE Precio <= 200;
c) Obtener todos los datos de los artículos cuyo precio esté entre los 60€ y los 120€
(ambas cantidades incluidas).
/* Con AND */
SELECT * FROM producto WHERE precio >= 60 AND precio <= 120;
/* Con BETWEEN */
SELECT * FROM producto WHERE precio BETWEEN 60 AND 120;
d) Obtener el precio medio de los artículos cuyo código de fabricante sea 2.
SELECT AVG(precio) FROM producto WHERE codigo_fabricante =2;
e) Obtener un listado completo de artículos, incluyendo por cada artículo los datos del
articulo y de su fabricante.
/* Sin INNER JOIN */
 SELECT * FROM producto, fabricante
WHERE producto.codigo_fabricante = fabricante.codigo;

/* Con INNER JOIN */

SELECT * FROM producto INNER JOIN fabricante
ON producto.codigo_fabricante = fabricante.codigo;
f) Obtener el precio medio de los productos de cada fabricante, mostrando sólo los
códigos de fabricante.
SELECT AVG(precio), codigo_fabricante
FROM producto GROUP BY codigo_fabricante;
g) Obtener el nombre y precio del artículo más barato.
SELECT nombre, precio FROM producto
WHERE precio = (SELECT MIN(precio) FROM producto);
h) Obtener una lista con el nombre y precio de los artículos más caros de cada
proveedor (incluyendo el nombre del proveedor).
/* Sin INNER JOIN */
SELECT P.nombre, P.precio, F.nombre
FROM producto P, fabricante F
WHERE P.codigo_fabricante = F.Codigo AND
P.precio = (SELECT MAX(P.precio)FROM producto P
WHERE P.codigo_fabricante= F.codigo)

/* Con INNER JOIN */

SELECT P.nombre, P.precio, F.nombre
FROM producto P INNER JOIN fabricante F
ON P.codigo_fabricante= F.codigo
AND P.precio =(SELECT MAX(P.precio)FROM producto P
WHERE P.codigo_fabricante= F.codigo)
 EJERCICIO 36. PELÍCULAS.
Dado el siguiente esquema:

CREATE DATABASE Peliculas;

USE Peliculas;

create table PELICULAS

(
Codigo int(2) auto_increment primary key,
Nombre varchar(30) not null,
CalificacionEdad int(2)
);

create table SALAS

(
Codigo int auto_increment primary key,
nombre varchar(30) not null,
Pelicula int(2),
constraint fk foreign key (Pelicula) references PELICULAS(Codigo) on delete set null
on update cascade
);

insert into PELICULAS(Nombre, CalificacionEdad) values

('El señor de los anillos', 13),
('Pulp Fiction', 16),
('Batman', 7),
('El padrino', 16),
('Hook', 7),
('Los cazafantasmas', 7);
insert into SALAS(Nombre, Pelicula) values
('Norte',1),
('Sur',3),
('Este',5),
('Oeste',null),
('Grande',2),
('Mini',null);

Debes realizar las siguientes consultas:

a) Mostrar el nombre de todas las películas
SELECT Nombre FROM PELICULAS;
b) Mostrar las distintas calificaciones de edad que existen
SELECT DISTINCT CalificacionEdad FROM PELICULAS;
c) Mostrar todas las salas que no proyectan ninguna película
SELECT * FROM SALAS WHERE Pelicula IS NULL;
d) Mostrar la información de todas las salas y, si se proyecta alguna película en la
sala, mostrar también la información de la película
SELECT * FROM SALAS LEFT JOIN PELICULAS
ON SALAS.Pelicula = PELICULAS.Codigo;
 EJERCICIO 37. HOSPITAL COMPLETO
Dadas las siguientes tablas y datos de cada tabla.
Escribe cómo realizarías las siguientes consultas:
a) Encuentre a todos los miembros del personal cuyo nombre empiece por 'H'.
select apellido
from plantilla
where upper(apellido) like 'H%';
b) Quienes son las enfermeras y enfermeros que trabajan en turnos de Tarde o
Mañana
select apellido
from plantilla
where upper(funcion) in ('ENFERMERO' ,'ENFERMERA')
and upper(turno) in ('T','M');
c) Mostrar, para todos los hospitales, el código de hospital, el nombre completo del
hospital y su nombre abreviado de tres letras (a esto podemos llamarlo ABR) Ordenar
la recuperación por esta abreviatura.
select substr(nombre,1,3) abr, hospital_cod, nombre
from hospital
order by 1;
d) Encontrar el salario medio de los Analistas.
select avg(salario) "SALARIO MEDIO"
from emp
where upper(oficio) = 'ANALISTA';
e) Calcular el número de personas que realizan cada oficio en cada departamento.
select dept_no, oficio, count(*)
from emp group by dept_no, oficio;
f) Buscar que departamentos tienen más de cuatro personas trabajando.
select dept_no, count(*)
 from emp
group by dept_no
having count(*) > 4;
g) Listar, a partir de las tablas EMP y DEPT2, el nombre de cada empleado, su oficio,
su número de departamento y el nombre del departamento donde trabajan.
select apellido, oficio, e.dept_no, dnombre
from emp e, dept2 d
where e.dept_no = d.dept_no;
h) Obtener el apellido, departamento y oficio de aquellos empleados que tengan un
oficio que este en el departamento 20 y que no sea ninguno de los oficios que está en
el departamento de VENTAS.
select apellido, dept_no, oficio
from emp
where oficio in (select oficio
from emp
where dept no = 20)
and oficio not in (select oficio
from emp e, dept2 d
where e.dept_no = d.dept_no
and upper(dnombre) = 'VENTAS');
i) Queremos averiguar el apellido del individuo más antiguo de la empresa.
select apellido, fecha-alt Fecha
from emp
where fecha_alt = (select min(fecha_alt)
from emp);
 EJERCICIO 38. AGENDA
Más consultas sobre AGENDA.

a) Mostrar todos los datos de todas las personas.

SELECT * FROM personas;
b) Obtén el DNI, apellidos y función de todas las personas.
SELECT dni, apellidos, funcion
FROM personas;
c) Mostrar los apellidos de las personas que vivan en LORCA.
SELECT apellidos
FROM personas
WHERE localidad = 'lorca';
d) Seleccionar los datos de aquellas personas que vivan en MURCIA y tengan un
salario superior a los 1500 euros.
SELECT *
FROM personas
WHERE localidad = 'murcia' AND salario > 1500;
e) Mostrar los datos de las personas que tengan un salario superior a 1500 euros y
sean médicos. Ordenar la salida por salario descendentemente.
 SELECT *
FROM personas
WHERE salario > 1500 AND funcion = 'medico'
ORDER BY salario DESC;
f) Mostrar los datos de las personas que tengan una M en el apellido y cuya función
sea CONSERJE
SELECT apellidos, funcion
FROM personas
WHERE apellidos LIKE '%M%' AND funcion = 'conserje';
g) Seleccionar los datos de aquellas personas cuya función sea MÉDICO o
DIRECTOR
SELECT *
FROM personas
WHERE funcion IN ('medico', 'director');
h) Obtener los datos de aquellas personas cuya función no sea CONSERJE y tengan
un salario superior a los 1500 euros, ordenados por apellido descendentemente.
SELECT *
FROM personas
WHERE funcion NOT IN ('conserje')
AND salario > 1500
ORDER BY apellidos DESC;

SELECT *
FROM personas
WHERE funcion <> 'conserje'
AND salario > 1500
ORDER BY apellidos DESC;
i) Con una consulta devuelve los apellidos de todas las personas. Al lado debe
aparecer la longitud de cada apellido.
SELECT apellidos, length(apellidos) largo
FROM personas;
j) Obtener los datos de las personas que trabajen en los hospitales 1 ó 2 y tengan un
salario superior a 1500 euros.
SELECT *
 FROM personas
WHERE cod_hospital IN (1, 2)
AND salario > 1500;

EJERCICIO 39. EMPLEADOS

Más consultas sobre EMPLEADOS.

a) Datos de los empleados ordenados por apellidos.

SELECT *
FROM emple
ORDER BY apellido;
b) Datos de los empleados ordenados por número de departamento
descendentemente.
SELECT *
FROM emple
ORDER BY dept_no DESC;
c) Mostrar los datos de los empleados cuyo salario sea mayor que 2000.
SELECT *
FROM emple
 WHERE salario > 2000;
d) Seleccionar los empleados cuyo oficio sea 'VENDEDOR'. Mostrar los datos
ordenados por apellido.
SELECT *
FROM emple
WHERE oficio = 'VENDEDOR'
ORDER BY apellido;
e) Mostrar los empleados cuyo departamento sea 20 y cuyo oficio sea 'EMPLEADO'.
Ordenar el resultado por apellido.
SELECT *
FROM emple
WHERE dept_no = 20 AND oficio = 'EMPLEADO'
ORDER BY apellido;
f) Seleccionar de la tabla EMPLE los empleados cuyo apellido empiece por 'A'.
SELECT *
FROM emple
WHERE apellido LIKE 'A%';
g) Mostrar los apellidos de los empleados que no tengan comisión y cuyo apellido
empiece por 'J'.
SELECT apellido
FROM emple
WHERE comision IS NULL AND apellido LIKE 'J%';
h) Mostrar los apellidos de los empleados cuyo oficio sea 'VENDEDOR', 'ANALISTA' o
'EMPLEADO'.
SELECT apellido
FROM emple
WHERE oficio IN ('VENDEDOR', 'ANALISTA', 'EMPLEADO');
i) Mostrar el apellido y número de los empleados cuyo salario no esté entre 1000 y
2000.
SELECT apellido, emp_no
FROM emple
WHERE salario NOT BETWEEN 1000 AND 2000;
j) Obtener los apellidos de todos los empleados en minúscula.
 SELECT lower(apellido)
FROM emple;

EJERCICIO 40. TIENDA DE INFORMÁTICA

Más consultas sobre TIENDA DE INFORMÁTICA.

a) Seleccionar el precio medio de todos los productos.

SELECT AVG(Precio) FROM ARTICULOS;
b) Obtener el número de artículos cuyo precio sea mayor o igual a 180€.
SELECT COUNT(*) FROM ARTICULOS WHERE Precio >= 180;
c) Obtener el nombre y precio de los artículos cuyo precio sea mayor o igual a 180€ y
ordenarlos descendentemente por precio, y luego ascendentemente por nombre.
SELECT Nombre, Precio
FROM ARTICULOS
WHERE Precio >= 180
ORDER BY Precio DESC, Nombre;
d) Obtener los nombres de los fabricantes que ofrezcan productos cuyo precio medio
sea mayor o igual a 150€.
/* Sin INNER JOIN */
SELECT AVG(Precio), FABRICANTES.Nombre
FROM ARTICULOS, FABRICANTES
WHERE ARTICULOS.Fabricante = FABRICANTES.Codigo
GROUP BY FABRICANTES.Nombre
HAVING AVG(Precio) >= 150;
/* Con INNER JOIN */
SELECT AVG(Precio), FABRICANTES.Nombre
 FROM ARTICULOS INNER JOIN FABRICANTES
ON ARTICULOS.Fabricante = FABRICANTES.Codigo
GROUP BY FABRICANTES.Nombre
HAVING AVG(Precio) >= 150;

EJERCICIO 41. PELÍCULAS.

Más consultas sobre PELÍCULAS.

a) Mostrar todas las películas que no han sido calificadas.

SELECT * FROM PELICULAS WHERE CalificacionEdad IS NULL;
b) Mostrar la información de todas las películas y, si se proyecta en alguna sala,
mostrar también la información de la sala.
SELECT *FROM SALAS RIGHT JOIN PELICULAS
ON SALAS.Pelicula = PELICULAS.Codigo;
c) Mostrar los nombres de las películas que no se proyectan en ninguna sala.
/* Con JOIN */
SELECT PELICULAS.Nombre
FROM SALAS RIGHT JOIN PELICULAS
ON SALAS.Pelicula = PELICULAS.Codigo
WHERE SALAS.Pelicula IS NULL;
/* Con Subconsulta */
SELECT Nombre FROM PELICULAS
WHERE Codigo NOT IN(SELECT Pelicula FROM SALAS
WHERE Pelicula IS NOT NULL);
 EJERCICIO 42. HOSPITAL COMPLETO
Más consultas sobre HOSPITAL.
a) Encontrar el salario más alto y el salario más bajo de la tabla de empleados, así
como la diferencia entre ambos.
select max(salario) maximo, min(salario) mínimo,
max(salario) - min(salario) diferencia
from emp;
b) Calcular el número de oficios diferentes que hay, en total, en los departamentos 10
y 20 de la empresa.
select count(distinct oficio) tareas
from emp
where dept_no in (10,20);
c) Se desea conocer el nombre y oficio de todos aquellos empleados que trabajan en
Madrid. La salida deberá estar ordenada por el oficio.
select apellido, oficio from emp e, dept2 d
where upper(loc) = 'MADRID' and e.dept_no = d.dept_no
order by oficio;
d) Obtener el número de empleado, numero de departamento y apellido de todos los
empleados que trabajen en el departamento 20 o 30 y su salario sea mayor que dos
veces el mínimo de la empresa. No queremos que el oficio sea PRESIDENTE.
select emp_no, dept_no, apellido
from emp
where dept_no in (20,30)
and salario > (select 2*min(salario)
from emp)
and upper(oficio) not in 'PRES%';
e) Queremos conocer el apellido, oficio, salario y departamento en el que trabajan, de
todos los individuos cuyo salario sea mayor que el mayor salario del departamento
30.
select apellido, oficio, salario, dept_no
from emp
where salario > (select max(salario)
from emp
where dept_no = 30);