Nvidal’s Blog

Just another WordPress.com weblog

Curso Basico de SQL (Capitulo IV)

CAPITULO IV – DML (Data Manipulation Language)

Una vez que tenemos definida la estructura de una tabla se pueden insertan los datos, modificarlos o borrarlos de la tabla.

Esta tarea entra dentro de las operaciones que se realizan con el lenguaje DML. Este lenguaje permite manipular los objetos de la base de datos, insertando, modificando y/o borrando el contenido de las tablas. Hay que recordar que estas sentencias no son ‘auto confirmadas’ y requieren de la sentencia COMMIT para que sus efectos perduren en el tiempo, o de la sentencia ROLLBACK para deshacer los cambios efectuados.

A continuación vamos a estudiar tres de las sentencias DML más comunes.

INSERT (Inserción):

El comando que permite insertar filas en las tablas es el siguiente.

INSERT INTO tabla [({columna,}*)] VALUES ({expresión,}+);

Sólo especificaremos las columnas donde insertar
su orden cuando no insertemos datos en todas ellas o no lo hagamos en el mismo orden en que definimos la tabla.
La asociación columna-valor es posicional.
Los valores deben cumplir con los tipos de datos definidos.
Los valores de tipo caracter y fecha deben ir encerrados entre comillas simples, ('').

A continuación se puede ver la inserción de filas en las tablas de ejemplo.

SQL> insert into dept values (60,'Informatica','Neyba');
SQL> insert into emp values (101,'Rivas','VP',null,'3-FEB-96',2000,null,60);

UPDATE (Actualización):

Otra de las operaciones más comunes es la modificación de la información almacenada en las tablas.

 Para ello se utiliza el comando UPDATE cuya sintaxis se muestra a continuación.

UPDATE tabla SET {columna = expresión,}+ [WHERE condición];
Se especificará en la cláusula SET las columnas que se actualizarán y con qué valores.
La cláusula WHERE indica las filas con las que se va a trabajar.
Si se omite la actualización afectará a todas las filas de la tabla.

SQL> Update emp set ename = ‘R. Rivas’, sal = 40000  where ename = ‘Rivas’;

DELETE (Borrado):

Con insertar y modificar, la otra operación que completa el trío es la de borrado de filas. La sintaxis es la que sigue:

DELETE  tabla [WHERE condición];
 

Borrará todas las filas que cumplan la condición especificada en la cláusula WHERE. Si esta cláusula no se fija, se borrarán todas las filas de la tabla. Aquí cabe decir que aunque con DELETE borremos todas las filas de una tabla, no borramos la definición de la tabla del diccionario y podemos insertar datos posteriormente en la tabla. Esta es una diferencia con la sentencia DROP TABLE, que produce la eliminación tanto del contenido de la tabla como de la definición de la misma.

SQL> delete emp where  ename = ‘Rivas’;

SELECT (Selección):

La recuperación de los datos en el lenguaje SQL se realiza mediante la sentencia SELECT, seleccionar. Esta sentencia permite indicar al SGBD la información que se quiere recuperar. Esta es la sentencia SQL, con diferencia, más habitual. La sentencia SELECT consta de cuatro partes básicas:

  • La cláusula SELECT seguida de la descripción de lo que se desea ver, los nombres de las columnas a seleccionar. Esta parte es obligatoria.
  • La cláusula FROM seguida de la especificación de las tablas de las que se han de obtener los datos. Esta parte es obligatoria.
  • La cláusula WHERE seguida por un criterio de selección, una condición. Esta parte es opcional.
  • La cláusula ORDER BY seguida por el criterio de ordenación. Esta parte es opcional.

Es importante señalar que para seleccionar datos desde una tabla o vista el usuario debe tener permisos sobre esta.

La sintaxis de la sentencia SELECT es la siguiente:

SELECT [distinct | all] {*  | column_mame [c_alias], column_mame [c_alias]…} 

FROM  {tabla [t_alias], tabla [t_alias]…} 

WHERE  condition

HAVING condition

GROUP BY  { column_mame, column_mame …}

ORDER BY  { column_mame [asc|desc], column_mame [asc|desc]…}

Distinct                        Retorna solo una ocurrencias de los valores de una  columna

All                                Retorna todas las ocurrencias de los registros seleccionados.

El valor por defecto de un select es el all.

*                                  Selecciona todas las columnas de una tabla o vista.

column_mame             Nombre de la columna de la tabla o vista.

C_alias                        Sobrenombre  de columna.

Table_name                Nombre de la tabla o vista.

T_alias                        Sobrenombre de la tabla o vista utilizada

Condition                    Condición que se debe cumplir para retornar resultados.

Asc                              Ordena de forma ascendente los registros seleccionados.

Desc                            Ordena de forma descendente los registros seleccionados.

Selección de Columnas:

Las columnas a seleccionar se enumeran sin más en la cláusula SELECT. Si se desea seleccionar todas las columnas de una tabla se puede hacer enumerando a todas las columnas o colocando un asterisco, *, en su lugar.

Cuando se consulta una base de datos, los nombres de las columnas se usan como cabeceras de presentación. Si éste resulta demasiado largo, corto o críptico, puede cambiarse con la misma sentencia SQL de consulta, creando un alias de columna.

SQL> select dname “Departamento”, loc “Está en” from dept;
Departamento   Esta en
————– ————-
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

Cláusula FROM

La cláusula FROM define las tablas de las que se van a seleccionar las columnas.

Se puede añadir al nombre de las tablas el usuario propietario de las mismas de la forma usuario.tabla. De esta manera podemos distinguir entre las tablas de un usuario y otro. Oracle siempre considera como prefijo el nombre del propietario de las tablas, aunque no se lo indiquemos. De esta forma dos o más usuarios pueden tener tablas que se llamen igual sin que surjan conflictos. Si quisiéramos acceder a las filas de la tabla dept del usuario scott, (además de tener privilegios de lectura sobre esa tabla) deberíamos escribir la siguiente sentencia SQL:

SQL> select * from scott.dept;

También se puede asociar un alias a las tablas para abreviar los nombres de las tablas. Un ejemplo se puede ver en la sentencia SQL siguiente:

SQL> select d.dname from dept d;

Cláusula WHERE

Hasta ahora hemos visto como puede utilizarse la sentencia SELECT para recuperar todas las columnas o un subconjunto de ellas de una tabla. Pero este efecto afecta a todas las filas de la tabla, a menos que especifiquemos algo más en la cláusula WHERE. Es aquí donde debemos proponer la condición que han de cumplir todas las filas para salir en el resultado de la consulta. La complejidad del criterio de búsqueda es prácticamente ilimitada, y en él se pueden conjugar operadores de diversos tipos con funciones de columnas, componiendo expresiones más o menos complejas.

Operadores de Comparación

Operador Operación Ejemplo
= Igualdad select * from emp where deptno = 10;
!=, <>, ^= Desigualdad select * from emp where deptno != 10;
< Menor que select * from emp where deptno  < 10;
> Mayor que select * from emp where deptno  > 10;
<= Menor o igual que select * from emp where deptno <= 10;
>= Mayor o igual que select * from emp where deptno >= 10;
in Igual a cualquiera de los miembros entre paréntesis select * from emp where deptno in (10, 30);
Not in Distinto a cualquiera de los miembros entre paréntesis select * from emp where deptno not in (10,30);
between Contenido en el rango select * from emp where empno between 1 and 1000;
Not between Fuera del rango select * from emp where empno not between 1 and 1000;
like ‘_abc%’ Contiene la cadena ‘abc’ a partir del segundo carácter y luego cualquier cadena de caracteres select * from emp where job like ‘MA%’;

Operadores de Aritméticos

Operador Operación Ejemplo
+ Suma select ename, sal+comm from emp where job=’SALESMAN’;
Resta select ename from emp where sysdate-hiredate > 365;
* Producto select ename, sal *12 from emp;
/ División select ename, sal /31 from emp;

Operadores de Cadenas de Caracteres

Operador Operación Ejemplo
|| Concatenación select ename||’ – ‘||job from emp;

Cláusula ORDER BY

Se utiliza para especificar el criterio de ordenación de la respuesta a la consulta. Por defecto la ordenación es ascendente, aunque se puede especificar un orden descendente. La ordenación se puede establecer sobre el contenido de columnas o sobre expresiones con columnas. A continuación se puede ver un ejemplo de uso de la cláusula ORDER BY en la que quiere obtener un listado de los empleados ordenado de manera descendente por su salario y en caso de igualdad de salario, ordenado ascendentemente por su nombre.

SQL> select ename, sal from emp order by sal desc, ename

ENAME            SAL

———- ———

KING            5000

FORD            3000

SCOTT           3000

JONES           2975

BLAKE           2850

CLARK           2450

TURNER          1500

MILLER          1300

ADAMS           1100

JAMES            950

10 rows selected.

Cláusula DISTINCT

Cuando se realiza una consulta sobre una tabla en la que se extrae información de varias columnas, puede ocurrir que, si no incluimos la/s columna/s que forman la clave principal, obtengamos filas repetidas en la respuesta.

Si este comportamiento no nos resulta satisfactorio podemos utilizar la cláusula DISTINCT para eliminar las filas duplicadas obtenidas como respuesta a una consulta.

Podemos ver como funciona en el siguiente ejemplo, en el que preguntamos por los distintos oficios de nuestros empleados.

SQL> select distinct job from emp;

JOB

———

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN 

Funciones de SQL:

Existen en SQL muchas funciones que pueden complementar el manejo de los datos en las consultas. Se utilizan dentro de las expresiones y actúan con los valores de las columnas, variables o constantes.

Se pueden incluir en las cláusulas SELECT, WHERE y ORDER BY.

Pueden anidarse funciones dentro de funciones. Y existe una gran variedad de funciones para cada tipo de datos:

  • Aritméticas,
  • de Cadenas de Caracteres,
  • de Manejo de Fechas,
  • de Conversión,
  • de Grupo.
  • Otras,

Funciones Aritméticas

Función Cometido Ejemplo Resultado
ABS(n) Calcula el valor absoluto de n. select abs(-15) from dual; 15
CEIL(n) Calcula el valor entero inmediatamente superior o igual a n. select ceil(15.7) from dual; 16
Floor(n) Calcula el valor entero inmediatamente inferior o igual a n. select floor(15.7) from dual; 15
MOD(m,n) Calcula el resto resultante de dividir m entre n. select mod(11,4) from dual; 3
POWER(m,n) Calcula la potencia n-esima de m. select power(3,2) from dual; 9
ROUND(m,n) Calcula el redondeo de m a n decimales. Si n<0 el redondeo se efectúa a por la izquierda del punto decimal. select round(123.456,1) from dual; 123.5
SQRT(n) Calcula la raíz cuadrada de n. select sqrt(4) from dual; 2
TRUNC(m,n) Calcula m truncado a n decimales (n puede ser negativo). select trunc(123.456,1) from dual; 123.4
SIGN(n) Calcula el signo de n, devolviendo -1 si n<0, 0 si n=0 y 1 si n>0. select sign(-12) from dual; -1

Funciones de Cadenas de Caracteres

Función Cometido Ejemplo Resultado
CHR(n) Devuelve el carácter cuyo valor codificado es n. select chr(65) from dual; A
ASCII(cad) Devuelve el valor ascii de cad. select ascii('A') from dual; 65
CONCAT(cad1,cad2) Devuelve cad1 concatenada con cad2. Esta función es equivalente al operador ||. select concat(concat(nombre,' es '),oficio) from emp; Cano es Presidente, etc.
LOWER(cad) Devuelve la cadena cad con todas sus letras convertidas a minúsculas. select lower('MinUsCulAs') from dual; minúsculas
UPPER(cad) Devuelve la cadena cad con todas sus letras convertidas a mayúsculas. select upper('maYuSCulAs') from dual; MAYUSCULAS
INITCAP(cad) Devuelve cad con el primer caracter en mayúsculas. select initcap('isabel') from dual; Isabel
LPAD(cad1,n,cad2)RPAD(cad1,n,cad2) Devuelve cad1 con longitud n, y ajustada a la derecha, rellenando por la izquierda con cad2. select lpad('P',5,'*') from dual; ****P
TRIM(cad)LTRIM (cad)RTRIM(cad) Devuelve cad1 sin los espacios (izquierda o derecha) select ltrim (' Espacio ‘) from dual;   Espacio
REPLACE(cad,ant,nue) Devuelve cad en la que cada ocurrencia de la cadena ant ha sido sustituida por la cadena nue. select replace('digo','i','ie') from dual; diego
SUBSTR(cad,m,n) Devuelve la subcadena de cad compuesta por n caracteres a partir de la posicion m. select substr('ABCDEFG',3,2) from dual; CD
INSTR(cad1, cad2, n1, n2) Devuelve la posición donde se encuentra la cad2 dentro de la cad1 a partir de la posición n1 y la ocurrencia n2. select INSTR('AAABBBDDDBBBAAA', 'BBB', 3, 2) FROM DUAL; 10
LENGTH(cad) Devuelve la longitud de cad. select length('cadena') from dual; 6


Funciones de Manejo de Fechas

Función Cometido Ejemplo Resultado
SYSDATE Devuelve la fecha y hora actuales. select sysdate from dual; 14-MAR-97
ADD_MONTHS(d,n) Devuelve la fecha d incrementada en n meses. select add_months(sysdate,4) from dual; 14-JUL-97
LAST_DAY(d) Devuelve la fecha del último día del mes de d. select last_day(sysdate) from dual; 31-MAR-97
MONTHS_BETWEEN(d1, d2) Devuelve la diferencia en meses entre las fechas d1 y d2. select months_between(sysdate,'01-JAN-97') from dual; 2.43409424
NEXT_DAY(d,cad) Devuelve la fecha del primer día de la semana cad después de la fecha d. select next_day(sysdate, 'sunday') from dual; 16-MAR-97

Funciones de Conversión de Tipos

Función Cometido Ejemplo Resultado
TO_NUMBER(cad,fmto) Convierte la cadena cad a un número, opcionalmente de acuerdo con el formato fmto. select to_number('12345') from dual; 124345
TO_CHAR(d, fmto) Convierte la fecha d a una cadena de caracteres, opcionalmente de acuerdo con el formato fmto. select to_char(sysdate) from dual; ’14-MAR-97′
TO_DATE(cad,fmto) Convierte la cadena cad de tipo varchar2 a fecha, opcionalmente de acuerdo con el formato fmto. select to_date('1-JAN-97') from dual; 01-JAN-97

Con las fechas pueden utilizarse varios formatos. Estos formatos permiten modificar la presentación de una fecha. En la siguiente tabla se presentan algunos formatos de fecha y el resultado que generan.

Máscaras de Formato Numéricas

Formato Cometido Ejemplo Resultado
cc ó scc Valor del siglo. select to_char(sysdate,'cc') from dual; 20
y,yyy ó sy,yyy Año con coma, con o sin signo. select to_char(sysdate,'y,yyy') from dual; 1,997
yyyy ó yyy ó yy ó y Año sin signo con cuatro, tres, dos o un dígitos. select to_char(sysdate,'yyyy') from dual; 1997
q Trimestre. select to_char(sysdate,'q') from dual; 1
ww ó w Número de la semana del año o del mes. select to_char(sysdate,'ww') from dual; 11
mm Número del mes. select to_char(sysdate,'mm') from dual; 03
ddd ó dd ó d Número del día del año, del mes o de la semana. select to_char(sysdate,'ddd') from dual; 073
hh ó hh12 ó hh24 La hora en formato 12h. o 24h. select to_char(sysdate,'hh') from dual; 12
mi Los minutos de la hora. select to_char(sysdate,'mi') from dual; 15
ss ó sssss Los segundos dentro del minuto, o desde las 0 horas. select to_char(sysdate,'sssss') from dual; 44159

 

Máscaras de Formato de Caracteres

Formato Cometido Ejemplo Resultado
Syear ó year Año en Inglés select to_char(sysdate,'syear) from dual; nineteen ninety-seven
month o mon Nombre del mes o su abreviatura de tres letras. select to_char(sysdate,'month') from dual; march
day ó dy Nombre del día de la semana o su abreviatura de tres letras. select to_char(sysdate,'day') from dual; friday
a.m. ó p.m. El espacio del día. select to_char(sysdate,'a.m.') from dual; p.m.
b.c. ó a.d. Indicador del año respecto al del nacimiento de Cristo. select to_char(sysdate,'b.c.') from dual; a.d.

Otras Funciones

Función Cometido Ejemplo Resultado
DECODE(var, val1, cod1, val2, cod2, …, defecto) Convierte el valor de var, de acuerdo con la codificación. select JOB,decode(JOB, 'PRESIDENT','P','MANAGER','M','X') from emp; P, D, X, …
GREATEST(exp1, exp2, …) Devuelve el mayor valor de una lista. SELECT GREATEST(1,2,4,33,4,45,6,56) FROM DUAL; 56
LEAST(cad,fmto) Devuelve el menor valor de una lista. SELECT LEAST(1,2,4,33,4,45,6,56) FROM DUAL; 1
NVL(val, exp) Devuelve la expresión exp si val es NULL, y val si en otro caso. select sal+nvl(comm,0) from emp; 450000, 350000, …

Cláusula GROUP BY

SQL nos permite agrupar las filas resultado de una consulta en conjuntos y aplicar funciones sobre esos conjuntos de filas.

La sintaxis es la siguiente:

SELECT {* | {columna,}+}

FROM {tabla,}+

WHERE condición

GROUP BY {columna ,}+

HAVING condición

ORDER BY {expresiónColumna [ASC | DESC],}+;

En la cláusula GROUP BY se colocan las columnas por las que vamos a agrupar. Y en la cláusula HAVING se especifica la condición que han de cumplir los grupos para pasar al resultado.

La evaluación de las diferentes cláusulas en tiempo de ejecución se efectúa en el siguiente orden:

  • WHERE filtra las filas
  • GROUP BY crea una tabla de grupo nueva
  • HAVING filtra los grupos
  • ORDER BY clasifica la salida

Un ejemplo de utilización de la selección de grupos puede ser seleccionar los empleados agrupados por su oficio. Un primer intento de consulta es el siguiente:

SQL> select ename, job from emp group by job;

select ename, job from emp group by job

       *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

Se presenta un error debido a que cuando se utiliza GROUP BY, las columnas implicadas en el SELECT y que no aparezcan en la cláusula GROUP BY deben tener una función de agrupamiento. En otras palabras, la columna nombre debe tener una función de agrupamiento que actue sobre ella (max, min, sum, count, avg). Si no puede ser así, deberá llevar dicha columna a la cláusula GROUP BY.

De nuevo, el ejemplo quedará así:

SQL> select count(ename), job from emp group by job;

COUNT(ENAME) JOB

———— ———

           2 ANALYST

           3 CLERK

           3 MANAGER

           1 PRESIDENT

           1 SALESMAN

Para condicionar  basados en grupo utilizando HAVING:

SQL> select count(ename), job from emp group by job

  2  having count(ename) >= 2 order by 1 desc;

 COUNT(ENAME) JOB

———— ———

           3 CLERK

           3 MANAGER

           2 ANALYST

 Las funciones de agrupamiento que se pueden utilizar son las siguientes.

Funciones de Agrupamiento

Función Cometido Ejemplo
COUNT(col) Cuenta el número de filas agrupadas. select count(ename),job from emp group by job;
AVG(col) Calcula el valor medio de todos los valores de la columna col. select avg(sal),job from emp group by job;
MAX(col) Calcula el valor máximo de todos los valores de la columna col. select max(sal),job from emp group by job;
MIN(col) Calcula el valor mínimo de todos los valores de la columna col. select min(sal),job from emp group by job;
SUM(col) Calcula la suma de los valores de la columna col. select sum(sal),job from emp group by job;
STDDEV(col) Calcula la desviación típica de los valores de la columna col sin tener en cuenta los valores nulos. select stddev(sal),job from emp group by job;
VARIANCE(col) Calcula la varianza de los valores de la columna col sin tener en cuenta los valores nulos. select variance(sal),job from emp group by job;

Hay que tener en cuenta que los valores nulos no participan en el cálculo de las funciones de conjuntos. Estas funciones se pueden utilizar con las cláusulas DISTINCT y ALL. También se pueden utilizar aunque no realicemos agrupación alguna en la consulta, considerando a toda la tabla como un grupo.

SQL> select count(*) from emp;

  COUNT(*)

———-

        10

Expresiones con Sentencias Select

El resultado de cada consulta es un conjunto de filas. Y con conjuntos se pueden realizar tres operaciones típicas: la unión, la intersección y la diferencia.

Unión, UNION

Combina todas las filas del primer conjunto con todas las filas del segundo. Cualquier fila duplicada se reducirá a una sola.

Intersección, INTERSECT

Examinará las filas de los conjuntos de entrada y devolverá aquellas que aparezcan en ambos. Todas las filas duplicadas serán eliminadas antes de la generación del conjunto resultante.

Diferencia, MINUS

Devuelve aquellas filas que están en el primer conjunto pero no en el segundo. Las filas duplicadas del primer conjunto se reducirán a una fila única antes de empezar la comparación con el segundo conjunto.

Reglas para el Manejo de los Operadores de Conjuntos:

  • Pueden ser encadenados en cualquier combinación, siendo evaluados de izquierda a derecha.
  • No existe jerarquía de precedencia en el uso de estos operadores, pero puede ser forzada mediante paréntesis.
  • Pueden ser empleados con conjuntos de diferentes tablas siempre que se apliquen las siguientes reglas:
    • Las columnas son relacionadas en orden, de izquierda a derecha.
    • Los nombres de las columnas son irrelevantes.
  • Los tipos de datos deben coincidir.

Como ejemplo podemos consultar sobre todos los nombres de empleado que trabajan para los departamentos 10 o 30. Esto se consigue restando a todos los nombres de empleados, aquellos que están en el departamento 20.

SQL> select ename from emp

  2  minus

  3  select ename from emp where deptno =20;

ENAME

———-

BLAKE

CLARK

JAMES

KING

MILLER

TURNER

6 rows selected.

Combinaciones

Hasta ahora hemos construido consultas con una única tabla, pero esto no debe ser siempre así.

De hecho, sólo se alcanza la verdadera potencia del SQL cuando combinamos el contenido de más de una tabla.

Supongamos que queremos conseguir una lista con los empleados y los departamentos para los que trabajan. Esta información está repartida en las dos tablas que tenemos, emp y dept. Así, podríamos intentar una consulta que seleccionara el campo nombre de la tabla emp y el nombre del departamento. Y aquí surge el primer problema, ¿cómo distinguimos entre dos columnas que llamándose igual, pertenecen a tablas distintas? Para eso se utiliza como prefijo o el nombre de la tabla (dept.nombre) o un alias de tabla, un nombre que se asocia a cada tabla y se coloca como prefijo a la columna (d.nombre).

Realicemos la consulta …

SQL> select e.ename, d.dname from emp e, dept d;

ENAME      DNAME

———- ————–

KING       ACCOUNTING

JONES      ACCOUNTING

BLAKE      ACCOUNTING

CLARK      ACCOUNTING

SCOTT      ACCOUNTING

TURNER     ACCOUNTING

ADAMS      ACCOUNTING

JAMES      ACCOUNTING

FORD       ACCOUNTING

……

El resultado puede sorprender un poco. Lo que obtenemos es el producto cartesiano de todos los empleados por todos los departamentos. SQL ha cogido cada fila de la tabla emp y le ha asociado todos los dept de la tabla Dept..

Para conseguir lo que queremos tenemos que forzar que se asocie a un empleado con el nombre del departamento para el que trabaja. Y esto se puede hacer si añadimos la condición de que el deptno tenga el mismo valor en la fila de la tabla emp que en la fila escogida de la tabla dept:

SQL> select e.ename, d.dname from emp e, dept d

  2  where e.deptno = d.deptno;

ENAME      DNAME

———- ————–

KING       ACCOUNTING

JONES      RESEARCH

BLAKE      SALES

CLARK      ACCOUNTING

SCOTT      RESEARCH

TURNER     SALES

ADAMS      RESEARCH

JAMES      SALES

FORD       RESEARCH

MILLER     ACCOUNTING

10 rows selected.

De la misma manera se pueden combinar más de dos tablas. Lo importante es emparejar los campos que han de tener valores iguales.

Reglas de Combinación:

  • Pueden combinarse tantas tablas como se desee.
  • El criterio de combinación puede estar formado por más de una pareja de columnas.
  • En la cláusula SELECT pueden citarse columnas de ambas tablas, condicionen o no la combinación.
  • Si hay columnas con el mismo nombre en las distintas tablas, deben identificarse especificando la tabla de procedencia o utilizando un alias de tabla.

Existe un tipo especial de combinación llamada Combinación Externa. Suponga que se crea un nuevo departamento, (insert into dept values (80,’Distribucion’,’Barahona’);) pero todavía no hemos asignado personal al mismo. Si realizamos la consulta anterior, el nuevo departamento no aparecerá en la respuesta. Pero esto se puede evitar si señalamos en la cláusula WHERE la posibilidad de que en la tabla de empleados no existan algunos de los códigos de departamento que si exista en la tabla de departamentos. Esto se hace colocando un (+) de la siguiente manera:

SQL> select e.ename, d.dname

  2  from emp e, dept d

  3  where e.deptno(+)=d.deptno;

ENAME      DNAME

———- ————–

KING       ACCOUNTING

CLARK      ACCOUNTING

MILLER     ACCOUNTING

JONES      RESEARCH

SCOTT      RESEARCH

ADAMS      RESEARCH

FORD       RESEARCH

BLAKE      SALES

JAMES      SALES

TURNER     SALES

           OPERATIONS

11 rows selected.

SubConsultas:

A veces se han de utilizar en una consulta los resultados de otra consulta, llamada subconsulta.

Un ejemplo de esto ocurre cuando queremos conocer los nombres de los empleados cuyo salario está por encima de la media:

SQL> select ename,sal from emp

  2  where sal > (select avg(sal) from emp);

ENAME

———-

KING

JONES

BLAKE

CLARK

SCOTT

FORD

La consulta más interna calcula el salario medio, y la consulta más externa lo utiliza para seleccionar los nombres que ganan más que la media.

El valor de comparación puede ser un valor simple, como en el ejemplo anterior, o un conjunto de valores. Hay que tener en cuenta este detalle ya que el tipo de operador a utilizar varía. En el primer caso se puede utilizar un operador de comparación de carácter aritmético (<, >, etc.). Y en el segundo uno de tipo lógico (IN).

Las subconsultas pueden devolver más de una columna, y se habrán de comparar de manera consecuente:

  • Las columnas de la cláusula WHERE de la consulta principal deben estar agrupadas por paréntesis.
  • Las columnas encerradas entre paréntesis deben coincidir en número y tipo de datos con los datos que devuelve la subconsulta.
  • El nivel de anidamiento de subconsultas es ilimitado.

Se puede utilizar una subconsulta para insertar valores en una tabla en el momento de la creación de la misma con la cláusula AS. Si quisiéramos crear una tabla con los datos de los empleados del departamento 10 lo podríamos hacer de la siguiente manera:

SQL> create table dep10 (ename, job, deptno)

  2  as select ename, job, deptno from emp

  3  where deptno = 10;

Table created.

Se puede utilizar su consulta a nivel de las columnas del SELECT:

SQL> select ename, job,

  2   (select dname from dept where deptno = e.deptno) depto

  3  from emp e;

ENAME      JOB       DEPTO

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

KING       PRESIDENT ACCOUNTING

JONES      MANAGER   RESEARCH

BLAKE      MANAGER   SALES

CLARK      MANAGER   ACCOUNTING

SCOTT      ANALYST   RESEARCH

TURNER     SALESMAN  SALES

ADAMS      CLERK     RESEARCH

JAMES      CLERK     SALES

FORD       ANALYST   RESEARCH

MILLER     CLERK     ACCOUNTING

No es necesario especificar tipos ni tamaños de las columnas, ya que vienen determinados por los tipos y tamaños de las columnas recuperadas en la subconsulta.

Clausula EXISTS:

Otra manera de emplear su consulta es la clausula EXISTS que permite evaluar el resultado de otro query o subconsulta.

SQL> select deptno, dname from dept d

  2  where exists (select 1 from emp

  3                where deptno = d.deptno

  4                and   sal   >= 1000);

   DEPTNO DNAME

——— ————–

       10 ACCOUNTING

       20 RESEARCH

       30 SALES

Tambien se puede negar la condicion o la no existencia:

SQL> select deptno, dname from dept d

  2  where NOT exists (select 1 from emp

  3                    where deptno = d.deptno

  4                    and   sal   >= 1000);

   DEPTNO DNAME

——— ————–

       40 OPERATIONS

Creación de Vistas:

Create View:

Las vistas son objetos utilizados solo para la consulta de datos. Su uso se fundamenta en agrupar informaciones de diferentes tablas y facilitar las consultas.

Sintaxis:

            CREATE VIEW view_name(column_name, column_name, …) AS Select;

            view_name                  Nombre de la vista a crear.

            column_name              Nombre de columnas.

            Select                           Select de donde saldran los datos

SQL> create view empleados_v (codigo, nombre, salario, cargo)

  2  as select empno, ename, sal, job from emp;

View created.

SQL> desc empleados_v

 Name                            Null?    Type

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

 CODIGO                          NOT NULL NUMBER(4)

 NOMBRE                                   VARCHAR2(10)

 SALARIO                                  NUMBER(7,2)

 CARGO                                    VARCHAR2(9)

Renombrando Objetos:

RENAME

El comando RENAME se utiliza para modificar el nombre de uno objeto, tabla, vista o sinónimo.

La sintaxis es la siguiente

               RENAME {tabla | vista | sinónimo} to nuevoNombre ;

Esta sentencia cambiará el nombre antiguo por el nuevo, y a partir de este momento cualquier acceso al objeto por el nombre antiguo será respondido con un mensaje de error. Conviene resaltar la diferencia entre el comando SYNONYM y el comando RENAME. Mientras que el primero mantiene el nombre original para acceder al objeto, el segundo elimina ese primer nombre sustituyéndolo por el nuevo.

SQL> Rename empleados to empleado;

EJERCICIO III

1)      Insertar 5 registros en cada una de las siguientes tablas:

  • Materias
  • Estudiantes
  • Notas

2)      Borrar los registros de las tablas Materia y Estudiantes que no tengan registros relacionados en la tabla de Notas.

3)      Hacer consulta relacionando las tablas Materia, Estudiantes y Notas y utilizar la cláusula ORDER BY y luego convertirla en una vista con los siguientes campos:

Columna       Tabla

————– ———–

MATRICULA      NOTAS

NOMBRE         ESTUDIANTES

MATERIA       NOTAS

NOM_MATERIA    MATERIAS

CALIFICACION   NOTAS

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: