DATABASE FOUNDATIONS
(ORACLE)
Evaluación de aptitudes
Apellido y Nombre: Alfaro Huamani Maycol , Orellano Torres Darwin
1. Realizar las siguientes consultas:
1.1. Consulta de empleados que me muestre su Apellido_pat, Apellido_Mat, Nombre y
DNI ordenado por Apellido_Pat empezando de la letra A
SELECT Apellido_Pat, Apellido_Mat, Nombre, DNI FROM empleados
WHERE Apellido_Pat LIKE 'A%'
ORDER BY Apellido_Pat;
1.2. Consulta de empleados que me muestre solo aquellos empleados que tengan la letra
M en su apellido Paterno ordenado por apellido paterno empezando de la letra A.
SELECT Apellido_Pat, Apellido_Mat, Nombre, DNI
FROM empleados
WHERE Apellido_Pat LIKE 'A%m%'
ORDER BY Apellido_Pat;
1.3. Consulta de empleados que me muestre solo aquellos que su edad esté entre los 50 y
65 años de edad, ordenados de mayor a menor.
SELECT Apellido_Pat, Apellido_Mat, Nombre, DNI, Edad
FROM empleados
WHERE Edad BETWEEN 50 AND 65
ORDER BY Edad DESC;
1.4. Consulta de empleados donde el DNI empiece con el número 4
SELECT Apellido_Pat, Apellido_Mat, Nombre, DNI, Edad
FROM empleados
WHERE DNI LIKE '4%'
1.5. Consulta que me liste los DNI, Nombres, Apellido Paterno y edad
SELECT DNI, Nombre, Apellido_Pat, Edad
FROM empleados;
1.6. Consulta que me muestre solo aquellos clientes que su edad sea 18, 20 0 30
SELECT DNI, Nombre, Apellido_Pat, Edad
FROM clientes
WHERE Edad IN (18, 20, 30);
1.7. Consulta que me muestre solo aquellos clientes que su nombre contenga la palabra
“RI”
SELECT DNI, Nombre, Apellido_Pat, Edad
FROM clientes
WHERE UPPER(Nombre) LIKE '%RI%';
1.8. Consulta que me muestre todos los productos que su precio no supere 20 soles
SELECT *
FROM productos
WHERE Precio <= 20;
1.9. Consulta que me muestre todos los productos donde su cantidad sea entre 0 a 10
SELECT *
FROM productos
WHERE Cantidad BETWEEN 0 AND 10;
1.10. Consulta donde me muestre Nombre, precio y cantidad de la tabla productos
ordenado por precio de menor a mayor.
SELECT*FROM productos
ORDER BY precio ASC
1.11. Consulta que me muestre los productos que su cantidad sea mayor igual a 10 o su
precio sea mayor igual a 15
SELECT *
FROM tabla_productos
WHERE cantidad >= 10 OR precio >= 15;
1.12. Consulta que me muestre las ventas realizadas los meses de enero y febrero del año
en curso.
SELECT
ventas.id_venta,
clientes.nombre AS nombre_cliente,
empleados.nombre AS nombre_empleado,
ventas.fecha_pedido,
SUM(detalle_ventas.cantidad) AS cantidad_vendida,
SUM(detalle_ventas.monto_parcial) AS monto_total_vendido
FROM
ventas
INNER JOIN
clientes ON ventas.id_cliente = clientes.id_cliente
INNER JOIN
empleados ON ventas.id_empleado = empleados.id_empleado
INNER JOIN
detalle_ventas ON ventas.id_venta = detalle_ventas.id_venta
INNER JOIN
productos ON detalle_ventas.id_producto = productos.id_producto
WHERE
EXTRACT(MONTH FROM ventas.fecha_pedido) IN (1, 2)
AND EXTRACT(YEAR FROM ventas.fecha_pedido) = EXTRACT(YEAR FROM SYSDATE)
GROUP BY
ventas.id_venta,
clientes.nombre,
empleados.nombre,
ventas.fecha_pedido;
1.13. Consulta que me muestre el Nombre, Apellido_pat , id_venta y Monto Total de los
clientes que realizaron una compra mayor a 200 soles.
SELECT
clientes.nombre,
clientes.apellido_pat,
ventas.id_venta,
SUM(ventas.monto_total) AS monto_total
FROM
clientes
JOIN
ventas ON clientes.id_cliente = ventas.id_cliente
WHERE
ventas.monto_total > 200
GROUP BY
clientes.nombre, clientes.apellido_pat, ventas.id_venta;
1.14. Consulta que me muestre el Nombre, Apellido_pat, Id_venta y Fecha_pedido de los
empleados realizaron ventas en el mes de enero.
SELECT
empleados.nombre,
empleados.apellido_pat,
ventas.id_venta,
ventas.fecha_pedido
FROM
empleados
INNER JOIN
ventas
ON
empleados.id_empleado = ventas.id_empleado
WHERE
EXTRACT(MONTH FROM ventas.fecha_pedido) = 1
1.15. Consulta que me muestre los productos vendidos entre el mes de enero y febrero.
SELECT
ventas.id_venta,
productos.id_producto,
productos.nombre,
ventas.fecha_pedido
FROM
ventas
INNER JOIN
productos
ON
ventas.id_venta = productos.id_producto
WHERE
EXTRACT(MONTH FROM ventas.fecha_pedido) IN (1, 2);
1.16. Consulta que me muestre el Nombre y descripción de los productos vendidos con
una cantidad superior a 10.
SELECT productos.id_producto, productos.descripcion
FROM detalle_ventas
JOIN productos ON detalle_ventas.id_producto = productos.id_producto
WHERE detalle_ventas.cantidad > 10;
1.17. Consulta que me muestre los detalles de ventas de los id_ventas (1,2,3,5)
SELECT *
FROM detalle_ventas
WHERE id_detalle IN (1, 2, 3, 5);
1.18. Consulta que me permita ver el resumen de ventas de los clientes que compraron,
empleados que vendieron y qué productos se vendió.
SELECT
clientes.nombre,
empleados.nombre,
productos.nombre,
SUM(detalle_ventas.cantidad) AS cantidad_vendida,
SUM(detalle_ventas.monto_parcial) AS monto_total_vendido
FROM
ventas
INNER JOIN
clientes
ON
ventas.id_venta = clientes.id_cliente
INNER JOIN
empleados
ON
ventas.id_venta = empleados.id_empleado
INNER JOIN
detalle_ventas
ON
ventas.id_venta = detalle_ventas.id_venta
INNER JOIN
productos
ON
detalle_ventas.id_producto = productos.id_producto
GROUP BY
clientes.nombre,
empleados.nombre,
productos.nombre;
1.19. Consulta que me muestre todos los productos donde nombre empiece con la
Palabra “Pa”
SELECT
nombre
FROM
productos
WHERE
nombre LIKE 'Pa%';
DIAGRAMA DE MODELO FÍSICO
2. Según las tablas que creaste ingresa datos por cada uno de las tablas utilizando las
sentencias DML, 8 registros por tabla.
3. Realice las siguientes consultas
• Realiza unca consulta donde realice la suma de precio de la tabla detalle_ventas
agrupado por productos.
• Realice una consulta donde solo muestre el 25% de la tabla ventas.
• Realice una consulta a la tabla detalle_ventas donde te devuelva registros únicos y no
duplicados
• Realice una consulta donde el precio de tu tabla productos muestre en un rango de 15
a 100 soles
4. Mediante sentencias DCL crearás dos usuarios llamados SENATI1 y SENATI2 con
contraseña 123 donde realizarás las siguientes acciones:
• SENATI1 y SENATI2 se deben de crear en la base de datos Ventas.
• Crearás un Rol llamado area_ventas donde tendrá los siguientes priviegios, podrá
realizar insertar, consulta y modificaciones a las tablas ventas, detalle_ventas.
• Agregar el usuario SENATI1 al rol área_ventas.
• El usuario SENATI2 podrá realizar inserción, consultas, modificación y eliminar
cualquier tabla de la base de datos ventas.
5. Realiza una transacción con los comandos TCL donde te permita insertar nuevos
registros en la tabla ventas1, detalle_ventas y te permita actualizar el precio de la
tabla productos un 10% menos por cada venta. Recuerda que si alguna instrucción
falla ninguna se debe realizar. (Utilizarás un stored procedure).
6. Realizar un Stored Procedure que me permita consultar la tabla empleado ya sea
por id_empleado, dni, nombre, appelido_pat o apellido_mat.
--1.1
create database Ventas1
use Ventas1
create table system_productos(
ID_producto INT PRIMARY KEY,
Nombre varchar(50) NOT NULL,
Descripcion varchar(50) NOT NULL,
Precio float(50) NOT NULL
)
alter table system_productos
ADD CONSTRAINT chk_precio check (precio>0);
select *from system_productos
INSERT INTO system_productos VALUES(02,'Pan','Integral','5')
CREATE TABLE SystemDestalle_Ventas(
ID_Detalle int primary key,
ID_Venta int NOT NULL ,
ID_Producto int NOT NULL,
Cantidad integer NOT NULL,
Precio Float(20) NOT NULL,
Monto_Parcial float(20) NOT NULL
)
ALTER TABLE SystemDestalle_Ventas
ADD FOREIGN KEY (ID_Venta ) references System_Ventas(ID_Venta )
ALTER TABLE SystemDestalle_Ventas
ADD FOREIGN KEY (ID_producto) references system_productos(ID_producto)
alter table SystemDestalle_Ventas
ADD CONSTRAINT chk_Cantidad check (Cantidad>0);
create table System_Ventas (
ID_Venta int PRIMARY KEY,
ID_Cliente int NOT NULL ,
ID_Empleado int NOT NULL,
fecha_Pedido date NOT NULL ,
Monto_Total float(50) NOT NULL,
Estado_Venta varchar(20)NOT NULL
)
alter table System_Ventas
ADD CONSTRAINT Monto_Total check (Monto_Total>0);
ALTER TABLE System_Ventas
ADD FOREIGN KEY (ID_Cliente) references System_Clientes(ID_Cliente)
ALTER TABLE System_Ventas
ADD FOREIGN KEY (ID_Empleado) references System_Empleados(ID_Empleado)
alter table System_Ventas
ADD constraint chk_Estado_Venta
--drop--
check(Estado_Venta in ('S','N'))
create table System_Clientes(
ID_Cliente int primary key,
DNI VARCHAR(20) unique NOT NULL,
Nombre varchar(50) NOT NULL,
Apellido_Pat VARCHAR(50) NOT NULL,
Apellido_Mat varchar(50)NOT NULL,
Edad INTEGER NOT NULL
)
ALTER TABLE System_Clientes
add CONSTRAINT check_edadClientes
check (edad>18 and edad<100);
select*from System_Clientes
insert into System_Clientes values (01,'778445','nathy','Garcia','Amao',19)
CREATE table System_Empleados(
ID_Empleado int primary key,
DNI VARCHAR(50) NOT NULL,
Nombre varchar(50)NOT NULL,
Apellido_Pat varchar(50)NOT NULL,
Apellido_Mat varchar(50)NOT NULL,
Edad integer NOT NULL
)
ALTER TABLE System_Empleados
add constraint DNI unique(DNI);
ALTER TABLE System_Empleados
add CONSTRAINT check_edadEmpleados
check (edad>=18);
select*from System_Empleados
insert into System_Empleados values (01,'7784545','Mey','Garciass','Ama',18)
--2.-- DML system_productos--
select *from system_productos
INSERT INTO system_productos VALUES(03,'keke','Natural','15'),
(04,'Tamal','Salado','20'),
(05,'Papel','Suave','25'),
(06,'Arroz','Integral','15'),
(07,'Fideos','Codito','35'),
(08,'Mantequilla','Dulcelia','15')
UPDATE system_productos
SET Nombre ='Leche'
where ID_producto=1;
Delete from system_productos
where ID_producto=8;
-- DML System_produros--
select *from system_productos
select*from System_Ventas
select *from SystemDestalle_Ventas
INSERT INTO SystemDestalle_Ventas VALUES
(02,01,05,100 ,50,45),
(03,01,06,100 ,50,45),
(04,01,01,100 ,50,45),
(05,01,02,100 ,50,45),
(06,01,03,100 ,50,45),
(07,01,06,100 ,50,45),
(08,01,02,100 ,50,45)
UPDATE SystemDestalle_Ventas
SET Precio ='100'
where ID_Detalle=1;
Delete from SystemDestalle_Ventas
where ID_Detalle=8;
-- DML System_clientes--
select *from System_Clientes
INSERT INTO System_Clientes
VALUES(02,'4567567','Magaly','Martinez','Ore',25),
(03,'4563366','Maria','Ore','Suares',25),
(04,'4777777','Julia','Galvan','luz',85),
(05,'4888888','Sol','Francisco','paz',35),
(06,'4555555','Luciana','caral','suares',65),
(07,'4999999','Flor','Quispe','Cruz',45),
(08,'4778555','Cielo','lopez','Molina',25)
UPDATE System_Clientes
SET DNI ='4588888'
where ID_Cliente=1;
Delete from System_Clientes
where ID_Cliente=8;
-- DML System_Empleados --
select *from System_Empleados
insert into System_Empleados values (02,'7334554','julio','Cabrera','paz',19),
(03,'7330004','Marcos','Ore','paze',19),
(04,'7111111','Mauricio','Molina','cal',22),
(05,'7222222','PASTOR','Mendoza','Ore',55),
(06,'7444444','Carlos','Cabrera','paz',19),
(07,'7555555','Matias','cruz','Martin',19),
(08,'7666666','Julian','luz','Lopes',25)
UPDATE System_Empleados
SET Nombre ='Pastor'
where ID_Empleado=5;
Delete from System_Empleados
where ID_Empleado=4;
select *from System_Ventas
insert into System_Ventas values
(05,05,05,'2023-10-11',300,'S'),
(06,06,06,'2023-10-11',300,'S'),
(07,07,07,'2023-10-11',300,'S')
UPDATE System_Ventas
SET Estado_Venta ='N'
where ID_Venta=5;
Delete from System_Ventas
where ID_Venta=8;
--3.1--
SELECT*FROM system_productos
SELECT*FROM SystemDestalle_Ventas
select p.nombre as nombreProducto, sum(dv.precio) as total_precio
from system_productos p
join SystemDestalle_Ventas dv on p.ID_producto=dv.ID_Producto
GROUP BY P.Nombre;
--3.2--
select*from System_Ventas
select top 25 percent sum(v.Monto_Total) as Porcentaje
from System_Ventas v
group by v.ID_Venta order by Porcentaje DESC;
--3.3
SELECT*FROM SystemDestalle_Ventas
SELECT DISTINCT ID_Producto
from SystemDestalle_Ventas;
--3.4
select*from system_productos
SELECT*FROM system_productos WHERE Precio>=15 AND Precio<=100;
--4.1
use master
create login SENATI1 with password ='123'
go
create login SENATI2 with password ='123'
go
use Ventas1
create user SENATI1 for login SENATI1 with default_schema=dbo
create user SENATI2 for login SENATI2 with default_schema=dbo
/*creacion de roles area_Venta*/
use Ventas1
create role area_ventas
grant alter on schema::dbo to area_ventas
grant insert,select, update on [dbo].[System_Ventas] to area_ventas
grant insert,select, update on [dbo].[SystemDestalle_Ventas] to area_ventas
--• Agregar el usuario SENATI1 al rol área_ventas
Alter role area_ventas add member SENATI1
--usuario senati2 PERMISOS
use Ventas1
grant insert,select, update, delete to SENATI2
--5 Insertar registros
--SET XACT_ABORT ON lo usamos ara asegurar que la transacción se cancele si hay
errores
--THROW lo usamos para lanzar el error para ser capturado por la aplicación
CREATE PROCEDURE RealizarVenta
AS
BEGIN
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
insert into System_Ventas values (10,02,01,'2023-02-04',550,'N');
insert into SystemDestalle_Ventas VALUES (09,01,02,420,50,21);
UPDATE system_productos SET Precio = Precio * 0.9;
print 'Los Precios se actualizaron en el Productos';
COMMIT;
print 'Transacción se realizo con éxito';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
print 'Error durante la transacción';
THROW;
END CATCH;
END;
EXEC RealizarVenta;
select*from System_Ventas
select*from SystemDestalle_Ventas
select*from system_productos
--6
ALTER PROCEDURE BuscarEmpleado
@Busqueda NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM System_Empleados
WHERE ID_Empleado = @Busqueda
OR DNI = @Busqueda
OR Nombre COLLATE Latin1_General_CI_AI LIKE '%' + @Busqueda + '%'
COLLATE Latin1_General_CI_AI
OR Apellido_Pat COLLATE Latin1_General_CI_AI LIKE '%' + @Busqueda + '%'
COLLATE Latin1_General_CI_AI
OR Apellido_Mat COLLATE Latin1_General_CI_AI LIKE '%' + @Busqueda + '%'
COLLATE Latin1_General_CI_AI;
END;
SELECT*FROM System_Empleados
EXEC BuscarEmpleado @Busqueda =5;
