EJERCICIOS RESUELTOS SQL.pdf

EJERCICIOS RESUELTOS

SQL

Dadas las siguientes tablas responda a las consultas en SQL

1. Listar los datos de los autores

select *

from autor ;

2. Listar nombre y edad de los estudiantes

select nombre,edad

from estudiante ;

3. Qu estudiantes pertenecen a la carrera de Industrias?

select nombre

from estudiante

where carrera="Industrias" ;

4. Listar los nombres de los estudiantes cuyo apellido comience con la letra G?

SELECT nombre

FROM estudiante

WHERE nombre LIKE "* G*" ;

(observacin: el primer * es el nombre, luego de un espacio en blanco viene G y luego cualquier expresin)

5. Quines 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 autores son de nacionalidad USA o Francia?

SELECT *

FROM autor

WHERE nacionalidad ='USA' OR nacionalidad ='Francia' ;

SELECT *

FROM autor

WHERE nacionalidad IN('USA','Francia') ;

7. Qu libros No Son del Area de Internet?

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. Listar el nombre del estudiante de menor edad

SELECT nombre

FROM estudiante

WHERE edad IN

(

SELECT min(edad)

FROM estudiante

) ;

10. Listar los nombres de los estudiante que se prestaron Libros de Base de Datos

SELECT *

FROM estudiante

WHERE idlector IN

(

SELECT idlector

FROM prestamo

WHERE idlibro IN

(

SELECT idlibro

FROM libro

WHERE area='Base de Datos'

)

) ;

11. Listar los libros de editorial AlfayOmega

SELECT *

FROM libro

WHERE editorial ='AlfaOmega'

12. Listar los libros que pertenecen al autor Mario Benedetti

SELECT *

FROM libro

WHERE idlibro IN

(

SELECT idlibro

FROM libaut

WHERE idautor IN

(

SELECT idautor

FROM autor

WHERE nombre='Benedetti Mario'

)

)

13. Listar los ttulos de los libros que deban devolverse el 10/04/07

SELECT *

FROM libro

WHERE idlibro IN

(

SELECT idlibro

FROM prestamo

WHERE fechadevolucion=#04/10/07#

AND devuelto=No

)

14. Hallar la suma de las edades de los estudiantes

SELECT sum(edad) AS [La suma de las edades es: ]

FROM estudiante

15. Listar los datos de los estudiantes cuya edad es mayor al promedio

SELECT *

FROM estudiante

WHERE edad > ( SELECT avg(edad) FROM estudiante )

0

2 Las operaciones SQL correspondientes al SELECT se realizarn con el siguente ejempo:

PERSONAS

Cedula Nombre Primer_Apellido

Segundo_Apellido Sexo Direccin Telefono Salario Cedula_Sup

Cod_de

p

71134534 Juan Mesa Uribe M Cra 25 22-1 2567532 1,600,000 23423445 3

23423445 Ana Mara

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 1

43533322 Patricia Angel Guzmn F Cll. 45 23-1 2674563 1,350,000 71134534 3

78900456 Carlos Betancur Agudelo M Cir. 5 12-5 4445775 1,500,000 75556743 1

73456789 Mario Gmez 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 Prez M Tr. 3 32-1 4345678 1,200,000 75556743 1

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 Cnyuge

71134534 Mara F 05-Mar-60 Cnyuge

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

Tcnico 71134534

PROYECTOS

Numero_Proy Nombre Lugar Codigo_Dep

129001 Registro y Matrcula Bloque 21 2

139001 Red Lan Bloque 14 1

139002 Instalacin 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 Evaluacin de equipos PC's Bloque 18 3

a. Select bsico. Se desea obtener la cdula y el nombre de todas las personas que trabajan

en la compaa.

SELECT cedula, nombre

FROM personas

Resultado/

Cedula Nombre

71134534 Juan

23423445 Ana Mara

12453535 Gloria

75556743 Pedro

43533322 Patricia

78900456 Carlos

b. Select con clausula WHERE. Se desea obtener toda la informacin de la persona cuya

cdula sea igual a 12453535.

SELECT nombre,primer_apellido,segundo_apellido,direccion,telefono

FROM personas

WHERE cedula = 12453535

Resultado/

Nombre Primer_Apellido Segundo_Apellido Direccin Telefono

Gloria Betancur Garces Tr. 12 43-5 2756533

c. En la clausula WHERE es posible utilizar los conectores lgicos AND - OR. Se necesita

la cdula 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 utilizacin del comodn '*'. Se desea obtener la informacin

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 informacin:

SELECT dependientes.*

FROM personas, dependientes


AND sexo = 'M'

AND dependiente.cedula = personas.cedula

Resultado/




e. Utilizando alias o sinnimos de trabajo a las tablas del Select. Estos se utilizan por

facilidad en el manejo de la instruccin. La misma consulta anterior:

SELECT d.*

FROM personas p, dependientes d


AND sexo = 'M'

AND d.cedula = p.cedula

Resultado/




f. Cuando se necesita extraer informacin 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 compaa:

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 ms significativas en el Select es el COUNT, la cual se utiliza para

contar la cantidad de registros que cumplen con una condicin especfica:

g.1 Mostrar el total de empleados en la compaa:

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 cuntos salarios diferentes o distintas se pagan en la compaa:

SELECT count(distinct salario)

FROM personas

Resultado/

6

h. Clusula WHERE compara sus campos comunmente con valores nicos, pero tambien es

posible comparar con un "conjunto" de valores. Esto es realizable a travs del operador

IN. Ejemplo, se desea saber qu empleados estn involucrados en los proyectos 139001 o

139002.

h.1 Forma bsica:

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. Operacin Select con anidamientos. La clausula WHERE comunmente compara los

campos con valores exactos, pero tambin es probable utilizarla comparando sus campos

con otras sentencias Select. Esta forma tambin 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 utilizacin de la clusula 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 informacin nula a travs de la clusula

NULL. Ejemplo, Mostrar los empleados que no tengan asignado salario:

SELECT *

FROM personas

WHERE salario IS NULL

k. Otra clusula 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 operacin de Select tambin es posible utilizar funciones agregadas para: sumar

(SUM), maximizar (MAX), minimizar (MIN) y promediar (AVG). Se pueden utilizar al

nivel de la clusula SELECT o en la clusual HAVING (que veremos posteriormente.

Ejemplo, el total pagado por la compaa, el mximo y el mnimo salario y el promedio

pagado:

SELECT sum(salario), max(salario), min(salario), avg(salario)

FROM personas

m. Agrupacin de tuplas y aplicacin de condiciones para ellas. Aqu se utilizan dos

clusulas nuevas: GROUP BY, la cual agrupa tuplas segn las columnas puestas en la

clusula Select; HAVING, permite hacer operaciones sobre estas agrupaciones. Veamos:

m.1 Mostrar el nmero y el nombre del proyecto en donde trabajen ms 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 clusula WHERE adems de las anteriores instrucciones tambin puede utilizar la

instruccin LIKE, que le sirve para encontrar informacin string no precisa. Veamos el

siguiente ejemplo:

SELECT nombre, numero_proy

FROM proyectos

WHERE nombre LIKE '%lic%'

o. En la clusula Select tambin es posible realizar operaciones aritmticas '+', '-', '*', con

los campos de valor:

SELECT salario*1.18

FROM personas

WHERE salario < 1200000

p. Una clusula ms que podemos utilizar en la operacin Select es la que me permite dale

un orden a las tuplas, ORDER BY, segn el o los criterios indicados a travs 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 informacin de un

Sistema de Transportes intermunicipales:

TERMINALES_TRANSPORTE (cod_terminal, nombre, ciudad, estado)

VIAJES(nmero, transportadora, das)

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 informacin sobre viajes de lneas

areas. Cada VIAJE se identifica con un nmero de viaje, y consta de uno o ms

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 especficos. 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 nmeros de viaje y los das de todos los viajes o trayectos de

viaje que salen del terminal codigo CA001 y llegan al terminal cdigo BO001.

Solucin 1: SELECT num_viaje, num_trayecto,fecha

FROM viajes_realizados

WHERE cod_terminal_sale = 'CA001'

AND cod_terminal_llega = 'BO001';

Solucin 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 nmeros de viaje, cdigos de terminal de salida, horas de salida

programadas, cdigos de terminal de llegada, horas de llegada programadas y das de todos

los viajes o trayectos de viajes que salgan de algn terminal de la ciudad de Santa Marta y

lleguen a algn terminal de la ciudad de Buenaventura.

Solucin 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);

Solucin 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


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 Medelln, en el ao 1999.

Solucin 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 = 'Medelln' AND cod_terminal_llega = cod_terminal)

AND fecha between '01/01/00' and '31/12/99'

AND ta.num_viaje = vr.num_viaje;

Solucin 2:

SELECT distinct cod_tarifa, monto

FROM viajes_realizados vr, tarifas ta, terminales_transporte

WHERE cod_terminal_sale = (SELECT cod_terminal


WHERE ciudad = 'Santa Marta')

AND cod_terminal_llega = (SELECT cod_terminal


WHERE ciudad = 'Medelln')

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 trfico en un da (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 ms 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);

____________________________________________________

Creacin de tablas de una base de datos

Estructura general de la instruccin en SQL para crear una tabla

Se usa la instruccin 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 numricos.

Ejemplos: NUMBER(10) - especifica un entero de 10 dgitos

NUMBER (10,2) - especifica un nmero de 10 dgitos, donde 2 lugares son decimales.

NUMBER - es el "default".

CHAR - se usa para especificar cadenas de caracteres de TAMAO FIJO. Requiere que el dato est entre comillas sencillas.

Ejemplos:

CHAR(3) - especifica un tamao fijo de 3 caracteres

CHAR - es el "default" y su tamao es de un caracter.

VARCHAR2 - se usa para especificar cadenas de caracteres de TAMAO VARIABLE.

Requiere que el dato est entre comillas sencillas. Ejemplos:

VARCHAR2(3) - especifica un tamao fijo de 3 caracteres

VARCHAR2 - es el "default" y su tamao 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 vlido cuando el dato fue guardado en la base de datos con ese formato.

'08-mar-1999' es un dato vlido 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 validacin.

A cada constraint se le identtifica con un nombre, ese nombre debe ser alusivo a su funcin o propsito.

"PRIMARY KEY" - se puede especificar en forma directa cuando se declara el campo al crear la tabla o por medio de un "constraint" explcitamente, en particular cuando es compuesto. Ejemplos

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

etc. );

CREATE TABLE tabla2 ( campo1 char(2), campo2 number(5), campo3 varchar2(20), campo4 number)5), CONSTRAINT keytabla2 PRIMARY KEY(campo1,campo2) );

En este ltimo caso el "primary key" es compuesto.

"FOREIGN KEY" - se puede especificar en forma directa cuando se declara el campo al crear la tabla o por medio de un "constraint" explcitamente. Es importante que los tipos y tamao de los campos aludidos en el foreign key correspondan. Ejemplo 1

CREATE TABLE tabla1 ( campo1 number(7) PRIMARY KEY, campo2 varchar2(5), etc. );

CREATE TABLE tabla2 ( camp1 char(2), camp2 number(5), camp3 varchar2(20),

camp4 number(7) FOREIGN KEY tabla1(campo1), CONSTRAINT keytabla2 PRIMARY KEY(camp1,camp2) ); Ejemplo 2

CREATE TABLE tabla1 ( campo1 number(7) PRIMARY KEY, campo2 varchar2(5), etc. );

CREATE TABLE tabla2 ( camp1 char(2), camp2 number(5),

camp3 varchar2(20), camp4 number)5), CONSTRAINT keytabla2 PRIMARY KEY(camp1,camp2) CONSTRAINT foreignkeytabla1 FOREIGN KEYcamp4 REFERENCES tabla1(campo1) ); Para ms informacin leer el Captulo 16 del libro de texto (Koch & Loney).

Otras instrucciones o mandatos que complementan la creacin de tablas

Las siguientes son instrucciones de SQL

ALTER TABLE

Esta instruccin nos permite aadir campos y modificar definiciones de campos a tablas que estn creadas.

Por ejemplo si tenemos la tabla TABLA1 creada de la forma siguiente:

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

y queremos aadir un campo3 esto se puede hacer con:

ALTER TABLE tabla1 ADD( campo3 number(5) );

Por otro lado si queremos cambiar a que el campo3 permita 7 dgitos en lugar de 5, podemos hacer lo siguiente:

ALTER TABLE tabla1 MODIFY( campo3 number(7) );

DROP TABLE

Esta instruccin nos permite eliminar la estructura de la tabla al igual que los datos que hayan.

Por ejemplo si queremos eliminar la tabla TABLA1 lo hacemos de la forma siguiente:

DROP TABLE tabla1;

TRUNCATE TABLE

Esta instruccin nos permite eliminar los datos de la tabla, pero no elimina la estructura de la tabla. SOLO ELIMINA LOS DATOS

Por ejemplo si queremos eliminar los datos de la tabla TABLA1 lo hacemos de la forma siguiente:

TRUNCATE TABLE tabla1;

INSERT INTO

Esta instruccin nos permite insertar datos en una tabla ya creada.



y queremos insertar un rcord completo lo podemos hacer con:

INSERT INTO tabla1(campo1,campo2) VALUES (1234567,'abcde');

Como en este caso se estn insertando datos en todos los campos se pueden omitir los nombres de los campos, entonces quedara as:

INSERT INTO tabla1 VALUES (1234567,'abcde'); UPDATE

Esta instruccin nos permite cambiar o actualizar datos en una tabla ya creada y ya poblada.


CREATE TABLE tabla1 ( campo1 number(7) PRIMARY KEY,

campo2 varchar2(5), );

y queremos cambiar el campo2 por 'xyzwv' lo podemos hacer con:

UPDATE tabla1 SET campo2 = 'xyzwv' WHERE campo1 = 1234567;

DELETE

Esta instruccin nos permite eliminar rcords de una tabla ya creada y ya poblada.



y queremos eliminar el rcord con "primary key" igual 12345, esto lo podemos hacer con:

DELETE FROM tabla1 WHERE campo1 = 1234567;

COMMIT;

Esta instruccin nos permite confirmar una operacin de "insert", "update" o "delete" en una tabla.

Si se realiza una de esas operaciones y no se hace inmediatamente un COMMIT ese cambio no se hace en la base de datos, claro est se hace un COMMIT implcito si usted sale de la cuenta (con exit o quit) o hace un create table, create view, drop table, drop view, grant, revoke, connect, disconnect, alter, audit o noaudit.

ROLLBACK;

Esta instruccin nos permite eliminar una operacin de "insert", "update" o "delete" en una tabla si no se ha hecho un COMMIT.

Los siguientes son mandatos de SQLPLUS

Los mandatos de SQLPLUS no llevan el "semicolon" al final, esto el smbolo ; .

DESC tabla

Este mandato nos permite tener una descripcin general de la tabla especificada.

Por ejemplo si queremos saber que hay en la tabla1, escribiramos

DESC tabla

Ms adelante veremos otros mandatos de SQLPlus tales como SET, TTITLE, etc.

Operaciones aritmticas en consultas

Operaciones aritmticas

Dentro de un SELECT podemos proyectar resultados que provienen de un clculo relacionado a uno o varios campos de la base de datos

usando operaciones aritmticas.

Las operaciones fundamentales son sumar (+), restar (-), dividir (/ ) y

multiplicar (* ).

Veamos los siguientes ejemplos usando la base de datos de Summit Sporting Goods:

SELECT last_name, salary*12

FROM s_emp:

Esa consulta muestra el apellido y el sueldo anual de los empleados.

SELECT last_name, salary+200 FROM s_emp;

Esa consulta muestra el apellido y el sueldo mensual aumentndole $200.

SELECT last_name, commision_pct*salary/100 FROM s_emp

WHERE title= Sales Representative;

Esa consulta presenta el apellido y lo que gana el empleado en comisin para los empleados que son vendedores.

FUNCIONES

Hay tres clases de funciones numricas. Estas se clasifican como: 1- "single values" 2- "group of values" 3- "lists of values"

"Single values"

Aqu se consideran las operaciones aritmticas tales como sumar (+),

restar (-), dividir (/ ) y multiplicar (* ).

Tambin se consideran otras tales como la de valor absoluto (ABS(valor)), raz cuadrada (SQRT(valor)), etc. En el Captulo 6 - "Playing the Numbers", del texto encontramos muchas ms.

Veamos ejemplos usando algunas funciones tomando en cuenta la base

de datos de Summit Sporting Goods:

SELECT last_name, ROUND(commision_pct*salary/100,2) FROM s_emp

WHERE title= Sales Representative;

Esa consulta presenta el apellido y lo que gana el empleado en comisin para los empleados que son vendedores redondeando a dos lugares

decimales la ganancia de la comisin.

SELECT last_name APELLIDO, NVL(commision_pc,0) COMISION FROM s_emp;

Esa consulta presenta el apellido y el porciento comisin para todos los empleados y cuando no son son vendedores (que tienen valor nulo

(NULL) en commision_pct asigna el valor cero(0).

SELECT last_name APELLIDO, TRUNC(SYSDATE - start_date) DIAS

FROM s_emp;

Esa consulta presenta el apellido y la cantidad de das que los empleados llevan en la empresa. La palabra reservada SYSDATE trae la fecha actual del sistema (la fecha de ese dia).

SELECT last_name APELLIDO, SYSDATE - start_date DIAS

FROM s_emp;

Esa consulta presenta el apellido y la cantidad de das que los empleados llevan en la empresa. Si te fijas hemos omitido la funcin TRUNC, esto traer como consecuencia que el resultado tenga lugares decimales en la cantidad de das dependiendo de las horas que hayan transcurrido del da en que hacemos la consulta. La cantidad de horas se presentarn en fraccin decimal.

Por ejemplo si nos presenta 23.5 das, quiere decir que han pasado 23

das y 12 horas (que equivale al .5 de un da)

SELECT last_name APELLIDO, TRUNC((SYSDATE-start_date)/7) SEMANAS, MOD(TRUNC(SYSDATE-start_date),7) DIAS

FROM s_emp;

Esa consulta presenta el apellido y la cantidad de SEMANAS das que los empleados llevan en la empresa. La funcin MOD da como resultado el residuo que queda al dividir (SYSDATE-start_date) por 7.

"Group of values" - Funciones agrupadas

Estas funciones ofrecen resultados que se aplican a los valores de una columna en total o parte de ellos. Son funciones para obterner el promedio, un contador, valor mximo,etc

Veamos ejemplos usando algunas funciones tomando en cuenta la base

de datos de Summit Sporting Goods:

SELECT AVG(salary)

FROM s_emp;

En este ejemplo se proyecta el promedio de los sueldos de todos los

empleados.

SELECT MAX(salary)

FROM s_emp;

En este ejemplo se proyecta el sueldo mayor de todos los empleados.

SELECT COUNT(*) FROM s_emp;

Aqu se cuenta cuntas filas (rcords) de empleados hay en la tabla

s_emp.

SELECT SUM(salary)

FROM s_emp;

Aqu vemos el total al sumar los sueldos de todos los empleados.

SELECT COUNT(*) "Cantidad de empleados", AVG(salary) "Promedio de sueldos", SUM(salary) "Total de sueldos"

FROM s_emp;

En este ejemplo obtenemos cuntos empleados hay, cual es el sueldo promedio y a cunto asciende el total de los sueldos.

SELECT COUNT(DISTINCT title), COUNT (*)

FROM s_emp;

En este ejemplo tenemos cuntos puestos (titles) distintos hay y cuntos

empleados hay en total.

SELECT title PUESTOS, COUNT(tilte) "CANTIDAD DE EMPLEADOS" FROM s_emp GROUP BY title;

Este ejemplo muestra los puestos (title) contando cuntas personas estn en los diferentes puestos, esto ltimo lo hace la clasula GROUP

BY.

SELECT title PUESTOS, COUNT(tilte) "CANTIDAD DE EMPLEADOS" FROM s_emp

GROUP BY title

HAVING COUNT(title) >2;

Este ejemplo muestar los puestos (title) contando cuntas personas estn en los diferentes puestos, esto ltimo lo hace la clasula GROUP BY. Adems solo se expondrn los puestos donde hay ms de dos personas con ese puesto, esto lo controla la clasula HAVING COUNT(title) >2.

OPERACIONES

La operacin concatenar quiere decir unir dos cadenas de caracteres. Aqu se usan los smbolos || para indicar la concatenacin.

Ejemplos:

SELECT last_name||first_name FROM s_emp;

En este ejemplo se produce una columna pegando el apellido al nombre. (no deja espacio en blanco entre ambos, por ejemplo mostrara el nombre as : RiveraJos )

SELECT last_name||','||first_name FROM s_emp;

En este ejemplo se produce una columna con el apellido y el nombre. (separndolos por coma, por ejemplo mostrara el nombre as : Rivera,Jos )

SELECT last_name||' '||first_name FROM s_emp;

En este ejemplo se produce una columna con el apellido y el nombre. (separndolos con un espacio en blanco, por ejemplo mostrara el nombre as : Rivera Jos )

FUNCIONES

Respecto a funciones para manejar cadenas de caracteres hay una gran variedad. Lea el Captulo 5 -"Getting text information and changing it" del libro de texto. A continuacin mostraremos el uso de algunas solamente.

Veamos ejemplos usando algunas funciones tomando en cuenta la base de datos de Summit Sporting Goods:

SELECT last_name, LENGTH(last_name) FROM s_emp WHERE id =10;

En este ejemplo el resultado es el apellido del empleado (que es Havel) y la cantidad de caracteres que es 5.

SELECT last_name FROM s_emp WHERE LENGTH(last_name) =5;

El ejemplo nos proyecta los apellidos que tienen 5 caracteres.

SELECT LOWER(last_name) FROM s_emp;

Aqu se presentan los apellidos en letras minsculas.

SELECT last_name, first_name FROM s_emp WHERE UPPER(last_name)='PATEL';

En este caso nos proyecta el apellido y nombre de los empleados que tengan apellido 'PATEL', sin importar como el dato est en la base de datos. Esto es puede estar 'PATEL', 'Patel', 'patel' o de cualquier otra forma.

Igual que las funciones LOWER y UPPER hay otra de INITCAP.

La funcin SUBSTR permite obtener una subcadena de caracteres a partir de una cadena de caracteres. Por ejemplo:

SELECT SUBSTR(last_name, 3,2) FROM s_emp WHERE id = 10;

En este ejemplo se toman 2 caracteres del apellido del empleado 10 empezando en el caracter # 3. El empleado 10 tiene apellido 'Havel', por lo tanto el resultado es 've'.

La funcin RPAD justifica a la derecha, veamos el ejemplo siguiente:

SELECT RPAD(last_name || ','|| first_name, 20,'.') NOMBRE, id ID FROM s_emp WHERE id IN (3,6,7);

Aqu se proyecta la concatenacin " last_name || ','|| first_name" usando 20 espacios, si esa concatenacin no ocupa 20 espacios se rellena co puntos. Veamos que obtenemos de salida:

NOMBRE______________ __ID

Nagayama Midori................. 3 Urguhart,Molly...................... 6 Menchu,Roberta................... 7

La funcin INSTR busca la posicin de una subcadena en una cadena. Veamos varios ejemplos:

SELECT INSTR(last_name, 'a') FROM s_emp WHERE id IN (3,6,7);

Aqu se quiere saber en que posicin se encuentra la letra 'a' en esos apellidos. La respuesta es 2, 6 y 0. En 'Nagayama' cuenta la primera 'a', en 'Menchu' la respuesta es 0 por que no hay 'a'.

Operaciones con fechas en consultas

OPERACIONES

Podemos hacer operaciones con fechas tales como las siguientes:

SELECT SYSDATE - start_date from s_emp;

Esta consulta nos trae la cantidad de dias que lleva un empleado en la empresa. Por lo general el resultado incluye decimales pues toma la faccin de horas que han pasado de la fecha del sistema de ese da. (Esa faccin no es equivalente a horas, solo es una faccin del da).

Hay otras operaciones, algunas se realizan a travs de funciones. Por eso las veremos en la prxima seccin. (Ver el Captulo 7 - "Dates: then, now, and the difference" del texto)

FUNCIONES

Hay una gran variedad de funciones con fechas. A continuacin veremos un ejemplo donde le aadimos 6 meses a la fecha en que el empleado comenz:

SELECT last_name "Apellido", first_name "Nombre", start_date "Fecha de inicio", ADD_MONTHS(start_date,6) "Fecha de evaluacin" FROM s_emp;

Veamos otro ejemplo:

SELECT last_name, first_name, MONTHS_BETWEEN(SYSDATE, start_date) MONTHS FROM s_emp;

El Captulo 7 del texto presenta muchos ejemplos de como se pueden combinar funciones y/o operaciones, inclusive combinando con funciones aritmticas tales como ROUNd y TRUNC .

Creacin de Views

DEFINICION

Un view es una tabla "virtual" que podemos disear para que los usuarios puedan hacer consultas. OJO: la creaci2n de un view no crea una tabla fsicamente en la base de datos ni en el sistema.

VENTAJAS

1. Seguridad - restringe al ususario de stos los datos que pueden ver. 2. Conveniencia - una consulta compleja se puede obtener de forma sencilla seleccionando datos del view.

SINTAXIS

CREATE VIEW nombre AS query

WITH CHECK OPTION (constraint);

EJEMPLOS

1- CREATE VIEW emp45 AS SELECT id, last_name, first_name, title FROM s_emp WHERE DEPT_ID = 45;

Podemos ver los datos del view con la siguiente consulta:

SELECT * FROM emp45;

A travs de este view podemos hacer actualizaciones, por ejemplo:

UPDATE emp45 SET dept_id = 10 WHERE id = 10;

Si ahora haces la consulta al view vers que se elimin un rcord.

Para NO alterar la base de datos, te recomiendo que remuevas el cambio con ROLLBACK.

2- CREATE VIEW emp45 AS SELECT id, last_name, first_name, title FROM s_emp WHERE DEPT_ID = 45

WITH CHECK OPTION;

OJO: Antes de crear este view elimina el anterior con DROP VIEW

emp45;

La clasula WITH CHECK OPTION impide hacer actualizaciones a

travs del view.

Trata de actualizar con la siguiente instruccin y vers que te dar un error, no te permitir actualizaciones.

UPDATE emp45 SET dept_id = 10 WHERE id = 10;

INFORMACION sobre los views

Podemos conseguir informacin sobre los views creados en la tablas del diccionario de datos que se llama USER_VIEWS.

Escribe el siguiente mandato de SQLPlus para que veas los campos de la tablas USER_VIEWS:

DESCRIBE user_views

Tambin puedes ver los views con la siguiente consulta:

SELECT * FROM user_views;

EJERCICIOS RESUELTOS SQL.pdf

Documents

Transcript of EJERCICIOS RESUELTOS SQL.pdf