SQL (Structured Query Language) Las operaciones sobre la base de datos están determinadas por el Lenguaje SQL, bajo estándares determinados los cuales pueden ser utilizadas de forma básica o avanzada según el DBMS utilizado. • • • • • • • • •
SELECT Operaciones Adicionales Operaciones Adicionales Operaciones Adicionales Operaciones Adicionales Operaciones Adicionales Operaciones Adicionales Tutorial Sql (.pdf) EJERCICIOS
1 2 3 4 5 6
Las operaciones SQL correspondientes al SELECT se realizarán con el siguente ejempo:
PERSONAS Ced Cedula
Nombre
71134534 Juan
Prim rimer_A er_Ap pellid llido o Segund undo_Ape Apellid llido o Sexo Dir Direcc ección ión Mesa
Telef elefo ono Salari lario o
Cedu edula_ la_Sup Cod_ od_dep
Uribe
M
Cra 25 22-1 2567532 1,600,000 23 2 3423445
3
23423445 Ana María Be Betancur
Bermudez
F
Cra 45 1113
3433444 444 1,700 ,700,0 ,00 00 438902 90231
2
12453535 Gloria
Betancur
Garces
F
Tr. 12 43-5 2756533 1,350,000 71134534
3
75556743 Pe Pedro
Ochoa
Pelaez
M
Cll.6ta 1445
2686885 885 1,700 ,700,0 ,00 00 438902 90231
1
43533322 Patricia
Angel
Guzmán
F
Cll. 45 23-1 2674563 1,350,000 71134534
3
78900456 Carlos
Betancur
Agudelo
M
Cir. 5 12-5
1
4445775 1,500,000 75556743
73456789 Mario
Gómez
Angel
M
Cr. 53 23-1 3456789 1,200,000 23423445
2
43890231 Claudia
Gonzalez
Beltran
F
Cll. 10 14-1 2660356 1,800,000 43890231
0
78900700 Fabio
Solano
Pérez
M
Tr. 3 32-1
1
4345678 1,200,000 75556743
DEPENDIENTES Cedula
Nombre_Dep
Sexo
FechaN
Parentesco
78900456
Juanita
F
12-Abr-95
Hija
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
Teleinformatica
75556734
2
Desarrollo
23423445
3
Soporte Técnico
71134534
PROYECTOS Numero_Proy
Nombre
Lugar
Codigo_Dep
129001
Registro y Matrícula
Bloque 21
2
139001
Red Lan
Bloque 14
1
139002
Instalación nuevo Switche
Bloque 21
1
129002
Notas
Campus
2
129003
Paso de aplicativos FOXPRO A COBOL
Bloque 21
2
149001
Inventario de HW y SW
Minas
3
149002
Licenciamiento
Campus
3
149003
Evaluación de equipos PC's
Bloque 18
3
1. OPERACIÓN SELECT La sintaxis básica de esta operación es:
SELECT FROM WHERE 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:
π
cedula, nombre
(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 Similar la operación el álgebra relacional sería:
π
nombre, primer_apellido, segundo_apellido, direccion, telefono
(σ
cedula = 1245353
(PERSONAS) )
Resultado/ Nombre
Primer_Apellido
Segundo_Apellido
Dirección
Telefono
Gloria
Betancur
Garces
Tr. 12 43-5
2756533
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
Nombre
78900456
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
2. EJERCICIOS CON LA CLAUSULA SELECT 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.
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.
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. 4. Liste los terminales que tienen el mayor tráfico en un día (haga el ejemplo con cualquier fecha). 5. Muestre los viajes con los correspondientes transportes, que tuvieron más de 50 pasajero con reservas.
