Curso de introducción a bases de de datos SQL: Ejercicios Resueltos

Curso de introducción a bases de datos SQL

Resolución de ejercicios

Ejercicio Nº 0 : Mi primera base de datos.

¡Bienvenido al curso de SQL en Oracle! En este ejercicio, vamos a dar nuestros primeros pasos para conectarnos a la base de datos de Oracle utilizando SQL Developer.

Ejercicio Nº 1 : Mi primera tabla.

Diseña y crea la siguiente tabla llamada «Camionero» en una base de datos Oracle:

Camionero: Esta tabla almacenará información sobre los camioneros contratados, incluyendo su identificación, nombre, teléfono, dirección, población y salario.

				
					create table Camionero (
    dni VARCHAR2(30),
    nombre VARCHAR2(30),    
    telefono VARCHAR2(9),
    direccion VARCHAR2(30),
    poblacion VARCHAR2(30),
    salario NUMBER(6,2)
);

Ejercicio Nº 2 : Tablas y atributos (Empresa de transporte).

Diseña y crea las siguientes tablas en una base de datos Oracle:

Camionero: Esta tabla almacenará información sobre los camioneros contratados, incluyendo su identificación, nombre, teléfono, dirección, población y salario. (Tabla creada en el ejercicio anterior.)
Paquete: Esta tabla registrará los paquetes a entregar, con detalles como el código del paquete, descripción, destinatario, dirección del destinatario y el DNI del camionero asignado para su entrega.
Provincia: En esta tabla se almacenarán las provincias relacionadas con los paquetes, junto con su código y nombre, además del código del paquete asociado.
Camion: Aquí se registrarán los camiones utilizados en el transporte, incluyendo su matrícula, modelo, tipo y potencia.
Camionero_Camion: Esta tabla establecerá la relación entre los camioneros y los camiones que conducen, mediante el registro de sus DNIs y las matrículas de los camiones asignados.

				
					create table Paquete (
    cod_paquete varchar2(4),
    descripcion varchar2(30),
    destinatario varchar2(30),
    direccion_destinatario varchar2(30),
    dni varchar2(9)
);

create table Provincia (
    cod_provincia number(4),
    nombre varchar2(30),
    cod_paquete number(4)
);

create table Camion (
    matricula varchar2(8),
    modelo varchar2(30),
    tipo varchar2(30),
    potencia number(4)
);

create table camionero_camion (
    dni varchar2(9),
    matricula varchar2(8)
);

Ejercicio Nº 3 : Tablas y atributos (Gestión de empleados).

Diseña y crea las siguientes tablas en una base de datos Oracle:

Empleado: Esta tabla almacenará información básica sobre los empleados de una empresa, incluyendo su identificación, nombre, apellido, fecha de contratación, salario y departamento al que pertenecen.
Proyecto: En esta tabla se registrarán los proyectos en los que están involucrados los empleados, con detalles como el código del proyecto, nombre, descripción y la fecha de inicio del proyecto.
Departamento: Aquí se almacenarán los departamentos de la empresa, junto con su código, nombre y la ubicación física del departamento.
Asignación: Esta tabla establecerá la relación entre los empleados y los proyectos en los que participan, mediante el registro de sus identificaciones y los códigos de proyecto correspondientes.

				
					create table Empleado (
    id_empleado varchar2(4),
    nombre varchar2(30),
    apellido varchar2(30),
    fecha_contratacion date,
    salario number(6,2),
    departamento varchar2(30)
);

create table Proyecto (
    cod_proyecto varchar2(4),
    nombre varchar2(30),
    descripcion varchar2(100),
    fecha_inicio date
);

create table Departamento (
    cod_departamento varchar2(4),
    nombre varchar2(30),
    ubicacion_fiscal varchar2(30)
);

create table Asignacion (
    id_empleado varchar2(4),
    cod_proyecto varchar2(4)
);

Ejercicio Nº 4 : Aplicación de restricciones básicas (Tienda).

Identifica las posibles tablas, atributos, claves primarias y claves externas asociadas del siguiente escenario:

Estamos desarrollando una aplicación de gestión de inventarios para una tienda en línea que vende productos electrónicos. Los usuarios pueden ver los productos disponibles, agregarlos al carrito de compras y realizar pedidos. Cada producto tiene un nombre, una descripción, un precio y una cantidad disponible en inventario. También se almacena información sobre la categoría a la que pertenece cada producto. Para los clientes, la base de datos guarda el nombre, la dirección de correo electrónico y el número de teléfono. Los pedidos contienen información sobre los productos comprados, la cantidad de cada producto y la fecha del pedido.

Objetivos:

Identificar tablas, atributos, claves primarias y claves externas de una BD.

Donde:

Negrita –> Clave Primaria (Color amarillo).
Subrayado –> Clave externa (Color verde).

Resolución

En primer lugar vamos a identificar las distintas tablas que vamos a necesitar para gestionar correctamente nuestra base de datos, para ello nos vamos a fijar en cuales son los principales grupos de datos que queremos almacenar, es importante distinguir entre las tablas y los atributos.

Tablas

productos
cliente
carrito
pedidos
categoria_producto

Atributos

A parte de los atributos mencionados en el enunciado, recordar que debemos asignar siempre una columna o atributo que lo identifique de forma única, en caso de no tener un código claro le asignaremos un atributo identificador (por ejemplo a la tabla producto le asignamos el atributo id_producto para que cada producto este numerado y se pueda diferenciar del resto, este atributo sera la clave primaria):

productos (id_producto. nombre, descripción, precio, cantidad_disponible, id_categoria)
cliente(id_cliente, nombre, email, telefono)
categoria (id_categoria, nombre_categoria)
pedido(id_pedido, id_cliente, fecha_pedido)
detalle_pedido (id_detalle, id_pedido, id_producto, cantidad)

Clave Primaria (PRIMARY KEY)

productos (id_producto, nombre, descripción, precio, cantidad_disponible, id_categoria)
cliente(id_cliente, nombre, email, telefono)
categoria (id_categoria, nombre_categoria)
pedido(id_pedido, id_cliente, fecha_pedido)
detalle_pedido (id_detalle, id_pedido, id_producto, cantidad)

Clave Externa (FOREIGN KEY)

productos (id_producto, nombre, descripción, precio, cantidad_disponible, id_categoria)
cliente(id_cliente, nombre, email, telefono)
categoria (id_categoria, nombre_categoria)
pedido(id_pedido, id_cliente, fecha_pedido)
detalle_pedido (id_detalle, id_pedido, id_producto, cantidad)

Ejercicio Nº 5 : Identificación de tablas, atributos y restricciones.

Identifica las posibles tablas, atributos, claves primarias y claves externas asociadas del siguiente escenario:

Estamos realizando una aplicación de películas en streaming donde los usuarios pueden examinar el catálogo de películas y seleccionar los productos que deseen.
Cada película tiene un título, año, género y descripción. También conserva la información del director y de la distribuidora de cualquier película.
Para los directores, la base de datos conserva el nombre, la fecha de nacimiento, la biografía y la URL de su página personal.
Para las distribuidoras, la base de datos conserva el nombre, el CIF, el número de teléfono, el email y la URL de su sitio web.

Objetivos:

Identificar tablas, atributos, claves primarias y claves externas de una BD.

Donde:

Negrita –> Clave Primaria (Color amarillo).
Subrayado –> Clave externa (Color verde).

Resolución

Tablas

peliculas
director
distribuidora

Atributos

A parte de los atributos mencionados en el enunciado, recordar que debemos asignar siempre una columna o atributo que lo identifique de forma única:

pelicula (id_pelicula, titula, año, genero, descripcion, id_director, cif_distribuidora)
director (id_director, nombre, fecha_nacimiento, biografia, url)
distribuidora (cif_distribuidora, nombre, telefono, email, url)

Clave Primaria (PRIMARY KEY)

pelicula (id_pelicula, titula, año, genero, descripcion, id_director, cif_distribuidora)
director (id_director, nombre, fecha_nacimiento, biografia, url)
distribuidora (cif_distribuidora, nombre, telefono, email, url)

Clave Externa (FOREIGN KEY)

pelicula (id_pelicula, titula, año, genero, descripcion, id_director, cif_distribuidora)
director (id_director, nombre, fecha_nacimiento, biografia, url)
distribuidora (cif_distribuidora, nombre, telefono, email, url)

Ejercicio Nº 6 : Aplicación de restricciones (Paquetería).

Se pide añadir sobre las tablas creadas anteriormente las restricciones de clave primaria
y clave externa utilizando ALTER TABLE, para ello seguir las indicaciones del grafo
relacional siguiente:

CAMIONERO (dni, nombre, teléfono dirección, población, salario)
PAQUETE (cod_paquete, descripción, destinatario, dirección_destinatario,
dni_camionero)
PROVINCIA (cod_provincia, nombre, cod_paquete)
CAMION (matrícula, modelo, tipo, potencia)
CAMIONERO_CAMION (dni, matricula)

Donde:

Negrita –> Clave Primaria
Subrayado –> Clave externa.

Resolución

Para la resolución de este ejercicio debemos de asignar dos restricciones, en primer lugar, asignaremos las restricciones de PRIMARY KEY o clave primaria y luego asignaremos la clave foránea o externa, conocida como FOREIGN KEY.

Claves primarias (PRIMARY KEY)

				
					ALTER TABLE camionero ADD CONSTRAINT Caminonero_pk PRIMARY KEY (dni);

Desglosemos la sentencia ALTER TABLE de la tabla "camionero" paso a paso:

ALTER TABLE camionero: Esta parte indica que estamos realizando una modificación en la tabla llamada «camionero». Específicamente, estamos añadiendo algo a esta tabla.
ADD CONSTRAINT: Aquí estamos diciendo qué tipo de modificación queremos realizar en la tabla. En este caso, queremos agregar (ADD) una restricción (CONSTRAINT). Una restricción es una regla que se aplica a una o más columnas de una tabla para garantizar la integridad de los datos.
Caminonero_pk: Esto es el nombre que le estamos dando a la restricción que estamos añadiendo. En este caso, «Caminonero_pk» parece indicar que es una restricción de clave primaria (PRIMARY KEY). Es una convención común nombrar las restricciones de clave primaria comenzando con el nombre de la tabla seguido de «_pk» para indicar que es la clave primaria de esa tabla.
PRIMARY KEY (dni): Aquí estamos especificando qué columna o conjunto de columnas va a ser la clave primaria de la tabla «camionero». La clave primaria es una columna o un conjunto de columnas que identifica de forma única cada fila en una tabla. En este caso, estamos indicando que la columna «dni» será la clave primaria de la tabla «camionero».

Entonces, en resumen, la sentencia ALTER TABLE que proporcionaste está diciendo que deseamos agregar una restricción de clave primaria a la tabla «camionero» y que la columna «dni» será esa clave primaria. El nombre de la restricción se establece como «Caminonero_pk». Esto garantiza que cada valor en la columna «dni» será único en la tabla y servirá como identificador único para cada fila en la tabla «camionero».

				
					ALTER TABLE paquete ADD CONSTRAINT paquete_pk PRIMARY KEY (cod_paquete);

				
					ALTER TABLE provincia ADD CONSTRAINT provincia_pk PRIMARY KEY (cod_provincia);

Claves externas (FOREIGN KEY)

Como podemos observar en el enunciado, la tabla camionero no posee ninguna clave externa, pero la tabla paquete posee como clave externa la columna dni, que está relacionada con la columna dni (PRIMARY KEY) de la tabla camionero, la cual hemos asignado anteriormente como clave primaria.

				
					ALTER TABLE paquete ADD CONSTRAINT paquete_fk FOREIGN KEY (dni) REFERENCES camionero (dni);

vamos a desglosar la sentencia paso a paso:

ALTER TABLE paquete: Esto indica que estamos realizando una modificación en la tabla llamada «paquete». Específicamente, vamos a agregar algo a esta tabla.
ADD CONSTRAINT paquete_fk: Estamos agregando una restricción (constraint) a la tabla «paquete» y le estamos dando el nombre «paquete_fk». Este nombre sigue una convención común que indica que es una clave foránea (foreign key) de la tabla «paquete».
FOREIGN KEY (dni): Aquí estamos especificando que la restricción que estamos agregando será una clave foránea. La clave foránea es una columna o conjunto de columnas que establece una relación entre dos tablas. En este caso, la clave foránea será la columna «dni» en la tabla «paquete».
REFERENCES camionero (dni): Aquí estamos indicando la tabla y la columna a la que se hace referencia con la clave foránea. Estamos diciendo que la columna «dni» en la tabla «paquete» hace referencia a la columna «dni» en la tabla «camionero». Esto establece una relación entre las tablas «paquete» y «camionero», donde la columna «dni» en «paquete» hace referencia a los valores únicos de la columna «dni» en «camionero».

En resumen, la sentencia ALTER TABLE que proporcionaste está estableciendo una relación entre las tablas «paquete» y «camionero», donde la columna «dni» en «paquete» actúa como una clave foránea que hace referencia a la columna «dni» en «camionero». Esto asegura la integridad referencial entre las dos tablas.

				
					ALTER TABLE provincia ADD CONSTRAINT provincia_fk FOREIGN KEY (cod_paquete) REFERENCES paquete (cod_paquete);

				
					ALTER TABLE camionero_camion ADD CONSTRAINT camionero_camion_fk1 FOREIGN KEY (dni) REFERENCES camionero (dni);

				
					ALTER TABLE camionero_camion ADD CONSTRAINT camionero_camion_fk2 FOREIGN KEY (matricula) REFERENCES camion (matricula);

Ejercicio Nº 7 : Creación de tablas y aplicación de restricciones.

Tomando como referencia el modelo relacional, se solicita crear las sentencias SQL de creación de tablas y sus restricciones:

Crear las tablas especificadas en el grafo relacional, teniendo en cuenta las reglas de nomenclatura en la creación de las mismas.
Seleccionar el tipo de dato más adecuado para cada una de las columnas que conforman cada una de las tablas.
Establecer las restricciones de clave primaria en cada una de las tablas.
Establecer las restricciones de clave externa que sean necesarias.

ASIENTO(NUMEO_ASIENTO, ID_AVION, CARACTERÍSTICAS)
AVION (ID_AVION, NOMBRE, DESCRIPCIÓN, FECHA)
BILLETE (COD_BILLETE, NOMBRE, APELLIDOS, NUMERO_ASIENTO, ID_AVION)

Donde:

Negrita –> Clave Primaria (Color amarillo).
Subrayado –> Clave externa (Color verde).

				
					ALTER TABLE asiento (
    numero_asiento number(3),
    id_avion varchar2(10) REFERENCES avion (id_avion),
    caracteristicas varchar2(100),
    CONSTRAINT asiento_pk PRIMARY KEY (numero_asiento, id_avion)
);

				
					ALTER TABLE avion (
    id_avion varchar2(10) PRIMARY KEY,
    nombre varchar2(30),
    descripcion varchar2(100),
    fecha date
);

				
					ALTER TABLE billete (
    cod_billete varchar2(10) PRIMARY KEY,
    nombre varchar2(30),
    apellidos varchar2(30),
    numero_asiento numero(3),
    id_avion varchar2(10),
    CONSTRAINT billete_fk FOREIGN KEY (numero_asiento, id_avion) REFERENCES asiento (numero_asiento, id_avion)
);

Ejercicio Nº 8 : Inserción de datos en tablas existentes

A continuación, se te proporcionarán una serie de valores para cada tabla de una base de datos ya creada. Tu tarea es insertar estos datos en las tablas del ejercicio anterior (tablas del ejercicio 5) utilizando la sentencia INSERT INTO.

Las tablas y sus atributos son las siguientes:

CAMIONERO (dni, nombre, teléfono, dirección, población, salario)
PAQUETE (cod_paquete, descripción, destinatario, dirección_destinatario, dni)
PROVINCIA (cod_provincia, nombre, cod_paquete)
CAMION (matrícula, modelo, tipo, potencia)
CAMIONERO_CAMION (dni, matrícula)

A continuación se presentan los valores que deberás insertar en cada tabla:

CAMIONERO:

(12345678A, ‘Juan Pérez’, ‘123456789’, ‘Calle Principal 123’, ‘Ciudad A’, 2000.00)
(987654321, ‘María López’, ‘987654321’, ‘Avenida Central 456’, ‘Ciudad B’, 1800.50)

PAQUETE:

(1001, ‘Electrodomésticos’, ‘Pedro García’, ‘Calle Secundaria 456’, 123456789)
(1002, ‘Libros’, ‘Ana Martínez’, ‘Plaza Mayor 789’, 987654321)

PROVINCIA:

(1, ‘Provincia A’, 1001)
(2, ‘Provincia B’, 1002)

CAMION:

(‘ABC123’, ‘Volvo’, ‘Carga’, 300)
(‘DEF456’, ‘Mercedes’, ‘Pasajeros’, 250)

CAMIONERO_CAMION:

(123456789, ‘ABC123’)
(987654321, ‘DEF456’)

				
					-- Insertar valores en la tabla CAMIONERO
INSERT INTO CAMIONERO (dni, nombre, telefono, direccion, poblacion, salario)
VALUES (123456789, 'Juan Pérez', '123456789', 'Calle Principal 123', 'Ciudad A', 2000.00);
       
INSERT INTO CAMIONERO (dni, nombre, telefono, direccion, poblacion, salario)
VALUES (987654321, 'María López', '987654321', 'Avenida Central 456', 'Ciudad B', 1800.50);

-- Insertar valores en la tabla PAQUETE
INSERT INTO PAQUETE (cod_paquete, descripcion, destinatario, direccion_destinatario, dni)
VALUES (1001, 'Electrodomésticos', 'Pedro García', 'Calle Secundaria 456', 123456789);

INSERT INTO PAQUETE (cod_paquete, descripcion, destinatario, direccion_destinatario, dni)
VALUES (1002, 'Libros', 'Ana Martínez', 'Plaza Mayor 789', 987654321);

-- Insertar valores en la tabla PROVINCIA
INSERT INTO PROVINCIA (cod_provincia, nombre, cod_paquete) VALUES (1, 'Provincia A', 1001);

INSERT INTO PROVINCIA (cod_provincia, nombre, cod_paquete) VALUES (2, 'Provincia B', 1002);

-- Insertar valores en la tabla CAMION
INSERT INTO CAMION (matricula, modelo, tipo, potencia) VALUES ('ABC123', 'Volvo', 'Carga', 300);

INSERT INTO CAMION (matricula, modelo, tipo, potencia) VALUES ('DEF456', 'Mercedes', 'Pasajeros', 250);

-- Insertar valores en la tabla CAMIONERO_CAMION
INSERT INTO CAMIONERO_CAMION (dni, matricula) VALUES (123456789, 'ABC123');

INSERT INTO CAMIONERO_CAMION (dni, matricula) VALUES (987654321, 'DEF456');

Ejercicio Nº 9 : Declaración de consultas 1

Sobre el caso práctico de la base de datos del diagrama siguiente realizar las consultas que se piden:

Número de partidos jugados en febrero.

Id de equipo y suma de las alturas de sus jugadores.

Id de equipo y salario total de cada equipo para equipos con más de 4 jugadores registrados.

Número de ciudades distintas.

Datos del jugador más alto. (No hemos visto todavía subconsultas en el WHERE, es un avance a la siguiente unidad).

				
					-- Número de partidos jugados en febrero
SELECT COUNT(*) FROM partidos WHERE date LIKE '%-02-%';

-- Id de equipo y suma de las alturas de sus jugadores
SELECT id_equipo, SUM(altura) FROM jugadores GROUP BY equipo;

--Id de equipo y salario total de cada equipo para equipos con más de 4 jugadores registrados
SELECT id_equipo, SUM(salario) FROM jugadores GROUP BY equipo HAVING COUNT(*) > 4;

--Número de ciudades distintas.
SELECT COUNT(DISTINCT ciudad) FROM equipos;

-- Datos del jugador más alto.
SELECT * FROM jugadores WHERE altura = (SELECT MAX(altura) FROM jugadores);

Ejercicio Nº 10 : Declaración de consultas 2

Sobre el caso práctico de la base de datos del diagrama siguiente, realizar las consultas que se piden:

Datos de los jugadores con equipo, más nombre del equipo y ciudad.
Datos de los jugadores libres (sin equipo).
Datos de todos los jugadores, mostrando el nombre de su equipo si lo tienen.
Es la primera jornada y queremos saber las ciudades de los equipos que aún no han jugado como locales.
Tras la primera jornada queremos saber un listado de las ciudades en las que hay equipos junto con el resultado de los partidos que han tenido lugar en ellas.
Listado de jugadores, con el nombre y apellidos de su capitán al lado si lo tienen.
Es la sexta jornada y queremos sacar todos los equipos y, si se han enfrentado con el Alcoyano como locales, poner el resultado.

				
					-- Datos de los jugadores con equipo, más nombre del equipo y ciudad.
SELECT jugadores.*, e.nombre, e.ciudad FROM jugadores j (INNER) JOIN equipos e ON j.equipo = e.id_equipo; 

-- Datos de los jugadores libres (sin equipo).
SELECT nombre, apellido FROM jugadores WHERE id_equipo IS NULL;

--Datos de todos lo jugadorer, mostrando el nombre, de su equipo si lo tienen.
SELECT j.*, e.nombre FROM jugadores j (INNER) JOIN euipo e ON j. = e.id_equipo;

-- Es la primera jornada y queremos saber las ciudades de los equipos que aún no han jugado como locales.
SELECT e.ciudad FROM equipos e WHERE NOT EXISTS (SELECT p.id_partidos FROM partidos p WHERE p.elocal = e.id_equipo);

-- Tras la primera jornada queremos saber un listado de las ciudades en las que hay equipos junto con el resultado de los partidos que han tenido lugar en ellas.

Ejercicio Nº 11 : Declaración de consultas 3

Sobre el caso práctico de la base de datos del diagrama del esquema HR de Oracle realizar las consultas que se piden:

Consultar el nombre del departamento y su mánager.
Repetir la consulta anterior, para que aparezca primero el mánager y luego el nombre del departamento.
Consultar el nombre, el apellido, el salario y la fecha de contratación de los empleados.
Consultar el id del país, su nombre y el id del continente al que pertenece.
Consultar todos los datos almacenados en la tabla de trabajos.
Consultar todos los datos almacenados en la tabla que almacena los continentes o regiones.
Consultar el apellido, el salario y el salario mensual (salario divido entre 12) de un empleado.
Consultar el apellido, id del trabajo y salario de un empleado concatenando el valor de estas columnas y renombrado la columna resultante de manera identificativa.

				
					-- Consultar el nombre del departamento y su mánager.
SELECT department_name, manager_id FROM departments;

				
					
-- Repetir la consulta anterior, para que aparezca primero el mánager y luego el nombre del departamento.
SELECT manager_id, department_name FROM departments;

				
					-- Consultar el nombre, el apellido, el salario y la fecha de contratación de los empleados.
SELECT firt_name, last_name, salary, hire_date FROM employees;

				
					-- Consultar el id del país, su nombre y el id del continente al que pertenece.
SELECT country_id, country_name, region_id FROM countries;

				
					-- Consultar todos los datos almacenados en la tabla de trabajos.
SELECT * FROM jobs;

				
					-- Consultar todos los datos almacenados en la tabla que almacena los continentes o regiones.
SELECT * FROM regions;

				
					
-- Consultar el apellido, el salario y el salario mensual (salario divido entre 12) de un empleado.
SELECT last_name, salary, salary/12 FROM employees;

				
					
-- Consultar el apellido, id del trabajo y salario de un empleado concatenando el valor de estas columnas y renombrado la columna resultante de manera identificativa.
SELECT last_name||job_id||salary AS tabla FROM employees;

Ejercicio Nº 12 : Declaración de consultas 4

Sobre el caso práctico de la base de datos del diagrama del esquema HR de Oracle realizar las consultas que se piden:

Desarrolle una consulta que muestre solo los nombres de los empleados que no se repiten.
Hallar el salario medio, el máximo y el mínimo para cada grupo de empleados por puesto de trabajo, mostrar sólo los que tienen el puesto de trabajo REP y el salario medio excede a 8000

				
					-- Desarrolle una consulta que muestre solo los nombres de los empleados que no se repiten.
SELECT DISTINCT first_name FROM employees

				
					-- Hallar el salario medio, el máximo y el mínimo para cada grupo de empleados por puesto de trabajo, mostrar sólo los que tienen el puesto de trabajo REP y el salario medio excede a 8000
SELECT avg(salary), max(salary), min(salary) 
FROM employees 
GROUP BY job_id 
HAVING job_id LIKE '%REP%' AND count(*)>1 AND avg(salary)>8000;

				
					- Contar los empleados que llevan más de 15 años trabajados hasta hoy agrupándolos por número de años trabajados.SELECT avg(salary), max(salary), min(salary) 
FROM employees 
GROUP BY job_id 
HAVING job_id LIKE '%REP%' AND count(*)>1 AND avg(salary)>8000;

Ejercicio Nº 13 : Declaración de consultas 5

Sobre el caso práctico de la base de datos del diagrama del esquema HR de Oracle realizar las consultas que se piden:

Consultar los países asociados a cada continente, las columnas que se desea que aparezcan como parte de esta columna son: region_id, region_name, country_name.
Consultar los departamentos que se encuentran ubicados fuera de estados unidos.
Consultar todos los países que pertenezcan al continente asiático.
Para cada departamento obtener la ciudad donde se encuentra situado (city), y el id de su localización (location_id). Realizar esta consulta mediante NATURAL JOIN.
Para cada departamento obtener la ciudad donde se encuentra situado (city), teniendo en cuenta que la ciudad donde está situado el departamento no puede empezar por S. Realizar esta consulta mediante USING.
Consultar los nombres de empleados y nombre sus departamentos correspondientes.

Consultar los países asociados a cada continente, las columnas que se desea que aparezcan como parte de esta columna son: region_id, region_name, country_name.

				
					SELECT r.region_id, r.region_name, c.country_name FROM countries NATURAL JOIN regions;

Consultar los departamentos que se encuentran ubicados fuera de estados unidos.

				
					SELECT department_name, city, country_id 
FROM departments 
NATURAL JOIN locations WHERE country_id != 'US';

Consultar todos los países que pertenezcan al continente asiático.

				
					SELECT country_id 
FROM countries 
NATURAL JOIN reginos WHERE region_name = 'Asia';

Para cada departamento obtener la ciudad donde se encuentra situado (city), y el id de su localización (location_id). Realizar esta consulta mediante NATURAL JOIN.

				
					SELECT department_id, city, location_id 
FROM departments 
NATURAL JOIN locations;

Para cada departamento obtener la ciudad donde se encuentra situado (city), teniendo en cuenta que la ciudad donde está situado el departamento no puede empezar por S. Realizar esta consulta mediante USING.

				
					SELECT department_id, city 
FROM departments 
USING locations WHERE city NOT LIKE = 'locations%';

Consultar los nombres de empleados y nombre sus departamentos correspondientes.

				
					SELECT first_name, department_name
FROM empployees 
JOIN departments USING (department_id);

				
					-- También sería correcto de la siguinte forma:
SELECT first_name, department_name
FROM e.empployees 
JOIN d.departments ON (e.department_id = d.department_id);

Ejercicio Nº 14 : Consultas usando OUTER JOIN

Sobre el caso práctico de la base de datos del diagrama siguiente, realizar las consultas que se piden:

Datos de los jugadores con equipo, más nombre del equipo y ciudad.
Datos de los jugadores libres (sin equipo).
Datos de todos los jugadores, mostrando el nombre de su equipo si lo tienen.
Es la primera jornada y queremos saber las ciudades de los equipos que aún no han jugado como locales.
Tras la primera jornada queremos saber un listado de las ciudades en las que hay equipos junto con el resultado de los partidos que han tenido lugar en ellas.
Listado de jugadores, con el nombre y apellidos de su capitán al lado si lo tienen.
Es la sexta jornada y queremos sacar todos los equipos y, si se han enfrentado con el Alcoyano como locales, poner el resultado.

Datos de los jugadores con equipo, más nombre del equipo y ciudad.

				
					SELECT j.*, e.nombre, e.ciudad 
FROM jugadores j 
JOIN equipos e
ON j.equipo = e.id_equipo;

Datos de los jugadores libres (sin equipo).

				
					SELECT * FROM jugadores WHERE id_equipo IS NULL;

Datos de todos los jugadores, mostrando el nombre de su equipo si lo tienen.

				
					SELECT j.*, e.nombre FROM jugadores j INNER JOIN equipos e ON equipo = id_equipo;

Es la primera jornada y queremos saber las ciudades de los equipos que aún no han jugado como locales.

				
					SELECT e.ciudad, e.nombre 
FROM equipos e 
WHERE NOT EXISTS (SELECT id_partido FROM partidos p WHERE p.elocal = e.id_equipo);

Tras la primera jornada queremos saber un listado de las ciudades en las que hay equipos junto con el resultado de los partidos que han tenido lugar en ellas.

				
					SELECT e.ciudad, p.resultado 
FROM equipos e LEFT JOIN partidos p ON e.id_equipo = p.elocal);

Listado de jugadores, con el nombre y apellidos de su capitán al lado si lo tienen.

				
					SELECT jugador.nombre, capitan.nombre, capitan.apellidos 
FROM jugadores capitan 
RIGHT JOIN jugadores jugador ON capitan.id_jugador = jugador.id_capitan);

Es la sexta jornada y queremos sacar todos los equipos y, si se han enfrentado con el Alcoyano como locales, poner el resultado.

				
					SELECT e.nombre, p.resultado 
FROM equipo e LEFT JOIN partidos p ON e.id_equipo = p.elocal 
JOIN equipos alc ON p.evisitante = alc.id_equipo WHERE alc.nombre = "Alcoyano";

Ejercicio Nº 15 : Declaración de subconsultas 1

Sobre el caso práctico de la base de datos del diagrama siguiente, realizar las consultas que se piden:

Datos del jugador más alto.
Suma de alturas de los jugadores del Cai y del Madrid.
Datos de jugadores de equipos que hayan jugado algún partido contra el valencia en casa.
Nombre de jugadores que midan más que todos los del caja laboral.
Datos de jugadores cuyo salario sea mayor que el de sus capitanes.
Datos del equipo con más jugadores registrados.
Nombre de los jugadores mejor y peor pagados.

Datos del jugador más alto.

				
					SELECT * FROM jugadores WHERE altura = (SELECT max(altura) FROM jugadores);

Suma de alturas de los jugadores del Cai y del Madrid.

				
					SELECT sum(altura) 
FROM jugadores 
WHERE equipo IN (SELECT id_equipo FROM equipos WHERE nombbre LIKE = '%cai%' OR nombre LIKE '%madrid%')

Datos de jugadores de equipos que hayan jugado algún partido contra el valencia en casa.

				
					SELECT * FROM jugadores WHERE equipos IN (SELECT elocal FROM partidos WHERE evisitante = (SELECT id_equipos WHERE nombre LIKE = '%Valencia%');

Nombre de jugadores que midan más que todos los del caja laboral.

				
					SELECT nombre FROM jugadores WHERE altura > ALL(SELECT altura FROM jugadores WHERE equipo IN (SELECT id_equipo FROM equipos WHERE nombre LIKE '%Laboral%'));

Datos de jugadores cuyo salario sea mayor que el de sus capitanes.

				
					SELECT * FROM jugadores a 
WHERE a.salario > (SELECT b.salary FROM jugadores b WHERE a.id_capitan = b.id_jugador);

Datos del equipo con más jugadores registrados.

				
					SELECT * 
FROM equipos WHERE id_equipo IN 
(SELECT equipo FROM (SELECT COUNT(*) 
                     AS nj, equipo 
                     FROM jugadores 
                     GROUP BY equipo) 
                     AS A wHERE a.nj IN(SELECT count(*) 
                                        AS nj, equipo 
                                        FROM jugadores GORUP Y equipo) 
                                        AS b));

Nombre de los jugadores mejor y peor pagados.

				
					SELECT nombre FROM jugadores WHERE salario IN (SELECT max(salario) 
FROM jugadores) OR salario IN  (SELECT min(salario) FROM jugadores);

Ejercicio Nº 16 : Declaración de subconsultas 2

Sobre el caso práctico de la base de datos del diagrama del esquema HR de Oracle realizar las consultas que se piden:

Realice una consulta que muestre aquellos empleados que tengan un sueldo superior a los del empleado cuyo apellido (last_name) es Abel.
Consulta que empleados tienen el mismo trabajo que el empleado que tiene el id = 141.
Realiza una consulta que muestre los datos de los departamentos que no tiene un trabajador asignado.

Realice una consulta que muestre aquellos empleados que tengan un sueldo superior a los del empleado cuyo apellido (last_name) es Abel.

				
					SELECT first_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

Consulta que empleados tienen el mismo trabajo que el empleado que tiene el id = 141.

				
					SELECT first_name, job_id FROM employees WHERE job_id = (SELECT jod_id FROM employees WHERE employee_id = 141)

Realiza una consulta que muestre los datos de los departamentos que no tiene un trabajador asignado.

				
					SELECT * 
FROM departments 
WHERE NOT EXISTS (SELECT employee_id FROM employees WHERE employee.employe_id = department.department_id);

Ejercicio Nº 17 : Declaración de subconsultas 3

Sobre el caso práctico de la base de datos del diagrama del esquema HR de Oracle realizar las consultas que se piden:

Escriba una consulta que permita obtener el apellido (last_name), el id del trabajo (job_id) y el salario (salary) de aquellos empleados, cuyo id del trabajo es igual que el del empleado cuyo apellido es “Colmenares” y tengan mayor salario que dicho empleado.
Escriba una consulta que muestre el apellido (last_name), el id del trabajo (job_id) y el salario (salary) de aquellos empleados cuyo salario sea igual al salario mínimo.
Escriba una consulta que muestre el apellido (last_name) del empleado que gana el salario máximo de la empresa.
Escriba una consulta que muestre todos los datos de los departamentos que tienen un salario mínimo mayor que el del departamento 50.
Escriba una consulta que muestre el número de empleado (employee_id) y el apellido (last_name) de todos los empleados que trabajan en un departamento con un empleado cuyo apellido contiene la letra “u”.
Escriba una consulta que muestre el apellido (last_name), el número de departamento (department_id) y el ID del trabajo (job_id) de todos los empleados cuyo ID de localización (location_id) sea el 1700.

Escriba una consulta que permita obtener el apellido (last_name), el id del trabajo (job_id) y el salario (salary) de aquellos empleados, cuyo id del trabajo es igual que el del empleado cuyo apellido es “Colmenares” y tengan mayor salario que dicho empleado.

				
					SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Colmenares')
AND salary > (SELECT salary FROM employees WHERE last_name = 'Colmenares');

Escriba una consulta que muestre el apellido (last_name), el id del trabajo (job_id) y el salario (salary) de aquellos empleados cuyo salario sea igual al salario mínimo.

				
					SELECT last_name, job_id, salary 
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

Escriba una consulta que muestre el apellido (last_name) y el salario (salary) del empleado que gana el salario máximo de la empresa.

				
					SELECT last_name, salary 
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Escriba una consulta que muestre todos los datos de los departamentos que tienen un salario mínimo mayor que el del departamento 50.

				
					SELECT *
FROM departments
WHERE (SELECT MIN(salary) 
      FROM employees 
      WHERE department_id = departments.department_id) > (SELECT MIN(salary) 
                                                          FROM employees 
                                                          WHERE department_id = 50);

Escriba una consulta que muestre el número de empleado (employee_id) y el apellido (last_name) de todos los empleados que trabajan en un departamento con un empleado cuyo apellido contiene la letra “u”.

				
					SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);

Escriba una consulta que muestre el apellido (last_name), el número de departamento (department_id) y el ID del trabajo (job_id) de todos los empleados cuyo ID de localización (location_id) sea el 1700.

				
					SELECT e.last_name, e.department_id, e.job_id
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE l.location_id = 1700;

Ejercicio Nº 18 : Inserción de consultas

Sobre el caso práctico de la base de datos del diagrama siguiente, realizar las consultas que se piden:

Se creará una tabla equipos_filiales como copia de la tabla de equipos, y contendrá las mismas tuplas pero el campo “nombre” tendrá el mismo valor que en las tuplas originales, pero con la cadena “ Filial” añadida al final y con los puntos a cero.
Borrar todos los partidos como visitante del equipo con nombre “ALCOYANO”
Subir un 10 % el salario de los jugadores que hayan jugado un partido el 24 de Diciembre de 2018.

Realizar consultas (inserción, borrado y eliminación) en las que se utilicen
subconsultas.

Se creará una tabla equipos_filiales como copia de la tabla de equipos, y
contendrá las mismas tuplas pero el campo “nombre” tendrá el mismo valor que en las tuplas originales, pero con la cadena “ Filial” añadida al final y con los puntos a cero.

				
					-- En primer lugar creamos la tabla "equipos_filiales"
CREATE TABLE equipos_filiales (
    id_equipo INT(11) CONSTRAINT id_equipo_pk PRIMARY KEY,
    nombre_filial VARCHAR2(45) NOT NULL,
    ciudad VARCHAR2(45),
    web VARCHAR2(250),
    puntos INT(11)
);

				
					-- Ahora insertamos las mismas tuplas pero cambiando el campo de nombre por el de Filial.
-- Primero consultamos que los datos son los deaseados:
SELECT nombre || "Filial", ciudad, web, puntos FROM equipos;

-- Luego insertamos los datos en la nueva tabla.
INSERT INTO equipos_filiales (nombre_filial, ciudad, web, puntos);
SELECT nombre || "Filial", ciudad, web, puntos FROM equipos;

Borrar todos los partidos como visitante del equipo con nombre “ALCOYANO”

				
					-- Consulta
SELECT id_equipos FROM equipos e WHERE e.nombre = "ALCOYANO" AND e.id_equipos = p.evisitante;

-- Borrado de los datos 
DELETE FROM partidos p WHERE EXISTS (SELECT id_equipos 
                                     FROM equipos e 
                                     WHERE e.nombre = "ALCOYANO" AND e.id_equipos = p.evisitante;);

Subir un 10 % el salario de los jugadores que hayan jugado un partido el 24 de Diciembre de 2018.

				
					-- Consulta 
SELECT id_partido 
FROM partidos p, equipos e 
WHERE (e.id_equipo = p.elocal OR e.id_quipo = p.evisitante) -- Realacionamos las tablas partidos y equipos.
AND j.id_quipo = e.id_quipo -- Relacionamos la talba equpos con jugadores. 
AND p.fecha = '24-DEC-2018'; -- Indicamos el filtro, en este caso por la fecha de la tabla partidos.

-- Modificar los datos para subir el salario un 10%:
UPDATE jugadores j SET salario = salario * 1.10
WHERE EXISTS (
SELECT id_partido 
FROM partidos p, equipos e 
WHERE (e.id_equipo = p.elocal OR e.id_quipo = p.evisitante) 
AND j.id_quipo = e.id_quipo
AND p.fecha = '24-DEC-2018');

Ejercicio Nº 19 : Uso de INSERT, UPDATE y DELETE

Sobre el caso práctico de la base de datos del diagrama siguiente, ya usada en un ejercicio anterior, realizar las consultas que se piden:

Crear un nuevo equipo con nombre “RACING DE GRANADA”, ubicado en la ciudad de Granada con 0 puntos e id_equipo 78.
Añadir a Jugadores un jugador que se llame “Antonio”, “Martínez” con identificador 789 que pertenezca al equipo 78 y con fecha de alta el momento de inserción.
Hacer definitivas las inserciones anteriores.
Actualizar el salario del jugador 789 a 10000 y su altura a 1.88.
Anular la actualización anterior.
Borrar todos los jugadores con “Antonio” y “Martínez” como nombre y apellido.
Borrar todos los jugadores sin capitán.
Insertar un partido entre el equipo 15 y el 78 con el identificador 50 y como resultado “4 a 1” y como árbitro “GARCIA PEREZ”.
Hacer efectivos los cambios.

Crear un nuevo equipo con nombre “RACING DE GRANADA”, ubicado en la ciudad de Granada con 0 puntos e id_equipo 78.

				
					INSERT INTO equipos (id_equipo, nombre, ciudad, puntos) 
VALUES(78, 'RACIING DE GRANADA', 'GRANADA', 0);

Añadir a Jugadores un jugador que se llame “Antonio”, “Martínez” con identificador 789 que pertenezca al equipo 78 y con fecha de alta el momento de inserción.

				
					INSERT INTO jugadores (id_jugador, nombre, apellido, fecha_alta, equipo) 
VALUES(789, 'Antnio', 'Martínez', SYSDATE, 78);

Hacer definitivas las inserciones anteriores.

				
					COMMIT;

Actualizar el salario del jugador 789 a 10000 y su altura a 1.88.

				
					UPDATE jugadores SET salario = 10000, altura = 1.88 WHERE id_jugador= 789;

Anular la actualización anterior.

				
					ROLLBACK;

Borrar todos los jugadores con “Antonio” y “Martínez” como nombre y apellido.

				
					DELETE FROM jugadores WHERE nombre = 'Antonio' AND apellidos='Martínez';

Borrar todos los jugadores sin capitán.

				
					DELETE FROM jugadores WHERE capitan IS NULL

Insertar un partido entre el equipo 15 y el 78 con el identificador 50 y como resultado “4 a 1” y como árbitro “GARCIA PEREZ”.

				
					INSERT INTO partidos (id_partido, elocal, evisitante, resultado, arbitro) 
VALUES (50, 15, 78, "4 a 1", "Garcia Perez");

Hacer efectivos los cambios.

				
					COMMIT;

Ejercicio Nº 20 : Uso de transacciones en Oracle

Sobre el caso práctico de la base de datos del diagrama siguiente, ya usada en un ejercicio anterior, realizar las consultas que se piden:

Hacer efectivas todas las transacciones previas que hubiera en la sesión.
Hacer un punto de guardado de la transacción con el nombre “carga_victoria_Alcoyano».
Sumar 3 a los puntos que tenga el equipo denominado “ALCOYANO”.
Al descubrirse que hay un error anular el cambio y devolver la base de datos al estado anterior.
Hacer un punto de guardado de la transacción con el nombre “carga_empate_Alcoyano’.
Sumar 1 a los puntos que tenga el equipo denominado “ALCOYANO”.
Sumar 1 a los puntos que tenga el equipo denominado “NUMANCIA”.
Hacer efectivos, los cambios.

Hacer efectivas todas las transacciones previas que hubiera en la sesión.

				
					COMMIT;

Hacer un punto de guardado de la transacción con el nombre “carga_victoria_Alcoyano».

				
					SAVEPOINT carga_victoria_Alcoyano;

Sumar 3 a los puntos que tenga el equipo denominado “ALCOYANO”.

				
					UPDATE equipos SET puntos = puntos + 3 WHERE nombre = 'Alcoyano';

Al descubrirse que hay un error anular el cambio y devolver la base de datos al estado anterior.

				
					ROLLBACK TO SAVEPOINT carga_victoria_Alcoyano;

Hacer un punto de guardado de la transacción con el nombre “carga_empate_Alcoyano’.

				
					SAVEPOINT carga_empate_Alcoyano;

Sumar 1 a los puntos que tenga el equipo denominado “ALCOYANO”.

				
					UPDATE equipos SET puntos = puntos + 1 WHERE nombre = 'ALCOYANO';

Sumar 1 a los puntos que tenga el equipo denominado “NUMANCIA”.

				
					UPDATE equipos SET puntos = puntos + 1 WHERE nombre = 'NUMANCIA';

Hacer efectivos, los cambios.

				
					COMMIT;

Ejercicio Nº 21 : Sentencias de borrado de datos

Sobre el caso práctico de la base de datos del diagrama siguiente, ya usada en un ejercicio anterior, realizar las consultas que se piden:

Insertar en la base de datos dos departamentos nuevos y dos empleados nuevos que pertenezcan a estos.
Cambiar los puestos de trabajo de los nuevos empleados a otros que vamos a agregar.

Insertar en la base de datos dos departamentos nuevos y dos empleados nuevos que pertenezcan a estos.

				
					-- Inserción de los nuevos departamentos por separado.
INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) 
VALUES (280, 'Secret Investments', 1700);

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) 
VALUES (290, 'Armored Suits', 1700);

				
					-- Inserción de los nuevos departamentos por separado.
INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) 
VALUES (280, 'Secret Investments', 1700),
       (290, 'Armored Suits', 1700);

Si intentamos añadir directamente los empleados antes que los departamentos, obtendremos un error.

				
					-- Inserción de los usuarios por separado.
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID) 
VALUES (207, 'Robert', 'Redford', 'RRMAIL', SYSDATE, 'FI_MGR', 280);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)
VALUES (208, 'Anthony', 'Stark', 'SIMAIL', SYSDATE, 'MK_MAN', 290);

				
					-- Inserción de los usuarios en una única sentencia.
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)
VALUES 
    (207, 'Robert', 'Redford', 'RRMAIL', SYSDATE, 'FI_MGR', 280),
    (208, 'Anthony', 'Stark', 'SIMAIL', SYSDATE, 'MK_MAN', 290);

Cambiar los puestos de trabajo de los nuevos empleados a otros que vamos a agregar. Para ello, vamos a insertar primero los puestos y después actualizaremos la tabla EMPLOYEES.

				
					-- Insertamos primer puesto de trabajo
INSERT INTO JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) 
VALUES ('AR_ENG', 'Armour Engineer', 10000, 28000);

-- Insertamos segundo puesto de trabajo
INSERT INTO JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) 
VALUES ('SC_CONT', 'Secret Contact', 20000, 35000);

-- Asignamos el trabajador a al primer puesto de trabajo nuevo creado.
UPDATE EMPLOYEES SET JOB_ID = 'AR_ENG' WHERE EMPLOYEE_ID = 208;

-- Asignamos el trabajador a al segundo puesto de trabajo nuevo creado.
UPDATE EMPLOYEES SET JOB_ID = 'SC_CONT' WHERE EMPLOYEE_ID = 207;

Contenido

Ejercicio Nº 0 : Mi primera base de datos.

Ejercicio Nº 1 : Mi primera tabla.

Ejercicio Nº 2 : Tablas y atributos (Empresa de transporte).

Ejercicio Nº 3 : Tablas y atributos (Gestión de empleados).

Ejercicio Nº 4 : Aplicación de restricciones básicas (Tienda).

Resolución

Tablas

Atributos

Clave Primaria (PRIMARY KEY)

Clave Externa (FOREIGN KEY)

Ejercicio Nº 5 : Identificación de tablas, atributos y restricciones.

Resolución

Tablas

Atributos

Clave Primaria (PRIMARY KEY)

Clave Externa (FOREIGN KEY)

Ejercicio Nº 6 : Aplicación de restricciones (Paquetería).

Resolución

Claves primarias (PRIMARY KEY)

Claves externas (FOREIGN KEY)

Ejercicio Nº 7 : Creación de tablas y aplicación de restricciones.

Ejercicio Nº 8 : Inserción de datos en tablas existentes

Ejercicio Nº 9 : Declaración de consultas 1

Ejercicio Nº 10 : Declaración de consultas 2

Ejercicio Nº 11 : Declaración de consultas 3

Ejercicio Nº 12 : Declaración de consultas 4

Ejercicio Nº 13 : Declaración de consultas 5

Ejercicio Nº 14 : Consultas usando OUTER JOIN

Ejercicio Nº 15 : Declaración de subconsultas 1

Ejercicio Nº 16 : Declaración de subconsultas 2

Ejercicio Nº 17 : Declaración de subconsultas 3

Ejercicio Nº 18 : Inserción de consultas

Ejercicio Nº 19 : Uso de INSERT, UPDATE y DELETE

Ejercicio Nº 20 : Uso de transacciones en Oracle

Ejercicio Nº 21 : Sentencias de borrado de datos