Documentos
Editando: dbguia1-2.md
Basado en v1
Cancelar
Historial
Tu nombre
Insertar tabla
Insertar diagrama
Insertar esquema
Guardar
--- grupo: G0X tarea: Consultas SQL sobre Guía 1 y Guía 2 motor: Oracle Database fecha_entrega: 2026-07-24 integrantes: - carnet: 2026010179 nombre: Tatiana Vanessa Mendoza Ruano - carnet: 2026010235 nombre: Luis Ernesto Galdámez Vides - carnet: 2026011679 nombre: Herbert Geovanny Solano Chávez - carnet: 2026010976 nombre: Jorge Antonio Velásquez Vásquez - carnet: 2026011059 nombre: Roberto Alejandro Medina Rodas - carnet: 2026010071 nombre: Irvin Alexander González Benítez --- # Consultas SQL — Guía 1 y Guía 2 > **Objetivo.** Crear un esquema mínimo de comercio electrónico (clientes, productos, pedidos) y resolver > cuatro ejercicios: dos de consulta simple, uno de manipulación de datos y uno de agregación. | Consulta | Guía | Tema | Comandos | Peso | |:--------:|:----:|------|----------|:----:| | 1 | Guía 1 | Elementos de BD | `SELECT` · `ORDER BY` | 25 % | | 2 | Guía 1 | Elementos de BD | `SELECT` · `WHERE` · subconsulta | 25 % | | 3 | Guía 2 | Comandos SQL | `INSERT` · `UPDATE` · `COMMIT` | 25 % | | 4 | Guía 2 | Funciones de agregación | `COUNT` · `SUM` · `GROUP BY` | 25 % | --- ## Índice 1. [Modelo de datos](#1-modelo-de-datos) 2. [Script de creación y carga](#2-script-de-creación-y-carga) 3. [Estado inicial de la base](#3-estado-inicial-de-la-base) 4. [Cómo procesa Oracle una consulta](#4-cómo-procesa-oracle-una-consulta) 5. [Consulta 1 — Listado ordenado de clientes](#consulta-1--listado-ordenado-de-clientes) 6. [Consulta 2 — Pedidos de un cliente específico](#consulta-2--pedidos-de-un-cliente-específico) 7. [Consulta 3 — Alta de producto y ajuste de stock](#consulta-3--alta-de-producto-y-ajuste-de-stock) 8. [Consulta 4 — Pedidos y gasto por cliente](#consulta-4--pedidos-y-gasto-por-cliente) 9. [Resumen de resultados](#9-resumen-de-resultados) --- ## 1. Modelo de datos ```mermaid erDiagram CLIENTES ||--o{ PEDIDOS : "realiza" CLIENTES { NUMBER_5 id_cliente PK "Clave primaria" VARCHAR2_60 nombre "NOT NULL" VARCHAR2_60 email VARCHAR2_20 telefono } PEDIDOS { NUMBER_6 id_pedido PK "Clave primaria" NUMBER_5 id_cliente FK "Referencia a CLIENTES" DATE fecha NUMBER_8_2 total } PRODUCTOS { NUMBER_5 id_producto PK "Clave primaria" VARCHAR2_60 nombre "NOT NULL" NUMBER_6_2 precio NUMBER_5 stock } ``` **Lectura de la cardinalidad:** un cliente puede tener **cero, uno o muchos** pedidos (`||--o{`); cada pedido pertenece a **exactamente un** cliente. --- ## 2. Script de creación y carga Este script crea el esquema de las db al ejecutarlo una vez ```sql -- ============================================================ -- DDL: definición de estructuras -- ============================================================ CREATE TABLE clientes ( id_cliente NUMBER(5) PRIMARY KEY, nombre VARCHAR2(60) NOT NULL, email VARCHAR2(60), telefono VARCHAR2(20) ); CREATE TABLE productos ( id_producto NUMBER(5) PRIMARY KEY, nombre VARCHAR2(60) NOT NULL, precio NUMBER(6,2), stock NUMBER(5) ); CREATE TABLE pedidos ( id_pedido NUMBER(6) PRIMARY KEY, id_cliente NUMBER(5) REFERENCES clientes(id_cliente), fecha DATE, total NUMBER(8,2) ); -- ============================================================ -- DML: carga de datos de referencia -- (se listan las columnas de forma explícita: buena práctica) -- ============================================================ INSERT INTO clientes (id_cliente, nombre, email, telefono) VALUES (1, 'Carlos Gómez', 'carlos@mail.com', '7011-2233'); INSERT INTO clientes (id_cliente, nombre, email, telefono) VALUES (2, 'Beatriz Lima', 'bea@mail.com', '7022-3344'); INSERT INTO clientes (id_cliente, nombre, email, telefono) VALUES (3, 'Jorge Alas', 'jorge@mail.com', '7033-4455'); INSERT INTO clientes (id_cliente, nombre, email, telefono) VALUES (4, 'Rosa Martínez', 'rosa@mail.com', '7044-5566'); INSERT INTO productos (id_producto, nombre, precio, stock) VALUES (1, 'Mouse inalámbrico', 12.50, 40); INSERT INTO productos (id_producto, nombre, precio, stock) VALUES (2, 'Teclado mecánico', 35.00, 15); INSERT INTO productos (id_producto, nombre, precio, stock) VALUES (3, 'Monitor 24"', 145.00, 8); INSERT INTO productos (id_producto, nombre, precio, stock) VALUES (4, 'Cámara web HD', 28.00, 10); INSERT INTO pedidos (id_pedido, id_cliente, fecha, total) VALUES (501, 1, DATE '2026-07-10', 47.50); INSERT INTO pedidos (id_pedido, id_cliente, fecha, total) VALUES (502, 2, DATE '2026-07-11', 145.00); INSERT INTO pedidos (id_pedido, id_cliente, fecha, total) VALUES (503, 1, DATE '2026-07-12', 35.00); INSERT INTO pedidos (id_pedido, id_cliente, fecha, total) VALUES (504, 3, DATE '2026-07-13', 12.50); INSERT INTO pedidos (id_pedido, id_cliente, fecha, total) VALUES (505, 1, DATE '2026-07-14', 28.00); COMMIT; ``` ### Orden obligatorio de ejecución ```mermaid flowchart TD A["CREATE TABLE clientes"] --> B["CREATE TABLE productos"] B --> C["CREATE TABLE pedidos"] C --> D["INSERT en clientes<br/>4 filas"] D --> E["INSERT en pedidos<br/>5 filas"] E --> F{"¿Cada id_cliente<br/>existe en clientes?"} F -- "Sí" --> G["COMMIT<br/>cambios permanentes"] F -- "No" --> H["ORA-02291<br/>integridad referencial violada"] style G fill:#d3f9d8,stroke:#2b8a3e,color:#1b4332 style H fill:#ffe3e3,stroke:#c92a2a,color:#7f1d1d ``` **Por qué importa el orden:** - `pedidos` se crea **después** de `clientes` porque su `FOREIGN KEY` apunta a esa tabla; al revés Oracle lanzaría `ORA-00942: table or view does not exist`. - Los pedidos se insertan **después** de los clientes: el pedido 501 exige que el cliente 1 ya exista. - `productos` es independiente, puede crearse en cualquier momento. --- ## 3. Estado inicial de la base Así queda la base justo después del `COMMIT` del script anterior. ### `CLIENTES` — 4 filas | id_cliente | nombre | email | telefono | |:---:|---|---|---| | 1 | Carlos Gómez | carlos@mail.com | 7011-2233 | | 2 | Beatriz Lima | bea@mail.com | 7022-3344 | | 3 | Jorge Alas | jorge@mail.com | 7033-4455 | | 4 | Rosa Martínez | rosa@mail.com | 7044-5566 | ### `PRODUCTOS` — 4 filas | id_producto | nombre | precio | stock | |:---:|---|---:|---:| | 1 | Mouse inalámbrico | 12.50 | 40 | | 2 | Teclado mecánico | 35.00 | 15 | | 3 | Monitor 24" | 145.00 | 8 | | 4 | Cámara web HD | 28.00 | 10 | ### `PEDIDOS` — 5 filas | id_pedido | id_cliente | fecha | total | |:---:|:---:|---|---:| | 501 | 1 | 2026-07-10 | 47.50 | | 502 | 2 | 2026-07-11 | 145.00 | | 503 | 1 | 2026-07-12 | 35.00 | | 504 | 3 | 2026-07-13 | 12.50 | | 505 | 1 | 2026-07-14 | 28.00 | ### Cómo se enlazan las filas ```mermaid flowchart LR subgraph CL["CLIENTES"] C1["1 · Carlos Gómez"] C2["2 · Beatriz Lima"] C3["3 · Jorge Alas"] C4["4 · Rosa Martínez"] end subgraph PE["PEDIDOS"] P1["501 · 47.50"] P3["503 · 35.00"] P5["505 · 28.00"] P2["502 · 145.00"] P4["504 · 12.50"] end C1 --> P1 C1 --> P3 C1 --> P5 C2 --> P2 C3 --> P4 C4 -.- N["sin pedidos"] style C4 fill:#fff3bf,stroke:#e67700,color:#663c00 style N fill:#fff3bf,stroke:#e67700,color:#663c00 ``` > 🔎 **Rosa Martínez (id 4) no tiene pedidos.** Ese detalle será clave en la Consulta 4. --- ## 4. Cómo procesa Oracle una consulta El orden en que **escribimos** una consulta no es el orden en que el motor la **evalúa**: ```mermaid flowchart LR F["1 · FROM<br/>elige la tabla"] --> W["2 · WHERE<br/>filtra filas"] W --> G["3 · GROUP BY<br/>agrupa filas"] G --> H["4 · HAVING<br/>filtra grupos"] H --> S["5 · SELECT<br/>proyecta columnas"] S --> O["6 · ORDER BY<br/>ordena la salida"] style F fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style O fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 ``` Esto explica dos cosas que aparecen más adelante: - En la Consulta 4 se puede usar el alias `gasto_total` dentro del `ORDER BY` (paso 6, después del `SELECT`), pero **no** dentro del `WHERE` (paso 2, antes del `SELECT`). - Con `GROUP BY`, el `SELECT` solo puede mostrar columnas agrupadas o funciones de agregación, porque para entonces las filas individuales ya se fusionaron. --- ## Consulta 1 — Listado ordenado de clientes **Guía 1: Elementos de BD · Peso 25 %** > **Enunciado.** Listar el nombre y el email de todos los clientes, ordenados alfabéticamente por nombre. ### SQL ```sql SELECT nombre, email FROM clientes ORDER BY nombre ASC; ``` ### Flujo de ejecución ```mermaid flowchart TD A["FROM clientes<br/>lee las 4 filas · full table scan"] --> B["WHERE<br/>no hay filtro: pasan las 4"] B --> C["SELECT nombre, email<br/>descarta id_cliente y telefono"] C --> D["ORDER BY nombre ASC<br/>ordena alfabéticamente"] D --> E["Result set<br/>4 filas · 2 columnas"] style A fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style E fill:#d3f9d8,stroke:#2b8a3e,color:#1b4332 ``` ### Qué ocurre por dentro 1. Sin `WHERE`, Oracle recorre la tabla completa; no puede aprovechar el índice de la clave primaria. 2. La proyección (`SELECT nombre, email`) reduce el ancho de cada fila: viajan 2 columnas en vez de 4. 3. El `ORDER BY` es la operación más costosa; sobre 4 filas es instantáneo, pero sobre millones requeriría área de ordenamiento en memoria o disco temporal. 4. **La tabla no se modifica.** Un `SELECT` es de solo lectura. ### Resultado esperado | nombre | email | |---|---| | Beatriz Lima | bea@mail.com | | Carlos Gómez | carlos@mail.com | | Jorge Alas | jorge@mail.com | | Rosa Martínez | rosa@mail.com | El orden cambió respecto al de inserción: **B → C → J → R**. `ASC` es el valor por defecto, se escribe solo por claridad. --- ## Consulta 2 — Pedidos de un cliente específico **Guía 1: Elementos de BD · Peso 25 %** > **Enunciado.** Mostrar todos los pedidos del cliente "Carlos Gómez" (busca primero su `id_cliente`), > incluyendo `id_pedido`, `fecha` y `total`. ### SQL ```sql -- Paso previo: averiguar el identificador del cliente SELECT id_cliente FROM clientes WHERE nombre = 'Carlos Gómez'; -- devuelve 1 -- Consulta solicitada SELECT id_pedido, fecha, total FROM pedidos WHERE id_cliente = 1; ``` ### Versión recomendada — subconsulta Fijar el `1` a mano funciona hoy, pero se rompe si cambian los datos. La subconsulta resuelve el identificador en tiempo de ejecución: ```sql SELECT id_pedido, fecha, total FROM pedidos WHERE id_cliente = (SELECT id_cliente FROM clientes WHERE nombre = 'Carlos Gómez'); ``` ### Flujo de ejecución ```mermaid flowchart TD A["Subconsulta:<br/>FROM clientes WHERE nombre = 'Carlos Gómez'"] --> B{"¿Cuántas filas<br/>devuelve?"} B -- "Exactamente 1" --> C["Sustituye el valor: id_cliente = 1"] B -- "Ninguna" --> D["Comparación con NULL<br/>result set vacío"] B -- "Más de una" --> E["ORA-01427<br/>single-row subquery returns more than one row"] C --> F["FROM pedidos<br/>lee las 5 filas"] F --> G["WHERE id_cliente = 1<br/>descarta 502 y 504"] G --> H["SELECT id_pedido, fecha, total"] H --> I["Result set<br/>3 filas"] style C fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style I fill:#d3f9d8,stroke:#2b8a3e,color:#1b4332 style D fill:#fff3bf,stroke:#e67700,color:#663c00 style E fill:#ffe3e3,stroke:#c92a2a,color:#7f1d1d ``` ### El filtro sobre la tabla, fila por fila | id_pedido | id_cliente | fecha | total | ¿Pasa el `WHERE`? | |:---:|:---:|---|---:|:---:| | 501 | 1 | 2026-07-10 | 47.50 | ✅ | | 502 | 2 | 2026-07-11 | 145.00 | ❌ | | 503 | 1 | 2026-07-12 | 35.00 | ✅ | | 504 | 3 | 2026-07-13 | 12.50 | ❌ | | 505 | 1 | 2026-07-14 | 28.00 | ✅ | ### Resultado esperado | id_pedido | fecha | total | |:---:|---|---:| | 501 | 2026-07-10 | 47.50 | | 503 | 2026-07-12 | 35.00 | | 505 | 2026-07-14 | 28.00 | **3 filas.** Suma: 110.50 (dato que reaparece en la Consulta 4). --- ## Consulta 3 — Alta de producto y ajuste de stock **Guía 2: Comandos SQL · Peso 25 %** > **Enunciado.** Insertar un nuevo producto ("Audífonos Bluetooth", precio 22.00, stock 25) y luego > actualizar el stock de "Mouse inalámbrico" restando 3 unidades. Confirmar los cambios. ### SQL ```sql INSERT INTO productos (id_producto, nombre, precio, stock) VALUES (5, 'Audífonos Bluetooth', 22.00, 25); UPDATE productos SET stock = stock - 3 WHERE id_producto = 1; COMMIT; ``` > **`stock = stock - 3`, no `stock = 37`.** La expresión lee el valor actual y le resta 3, así el > resultado sigue siendo correcto aunque otra transacción haya cambiado el stock antes. ### Ciclo de la transacción ```mermaid flowchart TD A["Inicio implícito de transacción"] --> B["INSERT producto 5"] B --> C{"¿id_producto 5<br/>ya existe?"} C -- "No" --> D["Fila creada<br/>1 row inserted"] C -- "Sí" --> E["ORA-00001<br/>unique constraint violated"] D --> F["UPDATE productos<br/>WHERE id_producto = 1"] F --> G{"¿Coincide alguna fila?"} G -- "Sí" --> H["stock: 40 → 37<br/>1 row updated"] G -- "No" --> I["0 rows updated<br/>sin error, sin cambios"] H --> J{"¿Confirmar?"} J -- "COMMIT" --> K["Cambios permanentes<br/>visibles para todos"] J -- "ROLLBACK" --> L["Todo se deshace<br/>vuelve al estado inicial"] style K fill:#d3f9d8,stroke:#2b8a3e,color:#1b4332 style E fill:#ffe3e3,stroke:#c92a2a,color:#7f1d1d style I fill:#fff3bf,stroke:#e67700,color:#663c00 style L fill:#fff3bf,stroke:#e67700,color:#663c00 ``` ### La tabla `PRODUCTOS` antes y después **Antes — 4 filas** | id_producto | nombre | precio | stock | |:---:|---|---:|---:| | 1 | Mouse inalámbrico | 12.50 | **40** | | 2 | Teclado mecánico | 35.00 | 15 | | 3 | Monitor 24" | 145.00 | 8 | | 4 | Cámara web HD | 28.00 | 10 | **Después del `COMMIT` — 5 filas** | id_producto | nombre | precio | stock | Cambio | |:---:|---|---:|---:|---| | 1 | Mouse inalámbrico | 12.50 | **37** | 🔄 `40 - 3` | | 2 | Teclado mecánico | 35.00 | 15 | — | | 3 | Monitor 24" | 145.00 | 8 | — | | 4 | Cámara web HD | 28.00 | 10 | — | | **5** | **Audífonos Bluetooth** | **22.00** | **25** | ➕ fila nueva | ### Qué ocurre por dentro 1. El `INSERT` abre una transacción y escribe la fila en un bloque de datos, además de registrar el cambio en el *redo log* y guardar la imagen previa en el segmento de *undo*. 2. Hasta el `COMMIT`, **solo tu sesión ve los cambios**; otra sesión sigue leyendo `stock = 40`. 3. El `UPDATE` bloquea la fila del producto 1; cualquier otra sesión que intente modificarla queda esperando. 4. `COMMIT` hace los cambios permanentes y libera los bloqueos. Un `ROLLBACK` en su lugar habría restaurado la tabla al estado "Antes". ### Verificación ```sql SELECT id_producto, nombre, precio, stock FROM productos ORDER BY id_producto; ``` --- ## Consulta 4 — Pedidos y gasto por cliente **Guía 2: Funciones de agregación · Peso 25 %** > **Enunciado.** Mostrar, para cada cliente (`id_cliente`), cuántos pedidos ha realizado y cuánto ha > gastado en total, ordenado de mayor a menor gasto total. ### SQL ```sql SELECT id_cliente, COUNT(id_pedido) AS total_pedidos, SUM(total) AS gasto_total FROM pedidos GROUP BY id_cliente ORDER BY gasto_total DESC; ``` ### Flujo de ejecución ```mermaid flowchart TD A["FROM pedidos<br/>5 filas"] --> B["GROUP BY id_cliente<br/>forma 3 grupos"] B --> C["Grupo 1<br/>pedidos 501, 503, 505"] B --> D["Grupo 2<br/>pedido 502"] B --> E["Grupo 3<br/>pedido 504"] C --> F["COUNT = 3<br/>SUM = 110.50"] D --> G["COUNT = 1<br/>SUM = 145.00"] E --> H["COUNT = 1<br/>SUM = 12.50"] F --> I["ORDER BY gasto_total DESC"] G --> I H --> I I --> J["Result set<br/>3 filas"] style A fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style J fill:#d3f9d8,stroke:#2b8a3e,color:#1b4332 ``` ### El plegado de filas, paso a paso ```mermaid flowchart LR subgraph ORIG["FILAS ORIGINALES (5)"] direction TB R501["501 · cliente 1 · 47.50"] R503["503 · cliente 1 · 35.00"] R505["505 · cliente 1 · 28.00"] R502["502 · cliente 2 · 145.00"] R504["504 · cliente 3 · 12.50"] end subgraph GRUPOS["GRUPOS (3) — GROUP BY id_cliente"] direction TB G1["cliente 1<br/>cnt = 3<br/>suma = 110.50"] G2["cliente 2<br/>cnt = 1<br/>suma = 145.00"] G3["cliente 3<br/>cnt = 1<br/>suma = 12.50"] end subgraph ORD["ORDENADO (3) — ORDER BY suma DESC"] direction TB O1["cliente 2 · 1 · 145.00"] O2["cliente 1 · 3 · 110.50"] O3["cliente 3 · 1 · 12.50"] end R501 -- SUM --> G1 R503 -- SUM --> G1 R505 -- SUM --> G1 R502 -- SUM --> G2 R504 -- SUM --> G3 G2 --> O1 G1 --> O2 G3 --> O3 style R501 fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style R503 fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style R505 fill:#e7f5ff,stroke:#1971c2,color:#0b3d66 style R502 fill:#fff3bf,stroke:#e67700,color:#5c3d00 style R504 fill:#f3d9fa,stroke:#9c36b5,color:#4a1259 style G1 fill:#d0ebff,stroke:#1971c2,color:#0b3d66 style G2 fill:#ffe8a3,stroke:#e67700,color:#5c3d00 style G3 fill:#eebefa,stroke:#9c36b5,color:#4a1259 style O1 fill:#ffe8a3,stroke:#e67700,color:#5c3d00 style O2 fill:#d0ebff,stroke:#1971c2,color:#0b3d66 style O3 fill:#eebefa,stroke:#9c36b5,color:#4a1259 ``` ### Resultado esperado | id_cliente | total_pedidos | gasto_total | |:---:|:---:|---:| | 2 | 1 | **145.00** | | 1 | 3 | 110.50 | | 3 | 1 | 12.50 | ### Dos detalles que suelen restar puntos **1. Rosa Martínez (id 4) no aparece.** La consulta parte de `pedidos`, y esa tabla no contiene ninguna fila suya. Para mostrarla con `0` y `0.00` hay que salir desde `clientes` con un `LEFT JOIN`: ```sql SELECT c.id_cliente, c.nombre, COUNT(p.id_pedido) AS total_pedidos, NVL(SUM(p.total), 0) AS gasto_total FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id_cliente GROUP BY c.id_cliente, c.nombre ORDER BY gasto_total DESC; ``` | id_cliente | nombre | total_pedidos | gasto_total | |:---:|---|:---:|---:| | 2 | Beatriz Lima | 1 | 145.00 | | 1 | Carlos Gómez | 3 | 110.50 | | 3 | Jorge Alas | 1 | 12.50 | | 4 | Rosa Martínez | **0** | **0.00** | `COUNT(p.id_pedido)` cuenta 0 porque ignora los `NULL`; `NVL` convierte el `SUM` nulo en `0`. **2. Más pedidos ≠ más gasto.** Carlos hizo 3 pedidos pero gastó menos que Beatriz, que hizo 1 solo de 145.00. El `ORDER BY` responde exactamente a lo que pide el enunciado: gasto, no cantidad. --- ## 9. Resumen de resultados | # | Comando principal | Filas devueltas | ¿Modifica datos? | Resultado clave | |:-:|---|:---:|:---:|---| | 1 | `SELECT … ORDER BY` | 4 | No | Clientes de la B a la R | | 2 | `SELECT … WHERE` | 3 | No | Pedidos 501, 503, 505 | | 3 | `INSERT` + `UPDATE` + `COMMIT` | — | **Sí** | Producto 5 creado · stock 40 → 37 | | 4 | `COUNT` + `SUM` + `GROUP BY` | 3 | No | Beatriz encabeza con 145.00 |
Insertar tabla
Filas
Columnas
Cancelar
Insertar