jfernan12__clase_3_-_el_lenguaje_sql

8/3/2019 JFERNAN12__Clase_3_-_El_lenguaje_SQL

1/17

El Lenguaje SQL. Base de Datos II 1

Prof. Jairo Fernndez Mora Universidad Latina - Heredia

El lenguaje SQL

Definiciones y conceptos bsicos

El lenguaje SQL (Structured Query Language) se cre para poder acceder y manipular

los datos en las bases de datos relacionales, en un inicio por IBM. Originalmente sellam SEQUEL (Structured English Query Language, pronunciado como sequel).Oracle introdujo la primera versin comercial de este lenguaje.

Con el tiempo y por la popularidad del lenguaje, el Instituto Nacional de Estndares deAmrica (ANSI, por sus siglas en ingls) defini un SQL estndar y la ISO lo aceptcomo estndar mundial. Es decir, el SQL bsico es similar en cualquier base de datosrelacional del mercado, aunque casi siempre, los proveedores de bases de datos,modifican este lenguaje con funciones y capacidades que lo fortalecen. Ese es el casode Oracle, que ha creado el lenguaje SQL*Plus y PL/SQL.

En las bases de datos relacionales, la informacin es almacenada en tablas (equivalentea las relaciones del modelo relacional). Las tablas contienen columnas (equivalente alos atributos) y cada conjunto de columnas con informacin es llamada fila (como lastuplas).

En este manual, usaremos las siguientes tres tablas para desarrollar los ejemplos.Imaginamos una base de datos de un banco, en el cual hay una tabla para los clientes,una para las cuentas (pueden ser de Dbito o de Ahorro) y una para los prstamos.

SQL> desc clientes_tb

Name Null? Type

------------------------------- -------- ----

CLIENTE NOT NULL NUMBER(5)

NOMBRE NOT NULL VARCHAR2(50)CEDULA NOT NULL VARCHAR2(20)

CIVIL NOT NULL VARCHAR2(1)

TELEFONO VARCHAR2(20)

AGENCIA VARCHAR2(1)

SQL> desc cuentas_tb

Name Null? Type

------------------------------- -------- ----

CUENTA NOT NULL NUMBER(5)


APERTURA NOT NULL DATE

SALDO NOT NULL NUMBER(12,2)

TASA NOT NULL NUMBER(8,4)

TIPO VARCHAR2(1)

SQL> desc prestamos_tb

Name Null? Type

------------------------------- -------- ----

PRESTAMO NOT NULL NUMBER(5)


APERTURA NOT NULL DATE

MONTO NOT NULL NUMBER(12,2)

TASA NOT NULL NUMBER(8,4)

SALDO NOT NULL NUMBER(12,2)

INTERES_AL NOT NULL DATE


2/17



Las tablas contienen los siguientes datos, inicialmente:

SQL> select * from clientes_tb;

CLIENTE NOMBRE CEDULA C TELEFONO A

--------- --------------------------- -------------------- - -------------------- -

1 LUIS ROJAS 2-434-223 S 222-4598 A

2 MARIA LUISA HERRERA 1-324-887 C 231-4390 S3 FERNANDO SALAS 4-121-357 S S

4 ARIANA DEL BOSQUE 1-545-1098 D 221-1489 H

SQL> select * from cuentas_tb;

CUENTA CLIENTE APERTURA SALDO TASA T

--------- --------- ---------- --------- --------- -

1 1 01-12-1998 100000 14 D

2 1 12-01-1999 25000 16 A

3 2 23-09-1996 550000 14 D

4 3 18-05-2001 130000 15 D

5 4 18-05-2001 2000000 18 A

6 4 21-09-2001 150000 14 D

6 rows selected.

SQL> select * from prestamos_tb;

PRESTAMO CLIENTE APERTURA MONTO TASA SALDO INTERES_AL

--------- --------- ---------- --------- --------- --------- ----------

1 1 12-04-1999 10000000 25 9000000 11-10-2001

2 3 14-08-2000 15000000 25.5 14500000 11-10-2001

3 4 30-09-2001 7000000 25 7000000 11-10-2001

Lenguaje DDL.

En SQL tenemos dos lenguajes para procesar la informacin: el DDL y el DML. Ellenguaje de definicin de datos (DDL) permite crear, alterar o eliminar lasestructuras y objetos de la base de datos, tales como tablas, vistas, ndices,triggers, etc. Utiliza fundamentalmente tres comandos:CREATE, ALTERy DROP.

Por ejemplo, las tablas de ejemplo fueron creadas con los siguientes comandosCREATE. Con estos comandos defino la estructura del objeto (en estos casostablas) y sus caractersticas.

CREATE TABLE CLIENTES_TB(

CLIENTE NUMBER(5) NOT NULL,

NOMBRE VARCHAR2(50) NOT NULL,

CEDULA VARCHAR2(20) NOT NULL,

CIVIL VARCHAR2(1) DEFAULT 'S' NOT NULL,

TELEFONO VARCHAR2(20),

AGENCIA VARCHAR2(1))

STORAGE( INITIAL 10K NEXT 5K PCTINCREASE 0)

/


3/17



CREATE TABLE CUENTAS_TB(

CUENTA NUMBER(5) NOT NULL,


APERTURA DATE NOT NULL,

SALDO NUMBER(12,2) NOT NULL,

TASA NUMBER(8,4) NOT NULL,

TIPO VARCHAR2(1))


/

CREATE TABLE PRESTAMOS_TB(

PRESTAMO NUMBER(5) NOT NULL,


APERTURA DATE NOT NULL,

MONTO NUMBER(12,2) NOT NULL,

TASA NUMBER(8,4) NOT NULL,

SALDO NUMBER(12,2) NOT NULL,

INTERES_AL DATE NOT NULL)


/

Asignacin del espacio a los objetos.En Oracle se puede asignar un tamao a cada objeto. Particularmente, a las tablas e ndices seles asigna el espacio por medio de la clusula STORAGE. Ella tiene tres parmetros: INITIALpara decir el espacio que ocupara desde que es creado el objeto. NEXT para indicar el tamaode las extensiones posteriores que se le aaden cuando se llena el espacio disponible yPCTINCREASE para indicar el porcentaje de incremento en cada extensin. Por ejemplo, en latabla clientes_tb se le asign un tamao inicial de 10K. Cuando se llene esos 10K, le asignaruna extensin de 5K cada vez que sea necesario. El tercer parmetro se recomienda siempredejarlo en cero para facilitar la proyeccin de crecimiento de la tabla.

Valores NULOS.Las columnas de una tabla pueden tomar valores nulos, es decir, pueden no contener valoralguno. No debe confundirse un valor nulo con un cero o un espacio en blanco, pues estos sonvalores realmente. El nulo indica que esa columna no contiene informacin alguna. A la hora decrear tablas, le indicamos si cada columna acepta o no valores nulos, mediante la clusula NULLo NOT NULL. En la tabla de clientes_tb, las columnas cliente y nombre, por ejemplo, no aceptanvalores nulos (de hecho, una llave primaria nunca puede aceptar nulos), pero las columnastelfono y agencia s podran aceptar nulos.

TIP: Con los valores nulos hay que tener la consideracin de que si se compara una columnanula con cualquier otra, el resultado ser FALSE. Por ejemplo si preguntamos

Select ... where agencia = H;y la fila tiene la agencia en nulo, la fila ser filtrada (no ser tomada en cuenta). Para evitarerrores se aconseja el uso de la funcin NVL(a,b) donde a es la columna a evaluar y b es el valor

que tomar si a es nulo. De esta forma, siempre compararemos la fila.


4/17



Con el comando ALTER es posible cambiar alguna caracterstica de un objeto.Por ejemplo, podemos variar el tipo de datos o podemos agregar una llave. Acontinuacin se presentan los comandos con los que se definieron las llavesprimarias y forneas de las tablas ejemplo.

/* ********************************************************* */

/* Alteramos las tablas para agregarles las llaves primarias */

/* ********************************************************* */

ALTER TABLE CLIENTES_TB

ADD (CONSTRAINT CLI_PK PRIMARY KEY

(CLIENTE));

ALTER TABLE CUENTAS_TB

ADD (CONSTRAINT CUE_PK PRIMARY KEY

(CUENTA));

ALTER TABLE PRESTAMOS_TB

ADD (CONSTRAINT PRE_PK PRIMARY KEY

(PRESTAMO));

/* ******************************************************** */

/* Alteramos las tablas para agregarles las llaves forneas */

/* ******************************************************** */

ALTER TABLE CUENTAS_TB ADD CONSTRAINT

CUE_CLI_FK FOREIGN KEY

(CLIENTE) REFERENCES CLIENTES_TB

(CLIENTE)

/

ALTER TABLE PRESTAMOS_TB ADD CONSTRAINT

PRE_CLI_FK FOREIGN KEY

(CLIENTE) REFERENCES CLIENTES_TB

(CLIENTE)

/

Con estos comandos DDL tambin se puede incorporar la integridad referencialen las bases de datos. Aunque este tema se ve ms adelante, pondremos unosejemplos de afirmaciones.

/* *************************************************** */

/* Alteramos las tablas para agregarles una afirmacin */

/* de que los valores posibles para la columna CIVIL */

/* slo podrn ser S,C,V,D y U, en mayscula. */

/* *************************************************** */

ALTER TABLE CLIENTES_TB

ADD (CONSTRAINT CK_CIVIL CHECK ( CIVIL IN ('S','C','V','D','U')))

/

ALTER TABLE CUENTAS_TB

ADD (CONSTRAINT CK_TIPO CHECK ( TIPO IN ('A','D')))

/


5/17



Si por alguna razn se desea eliminar completamente un objeto, digamos unatabla, podramos ejecutar el comando DROP. Esto desaparece el objeto y suscontenidos. Para borrar la tabla de prstamos el comando sera:

DROP TABLE prestamos_tb;

Luego de borrado un objeto, no es posible deshacer esta accin. Un drop esdefinitivo.

Lenguaje DML.

Por otra parte, tenemos el lenguaje de manipulacin de datos (DML) que permiteconsultar, insertar, actualizar o borrar los datos. Note la diferencia entre losdos lenguajes, pues mientras uno opera sobre las estructuras, este otro opera

sobre los datos. En el DML tenemos cuatro instrucciones bsicas: SELECT,INSERT, UPDATE y DELETE.

Con el INSERT podemos agregar filas a las tablas. Existen dos formas de haceresto. La primera es indicando los valores explcitamente. Por ejemplo:

INSERT INTO CLIENTES_TB VALUES(1,'LUIS ROJAS','2-434-223','S','222-4598','A');

Dentro del parntesis se indican los valores que deben tomar las distintascolumnas en la fila. Cada valor debe corresponder por posicin a cada columnade la tabla.

Una variante del insert es que puedo indicar explcitamente las columnas quequiero llenar (puede ser que no quisiera llenar todas). Las columnas que nollenar deben aceptar valores nulos o la base de datos me devolver un error.

INSERT INTO CLIENTES_TB(CLIENTE, NOMBRE, CEDULA, CIVIL, TELEFONO, AGENCIA)

VALUES(1,'LUIS ROJAS','2-434-223','S','222-4598','A');

Tambin se puede insertar filas a partir de una consulta. Esto provocara que seinserten todas las filas que genere el comando select.

INSERT INTO CLIENTES_TB SELECT * FROM CLIENTES_OLD_TB;

A continuacin se incluyen los comandos que se utilizaron para insertar los datosdel ejemplo.

INSERT INTO CLIENTES_TB VALUES(1,'LUIS ROJAS', '2-434-223', 'S', '222-4598', 'A');

INSERT INTO CLIENTES_TB VALUES(2,'MARIA LUISA HERRERA','1-324-887','C','231-4390', 'S');

INSERT INTO CLIENTES_TB VALUES(3,'FERNANDO SALAS', '4-121-357', 'S', NULL, 'S');


6/17



INSERT INTO CLIENTES_TB VALUES(4, 'ARIANA DEL BOSQUE','1-545-1098','D','221-1489', 'H');

INSERT INTO CUENTAS_TB VALUES(1, 1, '01-12-1998', 100000, 14, 'D');

INSERT INTO CUENTAS_TB VALUES(2, 1, '12-01-1999', 25000, 16, 'A');



INSERT INTO CUENTAS_TB VALUES(5, 4, '18-05-2001', 2000000, 18, 'A');


INSERT INTO PRESTAMOS_TB VALUES(1, 1, '12-04-1999', 10000000, 25, 9000000, SYSDATE);

INSERT INTO PRESTAMOS_TB VALUES(2, 3, '14-08-2000', 15000000, 25.5, 14500000, SYSDATE);

INSERT INTO PRESTAMOS_TB VALUES(3, 4, '30-09-2001', 7000000, 25, 7000000, SYSDATE);

Ejemplo de respuesta a la hora de insertar una fila:

SQL> INSERT INTO PRESTAMOS_TB VALUES(3, 4, '30-09-2001', 7000000, 25, 7000000, SYSDATE)

2 /

1 row created.

Si se desea alterar el valor de alguna columna, se puede utilizar el comandoUPDATE. Con este comando podemos modificar 1 o ms filas a la vez,dependiendo de las condiciones del where. Es decir, el comando alterar todaslas filas que cumplan con las condiciones establecidas. Si queremos modificar elnombre del cliente 1 (que es LUIS ROJAS) para que ahora se llame LUIS

ALBERTO ROJAS, el comando sera:UPDATE clientes_tbSET nombre = LUIS ALBERTO ROJASWHERE cliente = 1;

Si se desea modificar ms de una columna, simplemente se separan por unacoma todas las columnas a modificar. En el ejemplo siguiente se aprecia estaidea. Hay que aclarar que este UPDATE solamente modificar una fila, pues lacolumna cliente es llave primaria en la tabla de clientes_tb.

UPDATE clientes_tbSET nombre = LUIS ALBERTO ROJAS,

agencia = HWHERE cliente = 1;


7/17



Tambin podra querer hacer una modificacin a varias filas al mismo tiempo.Por ejemplo, podramos querer aumentar todas las tasas de inters de lascuentas de ahorro (tipo igual a A) en un 2%. El comando sera:

UPDATE cuentas_tb

SET tasa = tasa + 2WHERE tipo = A;

Cuando lo que necesito es borrar filas de una tabla, recurro al comandoDELETE. Con este comando, se borran todas las filas que cumplan con lascondiciones establecidas en el where. Un ejemplo para borrar todos los clientesque son atendidos en la agencia de Heredia sera el siguiente:

DELETE FROM clientes_tb WHERE agencia = H;

Las consultas.

Las consultas son la accin ms utilizada y permiten mostrar informacin que hasido almacenada en la base de datos. Para realizar consultas en SQL se utiliza elcomando SELECT. A continuacin un ejemplo para analizar la sintaxis.

SELECT nombre, cedulaFROM clientes_tbWHERE agencia = 'S'

ORDER BY nombre DESC;

El comando indica, luego de la palabra SELECT, las columnas que se quierenconsultar, separndolas por coma.

En el FROM se indica la o las tablas de dnde se tomarn las columnas(podemos hacer mezclas de informacin de varias tablas, llamadas joins).

Con el WHERE se pueden filtrar algunas de las filas y limitar el resultado de laconsulta a ciertas filas en especfico.

La clusula ORDER indica por cul o cules columnas se ordenar el resultado ysi se ordena ascendentemente (default) o descendentemente (DESC). Esta noes la sintaxis completa del SELECT, pero poco a poco la iremos viendo.

Si hacemos una comparacin con el lgebra relacional, tendremos que lascolumnas que van luego del SELECT corresponden a la operacin de proyectar,las tablas que van en el FROM corresponden a las relaciones participantes y a los


8/17



productos cartesianos y las condiciones indicadas en el WHERE son la operacinseleccionar. Si ejecutamos el ejemplo anterior, nos devolvera:

Nombre Cedula

============================= =======

MARIA LUISA HERRERA 1-324-887

FERNANDO SALAS 4-121-357

Los joins en las consultas

Los JOINS son consultas en las que se utiliza ms de una tabla. Para relacionardos tablas stas deben tener alguna columna o conjunto de columnas en comn,estableciendo as una relacin entre ellas. Normalmente estas dos tablas se vana relacionar por una llave fornea, que determinar cul fila de una tablacorresponde a cul fila de la otra.

Por ejemplo, tenemos una tabla de clientes (clientes_tb) que tiene como llaveprimaria la columna cliente. Tenemos tambin una tabla de cuentas(cuentas_tb) que tiene una columna llamada cliente que se usa como llavefornea hacia la tabla de clientes, para indicar de esta forma, a cul clientepertenece cada cuenta. En la clusula WHERE de una consulta tipo JOINdebemos relacionar las tablas. Esto se hace al igualar las columnascorrespondientes. Veamos unos ejemplos:

SQL> SELECT clientes_tb.cliente, nombre, cuenta, saldo

2 FROM clientes_tb, cuentas_tb

3 WHERE clientes_tb.cliente = cuentas_tb.cliente

4 ;

CLIENTE NOMBRE CUENTA SALDO

------- -------------------------------------------------- ---------- ----------

1 LUIS ROJAS 1 100000

1 LUIS ROJAS 2 25000

2 MARIA LUISA HERRERA 3 550000

3 FERNANDO SALAS 4 130000

4 ARIANA DEL BOSQUE 5 2000000

4 ARIANA DEL BOSQUE 6 150000

Los JOINS permiten que mostremos columnas de cualquier tabla incluida en larelacin, como en el ejemplo se incluyen columnas de la tabla clientes_tb y decuentas_tb. Cuando en dos o ms tablas de un mismo JOIN existen columnas

con el mismo nombre, es necesario anteponerles el nombre de la tabla a la quepertenecen, para evitar un problema de ambigedad.

El no relacionar dos tablas que estn en un select tipo JOIN provocar unproducto cartesiano, es decir, la multiplicacin de las filas, provocando datos

inflados y confundiendo al usuario. Esto significa que SQL tomar cada fila de


9/17



una tabla y la relacionar con cada fila de la otra, aunque no exista unaverdadera relacin conceptual entre ellas.

En un JOIN puede haber ms de dos tablas relacionadas y aunque siemprese deben indicar todas las relaciones, lo normal es que ellas se relacionen de dos

en dos.

Inner join y outer join.

El inner join es la forma normal de relacionar tablas. Es como lo explicamosanteriormente. El formato para indicar el inner join puede ser uno de lossiguientes:

SELECT clientes_tb.cliente, nombre, cuenta, saldoFROM clientes_tb, cuentas_tbWHERE clientes_tb.cliente = cuentas_tb.cliente;

SELECT clientes_tb.cliente, nombre, cuenta, saldoFROM clientes_tb INNER JOIN cuentas_tbON clientes_tb.cliente = cuentas_tb.cliente;

En el inner join ocurre que si una fila tomada en cuenta tiene la columna convalor nulo, la fila ser descartada para esta consulta. Esto se debe a que lacondicin expresada indica que las columnas (cliente en el ejemplo anterior)

deben ser iguales y si alguna tiene valor nulo, la condicin es FALSE y la fila nose toma en cuenta realmente.

El outer join es una alternativa para esta situacin. l incluye todas las filas quecumplen la condicin ms otras filas que no encuentran su correspondiente filaen la otra tabla.

Por ejemplo, tengo prstamos registrados para tres de los cuatro clientes. Elcliente MARIA LUISA HERRERA no tiene un prstamo, por lo que si corremos unaconsulta de un join normal (inner) como la siguiente:

SELECT prestamo, nombre, montoFROM clientes_tb, prestamos_tbWHERE clientes_tb.cliente = prestamos_tb.clienteORDER BY 1;


10/17



El resultado sera:

PRESTAMO NOMBRE MONTO

-------- -------------------------------------------------- ----------

1 LUIS ROJAS 10000000

2 FERNANDO SALAS 15000000

3 ARIANA DEL BOSQUE 7000000

Sin embargo, si usamos un OUTER JOIN, como el siguiente:

SELECT prestamo, nombre, montoFROM clientes_tb, prestamos_tbWHERE prestamos_tb.cliente(+) = clientes_tb.clienteORDER BY 1;

El resultado sera:

PRESTAMO NOMBRE MONTO-------- -------------------------------------------------- ----------

1 LUIS ROJAS 10000000

2 FERNANDO SALAS 15000000

3 ARIANA DEL BOSQUE 7000000

MARIA LUISA HERRERA

Note que la cuarta fila corresponde al cliente que no tiene prstamo, por lo quela columna PRESTAMO aparece con valor nulo, al igual que el monto delprstamo. La diferencia es que en este tipo de JOIN s aparecen las filas de latabla de clientes_tb, aunque no encuentren su correspondiente fila enprestamos_tb.

Alias.

Una forma de renombrar temporalmente una tabla es por medio de los alias.Esto sirve para poner nombres ms cmodos a las tablas si stos son muy largoso si hay que escribirlos muchas veces. En el siguiente ejemplo se le puso el aliasde cl a clientes_tb y cu a cuentas_tb.

SELECT cl.cliente, nombre, cuenta, saldo

FROM clientes_tb cl, cuentas_tb cu

WHERE cl.cliente = cu.cliente;

Funciones de una sola fila (one row functions).

En SQL tambin podemos aplicar una serie de funciones y clusulas para ampliarel poder de las consultas. Veamos algunos ejemplos para tener ms claro elpanorama.


11/17



En la siguiente consulta usamos LIKE para comparar valores parciales. Se usael comodn % para indicar cualquier valor. Aqu vamos a preguntar por todaslas filas cuyo nombre tenga la cadena de caracteres LUIS, no importa si es alinicio, en medio o al final. Si quisiramos preguntar por lo que empiecen por

LUIS aunque terminen en cualquier valor, entonces usaramos LIKE LUIS%.

SQL> SELECT cliente, nombre

2 FROM clientes_tb

3 WHERE nombre LIKE '%LUIS%'

4 ORDER BY nombre DESC

5 ;

CLIENTE NOMBRE

--------- --------------------------------------------------

2 MARIA LUISA HERRERA

1 LUIS ALBERTO ROJAS

En la siguiente consulta usaremos BETWEEN para preguntar por un rango devalores. Aqu queremos buscar todas las filas en que su fecha de apertura est

entre el 1 de enero del 2000 y el 30 de octubre del 2001. Los valores extremosson incluidos dentro del rango.

SQL> SELECT prestamo, nombre, apertura, monto

2 FROM clientes_tb, prestamos_tb

3 WHERE apertura BETWEEN '01-01-2000' AND '30-10-2001'

4 AND clientes_tb.cliente = prestamos_tb.cliente;

PRESTAMO NOMBRE APERTURA MONTO

--------- -------------------------- ---------- ---------

2 FERNANDO SALAS 14-08-2000 15000000

3 ARIANA DEL BOSQUE 30-09-2001 7000000

En la siguiente consulta preguntaremos por los valores nulos. Usamos la funcinNULL para buscar las filas en que el telfono es conocido (no es nulo).

SQL> SELECT nombre, telefono

2 FROM clientes_tb

3 WHERE telefono IS NOT NULL

4 ;

NOMBRE TELEFONO

-------------------------------------------- --------------------

LUIS ALBERTO ROJAS 222-4598

MARIA LUISA HERRERA 231-4390

ARIANA DEL BOSQUE 221-1489

Existe una funcin muy especial que se llama DECODE y que permite presentarun valor distinto del contenido en la columna. Por ejemplo, en la tablaclientes_tb, la columna civil puede tomar los valores S, C, D, V, U. Si ala hora de hacer una consulta queremos presentar los nombres de esosvalores, podemos hacer como en el siguiente ejemplo:

SQL> SELECT cliente,

2 SUBSTR(nombre,1,10) nomb,

3 DECODE(civil,'S','SOLTERO',4 'C','CASADO,5 'D','DIVORCIADO,


12/17



6 'V','VIUDO,7 'U','UNION,8 'DESCONOCIDO) e_civil,9 FROM clientes_tb10 WHERE agencia IN (A,H,S);CLIENTE NOMB CIVIL

--------- ---------- --------------

1 LUIS ROJAS SOLTERO

2 MARIA LUIS CASADO3 FERNANDO SA SOLTERO

4 ARIANA DEL DIVORCIADO

En este ejemplo tambin podemos ver el uso de SUBSTR. Esta es una funcinque permite presentar un subconjunto de caracteres de una hilera. Se indica lacolumna, la posicin desde donde inicia la subhilera y la cantidad de caracteresque extraer. En el ejemplo, se presentan los primeros 10 caracteres delnombre del cliente.

Adems, estamos usando una funcin de comparacin llamada IN, que permite

comparar una columna con una lista de valores (es como hacer varios OR). Enel ejemplo (en el WHERE), filtramos todas las filas en que la agencia sea A, H y S(Alajuela, Heredia y San Jos).

Hay ms funciones que se pueden usar. Para eso hay que investigar en elmanual de referencia de SQL.

Funciones de grupo.

Hasta ahora solamente hemos usados columnas tal cual en la parte de select o

en el where. SQL tambin nos da la posibilidad de utilizar funciones de grupopara presentar resultados o para filtrar datos. Las funciones de grupo bsicasson SUM, COUNT, MAX, MIN, AVG y algunas otras ms.

Como su nombre lo dice, las funciones de grupo agrupan resultados. SUMtotaliza el valor de una columna, COUNT cuenta la cantidad de ocurrencias ofilas, MAX retorna el valor mayor de una columna entre todas las filas, MIN elvalor menor, AVG retorna el promedio simple de los valores de la columnaasociada.

SQL> SELECT SUM(SALDO) total,

2 COUNT(cuenta) cantidad4 FROM cuentas_tb

5 WHERE saldo > 100000;

TOTAL CANTIDAD

--------- ---------

2830000 4


13/17



Si queremos usar solamente funciones de grupo en el select en realidad estamosobteniendo totales generales, por lo que la consulta siempre retornar una solafila como resultado. Sin embargo, si queremos utilizar funciones de grupo ycolumnas simples en el mismo query o select, estaremos pretendiendoresultados parciales o cortes por los distintos valores de las columnas simples.

Cuando es as, debemos agregar al final la clusula GROUP BYy las columnassimples. En el ejemplo siguiente, queremos obtener el total de saldo, lacantidad de filas y la fecha ms reciente agrupadas por tipo de cuenta. Como lacolumna tipo tiene dos posibles valores, el query retornar dos filas.

SQL> SELECT tipo, SUM(SALDO) total,

2 COUNT(cuenta) cantidad,

3 MAX(apertura) fecha

4 FROM cuentas_tb

5 WHERE saldo > 1000

6 GROUP BY tipo;

T TOTAL CANTIDAD FECHA

- --------- --------- ----------A 2025000 2 18-05-2001

D 930000 4 21-09-2001

Condiciones para columnas de grupo.

Existe una forma de filtrar datos en un query para columnas de grupo. Para estose usa la clusula HAVING. Este es como un where, pero para columnasagrupadas.Si aplicamos el siguiente query, obtenemos:

SELECT cliente, COUNT(cuenta)FROM cuentas_tbGROUP BY cliente;

CLIENTE COUNT(CUENTA)------- -------------

1 22 14 23 1

Podemos notar que el cliente 1 y el cliente 4 tienen 2 cuentas, pero el cliente 2 yel 3 solamente una. Si necesito consultar solamente aquellos clientes que tienenms de 1 (es decir, que el COUNT(cuenta) sea mayor a 1), entonces puedohacer lo siguiente:


14/17



SELECT cliente, COUNT(cuenta)FROM cuentas_tbHAVING COUNT(cuenta) > 1GROUP BY cliente;

Y obtendr solamente dos filas, correspondientes a cliente 1 y 4.

CLIENTE COUNT(CUENTA)------- -------------

1 24 2

Operaciones entre consultas (conjuntos)

Tambin podemos aplicar operaciones entre consultas. Por ejemplo, podramosquerer saber cules clientes tienen una cuenta y no tienen un prstamo. Esdecir, los clientes que tienen al menos una fila en cuentas_tb, menos losclientes que tienen al menos un prstamo. Esto se hace con la funcin MINUS.

SQL> SELECT cliente

2 FROM cuentas_tb

3 MINUS

4 SELECT cliente5 FROM prestamos_tb;

CLIENTE

---------

2

Esta es una variante ms compleja del query anterior, pues permite presentar elnombre del cliente.

SQL> SELECT nombre

2 FROM clientes_tb cli, cuentas_tb cue

3 WHERE cli.cliente = cue.cliente

4 MINUS

5 SELECT nombre7 FROM clientes_tb cli, prestamos_tb pre6 WHERE cli.cliente = pre.cliente;

NOMBRE

--------------------------------------------------MARIA LUISA HERRERA

Tambin existe la funcin UNION que permite unir los resultados de dos queriesdistintos. Es necesario para aplicar un UNION que ambas consultas tengan lamisma cantidad de columnas con tipos de datos correspondientes.


15/17



Consultas anidadas.

En SQL es posible usar una consulta dentro de otra consulta. Por ejemplo, parasaber cul es el nombre del cliente que tenga la cuenta con mayor saldo detodos necesitamos conocer primero cul es el saldo mayor y luego buscar el

nombre de quien tenga una cuenta con ese monto. Para saber cul es el saldomayor podemos usar:

SELECT MAX(saldo) FROM cuentas_tb;

Entonces, podemos hacer un query que busque el nombre del cliente que tengauna cuenta con saldo igual a ese saldo, pero en lugar de poner un valor fijo parael saldo a buscar (el mayor), ponemos el SELECT anterior entre parntesis.

SELECT nombreFROM clientes_tb, cuentas_tbWHERE clientes_tb.cliente = cuentas_tb.clienteAND saldo = (SELECT MAX(saldo) FROM cuentas_tb);

El resultado sera:

NOMBRE---------------------------------------------------

ARIANA DEL BOSQUE

Disparadores (triggers).

Los triggers son programas especiales que estn asociados a una tabla y a unevento en particular. Cuando ese evento ocurre en esa tabla el trigger seejecuta. Es decir, un trigger no es llamado explcitamente, sino que se ejecutaautomticamente cuando ocurre el evento al que est asociado.

Existen triggers bsicos para 6 eventos:

a.Antes de insertar una fila en la tabla.b. Despus de insertar una fila en la tabla, pero justo antes de grabarlaen la base de datos.

c. Antes de eliminar una fila en una tabla.d. Despus de eliminar una fila, pero justo antes de confirmar esa

eliminacin en la base de datos.e.Antes de modificar una o ms columnas en una fila.


16/17



f. Despus de modificar una o ms columnas en una fila, pero justoantes de grabar esos cambios en la base de datos.

Estos triggers o disparadores, estn almacenados en la base de datos, como unobjeto ms. Es decir, no estn en ningn programa, por lo que siempre se

ejecutan sin importar si el evento fue provocado por un programa de aplicacin,un comando de SQL o cualquier otra herramienta. Esto es importante paragarantizar que la ejecucin del trigger no depende de que el programador sehaya acordado de poner un llamado en un programa o que si alguien entra porSQL, se olviden de ejecutar el trigger.

Normalmente los triggers se usan para validaciones o para ejecutar reglas delnegocio. Por ejemplo, en la tabla de cuentas_tb, podramos poner un trigger deBEFORE INSERT, para garantizar que el saldo de la cuenta sea mayor o igual acero, pero nunca un nmero negativo (si esa es la regla del negocio). El cdigopara hacer ese trigger es el siguiente:

CREATE OR REPLACE TRIGGER cuentas_bi_trBEFORE INSERTON cuentas_tbFOR EACH ROWBEGIN

IF (:new.saldo < 0) THENRAISE_APPLICATION_ERROR(-20000,'SALDO DEBE SER MAYOR O IGUAL A CERO');

END IF;END;

El trigger se llama cuentas_bi_tr. BEFORE INSERT indica el tipo de evento. Puede ser BEFORE o AFTER,

combinado con INSERT, DELETE o UPDATE. ON cuentas_tb indica la tabla en la que debe ocurrir el evento para que se

active este programa. FOR EACH ROW significa que se aplicar a cada fila individualmente. Luego viene un bloque (BEGIN END) que contiene las instrucciones que

queremos aplicar. En este ejemplo estamos haciendo un IF para validarque el saldo no sea menor a cero. Si fuera menor a cero la instruccinRAISE_APPLICATION_ERROR se encarga de enviar un mensaje de error y

frena la ejecucin del evento, con lo que la fila no sera grabada en esteejemplo y logramos mantener la integridad de la informacin.


17/17



Prctica.

1. Cree las tablas, las llaves e inserte los datos iniciales, segn se ejemplificen este documento. Recuerde que para confirmar la insercin de losdatos, hay que dar el comando COMMIT; despus de hacer los comandos

insert. Puede hacer un solo commit despus de todos los inserts.2. Haga una consulta que presente el cliente, el nombre y la cdula de todos

los clientes que viven solteros.3. Haga una consulta que presente el cliente, el nombre y el inters diario

que reciben en sus cuentas. El inters diario se calcula al multiplicar elsaldo por la tasa y dividir entre 100 y entre 365 (los das del ao).

4. Haga una consulta que presente el prstamo, nombre del cliente y eldinero pagado (lo que me prestaron menos lo que me queda por pagar).

5. Haga una consulta que agrupe las cuentas segn el cliente dueo. Debeindicar el cdigo del cliente, la cantidad total de cuentas que tiene y el

monto total acumulado de todas las cuentas de cada cliente.6. Haga una consulta que presente el prstamo que tiene el monto mayor.7. Haga una consulta que presente el total de dinero adeudados al banco

hasta el da de hoy.8. Haga una consulta que presente el nombre y saldo de las cuentas de

Dbito y que NO son atendidos en la agencia de Alajuela.9. Si el cliente 1 viene a hacer un depsito en su cuenta 2, por un monto de

5000, cul es el comando que permite realizar esta operacin?10.Escriba un comando para modificar el saldo del prstamo 2, porque el

cliente vino a abonar 200,000 al saldo.11.Escriba un comando para modificar la tasa de inters de las cuentas de

Dbito, para que bajen en 2 puntos porcentuales.12.Escriba el comando (o los comandos) necesarios para hacer unatransferencia de fondos de la cuenta 3 a la cuenta 2. La transaccin serpor 50,000.

13.Haga un trigger en la tabla de PRESTAMOS_TB, para que por cadaprstamo con monto mayor a 500,000 que sea agregado, incluya unacuenta nueva para ese mismo cliente en la tabla de CUENTAS_TB. Estoes porque no se le entregar el dinero del prstamo al cliente, sino que sele depositar en una cuenta nueva de donde l podr sacar el dinero.

14.Cree el trigger del ejemplo (cuentas_bi_cr)15.Haga un comando para rebajar 200,000 a la cuenta 6. El trigger debe

impedir esta ejecucin.

jfernan12__clase_3_-_el_lenguaje_sql

Documents