Nvidal’s Blog

Just another WordPress.com weblog

Curso Basico de SQL (Capitulo II)

CAPITULO II – SENTENCIAS DML

 Las sentencias SQL pertenecen a dos categorías principales: Lenguaje de Definición de Datos, DDL y Lenguaje de Manipulación de Datos, DML. Estos dos lenguajes no son lenguajes en sí mismos, sino que es una forma de clasificar las sentencias de lenguaje SQL en función de su cometido. La diferencia principal reside en que el DDL define objetos en la base de datos y sus efectos se pueden ver en el diccionario de datos; mientras que el DML es el que permite consultar, insertar, modificar y eliminar la información almacenada en los objetos de la base de datos.

Cuando se ejecutan las sentencias DDL de SQL, el SGBD o RDBMS confirma la transacción actual antes y después de cada una de las sentencias DDL. En cambio, las sentencias DML no llevan implícito el commit y se pueden deshacer. Existe pues un problema al mezclar sentencias DML con DDL, ya que estas últimas pueden confirmar las primeras de manera involuntaria e implícita, lo que en ocasiones puede ser un problema.

A continuación se presenta una tabla con las sentencias SQL más comunes, clasificadas según el lenguaje al que pertenecen.

Sentencias DDL Objetivo
Create Crear objetos de Base de Datos.
Alter Cambiar objetos de Base de Datos.
Drop Eliminar objetos de Base de Datos.
Truncate Eliminar todas las filas de una tabla.
Grant Conceder privilegios o papeles, roles, a un usuario o a otro rol.
Revoke Retirar los privilegios de un usuario o rol de la base de datos.
Sentencias DML Objetivo
Insert Añadir filas de datos a una tabla.
Delete Eliminar filas de datos de una tabla.
Update Modificar los datos de una tabla.
Select Recuperar datos de una tabla.
Commit Confirmar como permanentes las modificaciones realizadas.
Rollback Deshacer todas las modificaciones realizadas desde la última confirmación.
Savepoint Puntos de savamento de transacciones.

Sentencias DDL:

La primera fase de cualquier base de datos comienza siempre con sentencias DDL, ya que antes de poder almacenar información debemos definir los objetos básicos donde 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 donde se almacenan elementos de información.

Antes de llevar a cabo la creación de una tabla conviene planificar:

  • nombre de la tabla,
  • nombre de cada columna,
  • tipo y tamaño de los datos almacenados en cada columna,
  • información adicional, restricciones, índices, llaves, etc.

Hay que tener en cuenta también ciertas restricciones en la formación de los nombres de las tablas: longitud máxima de 30 caracteres, no puede haber nombres de tabla duplicados, deben comenzar con un carácter alfabético, permitir caracteres alfanuméricos y el guión bajo ‘_’, y Oracle no distingue entre mayúsculas y minúsculas.

Creación de Tablas:
Create Table: sentencia utilizada para la creación de tablas:

Sintaxis:

            CREATE  TABLE  table_name

            ( {culumn_name datatype [c_constraints]}, culumn_name datatype [c_constraints]},…,

                 [T_constraints]);                                                                                                                                                            

Table_name                Nombre de la tabla.

Column_name             Nombre de la columna.

Data type                     Tipo de Dato.

C_constraints              Restricciones de la Columna.

T_constraints               Restricciones de la Tabla.

 Existen varios tipos de datos en SQL. De esta manera, cada columna puede albergar una información de naturaleza distinta. Los tipos de datos más comunes y sus características se resumen en la siguiente tabla.

CHAR(n).

Datos de tipo carácter, de n caracteres de longitud. La cantidad máxima permitida es de 255.
DATE Rango válido de fecha desde el 1 de enero 4712 AC

Hasta el 31 de diciembre del 4712 DC.

LONG

Dato de tipo carácter con tamaño variable hasta 65535 caracteres, solo se puede definir una columna LONG por tabla. Estas no se pueden utilizar en subconsultas, funciones, expresiones, cláusulas WHERE o índices.
RAW(n) Datos binarios con una longitud de n bytes. El tamaño máximo es de 255 bytes.
LONG RAW Datos binarios puros. Por lo demás igual a LONG

NUMBER

Numero de 40 dígitos.
NUMBER(n) Numero de n dígitos.
NUMBER(n,d) Numero de n dígitos con d cifras a la derecha del punto decimal.
VARCHAR2(n) Cadena de caracteres de longitud variable hasta n caracteres.

Ejemplos Creación de Tablas:

1)      Tabla simple con restricciones a nivel de tabla.

CREATE TABLE materias

    (MATERIA       NUMBER(2),

     NOM_MATERIA   VARCHAR2(35),

     CREDITOS      NUMBER(2),

     CONSTRAINT pk_materias PRIMARY KEY (materia)

    );

Al definir una columna como PK (Primary Key o llave primaria) automáticamente adquiere la restricción  NOT NULL y  crea un ÍNDICE por esa columna. El nombre del PK (pk_materia) definido al crear la tabla es el que usara oracle para almacenarlo en la base de datos. Las llaves primarias pueden estar compuestas por 1 o mas columnas de la tabla y solo se permite uno por tabla.

Para ver su estructura:

DESC materias

 Name                            Null?    Type

 ——————————- ——– —-

 MATERIA                         NOT NULL NUMBER(2)

 NOM_MATERIA                              VARCHAR2(35)

 CREDITOS                                 NUMBER(2)

2)      Tabla con restricciones tanto de columnas  como de la tabla.

CREATE TABLE estudiantes

   (matricula         NUMBER(5),

    nombre            VARCHAR2(35) NOT NULL,

    fecha_nacimiento  DATE,

    sexo              VARCHAR2(1) DEFAULT ‘M’

                      CONSTRAINT ck_sexo_estudiantes

    check (sexo in (‘M’,’F’)),

    CONSTRAINT pk_estudiante PRIMARY KEY (matricula) );

Si se desea que una columna tenga algún valor siempre, se define como NOT NULL. También se puede restringir a valores específicos utilizando CHECK. Todas estas restricciones son validadas por la Base de Datos.

Para ver su estructura:

DESC estudiantes

 Name                            Null?    Type

 ——————————- ——– —-

 MATRICULA                       NOT NULL NUMBER(5)

 NOMBRE                          NOT NULL VARCHAR2(35)

 FECHA_NACIMIENTO                         DATE

 SEXO                                     VARCHAR2(1)

3)      Relaciones entre Tablas.

CREATE TABLE notas

  (MATRICULA           NUMBER(5),

   MATERIA             NUMBER(2),

   CALIFICACION        NUMBER(3) NOT NULL,

CONSTRAINT pk_notas  PRIMARY KEY (matricula, materia),

CONSTRAINT fk1_notas FOREIGN KEY (matricula)

                     REFERENCES estudiantes(matricula),

CONSTRAINT fk2_notas FOREIGN KEY (materia)

   REFERENCES materias (materia)  );

Esta es una forma un poco más compleja, ya que esta tabla depende o más bien, esta referenciada a otras tablas. El PK esta compuesto por dos columnas  (matricula y materia). También tiene varias llaves secundarias o foráneas (FOREIGN KEY o FK), que a su vez son llaves primarias en la tabla referenciada.

Para ver su estructura:

DESC notas

 Name                            Null?    Type

 ——————————- ——– —-

 MATRICULA                       NOT NULL NUMBER(5)

 MATERIA                         NOT NULL NUMBER(2)

 CALIFICACION                    NOT NULL NUMBER(3)

Creación de Secuencias:

Las secuencias son objetos de Oracle que maneja una numeración automática.

CREATE  SEQUENCE  sequence_name

[INCREMENT BY n ]

[START WITH n ]

Los valores se obtienen mediante estas variables y solo en sentencias de SQL:

sequence_name.CURRVAL  (valor actual)

sequence_name.NEXTVAL (siguiente valor)

Ejemplo:

SQL> CREATE SEQUENCE empleado_sec;

SQL> Select empleado_sec.nextval from dual;

Modificación De Tablas:

Después de crear una tabla, a veces nos encontramos con que se necesita añadir una columna adicional o modificar la definición de una columna existente. Esta operación se puede realizar con el comando ALTER TABLE.

Hay que tener en cuenta varios puntos:

  • No es posible disminuir el tamaño de una columna al menos que la tabla esta vacía.
  • En las modificaciones, los tipos anterior y nuevo deben ser compatibles, o la tabla debe estar vacía.
  • La opción ADD … NOT NULL sólo será posible si la tabla está vacía o si se especifica un valor por defecto.
  • La opción MODIFY … NOT NULL sólo podrá realizarse cuando la tabla no contenga ninguna fila con valor nulo en la columna en cuestión.

ALTER  TABLE: Este comando modifica la estructura de una tabla.

Sintaxis:

ALTER TAB LE  table_name[ADD | MODIFY | DROP COLUMN] column_name  datatype c_constraint;

            Table_name                Nombre de la tabla a modificar

            Column_name             Columna a modificar

            C_constraint                Restricciones de la columna.

Ejemplos:

1)      Adiciona la columna profesor a la  tabla materias.

            ALTER TABLE materias ADD profesor VARCHAR2(5);

      SQL> DESC materias

 Name                            Null?    Type

 ——————————- ——– —-

 MATERIA                         NOT NULL NUMBER(2)

 NOM_MATERIA                              VARCHAR2(35)

 CREDITOS                                 NUMBER(2)

 PROFESOR                                 VARCHAR2(5)

2)      Este ejemplo modifica la columna profesor a la tabla materias.

            ALTER TABLE materias MODIFY profesor VARCHAR2(25) NOT NULL;

      SQL> DESC materias

 Name                            Null?    Type

 ——————————- ——– —-

 MATERIA                         NOT NULL NUMBER(2)

 NOM_MATERIA                              VARCHAR2(35)

 CREDITOS                                 NUMBER(2)

 PROFESOR                                 VARCHAR2(25)

3)      Adiciona la columna status a la  tabla materias.

ALTER TABLE materias ADD status VARCHAR2(1)

  CONSTRAINT ck_status_materias

  CHECK(status IS NOT NULL AND status IN (‘A’,’D’));

SQL> DESC materias

 Name                            Null?    Type

 ——————————- ——– —-

 MATERIA                         NOT NULL NUMBER(2)

 NOM_MATERIA                              VARCHAR2(35)

 CREDITOS                                 NUMBER(2)

 PROFESOR                        NOT NULL VARCHAR2(25)

 STATUS                                   VARCHAR2(1)

4)      Adiciona y borra columna aula de la tabla materias.

ALTER TABLE materias ADD aula NUMBER;

ALTER TABLE materias DROP COLUMN aula;

Alterando Restricciones:

Otra forma más compleja de modificar los componentes de una tabla es la siguiente.

Sintaxis:

ALTER TAB LE  table_name {[ADD | DROP]} CONSTRAINT constraint_name 

     {[PRIMARY KEY  (column_name, column_name,…) ] |

       [FOREIGN KEY   (column_name, column_name,…)

                 REFERENCES table_name_refs(r_column_name, r_column_name,…)]|

       [CHECK (condition)]}

 

            Table_name                Nombre de la tabla a modificar

            Column_name             Nombre columna a de la tabla.

            Constraint_name         Nombre de la Restricciones.

Table_name_refs         Nombre de la tabla referenciada.

R_column_name         Nombre columna a de la tabla Referenciada

Condition                    Condición que valida la restricción

Ejemplo:

  1. Elimina la restricción ck_status_materia  en la tabla MATERIAS; el pk_notas y fk1_notas de la tabla de NOTAS.

       ALTER TABLE materias DROP  CONSTRAINT ck_status_materias;

       ALTER TABLE notas      DROP  CONSTRAINT pk_notas;

      ALTER TABLE notas      DROP  CONSTRAINT fk1_notas;

 

  1. Adiciona la restricción  ck_status_materias  en la tabla MATERIAS.

             ALTER TABLE materias ADD  CONSTRAINT ck_status_materias

                          CHECK(status IS NOT NULL AND status IN (‘A’,’D’));

  1. Adiciona  el Primary Key de la tabla de NOTAS.

       ALTER TABLE notas  ADD  CONSTRAINT pk_notas

                          PRIMARY KEY (matricula, materia);

  

  1. Adiciona  el Foreign Key fk1_notas de la tabla de NOTAS.

              ALTER TABLE notas  ADD  CONSTRAINT fk1_notas

                                    FOREIGN KEY (matricula)  REFERENCES estudiantes(matricula);

 

EJERCICIO I

 1)       Crear una tabla llamada DEPTOS con las siguientes características:

 Id_departamento numérico de 5 dígitos, PK

Nom_departamento carácter de 5 posiciones.

2)      Crear una tabla llamada EMPLEADOS con las siguientes características:

 Id_empleado  numérico de 5 dígitos, PK

Nom_empleado   carácter de 35  posiciones, obligatorio.

Cargo carácter de 25 posiciones.

Id_departamento   numérico de 5 dígitos, FK referenciado a la tabla DEPTOS.

Fecha_nacimiento tipo fecha, obligatorio

Sexo  carácter de 1 posición, restringido a solo dos valores (F,M), obligatorio.

Sueldo  numérico de 12 dígitos y 2 decimales, obligatorio.

Status_empleado carácter de 1 posición, restringido a (A,D), obligatorio y valor A por defecto..

3)      Crear tabla maestro de transacciones llamada TRANSACCIONES:

 Id_transaccion numérico de 5 dígitos, PK

Nom_transaccion character de 35 posiciones, obligatorio.

Origen  carácter de 1 posición restringido a (D,C), obligatorio.

 4)      Crear una tabla de transacciones por empleados llamada TRANS_X_EMPLEADOS:

 Sec_Transaccion numérico de 10 dígitos, PK

Id_empleado  numérico de 5 dígitos, PK, FK referenciada a EMPLEADOS.

Id_transaccion numérico de 5, PK, FK referenciada a TRANSACCIONES.

Fecha_transaccion  tipo fecha, obligatorio.

Monto_transaccion numérico de 12 dígitos y 2 decimales, obligatorio.

5)      Modificar la columna Nom_departamento de la tabla DEPTOS aumentando el tamaño a 35 caracteres; obligatorio.

6)      Agregar la columna Comentarios de tipo variable (LONG) a la tabla EMPLEADOS

septiembre 28, 2009 - Posted by | Oracle

Aún no hay comentarios.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: