Practico SQL 2015 | Johnson Armando .edu

Bases de Datos – Año 2015

Práctico SQL Ejercicio 1: Esquema HR (Recursos Humanos) Nota: Ejercicio para resolver en Oracle con Herramienta iSQLplus Cuando se realiza la instalación de la Base de Datos de Oracle, por defecto se crean esquemas de ejemplo, para poder realizar pruebas. Uno de ellos es el esquema HR que trata de Recursos Humanos, a continuación se muestra el esquema HR.

Ahora resuelva las siguientes consultas planteadas usando el lenguaje SQL. 1. Mostrar el nombre (first_name y last_name) y el salario (salary) de todos los empleados. Cambie el nombre de la columna first_name a Nombre y last_name a Apellido y salary a Salario. 2. Mostrar el nombre (first_name y last_name) y el salario (salary) de todos los empleados ordenado alfabéticamente por apellido (last_name). 3. Mostrar el apellido (last_name) y la Comisión (commission_pct) que perciben los empleados cuya comisión sea mayor a 0.25. Mostrarlos ordenado en forma descendente por last_name. 4. Mostrar la cantidad de empleados que trabajan en el departamento 100. 5. Mostrar todos los datos de los departamentos con identificador 10 ó 70 (department_id). 6. Mostrar el nombre (last_name) de los empleados junto al nombre del departamento (department_name) donde trabajan. 7. Mostrar el nombre (last_name) de los empleados de los departamentos de Finanzas (Finance) y/o Transporte (Shipping). 8. Mostrar sin repetir los tipos de trabajos (jobs) que realizan en los departamentos los empleados. Liste el el identificador del departamento y el nombre del trabajo. 9. Mostrar los departamentos en los que los empleados realizan trabajos de Contabilidad (Accountant) y Asistente de Administración (Administration Assistant). Liste el identificador del departamento y el nombre del trabajo. NOTA: En este caso no hay tuplas que satisfagan la intersección. 10. Mostrar el nombre de los departamentos que tienen al menos 3 empleados. 11. Mostrar el nombre de los empleados (last_name) y el salario (salary) de aquellos empleados que tienen un salario mayor que el salario promedio. Ordene el listado en forma descendente por salario. 12. Mostrar el salario máximo de los empleados del departamento 110. Ordene el listado en forma ascendente por salario. 13. Mostrar el nombre del/de los empleados que tienen el sueldo máximo del departamento 110. Ordene el listado en forma ascendente por salario. 14. Busque todos los empleados que no sean supervisores (o directores). Nota: En la tabla departments el atributo manager_id tiene la identificación de los supervisores de cada dpto.

1

Bases de Datos – Año 2015

Ejercicio 2: Resuelva lo solicitado en cada Caso Caso 1: Deportes Nota: Ejercicio para resolver con Herramienta SQLive y iSQLplus PERS (Personas) = {Dni, Nom, Sexo, Edad, Dir, Tel, Zona} DEPO (Deportes) = {Codd, Nomd, Clase} CLUB (Clubes) = {Codc, Nomc, Dir, Tel, Cat, Zona} PRAC (Practican) = {Dni, Codd, Codc} a) 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Resuelva las consultas planteadas usando el lenguaje SQL. Obtener el nombre de las personas que practican (simultáneamente) los deportes D01, D22 y D10 (códigos). Obtener el nombre de las personas que no practican deportes en Ausonia. Obtener el DNI de las personas que practican deportes en Ausonia o bien practican en la UVT. Obtener el código de los deportes que son practicados solamente por hombres. Obtener nombre y dirección de las personas que viven en la misma zona que Juan Pérez, DNI 20.498.425. Obtener el nombre de las personas mayores que Ana García (DNI: 18.498.425). Obtener el nombre de las personas que practican en clubes que están ubicados en la misma zona en la que ellos viven. Obtener el nombre de las personas junto al nombre del deporte que práctica. Se deben incluir todas las personas registradas más allá que no estén practicando ningún deporte. Obtener el nombre de los deportes practicados en todos los clubes. Obtener los clubes (todos sus datos) en los que se practican todos los deportes de balón (clase). Obtener las personas (todos sus datos personales) que practican todos los deportes. Obtener el nombre de las personas que practican todos los deportes que se practican en el Banco Hispano.

b) Sobre el esquema de base de dato planteado en el Caso 1 - Deportes enuncie las consultas en SQL para actualizarla: 1. Inserte nuevas Personas (deportista) con los siguientes datos: < 14895399, Pérez Edgar, M, 35, Hipólito Irigoyen 528, 4222935, Rawson > < 24835599, González Rosa, F, 50, Hipólito Irigoyen 528, 4229115, Rivadavia > < 34895399, Fernández Luis, M, 28, Hipólito Irigoyen 528, 4293538, Santa Lucia > < 17895399, Ruiz Analía, F, 18, Hipólito Irigoyen 528, 4258912, Pósito > 2. Inserte el nuevo deporte con los siguientes datos: < d99 , Tenis , terrestre > 3. Inserte el nuevo club con los siguientes datos: < c99, Universidad, 25 de Mayo, 4311425, cat, Capital > 4. Inserte la nueva tupla para practica con los siguientes datos: < 14895399, d99, c99 > 5. Actualice el teléfono del Club UVT por el número 4312548. 6. Elimine el Deporte “Tenis”, controle si le permite realizar el borrado de la tupla. c) Sobre el esquema de base de dato planteado en el Caso 1 - Deportes realice las siguientes vistas: 1. Una Vista llamada Atletas que tenga los siguientes atributos dni, nombre, sexo, edad. 2. Una Vista llamada Futbolistas que tenga los siguientes atributos dni, nombre, edad y tel.

2

Bases de Datos – Año 2015

Caso 2 – Sistema de Facturación Nota: Ejercicio para resolver con Herramienta SQLive y iSQLplus CLI (Cliente) = {dni, nya, sexo, edad, dir, tel, zona} RU (Rubros) = {codr, desc_ru} ART (Artículo) = {coda, desc_art, stock, pto_rep (punto de reposición), codr, precio (precio actual)} FAC (Factura) = {nrofact, fecha, dni} REM (Remito) = {nrorem, fecha, nrofact} LÍ-F (Línea-Factura) = { nrofact, coda, cant, precio (precio al momento de la venta)} LÍ-R (Línea-Remito) = { nrorem, coda, cant} a)

Resuelva las consultas planteadas usando el lenguaje SQL.

1. Mostrar el código, la descripción y el stock de los artículos. 2. Obtener los artículos (código del artículo, descripción y stock) cuyo stock es mayor a 100 y que pertenezcan al rubro R01. 3. Obtener los artículos que se deberían reponer (código del artículo, descripción y stock); es decir, el stock es menor o igual al punto de reposición. 4. Mostrar a que rubro pertenece cada artículo (obteniendo descripción del artículo y del rubro). 5. Obtener el código y la descripción del artículo, de aquellos artículos que pertenecen al rubro limpieza. 6. Obtener los números de facturas que se emitieron desde el 10/01/2014 hasta el 10/04/2014. 7. Mostrar el nombre de los clientes que viven en la zona de Santa Lucia. 8. Obtener el nombre de los clientes que viven en la misma zona que Rita Pérez (DNI: 38.498.425). 9. Obtener el nombre de los clientes que han comprado más de un producto; es decir, en la misma factura compro distintos tipos de productos sin importar la cantidad de c/u de ellos. 10. Mostrar el nombre de los clientes que viven en la zona de Santa Lucia o en la zona de Rawson o en la zona de Rivadavia. 11. Mostrar los clientes (el dni y el nombre) que han comprado artículos del rubro limpieza y también han comprado artículos del rubro perfumería. 12. Mostrar los clientes (el dni y el nombre) que no viven en la zona de Capital. 13. Mostrar los clientes (el dni y el nombre) que no han comprado artículos del rubro librería. 14. Mostrar las facturas (todos los datos) que posean artículos pendientes; es decir, las facturas que poseen artículos pendientes (faltan enviar artículos). Considere que siempre se entrega la cantidad total en el remito; es decir, la cantidad de la factura coincide con la cantidad del remito). 15. Mostrar los clientes (todos los datos) que entre sus facturas donde han comprado todos los artículos. 16. Mostrar la cantidad total de desodorante Rexona Men que se han vendido. 17. Mostrar el nombre del artículo menos vendido (en cantidad total). b) Sobre el esquema de base de dato planteado en el Caso 2 - Sistema de Facturación, enuncie las consultas en SQL para actualizarla: 1. Inserte nuevos Clientes con los siguientes datos: < 14895399, Perez Edgar, M, 35, Hipólito Irigoyen 528, 4222935, Rawson > < 24835599, González Rosa, F, 50, Hipólito Irigoyen 528, 4229115, Rivadavia > < 34895399, Fernández Luis, M, 28, Hipólito Irigoyen 528, 4293538, Santa Lucia > < 17895399, Ruiz Analía, F, 18, Hipólito Irigoyen 528, 4258912, Pósito > 2. Inserte el nuevo rubro con los siguientes datos: < R08 , Lacteos > 3. Inserte el nuevo artículo con los siguientes datos: < A21, Yogurt Entero, 100, 25, R08, 10 > 4. Actualice el teléfono de la Cliente González Rosa por el número 4312548. c) Sobre el esquema de base de dato planteado en el Caso 2 - Sistema de Facturación, realice las siguientes vistas: 1. Una Vista llamada Reponer que contenga los siguientes atributos: coda, desc_art, stock, pto_rep. La vista deberá obtener los artículos que se deberán reponer.

3

Bases de Datos – Año 2015

Caso 3 - Alumnos de las Carrera LCC Nota: Ejercicio para resolver con Herramienta SQLive y iSQLplus ALU (Alumno) = {dni, nomAlu, dir, tel} MAT (Materia) = {codM, nomM, despliegue, año} CORRE (Correlativas) = {codM, codMc}1 INSC (Inscripto) = {dni, codM, fecha}2 BOL (Boleta) = {dni, codM, fecha}3 EXA (Examen) = {dni, codM, fecha, nota} Notas: 1- Todas las correlatividades son examen con examen. 2- Un alumno se inscribe una sola vez en una materia. Puede estar inscripto en materias de años distintos. 3- Las boletas no tienen vencimiento.

a)

Resuelva las consultas planteadas usando el lenguaje SQL.

1. 2. 3. 4. 5. 6. 7.

Alumnos (todos los datos) que nunca tuvieron aplazos. Exámenes (nombre del alumno, nombre de la materia, fecha y nota) rendidos en forma regular. Alumnos (nombre) que han rendido (aprobado) Bases de Datos y Bases de Datos Avanzadas. Alumnos (dni y nombre) que han sacado boleta de todas las materias de primer y segundo año. Alumnos (dni y nombre) que están inscriptos en materias de distintos años. Materias (todos los datos) que tienen algún inscripto en el presente año. Alumnos (todos sus datos personales) que están en condiciones de rendir Bases de Datos; es decir, tener rendidas sus correlativas además de haber obtenido la boleta. 8. Alumnos que han rendido más de una vez mal la misma materia. 9. Nombre de cada materia, junto a cada una de sus correlativas (nombre también). b) Sobre el esquema de base de dato planteado en el Caso 3 - Alumnos de las Carrera LCC, enuncie las consultas en SQL para actualizarla: 1. Inserte nuevos Alumnos con los siguientes datos: < 14895399, Ruiz Mathías, Hipólito Irigoyen 528, 4222935 > < 24835599, González Emiliano, Av. Central 128 -O-, 4229115 > 2. Inserte la nueva materia con los siguientes datos: < 24, Base de Datos Avanzadas, Primer Semestre, 5 > 3. Actualice el despliegue de la Materia Base de Datos por Anual. c) Sobre el esquema de base de dato planteado en el Caso 3 - Alumnos de las Carrera LCC, realice las siguientes vistas: 1. Una Vista llamada Insc-BD que contenga los siguientes atributos: dni, codM, fecha. La vista deberá obtener los alumnos que se inscribieron para cursar Base de Datos. 2. Una Vista llamada Boletas-BD que contenga los siguientes atributos: dni, codM, fecha. La vista deberá obtener los alumnos que tienen boleta de Base de Datos.

4

Bases de Datos – Año 2015

Caso 4 - Accidentes de Tránsito Nota: Ejercicio para resolver con Herramienta SQLive y iSQLplus COMPSEG (Compañía de Seguros) = {cuit, nomC, domi, tel} VEHI (Vehículos) = {pat, modelo, tipo (Moto, Auto, Camioneta o Camión), cuit, cm, dni} MARCAS = {cm, descrip} PERS (Personas) = {dni, nom, edad, domi, tel} ACTA = {nroActa, lugar, fecha, hora} INVO (Involucrados) = {nroActa, pat, dni, conductor (si/no), daño (sin daño, leve, grave o fatal)} a)

Resuelva las consultas planteadas usando el lenguaje SQL.

1. Obtener el nombre de las compañías de Seguros que tienen asegurados todos los tipos de vehículos. 2. Obtener todos los datos de los vehículos que no han tenido accidentes fatales. 3. Obtener la patente de los autos involucrados en choques, en los que viajaba solamente el conductor (es decir, no tenía acompañantes). 4. Obtener todos los datos de las personas que han chocado manejando su propio vehículo. 5. Obtener las personas (dni y nombre) que poseen más de un vehículo asegurado en la misma compañía de Seguros. 6. Obtener las personas (dni y nombre) que estuvieron involucradas en accidentes con daño “leve” y también estuvieron involucradas en accidentes sin daño. b) Sobre el esquema de base de dato planteado en el Caso 4 - Accidentes de Tránsito, enuncie las consultas en SQL para actualizarla: 1. Inserte nuevas Personas con los siguientes datos: < 14895399, Ruiz Mathías, 34, Hipólito Irigoyen 528, 4222935 > < 24835599, González Emiliano, 56, Av. Central 128 -O-, 4229115 > 2. Actualice el teléfono y el domicilio de la persona González Emiliano por el número 4312548 y el domicilio Juan Jufre 345 -N-. c) Sobre el esquema de base de dato planteado en el Caso 4 - Accidentes de Tránsito, realice las siguientes vistas: 1. Una Vista llamada AccidentesLeves que contenga los siguientes atributos: dni y nombre. La vista deberá obtener las personas que estuvieron involucradas en accidentes con daño “leve”.

5

Bases de Datos – Año 2015

Caso 5 - Banco Nota: Ejercicio para resolver con Herramienta SQLive y iSQLplus PERS (Personas) = {Dni, Nombre, Dirección, Local, Tel} CCTES (Cuentas Corrientes) = {Nro3, Fecha, ImporteInicial, SaldoActual2, Dni11, Dni21} CAHORRO (Cajas de Ahorro) = {Nro3, Fecha, ImporteInicial, SaldoActual, Dni11, Dni21} TRANSAC = {NroTrans, Nro (de cuenta corriente o caja de ahorro), Fecha, Importe, IdTipo, Saldo2} TIPO_TRANS = {IdTipo, Desc} Notas: 1- Tanto las cuentas corrientes como las cajas de ahorro pueden tener dos titulares. 2- Los saldos de las cuentas corrientes pueden ser negativos. 3- Los números de cuentas corrientes y los de cajas de ahorro son disjuntos.

a)

Resuelva las consultas planteadas usando el lenguaje SQL.

1. 2. 3. 4. 5.

Cajas de ahorros (todos sus datos) que aún no tienen movimientos. Nombre y DNI de todos los clientes del banco. Nombre de las personas que poseen alguna cuenta corriente o caja de ahorro. Cuentas corrientes que nunca generaron saldos negativos. Personas (dni y nombre) que tienen dinero disponible (tienen saldo a favor en alguna cta. corriente y/o en alguna caja de ahorro). 6. Cuentas corrientes (número y saldo) a las que se les efectuaron todos los tipos de transacción posible. 7. Personas (todos los datos) que efectuaron en más de una ocasión transacciones en sus cuentas corrientes en rojo; es decir, con saldo negativo. b) Sobre el esquema de base de dato planteado en el Caso 5 - Banco, enuncie las consultas en SQL para actualizarla: 1. Inserte nuevos Personas con los siguientes datos: < 14895399, Rodríguez Mathías, Hipólito Irigoyen 528, 4222935 > < 24835599, Gomez Emiliano, Av. Central 128 -O-, 4229115 > 2. Actualice el teléfono de la persona Rodríguez Mathías por el número 4312548. c) Sobre el esquema de base de dato planteado en el Caso 5 - Banco, realice las siguientes vistas: 1. Una Vista llamada Clientes-CtaCte que contenga los siguientes atributos: dni y nombre. La vista deberá obtener las personas que posean alguna cuenta corriente en el Banco. 2. Una Vista llamada Clientes-CajaAhorro que contenga los siguientes atributos: dni y nombre. La vista deberá obtener las personas que posean alguna caja de ahorro en el Banco.

6