Make your own free website on Tripod.com
 
     

 

 

INTRODUCCION AL SQL

se ha pretendido introducir y explicar los comandos mas utilizados con sus opciones más útiles, dejando los detalles más específicos a los manuales de referencia.

TERMINOLOGIA

SQL (Structured Query languaje o Lenguaje de Consultas Estructurado) es el que permite la comunicación con un Sistema Gestor de Bases de Datos. A continuación se mostraran algunos Sistemas que trabajan con SQL.

                               DMBS                                                   TIPO DE MAQUINA

                               DB2                                                       Main IBM
                               SQL/DS                                                Main IBM
                               RDB/VMS                                            Minis Digital
                               ORACLE                                              Main, Minis, Micros Oracle
                               INGRES                                 Minis, micros
                               SYBASE                                               Minis y LAN
                               INFORMIX                                          Minis y Micros
                               UNIFY                                                  Minis
                               SQL/SERVER                                       Minis y LAN
                               DBASE IV                                            Micros

El SQL es un lenguaje unificado, lo utilizan todo tipo de usuarios, desde el administrador de la base de datos, hasta el usuario final, permite la realización de cualquier consulta de datos.

Las sentencias de SQL se clasifican como parte del DDL o del DML.

DDL Lenguaje de Definición de Datos: Son las sentencias SQL que permiten definir los objetos de la Base de Datos.
DML Lenguaje de manipulación de datos: Son las sentencias SQL que permiten manejar la base de datos.

Siempre que se realiza alguna operación en la base de datos, no se realiza directamente sobre la tabla sino sobre una copia local de esta. De tal manera que si queremos que los resultados de estas operaciones se trasladen a la base de datos, hay que confirmar dicha operación con el comando commit. Si no queremos que estos resultados modifiquen la base de datos (Deshacer) se utiliza el comando rollback, aunque hay que hacer la claridad que algunas sentencias SQL no pueden volverse atrás.

CREACION

El primer paso en cualquier base de datos es la definición de los objetos básicos donde se agrupará la información. Los objetos básicos con que trabaja SQL son las tablas. Una tabla es un conjunto de celdas agrupadas en filas y columnas.

Pasos a tener en cuenta en la creacion de una tabla

  • Nombre de la tabla (longitud máxima 30 caracteres y debe comenzar con carácter alfabético)
  • Nombre de cada columna
  • Tipo y tamaño de los datos almacenados en cada columna
  • Información adicional, restricciones, etc.

 

 

La sintaxis del comando SQL que permite crear una tabla es el siguiente.

CREATE TABLE nombre-tabla                                 *
({nombre-columna tipo de columna [NOT NULL],}
                {CONSTRAINT nombre-de-la-restriccion
                               {UNIQUE ([nombre-columna,])
                               DEFAULT expresión
                               CHECK (condición)
PRIMARY KEY ([nombre-columna,])
FOREIGN KEY (nombre-columna) REFERENCES nombre-tabla (nombre-columna)},});

TIPOS DE COLUMNAS

Tipo de dato

Descripción

Varchar2 (tamaño)

Datos de tipo carácter alfanumérico de longitud variable. Tamaño máximo 2000.

Char (tamaño)

Datos de tipo carácter alfanumérico de longitud fija. Tamaño máximo 255.

Long

Datos de tipo carácter alfanumérico de longitud variable. Tamaño máximo 2 Gigas.

Number (num,dec)

Almacena datos numéricos de num dígitos, de los cuales  dec son decimales.

Date

Almacena fechas .

 

RESTRICCIONES

Las restricciones se utilizan para garantizar que los datos cumplan unas condiciones especificas. SQL maneja las siguientes.

  • NOT NULL                  Obliga a que la columna tenga un valor no nulo. Los valores nulos son diferentes de 0 y el espacio en blanco.
  • UNIQUE                      Evita valores repetidos en una columna, admitiendo valores nulos.
  • CHECK                        Verifica que se cumpla una condición especifica
  • DEFAULT                   Estable un valor por defecto para esta columna, si no se le asigna ninguno.
  • PRIMARY KEY          Establece columna o conjunto de columnas que hacen parte de la clave primaria. Solo puede existir una clave primaria por tabla. Puede ser referenciada como clave foránea por otras tablas.
  • FOREIGN KEY          Establece que el contenido de esta columna será uno de los valores contenidos en una columna de otra tabla o de la misma. Se puede obligar que cuando una fila de la tabla maestra sea borrada, todas las filas de la tabla detalle cuya clave foránea coincida con la clave borrada se borren tambien.

 

Ejemplos.

CREATE TABLE carrera
 (
Cod_Car                number(3),
Nom_Car               varchar2(30) not null,
CONSTRAINT car_pk       PRIMARY KEY (Cod_Car),
);

 

CREATE TABLE estudiante
 (
Cod_Est                                number(8),
Nom_Est               varchar2(35) not null,
Car_est                  number(3),
Pat_est                  number(8),
Otros_datos         varchar(40),
CONSTRAINT est_pk       PRIMARY KEY (Cod_est),
CONSTRAINT est_fk        FOREIGN KEY (Car_Est)  REFERENCES carrera(Cod_car)
ON DELETE CASCADE,
CONSTRAINT est_ch       CHECK (Pat_est > 0)
);

DESCRIBE

Comando SQL muy útil para conocer la estructura de una tabla, las columnas que la forman, su tipo y sus restricciones.

Ejemplo :

DESC     estudiante

Cod_Est                                number(8)
Nom_Est               not null varchar2(35)
Car_est                  number(3)
Pat_est                  number(8)
Otros_datos         varchar(40)

 

MODIFICACION

Una vez creada una tabla puede ocurrir que se necesita una columna adicional o modificar una existente. Esta operación se realiza con el comando ALTER TABLE.

ALTER TABLE nombre-tabla {ADD|MODIFY} ({nombre-columna tipo columna [NOT NULL],});

Ej: Adicionar la columna Fecha_est a la tabla estudiante.
ALTER TABLE estudiante ADD (fecha_est date not null);

Se deben tener en cuenta las siguientes observaciones:

  • No es posible disminuir el tamaño de una columna
  • En las modificaciones, los tipos anterior y nuevos deben ser compatibles o la tabla estar vacía.
  • La opción ADD .... NOT NULL solo es posible sí la tabla esta vacía.
  • La opción MODIFY .... NOT NULL solo es posible si la tabla no tiene ningún NULL en la columna en cuestión.

 

Tambien es posible modificar una tabla añadiendo o eliminando restricciones. El Comando es el siguiente:

ALTER TABLE nombre-tabla {ADD|DROP} CONSTRAINT nombre-restriccion;

ELIMINACION DE TABLAS

EL comando para eliminar físicamente una tabla de la estructura de la base de datos es:

DROP TABLE nombre-tabla [CASCADE CONSTRAINTS]
INSERCION ACTUALIZADO Y BORRADO

Con la estructura de la tabla ya definida se pueden insertar los datos, modificarlos o borrarlos de la tabla. Esto se realiza con el lenguaje DML. Recuerde que todas estas sentencias requieren la orden commit para actualizar las tablas o rollback para deshacer los cambios efectuados.

INSERCION

INSERT  INTO nombre-tabla [({nombre-columna(s),})] VALUES ({expresión,})

Ej: Ingresar la carrera con código 150
INSERT INTO carrera VALUES (150,'Ingenieria de Sistemas');

ACTUALIZACION

UPDATE nombre-tabla  SET {nombre-columna = expresión,} [WHERE condición];

Ej: Modificar el patrimonio del estudiante cuyo código es 98115310
UPDATE estudiante SET pat_est = 15000000 WHERE cod_est = 98115310;

BORRADO

DELETE FROM nombre-tabla [WHERE condición];

Ej: Eliminar el  estudiante cuyo código es 98115310
DELETE FROM estudiante  WHERE cod_est = 98115310;
Si se omite la opcion WHERE eliminara todas las filas de la tabla estudiante.

SELECCIÓN

La selección de los datos en SQL se hace mediante la sentencia select.  Consta de cuatro partes:

  • La sentencia select seguida de la descripccion de lo que se desea ver,  los nombres de las columnas a seleccionar. Obligatorio.
  • La  clausula  from  seguida de la especificacion de las tablas de las que se ha de obtener los datos. Obligatorio.
  • La clausula where seguida por un criterio de selección,  es decir, una condicion. Opcional.
  • La clausula order by seguida por el criterio de ordenacion. Opcional

 

Sintaxis:

SELECT {* | {nombre-columna(s),}
FROM {nombre-tabla(s),}
[WHERE condicion]
[ORDER BY {expresion | nombre-columna [ASC | DESC]}];

Suponga que se tienen definidas las siguientes tablas:

OFICINA

Codofi    Nombre                 Region                   Director

010          POBLADO            SUR                       110
020          LAURELES           OCCIDENTE        115
030          VILLANUEVA     CENTRO               120
040          LORETO               ORIENTE              120

PRODUCTO

Codpro  Nombre                 Preuni                    Stock

101          ESCOBAS             1,500                      20
102          CEPILLOS             2,200                      15
103          JABON FAB        8,000                      40

CLIENTE

Codcli    Nombre                 Cupo                      Codven

80            JUAN                    150,000                  95
81            PEDRO                  120,000                  96
82            ANA                      130,000                  96

VENDEDOR

Codven  Nombre                 Codofi                   Ventas                   Meta                      Salario

95            MARIA                 010                         20,000                    30,000                    5,000
96            LUISA                   020                         15,000                    17,000                    3,000

PEDIDOS

Numero  Codcli                    Codpro                  Cantidad               Valor

1              80                           101                         5                             7,500      
2              81                           101                         6                             9,000
3              81                           102                         5                          11,000
4              82                           103                         4                        32,000

Operadores de Comparacion

Operación

Ejemplo

=

Igualdad

Select * from vendedor where codven=96;

!=, <>

Desigualdad

Select * from vendedor where codofi != 95;

Menor  que

Select * from vendedor where ventas < 18,000;

Mayor que

Select * from  vendedor where ventas > 18,000

<=

Menor o igual

Select * from  vendedor where ventas <= 18,000

>=

Mayor o igual

Select * from  vendedor where ventas > =18,000

IN

Igual a cualquiera de los valores entre parentesis

Select * from vendedor where codofi in (010,020,030)

NOT IN

Distinto a cualquiera de los valores entre parentesis

Select * from vendedor where codofi not in (010,020,030)

BETWEEN

Contenido en el rango

Select * from vendedor where ventas between 10,000 and  20,000

NOT BETWEEN

Fuera del rango

Select * from vendedor where ventas not between 10,000 and  20,000

Operadores aritméticos

Operación

Ejemplo

+

Suma

Select nombre, salario+comision from vendedor;

-

Resta

Select nombre, meta-ventas from Vendedor;

*

Producto

Select nombre, salario * 12 from vendedor;

/

Division

Select nombre, salario/30 from vendedor;

ROUND (l, k)

Calcula el redondeo de l a k decimales

Select round (857.497,1 from dual; Resultado=857.5

SQRT (l)

Calcula la raíz cuadrada de l

Select sqrt (36) from dual; Resultado = 6

TRUNC (l,k)

Calcula l, truncado a k decimales.

Select trunc (857.497,1) from dual; Resultado = 857.4

CONSULTAS SIMPLES

Mostrar nombres de oficinas de la Región Centro.

SELECT nombre
FROM oficina
WHERE región = ' CENTRO' ;

Mostrar Vendedores cuyas ventas superan  la meta.

                SELECT *
                FROM vendedor
                WHERE ventas > meta ;

Mostrar valor del inventario de los productos clasificado por valor

                SELECT codpro, nombre, preuni*stock
                FROM producto;
                ORDER BY preuni*stock

Mostrar los códigos de los empleados que son directores de oficina

                SELECT director
                FROM oficina;

                En pantalla se desplegara el siguiente pantallazo:

                                               110
                                               115
                                               120
                                               120

Como el código 120 tiene las oficinas 030 y 040 a cargo aparecera 2 veces. Esto se soluciona utilizando la clausula DISTINTC.

SELECT  DISTINTC director
                FROM oficina;
                En pantalla se desplegara el siguiente pantallazo:
                                               110
                                               115
                                               120
CONSULTAS MULTITABLA

Mostrar los Clientes con el respectivo nombre del vendedor que lo atiende.

                SELECT codcli, cliente.nombre, vendedor.nombre
                FROM cliente, vendedor
                WHERE cliente.codven = vendedor.codven

Mostrar ventas realizadas indicando nombres del cliente y del producto en vez de los códigos.

                SELECT numero, cliente.nombre, producto.nombre, cantidad, valor
                FROM pedidos, cliente, producto
                WHERE pedidos.codcli = clientes.codcli
                AND pedidos.codpro = producto.codpro;

CONSULTAS SUMATORIAS

Función

Operación

Ejemplo

COUNT {* | (nombre-columna)}

Cuenta numero de filas

Select count (*) from oficina; Cuenta el numero de oficinas.

AVG   (nombre-columna)

Calcula el promedio de todos los valores de la columna

Select avg(meta) from vendedor; Calcula promedio de la meta

MAX  (nombre-columna)

Calcula el valor mayor de la columna

Select max(valor) from pedidos Calcula mayor venta realizada

MIN (nombre-columna)

Calcula el menor valor de la columna

Select min(valor) from pedidos Calcula menor venta realizada

SUM (nombre-columna)

Calcula suma de los valores de la columna

Select sum(valor) from pedidos Calcula suma de todas las ventas realizadas.

 
Mostrar mejor venta realizada
                SELECT  max(ventas)
FROM vendedor;
               
Mostrar numero de vendedores que han superado la meta

SELECT  count(codven)
                FROM vendedor
                WHERE ventas > meta;

Mostrar compras totales del cliente cuyo código es 81

                SELECT count (valor)
                FROM cliente
                WHERE codcli = 81;

CONSULTAS CON ROMPIMIENTO DE CONTROL

Para rompimientos de control se utiliza la cláusula  group by . Si la consulta solicitada requiere que algún conjunto de filas cumpla con una condición, se utiliza el comando having  en vez del where.

Mostrar total compras de cada cliente
                SELECT codcli "Cliente", sum (valor) "Total"
                FROM pedidos
                GROUP BY codcli;

                Producirá el siguiente pantallazo:

                Cliente                                    Total

  •   7,500
  • 20,000
  • 32,000

 

Mostrar numero de vendedores por oficina

                SELECT codofi, count(*)
                FROM oficina
                GROUP BY codofi;

De la tabla de pedidos mostrar el promedio de compras de  aquellos clientes que realizaron en total pedidos superiores a los $ 15,000

                SELECT codcli, avg (valor)
                FROM pedidos
                GROUP BY codcli
                HAVING sum (valor) > 15000;

SUBCONSULTAS

Otra de las facilidades que nos permite SQL es incluir una o mas consultas dentro de una consulta, como se muestra a continuación.

Mostrar vendedores de la  oficina de VILLANUEVA

                SELECT codven, nombre
                FROM vendedor
                WHERE codofi = (SELECT codofi
                                               FROM oficina
                                               WHERE nombre = 'VILLANUEVA');

Mostrar el total de compras de aquellos clientes que han realizado mas de 2 compras

                SELECT codcli, sum (valor)
                FROM pedidos
                GROUP BY codcli
                HAVING count (codcli)       > 2;

Mostrar clientes que son atendidos por vendedores de las oficinas región Centro

                SELECT codcli
                FROM cliente
                WHERE codven IN (SELECT  codven
                                                  FROM vendedor
                                                  WHERE codofi = (SELECT codofi
                                                                                   FROM oficina
                                                                                   WHERE región = 'CENTRO');

                Tambien podría ser:

 

 

                SELECT codcli
                FROM cliente, vendedor, oficina
                WHERE cliente.codven = vendedor.codcli
                AND                      vendedor.codofi = oficina.codofi
                AND                      oficina.region = ' CENTRO ';

 

INDICES

Los índices en SQL almacenan valores y punteros a las filas donde los valores se producen.

Ej;

                SELECT  stock, precio
                FROM      producto
                WHERE  nombre = ' ESCOBAS' ;

La sentencia no dice si existe índice sobre la columna nombre y la consulta se llevara a cabo, exista índice o no. Si no existe, hay que recorrer secuencialmente la tabla producto fila por fila. Si Existe examina el índice y sigue al puntero para encontrar la(s) fila(s).

La búsqueda en índices es muy rápida, pasar del índice a la fila tambien es muy rápido ya que el índice informa al DBMS en que lugar del disco esta localizada la fila. Tiene la desventaja de que ocupa espacio adicional y que existirán tiempos adicionales en las operaciones INSERT y UPDATE.

Sintaxis:

CREATE UNIQUE INDEX nombre-indice
ON nombre-tabla (nombre-columna(s);

CREATE UNIQUE INDEX indice1
                ON producto (nombre)

Para borrar un índice:

DROP INDEX nombre-indice

DROP INDEX indice1

VISTAS

Una vista es una consulta que esta permanentemente almacenada en la base de datos y a la que se le asigna un nombre. Es como una tabla virtual. No puede ser modificada, gasta espacio pero es muy veloz para consultas y se actualiza automáticamente.

Sintaxis:

CREATE VIEW nombre-vista AS
SELECT {* | {nombre-columna(s),}
FROM {nombre-tabla(s),}
[WHERE condición]
[ORDER BY {expresion | nombre-columna [ASC | DESC]}];

CREATE VIEW vista 1 AS
SELECT codpro, nombre, cantidad, valor
FROM pedido, producto
WHERE pedido.codpro = producto.codpro;

Para eliminar una vista se utiliza la sentencia:

DROP VIEW nombre-vista;

SEGURIDAD SQL

Siempre que un usuario crea una tabla, se convierte en el dueño. Si quiere añadir o quitar permisos sobre una tabla determinada a otros usuarios debe definir estos con la cláusula GRANT.

Sintaxis:

GRANT [SELECT | INSERT | DELETE | UPDATE]
ON         nombre-tabla
TO          nombre-usuario
WITH GRAN OPTION    àSi se especifica esta opción, al usuario que se le asigne el permiso puede dar privilegios a otros

Para quitar privilegios

REVOQUE  [SELECT | INSERT | DELETE | UPDATE | ALL PRIVILEGES]
ON         nombre-tabla
FROM   nombre-usuario

INFORMES CON SQL

Como ya hemos visto, con SQL se puede crear la estructura de la base de datos, se pueden actualizar estos, pero la herramienta más poderosa es para crear informes refinados, rápidos y bien formateados. Permitiendo un manejo sencillo sobre los títulos, cabeceras de columnas, subtotales y totales. Es el uso más común de SQL, consultas e impresión de informes.

PRINCIPALES COMANDOS

REMARK: Comentarios. Se puede abreviar a REM
SET HEADSEP <carácter>: Indica cual es el carácter utilizado para partir un titulo en 2 o más líneas.
TTITLE: Titulo superior
BTITLE: Titulo inferior
COLUMN: Columnas del informe
BREAK ON: Donde imprimir subtotales o totales
COMPUTE SUM: Hace que SQL calcule totales
SET LINE SIZE: Caracteres por línea
SET PAGE SIZE: Líneas por pagina
SPOOL: Redirecciona el informe para un archivo
/* Comentarios entre varias líneas */
 
Ejemplo:

REM Programa ejemplo de informe con SQL
REM Descripción : Informe de ventas por articulo.

SET HEADSEP  !

TTITLE ' INFORME DE VENTAS DETALLADO ! POR ARTICULO '
BTTILE                 ' COMPAÑÍA XYZ '

COLUMN nombre    HEADING ' Nombre ! Producto ' format a18 word -wrapped à siga en la siguiente línea
COLUMN cantidad   HEADING ' Unidades '
COLUMN  valor         HEADING ' Valor ! Venta '
BREAK ON nompro   SKIP 2 à salte 2 líneas antes de imprimir subtotales
COMPUTE SUM OF valor ON nompro

SET LINESIZE     80
SET PAGESIZE    50

SPOOL  informe.lst à el informe es guardado en un archivo llamado informe.lst

SELECT  nombre, cantidad, valor
FROM pedido, producto
WHERE pedido.codpro = producto.codpro
ORDER BY nompro;

                               INFORME DE VENTAS DETALLADO
                                             POR ARTICULO

Nombre                                 Unidades                                               Valor
Producto                                                                                              Venta

ESCOBAS                                  5                                       7,500

  •                              9,000

                sub             16,500

CEPILLOS                                  5                                   11,000

                                                                       Sub            11,000

JABON FAB                              4                                32,000

                                                                        Sub           32,000

                                                                        Tot            59,500

     

 

CURSORES

Introcción a cursores PL/SQL


El área de contexto es la memoria designada para procesar una instrucción SQL, la cual incluye

  • el número de registros procesados,
  • un apuntador a la representación de la instrucción SQL analizada y,
  • en el caso de una consulta, el conjunto de registros que regresan de la consulta.

Un cursor es un manejador o apuntador para el área de contexto. Por medio de éste un programa PL/SQL puede controlar el área de contexto.
Los cursores implícitos son creados por Oracle para manejar alguna instrucción SQL y no son declarados por el programador.
Los cursores explícitos son aquellos qwe se declaran, generalmente por medio de una consulta SQL.

Pasos para procesar un cursor explícito

  1. Declaración del cursor
    • La consulta no debe contener la cláusula INTO.
    • Se puede hacer referencia a variables dentro de la cláusula WHERE.
    CURSOR nombre_cursor IS
     instrucción_SELECT 
 
    • También es posible declarar una lista de parámetros:
    CURSOR nombre_cursor(param1 tipo1, ..., paramN tipoN) IS
     instrucción_SELECT 
 
 
  1. Abrir el cursor
    • La sintaxis es:
OPEN nombre_cursor;
    • Se examinan los valores de las variables
    • Se determina el conjunto activo
    • El apuntador para el conjunto activo se establece en el primer registro
  1. Recuperar los resultados en variables PL/SQL
    • Tiene dos formas:
FETCH cursor_name INTO lista_variables;

o

FECTH cursor_name INTO registro_PL/SQL;
  1. Cerrar el cursor
    • La sintaxis es:
CLOSE nombre_cursor;
    • Cuando se cierra el cursor, es ilegal tratar de usarlo
    • Es ilegal tratar de cerrar un cursor que ya está cerrado o no ha sido abierto

 

Atributos de cursores explícitos

Toman los valores TRUE, FALSE o NULL dependiendo de la situación:


Atributo

Antes de abrir

Al abrir

Durante la recuperación

Al finalizar la recuperación

Después de cerrar

%NOTFOUND

ORA-1001

NULL

FALSE

TRUE

ORA-1001

%FOUND

ORA-1001

NULL

TRUE

FALSE

ORA-1001

%ISOPEN

FALSE

TRUE

TRUE

TRUE

FALSE

%ROWCOUNT

ORA-1001

0

*

**

ORA-1001

* Número de registros que ha recuperado hasta el momento
** Número de total de registros

Manejo del cursor por medio de ciclos

Por medio de ciclo LOOP. Debe tenerse cuidado de agregar una condición para salir del ciclo:

    OPEN nombre_cursor;
    LOOP
        FECTH nombre_cursor INTO lista_variables;
        EXIT WHEN nombre_cursor%notfound;
        /* Procesamiento de los registros recuperados */
    END LOOP;
    CLOSE nombre_cursor;
 
        

Por medio de un ciclo WHILE LOOP. La instrucción FECTH aparece dos veces.

    OPEN nombre_cursor;
    FECTH nombre_cursor INTO lista_variables;
    WHILE nombre_cursor%found LOOP
        /* Procesamiento de los registros recuperados */
        FECTH nombre_cursor INTO lista_variables;
    END LOOP;
    CLOSE nombre_cursor;
        
 

Por medio de un ciclo FOR LOOP. Es la forma más corta ya que el cursor es implicitamente se ejecutan las instrucciones OPEN, FECTH y CLOSE.

    FOR variable IN nombre_cursor LOOP
        /* Procesamiento de los registros recuperados */
    END LOOP;