guía de laboratorio #3aaula.fia.ues.edu.sv/.../guia03bad115_sqlbasicooraclev2.pdf ·...

U N I V E R S I DA D D E E L S A LVA D O R

FA C U LTA D D E I N G E N I E R I A Y A R Q U I T E C T U R A

E S C U E L A D E I N G E N I E R I A D E S I S T E M A S

I N F O R M AT I C O S

Base de Datos BAD115

Catedráticos: Ing. Elmer Arturo Carballo Ruiz MSc.

Ing. César Augusto González Rodríguez MAF.

Ciclo

I

Guía de Laboratorio #3a

Lenguaje estructurado de Consulta SQL

Básico

Contenido Objetivos .................................................................................................................................. 1

Introducción ............................................................................................................................. 1

Desarrollo. ................................................................................................................................ 2

Esquemas a utilizar en la guía ................................................................................................. 2

Definición .......................................................................................................................... 2

Esquemas .......................................................................................................................... 2

Conexiones ........................................................................................................................ 3

Ejercicio 1 Consultar Catalogos ............................................................................................... 4

Ejercicio 2: Consultar la Estructura de una Tabla ...................................................................... 6

Ejercicio 3: Consultar el Contenido de una Tabla...................................................................... 6

Fundamentos de SQL ................................................................................................................. 7

Consultas Simples.....................................................................................................................10

Ejercicio 4: Mostrar registros de la Tabla Jobs en HR ...........................................................10

Ejercicio 5: Mostrar los atributos job_title y min_salary de la Tabla Jobs en HR ....................10

Ejercicio 6: Mostrar los atributos job_title y min_salary de la Tabla Jobs en HR, renombrando

las columnas con los nombres Titulo y Salario Minimo. .......................................................11

Ejercicio 7: Mostrar codigos de departamento(sin repeticiones) que existen en la table

Employees de HR ..............................................................................................................11

Ejercicio 8: Extraer Datos de la Tabla DUAL .........................................................................12

Ejercicio 9: Ejercicios de Limitación las Filas(where)............................................................12

Consultas con Operadores Lógicos ............................................................................................16

Consultas con Otros Operadores(IN, NOT IN… ) ..........................................................................17

Consultas con Ordenamiento de Filas ........................................................................................19

Consultas con Expresiones Condicionales(CASE).........................................................................21

Anexo. .....................................................................................................................................24

Universidad de El Salvador BAD-115 Guía de Laboratorio #3

Ciclo II-2012 1

Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF

Objetivos Que el estudiante sea capaz de:

Realizar las operaciones básicas de consulta en SQL sobre Oracle utilizando una sola tabla.

Comprender los conceptos fundamentales referentes a consultas en SQL.

Utilizar correctamente la cláusula where para restringir las filas a mostrar, con expresiones

simples y complejas.

Hacer ajustes en las consultas que utilizan valores nulos.

Introducción

En esta de laboratorio se utilizaran los esquemas Scott y HR para realizar

operaciones básicas de consulta con SQL, ejecutando instrucciones en SQLDeveloper

conectado a los esquemas antes mencionados.

Se detallaran fundamentos de consultas en SQL que incluyen definiciones de elementos

básicos, Operadores Aritméticos, de concatenación, de conjunto, precedencia der

operadores, literales, etc.

Se utilizaran operadores lógicos y relacionales, ordenamiento de filas, valores

condicionales dentro de un atributo (CASE) y otras operaciones especiales como

concatenación y manejo de valores nulos en consultas.


Ciclo II-2012 2


Desarrollo.

Esquemas a utilizar en la guía

Definición

El conjunto de objetos que tiene una cuenta de usuario se denomina esquema del usuario,

por lo tanto el nombre del esquema será también el nombre del usuario.Cuando creamos la base

de datos de Oracle, por defecto crea dos esquemas de ejemplo, para poder realizar nuestras

pruebas.

Esquemas

Estos esquemas son los siguientes:

HR Se trata de un esquema de recursos humanos, cuenta con 7 tablas.

SCOTT Se trata de un esquema muy básico de recursos humanos, cuenta con tan solo 4 tablas.

Esquema HR


Ciclo II-2012 3


Esquema SCOTT

Conexiones

Realice las conexiones para los esquemas Scott y HR con los parámetros que se muestran:

HR


Ciclo II-2012 4


SCOTT

Si ya los había hecho, haga caso omiso de este apartado. Si tiene instalada la versión express de

Oracle, consulte el Anexo.

Ejercicio 1 Consultar Catalogos a) Consultar el catálogo de scott


Ciclo II-2012 5


b) Consultar el catálogo de HR


Ciclo II-2012 6


Ejercicio 2: Consultar la Estructura de una Tabla

Sintaxis DESCRIBE Nombre_Tabla

Como ejemplo ilustrativo consultemos la estructura de la tabla EMP del esquema SCOTT:

Ejercicio 3: Consultar el Contenido de una Tabla

Sintaxis SELECT * FROM Nombre_Tabla

Como ejemplo ilustrativo consultemos e contenido de la tabla DEPT de SCOTT:


Ciclo II-2012 7


Fundamentos de SQL

Definiciones

Data Manipulation Language (DML)

Usado para acceder, crear, modificar, o eliminar data en una estructura de base de datos

existente.

Data Definition Language (DDL)

Usado para crear, modificar, o eliminar objetos de base de datos y sus privilegios.

Transaction Control

Las instrucciones de control de transacciones garantizan la consistencia de los datos,

organizando las instrucciones SQL en transacciones lógicas, que se completan o fallan

como una sola unidad.

Session Control

Estas instrucciones permiten controlar las propiedades de sesión de un usuario. La sesión

se inicia desde el momento en que el usuario se conecta a la base de datos hasta el

momento en que se desconecta.

System Control

Usadas para manejar las propiedades de la base de datos.

Tipos de Datos de Oracle

Categoría Tipos de Datos

Character CHAR, NCHAR, VARCHAR2, NVARCHAR2

Number NUMBER

Long and raw LONG, LONG RAW, RAW

Date and time DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME STAMP WITH LOCAL TIME

ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND

Large object CLOB, NCLOB, BCLOB, BFILE

Row ID ROWID, UROWID


Ciclo II-2012 8


Operadores y Literales

Operadores Aritméticos

Operador Propósito Ejemplo

+ - Operadores unarios: Usado para representar datos positivos y

negativos. Para datos positivos, el + es opcional. -234.56

+ Suma: Usado para sumar dos números o expresiones 5+7

- Resta: Usado para encontrar la diferencia entre dos números o

expresiones. 56.8-18

* Multiplicación: Usado para multiplicar dos números o

expresiones. 7*5

/ División: Usado para dividir un número o expresión con otro. 8.67/3

Operador de Concatenación

Dos barras verticales ( || ) son usadas como operador de concatenación. La siguiente

tabla muestra dos ejemplos.

Ejemplo Resultado

‘Alianza Lima’ || ‘Campeón’ ‘Alianza LimaCampeón’

‘Alianza Lima ’ || ‘Campeón’ ‘Alianza Lima Campeón’

Operadores de Conjuntos

Estos Operadores son usados para combinar el resultado de dos consultas.

Operador Propósito

UNION Retorna todas las filas de cada consulta; no las filas duplicadas.

UNION ALL Retorna todas las filas de cada consulta, incluyendo las filas duplicadas.

no las filas duplicadas

INTERSECT Retorna las filas distintas del resultado de cada consulta.

MINUS Retorna las filas distintas que son retornadas por la primera consulta pero


Ciclo II-2012 9


que no son retornadas por la segunda consulta.

Precedencia de Operadores

Precedencia Operador Propósito

1 - + Operadores unarios, negación

2 * / Multiplicación, división

3 + - || Suma, resta, concatenación

Literales

Son valores que representan un valor fijo. Estos pueden ser de cuatro tipos diferentes:

Texto

‘CEPS-UNI’

‘Nos vemos en Peter’’s, la tienda del chino’

‘El curso es “Oracle”, y lo dictan en CEPS-UNI

’28-JUL-2006’

Entero 45

-345

Número

25

-456.78

15E-15


Ciclo II-2012 10


Consultas Simples

Usando la Sentencia SELECT

Consulta del contenido de una Tabla

Nota: Las siguientes consultas se realizaran sobre el esquema HR a no ser que se indique lo

contrario

Ejercicio 4: Mostrar registros de la Tabla Jobs en HR

Seleccionando Columnas

Ejercicio 5: Mostrar los atributos job_title y min_salary de la Tabla Jobs en HR


Ciclo II-2012 11


Alias para Nombres de Columnas

Ejercicio 6: Mostrar los atributos job_title y min_salary de la Tabla Jobs en HR,

renombrando las columnas con los nombres Titulo y Salario Minimo.

Nota: pruebe este mismo comando, suprimiendo la palabra AS

Asegurando Valores Únicos

Ejercicio 7: Mostrar codigos de departamento(sin repeticiones) que existen en la table

Employees de HR


Ciclo II-2012 12


La Tabla DUAL

Ejercicio 8: Extraer Datos de la Tabla DUAL

La fecha del sistema

Mostrar el usuario con el que se ha entrado y la fecha del sistema

Ejercicio 9: Ejercicios de Limitación las Filas(where)

Operadores de Comparación

Igualdad ( = )

a) Mostrar nombre, apellido y codigo de departamento de los empleados del

departamento 90.

Script: select first_name || ' ' || last_name, department_id from employees where department_id = 90;


Ciclo II-2012 13


Diferente ( !=, <>, ^= )

b) Mostrar nombre, apellido y porcentaje de comission de los empleados que no

tienen comission de 0.35.

Script : select first_name || ' ' || last_name, commission_pct from employees where commission_pct

<> .35;


Ciclo II-2012 14


Menor Que ( < )

c) Mostrar nombre, apellido y porcentaje de comission de los empleados que tienen

comission de menos del 15% ( 0.15).

Script : select first_name || ' ' || last_name, commission_pct from employees where commission_pct <

.15;

Mayor Que ( > )

d) Mostrar nombre, apellido y porcentaje de comission de los empleados que tienen

comission de mas del 35% ( 0.35).

Script : select first_name || ' ' || last_name, commission_pct from employees where commission_pct >

.35;

Menor ó Igual Que ( <= )

e) Mostrar nombre, apellido y porcentaje de comission de los empleados que tienen

comission de menos del 15% incluido este ( 0.15), .


<= .15;

Mayor ó Igual Que ( >= )

f) Mostrar nombre, apellido y porcentaje de comission de los empleados que tienen

comission de mas del 35% incluido este( 0.35).


>= .35;

ANY ó SOME

g) Mostrar nombre, apellido y codigo de departamento de los empleados que trabajan

en alguno de los departamentos que son menores o iguales a 10,15,20,o 25.

Script: select first_name || ' ' || last_name, department_id from employees where department_id <=

ANY (10,15,20,25);


Ciclo II-2012 15


ALL

h) Mostrar nombre, apellido y codigo de departamento de los empleados que trabajan

en los departamentos de numeracion mayor a 80, 90 y 100.

Script: select first_name || ' ' || last_name, department_id from employees where department_id >=

ALL (80,90,100);


Ciclo II-2012 16


Consultas con Operadores Lógicos

NOT

i) Mostrar nombre y codigo de departamento de los empleados que trabajan en los

departamentos que en los que su numeracion no es mayor o igual a 30.

Script select first_name, department_id from employees where not (department_id >= 30);

AND

j) Mostrar nombre y salario de los empleados que se apellidan Smith y ganan mas

de 75000.

Script: select first_name, salary from employees where last_name = 'Smith' and salary > 7500;

OR

k) Mostrar nombre y apellico de los empleados con nombre Kelly o su apellido es

Smith

Script: select first_name, last_name from employees where first_name = 'Kelly' or last_name =

'Smith';


Ciclo II-2012 17


Consultas con Otros Operadores(IN, NOT IN… )

IN y NOT IN

l) Mostrar nombre y codigo de departamento de los empleados que trabajan en los

departamentos 10,20 o 90.

Script: select first_name, last_name, department_id from employees where department_id in (10, 20,

90);

m) Mostrar nombre y codigo de departamento de los empleados que no trabajan en

los departamentos 10,30,40,50,60,80,90,100 y 110.

Script: select first_name, last_name, department_id from employees where department_id not in (10,

30, 40, 50, 60, 80,90, 100,110);

BETWEEN

n) Mostrar nombre, apellido y salario de los empleados que ganan entre 5000 y

6000.

Script: select first_name, last_name, salary from employees where salary between 5000 and 6000;

EXISTS

o) Mostrar nombre, apellido y codigo de departamento de los empleados que

trabajan en el departemento llamado Administracion.

Script: select first_name, last_name, department_id from employees e where exists (select 1 from

departments d where d.department_id = e.department_id and d.department_name =

'Administration');


Ciclo II-2012 18


IS NULL y IS NOT NULL

p) Mostrar nombre, apellido y codigo de departamento de los empleados que no

tienen un departamento asignado(department=nulo).

Script: select first_name,last_name, department_id from employees where department_id is null;

LIKE

q) Mostrar nombre, apellido y codigo de departamento de los empleados cuyos

nombres empiezan con la palabra Su y sus apellidos empiezan con S.

Script: select first_name, last_name from employees where first_name like 'Su% ' and last_name not

like 'S% ';


Ciclo II-2012 19


Consultas con Ordenamiento de Filas

a) Mostrar nombre, apellido y codigo de departamento de los empleados del

departamento 90 ordenados por nombre.

Script: select first_name, last_name from employees where department_id = 90 order by first_name;

b) Mostrar nombre, apellido y codigo de departamento de los empleados del

departamento 90 ordenados por apellido.

Script: select first_name || ' ' || last_name "Employee Name" from employees where department_id =

90 order by last_name;

c) Mostrar nombre, apellido, fecha de ingreso a la empresa y codigo de

managers(MID) de los empleados que estan en los departamentos 110,100

ordenados por mid ascendentemente, salario descendentemente, y fecha de

ingreso a la empresa ascendentemente.

Script: select first_name, hire_date, salary, manager_id mid from employees where department_id in

(110,100) order by mid asc, salary desc, hire_date;

d) Mostrar las distintas regiones de los paises ordenadas por su codigo.

Script: select distinct 'Region ' || region_id from countries order by 'Region ' || region_id;

e) Mostrar nombre, apellido, fecha de ingreso a la empresa y codigo de

managers(MID) de los empleados que estan en los departamentos 110,100

ordenados por codigo de managers(MID), apellido y fecha de ingreso a la

empresa.

Script: select first_name, hire_date, salary, manager_id mid from employees where department_id in

(110,100) order by 4, 2, 3;


Ciclo II-2012 20


Ordenando con registros Nulos

f) Mostrar el apellido y el porcentaje de commission de los empleados cuyo apellido

empieza con A ordenado por el porcentaje de comision.

Script: select last_name, commission_pct from employees where last_name like 'A% ' order by

commission_pct asc;

g) Mostrar el apellido y el porcentaje de commission de los empleados cuyo apellido

empieza con A ordenado por el porcentaje de comision considerando los valores

nulos como los primeros en ser ordenados.

Script: select last_name, commission_pct from employees where last_name like 'A% 'order by

commission_pct asc nulls first;


Ciclo II-2012 21


Consultas con Expresiones Condicionales(CASE)

La Expresión CASE

Caso 1

Formato

CASE <expresión>

WHEN <Valor1> THEN <Valor de Retorno 1>



. . .

[ELSE <Valor de Retorno>]

END

h) Mostrar el nombre del pais y codigo de region estableciendo que si es de la

Region 1 pertenece a Europa, 2 a America, 3 a Asia y si no esta en los tres casos

anteriores sera otro continente, hagalo solo para los paises en los que su nombre

empiece con la letra I.

Script: select country_name, region_id, case region_id when 1 then 'Europa' when 2 then 'America'

when 3 then 'Asia' else 'Otro' end as continente from countries where country_name like 'I% ';


Ciclo II-2012 22


Caso 2

Formato

CASE

WHEN <Condición1> THEN <Valor de Retorno 1>



. . .

[ELSE <Valor de Retorno>]

END

i) Mostrar el nombre, codigo de departamento y salario de los empleados

especificando que es bajo si es menor a 6000, regular si esta entre 6000 y es

menor de 10000 y Alto si es mayor o igual a 10000 en una columna llamada

categoria para los empleados que estan en los departamentos con identificador

menores o iguales a 30.

Script: select first_name, department_id, salary, case

when salary < 6000 then 'Bajo'

when salary < 10000 then 'Regular'

when salary >= 10000 then 'Alto'

end as Categoría

from employees

where department_id <= 30

order by first_name;


Ciclo II-2012 23



Ciclo II-2012 24


Anexo.

Este anexo ha sido hecho para que se pueda desarrollar las guías de Oracle sobre los esquemas HR

y SCOTT para la base de datos xe (Express Edition)

1. Re activar el usuario hr

2. Crear usuario Scott

Conéctese al usuario administrador, y dentro de este digite los comandos que se le muestran

Cierre la conexión al administrador y haga una conexión nueva a Scott con los parámetros

siguientes:


Ciclo II-2012 25


3. Crear el Esquema e introducirle los Datos al esquema Scott

a) Conéctese al usuario Scott y pegue el script de creación de Base de Datos

(01_Creacion_Scott_Oracle) luego presione el icono de ejecutar script.

b) Siempre en el usuario Scott, Borre el script anterior y pegue el script de inserción de

datos (01_ScriptDatos_Scott_Oracle) luego presione el icono de ejecutar script.

http://aula.fia.ues.edu.sv/materialpublico/bad115/GuiasLab/guia03_scripts/01_Creacion_Scott_Oracle.txt

http://aula.fia.ues.edu.sv/materialpublico/bad115/GuiasLab/guia03_scripts/01_ScriptDatos_Scott_Oracle.txt

guía de laboratorio #3aaula.fia.ues.edu.sv/.../guia03bad115_sqlbasicooraclev2.pdf ·...

Documents