SQL AVANZADO

Vista previa del material en texto

Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 1 
 
SQL AVANZADO 
A continuación se describen tres sentencias elementales de manipulación de datos en su sintáxis para 
PostgreSql 
INSERT 
INSERT INTO table_name [ ( column_name [, ...] ) ] 
 { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query 
} 
 [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] 
 
Permite insertar una o mas filas en una tabla. Para insertar mas de una fila a la vez también se puede utilizar 
una consulta SELECT. 
Las columnas destino pueden especificarse en cualquier orden, si no se especifican columnas, se considera 
que se ingresarán todas en el orden en que están declaradas en la tabla. Los valores provistos por la cláusula 
VALUES o por la consulta SELECT estarán asociados con el orden implícito o explícito de la lista de columnas 
de izquierda a derecha. 
Cada columna no presente en el orden implícito o explícito será llenada con su valor por defecto (si fue 
declarado) o con un valor NULL. 
Si la expresión para cualquier columna no se corresponde en su tipo de datos, se intentará automáticamente 
una conversión de tipos. 
La cláusula opcional: RETURNING causa que se retornen los valores insertados. Incluyendo aquellos obtenidos 
por sus valores por defecto. 
Ejemplos 
Insertar una fila en la tabla films 
INSERT INTO films VALUES 
 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); 
 
En el siguiente ejemplo, la columna duración es omitida, por lo tanto tomará el valor por defecto: 
INSERT INTO films (code, title, did, date_prod, kind) 
 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); 
 
En los siguientes ejemplos se utiliza la cláusula DEFAULT para la columna fecha 
INSERT INTO films VALUES 
 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'); 
 
INSERT INTO films (code, title, did, date_prod, kind) 
 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama'); 
 
Para insertar una fila solo con valores por defecto: 
 INSERT INTO films DEFAULT VALUES; 
 
 
 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 2 
 
Para insertar múltiples filas usando la opción multirow de VALUES: 
INSERT INTO films (code, title, did, date_prod, kind) VALUES 
 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), 
 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); 
 
En el siguiente ejemplo se insertan varias filas desde otra tabla con la misma distribución de columnas. 
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07'; 
 
Insertar una fila retornando el número de secuencia generado. 
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') 
 RETURNING did; 
o lo que es lo mismo: 
INSERT INTO distributors ( dname) VALUES ( 'XYZ Widgets') 
 RETURNING did; 
UPDATE 
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] 
 SET { column_name = { expression | DEFAULT } | 
 ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] 
 [ FROM from_list ] 
 [ WHERE condition ] 
 [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] 
 
UPDATE modifica valores de las columnas especificadas en todas las filas que cumplan la condición. Las 
columnas no especificadas retienen sus valores. 
Existen dos maneras de modificar una tabla usando información contenida en otras tablas: 
 Usando subconsultas 
 Especificando tablas adicionales en la cláusula FROM. 
La cláusula RETURNING permite que UPDATE retorne valores basados en cada una de las filas actualizadas. 
La lista de tablas posterior a FROM permite utilizar columnas de otras tablas en la condición WHERE y en las 
expresiones SET. Esto es similar a las listas de tablas que pueden ser especificadas en la cláusula FROM de la 
sentencia SELECT. 
CUIDADO! La tabla que está siendo modificada no 
debe aparecer en esta lista a no ser que se necesite un 
self-join. 
Ejemplos 
Cambiar la palabra Drama a Dramatic en la columna kind de la table films: 
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; 
 
Ajustar la temperatura y resetear las precipitaciones a su valor por defecto en una fila de la tabla weather: 
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT 
 WHERE city = 'San Francisco' AND date = '2003-07-03'; 
 
Realizar el mismo cambio anterior retornando los valores actualizados: 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 3 
 
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT 
 WHERE city = 'San Francisco' AND date = '2003-07-03' 
 RETURNING temp_lo, temp_hi, prcp; 
Sintáxis alternativa: 
UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT) 
 WHERE city = 'San Francisco' AND date = '2003-07-03'; 
 
Incrementar la cuenta de ventas de un vendedor que maneja la cuenta Acme Corporation, usando la cláusula 
FROM: 
UPDATE employees SET sales_count = sales_count + 1 
FROM accounts 
 WHERE accounts.name = 'Acme Corporation' 
 AND employees.id = accounts.sales_person; 
(Lo mas usual es que exista una condición de reunión en el WHERE) 
 
Realizar lo mismo pero con subconsulta: 
UPDATE employees SET sales_count = sales_count + 1 WHERE id = 
 (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); 
 
Supongamos que se desea agregar una columna redundante a una tabla de detalle de productos que 
mantenga el precio actual, para poder dar valor inicialmente a la nueva columna se podría utilizar: 
-- ejemplo con from 
update detprod dp 
set pro_precio = p.pro_precio 
from productos p 
where dp.pro_id=p.pro_id; 
 
-- ejemplo con subconsultas 
update detprod dp 
set pro_precio = ( 
 select pro_precio 
 from productos p 
 where dp.pro_id=p.pro_id) 
 
 
DELETE 
Borra filas que satisfacen la condición de la cláusula WHERE de una tabla especificada. Si no hay cláusula 
WHERE se borran todas las filas de la tabla. 
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] 
 [ USING using_list ] 
 [ WHERE condition ] 
 [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] 
 
Hay dos formas de borrar filas de una table usando información contenida en otras tablas: 
 Usando subconsultas 
 Especificando tablas adicionales en la cláusula USING 
La cláusula RETURNING causa que DELETE calcule y retorne los valores basados en cada fila que es eliminada. 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 4 
 
Ejemplos 
Borrar todos los films musicales: 
DELETE FROM films WHERE kind <> 'Musical'; 
 
Limpiar la tabla films: 
DELETE FROM films; 
 
Borrar las tareas completadas, devolviendo un detalle de las filas eliminadas. 
DELETE FROM tasks WHERE status = 'DONE' RETURNING *; 
 
PostgreSQL permite la referencia a columnas de otras tablas en la cláusula WHERE especificando las otras 
tablas en la cláusula USING (similar al FROM en el UPDATE). Por ejemplo, para borrar todos los films 
producidos por un determinado productor se puede hacer: 
DELETE FROM films USING producers 
 WHERE producer_id = producers.id AND producers.name = 'foo'; 
Es fundamental que se establezca la condición de reunión entre las dos tablas. 
Sintaxis alternativa con subconsulta: 
DELETE FROM filmsWHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo'); 
 
-- Eliminar detalles de productos cuyo precio de venta sea mayor 
-- que el precio actual (en tabla productos) 
delete from detprod dp 
using productos p 
where 
 dp.pro_id=p.pro_id and 
 dp.dp_precio > p.pro_precio 
returning vta_id 
 
-- con subconsultas 
delete from detprod dp 
where 
 dp_precio > ( 
 select pro_precio 
 from productos p 
 where dp.pro_id=p.pro_id 
 ) 
returning vta_id 
 
Funciones de ventana (window functions) 
Las funciones de ventana permiten iterar sobre los resultados de un select después de haber calculado 
proyecciones, filtros where, agrupamientos y condiciones de agrupamientos y justo antes del ordenamiento 
final y antes de limit y offset. 
El motor de funciones de ventana realiza un post-process del resultado del select para dividirlo en varias partes 
llamadas particiones. Estas particiones pueden tener un orden interno 
Una función de ventana realiza un cálculo a través de un conjunto de filas de la tabla que están de alguna 
manera relacionadas con la fila actual. Esto es comparable con el tipo de cálculo que se puede hacer con una 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 5 
 
función agregada. Pero a diferencia de las funciones agregadas regulares, el uso de una función de ventana 
no causa que las filas se agrupen en una fila única. Las filas conservan sus identidades separadas. 
Ejemplo que muestra cómo comparar el salario de cada empleado con el salario medio en su departamento: 
SELECT 
depname, empno, salary, 
avg(salary) OVER (PARTITION BY depname) 
FROM empsalary; 
 
 depname | empno | salary | avg 
-----------+-------+--------+----------------------- 
 develop | 11 | 5200 | 5020.0000000000000000 
 develop | 7 | 4200 | 5020.0000000000000000 
 develop | 9 | 4500 | 5020.0000000000000000 
 develop | 8 | 6000 | 5020.0000000000000000 
 develop | 10 | 5200 | 5020.0000000000000000 
 personnel | 5 | 3500 | 3700.0000000000000000 
 personnel | 2 | 3900 | 3700.0000000000000000 
 sales | 3 | 4800 | 4866.6666666666666667 
 sales | 1 | 5000 | 4866.6666666666666667 
 sales | 4 | 4800 | 4866.6666666666666667 
(10 rows) 
 
Las tres primeras columnas vienen directamente de la tabla empsalary, y hay una fila de salida para cada fila 
de la tabla. La cuarta columna representa un promedio tomado en todas las filas de la tabla que tienen el 
mismo depname que el valor de la fila actual. 
Una llamada a la función de ventana siempre contiene una cláusula OVER inmediatamente después el nombre 
de la función de la ventana y sus parámetros. Esto es lo que sintácticamente la distingue de una función 
agregada regular. 
La cláusula OVER determina exactamente cómo las filas de la consulta se dividen para ser procesadas por la 
función de ventana. Esto es detallado dentro de esta cláusula a través de PARTITION BY. La función de 
ventana calcula a través de las filas que caen en la misma partición que la fila actual. 
También se puede controlar el orden en que las filas son procesadas por funciones de ventana utilizando 
ORDER BY dentro de OVER. 
 
SELECT 
depname, empno, salary, 
rank() OVER (PARTITION BY depname ORDER BY salary DESC) 
FROM empsalary; 
 
 depname | empno | salary | rank 
-----------+-------+--------+------ 
 develop | 8 | 6000 | 1 
 develop | 10 | 5200 | 2 
 develop | 11 | 5200 | 2 
 develop | 9 | 4500 | 4 
 develop | 7 | 4200 | 5 
 personnel | 2 | 3900 | 1 
 personnel | 5 | 3500 | 2 
 sales | 1 | 5000 | 1 
 sales | 4 | 4800 | 2 
 sales | 3 | 4800 | 2 
(10 rows) 
 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 6 
 
Como se muestra aquí, la función Rank() produce un rango numérico dentro de la partición de la fila actual 
para cada valor diferente según ORDER BY. Rank() no necesita ningún parámetro explícito, ya que su 
comportamiento está determinado enteramente por la cláusula OVER. 
ORDER BY puede omitirse si el orden de las filas no es importante. También es posible omitir PARTTION BY, 
en cuyo caso sólo hay una partición que contiene todas las filas. 
Hay otro concepto importante asociado con funciones de ventana: para cada fila, hay un conjunto de filas 
dentro de su partición llamado window frame. Muchas funciones de ventana (no todas) sólo actúan sobre las 
filas del window frame, en lugar de toda la partición. Por defecto, si existe un ORDER BY dentro de OVER, el 
frame consta de las filas desde el inicio de la partición hasta la fila actual (además de las siguientes filas que 
son iguales a la fila actual según ORDER BY). Cuando se omite ORDER BY el frame predeterminado consta de 
todas las filas de la partición. 
SELECT 
salary, 
sum(salary) OVER () 
FROM empsalary; 
 
 salary | sum 
--------+------- 
 5200 | 47100 
 5000 | 47100 
 3500 | 47100 
 4800 | 47100 
 3900 | 47100 
 4200 | 47100 
 4500 | 47100 
 4800 | 47100 
 6000 | 47100 
 5200 | 47100 
(10 rows) 
 
En el ejemplo anterior, como no hay ORDER BY en la cláusula OVER, el frame es igual a la partición y por la 
falta de PARTITION BY, el frame finalmente es toda la tabla. Si se añade un ORDER BY se obtienen resultados 
muy diferentes: 
 
SELECT 
salary, 
sum(salary) OVER (ORDER BY salary) 
FROM empsalary; 
 
 salary | sum 
--------+------- 
 3500 | 3500 
 3900 | 7400 
 4200 | 11600 
 4500 | 16100 
 4800 | 25700 
 4800 | 25700 
 5000 | 30700 
 5200 | 41100 
 5200 | 41100 
 6000 | 47100 
(10 rows) 
 
Universidad Nacional de Salta. 
Facultad de Ciencias Exactas. 
Tecnicatura Universitaria en Programación 
PROGRAMACION AVANZADA EN BASES DE DATOS – OPTATIVA II Página 7 
 
Cláusula WINDOW 
Con el fin de simplificar la sintaxis es posible utilizar la cláusula WINDOW la cual contendrá la definición de la 
partición. 
Ejemplos: 
SELECT 
 *, 
 min(salary) over report_by_category, 
 avg(salary) over report_by_category, 
 max(salary) over report_by_category 
FROM 
 employe 
WINDOW 
 report_by_category as ( 
 partition by 
 category 
 ) 
LIMIT 5; 
id | name | salary | category | min | avg | max 
-----+-------------------+--------+----------+------+--------+------ 
1003 | Employe name 1003 | 6515 | C | 6515 | 7351 | 8187 
1005 | Employe name 1005 | 8187 | C | 6515 | 7351 | 8187 
1001 | Employe name 1001 | 6106 | D | 1001 | 6106 | 6106 
1002 | Employe name 1002 | 2491 | E | 1001 | 3810.5 | 5130 
1004 | Employe name 1004 | 5130 | E | 1001 | 3810.5 | 5130 
 
 
 
SELECT 
 *, 
 count(*) over report_by_category as count, 
 min(salary) over (report_by_category order by name) as ordered_min 
FROM 
 employe 
WINDOW 
 report_by_category as ( 
 partition by 
 category 
 ); 
 
 
 id | name | salary | category | count | ordered_min 
------+-------------------+--------+----------+-------+--------------- 
 2456 | Employe name 24 | 1008 | A | 1035 | 1008 
 2209 | Employe name 220 | 1000 | A | 1035 | 1000 
 3692 | Employe name 369 | 1022 | A | 1035 | 1000 
 3885 | Employe name 3885 | 103 | A | 1035 | 103 
 436 | Employe name 4365 | 1035 | A | 1035 | 103