Ejercicios Resueltos SQL PDF

Preview

Summary

Download Ejercicios Resueltos SQL PDF

Description

EJERCICIOS RESUELTOS SQL Dadas las siguientes tablas responda a las consultas en SQL

1. Lista r los da tos de los a utor es select * from autor ; 2. Lista r nombr e y eda d de los estudia ntes select nombre,edad from estudiante ; 3. ¿Qué estudia ntes per tenecen a la ca r r er a de Industr ia s? select nombre from estudiante where carrera="Industrias" ; 4. Lista r los nombr es de los estudia ntes cuyo a pellido comience con la letr a G? SELECT nombre FROM estudiante WHERE nombre LIKE "* G*" ; (observación: el primer * es el nombre, luego de un espacio en blanco viene G y luego cualquier expresión)

5. ¿Quiénes son los autores del libro “Visual Studio Net”, listar solamente los nombres? SELECT nombre FROM autor WHERE idautor IN ( SELECT idautor FROM libaut WHERE idlibro IN ( SELECT idlibro FROM libro WHERE titulo='Visual Studio Net' ) ); 6. ¿Qué a utor es son de na ciona lida d USA o Fr a ncia ?

SELECT * FROM autor WHERE nacionalidad ='USA' OR nacionalidad ='Francia' ; SELECT * FROM autor WHERE nacionalidad IN('USA','Francia') ;

7. ¿Qué libr os No Son del Ar ea de Inter net? SELECT * FROM libro WHERE area 'Internet' ;

8. ¿Qué libros se prestó el Lector “Raul Valdez Alanes”? SELECT * FROM libro WHERE idlibro IN ( SELECT idlibro FROM prestamo WHERE idlector IN ( SELECT idlector FROM estudiante WHERE nombre='Raul Valdez Alanes' ) );

9. Lista r el nombr e del estudia nte de menor eda d SELECT nombre FROM estudiante WHERE edad IN ( SELECT min(edad) FROM estudiante ); 10. Lista r los nombres de los estudiante que se pr esta r on Libr os de Base de Da tos SELECT * FROM estudiante WHERE idlector IN ( SELECT idlector FROM prestamo WHERE idlibro IN ( SELECT idlibro FROM libro WHERE area='Base de Datos' ) ); 11. Lista r los libr os de editor ial AlfayOmega SELECT * FROM libro WHERE editorial ='AlfaOmega' 12. Lista r los libr os que per tenecen al autor Mar io Benedetti SELECT * FROM libro WHERE idlibro IN ( SELECT idlibro FROM libaut WHERE idautor IN ( SELECT idautor FROM autor WHERE nombre='Benedetti Mario' ) )

13. Lista r los títulos de los libr os que debían devolver se el 10/04/07 SELECT * FROM libro WHERE idlibro IN ( SELECT idlibro FROM prestamo WHERE fechadevolucion=#04/10/07# AND devuelto=No ) 14. Ha llar la suma de la s eda des de los estudia ntes SELECT sum(edad) AS [La suma de las edades es: ] FROM estudiante 15. Lista r los da tos de los estudia ntes cuya eda d es mayor al pr omedio SELECT * FROM estudiante WHERE edad > ( SELECT avg(edad) FROM estudiante ) 0

2 Las operaciones SQL correspondientes al SELECT se realizarán con el siguente ejempo: PERSONAS Nombre

Primer_Apelli Segundo_Apellido Sexo do

Dirección

Telefono

Salario

71134534

Juan

Mesa

Uribe

M

Cra 25 22-1

2567532

1,600,000 23423445

3

23423445

Ana María

Betancur

Bermudez

F

Cra 45 11-13 3433444

1,700,000 43890231

2

12453535

Gloria

Betancur

Garces

F

Tr. 12 43-5

2756533

1,350,000 71134534

3

75556743

Pedro

Ochoa

Pelaez

M

Cll.6ta 14-45 2686885

1,700,000 43890231

43533322

Patricia Angel

Guzmán

F

Cll. 45 23-1

2674563

1,350,000 71134534

1 3

78900456

Carlos

Betancur

Agudelo

M

Cir. 5 12-5

4445775

1,500,000 75556743

1

73456789

Mario

Gómez

Angel

M

Cr. 53 23-1

3456789

1,200,000 23423445

43890231

Claudia Gonzalez

Beltran

F

Cll. 10 14-1

2660356

1,800,000 43890231

2 0

78900700

Fabio

Pérez

M

Tr. 3 32-1

4345678

1,200,000 75556743

1

Solano

DEPENDIENTES Cedula

Nombre_Dep

Sexo FechaN

Parentesco

78900456

Juanita

F

Hija

12-Abr-95

Cedula_Su p

Cod_de p

Cedula

78900456

Oscar

M

15-Ene-89

Hijo

23423445

Hector

M

23-Dic-67

Cónyuge

71134534

María

F

05-Mar-60

Cónyuge

71134534

Gloria

F

27-Nov-97

Hija

75556734

Jorge

M

14-Mar-96

Hijo

DEPARTAMENTOS Codigo_Dep Nombre_Dep

Cedula_Jefe

0

Gerencia

43890231

1 2

Teleinformatica 75556734 Desarrollo 23423445

3

Soporte Técnico

71134534

PROYECTOS Numero_Proy Nombre 129001 Registro y Matrícula

Lugar Bloque 21

Codigo_Dep 2

139001

Red Lan

Bloque 14

1

139002

Instalación nuevo Switche

Bloque 21

1

129002

Notas

Campus

2

129003

Paso de aplicativos FOXPRO Bloque 21 A COBOL

2

149001 149002

Inventario de HW y SW Licenciamiento

Minas Campus

3 3

149003

Evaluación de equipos PC's

Bloque 18

3

a. Select básico. Se desea obtener la cédula y el nombre de todas las personas que trabajan en la compañía. SELECT cedula, nombre FROM personas Resultado/

Cedula

Nombre

71134534

Juan

23423445

Ana María

12453535

Gloria

75556743

Pedro

43533322

Patricia

78900456

Carlos

b. Select con clausula WHERE. Se desea obtener toda la información de la persona cuya cédula sea igual a 12453535. SELECT nombre,primer_apellido,segundo_apellido,direccion,telefono FROM personas WHERE cedula = 12453535 Resultado/ Nombre Primer_Apellido Segundo_Apellido

Dirección

Gloria

Tr. 12 43-5 2756533

Betancur

Garces

Telefono

c. En la clausula WHERE es posible utilizar los conectores lógicos AND - OR. Se necesita la cédula y el nombre de las personas cuyo apellido sea BETANCUR y su sexo sea MASCULINO: SELECT cedula,nombre FROM personas WHERE primer_apellido = 'Betancur' AND sexo = 'M' Resultado/ Cedula 78900456

Nombre Carlos

d. Select combinando tablas y utilización del comodín '*'. Se desea obtener la información de todos los dependientes de las personas cuyo apellido sea BETANCUR y su sexo sea MASCULINO. Cuando se trabaja con varias tablas y se utiliza el '*', se le debe anteponer el nombre de la tabla de la cual se desea extraer la información: SELECT dependientes.* FROM personas, dependientes WHERE primer_apellido = 'Betancur'

AND sexo = 'M' AND dependiente.cedula = personas.cedula Resultado/ Cedula

Nombre_Dep

Sexo FechaN

Parentesco

78900456

Juanita

F

12-Abr-95

Hija

78900456

Oscar

M

15-Ene-89

Hijo

e. Utilizando alias o sinónimos de trabajo a las tablas del Select. Estos se utilizan por facilidad en el manejo de la instrucción. La misma consulta anterior: SELECT d.* FROM personas p, dependientes d WHERE primer_apellido = 'Betancur' AND sexo = 'M' AND d.cedula = p.cedula Resultado/ Cedula

Nombre_Dep

Sexo FechaN

Parentesco

78900456

Juanita

F

12-Abr-95

Hija

78900456

Oscar

M

15-Ene-89

Hijo

f. Cuando se necesita extraer información distintiva dentro de un grupo de tuplas, se utiliza la clausula DISTINCT. Por ejemplo, se necesita extraer los diferentes valores de salarios que se pagan en la compañía: SELECT distinct salario FROM personas Resultado/ Salario 1,600,000 1,700,000 1,350,000 1,500,000 1,200,000 1,800,000

g. Una de las clausulas más significativas en el Select es el COUNT, la cual se utiliza para contar la cantidad de registros que cumplen con una condición específica: g.1 Mostrar el total de empleados en la compañía: SELECT count(*) FROM personas Resultado/ 9 g.2 Mostrar el total de proyectos que tiene asignada la dependencia 3 SELECT count(*) FROM proyectos WHERE codigo_dep = 3 Resultado/ 3 g.3 Mostrar cuántos salarios diferentes o distintas se pagan en la compañía: SELECT count(distinct salario) FROM personas Resultado/ 6 h. Cláusula WHERE compara sus campos comunmente con valores únicos, pero tambien es posible comparar con un "conjunto" de valores. Esto es realizable a través del operador IN. Ejemplo, se desea saber qué empleados están involucrados en los proyectos 139001 o 139002. h.1 Forma básica: SELECT personas.* FROM personas, proyectos WHERE (numero_proy = 139001 OR numero_proy =139002) AND cod_dep = codigo_dep h.2 Forma con IN: SELECT personas.* FROM personas, proyectos WHERE numero_proy IN (139001,139002) AND cod_dep = codigo_dep

Resultado/ PENDIENTE i. Operación Select con anidamientos. La clausula WHERE comunmente compara los campos con valores exactos, pero también es probable utilizarla comparando sus campos con otras sentencias Select. Esta forma también es llamada Consulta anidada: i.1 Mostrar los diferentes proyectos en donde el ingeniero OCHOA participa: SELECT distinct numero_proy FROM proyectos WHERE numero_proy IN (select numero_proy from proyectos p, departamentos d, personas where p.codigo_dep = d.codigo_dep and primer_apellido = 'Ochoa') i.2 Mostrar los empleados cuyo jefe es de apellidos BETANCUR BERMUDEZ: SELECT personas.* FROM personas WHERE cedula_sup IN (select cedula from personas where primer_apellido = 'Betancur' and segundo_apellido = 'Bermudez') i.3 Mostrar el nombre de los empleados cuyo salario es mayor que el de todos los empleados del departamento 3. Aquí se utiliza la utilización de la cláusula ALL: SELECT nombre, primer_apellido, segundo_apellido FROM personas WHERE salario > ALL (select salario from personas where cod_dep = 3) j. En el select es posible validar la existencia de información nula a través de la cláusula NULL. Ejemplo, Mostrar los empleados que no tengan asignado salario: SELECT * FROM personas WHERE salario IS NULL k. Otra cláusula que es posible utilizar en el Select es EXIST, la cual ayuda a validar si el resultado de una consulta anidada es vacio o no.

k.1 Seleccionar todos los empleados cuyo dependiente tenga la misma cedula, sexo y nombre. SELECT p.nombre, p.primer_apellido, p.segundo_apellido FROM personas p WHERE EXIST (select * from dependiente d where p.cedula = d.cedula and d.sexo = p.sexo and nombre = nombre_dep) k.2 Seleccionar los empleados que no tienen dependientes: SELECT p.nombre, p.primer_apellido, p.segundo_apellido FROM personas p WHERE NOT EXIST (select * from dependiente d where p.cedula = d.cedula) l. Con la operación de Select también es posible utilizar funciones agregadas para: sumar (SUM), maximizar (MAX), minimizar (MIN) y promediar (AVG). Se pueden utilizar al nivel de la cláusula SELECT o en la cláusual HAVING (que veremos posteriormente. Ejemplo, el total pagado por la compañía, el máximo y el mínimo salario y el promedio pagado: SELECT sum(salario), max(salario), min(salario), avg(salario) FROM personas

m. Agrupación de tuplas y aplicación de condiciones para ellas. Aquí se utilizan dos cláusulas nuevas: GROUP BY , la cual agrupa tuplas según las columnas puestas en la cláusula Select; HAVING, permite hacer operaciones sobre estas agrupaciones. Veamos: m.1 Mostrar el número y el nombre del proyecto en donde trabajen más de dos empleados SELECT nombre, numero_proy FROM proyectos, trabaja_en WHERE numero_proy = nump GROUP BY nombre, numero_proy HAVING count(*) > 1 n. La cláusula WHERE además de las anteriores instrucciones también puede utilizar la instrucción LIKE, que le sirve para encontrar información string no precisa. Veamos el siguiente ejemplo:

SELECT nombre, numero_proy FROM proyectos WHERE nombre LIKE '%lic%' o. En la cláusula Select también es posible realizar operaciones aritméticas '+', '-', '*', con los campos de valor: SELECT salario*1.18 FROM personas WHERE salario < 1200000 p. Una cláusula más que podemos utilizar en la operación Select es la que me permite dale un orden a las tuplas, ORDER BY , según el o los criterios indicados a través de columnas. SELECT * FROM personas ORDER BY nombre, primer_apellido, segundo_apellido

3. Se tiene el siguiente esquema de base de datos para el manejo de información de un Sistema de Transportes intermunicipales: TERMINALES_TRANSPORTE (cod_terminal, nombre, ciudad, estado) VIAJES(número, transportadora, días) TARIFAS(num_viaje, cod_tarifa, monto, restricciones) TRAYECTO_VIAJE(num_viaje, num_trayecto, cod_terminal_sale, hora_salida_programada, cod_terminal_llega, hora_llegada_programada) VIAJES_REALIZADOS(num_viaje, num_trayecto, fecha, num_asientos_disponibles, id_transporte, cod_terminal_sale, hora_salida, cod_terminal_llega, hora_llegada) VIAJES_AUTORIZADOS(tipo_transporte, cod_terminal) TRANSPORTE(id_transporte, total_de_asientos, tipo_transporte) RESERVA_ASIENTOS(num_viaje, num_trayecto, fecha, num_asiento, nombre_cliente, tel_cliente)

El anterior esquema describe una base de datos con información sobre viajes de líneas aéreas. Cada VIAJE se identifica con un número de viaje, y consta de uno o más TRAYECTO_VIAJE con num_trayecto 1, 2, 3, etc. Cada trayecto tiene horas y terminales de salida y de llegada programados, y tiene muchos TRAYECTO_VIAJE, uno por cada fecha en que tiene lugar el viaje. Se mantienen TARIFAS para cada viaje. Para cada

movimiento de trayecto, se mantiene RESERVA_ASIENTOS, el transporte empleado en el trayecto y las horas de salida y llegada y los terminales específicos. Un TRANSPORTE se identifica con id_transporte y es de un cierto tipo_transporte. VIAJES AUTORIZADOS relaciona los tipo_transporte con los terminales en los que puede aterrizar. Cada TERMINAL se identifica con un cod_terminal. Especifique las siguientes consultas: 1. Prepare una lista con los números de viaje y los días de todos los viajes o trayectos de viaje que salen del terminal codigo ‘CA001’ y llegan al terminal código ‘BO001. Solución 1: SELECT num_viaje, num_trayecto,fecha FROM viajes_realizados WHERE cod_terminal_sale = 'CA001' AND cod_terminal_llega = 'BO001'; Solución 2: SELECT distinct numero, dias FROM viajes_realizados, viajes WHERE cod_terminal_sale = 'CA001' AND cod_terminal_llega = 'BO001' AND numero = num_viaje

2. Obtenga una lista con los números de viaje, códigos de terminal de salida, horas de salida programadas, códigos de terminal de llegada, horas de llegada programadas y días de todos los viajes o trayectos de viajes que salgan de algún terminal de la ciudad de Santa Marta y lleguen a algún terminal de la ciudad de Buenaventura. Solución 1: SELECT tv.*, dias FROM trayecto_viaje tv, terminales_transporte tt, viajes WHERE (ciudad = 'Santa Marta' AND cod_terminal_sale = tt.cod_terminal) AND (ciudad = 'Buenaventura' AND cod_terminal_llega = tt.cod_terminal) AND (numero = num_viaje); Solución 2: SELECT tv.*, dias FROM trayecto_viaje tv, viajes WHERE cod_terminal_sale = (SELECT cod_terminal FROM terminales_transporte WHERE ciudad = 'Santa Marta') AND cod_terminal_llega = (SELECT cod_terminal FROM terminales_transporte

WHERE ciudad = 'Buenaventura') AND numero = num_viaje; 3. Liste las diferentes tarifas que se aplicaron a los viajes que se realizaron entre los terminales de Santa Marta y Medellín, en el año 1999. Solución 1: SELECT distinct cod_tarifa, monto FROM viajes_realizados vr, tarifas ta, terminales_transporte WHERE (ciudad = 'Santa Marta' AND cod_terminal_sale = cod_terminal) AND (ciudad = 'Medellín' AND cod_terminal_llega = cod_terminal) AND fecha between '01/01/00' and '31/12/99' AND ta.num_viaje = vr.num_viaje; Solución 2: SELECT distinct cod_tarifa, monto FROM viajes_realizados vr, tarifas ta, terminales_transporte WHERE cod_terminal_sale = (SELECT cod_terminal FROM terminales_transporte WHERE ciudad = 'Santa Marta') AND cod_terminal_llega = (SELECT cod_terminal FROM terminales_transporte WHERE ciudad = 'Medellín') AND fecha between '01/01/00' and '31/12/99' AND ta.num_viaje = vr.num_viaje;

4. Liste los terminales que tienen el mayor tráfico en un día (haga el ejemplo con cualquier fecha). CREATE TABLE tmp (term varchar2(5), total number(10)); INSERT INTO tmp (term, total) SELECT cod_terminal, count(*) FROM terminales_transporte, viajes_realizados WHERE fecha = '21/10/00' AND cod_terminal_sale = cod_terminal OR cod_terminal_llega = cod_terminal GROUP BY cod_terminal; SELECT term, MAX(total) FROM tmp GROUP BY term;

5. Muestre los viajes con los correspondientes transportes, que tuvieron más de 50 pasajero con reservas.

SELECT num_viaje, id_transporte FROM viajes_realizados WHERE num_viaje IN (SELECT num_viaje FROM reserva_asientos GROUP BY num_viaje HAVING count(*) > 50);

____________________________________________________

Creación de tablas de una base de datos Estructura general de la instrucción en SQL para crear una tabla Se usa la instrucción CREATE TABLE nombre de la tabla ( campo1 tipo de dato1, campo2 tipo de dato2, etc. );

Tipos de datos en Oracle Los tipos de datos en Oracle son: NUMBER - se usa para especificar valores numéricos. Ejemplos: NUMBER(10) - especifica un entero de 10 dígitos NUMBER (10,2) - especifica un número de 10 dígitos, donde 2 lugares son decimales. NUMBER - es el "default". CHAR - se usa para especificar cadenas de caracteres de TAMAÑO FIJO. Requiere que el dato esté entre comillas sencillas. Ejemplos: CHAR(3) - especifica un tamaño fijo de 3 caracteres CHAR - es el "default" y su tamaño es de un caracter. VARCHAR2 - se usa para especificar cadenas de caracteres de TAMAÑO VARIABLE.

Requiere que el dato esté entre comillas sencillas. Ejemplos: VARCHAR2(3) - especifica un tamaño fijo de 3 caracteres VARCHAR2 - es el "default" y su tamaño es de .... DATE - se usa para especificar fechas con formato dd-mmm-yy ó dd-mmm-yyyy. Requiere que la fecha se especifique entre comillas sencillas. Ejemplos: '08-mar-99' es un dato válido cuando el dato fue guardado en la base de datos con ese formato. '08-mar-1999' es un dato válido cuando el dato fue guardado en la base de datos con ese formato. BOOLEAN - se usa para valores de cierto o falso. Puede asumir los valores TRUE, FALSE o NULL. Hay otros tipos de datos tales como: LONG, RAW, LONG RAW y otros.

"CONSTRAINT" Sirven para especificar condiciones sobre los campos, tales como especificar los "primary key", los "foreign key" o requisitos de validación. A cada ¨constraint¨ se le identtifica con un nombre, ese nombre debe ser alusivo a su función o propósito.

"PRIMARY KEY" - se puede especificar en forma directa cuando se

declara el campo al crear la tabla o por medio de un "constraint" explícitamente, en particular cuando es compuesto.

Ejemplos CREATE TABLE tabla1 ( campo1 number(7) PRIMARY KEY, campo2 varchar2(5),

etc. ); CREATE...