Source: https://www.scribd.com/document/276504580/practicas06-pdf
Timestamp: 2018-12-14 02:58:41+00:00

Document:
Uploaded by Juan Diego Polo Cosme
prácticas 2006-07
José Clavel Cerro
Dpto. de Lenguajes y
http://www.dlsi.ua.es/asignaturas/bd1/bd1.html
sesión 0_______________________________________________ 5
select 1 ______________________________________________ 11
select 2 ______________________________________________ 23
select 3 ______________________________________________ 29
select fecha___________________________________________ 35
conjuntos ____________________________________________ 39
funciones_____________________________________________ 45
group by - having ______________________________________ 59
subselect _____________________________________________ 65
subselect - exists ______________________________________ 71
adicionales 1 __________________________________________ 75
adicionales 2 __________________________________________ 77
adicionales 3 __________________________________________ 79
adicionales 4 __________________________________________ 81
adicionales 5 __________________________________________ 83
adicionales 6 __________________________________________ 85
adicionales 7 __________________________________________ 87
lo que haremos (en este caso en particular) será crear una base de datos que englobe tres tablas: PROFESORES. si queremos mantener mediante un gestor de bases de datos información docente. créditos : decimal(3. Ayuda en línea. o más comúnmente tablas. Tipos de datos. y está compuesta por relaciones. asignatura : char(5) ) Clave primaria: (dni. que representan los correspondientes atributos de la entidad o claves ajenas que permiten relacionar varias tablas entre sí. categoría : char(4). Base de Datos: Ejemplo PROFESORES ( dni : char(9). Concepto de Base de Datos y Relación (Tabla) Una base de datos es un conjunto de información interrelacionada que representa un sistema de información particular. fechainc : date ) Clave primaria: dni ASIGNATURAS ( código : char(5).1). La BD que gestione esta información se llamará Ejemplo. Así. ASIGNATURAS e IMPARTE. Selección de Base de Datos. y las tablas contenidas en ella se presentan en el siguiente cuadro. que almacenan los datos referentes a un objeto o a una interrelación entre objetos.sesión 0 MANEJO DE SQL WORKSHEET Objetivos: • Adquirir la destreza mínima para trabajar en el entorno ORACLE SQL WORKSHEET Contenidos • • • • • • • Concepto de Base de Datos y Tabla. créditosp : decimal(3.1) ) Clave primaria: código IMPARTE ( dni : char(9). nombre : char(25). asignatura) Clave ajena: dni → PROFESORES Clave ajena: asignatura → ASIGNATURAS 5 . Cada tabla tendrá sus columnas. Salvar y recuperar órdenes SQL. Manejo de menús. Entrar en ORACLE SQL Worksheet. descripción : char(40).
cuando la pantalla muestre el mensaje lilo: teclear la palabra windows 2.0 9. Dicho gestor está instalado en un servidor al que se accede por red desde un cliente Windows.Extensiones de Ejemplo: ASIGNATURAS código descripción créditos créditosp HI FBD DGBD PC FP 4. Oracle.5 3.5 6. Oracle 9. 1. 3. Una vez aparezca en la pantalla de entrada a Windows introduciremos los datos de usuario asignados por la EPS2 Cómo ejecutar SQL WORKSHEET.0 6. SQL Worksheet. 2 Este proceso puede cambiar de unas aulas a otras. . Arrancar el ordenador y.5 PROFESORES IMPARTE dni nombre categoría fechainc dni asignatura 21111222 21222333 21333444 EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO TEU TEU ASO6 21111222 21111222 21333444 FBD DGBD PC 01/10/1993 16/06/1989 16/06/1992 Ejecutar SQL Worksheet Esta primera sesión es de contacto con el sistema Oracle 1 y la herramienta cliente SQL Worksheet. Oracle es un sistema de gestión de bases de datos relacional.0 6.5 4. El primer diálogo que aparece en la pantalla nos pedirá información sobre: Usuario: alumno Contraseña: alumno Servicio: oracle Conectar como: normal 6 1 Actualmente. pinchar el botón de Inicio y elegir las opciones Programas. En el caso de que no existiera el acceso directo en el escritorio. Pulsando con la tecla izquierda del ratón dos veces sobre él conseguiremos ejecutar la herramienta cliente.0 1. En el escritorio nos encontraremos con un icono de acceso directo al programa SQL Worksheet. Como entrar al sistema.0 HISTORIA DE LA INFORMATICA FUNDAMENTOS DE LAS BASES DE DATOS DISEÑO Y GESTION DE BASES DE DATOS PROGRAMACION CONCURRENTE FUNDAMENTOS DE LA PROGRAMACION 1.
de arriba a abajo: • Conectar a un servidor • Ejecutar la instrucción SQL escrita actualmente • Historial de comandos • Instrucción anterior • Instrucción posterior • Ayuda (depende de la instalación) • Repaso rápido (depende de la instalación) En el menú Archivo. En la pantalla de introducción de datos podemos observar la pantalla dividida en dos: • Parte superior de la pantalla: aquí se escriben y almacenan temporalmente las instrucciones SQL • Parte inferior de la pantalla: muestra los resultados de la ejecución de una instrucción Además. tenemos una barra de botones que incluye las siguientes funciones. elegir la opción Salir. Algunos de los tipos de datos que nos podemos encontrar en ORACLE son: VARCHAR2(n) Cadena de caracteres de longitud variable con un máximo de n CHAR(n) LONG NUMBER(p. y código de asignatura.El cliente entrará en la pantalla de edición con la base de datos PROVEEDORES 3 seleccionada. dado que. Rango válido desde 1 de enero de 4712 AC hasta el 31 de diciembre de 4712 DC. o 231-1 bytes Números con precisión p y escala s (1<=p<=38) (-84<=s<=127) Cadena de caracteres binarios de longitud n (1<=n<=2000) Cadena de caracteres binarios de longitud variable hasta 2 gigabytes Datos de tipo fecha. con la forma dd/mm/yyyy(día. para la relación entre imparte y profesor. tales atributos son “comunes” porque el dominio es el mismo para ambos y se pueden comparar. podemos encontrar la opción “Guardar entrada como”. sin embargo. en la parte izquierda. La base de datos PROVEEDORES será la base de datos utilizada durante todo el desarrollo de las prácticas de BD1. como veremos en próximas sesiones. En el menú Archivo. Tipos de datos En general. además. puesto que sólo podremos comparar columnas con idéntico tipo de datos. Los valores date deben manejarse encerrados entre comillas simples. que en la tabla imparte el código de asignatura se llama asignatura y en la tabla asignaturas código. Cómo abandonar SQL WOKSHEET. Nótese. Los tipos de datos que acompañan en el esquema de BD a cada columna en cada tabla determinan los valores que pueden tomar éstas. en este caso dni de profesor.s) RAW(n) LONG RAW DATE 3 (1<=n<=4000) Cadena de caracteres de longitud fija de longitud n (1<=n<=2000) Cadena de caracteres de longitud variable hasta 2 gigabytes. mes y año). la utilización de varias tablas necesita que ellas se puedan relacionar por una columna común. Manejo de SQL WORKSHEET. o a la hora de manipular datos. que nos permite grabar la orden SQL (parte superior). listo ya para editar y ejecutar órdenes SQL. para la relación entre asignatura e imparte. Los dominios vienen definidos por los tipos de datos que ofrece el SGBD. La opción “Guardar salida como” hace lo propio con el resultado de la ejecución (parte inferior). cada tipo de datos presenta unos requisitos específicos para su manipulación. 7 . En realidad. Son de capital importancia a la hora de relacionar tablas en una sentencia select.
com/ En general. sólo se ejecutan una vez. VARCHAR2. Otras informaciones disponibles en el catálogo del sistema (el comando DESCRIBE se basa en algunas de las tablas que se utilizan aquí) son: select distinct table_name. COLUMN_NAME. DATA_LENGTH.oracle. select OWNER. DATA_PRECISION. en este caso. es bastante fácil encontrar información en internet por medio de cualquier buscador. RAW. se puede dar formato a las columnas con las siguientes órdenes. (es permanente para toda la sesión. tipo de dato de la columna. y la precisión de la columna si el tipo de dato lo requiere). Direcciones con ayuda Oracle disponible: • www. o LONG RAW. column_name from all_cons_columns where OWNER='BD1TABLAS'. DATA_SCALE from all_tab_columns where OWNER='BD1TABLAS'. NUMBER. LONG. Obtener información sobre una tabla de la BD Ejecutar DESC nombreTabla o DESCRIBE nombreTabla para mostrar información de la tabla nombreTabla: ( el nombre de cada columna. más de una línea) SET PAGESIZE 200 (para evitar cabeceras tantas cabeceras) 8 . DATE.com • www. DATA_TYPE. TABLE_NAME. CHAR. select * from all_cons_columns where OWNER='BD1TABLAS'. si los valores nulos se permiten o no (NULL or NOT NULL) en esa columna. incluída la cabecera.redcientifica. por ejemplo. Donde BD1TABLAS es un usuario (el propietario. hay que volver a ejecutarlas con las modificaciones oportunas) COLUMN COLUMN COLUMN COLUMN COLUMN COLUMN OWNER FORMAT a20 CONSTRAINT_NAME FORMAT A20 TABLE_NAME FORMAT A15 COLUMN_NAME FORMAT A20 POSITION FORMAT 99 DATA_TYPE FORMAT A15 Y también: SET LINESIZE 600 (para evitar que la filas de salida. de la BD PROVEEDORES) Para que los datos se muestren en pantalla sin saltos de línea. si se quiere otro formato.INFORMACIÓN ADICIONAL Ayuda en línea. ocupen .
si se quieren guardar datos con seguridad es preferible hacerlo en disco flexible. No obstante. no se garantiza que los datos ahí guardados permanezcan de una sesión para otra. Por lo tanto.Sobre guardar órdenes en disco En la máquina se dispone de un disco duro local que se puede utilizar para guardar y recuperar ficheros generados por el alumno. Para mantener los ficheros de una semana a otra recomendamos el uso del espacio en disco de la EPS. 9 .
Las extensiones de cada relación se incluyen como ayuda para comprobar la corrección de las órdenes select utilizadas. ya creada y a disposición del alumno. El resto de la sesión practicará con el primer grupo de requerimientos propuestos. • Comentar el esquema lógico propuesto. y los resultados que puede obtener.select 1 SQL . • Tipos de datos.SELECT Objetivos: • Introducir al alumno en el SQL y la orden SELECT. • La orden SELECT-FROM-WHERE. También se proporciona información sobre las consultas más sencillas a realizar sobre una BD. • Proyecciones. Contenidos: • Sistema de información propuesto. Se detalla el esquema lógico que describe la BD de PROVEEDORES. cuyo objetivo es familiarizarle con el entorno de trabajo SQL WorkSheet de ORACLE. intentando que comprenda su significado. Selecciones. 11 . El esquema lógico es la referencia que permite construir las órdenes select con las que interrogar a la BD. el sistema real que pretende representar. • ORDER BY • Consultas 1.
• Catálogo de venta (pieza) Las piezas que distribuye nuestra empresa y el precio de venta al público de las mismas. preciocompra NUMBER(9. las tareas que se pretenden mecanizar son las siguientes (entre paréntesis aparecen las tablas directamente relacionadas con cada una): • Lista de suministradores (vendedor).2). nomvend VARCHAR2(30). fecharecep DATE. cantrecibida NUMBER(4)) Clave Primaria: (numpedido. numbin NUMBER(10).2). nompieza VARCHAR2(30).LA BASE DE DATOS PROVEEDORES TABLA VENDEDOR COLUMNAS ( numvend NUMBER(4). descuento NUMBER(2)) Clave Primaria: (numpieza. Los datos de los proveedores que nos suministran la mercadería que. cantdisponible NUMBER(5). numpieza VARCHAR2(16). numlinea) Clave Ajena: (numpedido)→ PEDIDO Clave Ajena: (numpieza)→ PIEZA INVENTARIO ( numpieza VARCHAR2(16). provincia VARCHAR2(20) ) ( numpieza VARCHAR2(16). 12 . numvend NUMBER(4). cantpedida NUMBER(4). posteriormente. numvend) Clave Ajena: (numpieza)→ PIEZA. Se compran (tablas PEDIDO y LINPED) ciertas mercancías a los distintos proveedores (tabla VENDEDOR) y son vendidas posteriormente al público o a otros distribuidores (que no hemos considerado en la BD) Básicamente. preciovent NUMBER(9. fecharecuento DATE. numvend NUMBER(4). periodorecuen NUMBER(2). es vendida al público en general.2)) RESTRICCIONES Clave Primaria: (numvend) PRECIOSUM ( numpieza VARCHAR2(16). Clave Ajena: (numvend)→ VENDEDOR PEDIDO ( numpedido NUMBER(5). diassum NUMBER(3). telefono CHAR(11). preciounit NUMBER(9. ciudad VARCHAR2(20). numlinea NUMBER(2). nombrecomer VARCHAR2(30). cantminima NUMBER(5) ) Clave Primaria: (numbin) Clave Alternativa: (numpieza) Clave Ajena: (numpieza)→ PIEZA PIEZA Clave Primaria: (numpieza) La base de datos pretende reflejar la política de compras de una empresa de distribución. calle VARCHAR2(30). fecha DATE ) Clave Primaria: (numpedido) Clave Ajena: (numvend)→ VENDEDOR LINPED ( numpedido NUMBER(5).
13 . donde cada entrada. que hayan sido comprados a un precio distinto porque se negociara en ese instante con cualquiera de los suministradores. donde un conjunto de líneas pertenecientes a un mismo pedido se numeran desde la número 1 en adelante. pieza). controlar si se han servido en el tiempo estimado y en la cantidad pedida. Sólo aquellas piezas que aparezcan en una línea de pedido han sido solicitadas al correspondiente vendedor y. donde se especifica qué vendedor nos ha suministrado el pedido completo. Cada pedido consta de: • Cabecera de pedido (tabla pedido). Contiene el código de pieza que sirvió el proveedor. si se ha hecho. pieza). la cantidad que se le pidió y la cantidad que realmente ha servido a nuestra empresa y la fecha en la que se recibió. • Control de existencias (inventario. • Líneas de pedido (tabla linped).• Lista de precios de suministro (preciosum. De aquellas mercancías que se solicitan a los proveedores. • Control de pedidos (pedido. Mediante la confección de un inventario. vendedor. vendedor. linped. No se debe confundir esta información con la de los pedidos: los artículos almacenado en preciosum puede que no se hayan pedido nunca y. es el recuento real de existencias. así como el precio al que se le compro (puede ser diferente al estipulado en preciosum). si la cantidad recibida es mayor que cero. Es información histórica cuyo origen no nos preocupa. que corresponde a un único artículo. y la fecha en que se realizó el pedido. pieza). habrán sido recibidas por la empresa en esa cantidad. Conocer los precios a los que los proveedores nos podrían suministrar las piezas.
A.A.A. 1000. HARW S. OXFORD BLUES QUINTANAR DE LA ORDE NEW ORLEANS RIVAS VACIAMADRID RIVAS VACIAMADRID LOUISSIANA MADRID MADRID 98-0101010 MOROS. FRANCISCO DE ASIS. 5660788 MAYOR. 3667788 COLON. 3334455 CISCAR.00 4.A. S. 54 ALICANTE ALICANTE SEVESOFT 5779988 GENERAL LACY.L. 44 SAN VICENTE ALICANTE ASX.00 18. Valencia 3205 ALICANTE ALICANTE HARW S.00 . 64 SAN VICENTE ALICANTE tabla PIEZA numpieza A-1001-L C-1002-H C-1002-J C-400-Z DD-0001-210 DD-0001-30 DK144-0001 DK144-0002-P FD-0001-144 FD-0002-720 M-0001-C M-0002-C M-0003-C O-0001-PP O-0002-PP P-0001-33 T-0001-IBM T-0002-AT T-0003-AT X-0001-PC 14 TOLEDO nompieza MOUSE ADL 3B FILTRO PANTALLA X200 DISCO DURO WESTERN DIG 210M 28 DISCO DURO 30M SEAGATE DISKETTE 1.00 200.00 350. TABAC & SOFT 5661100 MAYOR. 98-5696969 ARZOBISPO LOACES 999-2014455 BANESTOESSOFT S. LA MEJOR S.A.A.00 400. 3 I SAN VICENTE ALICANTE DONDEQUIERAS.tabla VENDEDOR numvend 1 2 3 4 5 6 7 8 9 10 11 12 13 8001 8002 8003 100 101 102 200 55 201 nomvend AGAPITO LAFUENTE DEL CORRAL LUCIANO BLAZQUEZ VAZQUEZ GODOFREDO MARTIN MARTINEZ JUANITO REINA PRINCESA JUANITO REINA PRINCESA MANOLO PIEDRA POMEZ MANUEL PEREZ RODRIGUEZ LUISA PINTO HEREDIA CHEMA PAMUNDI GUSTAVO DE BASICA MARIO DUQUE LIZONDO JOSE ANTONIO MARTINEZ JUAN MANUEL GOMEZ SANTISTEBAN JUAN RODRIGUEZ JUAN JUAN MARTINEZ GARCIA LUIS RODRIGUEZ SALA PEDRO GRACIA MORALES SALVADOR PLA GARCIA SOLEDAD MARTINEZ ORTEGA SEVERINO MARTIN MARTINEZ LUIS GARCIA SATORRE MANUEL ORTUÑO LAFUENTE nombrecomer telefono calle Ciudad provincia MECEMSA 96-5782401 Avda.44 IBM FLOPPY 720K IBM MONITOR SYNCMASTER 3 COLOR MONITOR COLOR SONY BT MONITOR IBM 3570 COLOR PEGATINAS CONCIERTO JEVI PACK PEGATINAS CONCIERTO JEVI PLACA INTEL 33Mz TECLADO XT IBM TECLADO AT SUSUSU TECLADO AT HP TECLADO ESTANDAR PC preciovent 7.00 120.A. 96-5660727 AVIACION 92.00 7. S. 3335588 SALAMANCA. 5889944 POETA ALONSO. S. 17 ALICANTE ALICANTE HARW S. 13F S.00 170.44 PANASONIC PACK DISKETTE 144 PANASONIC FLOPPY 1. 21 VALENCIA VALENCIA OLE ESPAÑA. 21 VALENCIA VALENCIA ALMORADI ALICANTE HALA S. 100 VALENCIA VALENCIA SOFT S. 5 VALENCIA VALENCIA HARW S.00 350. VALENCIA 3372 ALICANTE ALICANTE HARW S.00 1. 15 2 B ALICANTE ALICANTE MECEMSA 96-4141722 AVDA. OLE ESPAÑA.00 20.00 100. S.A. OLE ESPAÑA.00 250.00 180.A.10 10.00 70.00 110.A.A.00 55.00 150.A. 19 GIJON ASTURIAS OLE ESPAÑA. 87879998 PEREZ GALDOS.A. 12 ALICANTE ALICANTE HALA S. 10 1 SOFTHARD DISTRIBUIDORA S.A. 903-696969 LO ANGELE LOS EU'S LA DEAQUI 98-5363636 GIJON ASTURIAS HUMP S.A. 102 VALENCIA VALENCIA SALAMANCA. S. 3667789 COLON. 96-3232321 GENERAL LACY.A.
00 210.00 15.00 15.00 146.00 cantpedida 10 20 20 20 22 100 1 15 3 10 15 1000 2000 45 fecharecep 10/05/1992 10/05/1992 10/05/1992 10/05/1992 17/10/1992 15/10/1992 15/10/1992 17/10/1992 17/10/1992 17/10/1992 11/06/1993 25/08/1995 25/08/1995 09/10/1992 cantrecibida 10 18 20 20 22 101 1 15 3 10 13 1000 1998 8 tabla INVENTARIO numpieza DD-0001-30 P-0001-33 O-0002-PP M-0001-C M-0003-C DD-0001-210 FD-0001-144 numbin 1 2 3 4 5 6 7 cantdisponible 120 10 110 15 2 10 10 fecharecuento 15/10/1990 15/10/1992 15/10/1992 15/10/1992 20/10/1992 12/11/1992 12/11/1992 periodorecuen 1 1 2 1 2 2 cantminima 15 5 3 2 0 1 0 15 .00 7.00 31.00 99.00 135.tabla PEDIDO numpedido 1 2 3 4 5 6 7 numvend 1 1 2 2 1 5 8002 fecha 05/05/1992 11/10/1992 15/10/1992 16/10/1992 22/10/1992 22/08/1995 02/10/1992 tabla LINPED numpedido 1 1 1 1 1 2 2 3 3 4 5 6 6 7 numlinea 1 2 3 4 5 1 2 1 2 1 1 1 2 1 numpieza M-0001-C P-0001-33 FD-0001-144 DD-0001-210 T-0002-AT DK144-0002-P T-0002-AT DD-0001-210 P-0001-33 O-0002-PP T-0002-AT O-0001-PP O-0002-PP C-400-Z preciocompra 300.45 30.00 150.00 99.00 210.00 5.
50 8.00 250.00 77.00 280.00 2.00 120.56 5.00 170.00 90.00 0.00 4.00 136.00 120.00 95.00 155.00 99.00 280.00 75.60 5.00 19.tabla PRECIOSUM numpieza A-1001-L A-1001-L A-1001-L A-1001-L C-1002-H C-1002-J C-400-Z C-400-Z DD-0001-210 DD-0001-210 DD-0001-210 DD-0001-30 DK144-0001 DK144-0002-P DK144-0002-P FD-0001-144 FD-0001-144 FD-0001-144 FD-0002-720 M-0001-C M-0001-C M-0002-C M-0002-C M-0003-C M-0003-C M-0003-C O-0001-PP O-0001-PP O-0001-PP O-0002-PP O-0002-PP O-0002-PP O-0002-PP P-0001-33 P-0001-33 P-0001-33 P-0001-33 P-0001-33 T-0001-IBM T-0001-IBM T-0001-IBM T-0002-AT T-0002-AT T-0002-AT T-0002-AT T-0002-AT T-0002-AT T-0003-AT T-0003-AT 16 numvend 3 4 100 1 1 1 1 8002 1 2 101 1 1 1 2 1 102 55 1 1 3 9 1 3 4 1 5 55 1 2 5 101 1 2 1 4 3 5 2 100 1 1 2 4 5 100 201 1 3 preciounit 5.00 180.00 90.00 98.00 250.50 1.00 34.50 81.00 15.00 280.50 7.75 80.00 200.00 33.45 diassum 1 1 3 3 2 2 4 3 3 5 15 4 3 3 5 3 3 10 3 3 7 1 10 2 7 7 1 7 1 1 1 10 1 5 3 7 2 3 5 5 15 3 5 7 3 2 1 3 descuento 5 15 12 14 7 13 10 15 5 15 15 12 10 10 7 5 5 .00 300.00 150.00 350.50 15.00 35.00 140.90 4.00 25.50 130.00 0.00 60.00 30.00 210.00 30.00 150.
formada por dos cláusulas. en primer lugar. En este momento veremos la expresión mínima de la orden. Supongamos que sobre la base de datos Ejemplo queremos obtener todos los datos acerca de los profesores.LA ORDEN SELECT-FROM-WHERE Sintaxis general de la orden select SELECT [ DISTINCT ] listaColumnas FROM listaTablas [ WHERE condición ] [ GROUP BY listaColumnas [ HAVING condición ] ] [ ORDER BY listaColumnas [ ASC | DESC ] ] Select-From Para realizar consultas sobre una base de datos vamos a utilizar la orden SELECT de SQL. que obligatoriamente tendremos que especificar en cada consulta que realicemos. categoría from profesores resultado: nombre EVA GÓMEZ MANUEL PALOMAR RAFAEL ROMERO categoría TEU TEU ASO6 17 . Si deseamos conocer a qué categorías pertenecen los profesores que se encuentran en la BD: select categoría from profesores resultado: categoría TEU TEU ASO6 Podemos especificar tantas columnas como queramos: select nombre. Con la sintaxis que se muestra en el punto anterior seremos capaces de formular cualquier requerimiento (consulta) sobre las tablas que componen una determinada BD. seleccionar la base de datos. Debemos. y ejecutamos (en el Query-language) la siguiente orden select * from profesores resultado: dni 21111222 21222333 21333444 nombre EVA GÓMEZ MANUEL PALOMAR RAFAEL ROMERO categoría TEU TEU ASO6 Al especificar en la lista de atributos un asterisco le indicamos al SGBD que deseamos la información de todas las columnas definidas para la tabla PROFESORES. select y from.
puesto que la duplicación se refiere a filas completas y no a una columna en particular: select distinct dni. los operadores de comparación >. categoría from profesores resultado: dni 21111222 21222333 21333444 categoría TEU TEU ASO6 La cláusula WHERE Con la orden select-from obtenemos la información de las columnas requeridas de toda la tabla. Pretendemos obtener el nombre de los profesores titulares: select nombre from profesores where categoría = 'TEU' resultado: nombre EVA GÓMEZ MANUEL PALOMAR En la construcción de tales condiciones podemos utilizar las conectivas lógicas AND. <> Nombre de los profesores que son titulares o asociados a 6 horas: select nombre from profesores where categoría = 'TEU' or categoría = 'ASO6' resultado: 18 nombre EVA GÓMEZ MANUEL PALOMAR RAFAEL ROMERO . OR.Para evitar la salida de filas duplicadas podemos utilizar el modificador DISTINCT: select distinct categoría from profesores resultado: categoría TEU ASO6 Nótese. sin embargo. que las dos órdenes siguientes obtienen el mismo resultado. >=. <. categoría from profesores select dni. También. así como los paréntesis para alterar la evaluación de izquierda a derecha. <=. Si únicamente queremos información de aquellas filas que cumplen una determinada condición utilizaremos la cláusula where. y NOT.
5 créditos tiene 6 créditos tiene 6 créditos tiene 6 créditos tiene 9 créditos 19 . ' tiene '. descripción from asignaturas order by créditos.5 créditos: select créditos. descripción resultado: créditos 4. descripción from asignaturas where créditos > 4. descripción. créditos.La cláusula ORDER BY Podemos ordenar la salida producida por nuestra orden select por valores ascendentes o descendentes de una columna en particular. ' créditos' from asignaturas order by créditos resultado: 'laasignatura' La asignatura La asignatura La asignatura La asignatura La asignatura descripción HISTORIA DE LA INFORMATICA FUNDAMENTOS DE LAS BASES DE DATOS DISEÑO Y GESTION DE BASES DE DATOS PROGRAMACION CONCURRENTE FUNDAMENTOS DE LA PROGRAMACION 'tiene' créditos 'créditos’ tiene 4. El mismo requerimiento anterior pero en orden descendente y de aquellas que tienen más de 4.5 order by créditos desc resultado: créditos 9 6 6 6 descripción Fundamentos de la Programación Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Programación Concurrente Puede aplicarse más de un criterio de ordenación: select créditos.5 6 6 6 9 descripción Historia de la Informática Diseño y Gestión de Bases de Datos Fundamentos de las Bases de Datos Programación Concurrente Fundamentos de la Programación Constantes Se pueden explicitar constantes en la orden select de forma que dicho valor aparezca en todas las filas: select 'La asignatura '. Nombre de las asignaturas ordenadas de menor a mayor número de créditos: select créditos.5 6 6 6 9 descripción Historia de la Informática Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Programación Concurrente Fundamentos de la Programación Si no se indica nada la ordenación será ascendente. descripción from asignaturas order by créditos resultado: créditos 4.
Nombre de los vendedores con número de vendedor menor que 6. 5. 7. 3. ordenados de mayor a menor precio de compra. Números de pedido y números de vendedor. 2. Números de vendedores y días que tardarían en suministrar la pieza 'P-0001-33' 12. 14. Obtener todos los números de piezas de las piezas de la base de datos. Modificar el requerimiento anterior para eliminar duplicados. de posible suministrador y precio de suministro. ordenadas de menor a mayor precio. ordenados por código de vendedor y código de pieza. 11. Obtener todos los números de los vendedores de los que se sepa que pueden suministrar alguna pieza. 15. 9. 6. Número. Códigos de pieza de los que se sabe que algún vendedor nos podría hacer descuento. 8. en orden descendente del valor de descuento. obtener el número de pieza y el descuento.CONSULTAS SELECT1 1. Para cada pieza de la que se conozca algún suministrador. Nombre y empresa de los vendedores de la Comunidad Valenciana. para los pedidos solicitados el 15 de octubre de 1992. Vendedores de la provincia de Alicante. Códigos de pieza solicitados en el pedido 1. 4. Nombre de todas las piezas con un precio de venta menor que 1000. nombre y precio de venta de las piezas de precio de venta mayor que 10 o menor que 1 euros. 10. 20 . Número y nombre de los vendedores con número de vendedor menor que 6. 13. Códigos de pieza.
nompieza.numpieza/PIEZA(P)} --2 select nompieza from pieza where preciovent < 1000 En AR PIEZA donde preciovent<1000[nompieza] En CRT {P. En CRT se consideran declaradas correctamente todas las variables utilizadas.preciovent<10000} --4 select numpieza.SOLUCIONES SELECT1 Muchas de las soluciones también se muestran en álgebra relacional (AR) y cálculo relacional de tuplas (CRT).numvend. descuento from preciosum order by descuento desc En AR PRECIOSUM [numpieza.nompieza / PIEZA(P) ∧ P. por lo tanto todas las soluciones que se muestran aquí corresponden al enunciado al que se esté dando respuesta.nompieza. P. nomvend from vendedor where provincia = 'ALICANTE' --10 select nomvend. V. nompieza. --1 select numpieza from pieza En AR PIEZA[numpieza] En CRT {P.numvend<6} --8 select distinct numvend from preciosum --9 select numvend. En AR y CRT los resultados son relaciones. descuento] En CRT {P.nomvend / VENDEDOR(V) ∧ V.descuento / PRECIOSUM(P)} --5 select nomvend from vendedor where numvend < 6 --6 select distinct nomvend from vendedor where numvend < 6 En AR VENDEDOR donde numvend<6 [nomvend] En CRT {V. que son temas que se tratarán más adelante en las clases de teoría.nomvend / VENDEDOR(V) ∧ V.preciovent / PIEZA(P) ∧ P. nomvend] En CRT {V.numpieza.numvend<6} --7 select numvend. pero eliminando duplicados.preciovent] En CRT { P.preciovent>1000 ∧ P. P. preciovent from pieza where preciovent > 10 or preciovent < 1 order by preciovent (la ordenación no se puede expresar ni En CRT ni En AR) En AR PIEZA donde preciovent>10 o preciovent<1 [numpieza.preciovent<1000} --3 select numpieza. nomvend from vendedor where numvend < 6 En AR VENDEDOR donde numvend<6 [numvend. P.nompieza. nombrecomer from vendedor where provincia = 'ALICANTE' or provincia = 'CASTELLON' 21 .
numpieza. preciounit from preciosum order by numvend. diassum from preciosum where numpieza = 'P-0001-33' --12 select numpieza from linped where numpedido = 1 order by preciocompra desc --13 select numpedido. numvend from pedido where fecha = '15/10/1992' --14 select distinct numpieza from preciosum where descuento is not null --15 select numvend.or provincia = 'VALENCIA' --11 select numvend. numpieza 22 .
dni and asignatura = código resultado: nombre EVA GÓMEZ EVA GÓMEZ RAFAEL ROMERO descripción Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Programación Concurrente En primer lugar. por clarificar el mecanismo por el que se obtiene este resultado. imparte. necesitamos involucrar a las tres tablas al mismo tiempo. en la columna nominada como asignatura. Si encuentra tal valor. asignaturas. y el segundo en la de asignaturas. descripción from asignaturas. para obtener la información que precisamos.select 2 CONSULTAS SOBRE VARIAS TABLAS Objetivos: • Poder relacionar distintas tablas de la BD para obtener información más compleja. podemos preguntar por el nombre de los profesores y la descripción de las asignaturas que imparten. y busca el valor de dni de cada una en la tabla imparte. Si seleccionamos la BD Ejemplo. aparecerá el código de asignatura. que asocia dni de profesor con código de asignatura que imparte. profesores where profesores. que relaciona las claves primarias de cada una de las tablas asociadas por tal relación. El primer atributo se encuentra en la tabla profesores. select nombre. obtendrá la descripción y finalmente mostrará en pantalla el nombre y la descripción que le corresponde. Contenidos • Utilización de más de una tabla.dni = imparte. tupla por tupla. 23 . solicitamos nombre (del profesor) y descripción (de la asignatura). esta información se encuentra almacenada en la tabla imparte. La forma de especificar qué tablas vamos a consultar es construir una lista de nombres de tablas en la cláusula FROM. • Resolución de requerimientos UTILIZACIÓN DE MÁS DE UNA TABLA Para la resolución de la mayoría de requerimientos es necesario trabajar con información que se obtiene de relacionar varias tablas. • Sinónimos temporales de tabla. Podemos pensar. Así. en esa tupla de imparte en que lo ha encontrado. • Nombres cualificados de atributo. • Tipos de datos: dominios. que el SGBD recorre la tabla de profesores. buscando en la tercera tabla. La relación entre ambas tablas se encuentra en la tabla imparte.
dni resultado: asignatura FBD DGBD PC nombre EVA GOMEZ EVA GOMEZ RAFAEL ROMERO El SGBD primero combinaría todas las tuplas con todas de las tablas especificadas en el from: dni 21111222 21222333 21333444 21111222 21222333 21333444 21111222 21222333 21333444 4 nombre EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO categoria TEU TEU ASO6 TEU TEU ASO6 TEU TEU ASO6 fechainc 01/10/1993 16/06/1989 16/06/1992 01/10/1993 16/06/1989 16/06/1992 01/10/1993 16/06/1989 16/06/1992 dni 21111222 21111222 21111222 21111222 21111222 21111222 21333444 21333444 21333444 asignatura FBD FBD FBD DGBD DGBD DGBD PC PC PC Esto no es necesariamente real. Si en la cláusula from pusiéramos. Veamos en detalle como funciona la orden select-from-where para el caso de la siguiente consulta (códigos de asignaturas y nombre de los profesores que las imparten) 4 : select asignatura. nombre from profesores. el resultado final tendría 3 x 3 x 5 = 45 tuplas. o la asignatura HISTORIA DE LA INFORMÁTICA.dni = imparte. además.Nótese que no aparece MANUEL PALOMAR. imparte where profesores. ¿Qué pasaría si no utilizáramos la cláusula where para enlazar las tablas? Supongamos el siguiente requerimiento: select asignatura. ni la segunda es impartida por ningún profesor (de los conocidos por nuestro sistema). imparte resultado: asignatura FBD FBD FBD DGBD DGBD DGBD PC PC PC nombre EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO EVA GOMEZ MANUEL PALOMAR RAFAEL ROMERO Si no indicamos nada en la cláusula where la información que obtenemos es simplemente la combinación de cada tupla con todas las demás: si hay 3 tuplas en imparte y otras 3 en profesores. 24 . una de las ventajas de utilizar un SGBD es que las consultas se procesan de manera eficiente y de forma totalmente transparente para el usuario. puesto que ni el primero (en nuestra BD) imparte asignatura alguna. la cardinalidad de la relación resultante es 3 x 3 = 9 tuplas. la tabla asignaturas. nombre from profesores.
en este caso. buscaremos en imparte los dni de los profesores (son los que tienen al menos una asignatura asignada). y nos quedaría: dni 21111222 21111222 21333444 nombre EVA GOMEZ EVA GOMEZ RAFAEL ROMERO categoria TEU TEU ASO6 fechainc 01/10/1993 01/10/1993 16/06/1992 dni 21111222 21111222 21333444 asignatura FBD DGBD PC Y por último. imparte where profesores. La forma de evitar tuplas idénticas ya se mostró en la sesión anterior. no es necesario utilizar el nombre de la tabla. al comparar los dni que aparecen en profesores y en imparte hemos utilizado el nombre de la tabla a la que pertenecen cada uno. y con este valor obtendremos el nombre en profesores. de qué tabla): select profesores.dni = imparte. nombre from profesores. deberemos especificar en todo momento la tabla de la que queremos extraer la información. nombre from profesores. profesores. Caso de no existir ambigüedad. select dni.dni = imparte. y es por eso que aparece duplicada en este resultado.dni resultado: dni 21111222 21111222 21333444 nombre EVA GÓMEZ EVA GÓMEZ RAFAEL ROMERO Se recuerda que la ocurrencia EVA GÓMEZ está relacionada con dos asignaturas.Por lo especificado por la where eliminaría aquellas tuplas que no cumplieran la condición. nombres alternativos de las tablas. Si deseamos conocer el dni y el nombre de los profesores que imparten alguna asignatura. Utilizaremos el nombre completo de la columna (es indiferente. SINÓNIMOS TEMPORALES DE TABLA Para facilitar la escritura de las sentencias select (entre otros posibles motivos) se pueden utilizar alias temporales. ni asignatura ni código se utilizan en otras tablas.dni.dni resultado: ERROR Al especificar que nos muestre en pantalla el dni del profesor. Se especifican en la lista de tablas del from.” que separa el nombre de la tabla en cuestión de la siguiente. antes de la “. Si en el conjunto de columnas de todas las tablas que se especifican en la cláusula from existen varias con nombres iguales. 25 . las columnas especificadas en la select determinarían el resultado final: asignatura nombre FBD DGBD PC EVA GOMEZ EVA GOMEZ RAFAEL ROMERO NOMBRES CUALIFICADOS DE ATRIBUTO Si observamos la sentencia select anterior. el SGBD entra en conflicto puesto que no sabe si nos referimos al de la tabla profesores o al de la tabla imparte.dni e imparte.dni. imparte where profesores.
select distinct p.dni resultado: dni 21111222 21333444 26 nombre EVA GÓMEZ RAFAEL ROMERO .dni = i. nombre from profesores p.dni. imparte i where p.
22. nombre y precio de venta de las piezas que han sido compradas en un pedido servido por el vendedor 1. 19. 15. Obtener los números de pieza de las que conozcamos algún vendedor que nos la pueda suministrar. Precios a los que nos pueden ser suministradas las piezas DD-0001-210 y FD-0001-144. 8. Obtener el número de pieza junto con el nombre de todas las provincias desde las que puede sernos suministrada. y ciudad del vendedor que puede suministrarnos piezas con valor mayor de 100. Obtener todas las piezas que se recuenten el 15 /10/1992. 13. 6. en orden descendente del número de pieza. Obtener los números de pedido del vendedor número 1. teléfono. Pedidos y datos del vendedor cuya fecha de pedido no sea el 22 de octubre de 1992. 20. 5. 7. y pieza que ha sido comprada a un precio mayor que el estipulado en la lista de precios de suministro. 18. 27 . Nombres de proveedores que puedan suministrarnos la pieza numero A-1001-L 11. Lista el nombre y número de las piezas. 10. 16. y número y nombre de los vendedores que las podrían suministrar a esos precios. Modificar el requerimiento anterior para eliminar duplicados. Número y nombre de las piezas que puedan suministrarnos el vendedor número 2 y el 4 (no necesariamente que las puedan suministrar los dos). Piezas que nos puedan suministar los vendedores de la empresa Harw S. 2. Número. 12.A. Obtener el nombre y número de proveedores de la provincia de Valencia. Ídem en orden ascendente. Obtener los vendedores que pueden suministrarnos piezas con un descuento de más de 10%. Obtener el nombre y número de proveedores de la provincia de Valencia a los que se les ha solicitado un pedido. 4. Obtener los números de línea y su precio de compra del pedido número 1. Número de pieza y número y nombre de vendedor de aquellas piezas cuyo precio de venta es mayor que 50 o su descuento de suministro es mayor que 10. Número y nombre de vendedor. 3.CONSULTAS SELECT2 1. Precio unitario de suministro del número de pieza A-1001-L y el vendedor 100. Obtener nombre. 14. Obtener los vendedores ordenados alfabéticamente en orden descendente. 21. 23. 9. 17. Obtener número y nombre de todas las piezas recibidas el 1 de Mayo de 1992.
nompieza from preciosum ps.numpieza.numpieza ∧ ∃ P (Pedido(P) ∧ LP.numpieza=LP. preciovent from pieza pz.provincia/ Preciosum(PS) ∧ ∃ V(Vendedor(V) ∧ PS. pieza p where ps.numvend order by numpieza desc (En AR y C.numpedido and l.T. nompieza.preciounit / Preciosum(PS) ∧ numvend=100 ∧ numpieza=A-1001-L } --17 select p.numpieza.numpieza and (numvend = 2 or numvend = 4) En AR Pieza ∞ Preciosum donde numvend=2 o numvend=4 [numpieza.numpieza En AR Pieza ∞ Linped ∞ Pedido donde numvend=1 [numpieza. nompieza] En CRT {P.nompieza/ Pieza(P) ∧ ∃ PR (Preciosum(PR) ∧ P.numpieza.linped l where numvend = 1 and p. preciovent] En CRT {P.nompieza P. P. provincia] En CRT {PS.numpieza.numpieza.numpieza ∧ (numvend=2 ∨ numvend=4)) } --19 select nompieza.numpedido ∧ numvend=1)) } 28 .numpedido=P.preciovent/ Pieza(P) ∧ ∃ LP (Linped(LP) ∧ P.numvend = v. no se puede hacer order by) En AR Vendedor ∞ Preciosum [numpieza.numpieza=PR. V. pedido p.numpedido=l.R.SOLUCIONES SELECT2 --2 select distinct numpieza. vendedor v where ps.numpieza=pz. P.numpieza=p. l.numvend) } --9 select preciounit from preciosum where numvend = 100 and numpieza='A-1001-L' En AR Preciosum donde numvend=100 y numpieza=A-1001-L [precionuit] En CRT {PS.numvend=V. provincia from preciosum ps.
expresión [NOT] IN (listaValores) 29 . descripción from asignaturas where créditos between 5 and 8 resultado: créditos 6 6 6 descripción Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Programación Concurrente LISTAS Mediante el operador IN se puede buscar un determinado valor en una lista construida mediante constantes. RANGOS Expresiones del tipo 10 <= x <= 100 se pueden construir utilizando el operador de construcción de rangos BETWEEN. listas. deseamos conocer los créditos y descripción de las asignaturas cuyo número de créditos está entre 5 y 8. select créditos. y funciones de comparación de cadenas de caracteres.select 3 EXPRESIONES DE SELECCIÓN DE FILAS Objetivos: • Comparaciones de cadenas de caracteres. Contenidos • BETWEEN. Construcción de expresiones de selección de filas utilizando rangos. La sintaxis de tal subexpresión de la cláusula where es la siguiente: expresión [NOT] BETWEEN expresión AND expresión Por ejemplo. IN • LIKE. subcadenas.
el primero indica una cadena de caracteres de cualquier longitud. 'DGBD') resultado: descripción Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Nombre de los profesores que no imparten HI.dni and asignatura not in ('HI'. y el segundo un carácter cualquiera.Descripción de las asignaturas FBD y DGBD: select descripción from asignaturas where código in ('FBD'. FBD o DGBD: select nombre from profesores p. 'DGBD') resultado: nombre RAFAEL ROMERO Fijémonos en que MANUEL PALOMAR. SUBCADENAS DE CARACTERES Podemos preguntar por subcadenas dentro de columnas de tipo carácter. que soportan la siguiente sintaxis: columna [NOT] LIKE 'cadena' La cadena de caracteres cadena admite los comodines % y _ . 'FBD'. es decir. imparte i where p.dni = i. tampoco aparece en la tabla resultado puesto que su dni no aparece en la tabla IMPARTE. que no imparte ninguna de las asignaturas objeto de la búsqueda. Para ello utilizaremos los operadores LIKE o MATCHES. equivalentes en uso a los comodines de MS-DOS * y ?. Profesores que atiendan al nombre de 'RAFA': select * from profesores where nombre like 'RAFA%' resultado: dni 21333444 nombre RAFAEL ROMERO Código de las asignaturas de 'Bases de Datos' select código from asignaturas where descripción like '%Bases de Datos%' resultado: 30 código FBD DGBD categoría ASO6 .
Código de las asignaturas._} 31 . siendo tal código de 2 caracteres: select código from asignaturas where código like '_ _' resultado: código HI PC FP VALORES NULOS Podemos interrogar a la BD de datos en busca de valores desconocidos (información faltante) mediante el operador IS NULL.5 6 6 6 9 RESUMEN DE OPERADORES Recopilamos todos los operadores y conectivas lógicas vistos hasta ahora para la construcción de expresiones condicionales: = > < >= <= <> IS NULL AND OR NOT BETWEEN IN LIKE {%. cuya sintaxis es: columna IS [NOT] NULL Código y créditos de las asignaturas de las que conocemos su número de créditos: select código. créditos from asignaturas where créditos is not null resultado: código créditos HI FBD DGBD PC FP 4.
2. 18. Obtener nombre de vendedores y nombre de piezas que pueden suministrar ordenado en orden alfabético. 3. Nombre de vendedores que el nombre empieza por M seguido de cualquier carácter simple. Piezas 6 que sabemos que nos puede suministrar algún vendedor. Obtener el nombre de las piezas que puedan ser suministradas por proveedores cuyo nombre empiece por S. y un precio unitario entre 1 y 500. 7. Obtener el número de pieza y el precio de los monitores. una R y cualquier cadena de n caracteres. Castellón o Alicante. Obtener los nombres de vendedores en orden alfabético. 5. Listar las piezas con un descuento entre 1 y 10 cuyo nombre contenga una O y que se las hayamos solicitado a proveedores cuyo número oscile entre el 1 y el 1000. por lo que se ha de tratar como subcadenas dentro del valor del atributo. 9. Obtener los nombres de los vendedores de las provincias de Valencia. Obtener los nombres de pieza que pueden suministrar los vendedores de apellido 'García'. Nombres de vendedores que se apellidan López. Obtener nombres de vendedores y números de pieza que nos pueden suministrar. el 300 y/o el 400 con un precio de venta entre 10 y 1000. la BD no diferencia entre nombre y apellido primero y segundo. Nombre de las piezas que pueden sernos suministradas desde la Comunidad Valenciana. Nombres de vendedores que termina su nombre por Z. y ordenados alfabéticamente. 19.A. 14. 15.CONSULTAS SELECT3 1. 8. 6. y se llamen de apellido 5 Martínez o Martín. 6 Cuando no se especifica un atributo concreto. Obtener todos los vendedores que tengan teléfono. de aquellos vendedores cuyo apellido comienza por Martín. se entiende que se pide toda la información disponible en la BD de los objetos solicitados. 5 Evidentemente. 32 . Obtener los vendedores que viven en la calle Ciscar o Salamanca y de la Empresa Harw S. 10. 4. 17. para los vendedores de la provincia de Valencia o Alicante. 12. 16. Listar los nombres y números de vendedores así como el número y nombre de las piezas que pueden suministrar. Obtener el nombre de las piezas que puedan ser suministradas por aquellos proveedores cuyo nombre empiece por S. Obtener todos los nombres de piezas que pueda suministrar el vendedor 100. 11. 13. 20. Nombres de vendedores que empieza su nombre por J. Obtener los vendedores de la provincia de Valencia o Alicante que su nombre empieza por J o por M y tienen un número de vendedor entre 1 y 100.
preciosum ps. vendedor v where p.numpieza and ps. preciosum ps.numvend and (nomvend like 'S% MARTIN %' or nomvend like 'S% MARTINEZ %') --3 select nomvend from vendedor where provincia in ('ALICANTE'.numvend and nomvend like 'S%' --2 select nompieza from pieza p. vendedor v where p. ni el operador IS NULL.numpieza = ps.nomvend / Vendedor(V) ∧ (provincia=Alicante ∨ provincia=Castellon ∨ provincia=Valencia)} --7 select nomvend from vendedor where nomvend like 'M_R%' order by nomvend --12 select * from vendedor where telefono is not null order by nomvend 33 .numpieza and ps. 'CASTELLON') order by nomvend En AR Vendedor donde provincia=Alicante o provincia=Castellón o provincia=Valencia [nomvend] En CRT {V.numvend = v.numvend = v. así como tampoco se puede establecer un criterio para ordenar el resultado.SOLUCIONES SELECT3 En AR y CRT no existe el operador LIKE. --1 select nompieza from pieza p. 'VALENCIA'.numpieza = ps.
TO_CHAR(fecha [.) entonces debemos ayudarnos de estas funciones. En general. formato]) Convierte la fecha de tipo DATE a un valor VARCHAR2 en el formato especificado en "formato" Convierte la cadena de caracteres "cadena" de tipo TO_DATE(cadena [. cambiar el formato de visualización. etc. los tipos de datos de fecha y hora y las funciones que los manejan no suelen estar igualmente implementadas en todos los SGBD. 35 .fecha2) Devuelve los meses transcurridos entre fecha1 y fecha2 7 Aunque todas las demás sesiones de prácticas cumplen con el estándar SQL. la comparación entre fechas se hace directamente. Por ejemplo: select nombre from profesores where fechainc < ‘12/23/1989’ select nombre from profesores where fechainc < ‘12-23-1989’ resultado: nombre MANUEL PALOMAR Pero si necesitamos hacer transformaciones sobre un determinado dato (extraer parte de una fecha. que se pueden utilizar tanto en la lista de columnas que aparecerán en la tabla resultado de la select como en la construcción de expresiones de la where.n) Devuelve la fecha especificada con n meses más MONTHS_BETWEEN(fecha1. por lo que esta sesión es únicamente aplicable a Oracle. calcular días. formato]) CHAR a un valor de tipo DATE con el formato especificado en "formato" SYSDATE Devuelve la fecha actual del sistema ADD_MONTHS(fecha.select fecha FUNCIONES DE FECHA7 Objetivos: • Construir condiciones de selección de filas utilizando atributos de tipo fecha. Contenidos • TO_CHAR() • TO_DATE() • SYSDATE • ADD_MONTHS() • MONTHS_BETWEEN() FUNCIONES DE FECHA Se presentan a continuación distintas funciones de manejo de fechas y hora.
/ ' . to_char (fechainc. : 'texto' D DAY DD DY MM MON MONTH Q YYYY Y.'DD') < 15 select * from profesores where to_char(fechainc. Dicha máscara es una cadena de caracteres entre comillas simples en la que se pueden utilizar los siguientes: ELEMENTO . .'DD') from profesores where to_char(fechainc.FORMATOS DE FECHA Las funciones TO_CHAR() y TO_DATE() admiten distintos formatos de fecha que se pueden construir según una máscara especificada por el usuario.'MM') < 6 select * from profesores where fechainc = sysdate Para obtener el número de días entre dos fechas basta con utilizar la operación resta select sysdate-fechainc from profesores 36 .YYY YY SIGNIFICADO Marcas de puntuación y texto fijo que se reproduce en el resultado Día de la semana (1-7) Nombre del día de la semana Día del mes (1-31) Día del año (1-366) Mes (1-12) Nombre abreviado del mes Nombre completo del mes Cuatrimestre del año (1-4) Año con 4 dígitos Año con punto de millar Año con 2 dígitos Por ejemplo: select nombre.
CONSULTAS SELECT FECHA
Obtener el número de pieza de las piezas que se recuenten el 15/10/92.
Números de pieza de las piezas que se recuentan entre el 1 de Octubre de 1992 y el 31
Número y fecha de los pedidos solicitados en el segundo semestre del 92.
Nombre de los vendedores que sirvieron piezas en la primera quincena de mayo del 92,
Número, nombre de vendedor y mes en que se les solicitó un pedido de discos duros.
Años en que se ha efectuado algún recuento.
Número y descripción de las piezas que sirvieron los proveedores los días 10 de cada mes.
Número y descripción de las piezas que sirvieron los proveedores en el 95 y cuyo precio
de compra es superior al precio de suministro que ellos ofertaban.
Número de pieza, descripción, fecha de recepción, cantidad recibida, de las piezas
recibidas en el 95 y de precio de venta entre 50 y 100 euros.
SOLUCIONES SELECT FECHA
select numpieza
where fecharecuento=’15/10/1992’
where fecharecuento >= '1/10/1992' and fecharecuento <='31/10/1992'
select numpedido, fecha
where fecha >= '1/7/1992' and fecha <= '31/12/1992';
where to_char(fecha,'YY') = '92' and to_char(fecha,'MM') >= 6;
select distinct nomvend
from vendedor v, pedido pd, linped l
where v.numvend = pd.numvend
and pd.numpedido = l.numpedido
and to_char(fecharecep,'YYYY') = 1992
and to_char(fecharecep,'MM') = 5
and to_char(fecharecep,'DD') <= 15
order by nomvend
select distinct pz.numpieza, nompieza
from linped l, pieza pz
where l.numpieza = pz.numpieza
and to_char(fecharecep,'DD') = 10
• Combinar el resultado de varias sentencias select en un único resultado, utilizando los
operadores de conjuntos: unión, intersección y diferencia.
• Union, Union All
• Resolución de requerimientos.
Un operador sobre conjuntos combina el resultado de dos sentencias SELECT en un único
resultado. Para que se pueda realizar, las sentencias SELECT deben tener como resultado el
mismo número de columnas y los mismos tipos de datos (no es necesario que sean de igual
Los operadores de conjuntos son la UNION, UNION ALL, INTERSECT y MINUS. En la versión de
Oracle con la que trabajamos, todos tienen la misma precedencia y se evalúan de izquierda a
derecha. Si queremos alterar este orden debemos utilizar los paréntesis.
Al utilizar el operador UNION entre dos sentencias SELECT, el resultado final estará compuesto
por todas aquellas filas que aparecen en el resultado de como mínimo una de las SELECT. El
operador UNION elimina filas duplicadas en el resultado final. El operador UNION ALL opera de
igual modo que el operador UNION, pero no elimina filas duplicadas en el resultado final.
Supongamos que queremos saber el nombre de los profesores que son ASO6 o imparten
asignaturas de 6 créditos.
Se calcula cada SELECT por separado.
where categoría=’ASO6’
from profesores p, imparte i, asignaturas
where p.dni=i.dni and asignatura=codigo and creditos=6
En el resultado final aparecerán todas las filas que estén en cualquiera de los dos resultados.
resultado con UNION ALL:
Al utilizar el operador INTERSECT entre dos sentencias SELECT, el resultado final estará
compuesto por todas aquellas filas que aparezcan en los resultados de ambas sentencias (no es
suficiente que aparezcan sólo en el resultado de una de ellas)
Supongamos que queremos saber el nombre de los profesores que son ASO6 e imparten
Se calcula cada SELECT por separado y aparecen en el resultado final todas las filas que estén
en ambos resultados.
Al utilizar el operador MINUS entre dos sentencias SELECT, el resultado final estará compuesto
sólo por aquellas filas que aparecen en el resultado de la primera SELECT y no aparecen en el
Supongamos que queremos saber el nombre de los profesores que son ASO6 y no imparten
en estos dos últimos enunciados.dni=i.dni and asignatura=codigo and creditos=6 MINUS select nombre from profesores where categoría=’ASO6’ resultado: nombre EVA GÓMEZ Algunos de estos requerimientos se pueden resolver con una sola sentencia SELECT donde se combinen de modo adecuado las condiciones. asignaturas where p.dni=i. Queremos saber el nombre de los profesores que imparten asignaturas de 6 créditos y no son ASO6. 41 . imparte i.dni and asignatura=codigo and creditos=6 resultado: nombre Supongamos que las condiciones hubiesen estado al revés. asignaturas where p. el segundo se puede solucionar con una SELECT. mientras que en el caso del primero es necesario trabajar con dos. imparte i. select nombre from profesores p.select nombre from profesores where categoría=’ASO6’ MINUS select nombre from profesores p. En otras ocasiones debemos recurrir a operar con los resultados de varias sentencias. Por ejemplo.
al menos por medio de uno de ellos. 4. y que puedan ser suministradas por el vendedor número 1. Nombre de las empresas que tengan más de dos vendedores o que pueda suministrar la pieza ‘A-1001-L’. 2. Nombre de las empresas que tengan más de dos vendedores y que ninguno de sus vendedores pueda suministrar la pieza ‘A-1001-L’.CONSULTAS CONJUNTOS 1. Número y nombre de las piezas con un precio de venta mayor que 1000. 7. Número y fecha del pedido que contiene piezas que no están listadas en la lista de suministros. 42 . 5. Número y nombre de los vendedores que oferten alguna de las piezas que pueden ser suministradas por el vendedor número 1. pero que no oferten ninguna de las que puedan ser suministradas por el vendedor número 2. Nombre de las empresas que tengan más de dos vendedores y que pueda suministrar la pieza ‘A-1001-L’. 6. 3. Número de los pedidos en los que se soliciten teclados pero no monitores.
nombrecomer=Vendedor.nombrecomer and V2.nombrecomer / VENDEDOR(V1) ∧ ∃ V2(VENDEDOR(V2) ∧ V1.numvend and V2.numvend ∧ ∃ V3(VENDEDOR(V3) ∧ V2.numvend=ps.numvend<>V3.nombrecomer ∧ V1.numvend<>V3.numvend<>V2.numvend [V1. preciosum ps where v.nombrecomer ∧ V1.numvend<> VENDEDOR.nombrecomer ∧ V1.numvend<>V2.numvend<>Vendedor.numvend<>Vendedor.nombrecomer] ∩ (VENDEDOR ∞ PRECIOSUM DONDE NUMPIEZA=’A-1001-L’) [nombrecomer] En CRT {V1. V2 para VENDEDOR V1 × V2 × VENDEDOR donde V1.numvend<>V3.numvend=V1.numvend<> VENDEDOR.numvend<>V3.numvend<>V2.numvend=ps.nombrecomer / VENDEDOR(V1) ∧ ∃ V2(VENDEDOR(V2) ∧ V1.nombrecomer=V2. V2 para VENDEDOR V1 × V2 × VENDEDOR donde V1. V2 para VENDEDOR V1 × V2 × VENDEDOR donde V1.SOLUCIONES CONJUNTOS --1 select nombrecomer from vendedor group by nombrecomer having count(*)>2 UNION select nombrecomer from vendedor v.numvend)) 43 .numvend ∧ ∃ V3(VENDEDOR(V3) ∧ V2.numvend ∧ V2.numvend and V1.numvend and numpieza=’A-10001-L’ En AR Define alias V1.nombrecomer=V3.nombrecomer and V2.nombrecomer] ∼ (VENDEDOR ∞ PRECIOSUM donde numpieza=’A-1001-L’) [nombrecomer] En CRT {V1.numvend)) ∧ ∃ PS(PRECIOSUM(PS) ∧ PS. preciosum ps where v.nombrecomer] ∪ (VENDEDOR ∞ PRECIOSUM DONDE NUMPIEZA=’A-1001-L’) [nombrecomer] En CRT {V1.nombrecomer and V1.nombrecomer=V3.numvend<>V3.numvend and V2.numvend=V1.numvend<>V2.numvend=ps.numvend ∧ V2.nombrecomer=V2.nombrecomer=V2.numvend<>V3.numvend [V1.nombrecomer and V1.numvend<> VENDEDOR.nombrecomer / VENDEDOR(V1) ∧ ∃ V2(VENDEDOR(V2) ∧ V1.numvend and numpieza=’A-10001-L’ En AR Define alias V1.numvend<>Vendedor.nombrecomer=V2.numvend)) ∨ ∃ PS(PRECIOSUM(PS) ∧ PS.numvend ∧ V2.nombrecomer ∧ V1.nomvend ∧ numpieza=’A1001-L’} --2 select nombrecomer from vendedor group by nombrecomer having count(*)>2 INTERSECT select nombrecomer from vendedor v. preciosum ps where v.numvend ∧ ∃ V3(VENDEDOR(V3) ∧ V2.numvend<>V2.numvend and V1.nombrecomer=V3.numvend<>V2.numvend and V1.numvend and numpieza=’A-10001-L’ En AR Define alias V1.numvend [V1.nombrecomer=V2.nomvend ∧ numpieza=’A1001-L’} --3 select nombrecomer from vendedor group by nombrecomer having count(*)>2 MINUS select nombrecomer from vendedor v.nombrecomer=Vendedor.numvend and V2.nombrecomer=V2.nombrecomer and V1.nombrecomer and V2.nombrecomer ∧ V1.nombrecomer=Vendedor.nombrecomer ∧ V1.
numvend and ps1. preciosum ps1.numpieza=ps2.nombrecomer=V1.numvend=ps1.numpieza=ps.numvend=V4.numpieza=PS4.nomvend / VENDEDOR(V1) ∧ ∃ PS1(PRECIOSUM(PS1) ∧ PS1.preciovent>1000 ∧ ∃ PS(PRECIOSUM(PS) ∧ PS. nompieza from pieza p. nomvend from vendedor v.numpieza=P.numvend=ps1.numvend ∧ V4.numpieza=PS2.numpieza=ps2.numpieza. preciosum ps1. preciosum ps2 where v.numpieza and numvend=1 MINUS select v.numvend=V1. preciosum ps2 where v.numvend=V1. pero no es necesario. nompieza] En CRT {P.numpieza and numvend=2 En AR (PRECIOSUM donde numvend=1 [numpieza] ∞ PRECIOSUM ∞ VENDEDOR)[numvend. nomvend] ∼ (PRECIOSUM donde numvend=2 [numpieza] ∞ PRECIOSUM ∞ VENDEDOR)[numvend.numvend ∧ ∃ PS2(PRECIOSUM(PS2) ∧ PS2.numpieza ∧ PS. nomvend] En CRT {V1.numvend. P.numvend. V1. preciosum ps where p.nombrecomer) ) } --6 select v.numvend.numvend ∧ ∃ PS4(PRECIOSUM(PS4) ∧ PS4.nompieza / PIEZA(P) ∧ P.numpieza)) ) ) } --7 Se podría hacer utilizando el operador INTERSECT.numvend and ps1.numpieza and preciovent>1000 and numvend=1 En AR (PIEZA donde preciovent>1000 ∞ PRECIOSUM donde numvend=1)[numpieza. select numpieza.∧ ⎤ ∃ PS(PRECIOSUM(PS) ∧ numpieza=’A-1001-L’ ∧ ∃ V4(VENDEDOR(V4) PS.numpieza ∧ ⎤ ∃ PS3(PRECIOSUM(PS3) ∧ PS3.numvend=2 ∧ PS3.numvend=1)} 44 .numvend=1 ∧ PS1. nomvend from vendedor v.
así como la forma de realizar operaciones como suma. OPERACIONES ARITMÉTICAS Se pueden utilizar expresiones aritméticas tanto en la cláusula select. como en la construcción de condiciones de selección de filas. MAX(). Número de horas semanales. MIN(). Contenidos • operaciones aritméticas: +. *. (créditos/3)*2 from asignaturas resultado: descripción Historia de la Informática Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Programación Concurrente Fundamentos de Programación (créditos/3)*2 3 4 4 4 6 45 . en un sólo semestre.. producto. COUNT(). para obtener una nueva columna en la tabla resultado. -. ROUND().funciones ARITMÉTICA Y FUNCIONES AGREGADAS Objetivos: • Obtención de información calculada. / • SUM(). de valores escalares. etc. • etiquetas para columnas • Resolución de requerimientos. Introducción de las funciones estadísticas aplicables a columnas enteras y de cuenta de filas. que se imparten de cada asignatura: select descripción. ABS(). AVG().
¿Cuántos profesores hay en nuestra BD? select count( * ) from profesores resultado: ( count( * ) ) 3 ¿Cuántas asignaturas de más de 5 créditos hay en nuestra BD? select count( * ) from asignaturas where créditos > 5 resultado: ( count( * ) ) 4 ¿Cuántas valores de créditos distintos hay en nuestra BD? select count( distinct créditos ) from asignaturas resultado: ( count ) 3 46 . si no se especifica ésta. En ningún caso se pueden anidar funciones: avg ( min ( créditos ) ) no está permitido.Descripción de las asignaturas y número de horas semanales de las asignaturas con menos de 4 horas semanales de clase: select descripción. y se asume que la función se calcula únicamente sobre valores distintos de la expresión. Estas funciones devuelven un único valor para todas las tuplas seleccionadas mediante la condición de la cláusula where. Si se especifica la palabra clave distinct la expresión obligatoriamente ha de ser un nombre de columna.5 FUNCIONES AGREGADAS Se dispone de una serie de funciones agregadas que retornan valores calculados sobre una determinada columna. créditos from asignaturas where (créditos/3)*2 < 4 resultado: descripción Historia de la Informática créditos 4. COUNT( * COUNT( DISTINCT expr SUM( [DISTINCT] expr AVG( [DISTINCT] expr MIN( expr MAX( expr ROUND( expr ABS( expr ) ) ) ) ) ) ) ) número de filas número de valores distintos en la columna expr suma de todos los valores en expr promedio de todos los valores en expr el más pequeño de todos los valores en expr el mayor de todos los valores en expr redondea expr valor absoluto de expr Las funciones de tipo estadístico precisan que la expresión que se evalúe se construya sobre columnas de tipo de datos numérico. el cálculo se realiza sobre la totalidad de la columna. La expresión expr puede contener el nombre de una columna o un cálculo sobre una o varias columnas.
Se pueden etiquetar las columnas de la tabla resultado añadiendo. avg(créditos) mediactos from asignaturas resultado: num_asignaturas mediactos 5 6. antes de la coma si hubiera más columnas detrás. select count( * ) num_asignaturas.3 ¿Cuál es la media de horas por asignatura? select avg( créditos*2/3 ) from asignaturas resultado: ( avg ) 4. tal como se hace con los alias temporales de las tablas.2 Total de créditos ofertados en asignaturas de Bases de Datos: select sum( créditos ) from asignaturas where código like '%BD' resultado: sum( créditos ) 12 ETIQUETAS PARA COLUMNAS Dado que las expresiones calculadas no tienen un nombre específico de columna en la salida por pantalla. Esta cadena de caracteres aparecerá en el encabezado de la columna.30 47 . una cadena de no más de 18 caracteres y sin espacios en blanco.¿Cuál es la media de créditos por asignatura? select avg( créditos ) from asignaturas resultado: ( avg ) 6. es conveniente "renombrarlas" para aclarar su significado.
Total de la diferencia en euros entre lo pagado por compra de artículos y sus respectivos precios de suministro ofrecidos en su día por los proveedores si el segundo precio es menor que el primero. Obtener la cantidad de provincias distintas de las que tenemos conocimiento de algún proveedor. mínimo y media de precio de venta de las piezas. 10. Máximo. Cantidad de vendedores de nuestra BD. 5. 6. 9. Cantidad total de piezas que sabemos nos pueden suministrar los proveedores. Obtener la diferencia entre cantidad pedida y cantidad recibida de las líneas del pedido 1. Máximo descuento (en euros) de las piezas suministradas. 11. 7. Mínima diferencia entre precio de compra y precio de suministro del vendedor al que se le compró. 4.CONSULTAS FUNCIONES 1. 48 . 8. Número. Media de días de intervalo entre la fecha de envío del pedido 1 y de entrega de las distintas piezas solicitadas en ese pedido 3. nombre y diferencia entre precio de venta y precio de compra de la(s) pieza(s) que suministran los vendedores de Alicante. 2. Media de precios distintos de venta de piezas.
numpieza = ps.numpedido = l.numpieza --5 select avg(distinct preciovent) mediaventa from pieza 49 .numpedido --3 select count(distinct provincia) provincias from vendedor --4 select min(preciocompra-preciounit) minimo from preciosum ps. pedido pd. pedido pd where pd.numvend and pd.SOLUCIONES FUNCIONES --1 select numlinea.numvend = pd. linped l where ps. cantrecibida-cantpedida diferencia from linped where numpedido = 1 --2 select avg(abs(fecha-fecharecep)) mediarecep from linped l.numpedido = pd.numpedido and l.numpedido = 1 and l.
group by INFORMACIÓN AGRUPADA Objetivos: • Obtención de información calculada sobre grupos de filas. imparte i where p. nombre.dni resultado: ERROR 51 .dni.dni = i. nombre.dni group by p. imparte i where p. • No se pueden poner columnas no calculadas en la select que no aparezcan en la group by. nombre resultado: dni 21111222 21333444 nombre EVA GÓMEZ RAFAEL ROMERO ( count ) 2 1 En esta ocasión. select p. Contenidos • GROUP BY • Resolución de requerimientos. count( * ) from profesores p.dni = i. LA CLÁUSULA GROUP BY Supongamos que queremos obtener la cantidad de asignaturas que imparte cada profesor: select p. y por la utilización de la cláusula group by. El criterio de agrupación se forma con al menos todas las columnas no calculadas de la lista de la cláusula select. la cuenta de valores distintos de la columna asignatura se calcula en base a cada profesor de nuestra BD que imparte alguna asignatura.dni.dni group by p. Clasificación de información en función de algún criterio especificado mediante la cláusula GROUP BY. count( * ) from profesores p.dni.
dni e imparte.N.I.dni. podemos obtener información en una única fila de dos personas diferentes que se llaman igual. admite duplicados. la clave candidata). por ejemplo.N. Así. obviamente) que se llamen igual (nombres y apellidos). agrupamos datos por el nombre y no por el D. imparte i where p. Así.I. Sea una ocurrencia tal como ésta: DNI 21 22 52 CLIENTE NOMBRE JUAN JUAN OPERACIÓN 1 2 3 INGRESO CLIENTE 21 22 22 INGRESO 1000 2000 3000 .dni and asignatura <> 'FBD' group by p. select nombre.. aparte de enlazar tablas por columnas comunes.N. imparte i where p.• Si se pueden poner más columnas en la lista del group by que en la de la select. el nombre no será clave y. por lo tanto. como PROFESORES e IMPARTE por profesores. diferentes.dni = i. escribiremos la siguiente sentencia. si queremos calcular cuantas asignaturas imparte cada profesor sin contar Fundamentos de las Bases de Datos. Si. por ejemplo) es el D. si la clave primaria de una tabla de personas (profesores o alumnos.dni group by p. se puede utilizar para eliminar ciertas filas del cálculo.dni. select nombre. count( * ) from profesores p. podemos decir sin equivocarnos que no habrá duplicados en este atributo. nombre resultado: nombre EVA GÓMEZ RAFAEL ROMERO ( count(*) ) 1 1 ATRIBUTOS NO CLAVE EN EL GROUP BY Los únicos atributos que aseguran la identificación de una tupla respecto de las demás son los que forman la clave primaria (en general. Es decir.dni respectivamente. nombre resultado: nombre ( count(*) ) EVA GÓMEZ RAFAEL ROMERO 2 1 where y group by Cuando se utiliza la cláusula where. count( * ) from profesores p. No obstante.dni = imparte.I. es perfectamente posible encontrar a dos personas distintas (con números de D.
evitaremos la no inclusión de atributos clave en la cláusula group by. ORDENACIÓN DE LA SALIDA En la cláusula order by podemos especificar la expresión tal cual aparece en la select. la agrupación contabiliza todas las ocurrencias de ingreso como de uno sólo. count( * ) asignaturas from profesores group by p.dni.dni. y aunque no afecte al resultado. count( * ) asignaturas from profesores group by p. nombre order by 2 53 . ingreso where dni = cliente group by dni. avg( ingreso ) media from cliente. si queremos saber exactamente de a qué cliente nos referimos en cada tupla del resultado.dni. select dni. ingreso where dni = cliente group by nombre resultado: nombre JUAN media 2000 Puesto que el nombre es el mismo para los dos clientes. nombre resultado: nombre JUAN JUAN media 1000 2500 Es evidente que. debemos incluir la clave primaria como atributo de la relación derivada de la ejecución de la select.select nombre. count( * ) asignaturas from profesores group by p. si introducimos en la lista de columnas del GROUP BY aquellas que son su clave primaria: select nombre. avg( ingreso ) media from cliente. nombre order by asignaturas select dni. Sin embargo. nombre order by count( * ) resultado: dni 21333444 21111222 asignaturas 1 2 O bien podemos utilizar una alias para la columna (asignaturas en este ejemplo) o el orden de la columna empezando por la izquierda (la función está en la tercera columna en este ejemplo): select dni. En general.
resultado: dni 21333444 21111222 asignaturas 1 2 55 .
6. 12. Obtener para cada pieza. De cada pieza obtener el precio unitario medio de suministro. 8. precio de venta.CONSULTAS GROUP BY NOTA: es muy recomendable. y la media de la diferencia entre el precio de venta y el de suministro. De cada pieza de precio de venta mayor que 250 obtener el precio medio de suministro. 1. Calcular para cada pieza. nombre de la pieza. 2. Obtener la media de las ventas (en euros) realizadas por cada vendedor de cada pieza. 11. Obtener la cantidad de pedidos efectuados por fecha y el total pagado por las mercancías. aunque no se especifique explícitamente en el requerimiento. ordenado alfabéticamente por la descripción de la pieza y eliminando aquellas compradas a proveedores de Alicante. cuántos vendedores la podrían suministrar. Obtener. 3. la máxima diferencia entre cantidad pedida y cantidad recibida de entre todas las veces en que fue servida. 13. 10. se deben etiquetar las columnas calculadas. 9. Calcular por número de pedido. 5. Calcular las ganancias (precio de compra menos precio de suministro por la cantidad recibida) de cada vendedor que ha efectuado alguna venta. el número de la pieza y el número total de vendedores que nos pueden suministrar esa pieza. Obtener para cada pieza. por pieza solicitada. ordenado descendentemente por cantidad de empleados. la cantidad pedida total así como la diferencia entre cantidad pedida total y cantidad recibida total. por cada pieza solicitada. la media de la diferencia entre el precio de compra y el de suministro (que nos ofertaba el vendedor al que se le solicitó el pedido) de las líneas de cada pedido. 7. 4. el precio pagado en total por ese pedido. Obtener la cantidad de vendedores de cada empresa. Obtener número de pieza y número total de vendedores que la pueden suministrar para piezas de más de 250 de precio de venta. Obtener para cada número de pedido. que la salida se ordene ascendentemente (por defecto) por las columnas no calculadas de la lista de la cláusula select. Obtener. NOTA: así mismo. indicando cantidad y nombre de la misma. 56 . el tanto por ciento de beneficios del precio de venta al público respecto al precio medio de compra de todas las compras que se han realizado de la pieza 14.
numpieza group by p. preciovent. count(*) vendedores from preciosum group by numpieza order by numpieza --6 select ps. sum(cantpedida) cantidadPedida.numpieza group by p. sobre todo si estas funciones agregadas aparecen en la cláusula SELECT.T.numpieza.numpieza=ps. --1 select nompieza. nombrecomer from vendedor group by nombrecomer order by vendedores desc.numpieza and preciovent >= 250 group by ps.preciovent order by nompieza --2 select numpedido.numpieza 57 . nombrecomer --4 select numpieza. max(preciovent)/avg(preciocompra)*100 from linped l. preciosum ps where p.numpieza.numpieza = p. sum(preciocompra*cantrecibida) precioPagado. max(cantpedida-cantrecibida) maximo from linped group by numpieza order by numpieza --5 select numpieza.numpieza order by p.R.SOLUCIONES GROUP BY En AR y C.numpieza=p.nompieza. pieza p where ps.numpieza order by 2 --7 select numpieza.avg(preciounit) precio_Sum_Medio from preciosum group by numpieza order by numpieza --13 select p. pieza p where l. avg(preciovent-preciounit) media from pieza p.numpieza. count(*) vendedores from preciosum ps. sum(cantpedida-cantrecibida) no_recibido from linped group by numpedido order by numpedido --3 select count(*) vendedores. no hay definidas funciones agregadas por lo que muchos de los requerimientos nos los podríamos abordar.
count( * ) asignaturas from profesores p. nombre having count(*) >= 2 order by nombre resultado: dni 21111222 nombre EVA GÓMEZ asignaturas 2 59 . la cláusula having selecciona grupos. • Resolución de requerimientos. si en la where la condición que se especifica afecta a las tuplas de toda la tabla.dni. where selecciona sobre los datos almacenados en la tabla y having sobre la información calculada.dni. Normalmente.dni = i. imparte i where p. LA CLÁUSULA HAVING Al igual que la cláusula where selecciona filas. Supongamos que queremos saber cuantas asignaturas imparte cada profesor pero únicamente en el caso de que imparta 2 ó más asignaturas: select p. Contenidos • HAVING.dni group by p. Sobre esta última el having eliminaría aquellas tuplas que no cumplen la condición.having SELECCIÓN DE INFORMACIÓN AGRUPADA Objetivos: • Establecer selecciones sobre información agrupada. nombre.group by . el group by efectúa los cálculos en función de esa selección previa y da como resultado una tabla con la información calculada para cada grupo.
para cada profesor.select p. los efectos de esta última orden: 1. una única asignatura. la cláusula group by calcula. Eva Gómez sólo imparte. count( * ) asignaturas from profesores p. la cláusula where ha eliminado las tuplas de imparte de código de asignatura FBD.dni. 60 . nombre having count(*) >= 2 order by nombre resultado: dni nombre asignaturas En la expresión del having no se pueden utilizar los alias de las columnas (asignaturas) en este caso. igual que Rafael Romero.dni = i. nombre. cláusula a cláusula. la cláusula having elimina del resultado del paso anterior todas aquellas tuplas con un valor en la cuenta de filas menor que 2. Veamos.dni. 2.dni and asignatura <> 'FBD' group by p. El resultado es vacío puesto que ninguno de los grupos supera la condición. Puesto que no contamos FBD. el número de asignaturas que imparte. 3. imparte i where p.
12. Obtener el número de pieza y el precio unitario medio de aquellas piezas que tarden de media 14 días como máximo en ser suministradas (diassum=tiempo de suministro). 3. precio de compra y cantidad pedida de los números de línea 1 y recibidas en fecha 10-05-92. 11. Dar una relación del nombre de las piezas que nos pueden suministrar más de dos proveedores. Determinar el número total de proveedores que pueden suministrar la pieza 'P-0001-33’. exceptuando la información referida al vendedor número 1.CONSULTAS GROUP BY . Para cada pieza. 10. 6. Números de pedido que tengan más de tres líneas de pedido. Nombre de las empresas que tienen más de dos vendedores. y sus precios unitarios máximo y mínimo. y la cantidad de suministradores. 2. el nombre y el precio máximo unitario de las piezas cuyo precio de venta sea mayor que 250 o menor que 170. 7. 14. obtener el número de pieza. y la cantidad de suministradores. Para las piezas que se hayan ofertadoa un precio unitario medio mayor que 260 obtener el número de pieza.HAVING 1. 13. 8. su descuento medio oscile entre 10 y 17 y que tengan un precio unitario medio total superior a 150. de la que se tiene información sobre sus posibles vendedores. Obtener los números de pedido donde el precio total sea superior a 1000. Números de pedido donde el total de piezas pedidas es mayor que 40. 4. Para las piezas que se ofrecen a un precio unitario medio mayor que 260 (sin tener en cuenta los suministros menores de 250) obtener el número de pieza. el máximo precio unitario. ordenado por precio máximo. el máximo precio unitario. 9. Obtener el número. 5. 61 . El resultado se dará ordenado por número y nombre de vendedor y por nombre de la pieza. Obtener el número y el nombre del vendedor así como el número y nombre de las piezas de precio unitario mayor que 200 que nos puedan suministrar. Obtener los números de pedido. Obtener aquellos números de pedido y fecha en que se confeccionaron cuya cantidad de artículos pedidos sea superior a 30 y la recibida inferior a 10.
V2 para VENDEDOR V1 × V2 × VENDEDOR donde V1.numvend and V2.numlinea<> LP3.numlinea ∧ LP3.numpedido] En CRT {LP1.numlinea and LP2. sobre todo si estas funciones agregadas aparecen en la cláusula SELECT.numpedido and LP3.numpedido and LP1.numlinea) } --3 select numpedido from linped group by numpedido having sum(cantpedida)>40 --4 select numpedido from linped l group by numpedido having sum(cantrecibida*preciocompra)>1000 --5 select numpieza.numpedido and LP2.numvend))} --2 select numpedido from linped group by numpedido having count(*)>3 En AR Define alias LP1.numvend<>V2.nombrecomer | VENDEDOR(V1) ∧ ∃ V2(VENDEDOR(V2) ∧ V1.nombrecomer ∧ V1.numvend<>V3.numvend ∧ ∃ V3(VENDEDOR(V3) ∧ V2. linped l where p.numpedido.numpedido ∧ LP1.numlinea and LP1.numpedido.numpedido=l.numpedido ∧ LP3.nombrecomer and V2.numlinea<>LP3.numpedido=LP2.HAVING En AR y CRT no hay definidas funciones de agregados por lo que muchos de los requerimientos nos los podríamos abordar.numpedido group by p. LP2.numlinea [LP1.numlinea ∧ LP2. fecha from pedido p. count(*) cuantos from preciosum where preciounit >= 250 group by numpieza having avg(preciounit)>260 --7 select p.numvend [V1.numlinea<> LINPED.numlinea<>LP2.numpedido=LP3.numpedido=LINPED.nombrecomer=V2.nombrecomer=Vendedor.numlinea and LP2.numlinea ∧ LP1.numlinea ∧ LP1.numlinea and LP1.numvend<> VENDEDOR.numvend<>Vendedor.numpedido ∧ LP2.fecha having sum(cantpedida)>30 and sum(cantrecibida)<10 62 .numlinea<>LP4.numlinea<>LINPED.numpedido=LP3.numpedido=LP2.numpedido | LINPED(LP1) ∧ ∃ LP2 ∃ LP3 ∃ LP4 (LINPED(LP2) ∧ LINPED(LP3) ∧ LINPED(LP4) ∧ LP1.nombrecomer ∧ V1.nombrecomer and V1. max(preciounit) maximo. --1 select nombrecomer from vendedor group by nombrecomer having count(*)>2 En AR Define alias V1.numlinea<> LP3.numvend and V1.nombrecomer=V2.numvend ∧ V2.nombrecomer] En CRT {V1.numlinea<>LP2.SOLUCIONES GROUP BY .numpedido=LP4. LP3 para LINPED LP1 × LP2 × LP3 × LINPED donde LP1. count(*) from preciosum group by numpieza having avg(preciounit)>260 --6 select numpieza.numlinea<> LINPED.numvend<>V2. max(preciounit).numlinea<>LP4.numlinea<>LP4.nombrecomer=V3.numlinea and LP3.numvend<>V3.
nompieza having avg(descuento) between 10 and 17 and avg(preciounit) > 150 order by maximo 63 .preciosum ps where ps.nompieza.numpieza.numpieza and preciovent > 250 or preciovent < 170 group by p.numpieza.max(preciounit) maximo from pieza p.numpieza=p.--10 select p.
Cuando una orden select se encuentra dentro de la cláusula where de otra select recibe el nombre de subconsulta (subquery). 65 . [NOT]. CONSULTAS ANIDADAS (SUBQUERIES) En la condición de búsqueda de la orden select (en la cláusula where o en la having) también podemos: • comparar una expresión con el resultado de otra orden select • determinar si el valor de una expresión está incluido en los resultados de otra orden select • preguntar si una orden select ha seleccionado filas. créditos from asignaturas where créditos = ( select min(créditos) from asignaturas ) resultado: descripción Historia de la Informática créditos 4. Con ese valor se compara tupla a tupla y se obtiene la asignatura (o asignaturas) cuya cantidad de créditos es igual al mínimo. • operadores de comparación escalar.5 En primer lugar se calcula la select anidada (entre paréntesis) y se obtiene el valor mínimo para la columna créditos de la tabla asignaturas. Por ejemplo : select descripción. ALL. • un conjunto de valores (varias filas y una columna). • Resolución de requerimientos. sintaxis general de la subconsulta expresión op_comparación { ALL | [ ANY | SOME ] } ( orden select ) expresión [ NOT ] IN ( orden select ) [ NOT ] EXISTS ( orden select ) Los valores posibles a devolver por una orden select anidada son : • nada • un valor único (una fila y una columna). ANY.subselect CONSULTAS ANIDADAS Objetivos: • Utilizar el resultado de una sentencia select para establecer las condiciones de otra sentencia select. • IN. Contenidos • Subqueries.
5 En este caso. siempre y cuando ésta devuelva una única fila y una única columna. el número en créditos de la asignatura tiene que ser menor o igual que todos (all) los valores obtenidos en la subconsulta (que es la relación de créditos de todas las asignaturas). count). un valor simple. es decir. que el resultado de la subselect. En general serán órdenes select que calculan una función agregada (sum. max. igual. min. 66 . select descripción from asignaturas where créditos = ( select min(créditos) from asignaturas ) No sería correcto. DEVOLUCIÓN DE UN ÚNICO VALOR expresión op_comparación ( orden select ) Podemos utilizar los operadores de comparación para preguntar si el valor de una determinada expresión es mayor. menor. únicamente será en una y nada más que una columna (salvo el operador EXISTS). Nombre de la asignatura que tiene el mínimo número de créditos (es equivalente al primer ejemplo) : select descripción from asignaturas where créditos <= ALL ( select créditos from asignaturas ) resultado: descripción Historia de la Informática créditos 4. porque la subselect devuelve más de una fila. la siguiente orden : select descripción from asignaturas where créditos = ( select créditos from asignaturas where créditos < 9 ) DEVOLUCIÓN DE UNA LISTA DE VALORES expresión op_comparación ALL | [ ANY | SOME ] ( orden select ) Cuando la tabla resultado contiene más de una fila (pero una única columna. avg.Siempre que la subselect devuelva algo. etc. insistimos) hay que utilizar un modificador para el operador de comparación.
Nombre de las asignaturas que no tienen el mínimo número de créditos : select descripción from asignaturas where créditos > ANY ( select créditos from asignaturas ) resultado: créditos 6 6 6 9 descripción Fundamentos de las Bases de Datos Diseño y Gestión de Bases de Datos Programación Concurrente Fundamentos de la Programación Serían todas aquellas asignaturas cuyos créditos superasen al menos a uno (any) de los valores devueltos por la subconsulta. Obtener todos los datos de los profesores que imparte alguna asignatura : select * from profesores where dni IN ( select dni from imparte ) O dicho de otra manera : datos de los profesores cuyo dni aparece en la tabla imparte. imparte i where p.codigo and créditos < ANY ( select créditos from asignaturas ) resultado: nombre Eva Gómez Rafael Romero expresión [ NOT ] IN ( orden select ) También podemos consultar la pertenencia de un valor a la lista de valores devuelta por la subconsulta. imparte i where p. asignaturas a.* from profesores p.dni Se verá más clara la utilidad de este operador si preguntamos justo lo contrario. Otra forma de expresar el mismo requerimiento : select descripción from asignaturas where créditos != ( select min(créditos) from asignaturas ) Supongamos que deseamos obtener el nombre de los profesores que imparten una asignatura que no sea la máxima en número de créditos : select nombre from profesores p.dni = i.dni and i. El modificador some es equivalente a any.dni = i. 67 .asignatura = a. En este caso daría lo mismo procesar la orden : select p.
Obtener todos los datos de los profesores que no imparten asignaturas : select * from profesores where dni NOT IN ( select dni from imparte ) Por último. veamos que algunos de estos operadores son equivalentes : expresión IN (orden select) expresión NOT IN (orden select) 68 ≡ ≡ expresión =ANY (orden select) expresión !=ALL (orden select) .
Número de pedido y precio total pagado del pedido más caro. Número y descripción de las piezas que pueden ser suministradas por proveedores de la Comunidad Valenciana o que pueden ser suministradas por el vendedor que ha realizado la menor venta (pedido de importe más bajo) 20. Número y nombre del vendedor al que se le ha solicitado el pedido más caro. 19. Número y descripción de la pieza que ha sido pedida más veces. 17. 6. Número y nombre de los vendedores a los que no se les ha solicitado pedidos. Obtener los nombres de los vendedores de la misma empresa que Luis García. 3. número de pieza y descuento para aquellas piezas cuyo precio de venta supere en más del 15% la media del precio de compra de los pedidos en los que aparece. 14. obtener el número de pieza. 9. 21. 11. exceptuando aquellos suministrados por el vendedor número 1. Para cada pieza que se ofrece en la lista de suministros. Número de vendedor. 8. 2. Número y nombre de los vendedores que no ofertan ninguna pieza. la cantidad de piezas distintas que ha vendido. descripción y precio de venta de los monitores que no han sido nunca solicitados. Listar los vendedores que sean de la misma provincia que el vendedor número 100. Nombre de la empresa con mayor importe de ventas. Nombre de los suministradores que pueden suministrar al menos alguna de las piezas que puede suministrar el vendedor número 5.CONSULTAS SUBSELECT 1. por cada número de vendedor. y sus precios unitarios máximo y mínimo. 4. Calcular. 5. Número y descripción de la pieza más cara (precio de venta). Cantidad de piezas a la venta de las que no tenemos información sobre sus posibles suministradores. Número. 15. 10. para aquellos vendedores pertenecientes a la empresa con más proveedores. 13. Número y nombre de los vendedores a los que se les ha solicitado algún pedido. 16. Número. 18. 12. 7. Nombre de la empresa que ofrece la pieza más barata de precio de suministro. 69 . empresa en la que trabaja y número de piezas que puede suministrar el vendedor que tiene la media más alta de piezas servidas. Obtener todos los datos de los vendedores a los que se les han solicitado más pedidos que a todos los demás. Número y descripción de las piezas cuyo precio medio de suministro esté por encima del mayor precio de compra pagado por ella. nombre.
numpieza and ps.numvend. nomvend] En CRT {V.numvend. nomvend] En CRT {V.numpedido=l.numpedido and nombrecomer in (select nombrecomer from vendedor group by nombrecomer having count(*) >= all (select count(*) from vendedor group by nombrecomer)) group by v.numvend = p.max(preciocompra) from pieza p.numpieza=ps. V. nomvend from vendedor where numvend in (select numvend from pedido) order by nomvend --11 select v.numvend)} --3a select v. nomvend from vendedor v.numvend order by nomvend En AR VENDEDOR ∞ PEDIDO [numvend. nompieza having avg(preciounit)>= (select max(preciocompra) from linped where linped. nompieza.numvend=V.numvend=V.numvend and p.numvend=p. nomvend from vendedor where numvend not in (select numvend from preciosum) order by nomvend En AR (VENDEDOR[numvend] ∼ PRECIOSUM[numvend]) ∞ VENDEDOR[numvend.numpieza group by p.numvend)} --3b select numvend.avg(preciounit).numvend.numpieza. preciosum ps.numvend order by v.numvend --15 select p.numpieza.SOLUCIONES SUBSELECT --1 select * from vendedor where numvend in (select numvend from pedido group by numvend having count(*) >= all(select count(*) from pedido group by numvend)) --2 select numvend.numpieza=p.numpieza=l. pedido p.nomvend / VENDEDOR(V) ∧ ∃ P(PEDIDO(P) ∧ P. pedido p where v.nomvend / VENDEDOR(V) ∧ ⎤ ∃ PR(PRECIOSUM(PR) ∧ PR. linped l where v.count(distinct numpieza) npiezas from vendedor v.numvend. V.numpieza) 70 . linped l where p.
• Para cada profesor y asignatura comprueba que el primero imparte la segunda. no devolverá ninguna tupla : el resultado del segundo exists es falso. el correspondiente exists será falso. y.dni=i.asignatura=a. En nuestra base de datos Ejemplo no hay ningún profesor que imparta todas las asignaturas. Cuando el SGBD esté resolviendo la última subselect para ese profesor y esa asignatura. o lo que es lo mismo. 71 . • Resolución de requerimientos. finalmente verdadero (ese profesor no imparte esa asignatura).código and p. la segunda select recorre la tabla de asignaturas. • Para cada profesor.exists CONSULTAS ANIDADAS II Contenidos • El operador EXISTS. por lo tanto. y por efecto del operador de negación la condición será cierta : ese profesor aparecerá en la solución. El profesor no imparte todas las asignaturas de nuestra base de datos. para la primera subselect. Como ésta última tiene al menos una fila.dni ) ) Vamos a leer el requerimiento siguiendo las apariciones del operador exists dentro de las sucesivas órdenes select : Nombre de los profesores tales que no hay ninguna asignatura que no imparta él. el primer exists será verdadero. como está negado. ya existe una asignatura que obtiene en la cláusula where un valor verdadero : esa asignatura saldrá en la tabla resultado. Por lo tanto. EL OPERADOR EXISTS [ NOT ] EXISTS ( orden select ) El operador exists nos informa si una subconsulta ha devuelto algún resultado. y. Supongamos que queremos conocer si algún profesor imparte todas las asignaturas : select nombre from profesores p where not exists ( select código from asignaturas a where not exists ( select asignatura from imparte i where i. Supongamos que la forma que tiene el SGBD de resolver esta sentencia es la siguiente : • En la primera select recorremos la tabla de profesores. Un profesor que imparta todas las asignaturas obtendrá siempre una fila en la última subselect y. finalmente falso. como está negado. como no están relacionados. Una vez que ha fijado el profesor y la asignatura. si la tabla resultado tiene alguna fila. la última subconsulta dará como resultado verdadero si la imparte y falso en caso contrario (devolverá una tupla o no devolverá nada).subselect . Supongamos un profesor que no imparte al menos una de las asignaturas de nuestra base de datos. Exists devuelve verdadero si hay al menos una tupla en la relación derivada y falso si la relación derivada es vacía. ninguna asignatura aparecerá como resultado de la primera subselect.
72 .Aunque puede inducir a error. por su traducción intuitiva del inglés al español. mientras que el segundo devuelve un valor de verdad. la sintaxis también es muy diferente. no se deben confundir nunca los operadores IN y EXISTS : el primero devuelve una lista de valores.
5. 13. 12.REQUERIMIENTOS SUBSELECT . 6. fecha y número de vendedor del pedido más caro (utilizando subconsultas). Empresas que han servido la(s) pieza(s) de mayor precio de venta. Nombre de las empresas que cumplen que todos sus vendedores son de la Comunidad Valenciana. Número y nombre de la pieza de menor precio de suministro (utilizando EXISTS). 11. Numero y descripción de las piezas que se han solicitado en todos los pedidos del vendedor 1. Nombre de los vendedores a los que se les haya solicitado más de dos pedidos (utilizando subconsultas). Obtener todos los datos de los vendedores que sirvieron los pedidos del requerimiento anterior. Nombre de los vendedores que pueden suministrar todas las piezas. 3. Número y nombre de todos los vendedores de la ciudad de Alicante. Número y nombre de los vendedores que han servido algún pedido (utilizando obligatoriamente EXISTS). 7. ordenados alfabéticamente. Número de pedido. 2. ordenadas alfabéticamente.EXISTS 1. Numero de los pedidos cuyas piezas tienen todas un precio de venta mayor que la mitad del precio máximo de venta. Nombre de los vendedores que no pueden suministrar ninguna pieza. 8. Empresas que no han servido ninguna pieza. 9. 4. 10. 73 .
nombrecomer=V.provincia=VALENCIA))} O bien { V.nombrecomer / VENDEDOR(V) ∧ ⎤ ∃ V2 (VENDEDOR(V2) ∧ V2.numpieza=P.numpieza=P. numpedido] ÷ (PEDIDO donde numvend=1)[numpedido]) ∞ PIEZA) [numpieza.provincia=ALICANTE ∨ V2.numvend) order by nomvend En AR ((VENDEDOR[numvend] ∼ PRECIOSUM[numvend]) ∞ VENDEDOR)[nomvend] En CRT {V.'VALENCIA')) order by nombrecomer En AR VENDEDOR[nombrecomer] ∼ (VENDEDOR donde provincia<>ALICANTE or provincia<>CASTELLON or provincia<>VALENCIA)[nombrecomer] En CRT { V.provincia<>VALENCIA))} 74 . numpieza] ÷ PIEZA[numpieza]) ∞ VENDEDOR) [nomvend] En CRT { V.numvend=V.numvend)} --3 select numpieza. nompieza from pieza pz where not exists (select * from pedido pd where numvend=1 and not exists (select * from linped l where pd.numpieza)) En AR ((LINPED [numpieza.numpedido=l.numvend=V.numvend ∧ PR.nombrecomer=V.numvend and numpieza = pz.nompieza / PIEZA(P) ∧ ∀PD (PEDIDO(PD) ∧ PD.nomvend / VENDEDOR(V) ∧ ∀P (PIEZA(P) ⇒ ∃ PR (PRECIOSUM(PR) ∧ PR.nombrecomer ⇒ (V2.nombrecomer ∧ V2.numpieza.provincia=CASTELLON ∨ V2. nompieza] En CRT { P.numpedido and pz.EXISTS --1 select nomvend from vendedor v where not exists (select * from pieza pz where not exists (select * from preciosum where numvend = v.nombrecomer and provincia not in ('ALICANTE'.numpieza)) En AR ((PRECIOSUM [numvend.numpieza ))} --4 select distinct nombrecomer from vendedor v where not exists (select * from vendedor where nombrecomer = v.SOLUCIONES SUBSELECT .nombrecomer / VENDEDOR(V) ∧ ∀ V2 (VENDEDOR(V2) ∧ V2.numpedido=PD. P.numpedido ∧ LP.numpieza ))} --2 select nomvend from vendedor where numvend not in (select numvend from preciosum) order by nomvend --2 select nomvend from vendedor v where not exists (select * from preciosum where numvend = v.provincia<>ALICANTE ∧ V2.'CASTELLON'.nomvend / VENDEDOR(V) ∧ ⎤ ∃ PR(PRECIOSUM(PR) ∧ PR.numvend=1 ⇒ ∃ LP (LINPED(LP) ∧ LP.numpieza=l.provincia<>CASTELLON ∧ V2.
Para cada pieza comprada. Piezas que el vendedor numero 1 ofrece en la lista de suministros y que han sido servidas en algún pedido 3. Nombre y numero de las piezas que se han solicitado en algún pedido ordenadas por el nombre 6. Numero de vendedor y empresa para la que trabaja de aquellos que han vendido alguna pieza por un precio mayor que el estipulado por ellos en la lista de suministros 75 . Vendedores que pueden suministrarnos piezas que se venden al público con un precio de venta entre 50 y 100 y que esa pieza ha sido solicitada en algún momento (no necesariamente a ellos) 4. número de pieza y diferencia en euros entre el precio de compra y el de suministro del vendedor al que se le compró. 7. Toda la información de las piezas que puedan ser suministradas por vendedores de empresas cuyo nombre empieza por 'H'. ordenado descendentemente por dicha cantidad.adicionales 1 EJERCICIOS ADICIONALES CONSULTAS 1. Nombre de las empresas de Alicante a las que se ha comprado algún monitor 5. 2.
numpieza = ps.numpieza = p. preciounit.numvend = pd. linped l where p.numpedido and pd.numvend.numpieza and preciocompra > preciounit En AR VENDEDOR ∞ PRECIOSUM ∞ PEDIDO ∞ LINPED donde preciocompra > preciounit [numvend.numvend=PR. linped l where ps. V.preciocompra / PRECIOSUM(PR) ∧ LINPED(LP) ∧ ∃ P (PEDIDO(P) ∧ PR.numpedido and l.numpedido ∧ PR. pieza p.numpieza order by nompieza En AR PIEZA ∞ LINPED [numpieza.numpieza = p.numvend and pd.nompieza / PIEZA(P) ∧ ∃ LP (LINPED(LP) ∧ P.numpieza) } --6 select ps. pedido pd.numpieza = l.numpedido ∧ PR. preciosum ps where ps.preciounit.numpedido = l. nomvend] En CRT {V. PR.nomvend / VENDEDOR(V) ∧ ∃ PR(PRECIOSUM(PR) ∧ V.numpedido and l.numpieza = l. nombrecomer from vendedor v.numpieza from pieza p. preciounit .numpieza=LP. P.numvend=P. linped l.SOLUCIONES --3 select distinct numvend from preciosum ps. nompieza] En CRT {P.numvend and ps.numpieza = l. la resta como resultado.R. pedido pd.numpieza=PR.T. LP. linped l. preciocompra] En CRT {PR.numpedido = l.numpieza=LP. pieza p where v.numvend and v.numvend.numpieza ∧ preciocompra > p 76 .numvend ∧ ∃ P (PEDIDO(P) ∧ PR.numvend / PRECIOSUM(PR) ∧ ∃ P (PIEZA(P) ∧ P. p.numpieza ∧ preciovent≥5000 ∧ preciovent≤10000 ∧ ∃ LP (LINPED(LP) ∧ PR.numpieza)) } --4 select nombrecomer from vendedor v.numpieza and nompieza like '%MONITOR%' and provincia = 'ALICANTE' --5 select nompieza.numvend ∧ ∃ LP(LINPED(LP) ∧ P.numpedido=LP.numpieza.numpieza order by diferencia En AR PRECIOSUM ∞ PEDIDO ∞ LINPED [numpieza.numpieza) } En AR y C.numpieza=LP.numpieza and p.numvend ∧ P. --7 select distinct v.numvend = v.numpieza.: no está considerada.numpedido = pd. preciosum ps where l.numpedido=LP.numpieza and preciovent between 50 and 100 order by numvend En AR PIEZA ∞ PRECIOSUM ∞ LINPED donde preciovent≥50 and preciovent≤100 [numvend] En CRT {PR.numpieza=LP.numvend and pd.preciocompra from linped l.numvend = pd. en la formalización de estos lenguajes.numpieza. pedido pd.numvend = ps.numvend=P.
la media del precio unitario de cada pieza y el precio de venta de todas las piezas que puedan sernos suministradas por más de tres proveedores. 2. Para cada pedido obtener el número de líneas que tiene. Obtener para cada pieza. 7. Obtener número y nombre de las piezas que tengan una diferencia entre precio de venta y media de precio de suministro (preciounit) menor del 20% del precio de venta. 77 . la media del precio unitario de cada pieza y el precio de venta de todas las piezas de las que conocemos posibles suministradores. el número y nombre del vendedor y la fecha del pedido. ordenado alfabéticamente por vendedor. 6. 8. Obtener número y nombre de vendedores a los que les hayamos solicitado algún pedido. 3. 10. Obtener el número y el nombre de los vendedores y la cantidad de piezas que pueden suministrar ordenado alfabéticamente por vendedor. 5. Ordenar el resultado por el nombre de la pieza. 9. 4. la media del precio unitario de suministro y el precio de venta. teniendo en cuenta que la media de los precios unitarios debe estar entre 100 y 280. Obtener el nombre de las piezas. para las piezas de precio de venta mayor que 300. Obtener los números y nombres de los vendedores que han servido algún pedido con más de tres artículos diferentes. Obtener el nombre de los vendedores y la cantidad de piezas que pueden suministrar. la media del precio unitario de cada pieza y el precio de venta de todas las piezas de precio unitario medio mayor que 100 y que puedan ser suministradas por más de dos proveedores. Obtener el nombre de las piezas. Obtener el nombre de las piezas.adicionales 2 EJERCICIOS ADICIONALES CONSULTAS 1. el nombre de las pieza.
numpieza<>LP4.numvend=V.numvend.numpedido ∧ LP1.numpieza<> LINPED. pieza p where p. vendedor v where ps.numpieza<> LP3.nomvend.numpieza<>LP2. preciosum ps where p.numpieza and LP1.numpieza<>LP3.numpieza. preciovent having preciovent*20/100 > avg(preciounit) (necesito poner el preciovent en el group by porque es no calculado) 78 . preciovent from preciosum ps.numpieza<> LP3.numpedido=LP3.nompieza --10 select p.numpedido.count(*) cantidad from preciosum ps.numpieza and preciovent > 30000 group by p.numpieza. fecha (en este caso coincide count(*) y count(distinct numlinea) --7 select v.numpedido group by p. nomvend from vendedor v.preciovent having count(*)>2 and avg(preciounit) between 100 and 280 order by p.numvend.numpedido=LINPED.numpieza and LP2.count(*) cantidad from preciosum ps.numpieza and LP3. nompieza.numpedido and LP1.numpedido and LP2.numpieza=p.numvend=v.numvend=v.numpieza ∧ ∃ LP3 (LINPED(LP3) ∧ LP2. LP2. linped lp where v.numpieza<> LINPED.numvend. nompieza from pieza p.numpieza<>LP2. vendedor v where p.numpieza))))) } --4 select p.numpedido ∧ ∃ LP2 (LINPED(LP2) ∧ LP1. avg(preciounit) media.nomvend order by nomvend --6 select numvend.numpieza group by p.numvend and p.numpedido ∧ LP1.numvend = v.numpieza<>LINPED.v.numpieza group by nompieza. count(*).numpedido] ∞ PEDIDO ∞ VENDEDOR [numvend. V.nomvend order by nomvend --2 select v. p.numpieza ∧ LP2.numvend and p.numvend.numvend --8 select nompieza.SOLUCIONES --1 select nomvend.numpedido = lp. avg(preciosum).numpedido=LP2.numpedido=LP4.numvend.numvend = p.numpieza = ps.nomvend / VENDEDOR(V) ∧ ∃ P (PEDIDO(P) ∧ P.numpieza.numpieza ∧ LP3.numpedido.numvend group by v. preciovent from preciosum ps.numpieza<>LP4.numvend = p.numpieza<>LP4.numpieza<>LP3. p.numpieza ∧ LP2. linped l.numpieza = ps. nomvend] En CRT {V. nomvend. nomvend from vendedor v.nompieza.numpedido.numvend group by v. pieza p where ps.numvend.numpieza ∧ ∃ LP4 (LINPED(LP4) ∧ LP3. LP3 para LINPED LP1 × LP2 × LP3 × LINPED donde LP1. vendedor v where ps. nomvend.numpieza and LP2. nomvend having count(distinct numpieza)> 3 En AR Define alias LP1.numpieza and LP1.numpedido group by p.numpedido ∧ ∧ LP1. fecha from pedido p.numpedido=P.numpedido and LP3.numpedido=LP3.numvend) ∧ ∃ LP1 ( LINPED(LP1) ∧ LP1.numpedido=LP2.numpieza [LP1. pedido p. pedido p where v. preciovent having avg(preciosum) > 100 and count(*) > 2 (aquí también coincide count(*) y count(distinct numvend) --9 select nompieza.numvend.numpedido = l.numvend.
2. el importe total. para los pedidos de importe total superior a 10000 euros. 4. el número del vendedor al que se le solicitó. su nombre y la cantidad total de pedidos que se les ha solicitado. Ordena el resultado por el importe total. su nombre.adicionales 3 EJERCICIOS ADICIONALES CONSULTAS 1. Para las empresas que tengan un único vendedor. obtener el número de pedido. 6. 7. su nombre y el número y nombre del vendedor al que se le solicitaron. Ordena el resultado por el nombre de la pieza. obtener el número de la pieza. 5. el precio máximo al que nos la han ofrecido. obtener el número de la pieza. y el importe total entre todos los pedidos. obtener el nombre de la empresa y la cantidad total de pedidos que se le han solicitado. Obtener para los vendedores de la provincia de Alicante. el número de vendedor. Obtener la cantidad total de piezas que se solicitaron en el año 1992. Obtener el número de pedido. el precio mínimo y la cantidad de vendedores que nos la pueden suministrar. y el numero y nombre del vendedor al que se les solicitó. 79 . Para los pedidos que nos han sido servidos en más de un día. su nombre. y la cantidad de días distintos en los que nos han servido las piezas solicitadas. Para cada pieza que pueda ser suministrada a un precio medio unitario inferior a 10 euros. Para las piezas recibidas en domingo. 3.
pedido p. linped lp .numpieza.numvend.numvend = p. pieza p where ps.numvend. v.numpedido. pedido ped.numvend.numvend and p.numpieza. count(*) cantidadPED from vendedor v. nomvend from pieza p.numvend and to_char(fecharecep. nomvend.numvend. nomvend. v.numvend=p.numpedido and ped. vendedor v where lp.numpedido=ped.numpieza=p. min(preciounit) minPrecio.numpedido group by p. v. max(preciounit) maxPrecio.numpedido.numpieza.numvend.numpieza and lp. pedido p where v. ‘d’)=7 order by 2 80 . count(distinct fecharecep) from vendedor v.numpieza group by p.SOLUCIONES --1 select v. nompieza having avg(preciounit)<10 --5 select p. nomvend --4 select p. nompieza. nomvend having count(distinct fecharecep)> 1 --7 select p. linped lp where v.numpedido = lp. count(*) totalVend from preciosum ps.numvend=v.numvend and provincia=’ALICANTE’ group by v. nompieza.numpieza=p.
3. 5. Número y nombre de los vendedores a los que les hemos solicitado algún pedido en el año 1995 pero no les hemos solicitado ninguno en el año 1992. el número y nombre de vendedor junto con el importe total que les hemos pagado a través de todos los pedidos que se les ha solicitado. 2. 4. Obtener el nombre de las empresas de las que tengamos más de 1 de un vendedor de Alicante y no se les haya hecho ningún pedido (a ninguno de sus vendedores) antes del año 1995. Obtener el número de los vendedores (numvend) que nos hubiesen podido servir todo lo que se solicita en las líneas 1. Obtener el número y el nombre las piezas que puedan sernos suministradas por más de dos vendedores de la provincia de Alicante. 6. Obtener el número y nombre de las piezas que nunca hemos solicitado. Obtener para los vendedores de Alicante o Madrid. y que en total (entre todos los pedidos solicitados a todos los vendedores) hayamos pedido más de 500 unidades.adicionales 4 EJERCICIOS ADICIONALES CONSULTAS 1. 2 y 3 del pedido número 1. 81 .
numpieza.numvend.numvend. nomvend.nompieza / PIEZA(P) ∧ ⎤ ∃ LP(LINPED(LP) ∧ LP.numpieza ∧ PS.numvend.numpieza=lp.numlinea=3) ⇒ ∃ PS(PRECIOSUM(PS) ∧ PS. nompieza from linped lp.numvend and p.numlinea=2 ∨ LP.numvend=v.numpieza} --5 select numvend from preciosum ps.‘yyyy’)=1995 minus select v.numpieza En AR PIEZA[numpieza. V2 para VENDEDOR PRECIOSUM [numvend. nompieza] ∼ (PIEZA ∞ LINPED)[numpieza.numpedido=lp. linped lp where v.numpieza.numpedido=1 ∧ (LP. nomvend 82 .numlinea=1 ∨ LP.numpieza=lp.SOLUCIONES --1 select v.numpieza and numpedido=1 and numlinea=2 intersect select numvend from preciosum ps.numvend) ) } --6 select v.numpieza and numpedido=1 and numlinea=3 En AR Define alias V1. vendedor v where p.numvend / VENDEDOR(V) ∧ ∀ LP (LINPED(LP) ∧ LP.numpieza=P.numpieza=lp.numpieza=LP.numvend=p.numvend=v.‘yyyy’)=1992 --4 select numpieza.numvend and to_char(fecha. linped lp where ps.numvend and to_char(fecha.numpieza=p. nompieza from pieza minus select p. ‘MADRID’) group by v. nompieza] En CRT {P. nomvend from pedido p.numpedido and provincia I (‘ALICANTE’. sum(cantrecibida*preciocompra) Total from vendedor v. nomvend from pedido p. pedido p.numpieza and numpedido=1 and numlinea=1 intersect select numvend from preciosum ps. pieza p where lp. numpieza] ÷ LINPED DONDE (numpedido=1 and (numlinea=1 or numlinea=2 or numlinea=3)) [numpieza] En CRT {V. linped lp where ps.numvend. linped lp where ps.numvend=V. vendedor v where p. P.
Obtener el nombre de la empresa que tiene más de tres vendedores.adicionales 5 EJERCICIOS ADICIONALES CONSULTAS 1. 3. 6. y la fecha del pedido. Obtener los nombre y empresas de los vendedores que no son de la Comunidad Valenciana. Obtener para las piezas cuyo precio de suministro oscila entre 10 y 15. de los que se desconoce el teléfono. junto con el número y nombre de la pieza. 5. Obtener para el número y nombre de los vendedores de Alicante a los que se les haya solicitado alguna pieza. y al menos dos de ellos son de la provincia de Alicante. de la que nos habían indicado que su plazo de suministro sería superior a una semana. y el total de pedidos que les hemos hecho. 83 . su nombre. el número y nombre del vendedor al que se le solicitó. Obtener para los vendedores a los que les hayamos pagado en total (entre todos sus pedidos) más de 3000 euros. Ordena el resultado por la última columna. y la cantidad de vendedores que nos la pueden suministrar a ese precio. y la cantidad de pedidos distintos en los que se les ha solicitado. 4. Obtener para los pedidos con un importe total entre 400 y 600 euros. el número de la pieza. el número y nombre de vendedor junto con el importe total que les hemos pagado. 2. el número de pedido.
numvend. linped lp.numvend = pd. count(distinct numpedido) from vendedor v.numpedido.numpieza and p. fecha. fecha from pedido p. p.numpedido=lp.numpedido.’CASTELLON’. p.numvend and pd. nomvend having sum(ctdpedida*preciocompra) between 400 and 600 --4 select v. preciosum ps. nompieza. nompieza order by 5 --5 select nombrecomer from vendedor group by nombrecomer having count(*)>3 intersect select nombrecomer from vendedor where provincia=’AICANTE’ group by nombrecomer having count(*)>1 --6 select nomvend.numpedido and p. vendedor v where p.numpedido = lp. nomvend. nomvend.numvend=ps. v.numpieza.numpieza=lp. pedido pd. v.numvend.numvend and p.numvend and v.numvend=v.numvend. nombrecomer from vendedor where provincia not in (‘ALICANTE’. pieza p where v.numvend.numpieza and diassum>7 group by v.numpieza=ps.’VALENCIA’) and telefono is null 84 .SOLUCIONES --1 select p.numpieza. nomvend. linped lp.numpedido group by p.
3. Para la pieza (o piezas) de precio de venta mayor. Número de pieza. 8. Obtener el nombre de la pieza y su precio unitario medio de la pieza más barata de precio de venta. 9. 4. 7. 6. descripción. Nombre del vendedor y media de todos sus precios de suministro para los vendedores que viven en la misma ciudad que algún vendedor de apellido García. 2. y número y nombre de vendedor del pedido que no es el de menor importe de venta (preciocompra*cantrecibida) siendo el año del pedido posterior al 1992 (>1992).adicionales 6 EJERCICIOS CONSULTAS 1. Número de pedido. obtener el nombre de la pieza y número total de vendedores que la pueden suministrar. 5. Obtener el nombre de la pieza (o piezas) y su precio unitario máximo de la pieza de precio unitario medio más bajo. Nombre de los vendedores a los que se les haya solicitado más de dos pedidos en los que se sirvan discos duros. descripción y precio de venta de la pieza que más veces se ha pedido. Obtener el nombre de vendedor para los vendedores cuya media de precio unitario de suministro sea máxima. precio medio de suministro y descuento máximo de las piezas que proceden de la misma ciudad que la empresa Mecemsa y que se han solicitado 2 veces o más. 10. Número de pieza. 85 . Nombre y empresa de los vendedores de la ciudad a la que se le ha solicitado más piezas.
max(descuento) descuento from preciosum ps. avg(preciounit) mediasumin from vendedor v.numpieza.ciudad order by mediasumin --7 select numpedido. nomvend.numvend and ciudad IN (select ciudad from vendedor where nomvend like '%GARCIA%') group by v. nompieza. fecha from pedido p.numpieza=pz. preciosum ps where v.numvend=ps.numvend.nompieza order by ps. vendedor v where p.numvend=v. pieza pz.numpieza in (select numpieza from linped group by numpieza having count(*)>1) group by ps.numpieza and v. avg(preciounit) media.SOLUCIONES --2 select nomvend.numvend=ps.'YYYY')>1992 and numpedido in (select numpedido from linped group by numpedido having sum(preciocompra*cantrecibida) > any (select sum(preciocompra*cantrecibida) from linped group by numpedido)) --10 select ps.numpieza 86 . v.numpieza.numvend and v. vendedor v where ps.ciudad in (select ciudad from vendedor where nombrecomer='MECEMSA') and ps. nomvend.numvend.numvend and to_char(fecha.
6. junto con el número y nombre de vendedor que nos ha ofertado la pieza. pero al que nunca se la hemos solicitado. 87 . Número y nombre de los vendedores que oferten alguna de las piezas que pueden ser suministradas por el vendedor número 1. ordenado por el nombre de vendedor. pero que no oferten ninguna de las que puedan ser suministradas por el vendedor número 2. su nombre y la diferencia media entre el precio al que nos vende las piezas y el que nos había ofrecido por las mismas. Obtener un listado en el que figure el número y nombre de la pieza . Obtener. 2. para el vendedor que cumple que la diferencia de precio al que le compramos una pieza y el precio que nos había ofrecido por ella sea máxima. el número de vendedor.adicionales 7 EJERCICIOS CONSULTAS 1. Obtener el número de pieza de los teclados que nos han sido servidos en el mayo de cualquier año. Obtener los números y nombres de los vendedores que nos han servido más de tres artículos diferentes. 3. 4. Obtener el número y el nombre de los vendedores y la cantidad de piezas que pueden suministrar a un precio entre 15 y 20 euros. 5.
‘mm’)=5 --6 select v. linped lp where p.numvend=p.numpedido and ps. nomvend from vendedor v.numvend.numvend.numpieza=PS4. pedido p. nomvend. avg(preciocompra-preciounit) DifMedia from preciosum ps. linped lp where v. nomvend having count(distinct numpieza)>3 88 .numvend=V1.numvend and numpieza IN (select numpieza from preciosum where numvend=2)) --1 select numvend.nomvend / VENDEDOR(V1) ∧ ∃ PS1(PRECIOSUM(PS1) ∧ PS1. preciosum ps where v.numpedido=lp.numvend and lp.numpieza ∧ ⎤ ∃ PS3(PRECIOSUM(PS3) ∧ PS3. nomvend from vendedor v.numvend=V1.numvend=p. pedido p. pedido p. linped lp where p.numvend.numpieza) --4 select numpieza from pieza p. nomvend] En CRT {V1.numvend group by v. preciosum ps where v. vendedor v where p.numvend.numpieza=PS2.numvend and numpieza IN (select numpieza from preciosum where numvend=1) and numvend NOT IN ( select v.numpieza and nompieza like ‘%TECLADO%’ and to_char(fecharecep. V1.numvend=p.numpieza=ps.numvend.numvend=ps. nomvend from vendedor v. linped lp.numpedido group by v.numvend and p.numpieza=lp. nomvend] ∼ (PRECIOSUM donde numvend=2 [numpieza] ∞ PRECIOSUM ∞ VENDEDOR)[numvend.numpieza and p.numvend=2 ∧ PS3.numpedido=lp.numpedido=lp.numpieza)) ) ) } --3 select v.numvend.numvend=v.SOLUCIONES --1 select v.numvend and lp.numvend ∧ ∃ PS4(PRECIOSUM(PS4) ∧ PS4. nomvend having max(precicompra-preciounit) = (Select max(preciocompra–preciounit) from preciosum ps.numpedido and ps.numpieza=ps.numvend=1 ∧ PS1.numvend=ps. nomvend from vendedor where numvend IN ( select numvend from preciosum where numpieza IN (select numpieza from preciosum where numvend=1) MINUS select numvend from preciosum where numpieza IN (select numpieza from preciosum where numvend=2)) En AR (PRECIOSUM donde numvend=1 [numpieza] ∞ PRECIOSUM ∞ VENDEDOR)[numvend.numvend.numvend ∧ ∃ PS2(PRECIOSUM(PS2) ∧ PS2.
Documents Similar To practicas06.pdf

References: Resolución 
 resolución 
 Resolución 
 Resolución 
 Resolución 
 Resolución 
 Resolución 
 Resolución