administración de base de datos ii

UNIVERSIDAD DE PAMPLONA – Centro de Educación virtual y a Distancia

Universidad de

Centro de Educación Virtual y a Distancia

ADMINISTRACIÓN DE BASE DEDATOS II

SERGIO PEÑALOZA ROJAS

Álvaro González JovesRector

María Eugenia Velasco EspitiaDecana Facultad de Estudios Avanzados, Virtuales, a Distancia y Semiescolarizados

Programas de Educación a Distancia

Formando Colombianos De Bien


Tabla de Contenido

Presentación 1Introducción 2Horizontes 4

UNIDAD 1: LENGUAJE DE DEFINICIÓN DE DATOS 5Descripción Temática 5Horizontes 6Núcleos Temáticos y Problemáticos 6Proceso de Información 61.1 COMPONENTES DEL LENGUAJE SQL 61.1.1 Tipos de Datos 61.1.2 Operadores 91.2 RESTRICCIONES SEMANTICAS 101.2.1 Clave Primaria 101.2.2 Unicidad 111.2.3 Obligatoriedad 111.2.4 Clave Foránea 111.2.5 Verificación 121.2.6 Disparadores 121.3 BASE DE DATOS 131.3.1 Creación de Base de Datos 131.3.2 Eliminar Base de Datos 141.4 TABLAS 151.4.1 Creación de Tablas 161.4.2 Alterar Tablas 221.4.3 Eliminar Tablas 261.5 IMPLEMENTACIÓN EN POSTGRESQL 27Proceso de Comprensión y Análisis 34Solución de Problemas 36Síntesis Creativa y Argumentativa 37Autoevaluación 38Repaso Significativo 39Bibliografía Sugerida 40

Administración de Base de Datos II


UNIDAD 2: LENGUAJE DE DEFINICION DE DATOS I 41Descripción Temática 41Horizontes 42Núcleos Temáticos y Problemáticos 42Proceso de Información 422.1 COMPONENTES DEL LENGUAJE SQL 422.1.1 Características del Lenguaje SQL 422.1.2 Operadores 432.1.3 Cláusulas 442.1.4 Predicados 442.2 CONSULTAS DE ACCION 452.2.1 Insertar 452.2.2 Modificar 472.2.3 Eliminar 502.3 CONSULTAS DE SELECCIÓN – SIMPLES 522.3.1 Select 522.3.2 Consultas Simples con la Cláusula FROM 552.3.3 Consultas Simples con la Cláusula ORDER BY 602.3.4 Consultas Simples con el Predicado DISTINCT/ALL 632.3.5 Consultas Simples con la Cláusula WHERE 642.4 CONSULTAS DE SELECCIÓN – MULTITABLAS 692.4.1 Producto Cartesiano 702.4.2 Composición Interna – Producto cartesiano más Restricción 712.4.3 Composición Interna - INNER JOIN 762.4.4 Composición Externa 772.5 FUNCIONES DE FORMATO 792.6 IMPLEMENTACIÓN EN POSTGRESQ 83Proceso de Comprensión y Análisis 86Solución de Problemas 88Síntesis Creativa y Argumentativa 91Autoevaluación 91Repaso Significativo 92Bibliografía Sugerida 94

UNIDAD 3: LENGUAJE DE DEFINICION DE DATOS II 95Descripción Temática 95Horizontes 96Núcleos Temáticos y Problemáticos 96Proceso de Información 963.1 FUNCIONES DE COLUMNA 963.1.1 Función COUNT 963.1.2 Función SUM 983.1.3 Función MIN 99



3.1.4 Función MAX 1003.1.5 Función AVG 1013.2 CONSULTAS SUMARIAS 1023.2.1 La cláusula GROUP BY 1033.2.2 La cláusula HAVING 1043.3 SUBCONSULTAS 1063.3.1 Referencias Externas 1063.3.2 Anidar Subconsultas 1073.3.3 Subconsultas en la cláusula FROM 1083.3.4 Subconsulta en las cláusulas WHERE y HAVING 1093.4 IMPLEMENTACIÓN EN POSTGRESQL 111Proceso de Comprensión y Análisis 114Solución de Problemas 116Síntesis Creativa y Argumentativa 119Autoevaluación 119Repaso Significativo 120Bibliografía Sugerida 121

BIBLIOGRAFIA GENERAL 122



1

Presentación

La educación superior se ha convertido hoy día en prioridad para el GobiernoNacional y para las universidades públicas, brindado oportunidades de superacióny desarrollo personal y social, sin que la población tenga que abandonar su regiónpara merecer de este servicio educativo; prueba de ello es el espíritu de laspolíticas educativas que se refleja en el proyecto de Decreto Estándares de Calidaden Programas Académicos de Educación Superior a Distancia de la Presidencia dela República, el cual define: “Que la Educación Superior a Distancia es aquella quese caracteriza por diseñar ambientes de aprendizaje en los cuales se hace uso demediaciones pedagógicas que permiten crear una ruptura espacio temporal en lasrelaciones inmediatas entre la institución de Educación Superior y el estudiante, elprofesor y el estudiante, y los estudiantes entre sí”.

La Educación Superior a Distancia ofrece esta cobertura y oportunidad educativaya que su modelo está pensado para satisfacer las necesidades de toda nuestrapoblación, en especial de los sectores menos favorecidos y para quienes lasoportunidades se ven disminuidas por su situación económica y social, conactividades flexibles acordes a las posibilidades de los estudiantes.

La Universidad de Pamplona gestora de la educación y promotora de llevarservicios con calidad a las diferentes regiones, y el Centro de Educación Virtual y aDistancia de la Universidad de Pamplona, presentan los siguientes materiales deapoyo con los contenidos esperados para cada programa y les saluda como parteintegral de nuestra comunidad universitaria e invita a su participación activa paratrabajar en equipo en pro del aseguramiento de la calidad de la educación superiory el fortalecimiento permanente de nuestra Universidad, para contribuircolectivamente a la construcción del país que queremos; apuntando siempre haciael cumplimiento de nuestra visión y misión como reza en el nuevo EstatutoOrgánico:

Misión: Formar profesionales integrales que sean agentes generadores decambios, promotores de la paz, la dignidad humana y el desarrollo nacional.

Visión: La Universidad de Pamplona al finalizar la primera década del siglo XXIdeberá ser el primer centro de Educación Superior del Oriente Colombiano.

Luis Armando Portilla Granados – Director CEVDUP



2

Introducción

Hoy en día la información es el bien inmaterial más importante utilizada ya sea porempresas o por personas en particular, con un fin común, el de prescindir de estainformación en el momento deseado, con un mínimo costo de recursos y en formaeficiente. Por ello, surgieron las BD (Bases de Datos, por sus siglas en español) ylos SGBD (Sistemas de Gestión de Bases de Datos, pos sus siglas en español). Lascuales son vitales en una empresa u organización, ya que almacenan lainformación concerniente a los diferentes procesos de funcionamiento críticos, ycontrolan el acceso y la manipulación de los datos en está.

Los SGBD proporcionan en primer lugar un lenguaje que permite especificar laestructura y el tipo de los datos, así como las restricciones sobre los datos. Todoesto se almacenará en la base de datos. En segundo lugar un lenguaje de manejode datos bien sea procedurales y no procedurales. En los lenguajes proceduralesse especifica qué operaciones se deben realizar para obtener los datos, mientrasque en los lenguajes no procedurales se especifica qué datos deben obtenerse sindecir cómo hacerlo. El lenguaje no procedural más utilizado es el SQL (StructuredQuery Language, por sus siglas en inglés), Lenguaje de Consulta Estructurado, quede hecho, es un estándar y es el lenguaje de los SGBDR (Sistemas de Gestión deBases de Datos, por sus siglas en español). Las especificaciones más habitualesusadas en los motores de base de datos son SQL 1992 (llamadas también SQL-92o SQL-2), el SQL 1999 (denominado SQL-99 o SQL-3) y actualmente se estadesarrollando (en complementación) la versión SQL 2003. Los SGBDR popularescomo Oracle, Sysbase, Informix o de código abierto como PostgreSQL buscanbasarse o implementar estos estándares.

En el entorno de una BD de datos las personas juegan unos papeles importantespara un buen diseño de la BD. En la presente asignatura el estudiante tendrá quejugar los siguientes roles, en primer lugar ser Administrador de la BD quien seencarga del diseño físico de la BD y de su implementación, realiza el control de laseguridad y de la concurrencia, mantiene el sistema para que siempre seencuentre operativo, y por último Diseñador de la BD quien realizan el diseñológico de la BD, debiendo identificar los datos, las relaciones entre datos y lasrestricciones sobre los datos y sus relaciones. El diseñador de la BD debe tener unprofundo conocimiento de los datos de la empresa y también debe conocer sus



3

reglas de negocio. Las reglas de negocio describen las características principalesde los datos tal y como las ve la empresa.

Estimado estudiante concientizarse sobre la importancia de la asignatura conrespecto a su perfil profesional, ya que toda empresa por pequeña que sea, estáregida por un conjunto de reglas de negocio en el mundo real y que pueden sersistematizadas en un micro mundo como son las BD.



4

Horizontes

1. Utilizar correctamente las instrucciones del SQL, y las funciones para darsolución a diversos problemas.

2. Identificar claramente la utilidad del Lenguaje de Definición de Datos, en ladescripción de los objetos que forman una base de datos.

3. Entender los conceptos de Lenguaje no prodedural SQL en los Sistemas deGestión de Base de Datos Relacional.

4. Identificar claramente la utilidad del Lenguaje de Manipulación de Datos, en elprocesamiento de los datos de la base de datos.

5. Reconocer la importancia de la asignatura con respecto al perfil profesional delestudiante.

6. Determinar las características de las consultas sumarias, en el procesamientode los datos de la base de datos.



5

UNIDAD 1: LENGUAJE DE DEFINICIÓNDE DATOS

Descripción Temática

El SQL (Structured query language, por sus siglas en inglés), lenguaje de consultaestructurado, es un lenguaje surgido de un proyecto de investigación de IBM parael acceso a bases de datos relacionales. El cual se ha convertido en un estándar delenguaje de bases de datos, y la gran mayoría de los sistemas de bases de datos losoportan, desde sistemas para computadores personales, hasta grandescomputadores.

Como su nombre indica, el SQL nos permite realizar consultas a la base de datos.Pero el nombre se queda corto ya que SQL además realiza funciones de definiciónde la base de datos.

El DDL (Data Description Language, por sus siglas en inglés), Lenguaje deDefinición de Datos, incluye órdenes para definir, modificar o borrar las tablas enlas que se almacenan los datos y de las relaciones entre estas. (Es el que másvaria de un sistema a otro)

Los SGBDR son aquellos que almacenan y administran de manera lógica los datosen forma de tablas (o relaciones). Las tablas son la estructura básica donde sealmacena la información en la base de datos. Dado que en la mayoría de los casos,no hay forma de que el proveedor de base de datos sepa con antelación cualesson sus necesidades de almacenamiento de datos, es probable que necesite creartablas en la base de datos usted mismo. Muchas herramientas de base de datos lepermiten crear tablas sin ingresar SQL, pero debido a que las tablas son loscontenedores de toda la información, es importante incluir la sintaxis SQL en estaunidad.



6

Identificar claramente la estructura de una tabla en DDL.

Conocer los fundamentos básicos de DDL, por ser éste un importantelenguaje no procedural.

Plantear las restricciones semánticas en DDL del modelo relacional.

Componentes del lenguaje SQL.

Restricciones semánticas.

Base de Datos.

Tablas.

Ejemplos.

1.1 COMPONENTES DEL LENGUAJE SQL

1.1.1 Tipos de Datos

SQL admite una variada gama de tipos de datos para el tratamiento de lainformación contenida en las tablas, los tipos de datos pueden ser numéricos (cono sin decimales), alfanuméricos, de fecha o boléanos (si o no). Según el gestor debase de datos que estemos utilizando los tipos de datos varían, pero se reducenbásicamente a los expuestos anteriormente. Dependiendo de cada gestor de

Horizontes

Núcleos Temáticos y Problemáticos

Proceso de Información



7

bases de datos el nombre que se da a cada uno de estos tipos puede variar.Básicamente tenemos los siguientes tipos de datos.

Tipos Numéricos

Tipo Definición Bytes

Integer

IntValores enteros con signo. 4

Numeric(n,m) Números reales de hasta 18 digitos (con decimales),donde n representa el total de dígitos admitidos(normalmente denominado precisión) y m el número deposiciones decimales (escala).

variable

Decimal(n,m) Igual que el tipo numeric. variable

Real Número de coma flotante, este tipo de datos se sueleutilizar para los valores en notación científica.

4

Float Número de coma flotante, este tipo de datos se sueleutilizar para los valores en notación científica.

8

Tipos Alfanuméricos


char(n) Almacena de 1 a 255 caracteres alfanúmericos. Este valorviene dado por n, y es el tamaño utilizado en disco paraalmacenar dato. Es decir si defino un campo comochar(255), el tamaño real del campo será de 255, aunqueel valor solo contenga 100.

0-255

varchar(n) Igual que el tipo char, con la salvedad que varcharalmacena únicamente los bytes que contenga el valor delcampo.

0-255

Nota: el tamaño del campo varia en función de cada base de datos, siendo 255 elvalor standart.



8

Tipos Fecha


Date Almacena sólo fechas 4

Time Almacena sólo tiempos del día 8

TimeStamp Almacena la fecha y el tiempo 8

Nota: la aparición de los tipos de datos de fecha supuso una autentica revolucióndel mundo de la bases de datos, en realidad, la base de datos almacenainternamente números enteros, de hay que el tamaño sea de 4 bytes y 8 bytes (2enteros), pero aporta la validación del dato introducido.

Tipos Boléanos


Bit Los bit almacena un 0 ó no cero, según las bases de datos será1 ó -1. Se aplica la lógica booleana, 0 es falso y no ceroverdadero.

1

Boolean Los boolean pueden tener uno de sólo dos estados: “verdadero”o “falso”. Un tercer estado, “desconocido”, se representa por elSQL el valor nulo.

Los valores literales válidos para el “verdadero” son:

't' ó 'true' ó 'y' ó 'yes' ó '1'

Los valores literales válidos para el “falso” son:

'f' ó 'false' ó 'n' ó 'not' ó '0'

1



9

1.1.2 Operadores

Los operadores se pueden definir como combinaciones de caracteres que seutilizan tanto para realizar asignaciones como comparaciones entre datos.

Los operadores se dividen en aritméticos, relacionales, lógicos y concatenación.Para esta unidad sólo destacamos los operadores relacionales y lógicos.

Operadores Lógicos

Los operadores lógicos permiten comparar expresiones lógicas devolviendosiempre un valor verdadero o falso. Los operadores lógicos se evalúan de izquierdaa derecha.

Operador Significado

AND a b a AND b

TRUE TRUE TRUE

TRUE FALSE FALSE

TRUE NULL NULL

FALSE FALSE FALSE

FALSE NULL FALSE

NULL NULL NULL

OR a b a OR b

TRUE TRUE TRUE

TRUE FALSE TRUE

TRUE NULL TRUE

FALSE FALSE FALSE

FALSE NULL NULL

NULL NULL NULL

NOT a NOT a

TRUE FALSE

FALSE TRUE

NULL NULL



10

Operadores Relacionales

Los operadores mostrados aquí están definidos para un número de tipos de datosnativos, que van desde los tipos numéricos hasta los tipos date/time.

Operador Descripción Utilización

< Menor que 1<2

<= Menor o igual que 1<=2

> Mayor que 2>1

>= Mayor o igual que 2>=1

= Igual 1=1

<> Diferente 1<>2

1.2 RESTRICCIONES SEMANTICAS1

La cláusula CONSTRAINT sirve para definir una restricción que se podráeliminar cuando queramos sin tener que borrar la columna. A cada restricción se leasigna un nombre que se utiliza para identificarla y para poder eliminarla cuandose quiera.

Como restricciones tenemos la de clave primaria (clave principal), la de índiceúnico (sin duplicados), la de valor no nulo, y la de clave foránea.

1.2.1 Clave Primaria

La restricción de clave primaria (PRIMARY KEY) permiten declarar un atributo(campo) o conjunto de atributos como la clave primaria de una relación (identificaunívocamente cada tupla (o registros) de la relación.

1 CASTAÑO, Miguel. Diseño de Base de Datos – Problemas Resueltos. AlfaOmega.2001. pp. 126 a 129



11

1.2.2 Unicidad

La restricción de unicidad (UNIQUE) nos permite definir claves alternativas (losvalores de uno o varios atributos no pueden repetirse en diferentes tuplas oregistros de una relación)

1.2.3 Obligatoriedad

La restricción de obligatoriedad (NOT NULL) permite declarar si uno o variosatributos de una relación deben tomar siempre un valor, es decir, no puede tomarvalores nulos.

1.2.4 Clave Foránea

La restricción de clave foránea (FOREIGN KEY), también denominada integridadreferencial, se utiliza para, mediante claves foráneas (conjunto de atributos en unarelación que es una clave principal en otra o la misma relación) enlazar relacionesde una base de datos. La integridad referencial nos indica que los valores de laclave foránea en la relación hijo deben corresponderse con los valores de la claveprimaria en la relación padre o bien ser nulos si se admiten nulos. Los atributosque son clave foránea en una relación no necesitan tener los mismos nombresque los atributos de la clave primaria con la cual ellos se corresponden.

Además de la integridad referencial que nos permite enlazar relaciones entre sídando lugar a la estructura de la BD, el modelo relacional permite también definirlas opciones de borrado y modificación en las claves foráneas. Estas opcionesindican las acciones que hay que llevar a cabo cuando se produce un borrado omodificación de una tupla (o registros) en la relación padre referenciada por unarelación hija. La posibilidades para una operación de actualización (borrar omodificar) son:

Borrado/modificación en cascada (CASCADE)

El borrado (o modificación) de una tupla (o registros) en la relación padreocasiona un borrado (o modificación) de todas la tuplas relacionadas en la relaciónhija (tuplas cuya clave foránea coincida con el valor de la clave primaria de la tuplaeliminada o modificada en la relación padre).



12

Borrado/modificación restringido (RESTRICT)

En este caso si existen tuplas (o registros) en la relación hija relacionadas con latupla de la relación padre sobre la que se realiza la operación, entonces no sepuede llevar a cabo dicha operación.

Borrado/modificación con puesta a nulos (SET NULL)

Esta posibilidad nos permite poner valor de la calve foránea referenciada a NULLcunado se produce el borrado o modificación de una tupla (o registros) en larelación padre.

Las opciones de borrado y modificación pueden ser distintas para una determinadaclave foránea de una relación.

1.2.5 Verificación

La restricción de verificación (CHECK), en algunos casos puede ocurrir que seanecesario especificar una condición que deben cumplir los valores de determinadosatributos de una relación de la BD aparte de las restricciones ya vistas de claveprimaria, unicidad, obligatoriedad y clave foránea. Para ello se definen lasrestricciones de verificación que, al igual que las ya estudiadas, siempre llevanimplícitas un rechazo en caso de que no se cumpla la condición especificada y quetambién se comprueban ante una inserción, borrado o modificación.

1.2.6 Disparadores

A veces puede interesar especificar una acción distinta del rechazo cuando no secumple una determinada restricción semántica. En ese caso, se recurre al uso delos disparadores (TRIGGERs) que no permiten además de indicar una acción,especificar la acción que queremos se lleve a cabo si la condición se haceverdadera. Los disparadores pueden interpretarse como reglas que especificanque cuando se produce un evento, si se cumple una condición, entonces se realizauna determinada acción. Los disparadores no están soportados en el SQL-92; sinembargo, existen productos relacionales que los soportan aunque con diferenciasen el modo de funcionamiento. Además también son convenientes para manteneralgunas restricciones del Modelo E-R que no tienen contrapartida en el modelorelacional.

Nota: el manejo de los disparadores no esta contemplado en esta asignatura.



13

1.3 BASE DE DATOS

Una base de datos es un conjunto de datos almacenados entre los que existenrelaciones lógicas y ha sido diseñada para satisfacer los requerimientos deinformación de una empresa u organización. En una base de datos, además de losdatos, también se almacena su descripción. Esta descripción es lo que sedenomina metadatos, se almacena en el diccionario de datos o catálogo y es loque permite que exista independencia de datos lógica-física.

1.3.1 Creación de Base de Datos

Descripción

CREATE DATABASECrea una nueva base de datos. El creador pasa a ser el propietario de lanueva base de datos.

Estructura

CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ LOCATION [=] 'dbpath' ] [ TEMPLATE [=] template ]

[ ENCODING [=] encoding ]

Entradas

Pueden escribirse los parámetros optativos en cualquier orden, no sólo el ordenilustró anteriormente.

nameEl nombre de un base de datos para crear.

dbownerEl nombre del usuario de la base de datos quien será el dueño de la nuevabase de datos, opor defecto usará el valor predefinido defecto (a saber, elusuario que ejecuta la orden).

templateEl nombre de la plantilla para crear la nuevo banco de datos, o por defectousará la plantilla predefinida.



14

encodingEstablece la codificación para usar en la nueva base de datos. Especifiqueun cadena constante (por ejemplo, ' SQL_ASCII'), o un entero que pone encódigo el número, o por defecto usará la codificación predefinida.

Salidas

CREATE DATABASE

Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR

Mensaje devuelto si ocurre un error e indicando el error (No tiene privilegiospara crear la BD, la BD ya existe, entre otros)

Uso

Ejemplo No. 1.1Crear la base de datos factorx

CREATE DATABASE transporte;

Ejemplo No. 1.2Crear la base de datos factorx con la plantilla template0

CREATE DATABASE transporte TEMPLATE = template0;

1.3.2 Eliminar Base de Datos

Descripción

DROP DATABASEElimina las entradas de catálogo de una base de datos existente y borra eldirectorio que contiene los datos. Solamente puede ser ejecutado por elpropietario de la base de datos (normalmente quien la creó).

Estructura

DROP DATABASE name

Entradas

name

El nombre de una base de datos existente que se desea eliminar.



15

Salidas

DROP DATABASE


ERROR

Mensaje devuelto si ocurre un error e indicando el error (No tiene privilegiospara borrar la BD, la BD ya no existe, entre otros)

Uso

Ejemplo No. 1.3Eliminar la base de datos factorx

DROP DATABASE factorx;

1.4 TABLAS

El modelo relacional se basa en el concepto matemático de relación, quegráficamente se representa mediante una tabla. Codd, que era un expertomatemático, utilizó una terminología perteneciente a las matemáticas, en concretode la teoría de conjuntos y de la lógica de predicados.

Una relación es una tabla con columnas y filas. Un SGBD sólo necesita que elusuario pueda percibir la base de datos como un conjunto de tablas. Estapercepción sólo se aplica a la estructura lógica de la base de datos. No se aplica ala estructura física de la base de datos, que se puede implementar con distintasestructuras de almacenamiento.

Un atributo es el nombre de una columna de una relación. En el modelo relacional,las relaciones se utilizan para almacenar información sobre los objetos que serepresentan en la base de datos. Una relación se representa gráficamente comouna tabla bidimensional en la que las filas corresponden a registros individuales ylas columnas corresponden a los campos o atributos de esos registros. Losatributos pueden aparecer en la relación en cualquier orden.



16

1.4.1 Creación de Tablas

Descripción

CREATE TABLEIntroducirá una nueva clase o tabla en la base de datos actual. La tabla seráposeída por el usuario que introduce la sentencia.

Diagrama

Restricción1

Restricción2



17

Estructura

CREATE [ TEMPORARY | TEMP ] TABLE table_name (Column_name data_type

[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] [, ... ] [, PRIMARY KEY ( column [, ...] ) ] [, CHECK ( condition ) ] [, table_constraint_clause ] ) [ INHERITS ( inherited_table [, ...] ) ]

y table_constraint_clause es:

[ CONSTRAINT constraint_name ]{ UNIQUE ( column_name [, ... ] ) | PRIMARY KEY ( column_name [, ... ] ) | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn[, ... ] ) ][ ON DELETE action ] [ ON UPDATE action ] }

Entradas

Pueden escribirse los parámetros optativos en cualquier orden, no sólo el ordenilustró anteriormente.

table_name

El nombre de una nueva clase o tabla a crear.

column_name

El nombre de un campo.

data_type

El tipo del campo.



18

DEFAULT value

Un valor por defecto para el campo.

column_constraint_clause

La cláusula opcional de restricciones (constraint) especifica una listade restricciones de integridad o comprueba que las nuevasinserciones o actualizaciones deben satisfacer para que la inserción ola actualización tenga éxito.

table_constraint_clause

La cláusula opcional CONSTRAINT especifica una lista derestricciones de integridad que las nuevas inserciones o lasactualizaciones deberán satisfacer para que una sentencia insert oupdate tenga éxito. Cada restricción debe ser evaluada a unaexpresión booleana. Se pueden referenciar múltiples campos con unaúnica restricción. Sólo se puede definir una única cláusula PRIMARYKEY por tabla; PRIMARY KEY column (una restricción de tabla) andPRIMARY KEY (una restricción de campo) son mutuamenteexcluyentes.

Salidas

CREATE

Mensaje devuelto si la table se ha creado con éxito.

ERROR

Mensaje devuelto si la creación de la tabla falla. Este mensaje vienenormalmente acompañado por algún texto explicativo, como: ERROR:Relation 'table' already exists que ocurre en tiempo de ejecución, si latabla especificada ya existe en la base de datos. ERROR: DEFAULT: typemismatched Si el tipo de datos o el valor por defecto no corresponde altipo de datos de la definición del campo.



19

Uso

El presente ejercicio corresponde a un fragmento sobre la Convocatoria del FactorX, cuyo programa se realiza en televisión:

CONVOCATORIA FACTOR X

El concurso Factor X desea crear un sistema de reservado para los participantes,para lo cual es necesario crear una base de datos que almacene toda lainformación de las audiciones que organiza. Cada audición se celebra en unafecha determinada y también que almacene el lugar (ciudad y sitio) de la audición.

En la audición intervienen varios participantes que pueden ser de 3 tipos: entre 16y 24 años, mayores de 24 años y grupo.

En el caso de los participantes entre 16 y 24 años, y mayores de 24 años interesaalmacenar el instrumento que toca (si toca algún instrumento), el código asignadoen la convocatoria, su nombre y apellido, su número de identificación nacional y sifue aceptado o no.

Para los grupos hay que indicar si son duos, trios, etc, el nombre del grupo.Además, interesa el nombre de cada uno de sus componentes, así, como elinstrumento que toca (si toca algún instrumento) si fue aceptado o no. En estesentido es importante tener en cuenta los participantes, entre 16 y 24 años, ymayores de 24 años. Sin embargo no todos los participantes tienen que pertenecera un grupo.

El participante se puede presentar a una o varias audiciones y en cada una de ellasse debe registrar el tiempo (duración aproximada) de su interveción.…

Nota: los tres punto (…) al final de ejercicio indica que es mas largo y sólo se tomoun fragmento de él.



20

MODELO RELACIONAL DE LA BASE DE DATOS CONVOCATORIA FACTOR X

Notación:PK PRIMARY KEY, FK FORENIGN KEY, * UNIQUE.

Recuerde los atributos derivado no forman parte del modelo relacional.

El presente Modelo se obtuvo a partir de los conocimientos obtenidos del ModeloER y Modelo Relaciona (Visto en la Asignatura Administración de base de datos I)

Ejemplo No. 1.4Obtener el script para la tabla participante

CREATE TABLE participante(pa_codigo VARCHAR(10),pa_nombre VARCHAR(20) NOT NULL,pa_aceptado BOOLEAN,PRIMARY KEY(pa_codigo)

);

NotaEl atributo pa_codigo por ser clave primaria no acepta valores nulos aunqueno se halla indicado la restricción NOT NULL.

El atributo pa_aceptado por ser de tipo lógico acepta dos valores ‘t’ ó ‘f’ y elvalor null, ya que no tiene ninguna restricción de obligatoriedad.

Ejemplo No. 1.5Obtener el script para la tabla grupo. Para crear esta relación debe habercreado la tabla participante, ya que la tabla hija grupo depende de la tablapadre participante.



21

CREATE TABLE grupo(gr_codigo VARCHAR(10),PRIMARY KEY(gr_codigo),FOREIGN KEY(gr_codigo) REFERENCES participante(pa_codigo)

ON UPDATE CASCADE ON DELETE CASCADE);

NotaEn la relación se muestra un ejemplo de integridad referencial con lasrelaciones grupo y participante.

Las claves primarias de la relación hija grupo y de la relación padreparticipante son gr_codigo y pa_codigo, respectivamente. El atributogr_codigo de la relación grupo es una clave foránea que referencia larelación participante, es decir, los valores del atributo gr_codigo debencorresponder con los valores del atributo clave primaria pa_codigo de larelación participante.

El borrado de un participante supone un borrado de un grupo.

La actualización de un participante supone una actualización de un grupo.

Ejemplo No. 1.6Obtener el script para la tabla persona. Para crear esta relación debe habercreado las tablas participante y grupo , ya que la tabla hija persona depende delas tablas padre participante y grupo.

CREATE TABLE persona(pe_codigo VARCHAR(10),pe_identificacion VARCHAR(15) NOT NULL,pe_pertenece_gr VARCHAR(20),pe_apellido VARCHAR(20) NOT NULL,pe_tipo INTEGER NOT NULL,pe_instrumento CHAR(20),PRIMARY KEY(pe_codigo),UNIQUE (pe_identificacion),FOREIGN key(pe_codigo) REFERENCES participante(pa_codigo)

on update cascade on delete cascade,FOREIGN KEY(pe_pertenece_gr) REFERENCES grupo(gr_codigo)

ON UPDATE CASCADE ON DELETE SET NULL,CHECK(pe_tipo=1 or pe_tipo=2)

);



22

NotaEl atributo pe_instrumento no tiene la restricción de obligatoriedad, ya queen el ejercicio se indica que sólo se debe registrar el instrumento que tocasiempre y cuando toque algún instrumento.

El borrado de un grupo implica que todas las personas que pertenecen a élpasarán a contener NULL en el atributo pe_pertenece_gr.

La relación persona define una restricción sobe el atributo pe_tipo queestablece que el tipo de categoría del participante puede ser 1 (participantesentre 16 y 24 años) o 2 (mayores de 24 años) o viceversa. Así, si se va hainsertar una persona con n valor diferente a 1 o 2 la operación serechazaría. Al igual si se deja nulo debido a que tiene la restricción deobligatoriedad.

La Generalización Exclusiva de que si un participante esta clasificado como grupono puede ser persona o viceversa, se lleva acabo con los TRIGGERs. Como se dijoanteriormente en esta asignatura no se tiene contemplado este tema.

1.4.2 Alterar Tablas

Descripción

ALTER TABLECambia la definición de una tabla existente. Hay varios formas:

ADD COLUMNEsta forma agrega una nueva columna a la tabla que usa la misma sintaxiscomo CREATE TABLE.

DROP COLUMNEsta forma borra una columna de una tabla. Se dejarán caerautomáticamente también los índices y restricciones de la tabla queinvolucran la columna. Usted necesitará decir CASCADE si algo fuera de latabla depende de la columna, por ejemplo, referencias de calve foránea.

ALTER COLUMN TYPEEsta forma cambia el tipo de una columna de una tabla. Se convertirán losíndices y las restricciones de la tabla simples que involucran la columnaautomáticamente para usar el nuevo tipo de la columna.



23

Diagrama

Restricción1

Restricción2



24

Estructura

ALTER TABLE [ ONLY ] name [ * ]action [, ... ]

ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_columnALTER TABLE name RENAME TO new_name

donde action es una de estas acciones:

ADD [ COLUMN ] column type [ column_constraint [ ... ] ] DROP [ COLUMN ] column [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column TYPE type [ USING expression ] ALTER [ COLUMN ] column SET DEFAULT expression ALTER [ COLUMN ] column DROP DEFAULT ALTER [ COLUMN ] column { SET | DROP } NOT NULL ALTER [ COLUMN ] column SET STATISTICS integer

Entradas

nameEl nombre (posiblemente esquema-calificado) de una tabla existente paraalterar. Si ONLY se especifica, sólo esa tabla se altera. Si ONLY no seespecifica, la tabla y todas sus tablas descendientes se actualizan.

columnEl nombre de una nueva o existente columna.

new_columnEl nuevo nombre para una columna existente.

new_nameEl nuevo nombre para la tabla.

typeEl tipo de dato de la nueva columna, o el nuevo tipo de dato para unacolumna existente.



25

Salidas

ALTER TABLE


ERROR

Mensaje devuelto si ocurre un error e indicando el error (El tipo de dato noexiste, el atributo no existe para eliminar, entre otros)

Uso

Ejemplo No. 1.7

Adicionar el atributo pe_fecha_nacimiento de tipo date a la tabla persona:

ALTER TABLE persona ADD COLUMN pe_fecha_nacimiento date;

Ejemplo No. 1.8

Adicionar una restricción de NOT NULL al atributo pe_fecha_nacimiento de latabla persona:

ALTER TABLE persona ALTER COLUMN pe_fecha_nacimiento SET NOTNULL;

Ejemplo No. 1.9

Eliminar el atributo pe_fecha_nacimiento de la tabla persona:

ALTER TABLE persona DROP COLUMN pe_fecha_ncimientoRESTRICT;

Ejemplo No. 1.10

Renombrar el atributo pe_tipo por pe_categoria:

ALTER TABLE persona RENAME COLUMN pe_tipo TO pe_categoria;

Ejemplo No. 1.11

Renombra la tabla persona por solita

ALTER TABLE persona RENAME TO solista;



26

1.4.3 Eliminar Tablas

Descripción

DROP TABLEElimina tablas de una base de datos. Solo su propietario (owner) puededestruir las tablas, tanto los datos como su estructura.

Estructura

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

Entradas

nameEl nombre de una tabla existente que se desea eliminar.

CASCADEElimina automáticamente objetos que dependen de la tabla.

RESTRICTNo deja eliminar la tabla si cualquier objeto depende de él. Éste es el valorpor defecto.

Salidas

DROP

El mensaje devuelto si el comando concluyo exitosamente.

ERROR Relation "nombre" Does Not Exist!

Si la tabla o vista especificada no existe en la base de datos.

Uso

Ejemplo No. 1.12Para destruir la tabla persona

DROP TABLE persona;



27

Ejemplo No. 1.13Para destruir dos tabla grupo y participante

DROP TABLE grupo, participante;

1.5 IMPLEMENTACIÓN EN POSTGRESQL

Ingresar al SGBDR PostgreSQL

Haga clic sobre el servidor PosgresSQL Database Server 8.0 para conectarse alservidor



28

Ingrese la contraseña (es la que usted coloco en el momento de instalar elprograma) para iniciar la conexión.

Al conectarse sobre el servidor, la base de datos template1 queda por defectoactivada y el resto de bases de datos quedan tachadas con una cruz roja.



29

Abrir el edito para la base de datos template1

Observe que en el titulo de la ventana esta el nombre de la base de datostemplate1

Crear la base de datos empresaEn el editor de SQL escribir la sentencia para crear la base de datos:

CREATE DATABASE empresa TEMPLATE = template0;

Nota : PostgreSQL utiliza una plantilla para crear las bases de datos.



30

Ejecute el script. Si se creó la base de datos en la pestaña Data Outputinforma que así fue (Query returned successfully) en otro caso no muestranada o indicara el error producido en la pestaña Messages.

Cerrar el editor de SQL de la base de datos template1. El edito preguntarasi desea almacenar el script ¿Si o No?

Refrescar el objeto Databases para que aparezca la base de datosempresa



31

Elegir la base de datos empresa haciendo clic en el nombre



32

Abrir el editor para la base de datos empresa

Observe que en el titulo de la ventana esta el nombre de la base de datosempresa

Crear la tabla empleado

En el editor de SQL escribir las sentencias para crear la tabla:

CREATE TABLE empleado(emp_nss VARCHAR(10) NOT NULL,emp_nombre VARCHAR(20) NOT NULL,emp_apellido VARCHAR(20) NOT Null,emp_fecha_nacimiento DATE,emp_direccion VARCHAR(30),emp_sexo BOOLEAN,emp_salario FLOAT,emp_supervisor VARCHAR(10),PRIMARY KEY(emp_nss),FOREIGN KEY(emp_supervisor) REFERENCES empleado (emp_nss)



33

ON UPDATE CASCADE ON DELETE SET NULL,CHECK(emp_salario>0)

);

Ejecute el script. Si se creó la tabla en la pestaña Data Output informa queasí fue (Query returned successfully) en otro caso no muestra nada o indicarael error producido en la pestaña Messages.



34

Esta tabla (o relación) tiene la finalidad de guardarinformación referente a departamentos y ciudades

Supóngase que se tiene los siguientes datos:

L_id L_nombre L_pertenece L_extensiond1 Norte de Santanderd2 Santanderd3 Bolivard4 Tolimac1_1 Pamplona d1c1_2 Bucaramanga d2c1_3 Cartagena d3c2_1 Cúcuta d1c2_2 Lebrija d2c3_1 Ocaña d1

Partiendo de esta información determine:

1. Si en el DDL se tuviera la siguiente restricción: FOREIGN KEY(l_pertenece)REFERENCES lugar(l_id). ¿Cuál es el propósito de esa restricción si seintentara insertar la ciudad Ibagué? Acompañado de los siguientes datos:

L_id L_nombre L_pertenece L_extensionc1_4 Ibagué d4

2. Si en el DDL se tuviera la siguiente restricción: FOREIGN KEY(l_pertenece)REFERENCES lugar(l_id) ON UPDATE CASCADE ON DELETE CASCADE.¿Cuál es el propósito de esa restricción si se intentara eliminar el departamentoidentificado con d1?

3. Si en el DDL se tuviera la siguiente restricción: FOREIGN KEY(l_pertenece)REFERENCES lugar(l_id) ON UPDATE CASCADE ON DELETE CASCADE.

Proceso de Comprensión y Análisis



35

¿Cuál es el propósito de esa restricción si se intentara modificar la identificacióndel departamento d1 por dpto1?

4. Si en el DDL se tuviera la siguiente restricción: FOREIGN KEY(l_pertenece)REFERENCES lugar(l_id) ON UPDATE CASCADE ON DELETE RESTRICT.¿Cuál es el propósito de esa restricción si se intentara eliminar el departamentoidentificado con d2?

5. Si en el DDL se tuviera la siguiente restricción: FOREIGN KEY(l_pertenece)REFERENCES lugar(l_id) ON UPDATE CASCADE ON DELETE SET NULL.¿Cuál es el propósito de esa restricción si se intentara eliminar el departamentoidentificado con d2?

6. Si en el DDL se tuviera la siguiente restricción: FOREIGN KEY(l_pertenece)REFERENCES lugar(l_id) ON UPDATE CASCADE ON DELETE SET NULL.¿Cuál es el propósito de esa restricción si se intentara eliminar el departamentoidentificado con d2?

7. Si en el DDL se tuviera la siguiente restricción: l_nombrevarchar(30) NOTNULL. ¿Cuál es el propósito de esa restricción si se intentara insertar undepartamento cualquiera pero no se especifica el nombre?

8. Si en el DDL se tuviera la siguiente restricción: PRIMARY KEY(l_id). ¿Cuál esel propósito de esa restricción si se intentara insertar el departamentoAmazonas? Acompañado de los siguientes datos:

L_id L_nombre L_pertenece L_extensiond3 Amazonas

9. Si en el DDL se tuviera la siguiente restricción: UNIQUE(l_nombre). ¿Cuál esel propósito de esa restricción si se intentara insertar el departamento Bolivar?Acompañado de los siguientes datos:

L_id L_nombre L_pertenece L_extensiond5 Bolivar 25.978

10.Si en el DDL se tuviera la siguiente restricción: CHECK(l_extension>=0). ¿Cuáles el propósito de esa restricción si se intentara insertar el departamentoCaldas? Acompañado de los siguientes datos:

L_id L_nombre L_pertenece L_extensiond5 Caldas -7888



36

Caso de Estudio: GESTIÓN DE TRABAJOS DE GRADO DE FIN DECARRERA.

Una Institución Universitaria quiere generar un sistema para tener controlado enuna base de datos todo lo referente a los Trabajos de Grado de Fin de Carrera(T.G.F.C.): estudiantes que los realizan, profesores que los dirigen, temas de losque tratan y tribunales que los corrigen.

Por tanto, es de interés que los estudiantes se definan por su número dematrícula, DNI y nombre. Un estudiante realiza, evidentemente, sólo un T.G.F.C.Los T.G.F.C. se definen por su tema, por un número de orden y por la fecha decomienzo. Un T.G.F.C. determinado, no puede ser realizado por varios estudiantes.

Un profesor se define por su DNI, nombre y domicilio; y puesto que los T.G.F.C.son del área en el que trabaja, NO interesa conocer el T.G.F.C. que dirige sino aqué estudiante se lo dirige. Un Tribunal está formado por varios profesores y losprofesores pueden formar parte de varios tribunales. Por otra parte, sí es deinterés para el tribunal conocer qué estudiante es el que se presenta, con quéT.G.F.C. y en qué fecha lo ha defendido. El tribunal se define por un número detribunal, lugar de examen y por el número de componentes.

Al margen de esto, un estudiante puede haber pertenecido a algún grupo deinvestigación del que haya surgido la idea del T.G.F.C. Dichos grupos se identificanpor un número de grupo, su nombre y por su número de componentes. Unestudiante no puede pertenecer a más de un grupo y no es de interés saber si elgrupo tiene algo que ver o no con el T.G.F.C. del estudiante; sí siendo de interés lafecha de incorporación a dicho grupo.

Por otra parte, un profesor, al margen de dirigir el T.G.F.C. de algunos estudiantes,puede haber colaborado con otros en la realización de dicho T.G.F.C. pero siendootro profesor el que lo dirige. En este caso, sólo es interesante conocer quéprofesor ha ayudado a qué estudiante (a un estudiante le pueden ayudar variosprofesores).

Analice el contexto de la base de datos relacional “Gestión de Trabajos deGrado de Fin de Carrera”

Obtener el Diseño Lógico de la base de datos relacional “Gestión de Trabajosde Grado de Fin de Carrera”

Solución de Problemas



37

Escriba instrucciones apropiadas en el DDL de SQL para declarar el modelorelacional “Gestión de Trabajos de Grado de Fin de Carrera”

MODELO ENTIDAD RELACION DE UN FRAGMENTO DE LA BASE DE DATODE ALQUILER DE VEHICULOS

Obtener el DDL de SQL para declarar el modelo relacional expuesto en elmodelo ER.

Argumentar las restricciones semánticas (Supuestos semánticoscomplementarios y Semánticos no reflejados)

Síntesis Creativa y Argumentativa



38

Exponga otro caso de Modelo ER para aplicar el DDL.

MODELO RELACIONAL DE LA BASE DE DATOS BIBLIOTECA

Selección múltiple con única respuesta

1. Orden que permite crear la tabla usuarioA. Create table B. Creat tableC. New table D. Creater table

2. Orden que permite eliminar la tabla escribeA. Erase table B. Delete tableC. Drop table D. Free table

3. Orden que permite crear la base de datos bibliotecaA. Creat database B. Create databaseC. New basedata D. Creater basedata

4. Orden que permite eliminar la base de datos bibliotecaA. Erase basedata B. Delete databaseC. Drop database D. Free basedata

Complete

5. ¿Qué instrucción se debe incluir en la tabla presta para indicar que los atributoseje_id y lib_contiene_eje son una llave foránea?

Autoevaluación



39

6. ¿Qué instrucción que se debe incluir en la tabla ejemplar para indicar que elatributo eje_disponible es de tipo lógico ( ‘t’ o ‘f’ donde ‘t’ indica que estádisponible y ‘f’ que no esta disponible) y no recibe valores nulos?

7. ¿Qué instrucción se debe incluir en la tabla libro para indicar que el atributolib_id es la llave principal?

8. ¿Qué instrucción que se debe incluir en la tabla usuario para indicar que elatributo usu_fecha_nacimiento es de tipo fecha y recibe valores nulos?

9. ¿Qué instrucción que se debe incluir en la tabla libro para indicar que el atributolib_nombre es único?

10. Las relaciones del modelo de la biblioteca se quieren almacenar en una basede datos llamada biblioteca. ¿Cuál sería la orden para crear la base de datos?

Lenguaje de Definición de Datos, incluye órdenes para definir, modificar o borrarlas tablas en las que se almacenan los datos y de las relaciones entre estas.

Los SGBDR son aquellos que almacenan y administran de manera lógica los datosen forma de tablas. Las tablas son la estructura básica donde se almacena lainformación en la base de datos. Dado que en la mayoría de los casos, no hayforma de que el proveedor de base de datos sepa con antelación cuales son susnecesidades de almacenamiento de datos, es probable que necesite crear tablasen la base de datos usted mismo.

SQL admite una variada gama de tipos de datos para el tratamiento de lainformación contenida en las tablas, los tipos de datos pueden ser numéricos (cono sin decimales), alfanuméricos, de fecha o boléanos (si o no). Según el gestor debase de datos que estemos utilizando los tipos de datos varían, pero se reducenbásicamente a los expuestos anteriormente

Los operadores aritméticos, relacionales, lógicos y concatenación se puedendefinir como combinaciones de caracteres que se utilizan tanto para realizarasignaciones como comparaciones entre datos.

Repaso Significativo



40

El modelo relacional proporciona mecanismos para recoger restriccionessemánticas o de usuario. Como restricciones tenemos la de clave primaria(PRIMARY KEY), la de índice único (UNIQUE), la de valor no nulo (NOT NULL), lade verificación (CHECK) y la de clave foránea (FOREIGN KEY).

Una base de datos es un conjunto de datos almacenados entre los que existenrelaciones lógicas y ha sido diseñada para satisfacer los requerimientos deinformación de una empresa u organización. En una base de datos, además de losdatos, también se almacena su descripción.

CREATE DATABASE crea una nueva base de datos. El creador pasa a ser elpropietario de la nueva base de datos.

DROP DATABASE elimina las entradas de catálogo de una base de datos existentey borra el directorio que contiene los datos. Solamente puede ser ejecutado por elpropietario de la base de datos.

CREATE TABLE introducirá una nueva clase o tabla en la base de datos actual. Latabla será poseída por el usuario que introduce la sentencia.

ALTER TABLE cambia la definición de una tabla existente.

DROP TABLE elimina tablas de una base de datos. Solo su propietario (owner)puede destruir las tablas, tanto los datos como su estructura.

GROFF, James R. , WEINBERG, Paul N. Aplique SQL. McGrawHill. 1991.

ELMASRI / NAVATHE. Sistemas de Bases de Datos. Conceptos Fundamentales.Pearson Educación. 2000.

CASTAÑO, Miguel. Diseño de Base de Datos – Problemas Resueltos. AlfaOmega.2001.

Bibliografía Sugerida



41

UNIDAD 2: LENGUAJE DE DEFINICIONDE DATOS I


Los lenguajes orientados a transformaciones son lenguajes no procedurales queutilizan relaciones para transformar los datos de entrada en la salida deseada.Estos lenguajes tienen estructuras que son fáciles de utilizar y que permitenexpresar lo que se desea en términos de lo que se conoce. Uno de estos lenguajeses SQL, el cual está compuesto por comandos, cláusulas, operadores y funcionesde agregado. Estos elementos se combinan en las instrucciones para crear,actualizar y manipular las bases de datos.

El SQL (que se pronuncia deletreando en inglés las letras que lo componen, esdecir "ese-cu-ele" y no "siquel" como se oye a menudo) empieza en 1974 con ladefinición, por parte de Donald Chamberlin y de otras personas que trabajaban enlos laboratorios de investigación de IBM, de un lenguaje para la especificación delas características de las bases de datos que adoptaban el modelo relacional. Apartir de 1981, IBM comenzó a entregar sus productos relacionales y en 1983empezó a vender DB2. En el curso de los años ochenta, numerosas compañías(por ejemplo Oracle y Sybase, sólo por citar algunos) comercializaron productosbasados en SQL, que se convierte en el estándar industrial de hecho por lo querespecta a las bases de datos relacionales.

Actualmente, está en marcha un proceso de revisión del lenguaje por parte de loscomités ANSI e ISO, que debería terminar en la definición de lo que en estemomento se conoce como SQL3. Las características principales de esta nuevaencarnación de SQL deberían ser su transformación en un lenguaje stand-alone(mientras ahora se usa como lenguaje hospedado en otros lenguajes) y laintroducción de nuevos tipos de datos más complejos que permitan, por ejemplo,el tratamiento de datos multimediales.

El DML (Data Manipulation Language, por sus siglas e inglés), lenguaje demanipulación de datos, nos permite recuperar los datos almacenados en la base dedatos y también incluye órdenes para permitir al usuario actualizar la base dedatos añadiendo nuevos datos, suprimiendo datos antiguos o modificando datospreviamente almacenados.



42

Todas las sentencias empiezan con un verbo (palabra reservada que indica laacción a realizar), seguido del resto de cláusulas, algunas obligatorias y otrasopcionales que completan la frase. Todas las sentencias siguen una sintaxispara que se puedan ejecutar correctamente.

Identificar claramente el diagrama sintáctico de las consultas de selección yde acción.

Conocer los fundamentos básicos de DML, por ser éste un importantelenguaje no procedural.

Determinar las características de las consultas de selección para procesarlos datos almacenados en la base de datos.

Diferenciar la conceptualización de consultas de selección y de acción.

Componentes del lenguaje SQL.

Sentencias de Acción.

Sentencias de Seleccción.

Ejemplos.

2.1 COMPONENTES DEL LENGUAJE SQL

2.1.1 Características del Lenguaje SQL

Una sentencia SQL es como una frase (escrita en inglés) para manipular labase de datos con la que decimos lo que queremos obtener y de dondeobtenerlo.

Horizontes





43

Es no procedimental. Usted especifica la información requerida, mas no lasoperaciones o la forma de llegar a la información. Cada SGBDR tiene unoptimizador de búsquedas, el cual traduce sus cláusulas en SQL y elabora elcamino o la ruta óptima para llegar a los datos.

Cuando se trabaja con los datos, todas las filas afectadas por los comandos setratan como un solo bloque, no son tratadas una por una.

Adicionalmente, SQL permite que los resultados de una consulta sean los datosde entrada para una nueva

2.1.2 Operadores

A lo largo de este capítulo se estudiarán las posibilidades de filtrar los registros (otuplas) con el fin de recuperar solamente aquellos que cumplan unas condicionespreestablecidas.

Operadores LógicosYa están descritos en la unidad 1. Lenguaje de Definición de datos.

Operador Descripción

AND Es el "y" lógico. Evalúa dos condiciones y devuelve un valor deverdad sólo si ambas son ciertas.

OR Es el "o" lógico. Evalúa dos condiciones y devuelve un valor deverdad si alguna de las dos es cierta.

NOT Negación lógica. Devuelve el valor contrario de la expresión.

Operadores Relacionales

Ya están descritos en la unidad 1. Lenguaje de Definición de datos.

Operador Descripción< Menor que

> Mayor que<> Distinto de

<= Menor ó Igual que

>= Mayor ó Igual que= Igual que



44

Operadores Aritméticos

Operador Descripción+ Suma

- Resta

* Producto

/ División

** ^ Exponenciación

2.1.3 Cláusulas

Las cláusulas son condiciones de modificación utilizadas para definir los datos quedesea seleccionar o manipular.

Cláusula Descripción

FROM Utilizada para especificar la tabla de la cual se van aseleccionar los registros

WHERE Utilizada para especificar las condiciones que deben reunir losregistros que se van a seleccionar

ORDER BY Utilizada para ordenar los registros seleccionados de acuerdocon un orden específico

2.1.4 Predicados

Los predicados son condiciones que se indican en claúsula WHERE de una consultaSQL.

Operador DescripciónBETWEEN...AND Comprueba que al valor esta dentro de un intervalo

LIKE Compara un campo con una cadena alfanumérica. LIKEadmite el uso de caracteres comodines

ALL Señala a todos los elementos de la selección de la consulta

ANY Indica que la condición se cumplirá si la comparación es ciertapara al menos un elemento del conjunto.



45

Operador DescripciónEXISTS Devuelve un valor verdadero si el resultado de una

subconsulta devuelve resultados.

IN Comprueba si un campo se encuentra dentro de undeterminado rango. El rango puede ser una sentenciaSELECT.

2.2 CONSULTAS DE ACCION

2.2.1 Insertar

Descripción

INSERT INTOPermite la inserción de nuevas filas en una clase o una tabla. Se puedeinsertar una fila a la vez o varias como el resultado de una consulta. Lascolumnas en el resultado pueden ser listadas en cualquier orden.

Diagrama

Estructura

INSERT INTO table [ ( column [, ...] ) ] { VALUES ( expression [, ...] ) | SELECT query }

Entrada

tableEl nombre de una tabla existente.

columnEl nombre de una columna en table.

expressionUna expresión o un valor válidos a asignar en column.

queryUna consulta válida. Vea la instrucción SELECT para una mejor descripciónde argumentos válidos.



46

Salida

INSERT oid 1Mensaje devuelto si solo se ha insertado una fila. oid es el número OID dela fila insertada.

INSERT 0 #Mensaje devuelto si se ha insertado más de una fila. # es el número de filasinsertadas.

Uso

Los siguientes ejemplos están basados en el modelo relacional de la base dedatos convocatoria factor x. Ejercicio expuesto en la unidad 1 en la cual seobtuvo el DDL de las tablas participante, grupo, y persona.

Ejemplo No. 2.1Insertar una tupla (o resgistro) en la tabla participante

INSERT INTO participante VALUES(‘1’,’Juan Manuel’,’false’);

NotaEl tipo de datos del atributo pa_aceptado es boolean por lo tanto recibe ‘t’(´true’) 0 ‘f’ (´´false´) y por no tener la restricción de NOT NULL recibetambién NULL.

Ejemplo No. 2.2En este segundo ejemplo la columna pa_aceptado se omite y entonces tendráel valor por defecto de NULL:

INSERT INTO participante (pa_codigo,pa_nombre) VALUES(‘g1’,’Trigal’);

Pa_codigo Pa_nombre Pa_aceptado1 Juan Manuel falseg1 Trigal



47

2.2.2 Modificar

Descripción

UPDATECambia el valor de la columnas especificadas por todas las filas quesatisfacen la condición dada. Solamente necesita indicar las columnas queserán modificadas.

Para referencias a listas se usa la misma sintaxis de SELECT. O sea, puedesustituir un único elemento de una lista, un rango de elementos o una listacompleta con una única petición.

Debe tener permiso de escribir en la tabla para poder modificarla, así comopermiso de lectura de cualquier tabla cuyos valores sean mencionados en lacondición WHERE.

Diagrama

Estructura

UPDATE tabla SET columna = expresión [, ...] [ WHERE condición ]

Entrada


ColumnaEl nombre de la columna en tabla.

ExpresiónUna expresión válida o valor a ser asignado a la columna.



48

CondiciónConsulte la cláusula SELECT para un descripción más extensa de la cláusulaWHERE.

Salida

UPDATE #Mensaje obtenido si ha habido éxito. El símbolo # representa el número defilas que han sido actualizadas. Si # es igual a 0, ninguna fila fueactualizada.

Uso


Tabla Participante

Pa_codigo Pa_nombre Pa_aceptado1 Juan Manuel false2 Luis Antonio3 Maria José true4 José Mario5 Luis Fernando false6 Martha Lucia true7 Gloria Inelsa trueg1 Trigalg2 Trigo Limpiog3 Las Indomables true

Ejemplo No. 2.3Para cambiar la palabra "false" por "true" en la columna pa_aceptado de latabla participante

UPDATE participante SET pa_aceptado = 'false'WHERE pa_aceptado = 'true';



49

Pa_codigo Pa_nombre Pa_aceptado1 Juan Manuel true2 Luis Antonio3 Maria José true4 José Mario5 Luis Fernando true6 Martha Lucia true7 Gloria Inelsa trueg1 Trigalg2 Trigo Limpiog3 Las Indomables true

NotaEl tipo de datos del atributo pa_aceptado es boolean por lo tanto recibe ‘t’(‘true’) 0 ‘f’ (´false´) y por no tener la restricción de NOT NULL recibetambién NULL.

Ejemplo No. 2.4Para cambiar el nombre del participante con código 2 por el siguiente nombre“Anthony”

UPDATE participante SET pa_nombre = 'Anthony'WHERE pa_codigo = '2';

Pa_codigo Pa_nombre Pa_aceptado1 Juan Manuel true2 Luis Antonio3 Maria José true4 José Mario5 Luis Fernando true6 Martha Lucia true7 Gloria Inelsa trueg1 Trigalg2 Trigo Limpiog3 Las Indomables true

NotaSi el atributo pa_codigo es de tipo numérico no necesita colocar el códigoentre comillas.



50

2.2.3 Eliminar

Descripción

DELETEBorra las filas que satisfacen la cláusula WHERE de la tabla especificada.

Si la condición (cláusula WHERE) esta ausente, el efecto es borrar todas lasfilas de la tabla. El resultado es una tabla valida, pero vacía.

Para modificar la tabla usted debe poseer acceso de escritura a la misma,así como acceso de lectura a cualquier tabla cuyos valores son leídos en lacondición.

Diagrama

La opción tabla.* se utiliza cuando el origen está basado en variastablas, y sirve para indicar en qué tabla vamos a borrar.

La opción * es opcional y es la que se asume por defecto y se puede ponerúnicamente cuando el origen es una sola tabla.

Nota: las entradas tabla.* o * hay motores de bases de datos que no lasoportan como PostgreSQL.

Estructura

DELETE FROM table [ WHERE condition ]

Entrada


conditionEsta es una consulta SQL de selección la cual devuelve las filas a serborradas.



51

Referirse al comando SELECT para una mayor descripción de la cláusulaWHERE.

Salida

DELETE countMensaje devuelto si los items son borrados exitosamente. El valor count esla cantidad de filas borradas.Si count es 0, ninguna fila fue borrada.

Uso


Tabla Participante

Pa_codigo Pa_nombre Pa_aceptado1 Juan Manuel false2 Luis Antonio3 Maria José true4 José Mario5 Luis Fernando False6 Martha Lucia Trae7 Gloria Inelsa Traeg1 Trigalg2 Trigo Limpiog3 Las Indomables Trae

NotaTodos los que tienen el valor de true en el atributo pa_aceptado son losparticipantes aceptados



52

Ejemplo No. 2.5Eliminar todos los participantes excepto los aceptados

DELETE FROM participante WHERE pa_aceptado <> ‘true’;

Pa_codigo Pa_nombre Pa_aceptado3 Maria José Trae6 Martha Lucia Trae7 Gloria Inelsa Traeg3 Las Indomables Trae

Ejemplo No. 2.6Borra completamente el contenido de la tabla participante

DELETE FROM participante;

2.3 CONSULTAS DE SELECCIÓN - SIMPLES

Se utilizan para indicar al motor de datos que devuelva información de las basesde datos, esta información es devuelta en forma de conjunto de registros. Ésteconjunto de registros forma una tabla lógica, porque no se guarda en el disco sinoque está en memoria y cada vez que ejecutamos la consulta se vuelve a calcular.

2.3.1 Select

Descripción

SELECTDevuelve registros de una o más tablas. Los candidatos a ser seleccionadosson aquellos registros que cumplen la condición especificada con WHERE; sise omite WHERE, se retornan todos los registros.



53

Diagrama

Parte Significado

SELECT Palabra clave que indica que la sentencia de SQL que queremosejecutar es de selección.

ALL Indica que queremos seleccionar todos los valores.Es el valorpor defecto y no suele especificarse casi nunca.

DISTINCT Indica que queremos seleccionar sólo los valores distintos.

FROM

Indica la tabla (o tablas) desde la que queremos recuperar losdatos. En el caso de que exista más de una tabla se denominaa la consulta "consulta combinada" o "join". En las consultascombinadas es necesario aplicar una condición de combinacióna través de una cláusula WHERE.

WHEREEspecifica una condición que debe cumplirse para que los datossean devueltos por la consulta. Admite los operadores lógicosAND y OR.

GROUP BY Especifica la agrupación que se da a los datos. Se usa siempreen combinación con funciones agregadas.

HAVING

Especifica una condición que debe cumplirse para que los datossean devueltos por la consulta. Su funcionamiento es similar alde WHERE pero aplicado al conjunto de resultados devueltospor la consulta. Debe aplicarse siempre junto a GROUP BY y lacondición debe estar referida a los campos contenidos en ella.

ORDER BYPresenta el resultado ordenado por las columnas indicadas. Elorden puede expresarse con ASC (orden ascendente) y DESC(orden descendente). El valor predeterminado es ASC.



54

Estructura

SELECT [ ALL | DISTINCT ]expression [ AS name ] [, ...]

[ FROM table [ alias ] [, ...] ] [ WHERE condition ] [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ] [ ORDER BY column [ ASC | DESC ] [, ...] ] LIMIT { count | ALL } [ { OFFSET | , } start ]

Entrada

expressionEl nombre de una columna de la tabla o una expresión.

name

Especifica otro nombre para una columna o una expresión que utilice lacláusula AS. Este nombre se utiliza principalmente como etiqueta para lacolumna de salida. El nombre puede ser referenciado en cláusulas ORDERBY.

table

El nombre de una tabla existente a la que se refiere la cláusula FROM.

alias

Un nombre alternativo para la tabla precedente table. Se utiliza paraabreviar o eliminar ambigüedades en uniones dentro de una misma tabla.

condition

Una expresión booleana que da como resultado verdadero o falso (true orfalse). Consulte la cláusula WHERE.

column

El nombre de una columna de la tabla.

select

Una declaración de selección (select) exceptuando la cláusula ORDER BY.



55

Salida

Registros

El conjunto completo de registros (filas) que resultan de la especificación dela consulta.

count

La cantidad de registros (filas) devueltos por la consulta.

2.3.2 Consultas Simples con la Cláusula FROM

Estructura

La estructura básica de una consulta de selección es la siguiente:

SELECT Columnas FROM Tabla;

En donde columna es la lista de campos que se deseen recuperar y tabla es elorigen de los mismos.

Selección de Columnas

La lista de columnas que queremos que aparezcan en el resultado es loque llamamos lista de selección y se especifica delante de la cláusula FROM.

Asterisco (*)

Se utiliza el asterisco * en la lista de selección para indicar 'todas lascolumnas de la tabla'.

Se puede combinar el * con el nombre de una tabla (ej. oficinas.*), peroesto se utiliza más cuando el origen de la consulta son dos tablas.



56

Columnas de la tablaLas columnas se pueden especificar mediante su nombre simple (nbcol) osu nombre cualificado (nbtabla.nbcol, el nombre de la columna precedidodel nombre de la tabla que contiene la columna y separados por un punto).

Cláusula FROM

Aliastabla

Es un nombre de alias, es como un segundo nombre que asignamos a latabla, si en una consulta definimos un alias para la tabla, esta se deberánombrar utilizando ese nombre y no su nombre real, además ese nombresólo es válido en la consulta donde se define. La palabra AS que sepuede poner delante del nombre de alias es opcional y es el valor pordefecto por lo que no tienen ningún efecto.

Uso

Las siguientes consultas son basadas en el presente ejercicio. El cual es unfragmento del modelo relacional de transporte.…La empresa de transporte tiene varias sucursales en el país, distribuidasgeográficamente en departamentos los cuales están conformados por municipios.

En cada departamento se indica la identificación, el nombre (son únicos para todoel país), y la extensión.

De cada municipio se indica la identificación y el nombre, los cuales son únicos pordepartamento pero puede existir el mismo nombre de municipio en otrodepartamento.

En la empresa existe una colección de rutas. En cada ruta se indica laidentificación, la ciudad donde sale, la ciudad donde llega, y el número de horasque dura el trayecto....Nota: los tres punto (…) al comienzo y final de ejercicio indica que es mas largo ysólo se tomo un fragmento de él.



57

MODELO RELACIONAL DE LA BASE DE DATOS TRANSPORTE

Notación:PK PRIMARY KEY, FK FORENIGN KEY, * UNIQUE.

DDL DE LA BASE DE DATOS TRANSPORTE

create table departamento(dep_id varchar(10),dep_nombre varchar(30) not null,dep_extension float,primary key(dep_id),unique (dep_nombre),check(dep_extension>=0)

);

create table municipio(mun_id varchar(10),mun_nombrevarchar(30) not null,dep_id varchar(10),primary key(mun_id),unique (mun_nombre,dep_id),foreign key(dep_id) references departamento(dep_id)

on update cascade on delete cascade);



58

create table ruta(rut_numero varchar(10),rut_salida varchar(10) not null,rut_llegada varchar(10) not null,rut_numero_horas decimal(4,2),primary key(rut_numero),unique (rut_salida,rut_llegada),foreign key(rut_salida) references municipio(mun_id)

on update cascade on delete cascade,foreign key(rut_llegada) references municipio(mun_id)

on update cascade on delete cascade,check(rut_salida<>rut_llegada)

);

DATOS DE LA BASE DE DATOS TRANSPORTE

Tabla departamento

Dep_id Dep_nombre Dep_extensiond1 Norte de Santander 21658d2 Santander 30537d3 Bolivard4 Tolima 23562d5 Antioquia

Tabla municipio

Mun_id Mun_nombre Dep_idc1_1 Pamplona d1c2_1 Cúcuta d1c3_1 Ocaña d1c1_2 Bucaramanga d2c2_2 Lebrija d2c3_2 Barbosa d2c4_2 Barrancabermeja d2c1_3 Cartagena d3c2_3 Magangué d3c1_5 Barbosa d5



59

Tabla ruta

Rut_id Rut_salida Rut_llegada Rut_numero_horasr1_1 C1_1 c2_1 1.75r2_1 C1_1 c1_2 3.00r1_2 C2_1 c1_2 5.00r2_2 C2_1 c1_1 2.00r3_2 C2_1 c4_2 7.00r4_2 C2_1 c1_5 9.00r1_3 C1_2 c4_2 2.00r2_3 C1_2 c1_1 2.80

Consulta No. 2.1Listar todos los datos de los departamentos

SELECT * FROM departamento;

Ó

SELECT departamento.* FROM departamento;

Dep_id Dep_nombre Dep_extensiond1 Norte de Santander 21658d2 Santander 30537d3 Bolivard4 Tolima 23562d5 Antioquia

Consulta No. 2.2Obtener el nombre y la extensión de los departamentos.

SELECT dep_nombre,dep_extension FROM departamento;

Dep_nombre Dep_extensionNorte de Santander 21658Santander 30537BolivarTolima 23562Antioquia



60

Consulta No. 2.3

Obtener el nombre y la extensión de los departamentos pero incrementando laextensión en un 2%.

SELECT dep_nombre,dep_extension*1.02 FROM departamento;

Dep_nombre Dep_extensionNorte de Santander 22091.16Santander 31147.74BolivarTolima 24033.24Antioquia

2.3.3 Consultas Simples con la Cláusula ORDER BY

Estructura


SELECT Columnas FROM Tabla ORDER BY nbcolumnas;

En donde columna es la lista de campos que se deseen recuperar, tabla es elorigen de los mismos y nbcolumnas son los campos por los que ordena losregistros.

Cláusula ORDER BY

Para ordenar las filas del resultado de la consulta, tenemos la cláusula ORDERBY.

Con esta cláusula se altera el orden de visualización de las filas de la tablapero en ningún caso se modifica el orden de las filas dentro de la tabla. Latabla no se modifica.

Podemos indicar la columna por la que queremos ordenar utilizando sunombre de columna (nbcolumna) o utilizando su número de orden que ocupaen la lista de selección (Nºcolumna).



61

Por defecto el orden será ascendente (ASC)

Uso

Consulta No. 2.4

Obtener un listado de todos los departamentos ordenados alfabéticamente pornombre

SELECT * FROM departamentoORDER BY dep_nombre;

Ó

SELECT dep_id,dep_nombre,dep_extension FROM departamentoORDER BY dep_nombre;

Ó

SELECT dep_id,dep_nombre,dep_extension FROM departamentoORDER BY 2;

Dep_id Dep_nombre Dep_extensiond5 Antioquiad3 Bolivard1 Norte de Santander 21658d2 Santander 30537d4 Tolima 23562

Consulta No. 2.5Obtener un listado de los nombres de los municipios junto con el código deldepartamento al que pertenece el municipio, ordenar primero por nombre demunicipio alfabéticamente y luego por código de departamentoascendentemente.

SELECT mun_nombre,dep_id FROM municipioORDER BY mun_nombre, dep_id;



62

Mun_nombre Dep_idBarbosa d2Barbosa d5Barrancabermeja d2Bucaramanga d2Cartagena d3Cúcuta d1Lebrija d2Magangué d3Ocaña d1Pamplona d1

Consulta No. 2.6Obtener un listado de los nombres de los municipios junto con el código deldepartamento al que pertenece el municipio, ordenar primero por nombre demunicipio alfabéticamente y luego por código de departamentodescendentemente.

SELECT mun_nombre,dep_id FROM municipioORDER BY mun_nombre , dep_id DESC;

Mun_nombre Dep_idBarbosa D5Barbosa D2Barrancabermeja d2Bucaramanga d2Cartagena d3Cúcuta d1Lebrija d2Magangué d3Ocaña d1Pamplona d1

Consulta No. 2.7Obtener un listado de los nombres de los municipios junto con el código deldepartamento al que pertenece el municipio, ordenar primero por nombre demunicipio descendentemente y luego por código de departamentodescendentemente.



63

SELECT mun_nombre,dep_id FROM municipioORDER BY mun_nombre DESC, dep_id DESC;

Mun_nombre Dep_idPamplona d1Ocaña d1Magangué d3Lebrija d2Cúcuta d1Cartagena d3Bucaramanga d2Barrancabermeja d2Barbosa d5Barbosa d2

2.3.4 Consultas Simples con el Predicado DISTINCT/ALL

Estructura


SELECT DISTINCT/ALL Columnas FROM Tabla ORDER BYnbcolumnas;


Predicado DISTINCT

Al incluir el predicado DISTINCT en la SELECT, se eliminan del resultado lasrepeticiones de filas. Si por el contrario queremos que aparezcan todas las filasincluidas las duplicadas, podemos incluir la cláusula ALL o nada, ya que ALL esel valor que SQL asume por defecto.

Uso

Consulta No. 2.8

Obtener el nombre de todas las ciudades



64

SELECT mun_nombre FROM municipio;

Observe que el nombre del municipio Barbosa aparece dos veces.

Mun_nombrePamplonaCúcutaOcañaBucaramangaLebrijaBarbosaBarrancabermejaCartagenaMaganguéBarbosa

Si se quiere que no aparezcan registros repetidos se debe utilizar elpredicado DISTINCT

SELECT DISTINCT mun_nombre FROM municipio;

Mun_nombreBarbosaBarrancabermejaBucaramangaCartagenaCúcutaLebrijaMagangueOcañaPamplona

2.3.5 Consultas Simples con la Cláusula WHERE

Estructura


SELECT DISTINCT/ALL Columnas FROM TablaWHERE Condición ORDER BY nbcolumnas;



65


La cláusula WHERE selecciona únicamente las filas que cumplan la condición deselección especificada.

Cláusula WHERE

En la consulta sólo aparecerán las filas para las cuales la condición esverdadera (TRUE), los valores nulos (NULL) no se incluyen por lo tanto en lasfilas del resultado. La condición-de-selección puede ser cualquier condiciónválida o combinación de condiciones utilizando los operadores NOT (no) AND(y) y OR (ó).

Uso

Consulta No. 2.9Listar los nombres de los municipios del departamento d2.

SELECT mun_nombre FROM municipio WHERE dep_id='d2';

Mun_nombreBucaramangaLebrijaBarbosaBarrancabermeja

Consulta No. 2.10

Listar la información de todos los departamentos cuya extensión estacomprendida entre 20000 y 25000.

SELECT * FROM departamentoWHERE dep_extension >= 20000 AND dep_extension <= 25000;



66

Dep_id Dep_nombre Dep_extensiond1 Norte de Santander 21658d4 Tolima 23562

Consulta No. 2.11

Listar los nombres de los municipios del departamento d2 y d3.

SELECT mun_nombre FROM municipioWHERE dep_id = 'd2' OR dep_id = ’d3’;

Mun_nombreBucaramangaLebrijaBarbosaBarrancabermejaCartagenaMagangué

El Predicado BETWEEN dentro de la Cláusula WHERE

Examina si el valor de la expresión está comprendido entre los dos valoresdefinidos por exp1 y exp2.

Consulta No. 2.12

Resolver la consulta 10, utilizando el predicado BETWEEN.

SELECT * FROM departamento WHERE dep_extensionBETWEEN 20000 AND 25000;

Dep_id Dep_nombre Dep_extensiond1 Norte de Santander 21658d4 Tolima 23562



67

El Predicado IN dentro de la Cláusula WHERE

Examina si el valor de la expresión es uno de los valores incluidos en la lista devalores.

Consulta No. 2.13

Resolver la consulta 11, utilizando el predicado IN.

SELECT mun_nombre FROM municipio WHERE dep_id IN ('d2','d3');

Mun_nombreBucaramangaLebrijaBarbosaBarrancabermejaCartagenaMagangué

Consulta No. 2.14

Listar los nombres de los municipios que no están en los departamentos d2 yd3.

SELECT mun_nombre FROM municipio WHERE dep_id NOT IN ('d2','d3');

Mun_nombrePamplonaCúcutaOcañaBarbosa



68

El Predicado LIKE dentro de la Cláusula WHERE

Se utiliza cuando queremos utilizar caracteres comodines para formar el valorcon el comparar.

Los comodines más usados son los siguientes:

_ representa un carácter cualquiera

% representa cero o más caracteres

Consulta No. 2.15

Lista los municipios cuyo nombre empiece por C (C seguido de cero o máscaracteres).

SELECT * FROM municipio WHERE mun_nombre LIKE 'C%';

Mun_nombreCartagenaCúcuta

Consulta No. 2.16

Lista los departamentos cuyo nombre contiene Santander

SELECT * FROM departamento WHERE dep_nombre LIKE'%Santander%';

Dep_id Dep_nombre Dep_extensiond1 Norte de Santander 21658d2 Santander 30537

Consulta No. 2.17

Lista los municipios cuyo nombre contiene una c como tercera letra.

SELECT * FROM municipio WHERE mun_nombre LIKE '__c%';



69

Mun_id Mun_nombre Dep_idc2_1 Cúcuta d1c1_2 Bucaramanga d2

El Predicado IS NULL dentro de la Cláusula WHERE

Examina si el valor de la columna es nulo o no.

Consulta No. 2.18

Lista los departamentos que no tienen definido la extensión.SELECT * FROM departamento WHERE dep_extension IS NULL;

Dep_id Dep_nombre Dep_extensiond3 Bolivard5 Antioquia

2.4 CONSULTAS DE SELECCIÓN - MULTITABLAS

La composición de tablas consiste en concatenar filas de una tabla con filas deotra. En este caso obtenemos una tabla con las columnas de la primera tablaunidas a las columnas de la segunda tabla, y las filas de la tabla resultante sonconcatenaciones de filas de la primera tabla con filas de la segunda tabla.

Ejemplo No. 2.7Listar los municipios con el nombre del departamento al que pertenece, pues losdatos del municipio los tenemos en la tabla de Municipio pero el nombre deldepartamento está en la tabla departamento y además queremos queaparezcan en la misma línea; en este caso necesitamos reunir las dos tablas.



70

2.4.1 Producto Cartesiano

Descripción

El producto cartesiano es un tipo de composición de tablas, aplicando elproducto cartesiano a dos tablas se obtiene una tabla con las columnas de laprimera tabla unidas a las columnas de la segunda tabla, y las filas de la tablaresultante son todas las posibles concatenaciones de filas de la primera tablacon filas de la segunda tabla.

Diagrama

El producto cartesiano se indica poniendo en la FROM las tablas que queremoscomponer separadas por comas, podemos obtener así el producto cartesianode dos, o más tablas.

nbtablapuede ser un nombre de tabla o un nombre de consulta.

Hay que tener en cuenta que el producto cartesiano obtiene todas las posiblescombinaciones de filas por lo tanto si tenemos una tabla con 100 registros, otratabla con 50 registros el resultado tendrá 100x50 filas, si el producto lohacemos de estas dos tablas con una tercera de 20 filas, el resultado tendrá



71

10.0000 filas (100x50x20) y estamos hablando de tablas pequeñas. Se veclaramente que el producto cartesiano es una operación costosa sobre todo sioperamos con más de dos tablas o con tablas voluminosas.

Uso

Ejemplo No. 2.8Producto Cartesiano entre las tablas municipio y departamento del ejemploNo. 7

2.4.2 Composición Interna – Producto cartesiano más Restricción

DescripciónEs un producto cartesiano seguido de una restricción por igualdad.

A partir de las relaciones especificadas, construye una relación concatenandocada tupla (o registro) de la primera relación con cada una de las tuplas de lasegunda, siempre que ambas tuplas satisfagan una condición dada. Por logeneral esa condición es la igualdad indicando que los valores de la claveforánea en la relación hija deben corresponder con los valores de la claveprimaria en la relación padre.

Uso

Consulta No. 2.19Listar los nombres de los municipios junto con el nombre del departamento alque pertenece.

SELECT mun_nombre, dep_nombre FROM municipio, departamentoWHERE municipio.dep_id = departamento.dep_id;

Combinamos todos los municipios con todos los departamentos pero luegoseleccionamos los que cumplan que el dep_id de la tabla municipio sea igual al



72

dep_id de la tabla de departamento, por lo tanto nos quedamos con losmunicipios combinados con los datos del departamento correspondiente.

Donde la tabla municipio es la relación hija cuya clave foránea es el atributodep_id y la tabla departamento es la relación padre cuya clave primaria es elatributo dep_id.

En la consulta se usan dos atributos con el mismo nombre pero éstospertenecen a diferentes. Por ello, es preciso calificar el nombre del atributo conel nombre de la relación, a fin de evitar la ambigüedad. Esto se haceanteponiendo el nombre de la relación al nombre del atributo y separado losdos con un punto.

Mun_nombre Dep_nombrePamplona Norte de SantanderCúcuta Norte de SantanderOcaña Norte de SantanderBucaramanga SantanderLebrija SantanderBarbosa SantanderBarrancabermeja SantanderCartagena BolívarMagangué BolívarBarbosa Antioquia

Consulta No. 2.20Listar los nombres de los municipios junto con el nombre del departamento alque pertenece, ordenados alfabéticamente por nombre de departamento yluego alfabéticamente por nombre del municipio.

SELECT mun_nombre, dep_nombre FROM municipio, departamentoWHERE municipio.dep_id = departamento.dep_idORDER BY dep_nombre, mun_nombre;



73

Mun_nombre Dep_nombreBarbosa AntioquiaCartagena BolívarMagangué BolívarCúcuta Norte de SantanderOcaña Norte de SantanderPamplona Norte de SantanderBarbosa SantanderBarrancabermeja SantanderBucaramanga SantanderLebrija Santander

Consulta No. 2.21Listar los nombres de los municipios junto con el nombre del departamento alque pertenece cuya extensión esta comprendida entre 20000 y 25000,ordenados alfabéticamente por nombre de departamento y luegoalfabéticamente por nombre del municipio.

SELECT mun_nombre, dep_nombre FROM municipio, departamentoWHERE (municipio.dep_id = departamento.dep_id)AND (dep_extension >=20000 AND dep_extension <= 25000)ORDER BY dep_nombre, mun_nombre;

Ó renombrando tablas

SELECT mun_nombre, dep_nombre FROM municipio m, departamento dWHERE (m.dep_id = d.dep_id)AND (dep_extension >=20000 AND dep_extension <= 25000)ORDER BY dep_nombre, mun_nombre;

Ó accediendo a cada atributo con el alias

SELECT m.mun_nombre, d.dep_nombreFROM municipio m, departamento dWHERE (m.dep_id = d.dep_id)AND (d.dep_extension >=20000 AND d.dep_extension <= 25000)ORDER BY d.dep_nombre, m.mun_nombre;



74

Mun_nombre Dep_nombreCúcuta Norte de SantanderOcaña Norte de SantanderPamplona Norte de Santander

Consulta No. 2.22Para cada ruta, obtener su número, el nombre del municipio donde sale y elnombre del municipio donde llega.

SELECT r.rut_numero,s.mun_nombre,ll.mun_nombreFROM ruta R,municipio S, municipio LLWHERE r.rut_salida = s.mun_id AND r.rut_llegada = ll.mun_id;

En este caso se necesita la tabla ruta, y dos tablas municipio. Una de las cualesse utilizara para sacar el nombre del municipio origen y la otra para sacar elnombre del municipio destino.

SELECT r.rut_numero,s.mun_nombre,ll.mun_nombreFROM ruta AS R,municipio AS S, municipio AS LLWHERE r.rut_salida = s.mun_id AND r.rut_llegada = ll.mun_id;

En la consulta, se declara los nombres de las relaciones alternativas R, S y LL,llamados renombrados, para la relación ruta, municipio y municipiorespectivamente. El renombrado puede seguir directamente al nombre de larelación, o pude ir después de la palabra reservada AS (como).



75

Rut_numero Mun_nombre Mun_nombreR1_1 Pamplona CúcutaR2_1 Pamplona BucaramangaR1_2 Cúcuta BucaramangaR2_2 Cúcuta PamplonaR3_2 Cúcuta BarrancabermejaR4_2 Cúcuta BarbosaR1_3 Bucaramanga BarrancabermejaR2_3 Bucaramanga Pamplona

Ó renombrando los atributos del select

SELECT r.rut_numero AS Numero,s.mun_nombre AS Salida,ll.mun_nombre AS LLegadaFROM ruta R,municipio S, municipio LLWHERE r.rut_salida = s.mun_id AND r.rut_llegada = ll.mun_id;

Numero Salida Llegadar1_1 Pamplona Cúcutar2_1 Pamplona Bucaramangar1_2 Cúcuta Bucaramangar2_2 Cúcuta Pamplonar3_2 Cúcuta Barrancabermejar4_2 Cúcuta Barbosar1_3 Bucaramanga Barrancabermejar2_3 Bucaramanga Pamplona

Consulta No. 2.23Para cada ruta, obtener su número, el nombre del municipio donde sale, elnombre del municipio donde llega y cuanto tiempo dura la ruta. Para aquellasrutas cuyo tiempo esta por debajo de 3 horas y por encima de 6 horas.

SELECT r.rut_numero AS Numero,s.mun_nombre AS Salida,ll.mun_nombre AS LLegada, r.rut_numero_horas AS TiempoFROM ruta r,municipio s, municipio llWHERE r.rut_salida = s.mun_id AND r.rut_llegada = ll.mun_id AND(r.rut_numero_horas<3 OR r.rut_numero_horas>6);

Son necesarios los paréntesis para definir la condición.



76

Numero Salida Llegada TiempoR1_1 Pamplona Cúcuta 1.75R2_2 Cúcuta Pamplona 2.00R3_2 Cúcuta Barrancabermeja 7.00R4_2 Cúcuta Barbosa 9.00R1_3 Bucaramanga Barrancabermeja 2.00R2_3 Bucaramanga Pamplona 2.80

2.4.3 Composición Interna - INNER JOIN

Descripción

Permite emparejar filas de distintas tablas de forma más eficiente que con elproducto cartesiano cuando una de las columnas de emparejamiento es claveprimaria. Ya que en vez de hacer el producto cartesiano completo y luegoseleccionar la filas que cumplen la condición de emparejamiento, para cada filade una de las tablas busca directamente en la otra tabla las filas que cumplenla condición, con lo cual se emparejan sólo las filas que luego aparecen en elresultado.

Diagrama

tabla1 y tabla2son tabla (nombre de tabla con alias o no), de las tablas cuyos registros sevan a combinar.Pueden ser las dos la misma tabla, en este caso es obligatorio definir almenos un alias de tabla.

col1, col2son las columnas de emparejamiento.

Observar que dentro de la cláusula ON los nombres de columna deben sernombres cualificados (llevan delante el nombre de la tabla o el alias y unpunto).

Las columnas de emparejamiento deben contener la misma clase de datos, lasdos de tipo texto, de tipo fecha etc... los campos numéricos deben ser de tipossimilares.



77

comprepresenta cualquier operador de comparación ( =, <, >, <=, >=, o <> ) yse utiliza para establecer la condición de emparejamiento.Se pueden definir varias condiciones de emparejamiento unidas por losoperadores AND y OR poniendo cada condición entre paréntesis

Uso

Consulta No. 2.24Realizar la consulta No. 2.20 empleando INNER JOIN.

SELECT mun_nombre, dep_nombreFROM municipio INNER JOIN departamentoON municipio.dep_id = departamento.dep_idORDER BY dep_nombre, mun_nombre;

Consulta No. 2.25Realizar la consulta No. 2.23 empleando INNER JOIN.

SELECT r.rut_numero AS Numero,s.mun_nombre AS Salida,ll.mun_nombre AS LLegada, r.rut_numero_horas as tiempoFROM (ruta r INNER JOIN municipio s ON r.rut_salida = s.mun_id)INNER JOIN municipio ll ON r.rut_llegada = ll.mun_id AND(r.rut_numero_horas<3 OR r.rut_numero_horas>6);

2.4.4 Composición Externa

Descripción

Con una composición interna sólo se obtienen las filas que tienen al menos unafila de la otra tabla que cumpla la condición. Pues en los casos en quequeremos que también aparezcan las filas que no tienen una fila coincidente enla otra tabla, utilizaremos el LEFT o RIGHT JOIN.

Diagrama LEFT JOIN

La sintaxis es la misma que la del INNER JOIN, lo único que cambia es lapalabra INNER por LEFT (izquierda en inglés).

Esta operación consiste en añadir al resultado del INNER JOIN las filas de latabla de la izquierda que no tienen correspondencia en la otra tabla, y rellenaren esas filas los campos de la tabla de la derecha con valores nulos.



78

Diagrama LEFT JOIN

La sintaxis es la misma que la del INNER JOIN, lo único que cambia es lapalabra INNER por RIGHT (derecha en inglés).

Esta operación consiste en añadir al resultado del INNER JOIN las filas de latabla de la derecha que no tienen correspondencia en la otra tabla, y rellenaren esas filas los campos de la tabla de la izquierda con valores nulos.

Uso

Consulta No. 2.26Listar todos los departamentos junto con los municipios que tiene cada uno deellos, incluyendo los departamentos que no tienen ningún municipio.

SELECT dep_nombre,mun_nombreFROM municipio RIGHT JOIN departamentoON municipio.dep_id = departamento.dep_id;

SELECT dep_nombre,mun_nombreFROM departamento LEFT JOIN municipioON municipio.dep_id = departamento.dep_id;

Dep_nombre Mun_nombreNorte de Santander CúcutaNorte de Santander OcañaNorte de Santander PamplonaSantander BucaramangaSantander LebrijaSantander BarbosaSantander BarrancabermejaBolívar MaganguéBolívar CartagenaTolimaAntioquia Barbosa

El departamento Tolima no tiene registrado todavía algún municipio.



79

2.5 FUNCIONES DE FORMATO

Las funciones de formato proveen un poderoso conjunto de herramientas paraconvertir varios datetypes (date/time, int, float, numeric) a texto formateado yconvertir de texto formateado a su datetypes original.

Funciones Retorna Descripción

to_char(datetime, text) text convierte datetime a string

to_char(timestamp, text) text convierte timestamp a string

to_char(int, text) text convierte int4/int8 a string

to_char(flota, text) text convierte float4/float8 a string

to_char(numeric, text) text convierte numeric a string

to_datetime(text, text) datetime convierte string a datetime

to_date(text, text) date convierte string a date

to_timestamp(text, text) date convierte string a timestamp

to_number(text, text) numeric convierte string a numeric

Formato Descripción

HH hora del día(01-12)

HH12 hora del día(01-12)

MI minuto (00-59)

SS Segundos (00-59)

SSSS Segundos pasados la medianoche(0-86399)

Y,YYY año(4 o mas dígitos) con coma

YYYY año(4 o mas dígitos)

YYY últimos 3 dígitos del año

YY últimos 2 dígitos del año

Y último dígito del año



80

Formato Descripción

MONTH nombre completo del mes(9-letras) - todos los caracteres enmayúsculas

Month nombre completo del mes(9-letras) - el primer carácter en mayúsculas

month nombre completo del mes(9-letras) - todos los caracteres enminúsculas

MON nombre abreviado del mes(3-letras) -todos los caracteres enmayúsculas

Mon nombre abreviado del mes(3-letras) - el primer carácter en mayúsculas

mon nombre abreviado del mes(3-letras) - todos los caracteres enminúsculas

MM mes (01-12)

DAY nombre completo del día(9-letters) - todos los caracteres enmayúsculas

Day nombre completo del día(9-letters) - el primer carácter en mayúsculas

day nombre completo del día(9-letters) - todos los caracteres en minúsculas

DY nombre abreviado del día(3-letters) - todos los caracteres enmayúsculas

Dy nombre abreviado del día(3-letters) - el primer carácter en mayúsculas

dy nombre abreviado del día(3-letters) - todos los caracteres enminúsculas

DDD día del año(001-366)

DD día del mes(01-31)

D día de la semana(1-7; SUN=1)

W semana del mes

WW número de la semana en el año



81

EJERCICIO

Modelo Relacional DDLcreate table empleado(

emp_nss varchar(10) not null,emp_nombre varchar(20) not null,emp_apellido varchar(20) not null,emp_fecha_nacimiento date,emp_direccion varchar(30),emp_sexo boolean,emp_salario float,emp_supervisor varchar(10),primary key(emp_nss),foreign key(emp_supervisor)

references empleado (emp_nss)on update cascade on delete set null,

check(emp_salario>0));

DATOS DE LA RELACION EMPLEADO

Nss nombre apellido Fechanacimiento

dirección sexo

salario supervisor

888665555 Jaime Botello 1947-11-10 Sorgo450,Higueras, MX

t 550000

333445555 Federico Vizcarra 1965-12-08 Valle 638,Higueras, MX

t 400000 888665555

987654321 Jazmin Valdés 1951-06-20 Bravo 291,Belén, MX

f 430000 888665555

123456789 José Silva 1975-01-09 Fresnos 731,Higueras, MX

t 300000 333445555

666884444 Ramón Nieto 1972-09-15 Espiga 875,Heras, MX

t 380000 333445555

453453453 Josefa Esparza 1982-07-31 Rosas 5631,Higueras, MX

f 250000 333445555

999887777 Alicia Zapata 1978-07-19 Catillo 3321,Sucre, MX

f 250000 987654321

987987987 Ahmed Jabbar 1979-03-29 Dalias 980,Higueras, MX

t 250000 987654321



82

Consulta No. 2.27Listar el nss, el nombre, el apellido y la fecha de nacimiento de todos losempleados.

SELECT emp_nss, emp_nombre,emp_apellido, emp_fecha_nacimientoFROM empleado;

Emp_nss Emp_nombre Emp_apellido Emp_fecha_nacimiento888665555 Jaime Botello 1947-11-10333445555 Federico Vizcarra 1965-12-08987654321 Jazmin Valdés 1951-06-20123456789 José Silva 1975-01-09666884444 Ramón Nieto 1972-09-15453453453 Josefa Esparza 1982-07-31999887777 Alicia Zapata 1978-07-19987987987 Ahmed Jabbar 1979-03-29

Consulta No. 2.28Listar el nss, el nombre, el apellido y el año de nacimiento de todos losempleados.

SELECT emp_nss, emp_nombre,emp_apellido,TO_CHAR(emp_fecha_nacimiento, 'yyyy') AS añoFROM empleado;

Emp_nss Emp_nombre Emp_apellido año888665555 Jaime Botello 1947333445555 Federico Vizcarra 1965987654321 Jazmin Valdés 1951123456789 José Silva 1975666884444 Ramón Nieto 1972453453453 Josefa Esparza 1982999887777 Alicia Zapata 1978987987987 Ahmed Jabbar 1979

Consulta No. 2.29Listar el nss, el nombre, el apellido y la fecha de nacimiento de todos losempleados que nacieron en el mes de julio.

SELECT emp_nss, emp_nombre,emp_apellido, emp_fecha_nacimientoFROM empleadoWHERE TO_CHAR(emp_fecha_nacimiento,'mm')='07';



83

Emp_nss Emp_nombre Emp_apellido Emp_fecha_nacimiento453453453 Josefa Esparza 1982-07-31999887777 Alicia Zapata 1978-07-19


Ingresar al SGBDR PostgrSQL Conectarse al servidor Elegir la base de datos empresa



Insertar datos a la tabla empleado

En el editor de SQL escribir las sentencias para insertar datos a la tabla:



84

INSERT INTO empleado(emp_nss,emp_nombre,emp_apellido,emp_fecha_nacimiento,emp_direccion,emp_sexo,emp_salario)

VALUES ('888665555','Jaime','Botello','11-10-1947','Sorgo 450,Higueras,MX','t',550000);

INSERT INTO empleado VALUES ('333445555','Federico','Vizcarra','12-08-1965','Valle 638, Higueras, MX','t',400000,'888665555');

INSERT INTO empleado VALUES ('987654321','Jazmin','Valdés','06-20-1951','Bravo291, Belén, MX','f',430000,'888665555');

INSERT INTO empleado VALUES ('123456789','José','Silva','01-09-1975','Fresnos731, Higueras, MX','t',300000,'333445555');

INSERT INTO empleado VALUES ('666884444','Ramón','Nieto','09-15-1972','Espiga875, Heras, MX','t',380000,'333445555');

INSERT INTO empleado VALUES ('453453453','Josefa','Esparza','07-31-1982','Rosas 5631, Higueras, MX','f',250000,'333445555');

INSERT INTO empleado VALUES ('999887777','Alicia','Zapata','07-19-1978','Catillo3321, Sucre, MX','f',250000,'987654321');

INSERT INTO empleado VALUES ('987987987','Ahmed','Jabbar','03-29-1979','Dalias 980, Higueras, MX','t',250000,'987654321');



85

Ejecute el script. Si se insertaron los datos en la pestaña Data Outputinforma que así fue (Query returned successfully) en otro caso no muestranada o indicara el error producido en la pestaña Messages.

Consulta: Listar el nombre de pila, el apellido y el salario de los empleados queganan entre 280000 y 500000

En el editor de SQL escribir las sentencias para consultar datos:

SELECT emp_nombre, emp_apellido, emp_salarioFROM empleadoWHERE emp_salario > 280000 AND emp_salario < 500000;

Ejecute el script. Si la consulta no tiene errores de sintaxis en la pestañaData Output informa los resultados, en otro caso no muestra nada o indicara elerror producido en la pestaña Messages.

Consulta: Para cada empleado, obtener su nombre de pila y apellido, y elnombre de pila y apellido de su supervisor inmediato


SELECT e.emp_nombre, e.emp_apellido, s.emp_nombre, s.emp_apellido



86

FROM empleado e, empleado sWHERE s.emp_nss=e.emp_supervisor;






87

El ejercicio de Factor X esta planteado en la Unidad No. 1. Lenguaje de Definiciónde Datos.

Basado en las restricciones definidas para cada una de las relaciones (Ver el DDLen la unidad No. 1). Determine:

1. Plantear mínimo 5 consultas de acción sobre cada una de las relaciones (otablas) en un orden adecuado, de manera que al realizar una consulta deacción puede que dependa de alguna otra consulta, es decir, si se actualiza unregistro primero debe estar almacenado en la base de datos.

2. Para cada punto plantear una consulta de selección simple dependiendo de laestructura que debe tener y resolverla.

a. SELECT – FROMb. SELECT – FROM – ORDER BYc. SELECT – FROM – WHEREd. SELECT – FROM – WHERE – ORDER BYe. SELECT DISTINCT – FROMf. SELECT DISTINCT – FROM – ORDER BYg. SELECT DISTINCT – FROM – WHEREh. SELECT DISTINCT – FROM – WHERE – ORDER BYi. SELECT – FROM – WHERE. Aplicando el predicado BETWEENj. SELECT – FROM – WHERE. Aplicando el predicado INk. SELECT – FROM – WHERE. Aplicando el predicado LIKEl. SELECT – FROM – WHERE. Aplicando el predicado IS NULL

3. Para cada punto plantear una consulta de selección multitabla dependiendo dela estructura que debe tener y resolverla.

a. SELECT – FROM – WHEREb. SELECT – FROM – WHERE – ORDER BYc. SELECT DISTINCT – FROM – WHEREd. SELECT DISTINCT – FROM – WHERE – ORDER BYe. SELECT – FROM – WHERE. Aplicando el predicado BETWEENf. SELECT – FROM – WHERE. Aplicando el predicado INg. SELECT – FROM – WHERE. Aplicando el predicado LIKEh. SELECT – FROM – WHERE. Aplicando el predicado IS NULL



88

BASE DE DATOS VENTA DE PRODUCTOS

DDL en SQLcreate table persona( pers_identificacion varchar(15), pers_nombre varchar(20) not null,pers_apellido varchar(20) not null,

pers_fecha_nacimiento date,primary key(pers_identificacion)

);

create table cliente(pers_identificacion varchar(15),

clie_direccion_barrio varchar(30), clie_direccion_numero varchar(30), primary key(pers_identificacion),foreign key(pers_identificacion)references persona(pers_identificacion)

on update cascade on delete restrict);

create table empleado(pers_identificacionvarchar(15),

empl_codigo varchar(15) not null,empl_fecha_ingreso date not null,

empl_numero_hijos integer default 0 not null, empl_salario float default 0 not null, empl_comision float default 0, empl_jefe varchar(15), primary key(pers_identificacion), unique(empl_codigo), check(empl_numero_hijos>=0), check(empl_salario>=0), check(empl_comision>=0), check(pers_identificacion<>empl_jefe), foreign key(pers_identificacion)references persona(pers_identificacion) on update cascade on delete restrict, foreign key(empl_jefe)references empleado(pers_identificacion) on update cascade




89

on delete set null);

create table factura( fact_numero varchar(20), fact_fecha date not null, fact_cliente varchar(15) not null, fact_cajero varchar(15) not null, fact_vendedor varchar(15) not null, fact_observacion varchar(30) default 'ninguna' not null, primary key(fact_numero), check(fact_cajero<>fact_vendedor), foreign key(fact_cliente)references cliente(pers_identificacion) on update cascade on delete restrict, foreign key(fact_cajero)references empleado(pers_identificacion) on update cascade on delete restrict, foreign key(fact_vendedor)references empleado(pers_identificacion) on update cascade on delete restrict);

create table producto( prod_codigo varchar(15), prod_nombre varchar(20) not null, prod_precio float not null, prod_porcentaje_iva float not null, primary key(prod_codigo), unique(prod_nombre), check(prod_precio>0), check(prod_porcentaje_iva>=0 and prod_porcentaje_iva<=100));

create table detalle( fact_numero varchar(15), prod_codigo varchar(15), deta_cantidad integer not null, primary key(fact_numero,prod_codigo), check(deta_cantidad>0), foreign key(fact_numero)references factura(fact_numero) on update cascade



90

on delete restrict, foreign key(prod_codigo) references producto(prod_codigo) on update cascade

on delete restrict );

CONSULTAS

Hallar la comisión, el nombre, el apellido y el salario de los empleados con másde tres hijos, ordenados por comisión (desc) y luego por apellido (asc).

Obtener los apellidos y nombres de los jefes, ordenar por apellido. Obtener los nombres (nombre y apellido) de los jefes que en alguno de sus

apellidos tenga el apellido 'Rico'. Obtener los nombres de los empleados que nunca han sido cajeros. Obtener los nombres de los empleados que no tienen jefe. Obtener, por orden alfabético (asc), los nombres (nombre y apellido) y los

salarios de los empleados cuyo salario esté comprendido entre 1300 y 2500euros.

Datos de los empleados que cumplen la condición anterior o tiene al menos unhijo.

Muestre para cada empleado, el nombre (nombre y apellido) y el numero demeses que lleva el empleado en la empresa hasta la fecha junto con sunombre.

Calcule los empleados que llevan más de 30 años en la empresa. Muestre todoslos datos de cada uno de ellos.

Hallar, por orden alfabético, los nombres (nombre y apellido) de los empleadostales que si se les da una gratificación de 10 euros por hijo, el total de esagratificación no supera la centésima parte del salario.

Hallar, por orden de código de empleado, el nombre (nombre y apellido) y elsalario total (salario mas comisión) de los empleados cuyo salario total superalos 2500 euro mensuales.

Obtener, por orden alfabético, los nombres (nombre y apellido) y la direcciónde los clientes que no contengan en la dirección la palabra 'Carrera' ni 'Bloque'.

Obtener los nombres de los empleados que, o bien no depende de otro, o bienque tiene un salario superior a 1800 euros.

Listar todos los clientes (nombre y apellido) que realizaron compras en el mesde septiembre de 2006.

Obtener, por orden alfabético, los nombres (apellido y nombre) de los clientesque no contengan en su apellido la palabra 'Rodriguez' ni en su nombre lapalabra 'Carlos'.

Listar número de factura, nombres (apellido y nombre) del cliente, nombres(apellido y nombre) de vendedor y nombres (apellido y nombre) de cajerojunto con su detalle (nombre del producto cantidad, precio y subtotal del



91

mismo). Tener en cuenta el IVA. Obtener los datos del producto que no se ha vendido hasta el momento.

Escoja alguna aplicación de base de datos que conozca bien. Diseñe un modelo de base de datos relacional para su aplicación. Declare el modelo, empleando el DDL de SQL. Especifique varias consultas de acción, selección y multitabla que necesite su

aplicación de base de datos en SQL. Implemente su base de datos.

Selección múltiple con única respuesta

1. Sentencia que permite insertar datosA. Insert into B. UpdateC. Select D. Create

2. Sentencia que permite eliminar datosA. Drop B. UpdateC. Delete from D. Select

3. Sentencia que permite recuperar datosA. Insert into B. UpdateC. Select D. Where

4. Sentencia que permite cambiar datosA. Where B. UpdateC. Select D. As

5. Cláusula para indicar que relaciones se utilizaronA. Where B. Order byC. From D. In


Autoevaluación



92

6. Cláusula para indicar que registros se afectanA. Select B. WhereC. From D. In

7. Cláusula para indicar en que orden se presentan las tuplas (o registros)A. Where B. DescC. Order By D. Asc

8. Predicado para eliminar registros repetidos en un resultadoA. All B. Delete fromC. Drop D. Distinct

9. Predicado para permite evaluar si el atributo esta con datos o sin datosA. In B. is nullC. where D. not

10. Es una cláusulaA. In B. FromC. Between D. Like

El lenguaje de manipulación de datos, nos permite recuperar los datosalmacenados en la base de datos y también incluye órdenes para permitir alusuario actualizar la base de datos añadiendo nuevos datos, suprimiendo datosantiguos o modificando datos previamente almacenados.

Todas las sentencias empiezan con un verbo (palabra reservada que indica laacción a realizar), seguido del resto de cláusulas, algunas obligatorias y otrasopcionales que completan la frase. Todas las sentencias siguen una sintaxis paraque se puedan ejecutar correctamente.

Si queremos añadir en una tabla una fila con valores conocidos utilizamos lasentencia INSERT INTO tabla VALUES (lista de valores).

Si los valores a insertar se encuentran en una o varias tablas utilizamos INSERTINTO tabla SELECT ...

Para cambiar los datos contenidos en una tabla, tenemos que actualizar las filas dedicha tabla con la sentencia UPDATE tabla SET asignación de nuevos valores.




93

Para eliminar filas de una tabla se utiliza la sentencia DELETE FROM tabla.

Para recuperar los datos contenidos en una tabla o tablas se utiliza la sentenciaSELECT

Con la cláusula FROM podemos indicar que tablas se sacaran los datos paraprocesarlos.

Con la cláusula WHERE podemos indicar a qué filas afecta la actualización o elborrado o la consulta.

Para ordenar las filas del resultado de la consulta, tenemos la cláusula ORDER BY.

Al incluir el predicado DISTINCT en la SELECT, se eliminan del resultado lasrepeticiones de filas. Si por el contrario queremos que aparezcan todas las filasincluidas las duplicadas, podemos incluir la cláusula ALL o nada, ya que ALL es elvalor que SQL asume por defecto.

El predicado IN dentro de la cláusula WHERE, examina si el valor de la expresiónes uno de los valores incluidos en la lista de valores.

El Predicado BETWEEN dentro de la cláusula WHERE, examina si el valor de laexpresión está comprendido entre dos valores.

El predicado LIKE dentro de la cláusula WHERE, se utiliza cuando queremos utilizarcaracteres comodines para formar el valor con el comparar.

El predicado IS NULL dentro de la cláusula WHERE, examina si el valor de lacolumna es nulo o no.

El producto cartesiano se indica poniendo en la FROM las tablas que queremoscomponer separadas por comas, podemos obtener así el producto cartesiano dedos, o más tablas.

La composición es un producto cartesiano seguido de una restricción por igualdad.

INNER JOIN Permite emparejar filas de distintas tablas de forma más eficiente quecon el producto cartesiano cuando una de las columnas de emparejamiento esclave primaria.



94

GROFF, James R. , WEINBERG, Paul N. Aplique SQL. McGrawHill. 1991

ELMASRI / NAVATHE. Sistemas de Bases de Datos. Conceptos Fundamentales.Pearson Educación. 2000

CASTAÑO, Miguel. Diseño de Base de Datos – Problemas Resueltos. AlfaOmega.2001




95

UNIDAD 3: LENGUAJE DE DEFINICIONDE DATOS II


Muchas peticiones de información no requieren el nivel de detalle proporcionadopor las consultas SQL descritas en la Unidad 2. Lenguajes de Manipulación deDatos I. Hay peticiones que solicitan un único valor o un pequeño número devalores que resumen los contenidos de las bases de datos. SQL soporta estaspeticiones de datos de sumario mediante funciones de columna y mediante lascláusulas GROUP BY y HAVING de la sentencia SELECT que permiten resumir filas.

La característica de las subconsultas de SQL permite utilizar los resultados de unaconsulta como parte de otra. La capacidad de utilizar una consulta dentro de otrafue la razón original para la palabra “estructurada” en el nombre Lenguaje deConsultas Estructuradas (Structured Query Language – SQL). Las característicasde composición son menos conocidas que la característica de composición de SQL,pero juega un papel importante por tres (3) razones:

Una sentencia SQL con un subconsulta es frecuentemente el modo más naturalde expresar una consulta, ya que se asemeja más estrechamente a ladescripción de la consulta en lenguaje natural.

Las subconsultas hacen más fácil la escritura de sentencia SELECT, ya quepermiten descomponer una consulta en partes (la consulta y sus subconsultas)y luego recomponer las partes.

Hay algunas consultas que no pueden ser expresadas en el lenguaje SQL sinutilizar una subconsulta.

Esta unidad describe las subconsultas y cómo se utilizan en las cláusulas WHERE yHAVING de una sentencia SQL.

Los ejemplos de esta unidad están basados en el modelo relacional de la base dedatos de transporte. Ejercicio expuesto en la unidad 2. Lenguaje de manipulaciónde Datos I.



96

Identificar claramente los diagramas sintácticos de las consultas agrupadasy subconsultas.

Conocer los fundamentos básicos de DML, por ser éste un importantelenguaje no procedural.

Determinar las características de las consultas agrupadas y subconsultaspara procesar los datos almacenados en la base de datos.

Diferenciar la conceptualización de consultas agrupadas de las noagrupadas.

Consultas sumarias.

Subconsulta.

Ejemplo.

3.1 FUNCIONES DE COLUMNA

Una función de columna se aplica a una columna y obtiene un valor que resume elcontenido de la columna.

3.1.1 Función COUNT

Descripción

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



Horizontes



97

Estructura

COUNT(<expr>)

En donde expr contiene el nombre del campo que desea contar. Los operandosde expr pueden incluir el nombre de un campo de una tabla, una constante ouna función (siempre y cuando no sea otras de las funciones de columnas deSQL). Puede contar cualquier tipo de datos incluso texto.

Aunque expr puede realizar un cálculo sobre un campo, Count simplementecuenta el número de registros sin tener en cuenta qué valores se almacenan enlos registros. La función Count no cuenta los registros que tienen campos null amenos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco,Count calcula el número total de registros, incluyendo aquellos que contienencampos null. Count(*) es considerablemente más rápida que Count(Campo).

Uso

Consulta No. 3.1Obtener cuantos departamentos hay registrados.

SELECT COUNT(*) AS total FROM departamento;

ó

SELECT COUNT(dep_id) AS total FROM departamento;

ó

SELECT COUNT(dep_nombre) AS total FROM departamento;

Total5

NotaEn esta consulta no se puede hacer referncia con el atributo dep_extensiondentro de la function count porque solo contaria los que tienen valor.



98

Consulta No. 3.2Determine cuantos departamentos tienen registrado la extensión.

SELECT COUNT(dep_extension) AS total FROM departamento;

Total3

Consulta No. 3.3Determine cuantos departamentos no tienen registrado la extensión.

SELECT COUNT(*) - COUNT(dep_extension) AS totalFROM departamento;

Total2

3.1.2 Función SUM

Descripción

Devuelve la suma del conjunto de valores contenido en un campo específico deuna consulta. Los datos que se suman deben ser de tipo numérico.

Estructura

SUM(<expr>)

En donde expr respresenta el nombre del campo que contiene los datos quedesean sumarse o una expresión que realiza un cálculo utilizando los datos dedichos campos. Los operandos de expr pueden incluir el nombre de un campode una tabla, una constante o una función (siempre y cuando no sea otras delas funciones de columna de SQL).

Uso

Consulta No. 3.4Se desea saber el acumulado de las extensiones de los departamentos.

SELECT SUM(dep_extension) AS total FROM departamento;



99

Total75757

3.1.3 Función MIN

Descripción

Devuelven el mínimo de un conjunto de valores contenidos en un campoespecifico de una consulta.

Los valores de la columna pueden ser de tipo numérico, texto o fecha. Elresultado de la función tendrá el mismo tipo de dato que la columna. Si lacolumna es de tipo numérico MIN() devuelve el valor menor contenido en lacolumna, si la columna es de tipo texto MIN() devuelve el primer valor enorden alfabético, y si la columna es de tipo fecha, MIN() devuelve la fecha másantigua.

Estructura

MIN(<expr>)

En donde expr es el campo sobre el que se desea realizar el cálculo. Exprpueden incluir el nombre de un campo de una tabla, una constante o unafunción (siempre y cuando no sea otras de las funciones de columna de SQL)

Uso

Consulta No. 3.5Obtener la mínima extensión registrada.

SELECT MIN(dep_extension) AS total FROM departamento;

Total21658

Consulta No. 3.6Obtener el mínimo tiempo que dura el recorrido de una ruta.

SELECT MIN(rut_numero_horas) AS total FROM ruta;



100

Total1.75

3.1.4 Función MAX

Descripción

Devuelven el máximo de un conjunto de valores contenidos en un campoespecifico de una consulta.

Los valores de la columna pueden ser de tipo numérico, texto o fecha. Elresultado de la función tendrá el mismo tipo de dato que la columna. Si lacolumna es de tipo numérico MAX() devuelve el valor mayor contenido en lacolumna, si la columna es de tipo texto MAX() devuelve el último valor enorden alfabético, y si la columna es de tipo fecha, MAX() devuelve la fecha másreciente.

Estructura

MAX(<expr>)


Uso

Consulta No. 3.7Obtener la máxima extensión registrada.

SELECT MAX(dep_extension) AS total FROM departamento;

Total30537

Consulta No. 3.8Obtener el máximo tiempo que dura el recorrido de una ruta.

SELECT MAX(rut_numero_horas) AS total FROM ruta;



101

Total9.0

3.1.5 Función AVG

Descripción

Calcula el promedio (la media aritmética) de los valores indicados en elargumento, también se aplica a datos numéricos, y en este caso el tipo de datodel resultado puede cambiar según las necesidades del sistema pararepresentar el valor del resultado.

Estructura

AVG(<expr>)


Uso

Consulta No. 3.9Obtener el promedio del tiempo que dura el recorrido de todas las rutas quesalen de Cúcuta.

SELECT AVG(rut_numero_horas) AS totalFROM ruta r, municipio mWHERE r.rut_salida = m.mun_id AND m.mun_nombre = 'Cúcuta';

Total5.75

NotaRecuerde cuando hay más de una relación (o tabla) en la cláusula FROM sedebe destruir el producto cartesiano.



102

3.2 CONSULTAS SUMARIAS

Descripción

En la mayoría de los motores de bases de datos relacionales, se puede definirun tipo de consultas cuyas filas resultantes son un resumen de las filas de latabla origen.

Estructura

SELECT [ALL | DISTINCT ] <nombre_campo> [{,<nombre_campo>}] [{,<funcion_agregado>}]FROM <nombre_tabla>|<nombre_vista> [{,<nombre_tabla>|<nombre_vista>}][WHERE <condicion> [{ AND|OR <condicion>}]][GROUP BY <nombre_campo> [{,<nombre_campo >}]][HAVING <condicion>[{ AND|OR <condicion>}]][ORDER BY <nombre_campo>|<indice_campo> [ASC | DESC] [{,<nombre_campo>|<indice_campo> [ASC | DESC ]}]]

Diagrama

Introducen dos nuevas cláusulas a la sentencia SELECT, la cláusula GROUP BYy la cláusula HAVING, son cláusulas que sólo se pueden utilizar en una consulta



103

agregada, se tienen que escribir entre la cláusula WHERE y la cláusula ORDERBY.

3.2.1 La cláusula GROUP BY

Una consulta con una cláusula GROUP BY se denomina consulta agrupada ya queagrupa los datos de la tabla origen y produce una única fila resumen por cadagrupo formado. Las columnas indicadas en el GROUP BY se llaman columnas deagrupación.

Diagrama

Uso

Consulta No. 3.10Determinar el nombre del departamento junto con la cantidad de municipiosque tiene registrados cada uno de ellos.

SELECT dep_nombre, COUNT(d.dep_id) AS total_muncipiosFROM municipio m, departamento dWHERE m.dep_id = D.dep_idGROUP BY dep_nombre;

dep_nombre total_muncipiosAntioquia 1Santander 4Norte de Santander 3Bolivar 2

NotaEl atributo dep_nombre que forman parte de la lista de selección está porfuera de una función agregada por lo tanto, tiene que ir también en lacláusula GROUP BY.

Consulta No. 3.11Determinar el nombre del departamento junto con la cantidad de municipiosque tiene registrados cada uno de ellos, incluyendo los departamentos que notienen municipios.



104

SELECT dep_nombre, count(mun_nombre) AS total_municipiosFROM municipio m RIGHT JOIN departamento dON m.dep_id = D.dep_idGROUP BY dep_nombre;

dep_nombre total_muncipiosAntioquia 1Santander 4Tolima 0Norte de Santander 3Bolivar 2

Consulta No. 3.12Determinar el nombre del municipio junto con el nombre del departamento alque pertenece cada municipio y cuantas rutas salen de cada uno de ellos.

SELECT dep_nombre, mun_nombre, COUNT(d.dep_id) AS total_rutasFROM municipio m, departamento d, ruta rWHERE m.dep_id = d.dep_id AND r.rut_salida=m.mun_idGROUP BY dep_nombre, mun_nombre;

dep_nombre mun_nombre total_rutasNorte de Santander Cúcuta 4Norte de Santander Pamplona 2Santander Bucaramanga 2

NotaEl atributo dep_nombre y dep_municipio que forman parte de la lista deselección están por fuera de una función agregada por lo tanto, tiene que irtambién en la cláusula GROUP BY.

3.2.2 La cláusula HAVING

La cláusula HAVING nos permite seleccionar filas de la tabla resultante de unaconsulta agregada.



105

Diagrama

Para la condición de selección se pueden utilizar los mismos tests decomparación descritos en la cláusula WHERE, también se pueden escribircondiciones compuestas (unidas por los operadores OR, AND, NOT), peroexiste una restricción.

En la condición de selección sólo pueden aparecer: Valores constantes Funciones de columna columnas de agrupación (columnas que aparecen en la cláusula GROUP BY)

o cualquier expresión basada en las anteriores.

Uso

Consulta No. 3.13Determinar el nombre del departamento junto con la cantidad de municipiosque tiene registrados cada uno de ellos. Solamente para aquellos que tienenmás de 2 municipios.

SELECT dep_nombre, COUNT(d.dep_id) AS total_municipiosFROM municipio m, departamento dWHERE m.dep_id = D.dep_idGROUP BY dep_nombreHAVING count(d.dep_id)>2;

Dep_nombre Total_municipiosSantander 4Norte de Santander 3

NotaComo la condición depende del número de municipios y los cuales seobtuvieron por una función agregada, entonces no se puede utilizar WHEREpor que en él no se puede colocar condiciones con funciones agregadas.Para colocar funciones agregadas en una condición se debe utilizar lacláusula HAVING.



106

3.3 SUBCONSULTAS

Una subconsulta es una sentencia SELECT que aparece dentro de otra sentenciaSELECT que llamaremos consulta principal.

Se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusulaHAVING de la consulta principal.

Una subconsulta tiene la misma sintaxis (o estructura) que una sentencia SELECTnormal exceptuando que aparece encerrada entre paréntesis.

Tiene las siguientes restricciones: No puede contener la cláusula ORDER BY No puede ser la UNION de varias sentencias SELECT Si la subconsulta aparece en la lista de selección, o esta asociada a un

operador igual "=" solo puede devolver un único registro.

Cuando se ejecuta una consulta que contiene una subconsulta, la subconsulta seejecuta por cada fila de la consulta principal. Se aconseja no utilizar camposcalculados en las subconsultas, ralentizan la consulta.

Las consultas que utilizan subconsultas suelen ser más fáciles de interpretar por elusuario.

3.3.1 Referencias Externas

Una referencia externa es un nombre de columna que estando en la subconsulta,no se refiere a ninguna columna de las tablas designadas en la FROM de lasubconsulta sino a una columna de las tablas designadas en la FROM de laconsulta principal. Como la subconsulta se ejecuta por cada fila de la consultaprincipal, el valor de la referencia externa irá cambiando.

Uso

Consulta No. 3.14Realizar la Consulta No. 3.11

SELECT dep_nombre,(SELECT COUNT(m.dep_id) FROM municipio m

WHERE m.dep_id = d.dep_id) AS total_municipiosFROM departamento d;



107

Dep_nombre Total_municipiosNorte de Santander 3Santander 4Bolivar 2Tolima 0Antioquia 1

La consulta principal es SELECT … FROM departamento d;La subconsulta es (SELECT COUNT(m.dep_id) FROM municipio m WHEREm.dep_id = d.dep_id). En esta subconsulta tenemos una referencia externa( d.dep_id ) es un campo de la tabla departamento (origen de la consultaprincipal).

¿Qué pasa cuando se ejecuta la consulta principal?

- Se coge el primer departamento y se calcula la subconsulta sustituyendod.dep_id por el valor que tiene en el primer departamento. Lasubconsulta obtiene la cantidad de municipios que tiene el departamentodel dep_id = ‘d1’

- Se coge el segundo departamento y se calcula la subconsulta con dep_id‘d2’ (dep_id del segundo departamento)... y así sucesivamente hastallegar al último departamento.

NotaLa subconsulta aparece en la lista de selección, por lo tanto, solo puededevolver un único registro. Si llega a devolver más de un registro seproduce un ERROR.

3.3.2 Anidar Subconsultas

Las subconsultas pueden anidarse de forma que una subconsulta aparezca en lacláusula WHERE de otra subconsulta que a su vez forma parte de otra consultaprincipal. En la práctica, una consulta consume mucho más tiempo y memoriacuando se incrementa el número de niveles de anidamiento. La consulta resultatambién más difícil de leer , comprender y mantener cuando contiene más de unoo dos niveles de subconsultas.



108

Uso

Consulta No. 3.15Obtener el nombre de los departamentos de donde salen las rutas.

SELECT d.dep_nombreFROM departamento dWHERE d.dep_id IN ( SELECT m.dep_id

FROM municipio mWHERE m.mun_id IN ( SELECT r.rut_salida

FROM ruta r)

);

Dep_nombreNorte de SantanderSantander

En esta consulta , por cada linea de municipio se calcula la subconsulta deruta, y esto se repite por cada departamento, en el caso de tener 5 filas dedepartamentos y 10 filas de municipios, la subconsulta más interna seejecutaría 50 veces (5 x 10).

NotaEl predicado IN, examina si el valor de la expresión es uno de los valoresincluidos en la lista de valores producida por la subconsulta. La subconsultadebe generar una única columna y las filas que sean. Si la subconsulta noproduce ninguna fila, el test da falso.

3.3.3 Subconsultas en la cláusula FROM

En la cláusula FROM se puede encontrar una sentencia SELECT encerrada entreparéntesis pero más que subconsulta sería una consulta ya que no se ejecuta paracada fila de la tabla origen sino que se ejecuta una sola vez al principio, suresultado se combina con las filas de la otra tabla para formar las filas origen de laSELECT primera y no admite referencias externas.

En la cláusula FROM vimos que se podía poner un nombre de tabla o un nombrede consulta, pues en vez de poner un nombre de consulta se puede poner



109

directamente la sentencia SELECT correspondiente a esa consulta encerrada entreparéntesis.

Uso

Consulta No. 3.16Obtener el número de ruta que más dura el recorrido junto con su tiempo.

SELECT rut_numero, rut_numero_horasFROM ruta r,

(SELECT MAX(rut_numero_horas) AS mxFROM ruta) AS valor

WHERE rut_numero_horas = valor.mx;

Rut_numero Rut_numero_horasr4_2 9.0

NotaLa consulta principal está formada por dos relaciones: ruta la cual estarenombrada con r y la subconsulta renombrada con valor. Para evitar elproducto cartesiano se destruye en el WHERE, ya que la subconsulta puededevolver más de una tupla (o resgistro).

3.3.4 Subconsulta en las cláusulas WHERE y HAVING

Se suele utilizar subconsultas en las cláusulas WHERE o HAVING cuando los datosque queremos visualizar están en una tabla pero para seleccionar las filas de esatabla necesitamos un dato que está en otra tabla.

Uso

Consulta No. 3.17Realizar la Consulta No. 3.16

SELECT rut_numero , rut_numero_horasFROM rutaWHERE rut_numero_horas = (

SELECT MAX(rut_numero_horas) FROMruta);



110

Rut_numero Rut_numero_horasr4_2 9.0

NotaEl test de comparación con subconsulta, es el equivalente al test decomparación simple. Se utiliza para comparar un valor de la fila que se estáexaminado con un único valor producido por la subconsulta. La subconsultadebe devolver una única columna, sino se produce un error.

Si la subconsulta no produce ninguna fila o devuelve el valor nulo, el testdevuelve el valor nulo, si la subconsulta produce varias filas, SQL devuelveuna condición de error.

Consulta No. 3.18Obterne los nombre de los departamentos que no tienen registrado municipio.

SELECT dep_nombreFROM departamentoWHERE dep_id NOT IN (SELECT dep_id FROM municipio);

Dep_nombreTolima

NotaEl predicado IN, examina si el valor de la expresión es uno de los valoresincluidos en la lista de valores producida por la subconsulta. La subconsultadebe generar una única columna y las filas que sean. Si la subconsulta noproduce ninguna fila, el test da falso.



111

Consulta No. 3.19Realizar la consulta No. 3.18 con el predicado EXISTS

SELECT dep_nombreFROM departamento dWHERE NOT EXISTS (SELECT *

FROM municipio mWHERE m.dep_id=d.dep_id);

Dep_nombreTolima

NotaEl predicado EXISTS, examina si la subconsulta produce alguna fila deresultados.

Si la subconsulta contiene filas, el test adopta el valor verdadero, si lasubconsulta no contiene ninguna fila, el test toma el valor falso, nuncapuede tomar el valor nulo.

Con este test la subconsulta puede tener varias columnas, no importa yaque el test se fija no en los valores devueltos sino en si hay o no fila en latabla resultado de la subconsulta.

Cuando se utiliza el test de existencia en la mayoría de los casos habrá queutilizar una referencia externa. Si no se utiliza una referencia externa lasubconsulta devuelta siempre será la misma para todas las filas de laconsulta principal y en este caso se seleccionan todas las filas de la consultaprincipal (si la subconsulta genera filas) o ninguna (si la subconsulta nodevuelve ninguna fila)


Ingresar al SGBDR PostgrSQL Conectarse al servidor Elegir la base de datos empresa




112


Consulta: Obtener la suma de los salarios de todos los empleados, el salariomáximo, el salario mínimo y el salario medio.


SELECT SUM(emp_salario), MAX(emp_salario), MIN(emp_salario),AVG(emp_salario) FROM empleado;



113


Consulta: Obtener el nombre de pila y el apellido de los supervisores junto conla cantidad de empleados que tiene a cargo.


SELECT s.emp_nombre, s.emp_apellido, COUNT(*)FROM empleado e, empleado sWHERE s.emp_nss=e.emp_supervisorGROUP BY s.emp_nombre, s.emp_apellido;




114


El ejercicio de Factor X esta planteado en la Unidad No. 1. Lenguaje de Definiciónde Datos.

Basado en las restricciones definidas para cada una de las relaciones (Ver el DDLen la unidad No. 1). Determine:

1 Para cada punto plantear una Consulta que utilice la función de columna AVGó MIN ó MAX ó SUM ó COUNT dependiendo de la estructura que debe tener yresolverla.

a. SELECT – FROMb. SELECT – FROM – ORDER BYc. SELECT – FROM – WHEREd. SELECT – FROM – WHERE – ORDER BY

2 Para cada punto plantear una Consulta Sumatoria que utilice la función decolumna AVG ó MIN ó MAX ó SUM ó COUNT dependiendo de la estructuraque debe tener y resolverla.

a. SELECT – FROM – GROUP BYb. SELECT – FROM – GROUP BY – ORDER BYc. SELECT – FROM – WHERE – GROUP BYd. SELECT – FROM – WHERE – GROUP BY – ORDER BYe. SELECT – FROM – GROUP BY – HAVING




115

f. SELECT – FROM – GROUP BY – HAVING – ORDER BYg. SELECT – FROM – WHERE – GROUP BY – HAVINGh. SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY

4. Para cada punto plantear una subconsulta dependiendo de la estructura quedebe tener y resolverla.

a. SELECT <campos>, subconsulta FROMb. SELECT <campos>, subconsulta FROM – ORDER BYc. SELECT – FROM <tablas>, subconsulta WHEREd. SELECT – FROM <tablas>, subconsulta WHERE - ORDER BYe. SELECT – FROM – WHERE expresión <operador de relación> subconsultaf. SELECT – FROM – WHERE expresión <operador de relación> subconsulta

ORDER BYg. SELECT – FROM – WHERE expresión IN subconsultah. SELECT – FROM – WHERE expresión IN subconsulta ORDER BYi. SELECT – FROM – WHERE expresión NOT IN subconsultaj. SELECT – FROM – WHERE expresión NOT IN subconsulta ORDER BYk. SELECT – FROM – WHERE EXISTS subconsultal. SELECT – FROM – WHERE EXISTS subconsulta ORDER BYm. SELECT – FROM – WHERE NOT EXISTS subconsultan. SELECT – FROM – WHERE NOT EXISTS subconsulta ORDER BYo. SELECT – FROM <tablas>, subconsulta WHERE – GROUP BYp. SELECT – FROM <tablas>, subconsulta WHERE – GROUP BY – ORDER BYq. SELECT – FROM – GROUP BY – HAVING función columna <operador de

relación> subconsultar. SELECT – FROM – GROUP BY – HAVING función columna <operador de

relación> subconsulta ORDER BYs. SELECT – FROM – WHERE – GROUP BY – HAVING función columna <operador

de relación> subconsultat. SELECT – FROM – WHERE – GROUP BY – HAVING función columna <operador

de relación> subconsulta ORDER BY



116

BASE DE DATOS VENTA DE PRODUCTOS

DDL en SQLcreate table persona( pers_identificacion varchar(15), pers_nombre varchar(20) not null,pers_apellido varchar(20) not null,

pers_fecha_nacimiento date,primary key(pers_identificacion)

);

create table cliente(pers_identificacion varchar(15),

clie_direccion_barrio varchar(30), clie_direccion_numero varchar(30), primary key(pers_identificacion),foreign key(pers_identificacion)references persona(pers_identificacion)

on update cascade on delete restrict

);

create table empleado(pers_identificacionvarchar(15),

empl_codigo varchar(15) not null,empl_fecha_ingreso date not null,

empl_numero_hijos integer default 0 not null, empl_salario float default 0 not null, empl_comision float default 0, empl_jefe varchar(15), primary key(pers_identificacion), unique(empl_codigo), check(empl_numero_hijos>=0), check(empl_salario>=0), check(empl_comision>=0), check(pers_identificacion<>empl_jefe), foreign key(pers_identificacion)references persona(pers_identificacion) on update cascade on delete restrict, foreign key(empl_jefe)references empleado(pers_identificacion)




117

on update cascade on delete set null);

create table factura( fact_numero varchar(20), fact_fecha date not null, fact_cliente varchar(15) not null, fact_cajero varchar(15) not null, fact_vendedor varchar(15) not null, fact_observacion varchar(30) default 'ninguna' not null, primary key(fact_numero), check(fact_cajero<>fact_vendedor), foreign key(fact_cliente)references cliente(pers_identificacion) on update cascade on delete restrict, foreign key(fact_cajero)references empleado(pers_identificacion) on update cascade on delete restrict, foreign key(fact_vendedor)references empleado(pers_identificacion) on update cascade on delete restrict);

create table producto( prod_codigo varchar(15), prod_nombre varchar(20) not null, prod_precio float not null, prod_porcentaje_iva float not null, primary key(prod_codigo), unique(prod_nombre), check(prod_precio>0), check(prod_porcentaje_iva>=0 and prod_porcentaje_iva<=100));

create table detalle( fact_numero varchar(15), prod_codigo varchar(15), deta_cantidad integer not null, primary key(fact_numero,prod_codigo), check(deta_cantidad>0), foreign key(fact_numero)references factura(fact_numero)



118

on update cascade on delete restrict, foreign key(prod_codigo) references producto(prod_codigo) on update cascade

on delete restrict );

CONSULTAS

Listar número de factura y total de la factura. Determine que facturas tiene un total superior a 500 euros. (Número de factura

y monto) De cada factura indicar el número de la factura y cuantos productos se

vendieron. De cada factura indicar el número de la factura y cuantas unidades se

vendieron en general (no indicar el producto). Que producto es el más vendido. De cada factura indicar el número de la factura y nombre del producto con

menos unidades vendidas junto con la cantidad. Total de facturas hasta la fecha. Total de facturas por cada fecha. (fecha y cantidad) Total de facturas por cada año. (año y cantidad) Total recaudado hasta la fecha por las facturas. Total recaudado hasta la fecha por cada año. (año y total) Total recaudado hasta la fecha por cada mes del año. (mes-año y total) Obtener el precio del producto más costoso. Obtener el nombre del producto más costoso junto con su precio. Calcular el promedio general de las edades (en años) de los clientes. Determine que clientes (apellido y nombre) cuya edad esta por encima del

promedio general de edades de clientes. Indicar el código, nombre y apellido del vendedor y cuantas ventas (facturas) a

realizado en la empresa. indicar el código, nombre y apellido del vendedor y cuantas ventas (facturas) a

realizado en el presenté mes. Obtener el nombre del producto menos costoso junto con su precio. Determine que clientes tiene más de tres facturas. Indicar el número de la factura en la que más se recaudo junto con el nombre

del vendedor (nombre y apellido) y de cuanto fue el recaudo.



119

MODELO RELACIONAL DE LA BASE DE DATOS VIDEO TIENDA

Declare el modelo, empleando el DDL de SQL. Especifique varias consultas sumarias y subconsultas que necesite su aplicación

de base de datos en SQL. Implemente su base de datos.

Complete

1. ¿Qué función calcula el valor promedio de una expresión o columna?_______________________________

2. ¿Qué función encuentra el valor mayor en una expresión o una columna?_______________________________


Autoevaluación



120

3. ¿Qué función calcula el total de una expresión o columna?_______________________________

4. ¿Qué función encuentra el valor más pequeño en una expresión o columna?_______________________________

5. ¿Qué función cuenta el número de valores en una expresión o columna?_______________________________

6. ¿Qué cláusula selecciona los grupos de filas que construyen a los resultados deconsultas sumarias? _______________________________

7. ¿Qué cláusula genera múltiples filas de resultados, cada una resumiendo lasfilas de un grupo particular? _______________________________

8. ¿Qué Predicado comprueba si una subconsulta devuelve algún valor?_______________________________

9. ¿Qué predicado compara el valor del test con el conjunto de valores devueltopor una subconsulta? _______________________________

10.Cuando aparece una subconsulta en la cláusula _________________, losresultados de la subconsulta se utilizan para seleccionar los grupos de filas quecontribuyen con datos a los resultados de la consulta.

11.Cuando aparece una subconsulta en la cláusula _________________, losresultados de la subconsulta se utilizan para seleccionar las filas individualesque contribuyen a los datos de los resultados de la consulta.

SQL permite resumir datos de base de datos mediante un conjunto de funcionesde columna. Una función de columna SQL acepta una columna entera de datoscomo argumentos y produce un único dato que resume la columna.

Las funciones de columna pueden calcular el promedio, la suma, el valor mínimo yel valor máximo de una columna, contar el número de valores de datos de unacolumna o contar el número de filas de los resultados de la consulta.

El argumento de una función columna puede ser un solo nombre de columna opuede ser una expresión SQL.

Las consultas sumarias utilizan funciones de columna SQL para condensar unacolumna de valores en un único valor que resume la columna.

Una subconsulta es una consulta que aparece dentro de la cláusula WHERE oHAVING de otra sentencia SQL. En la cláusula WHERE, ayudan a seleccionar lasfilas individuales que aparecen en los resultados de la consulta. En la cláusula




121

HAVING, ayudan a seleccionar los grupos de filas que aparecen en los resultadosde la consulta. Las subconsultas proporcionan un modo eficaz y natural de manejarlas peticiones de consultas que se expresan en términos de los resultados de otrasconsultas.







122

BIBLIOGRAFÍA GENERAL




Date, C.J. Introdución a los sistemas de Base de Datos. Pretice Hall. 2001

Korth. Introducción a las Bases de Datos. McGrawHill. 2001

Sitios web General

Curso de SQL [en línea]. Aula Clic. Abril. 2001. [citado 4 agosto. 2007]. Disponibleen [http://www.aulaclic.es/sql/index.htm]

HERRARTE SÁNCHEZ, Pedro. Tutorial SQL [en línea]. devjoker. Octubre. 2005.[citado 4 agosto. 2007]. Disponible en[http://www.devjoker.com/asp/indice_contenido.aspx?co_grupo=csql]

Tutorial SQL [en línea]. quidel. [citado 4 agosto. 2007]. Disponible en[http://quidel.inele.ufro.cl/~pvalenzu/tutoriales/sql/sql.html]

Tutorial de SQL: Guía de Referencia de SQL [en línea]. 1keydata. [citado 4 agosto.2007]. Disponible en [http://sql.1keydata.com/es/]

Casares, Claudio. Curso de SQL [en línea]. Programación en Castellano. Enero.2003. [citado 4 agosto. 2007]. Disponible en[http://www.programacion.net/tutorial/sql/]

Manual del Usuario de PosgreSQL [en línea]. PostgreSQL. [citado 4 agosto.2007]. Disponible en [http://www.postgresql.org]

Arenas Valencia, Wilson. Problemas de mer y mere [en línea]. UniversidadTecnológica de Pereira. [citado 4 agosto. 2007]. Disponible en[http://www.utp.edu.co/~warenas/diseno.pdf]

http://www.aulaclic.es/sql/index.htm

http://www.devjoker.com/asp/indice_contenido.aspx

http://quidel.inele.ufro.cl/~pvalenzu/tutoriales/sql/sql.html

http://sql.1keydata.com/es/

http://www.programacion.net/tutorial/sql/

http://www.postgresql.org

administración de base de datos ii

Documents