Se debe transcribir las tablas y los datos de las mismas para REALIZAR LOS SIGUIENTES EJERCICIOS.
Las operaciones SQL correspondientes al SELECT se realizarán con el siguente ejemplo:
PERSONAS
DEPENDIENTES
DEPARTAMENTOS
PROYECTOS
La sintaxis básica de esta operación es:
SELECT <lista de atributos>
FROM <lista de tablas>
WHERE <condiciones>
Ejemplos:
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
Similar la operación el álgebra relacional sería:
p cedula, nombre (PERSONAS)
Resultado/
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
Similar la operación el álgebra relacional sería:
p nombre, primer_apellido, segundo_apellido, direccion, telefono (scedula = 1245353 (PERSONAS) )
Resultado/
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/
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/
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/
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
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);