Ejercicios : Filtro de Filas

1) Cree una consulta para mostrar el apellido, el cargo y el salario de los empleados que ganan más de 2000.
http://liang.armstrong.edu:5560/isqlplus

2) Cree una consulta para mostrar el apellido del empleado, el cargo y el número de departamento para el número de empleado 7788.

3) Mostrar el apellido, el cargo y el salario para todos los empleados cuyos salarios no están comprendidos entre 1000 y 3000.

4) Muestre el apellido del empleado, el cargo, el director y la fecha de inicio de los empleados contratados entre el 20 de febrero de 1981 y el 1 de mayo de 1982. Ordene la consulta en orden ascendente por fecha de inicio.

5) Muestre el apellido, el cargo y el número de departamento de todos los empleados de los departamentos 10 y 20 en orden alfabético por apellido.

6) Muestre el apellido, el cargo y el salario de los empleados que ganan entre 1000 y 3000, y están en el departamento 10 ó 20. Etiquete las columnas con Empleado y Salario Mensual, respectivamente.

7) Muestre el apellido, el cargo y la fecha de contratación de todos los empleados contratados en 1980.

8) Muestre el apellido, el salario y el cargo de todos los empleados que no tienen director.

9) Muestre el apellido, el cargo, el salario y la comisión para todos los empleados que ganan comisiones. Ordene los datos en orden descendente de salarios y comisiones.

10) Muestre el apellido de todos los empleados que tengan la c como segunda letra.

11) Muestre el apellido de todos los empleados que tengan una i y una o en el apellido.

12) Muestre el apellido de todos los empleados que tengan un apóstrofe (') en el apellido.

13) Muestre el apellido, el cargo, el director y el salario de todos los empleados cuyos cargos(job) sean ventas(SALES) o cajeros(CLERK) y cuyos salario no sean iguales a 2500, 3000 ni 3500.

Ejercicios : Select Básicos

1) La siguiente sentencia SELECT se ejecuta correctamente?

SELECT ename, job, sal AS Salario
FROM emp;

verdadero/falso

http://liang.armstrong.edu:5560/isqlplus

2) La siguiente sentencia SELECT se ejecuta correctamente?

SELECT *
FROM salgrade;

Verdadero/Falso

3) Hay cuatro errores de codificación en esta sentencia. ¿Cúales son?

SELECT empno, ename salary x 12
SALARIO ANUAL
FROM emp;

4) Muestre la estructura de la tabla DEPT. Seleccione todos los datos de la tabla.

5) Muestre la estructura de la tabla EMP. Cree una consulta para mostrar el apellido, el cargo, la fecha de contratación, el manager y el número de empleado para cada empleado, con el número de empleado en primer lugar.
Proporcione un alias para la columna.

6) Cree una consulta para mostrar los cargos únicos de la tabla EMP.

7) Mostrar el apellido concatenado con el cargo, separados por una coma y un espacio y llame a la columna Empleado y Cargo

Cadenas de Caracteres Literales

Un literal es un carácter, un número o una fecha que está incluido en la lista SELECT y que no es un nombre de columna o un alias de columna. Un literal está impreso para cada fila devuelta. Las cadenas literales de texto de formato libre se pueden incluir en el resultado de la consulta y son tratados de la misma manera que una columna en la lista SELECT.
Mientras que los literales de caracteres y fecha se deben escribir entre comillas simples (' '), los literales numéricos no.

Ejemplo:

• Mostrar los cargos y los apellidos de todos los empleados. La columna tiene la cabecera Detalle de Empleado.

SELECT ename ' es un ' job AS "Detalle de Empleado"
FROM emp;

http://liang.armstrong.edu:5560/isqlplus/

Debe recordar que:
Los espacios entre las comillas simples en la sentencia SELECT, mejoran la legibilidad del resultado.

Ejemplo:

• Mostrar el apellido y el salario de cada empleado, concatenados con un literal para dotar de más significado al resultado.
  

SELECT ename ': 1 Mes de salario = ' sal Mensualmente
FROM emp;

Operador de Concatenación

Enlaza columnas a otras columnas, expresiones aritméticas o valores constantes para crear una expresión de caracteres mediante el operador de concatenación (alt 124). Las columnas a cada lado del operador se combinan para hacer una única columna de resultados.

Ejemplo:

• Concatene ENAME y JOB, y asígnele el alias Empleados

SELECT ename ' es un ' job AS "Empleados"
FROM emp;

http://liang.armstrong.edu:5560/isqlplus

Debe recordar que:
El cargo y el apellido del empleado están combinados para formar una única columna de resultados.
La palabra clave AS delante del alias hace que la cláusula SELECT sea más fácil de leer.

Modificar tablas

Cláusula ALTER TABLE

Se utiliza para alterar la definición de una tabla:

• Añadiendo o eliminando columnas o restricciones.
• Modificando definiciones de columnas(tipo de datos, Not Null, etc)

Sintaxis:
Para agregar columnas

ALTER TABLE nombre_tabla
ADD ( {colum1 tipo_dato [DEFAULT exp][constraint_colum]}
[, {Colum2 tipo_dato [DEFAULT exp][constraint_colum]}]…);

Ejemplo:

• Agregue la columna telefono a la tabla emp1 con el tipo Char(8).

ALTER TABLE emp1
ADD (telefono CHAR(8));

http://liang.armstrong.edu:5560/isqlplus

Ejemplo:

• Agregue la columna pais a la tabla dept; dicha columna deberá ser del tipo de datos Varchar2, longitud 20 y Not null.

ALTER TABLE dept
ADD (pais varchar2(20) NOT NULL);

alter table dept
*
ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column

Debe recordar que:
No es posible añadir una columna nueva que tenga un constraint Not Null a una tabla que tenga datos.

Ejemplo:

• La comision debe ser menor o igual que el salario.

ALTER TABLE emp1
ADD CHECK(comision <= salario);

Ejemplo:

• Cree un Foreign Key entre las tablas empx(copia de la tabla emp) y la tabla deptx(copia de la tabla dept).

1. Primero crearemos la copia de las 2 tablas

Copia de la tabla emp

Create Table empx As
Select * from emp;

Copia de la tabla dept

Create table deptx As
Select * from dept;

2. Crearemos ahora el constraint Primary Key en la tabla deptx sobre el campo deptno. (Obligatorio porque al crearse la copia se pierden todos los constraints, excepto los Not Null)

Alter Table deptx
Add constraint pk_deptx Primary Key (deptno);

3. Ahora crearemos el constraint Foreign Key en el campo deptno de la tabla empx. (Recuerde que un departamento tiene varios empleados)

Alter table empx
Add constraint fk_empx_deptx Foreign Key (deptno)
References deptx

4. Verifiquemos la creación del Foreign Key: fk_empx_deptx en la tabla empx.

Select Constraint_name Nombre, Column_name Columna
From User_cons_columns
Where Table_name='EMPX' and Column_name='DEPTNO';

Restricciones de Columnas (Constraints)

La siguiente tabla muestra los diferentes de restricciones manejados por Oracle.

CONSTRAINT	USO
PRIMARY KEY	Identifica de manera única a cada fila de la tabla
CHECK	Especifica una condición que cada fila en la tabla debe satisfacer.
FOREIGN KEY(columna)	Designa una columna o combinación de columnas como la clave foránea.
REFERENCES(columna)	Establece y obliga una relación entre esta columna de la clave primaria de la tabla referenciada.
[NOT] NULL	Especifica que esta columna no puede contener un valor nulo.
UNIQUE	Especifica que una o mas columnas deben tener valores únicos a través de de todas las filas. Oracle crea un índice único para asegurar este constraint.
ON DELETE CASCADE	Especifica que Oracle mantendrá la integridad automáticamente removiendo los valores de la clave foránea dependiente si se remueven los valores de la clave primaria o única

Ejemplo:

• Crear una tabla que utilice los diferentes tipos de constraints. La tabla tendrá nombres de constraints creados por el propio Oracle.

Create table emp1
(Codigo number(4) Primary Key,
Nombre varchar2(10) Unique,
Trabajo varchar2(9) Not Null,
Jefe number(4) Null,
Ingreso date Default Sysdate,
Salario number(7,2) Default 0 Check(salario >= 0),
Comision number(7,2) Default 0 Check(comision > 0),
Dpto number(2) Check(dpto IN (10,20)));

http://liang.armstrong.edu:5560/isqlplus

Mostramos los constraints creados por defecto por Oracle.

Select Constraint_name Nombre, Column_name Columna
From User_cons_columns
Where Table_name='EMP1';

Ejemplo:

• Elimine varias restricciones simultáneamente.

alter table emp1
drop constraint SYS_C005310
drop constraint SYS_C005309
drop constraint SYS_C005308
drop constraint SYS_C005307
drop constraint SYS_C005306
drop constraint SYS_C005311 ;

Ejemplo:

• Crear una tabla que utilice los diferentes tipos de constraints. La tabla tendrá nombres de constraints creados por nosotros.

Create table emp_x
(codigo number(4) Constraint Pk_emp_x Primary Key,
Nombre varchar2(10) Constraint Un_nom Unique,
Trabajo varchar2(9) Constraint Nn_trab Not Null,
Jefe number(4) Null,
Ingreso date Default Sysdate,
Salario number(7,2) Default 0 Constraint Ck_sal Check(salario >= 0),
Comm number(7,2) Default 0 Constraint Ck_com Check(comm> 0),
Dpto number(2) Constraint Ck_dep Check(dpto IN (10,20)), Constraint Ck_sal_com Check(salario>=comm));

Ahora mostramos los nombres de constraints.

Select Constraint_name Nombre, Column_name Columna
From User_cons_columns
Where Table_name='EMP_X';

Diccionario de datos de Oracle

El diccionario de datos es un conjunto de tablas y vistas que contienen información descriptiva sobre las tablas, privilegios de acceso y otras características de las base de datos.
Es la fuente central de información tanto para el servidor Oracle como para los administradores y usuarios de la misma.
Se actualiza por el servidor Oracle, siempre que un comando DDL es ejecutado.
El diccionario de datos esta localizado en el tablespace SYSTEM del que es propietario el usuario SYS.

Tablas del diccionario más frecuentemente usadas por los usuarios:

---

http://liang.armstrong.edu:5560/isqlplus

USER_CATALOG	Tablas, vistas, sinónimos y secuencias Pertenecientes al usuario.
USER_CONSTRAINTS	Información sobre los constraints en las Tablas del usuario.
USER_INDEXES	Información sobre los índices de los Usuarios
USER_SEQUENCES	Información de las secuencias del Usuario.
USER_SYNONIMOUS	Información de los sinónimos del Usuario.
USER_TABLES	Información de las tablas del Usuario.
USER_USERS	Información sobre el usuario actual.
USER_VIEWS	Información sobre las vistas del Usuario.
ALL_TABLES	Información de todas las tablas Accesibles al usuario.
V$VERSION	Nos da información sobre la versión de Oracle.