Universidad Metropolitana de educación
Ciencia y Tecnología
Proyecto Final:
Base de Datos II
Integrantes:
Whitney Ifill 3-376-372
Marcos Zepeda 8-889-1892
Daniel Anaya 8-968-1568
Profesor:
ING. MANUEL BEIRO C.
Parte I – Confeccione la base de datos y tablas (20 puntos)
1. La base de datos se debe llamar CARS_PTY
2. Confeccione las tablas según el diagrama de entidad relación (Create Table)
CREATE TABLE PRODUCTOS
(COD_PRODUCTO INT NOT NULL PRIMARY KEY,
DESCRIPCION VARCHAR(50),
PRECIO NUMERIC(12,2),
MARCA VARCHAR(30)
)
CREATE TABLE CLIENTES
(COD_CLIENTE INT NOT NULL PRIMARY KEY,
NOMBRE VARCHAR(30),
APELLIDO VARCHAR(30),
CEDULA VARCHAR(30),
TELEFONO VARCHAR(30),
FEC_NACIMIENTO DATE,
DIRECCION VARCHAR(40),
CORREO VARCHAR(30)
)
CREATE TABLE PEDIDOS
(ID_PEDIDO INT NOT NULL PRIMARY KEY,
FECHA DATE,
COD_CLIENTEINT NOT NULL,
foreign key (COD_CLIENTE) REFERENCES CLIENTES(COD_CLIENTE)
)
CREATE TABLE PEDIDO_DETALLE
(ID_PEDIDO INT NOT NULL,
foreign key (ID_PEDIDO) REFERENCES PEDIDOS(ID_PEDIDO),
COD_PRODUCTO INT NOT NULL,
foreign key (COD_PRODUCTO) REFERENCES PRODUCTOS(COD_PRODUCTO),
CANTIDAD INT,
TOTAL NUMERIC(12,2)
)
Parte II – Inserción de Datos - (15 puntos)
Insertar los registros en las tablas utilizando los datos proporcionados en los cuadros de datos.
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (1, 'CITY LDX', 18500, 'HONDA');
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (2, 'CRV 2D', 26800, 'HONDA');
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (3, 'HI-LUX', 24000, 'TOYOTA');
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (4, 'RUSH', 20000, 'TOYOTA');
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (5, 'CS35 PLUS', 17500, 'CHANGAN');
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (6, 'ALVIS V3', 18000, 'CHANGAN');
INSERT INTO PRODUCTOS (COD_PRODUCTO, DESCRIPCION, PRECIO, MARCA)
VALUES (7, 'SWIFT SPORT', 15400, 'SUZUKI');
SELECT * FROM PRODUCTOS;
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (1, 'JUAN', 'REYES', '2-587-487', '233-2525', '1998-01-15', 'SAN FRANCISCO',
'
[email protected]');
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (2, 'YOHANA', 'PEREZ', '3-58-122', '255-8852', '1997-08-24', 'BETHANIA',
'
[email protected]');
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (3, 'CRISTEL', 'VILLAREAL', '8-758-965', '241-8212', '1992-04-02', 'EL CANGREJO',
'
[email protected]');
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (4, 'ANA PATRICIA', 'BUSTAMANTE', '3-75-454', '212-8744', '1989-07-08', 'EL PAICAL',
'
[email protected]');
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (5, 'MARIO', 'RODRIGUEZ', '8-755-413', '212-8746', '1990-09-25', 'LOS ROBLES',
'
[email protected]');
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (6, 'VANESSA', 'ESCUDERO', '3-58-968', '241-8215', '2000-11-15', 'BETHANIA',
'
[email protected]');
INSERT INTO CLIENTES(COD_CLIENTE, NOMBRE, APELLIDO, CEDULA, TELEFONO,
FEC_NACIMIENTO, DIRECCION, CORREO)
VALUES (7, 'DAVID', 'CARDENAS', '4-75-414', '212-8747', '1995-10-28', 'SAN ANTONIO',
'
[email protected]');
SELECT * FROM CLIENTES;
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (150, '2020-11-15', 1);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (151, '2020-11-15', 6);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (152, '2020-11-17', 5);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (153, '2020-11-17', 3);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (154, '2020-11-17', 5);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (155, '2020-11-23', 7);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (156, '2020-11-24', 1);
INSERT INTO PEDIDOS(ID_PEDIDO, FECHA, COD_CLIENTE)
VALUES (157, '2020-11-25', 3);
UPDATE PEDIDOS
SET FECHA = '2020-11-17'
WHERE ID_PEDIDO = 153;
UPDATE PEDIDOS
SET FECHA = '2020-11-17'
WHERE ID_PEDIDO = 154;
SELECT * FROM PEDIDOS;
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (150, 1, 1, 18500.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (151, 4, 1, 20000.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (151, 7, 1, 15400.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (152, 4, 1, 20000.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (153, 6, 1, 18000.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (154, 2, 1, 26800.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (155, 3, 1, 24000.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (155, 7, 1, 15400.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (156, 6, 1, 18000.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (157, 4, 1, 20000.00);
INSERT INTO PEDIDO_DETALLE(ID_PEDIDO, COD_PRODUCTO, CANTIDAD, TOTAL)
VALUES (157, 5, 1, 17500.00);
SELECT * FROM PEDIDO_DETALLE;
Parte III – Consultas (15 Puntos)
Crear las siguientes consultas de la base de datos:
1. Consulta donde se presente el nombre, apellido, teléfono y Fecha de nacimiento de
todos los clientes
SELECT NOMBRE, APELLIDO, TELEFONO, FEC_NACIMIENTO
FROM CLIENTES
2. Consulta donde se presente los productos con pedido mayor o igual a 25,000 dólares,
ordenados ascendente
o Producto, Marca, id_pedido, monto total_pedido
SELECT PRODUCTOS.DESCRIPCION, PRODUCTOS.MARCA,
PEDIDO_DETALLE.ID_PEDIDO, PEDIDO_DETALLE.TOTAL
FROM PRODUCTOS, PEDIDO_DETALLE
WHERE PRODUCTOS.COD_PRODUCTO = PEDIDO_DETALLE.COD_PRODUCTO
AND PEDIDO_DETALLE.TOTAL >= 25000;
3. Consulta que presente los pedidos generados en la fecha 17/11/2020 con los siguientes
datos:
o id_pedido, Nombre cliente, producto, cantidad, precio
SELECT PEDIDOS.ID_PEDIDO, CLIENTES.NOMBRE, PRODUCTOS.DESCRIPCION,
PEDIDO_DETALLE.CANTIDAD, PRODUCTOS.PRECIO
FROM CLIENTES, PRODUCTOS, PEDIDO_DETALLE, PEDIDOS
WHERE PEDIDOS.ID_PEDIDO = PEDIDO_DETALLE.ID_PEDIDO
AND PEDIDOS.ID_PEDIDO >= '2020-11-17' AND PEDIDOS.ID_PEDIDO <=
'2020-11-17';
4. Consulta que presente el nombre del cliente, la cantidad y monto total pedido.
o nombre, apellido, cantidad y monto total
SELECT CLIENTES.NOMBRE, CLIENTES.APELLIDO, PEDIDO_DETALLE.CANTIDAD,
PEDIDO_DETALLE.TOTAL
FROM CLIENTES, PEDIDO_DETALLE, PRODUCTOS
WHERE PRODUCTOS.COD_PRODUCTO = PEDIDO_DETALLE.COD_PRODUCTO
5. Consulta que presente el total pedido por marca: (Agrupado por Marca)
o Marca, cantidad total , monto total
SELECT MARCA, SUM(CANTIDAD), SUM(TOTAL)
FROM PRODUCTOS, PEDIDO_DETALLE
GROUP BY MARCA
Parte IV - Vistas (20 puntos)
Crear las siguientes vistas
5.1 Vista vw_pedidos
Con la siguiente salida:
Cod_producto, Nombre_producto, marca, cantidad total por producto,
Total del pedido por producto
CREATE VIEW vw_pedidos
AS
SELECT P.COD_PRODUCTO, P.DESCRIPCION, P.MARCA, PD.CANTIDAD, PD.TOTAL
FROM PRODUCTOS P, PEDIDO_DETALLE PD
WHERE P.COD_PRODUCTO = PD.COD_PRODUCTO;
5.2 Vista vw_produccion_marca
Con la siguiente salida:
Marca, cantidad total por marca, total del pedido por marca
CREATE VIEW vw_produccion_marca
AS
SELECT P.MARCA, SUM(PD.CANTIDAD) AS TOTAL_CANTIDAD, SUM(PD.TOTAL) AS
TOTAL_PEDIDO
FROM PRODUCTOS P, PEDIDO_DETALLE PD
WHERE P.COD_PRODUCTO = PD.COD_PRODUCTO GROUP BY MARCA
5.3 Vista vw_clientes
La vista debe presentar los datos de los clientes que tengan pedidos en la base de datos.
Con la siguiente salida:
Nombre del cliente, apellido, cantidad total, Total del pedido
CREATE VIEW vw_clientes
AS
SELECT NOMBRE, APELLIDO, SUM(PD.CANTIDAD) AS CANTIDAD_TOTAL,
SUM(PD.TOTAL) AS MONTO_TOTAL
FROM PEDIDO_DETALLE PD, PEDIDOS P, CLIENTES C
WHERE P.ID_PEDIDO = PD.ID_PEDIDO
AND C.COD_CLIENTE = P.COD_CLIENTE
GROUP BY NOMBRE, APELLIDO;
SELECT * FROM vw_pedidos;
SELECT * FROM vw_produccion_marca;
SELECT * FROM vw_clientes;
Parte V - Store Procedure (20 puntos)
6.1 Confeccione un Store Procedure con dos parámetros de entrada:
- Fecha inicial
- Fecha Final
Y el Store Procedure debe presentar sólo los datos de los Pedidos realizados en ese periodo de
tiempo
La salida del Store Procedure será:
--> Fecha, Nombre del producto, Nombre y apellido del cliente, cantidad, Total
6.2. Se desea poder aumentarles el precio a los productos fácilmente. Por eso solicita un programa
para aumentar un porcentaje de incremento a un producto en especial. Para ello se debe realizar
las siguientes operaciones:
Confeccione un Store Procedure que tenga como parámetros:
cod_producto
pje_incremento
Y la salida del Store Procedure será el producto afectado con su precio anterior y nuevo precio:
--> Código de producto, Nombre del producto, precio anterior, nuevo precio
Parte VI - Backup (10 puntos)
Confeccione un backup de la base de datos CARS_PTY
"C:\Users\WHITNEY\OneDrive\Documentos\CLASES WHITNEY\BASE DE DATOS II\backup
PROYECTO FINAL.sql"
