Lab8 y Lab09

UNSCH-

Escuela de Ing. De Sistemas

I. TEMA: En esta sección veremos como leer el contenido de una tabla mediante la ejecución de consultas sencillas a través de la instrucción SELECT.

II. OBJETIVOS ESPECIFICOS: 

Manejo de consultas condicionales, haciendo uso del comando

BETWEEN, IN,

LIKE 

Obtener resultados originados por la selección de uno o varios grupos, haciendo uso de las funciones de columna.

III. MARCO TEÓRICO Para reco recorr da darr : IMPLEMENTACION IMPLEMENTACION DE CONSULTAS BÁSICAS .

LA INSTRUCCIÓN SELECT La cláusula SELECT lista los datos a recuperar por la sentencia SELECT. Los elementos o datos a seleccionar pueden ser columnas de la base de datos o columnas a calcular por SQL cuando efectúa la consulta o también el asterisco(*) para recuperar todos los campos de un fichero o tabla.

SINTAXIS COMPLETA: SELECT [ALL|DISTINCT] ListaColumnas FROM listado de tablas [WHERE condiciónBúsqueda][ROWNUM] [ORDER BY {columna, expresión, alias} [ASC / DESC] ] [GROUP BY [ALL]expresiónAgruparPor] [ALL]expresiónAgruparPor] [WITH CUBE | ROLLUP ] [HAVING condiciónBúsqueda] AVG | COUNT | MAX | MIN |SUM (expresión)]

Modelamiento de datos

1

Ing. Elvira Fernández

UNSCH-


IV. CASO PRÁCTICO Se tiene la siguiente estructura de un modelo físico de datos, para ello crear un tablespace y un esquema ventas.

EJERCICIOS PROPUESTOS EJERCICIO1: El siguiente ejemplo retorna más relevantes de la tabla productos. El ejemplo solo retorna las filas de los productos que tengan un precio comprendido entre 12 y 20 soles

EJERCICIO2 :El siguiente ejemplo retorna las columnas más relevantes de la tabla productos. EL ejemplo solo retorna las filas de los productos que pertenezcan a las categorías 1,3 o 5


2


UNSCH-


CONSULTAS CONDICIONALES: BETWEEN Para indicar que deseamos recuperar los registros según el intervalo de valores de un campo emplearemos el operador Between cuya sintaxis es:

CAMPO [NOT] BETWEEN VALOR1 AND VALOR2 (LA CONDICION NOT ES OPCIONAL) En este caso la consulta devolvería los registros que contengan en “campo” un valor incluido en el intervalo Valor1, Valor2 (ambos inclusive). Si anteponemos la condición NOT devolverá aquellos valores no incluidos en el intervalo.

EJEMPLO 1: El siguiente ejemplo es similar al ejercicio 01. Se utiliza BETWEEN que especifica el intervalo inclusivo que se va a probar. SELECT IdProducto as Codigo,Nombre as “Descripcion”,PrecioProveedor as “Precio Unitario ” FROM PRODUCTo WHERE PrecioProveedor BETWEEN 12 AND 20

IN Este operador devuelve aquellos registros cuyo campo indicado coincide con alguno de una lista. Su sintaxis es:

EXPRESIÓN [NOT] IN (VALOR1, VALOR2,…)

EJEMPLO2: El siguiente ejemplo es similar al ejercicio 02, se utiliza IN que determina si un valor dado coincide con algún valor de una subconsulta o lista. SELECT * FROM PRODUCTO WHERE IdCategoria IN(1,3,5)

EJERCICIO3: Consultar todos los PROVEEDORES que vivan en las ciudades de Lima, Trujillo o Arequipa.

LIKE Se utiliza para comparar una expresión de cadena con un modelo específico. Sirve para comparar parcialmente los valores de un campo

Su sintaxis: EXPRESION LIKE MODELO En donde expresión es una cadena modelo o campo contra el que se compara expresión. Se puede utilizar el operador LIKE para encontrar valores en los campos que coincidan con el modelo especificado. Por modelo también se puede especificar un valor completo, o se pueden utilizar caracteres comodín para encontrar un rango de valores de la siguiente forma:


3


UNSCH-


EJEMPLO 3: Consultar todos los Empleados que tengan como apellido Paterno igual a Cardenas. SELECT IdEmpleado as codigo, Nombres,ApePat, ApeMat, FechaNacimiento FROM empleado WHERE ApePat like 'Cardenas'

A continuación presentaremos los distintos caracteres comodín para posteriormente comprobar las expresiones con diferentes modelos.

Carácter Comodín % (Subrayado) NOT

Descripción Cualquier cadena de cero o más caracteres Cualquier carácter individual Negación de la cadena

El operador Like se puede utilizar en una expresión para comparar un valor de un campo con una expresión de cadena. Por ejemplo, si introduce Like c% en una consulta SQL, la consulta devuelve todos los valores de campo que comiencen por la letra C. en una subconsulta con parámetros, puede hacer que el usuario escriba el modelo que se va utilizar.

EJERCICIO4: Este ejemplo devuelve los nombres cuyo contenido empiece con la letra A y la tercera letra también sea el carácter A, seguida de cualquier cadena.

ROWNUM Devuelve, en una consulta, el número de fila actual. Resulta útil para limitar número total de filas y se usa principalmente en la cláusula where de las consultas.

EJEMPLO 04: Supongamos que queremos recuperar los nombres de los 5 primeros proveedores , ordenado ascendentemente por departamento. SELECT IdProveedor,Nombre FROM PROVEEDOR Where ROWNUM <6 ORDER BY Departamento DESC

EJERCICIO 05: Muéstrame los 3 productos más baratos (nombre y precio).


4


UNSCH-


OBJETIVOS

I. 

II. 

 

III.

Realizar consultas utilizando las diferentes funciones de grupo.

CONTENIDO Funciones de Grupo o AVG o COUNT o MAX o MIN o SUM GROUP BY HAVING

MARCO TEÓRICO INTRODUCCION

Muchas peticiones de información no requieren el nivel de detalle proporcionado por las consultas descritas en las sesiones anteriores. Ahora veremos que las sentencias de oracle soporta peticiones de datos sumarios mediante funciones de columna y mediante cláusulas GROUP BY y HAVING de la sentencia SELECT. Las funciones de agregación permiten consolidar datos numéricos. El uso de la cláusula GROUP BY nos permitirá obtener reportes con data consolidada según distintos criterios.

IV.

FUNCIONES DE AGREGACION

Son funciones que permiten efectuar una operación aritmética que resume los valores de una columna de toda la tabla, o resume los valores de la columna agrupados según determinado criterio la función produce un solo valor que es el resumen de la tabla, o de cada uno de los grupos.

1.

SUM Devuelve la suma del conjunto de valores contenido en un campo específico de una consulta.

Sintaxis : SELECT SUM(`[DISTINCT] expresión) FROM TABLA En donde:


5


UNSCH-


DISTINCT indica que debe eliminarse los valores duplicados de expresión antes de avaluar la función.

expresión representa el nombre del campo que contiene los datos que desean sumarse o una expresión que realice un cálculo utilizando los datos de dichos campos. Los operandos de expresión pueden incluir el nombre de un campo de una tabla, una constante o una función.

Veamos algunos ejemplos: EJEMPLO 01: El siguiente ejemplo retorna el total de unidades pedidas (la suma de todos los valores almacenados en el campo 'cantidad' de la tabla Guia_detalle). SELECT SUM(cantidad ) as “Cantidad pedidos” FROM GUIA_DETALLE

EJERCICIO 01: El siguiente ejemplo retorna el monto Total de los productos vendidos de la tabla Guia_detalle

2.

AVG: Calcula la media aritmética de un conjunto de valores contenido en un campo especificado de una consulta.

Sintaxis : SELECT AVG((`[DISTINCT] expresión_numérica) FROM TABLA En donde:

expresión_numérica representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por AVG es la media aritmética(la suma de los valores dividido por el número de valores).la función AVG no incluye ningún campo Null en el cálculo.

DISTINCT indica que debe eliminarse los valores duplicados de expresión antes de avaluar la función.

EJEMPLO 02: Obtener el precio unitario promedio de todos los productos en la tabla productos. SELECT AVG(PrecioProveedor) as “PROMEDIO PRECIOS” FROM Producto


6


UNSCH-

3.


MIN, MAX Devuelven el mínimo o el máximo de un conjunto de valores contenido en un campo específico de una consulta.

Sintaxis : SELECT MIN(expresión) FROM TABLA SELECT MAX(expresión) FROM TABLA En donde:

expresión es el campo sobre el que se desea realizar el cálculo. expresión pueden incluir el nombre de un campo de una tabla, una constante o una función.

EJEMPLO 03: El siguiente ejemplo retorna el precio más alto y más bajo de los productos registrados en la tabla productos SELECT MAX (PrecioProveedor )AS “PRECIO MAYOR” , MIN(PrecioProveedor) AS “PRECIO MENOR” FROM PRODUCTO

4.

COUNT Calcula el número de registros devueltos por una consulta.

Sintaxis: SELECT COUNT[DISTINCT](expresión) FROM TABLA En donde:

expresión contiene el nombre del campo que desea contar. Los operandos de expresión pueden incluir el nombre de un campo de una tabla, una constante o una función. Puede contar cualquier tipo de datos incluso texto. Aunque expresión puede realizar un cálculo sobre un campo, count simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función COUNT no cuenta los registros que tienen campos NULL a menos que expresión sea el carácter comodín asterisco (*). Si utiliza un asterisco, COUNT calcula el número total de registros, incluyendo aquellos que contienen campos null. COUNT(*) Es considerablemente mas rápida que count(campo). No se debe poner el asterisco entre comillas simples (‘*’)

EJEMPLO 04: El siguiente ejemplo cuenta el total de productos registrados en la base de datos SELECT COUNT(*) AS TOTAL_PRODUCTOS


7


UNSCH-


FROM producto

EJERCICIO 02: El siguiente ejemplo retorna el total, la media, el máximo y el mínimo de unidades pedidas, y el número de pedidos realizados.

Nota:

Se pueden combinar varias funciones de columna en una expresión pero no se pueden anidar funciones de columna, es decir: SEL ECT (A VG(ventas) * 3) + SUM(cuota) F ROM ...

SEL ECT A VG(SUM (ventas)) F ROM ...

NO es correcto, no se puede incluir una función de columna dentro de una función de columna

es correcto

V. 5.

GROUP BY y HAVING GROUP BY Combina los registros con valores idénticos, en la lista de campos especificados, en un único registro. Para cada registro se crea un valor sumario si se incluye una función SQL agregada, como por ejemplo SUM o COUNT, en la instrucción SELECT. Su sintaxis es:

SELECT CAMPOS FROM TABLA WHERE CRITERIO GROUP BY CAMPOS DEL GRUPO GROUP BY es opcional. Los valores de resumen se omiten si no existe una función SQL agregada en la instrucción SELECT. Los valores null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores null no se evalúan en ninguna de las funciones SQL agregadas.

EJEMPLO 05: El siguiente ejemplo retorna la cantidad de productos registrados para cada categoría. SELECT idCategoria, COUNT(IDPRODUCTO) AS Cantidad_productos FROM PRODUCTO GROUP BY idCategoria

EJEMPLO 06: El siguiente ejemplo retorna la cantidad de productos por proveedor para las categorías 2 y 4.


8


UNSCH-


SELECT idCategoria, IdProveedor, COUNT(IDPRODUCTO ) AS cantidad_productos FROM PRODUCTO where idCategoria IN(2,4) GROUP BY idCategoria,IdProveedor ORDER BY idCategoria

NOTA: 

columna de agrupación se puede indicar mediante un nombre de columna o cualquier expresión válida basada en una columna pero no se La

pueden utilizar los alias de campo. 

VI.

Ejemplo :

SELECT importe/cant , SUM(importe) FROM pedidos GROUP BY importe/cant

Está permitido, equivaldría a agrupar las líneas de pedido por precio unitario y sacar de cada precio unitario el importe total vendido.

SELECT importe/cant AS precio, SUM(importe) FROM pedidos GROUP BY precio

No está permitido, no se puede utilizar un alias campo.

CLAUSULA HAVING: Especifica una condición de búsqueda de un grupo o agregado. Normalmente, HAVING se utiliza con la cláusula GROUP BY. Cuando no se utiliza GROUP BY, HAVING se comporta como la cláusula WHERE. HAVING es similar a WHERE, determina qué registros se seleccionan. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cual de ellos se van a mostrar. Haciendo uso del HAVING :


9


UNSCH-


EJEMPLO 07: El siguiente ejemplo retorna los productos cuyo monto total despachado es mayor a 15 000 de la tabla guia_detalle. SELECT idProducto, SUM(precioVenta*cantidad) AS “MOnto Total” FROM GUIA_DETALLE GROUP BY idProducto HAVING SUM(precioVenta*cantidad)>15000 ORDER BY “MOnto Total” DESC

EJEMPLO 08: Para cada pedido en que se encuentre más de 5 artículos, recuperar el número de pedido y el número de productos pedidos o vendidos. SELECT IdGuia, COUNT(IdProducto ) AS NRO_PRODUCTOS, sum(PrecioVenta*Cantidad)as IMPORTE FROM dbo.GUIA_DETALLE GROUP BY IdGuia HAVING COUNT(IdProducto )>10 ORDER BY IMPORTE

VII.

EJRCICIOS PROPUESTOS

EJERCICIO 01:

Mostrar los empleados que ganan el haber basico entre

1000 y 2000

EJERCICIO 02: El siguiente ejercicio retorna las columnas de la tabla producto más importantes. El ejemplo solo retorna las filas de los productos de los precios que no estén comprendidos entre 10 y 50 soles


10


UNSCH-


EJERCICIO 03:El siguiente ejercicio retorna todas las columnas de la tabla productos. El ejemplo sólo retorna las filas de los Productos que no empiece con F o C como carácter inicial en el nombre del producto.

EJERCICIO 04: el siguiente ejemplo retorna las columnas idProveedor, nombre, representante, ciudad y departamento de la tabla Proveedores. El ejemplo sólo retorna las filas de los Proveedores que tengan los caracteres “a” al final de la columna ciudad.

EJERCICIO 05: Muestréame el empleado que tenga el haber básico más alto

EJERCICIO 06: El siguiente ejemplo retorna el promedio de unidades pedidas (la media de todos los valores almacenados en el campo cantidad de la tabla Guia_detalle ').

EJERCICIO 07: El siguiente ejemplo retorna el pedido más pequeño y lo refleja en el campo 'minimo'.

EJERCICIO 08: El siguiente ejemplo retorna la guia de remisión más antigua

EJERCICIO 09: El siguiente ejemplo retorna la guia de remisión más reciente.

EJERCICIO 10: El siguiente ejemplo retorna el nombre del primer producto y del último producto si se ordena en base al nombre. EJERCICIO 11: el siguiente ejemplo retorna el total de empleados que nacieron el año 1985


11


UNSCH-


EJERCICIO 12: El siguiente ejemplo retorna el idGuia, el número de artículos en cada pedido y el monto total de cada pedido de la tabla Guia_detalle y ordenado por el código de la guia en forma ascendente.

EJERCICIO 13: El siguiente ejemplo retorna el Monto total despachado por producto y ordenado por el Monto total en forma descendente.


12


Lab8 y Lab09

Recommend Documents