---------------------------TP3------------------------------------------------------------------
--1--------------------------------------------------------------------------------------------
--Ejercicio 1 Cree una base de datos llamada EMPRESA que contenga las siguientes tablas:
CREATE DATABASE empresa2
USE empresa2
create table lista
( codigo_lista int primary key identity(1,1),
descripcion char(20) not null,
ganancia_porcentaje numeric(5,2) not null
);
create table oficina
( codigo_oficina int primary key identity(1,1),
codigo_director int,
descripcion char(50) not null
);
create table fabricante
( codigo_fabricante int primary key identity(1,1),
razon_social char(50) not null
);
create table cliente
( codigo_cliente int primary key identity(1,1),
codigo_lista int not null,
razon_social char(50) not null,
foreign key(codigo_lista)
references lista(codigo_lista)
on update cascade on delete no action
);
create table empleado
( codigo_empleado int primary key,
apellido char(50)not null,
nombre char(50) not null,
fecha_nacimiento datetime,
codigo_jefe int not null,
codigo_oficina int not null,
numero_documento char(8)not null,
codigo_documento int not null,
foreign key (codigo_oficina)
references oficina(codigo_oficina)
on update cascade on delete no action,
foreign key(codigo_documento)
references documento(codigo_documento)
on update cascade on delete no action
);
create table datos_contrato
( codigo_empleado int primary key,
fecha_contrato datetime not null,
cuota money not null,
ventas money,
foreign key(codigo_empleado)
references empleado(codigo_empleado)
on update cascade on delete no action
);
create table producto
( codigo_producto int primary key identity(1001,1),
descripcion char(50),
precio_costo money default 0,
codigo_fabricante int,
foreign key(codigo_fabricante)
references fabricante(codigo_fabricante)
on update cascade on delete no action
);
create table stock
( codigo_producto int primary key,
cantidad numeric not null default 0,
pto_reposicion numeric default 0,
foreign key (codigo_producto)
references producto(codigo_producto)
on update cascade on delete no action
);
create table precio_venta
( codigo_producto int ,
codigo_lista int ,
precio money not null default 0,
primary key(codigo_producto,codigo_lista),
foreign key(codigo_producto)
references producto(codigo_producto)
on update cascade on delete no action,
foreign key(codigo_lista)
references lista(codigo_lista)
on update cascade on delete no action
);
create table pedido
( codigo_pedido int primary key identity(1,1),
fecha_pedido datetime not null,
codigo_empleado int not null,
codigo_cliente int not null,
foreign key(codigo_empleado)
references empleado(codigo_empleado)
on update cascade on delete no action,
foreign key(codigo_cliente)
references cliente(codigo_cliente)
on update cascade on delete no action
);
create table detalle_pedido
( codigo_pedido int,
numero_linea int,
codigo_producto int not null,
primary key(codigo_pedido,numero_linea),
foreign key(codigo_pedido)
references pedido(codigo_pedido)
on update cascade on delete no action,
foreign key(codigo_producto )
references producto(codigo_producto)
on update cascade on delete no action
);
--2-------------------------------------------------------------------------------
--Ejercicio 2 Modificar diseño de tablas:
--1. En la tabla “detalle_pedido” agregue la columna “cantidad” con formato numérico.
ALTER TABLE detalle_pedido ADD cantidad NUMERIC
--2. En la tabla “cliente” agregue la columna “limite_credito” con formato moneda,
--valor predeterminado 0 .
ALTER TABLE cliente ADD limite_credito MONEY DEFAULT 0
SP_HELP documento
--3. En la tabla “lista”, check en la columna “ganancia_porcentaje” > 0.
ALTER TABLE lista ADD CONSTRAINT checkeo CHECK (ganancia_porcentaje >0)
--4. En la tabla “oficina” agregue la columna “ciudad” carácter de 50.
ALTER TABLE oficina ADD ciudad CHAR(50)
--5. Elimine la columna “ciudad” de la tabla “oficina”.
ALTER TABLE oficina DROP COLUMN ciudad
--6. En la tabla pedido agregue una “columna calculada” llamada “fecha_entrega” cuyo
--contenido se obtenga de sumar 30 a la columna “fecha_pedido”.
ALTER TABLE pedido ADD fecha_entrega AS(fecha_pedido + 30)
--3---------------------------------------------------------------------------------
--Ejercicio 3 Cargar los siguientes datos mediante instrucciones insert, (puede utilizar el script
--que se encuentra en el grupo).
insert into documento(descripcion)values('Documento Nacional de Identidad')
insert into documento(descripcion)values('Cédula de Identidad')
insert into documento(descripcion)values('Pasaporte')
insert into documento(descripcion)values('Libreta de Enrolamiento')
SELECT * FROM documento
insert into oficina(descripcion,codigo_director)values('Presidencia',101)
insert into oficina(descripcion,codigo_director)values('Gerencia',102)
insert into oficina(descripcion,codigo_director)values('Ventas Interior',104)
insert into oficina(descripcion)values('Ventas Exterior')
insert into fabricante(razon_social)values('ABC Comercial')
insert into fabricante(razon_social)values('General de Negocios S.A.')
insert into fabricante(razon_social)values('Tomasti Hnos.')
insert into fabricante(razon_social)values('Basic')
insert into fabricante(razon_social)values('Ferreteria Sudamericana')
insert into fabricante(razon_social)values('Pampas S.R.L.')
insert into lista(descripcion,ganancia_porcentaje)values('Mayorista',20)
insert into lista(descripcion,ganancia_porcentaje)values('Minorista',30)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(101,'Viguer','Antonio','02/05/56',12456897,0,1,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(102,'Jaumes','Alvaro','03/03/55',12654897,101,2,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(103,'Rovira','Juan','01/06/60',15789546,102,3,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(104,'Gonzales','Analia','03/04/58',13456789,102,3,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(105,'Pantalla','Marcela','02/12/63',14875987,102,3,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(106,'Bustamante','Jorge','05/05/63',4756984,102,3,2)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(107,'Sunta','Maria','02/06/70',16789547,101,2,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(108,'Victor','Juan','01/09/66',13854789,107,4,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(109,'Alvarez ','Adriana','03/09/56',12465879,107,4,1)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(110,'Clavel','Maria','02/07/64',99875987,107,4,3)
SELECT * FROM empleado
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(101,'02/01/98',57000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(102,'01/03/98',57000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(103,'01/06/99',57000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(104,'02/04/97',45000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(105,'03/05/98',45000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(106,'05/06/99',45000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(107,'05/06/97',45000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(108,'03/03/95',120000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(109,'09/03/87',120000)
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(110,'03/06/88',90000)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Arandela',0.50,1)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Bisagra',1,2)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Extractor',135,3)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Junta',21,2)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Cubo',350,4)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Perno',21,5)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Red',821,3)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Escofina',35,2)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Bomba V6',1012,1)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Rele',35,3)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Bagueta A3',48,5)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Ventilador',289,1)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Linterna 2X',28,2)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Linterna 3X',38,2)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1001,1000,1000)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1002,1500,1000)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1003,450,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1004,830,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1005,180,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1006,1000,1000)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1007,500,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1008,300,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1009,450,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1010,320,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1011,750,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1012,450,1000)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1013,150,500)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1014,300,500)
insert into precio_venta(codigo_producto,codigo_lista)values(1001,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1001,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1002,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1002,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1003,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1003,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1004,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1004,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1005,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1005,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1006,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1006,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1007,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1008,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1009,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1010,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1010,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1011,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1011,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1012,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1012,2)
insert into precio_venta(codigo_producto,codigo_lista)values(1013,1)
insert into precio_venta(codigo_producto,codigo_lista)values(1014,1)
insert into cliente(codigo_lista,razon_social)values(1,'Luis garcia Antón')
insert into cliente(codigo_lista,razon_social)values(1,'Jaime LLorens')
insert into cliente(codigo_lista,razon_social)values(1,'El Triángulo')
insert into cliente(codigo_lista,razon_social)values(1,'Bujes y Pernos')
insert into cliente(codigo_lista,razon_social)values(1,'Carlos Tena')
insert into cliente(codigo_lista,razon_social)values(2,'La Manivela')
insert into cliente(codigo_lista,razon_social)values(2,'Juan Bolto')
insert into cliente(codigo_lista,razon_social)values(2,'Alvarez Hnos.')
insert into cliente(codigo_lista,razon_social)values(1,'ORSAN S.A.')
insert into cliente(codigo_lista,razon_social)values(2,'Cristóbal García')
insert into cliente(codigo_lista,razon_social)values(1,'La Fontella')
insert into cliente(codigo_lista,razon_social)values(1,'Sunshine Ready')
insert into cliente(codigo_lista,razon_social)values(2,'San Antonio')
insert into cliente(codigo_lista,razon_social)values(2,'Castelnuovo S.A.')
insert into cliente(codigo_lista,razon_social)values(1,'BlueMoon')
insert into cliente(codigo_lista,razon_social)values(1,'Las Cabañas')
insert into cliente(codigo_lista,razon_social)values(2,'Electrosur')
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/03/02',101,1)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/03/02',101,2)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('04/03/02',102,3)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',104,4)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/03/02',104,1)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',104,5)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',105,4)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/03/02',108,7)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',108,6)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('04/03/02',104,6)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/03/02',101,1)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/03/02',110,4)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/04/02',110,12)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/04/02',102,12)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/04/02',104,15)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/04/02',108,15)
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('04/04/02',101,12)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(1,1,1003,10)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(1,2,1005,12)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(1,3,1007,45)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(2,1,1001,78)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(3,1,1008,32)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(3,2,1009,5)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(3,3,1003,46)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(4,1,1004,12)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(4,2,1005,45)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(5,1,1002,23)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(6,1,1007,30)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(6,2,1008,14)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(7,1,1006,7)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(7,2,1005,65)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(7,3,1004,120)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(8,1,1004,32)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(8,2,1006,74)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(9,1,1009,115)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(9,2,1003,89)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(10,1,1004,10)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(10,2,1001,12)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(11,1,1003,12)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(11,2,1008,6)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(11,3,1009,38)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,1,1005,65)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,2,1001,56)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,3,1009,45)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,4,1004,18)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,5,1003,14)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(13,1,1011,21)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(13,2,1012,32)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(14,1,1013,100)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(14,2,1014,6)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(14,3,1010,18)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(15,1,1009,9)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(15,2,1012,32)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(16,1,1014,5)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(17,1,1011,10)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(17,2,1001,12)
SELECT * FROM pedido
--4-----------------------------------------------------------------------------------------------
--Ejercicio 4 Resolver las siguientes consultas utilizando sentencias SQL.
--Consultas simples (una sola tabla).---------
--1. Obtener una lista con los nombres de las distintas oficinas de la empresa.
SELECT descripcion AS nom_oficinas FROM oficina
--2. Obtener una lista de todos los productos indicando descripción del producto, su precio
--de costo y su precio de costo IVA incluido (tomar el IVA como 21%).
SELECT descripcion, precio_costo, (precio_costo + precio_costo * 21/100) AS precio_iva
FROM producto
--3. Obtener una lista indicando para cada empleado apellido, nombre, fecha de
--cumpleaños y edad.
SELECT apellido, nombre, fecha_nacimiento, (YEAR(GETDATE()) - YEAR(fecha_nacimiento))
AS Edad
FROM empleado
--4. Listar todos los empleados que tiene un jefe asignado.
SELECT * FROM empleado --veo q codigo es el jefe
SELECT nombre, apellido, codigo_jefe
FROM empleado
WHERE codigo_jefe != 0
--5. Listar los empleados de nombre “María” ordenando por apellido.
SELECT nombre
FROM empleado
WHERE nombre='Maria'
--6. Listar los clientes cuya razón social comience con “L” ordenando por código de
--cliente
SELECT razon_social
FROM cliente
WHERE razon_social LIKE 'L%'
ORDER BY codigo_cliente
--7. Listar toda la información de los pedidos de marzo ordenando por fecha de pedido.
SELECT * FROM pedido
WHERE MONTH(fecha_pedido)=3
ORDER BY fecha_pedido
--8. Listar las oficinas que no tienen asignado director.
SELECT * FROM oficina
WHERE codigo_director IS NULL
--9. Listar los cuatro productos de menor precio de costo.
SELECT TOP 4 * FROM producto
ORDER BY precio_costo
--10. Listar los códigos de empleados de los tres que tengan la mayor cuota.
SELECT TOP 3 codigo_empleado
FROM datos_contrato
ORDER BY cuota DESC
--Consultas multitabla.----------
--1. De cada producto listar descripción, razón social del fabricante y stock ordenando por
--razón social y descripción.
SELECT p.descripcion, f.razon_social AS Fabricante, s.cantidad
FROM producto p, fabricante f, stock s
WHERE p.codigo_fabricante = f.codigo_fabricante AND
p.codigo_producto = s.codigo_producto
ORDER BY f.razon_social , p.descripcion
--2. De cada pedido listar código de pedido, fecha de pedido, apellido del empleado y
--razón social del cliente.
SELECT p.codigo_pedido, p.fecha_pedido, e.apellido AS empleado, c.razon_social AS Cliente
FROM pedido p, empleado e, cliente c
WHERE p.codigo_empleado = e.codigo_empleado AND
p.codigo_cliente = c.codigo_cliente
--3. Listar por cada empleado apellido, cuota asignada, oficina a la que pertenece y
--apellido de su jefe ordenando en forma descendente por cuota.
SELECT e.apellido, dc.cuota, o.descripcion AS Oficina, e.codigo_jefe
FROM empleado e, datos_contrato dc, oficina o
WHERE e.codigo_empleado = dc.codigo_empleado AND
e.codigo_oficina = o.codigo_oficina
ORDER BY dc.cuota DESC --falta ape del jefe
--4. Listar sin repetir la razón social de todos aquellos clientes que hicieron pedidos en
--abril.
SELECT DISTINCT c.razon_social, p.fecha_pedido
FROM cliente c, pedido p
WHERE c.codigo_cliente = p.codigo_cliente AND
MONTH(fecha_pedido) = 3
--5. Listar sin repetir los productos que fueron pedidos en marzo.
SELECT DISTINCT p.descripcion, pe.fecha_pedido
FROM producto p, pedido pe, detalle_pedido dp
WHERE p.codigo_producto = dp.codigo_producto AND
pe.codigo_pedido = dp.codigo_pedido AND
MONTH(pe.fecha_pedido) =3
--6. Listar aquellos empleados que están contratados por más de diez años ordenando por
--cantidad de años en forma descendente.
SELECT e.nombre, e.apellido, dc.fecha_contrato, (YEAR(getdate()) - YEAR(dc.fecha_contrato))
AS Antiguedad
FROM empleado e, datos_contrato dc
WHERE e.codigo_empleado = dc.codigo_empleado AND
(YEAR(getdate()) - YEAR(dc.fecha_contrato)) > 10
ORDER BY Antiguedad DESC
--7. Obtener una lista de los clientes mayoristas ordenada por razón social.
SELECT * FROM lista --primero verifico q codigo es mayorista
SELECT c.razon_social, l.descripcion
FROM cliente c, lista l
WHERE c.codigo_lista = l.codigo_lista AND
l.descripcion = 'mayorista'
ORDER BY c.razon_social
--8. Obtener una lista sin repetir que indique que productos compró cada cliente, ordenada
--por razón social y descripción.
SELECT DISTINCT c.razon_social, p.descripcion
FROM cliente c, producto p, pedido pe, detalle_pedido dp
WHERE c.codigo_cliente = pe.codigo_cliente AND
p.codigo_producto = dp.codigo_producto AND
pe.codigo_pedido = dp.codigo_pedido
ORDER BY c.razon_social, p.descripcion
--9. Obtener una lista con la descripción de aquellos productos cuyo stock está por debajo
--del punto de reposición indicando cantidad a comprar y razón social del fabricante
--ordenada por razón social y descripción.
ALTER TABLE producto ADD pto_reposicion INT DEFAULT 500
SELECT * FROM producto
SELECT p.descripcion, s.cantidad, f.razon_social AS Fabricante
FROM producto p, stock s, fabricante f
WHERE p.codigo_fabricante = f.codigo_fabricante AND
p.codigo_producto = s.codigo_producto
ORDER BY f.razon_social, p.descripcion
--10. Listar aquellos empleados cuya cuota es menor a 50000 o mayor a 100000.
SELECT * FROM datos_contrato
SELECT e.apellido, dc.cuota
FROM empleado e, datos_contrato dc
WHERE e.codigo_empleado = dc.codigo_empleado AND
dc.cuota < 50000 OR dc.cuota > 100000
--Consultas de actualización de datos--------
--1. Crear una tabla llamada “aux_empleado” que contenga las filas de la tabla empleados.
SELECT INTO aux_empleado FROM empleado
SELECT FROM empleado
SELECT * FROM aux_empleado
--2. Calcular los precios de venta mayorista y minorista de todos los productos.
SELECT FROM precio_venta
SELECT p.descripcion, p.precio_costo, l.descripcion, (p.precio_costo + p.precio_costo l.ganancia_porcentaje/100)
AS Precio_venta
FROM producto p, lista l, precio_venta pv
WHERE p.codigo_producto = pv.codigo_producto AND
pv.codigo_lista = l.codigo_lista
--2. ACTUALIZAR los precios de venta mayorista y minorista de todos los productos.
UPDATE precio_venta SET
precio = p.precio_costo + p.precio_costo * l.ganancia_porcentaje/100
FROM producto p, lista l, precio_venta pv
WHERE p.codigo_producto = pv.codigo_producto AND
pv.codigo_lista = l.codigo_lista
--3. Eliminar los fabricantes que no tengan productos en la tabla “productos”.
--SELECT codigo_fabricante, razon_social Primero corroboro
DELETE
FROM fabricante
WHERE codigo_fabricante NOT IN
(SELECT codigo_fabricante FROM producto)
SELECT * FROM fabricante --corroboro lo q borre
--4. Eliminar a los clientes que no haya realizado ningún pedido.
--SELECT codigo_cliente, razon_social Primero corroboro
DELETE
FROM cliente
WHERE codigo_cliente NOT IN
(SELECT codigo_cliente FROM pedido)
SELECT * FROM cliente --coorroboro lo q borre
--5. Aumentar en un 5% la cuota de aquellos empleados con contrato anterior al 01/01/1999.
SELECT FROM datos_contrato
--SELECT codigo_empleado Corroboro
UPDATE datos_contrato SET
cuota=cuota + cuota 5/100
WHERE (YEAR(fecha_contrato) < 1999)
--6. Cambiar el código de empleado del empleado 110 a 112 verificando la actualización en cascada.
SELECT * FROM empleado
UPDATE empleado SET
codigo_empleado=112
WHERE codigo_empleado = 110
--7. Crear una tabla llamada “aux_producto” que contenga todas las filas de la tabla producto.
SELECT INTO aux_producto FROM producto
SELECT FROM aux_producto --corroboro
--8. Actualizar el precio de costo de cada producto incrementándolo en un 10%.
SELECT * FROM producto
UPDATE producto SET
precio_costo=precio_costo + precio_costo * 10/100
--9. Recuperar los precios originales de los productos a partir de la tabla “aux_producto”.
SELECT FROM producto
SELECT FROM aux_producto
INSERT INTO producto(precio_costo) --NO FUNCIONA!!!!!
SELECT precio_costo FROM aux_producto
--10. Actualizar el punto de reposición de todos los productos del fabricante “Tomasti HNOS.” a 600.
SELECT p.descripcion, p.pto_reposicion, f.razon_social --CORROBORO
FROM producto p, fabricante f
WHERE p.codigo_fabricante = f.codigo_fabricante AND
f.razon_social = 'Tomasti Hnos.'
UPDATE producto SET
pto_reposicion = 600
FROM producto p, fabricante f
WHERE p.codigo_fabricante = f.codigo_fabricante AND
f.razon_social = 'Tomasti Hnos.'
--Consultas de resumen ------------------
--1. Hallar el precio medio de los productos del fabricante “ABC Comercial”.
SELECT f.razon_social, AVG(p.precio_costo) AS Promedio
FROM fabricante f, producto p
WHERE f.codigo_fabricante = p.codigo_fabricante AND
f.razon_social ='ABC Comercial'
GROUP BY f.razon_social
--2. Indicar la cantidad de productos que provee cada fabricante.
SELECT f.razon_social, COUNT(p.codigo_producto) AS Tot_Productos
FROM fabricante f, producto p
WHERE f.codigo_fabricante = p.codigo_fabricante
GROUP BY f.razon_social
--3. Informar por cada pedido: código de pedido, fecha de pedido, razón social del cliente e importe total.
SELECT pe.codigo_pedido, pe.fecha_pedido, c.razon_social, SUM(dp.cantidad * pv.precio) AS Total
FROM pedido pe, cliente c, detalle_pedido dp, precio_venta pv
WHERE pe.codigo_cliente = c.codigo_cliente AND
pe.codigo_pedido = dp.codigo_pedido AND
dp.codigo_producto = pv.codigo_producto
GROUP BY pe.codigo_pedido, pe.fecha_pedido, c.razon_social
--4. Actualizar la columna ventas en la tabla “datos_contrato” con el importe correspondiente al total
--de pedidos de cada empleado
SELECT * FROM datos_contrato
SELECT p.codigo_empleado, SUM(dp.cantidad pv.precio) AS Importe -- NO FUNC EL UPDATE
--UPDATE datos_contrato SET
--ventas=SUM(dp.cantidad pv.precio)
FROM pedido p, detalle_pedido dp, precio_venta pv, datos_contrato dt
WHERE p.codigo_pedido= dp.codigo_pedido AND
dp.codigo_producto = pv.codigo_producto AND
p.codigo_empleado = dt.codigo_empleado
GROUP BY p.codigo_empleado
--5. Informar cuántos empleados tiene cada oficina.
SELECT o.descripcion, COUNT(e.codigo_empleado) AS Cantidad_Empleados
FROM oficina o, empleado e
WHERE o.codigo_oficina = e.codigo_oficina
GROUP BY o.descripcion
--6. Hallar en que fecha se realizó el primer pedido.
SELECT TOP 1 * FROM pedido
ORDER BY fecha_pedido
--7. Informar cuales son las medias de las columnas “cuota” y “ventas”.
SELECT AVG(cuota) AS Media_cuota, AVG(ventas) AS Media_ventas -- NO LO entiendo
FROM datos_contrato
--8. Informar la cantidad de pedidos de cada cliente
SELECT c.razon_social, COUNT(p.codigo_pedido) AS Cant_Pedidos
FROM cliente c, pedido p
WHERE c.codigo_cliente = p.codigo_cliente
GROUP BY c.razon_social
--9. Informar la cantidad pedida de cada producto en el mes de marzo.
SELECT p.descripcion, COUNT(dp.codigo_producto) AS Cant_Pedidos, pe.fecha_pedido
FROM producto p, detalle_pedido dp, pedido pe
WHERE p.codigo_producto = dp.codigo_producto AND
dp.codigo_pedido = pe.fecha_pedido AND
MONTH(pe.fecha_pedido) = 3
GROUP BY p.descripcion, pe.fecha_pedido
SELECT * FROM pedido --NO FUNCIONA
--10. Informar la cantidad de productos con stock bajo el punto de reposición.
SELECT * FROM stock
SELECT p.descripcion, s.cantidad, p.pto_reposicion
FROM producto p, stock s
WHERE p.codigo_producto = s.codigo_producto AND
s.cantidad < p.pto_reposicion
--Subconsultas --------------------------------
--1. Listar razón social de los clientes atendidos por el empleado “González Analía”.
SELECT FROM detalle_pedido
SELECT FROM pedido
SELECT cl.razon_social, em.apellido, em.nombre
FROM cliente cl, empleado em, pedido pe
WHERE cl.codigo_cliente = pe.codigo_cliente AND
em.codigo_empleado = pe.codigo_empleado AND
em.apellido = 'Gonzales' AND em.nombre = 'Analia'
--2. Listar los empleados que no están en la oficina “Ventas Interior”.
SELECT * FROM empleado
WHERE codigo_oficina NOT IN
(SELECT codigo_oficina FROM oficina WHERE descripcion = 'Ventas Interior')
--3. Listar las oficinas en las que haya por lo menos un empleado cuyos pedidos
--representen un 10% más que su cuota
--4. Listar los productos que por los cuáles no se hayan pedido 200 o más unidades durante el mes de marzo.
SELECT pr.descripcion, pe.fecha_pedido, SUM(dp.cantidad) AS Total_Unidades
FROM producto pr, pedido pe, detalle_pedido dp
WHERE pr.codigo_producto = dp.codigo_producto AND
pe.codigo_pedido = dp.codigo_pedido AND
MONTH(pe.fecha_pedido) = 3
GROUP BY pr.descripcion, pe.fecha_pedido
HAVING SUM(dp.cantidad) < 100
--5. Listar sin repetir la razón social de aquellos clientes que hicieron por lo menos un pedido cuyo
--importe total sea mayor que $850.
SELECT razon_social FROM cliente --NO SALE
WHERE codigo_cliente IN (SELECT pe.codigo_cliente FROM pedido pe, precio_venta pv, detalle_pedido dp
WHERE dp.codigo_pedido = pe.codigo_pedido AND
dp.codigo_producto = pv.codigo_producto AND
SUM(pv.precio * dp.cantidad) > 850
GROUP BY dp.codigo_producto)
--6. Listar la razón social de aquellos fabricantes cuyos productos no fueron pedidos en el mes de abril.
SELECT razon_social FROM fabricante
WHERE codigo_fabricante NOT IN
(SELECT p.codigo_fabricante FROM producto p, pedido pe, detalle_pedido dp
WHERE p.codigo_producto = dp.codigo_producto AND
pe.codigo_pedido = dp.codigo_pedido AND
MONTH(pe.fecha_pedido) = MONTH(4))
--7. Listar la razón social de aquellos fabricantes cuyos productos fueron pedidos en el mes de abril
SELECT razon_social FROM fabricante
WHERE codigo_fabricante IN
(SELECT p.codigo_fabricante FROM producto p, pedido pe, detalle_pedido dp
WHERE p.codigo_producto = dp.codigo_producto AND
pe.codigo_pedido = dp.codigo_pedido AND
MONTH(pe.fecha_pedido) = MONTH(1))
--8. Listar sin repetir los productos que figuren con por lo menos una cantidad pedida
--mayor a 70 en el mes de marzo.
SELECT p.descripcion, dp.cantidad, pe.fecha_pedido
FROM producto p, detalle_pedido dp, pedido pe
WHERE p.codigo_producto = dp.codigo_producto AND
dp.codigo_pedido = pe.codigo_pedido AND
dp.cantidad > 70 AND
MONTH(pe.fecha_pedido) = 3
--9. Listar los productos de los cuales no se tiene precio minorista.
SELECT FROM lista
SELECT FROM producto
SELECT * FROM precio_venta
SELECT pr.descripcion, li.descripcion, pv.precio
FROM producto pr, lista li, precio_venta pv
WHERE pr.codigo_producto = pv.codigo_producto AND
li.codigo_lista = pv.codigo_lista AND
pv.codigo_lista = 2 AND pv.precio IS NULL
--10. Listar los clientes de Adriana Alvarez que no hayan
--hecho un pedido con importe total superior a $1000.
SELECT c.razon_social FROM cliente c, empleado e, pedido pe -- sin terminar
WHERE pe.codigo_empleado = e.codigo_empleado AND
pe.codigo_cliente = c.codigo_cliente AND
e.apellido = 'Viguer'
----------------------------TP4----------------------------------------------------------
--1. Crear una Vista que muestra de cada fabricante sus productos y la cantidad en stock.
CREATE VIEW fabri_prod AS
SELECT f.razon_social, p.descripcion, s.cantidad
FROM fabricante f, producto p, stock s
WHERE f.codigo_fabricante = p.codigo_fabricante AND
p.codigo_producto = s.codigo_producto
--la uso
SELECT * FROM fabri_prod
ORDER BY razon_social
--2. Listar mediante el uso de cursores, para cada oficina nombre de la oficina, apellido y
--nombre del director y apellido y nombre de cada uno de los empleados asignados a la oficina.
--Me falto los nombre de empleados
--2° declaro cursor
DECLARE c_listar CURSOR FOR
SELECT o.descripcion, e.apellido, e.nombre FROM oficina o, empleado e
WHERE o.codigo_oficina = e.codigo_oficina AND
e.codigo_empleado IN (SELECT o.codigo_director FROM oficina)
--3° Abro el puntero
OPEN c_listar
--4° Lo recorro
FETCH c_listar INTO @nom_oficina, @ape_director, @nom_director
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @nom_oficina+'DIRECTOR = '+TRIM(@ape_director)+' '+@nom_director
FETCH c_listar INTO @nom_oficina, @ape_director, @nom_director
END
--5° Cierro el puntero
CLOSE c_listar
--6° Libero memoria
DEALLOCATE c_listar
--3. Listar mediante el uso de cursores, de cada pedido los datos del encabezado y su detalle valorizado.
SELECT FROM pedido
SELECT FROM detalle_pedido
DECLARE c_listar2 CURSOR FOR
SELECT p.descripcion, dt.cantidad, (dt.cantidad * pv.precio) AS valor_total
FROM producto p, detalle_pedido dt, precio_venta pv
WHERE dt.codigo_producto = p.codigo_producto AND
p.codigo_producto = pv.codigo_producto
OPEN c_listar2
FETCH c_listar2 INTO @nom_producto, @nom_cant, @nom_total
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT TRIM(@nom_producto)+'='+ TRIM(@nom_cant)+' '+'$'+TRIM(@nom_total)+'.'
FETCH c_listar2 INTO @nom_producto, @nom_cant, @nom_total
END
CLOSE c_listar2
DEALLOCATE c_listar2
--4. Modificar la tabla cliente incorporando el campo "clave" y utilice cursores
--para completarlo con el nombre de usuario.
SELECT * FROM cliente
ALTER TABLE cliente DROP COLUMN clave
ALTER TABLE cliente ADD clave varchar(20) DEFAULT 0
DECLARE @nom_usuario varchar(20)
DECLARE c_listar3 CURSOR FOR
SELECT razon_social FROM cliente FOR UPDATE
OPEN c_listar3
FETCH c_listar3 INTO @nom_usuario
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATE cliente SET
clave='COD'+@nom_usuario
PRINT TRIM(@nom_usuario)+'actualizado'
FETCH c_listar3 INTO @nom_usuario
END
CLOSE c_listar3
DEALLOCATE c_listar3
--5. Crear un procedimiento almacenado que actualice los precios de venta de todos aquellos
--productos cuyo precio de venta es menor al precio de venta promedio.
--Ingresar por parámetro la lista a actualizar (mayorista o minorista) y el porcentaje de incremento.
CREATE PROCEDURE act_precio --HECHO UNA PARTE
(@porcen int)
AS
BEGIN
UPDATE precio_venta SET
precio=p.precio_costo + p.precio_costo* @porcen / 100
FROM producto p, precio_venta pv
WHERE p.codigo_producto = pv.codigo_producto
END
--lo ejecuto
EXEC act_precio @porcen=10
--lo elimino
DROP PROCEDURE act_precio
SELECT * FROM precio_venta
--6. Crear un procedimiento almacenado que devuelva los datos de todos los productos
--o de aquel producto cuya descripción se ingresa por parámetro.
CREATE PROCEDURE d_art
(@descrp varchar(20))
AS
BEGIN
IF(@descrp IS NULL) BEGIN
SELECT * FROM producto END
IF(@descrp IS NOT NULL) BEGIN
SELECT * FROM producto
WHERE descripcion = @descrp END
END
--lo ejecuto
EXEC d_art @descrp = NULL
--lo elimino
DROP PROCEDURE d_art
--7. Crear un procedimiento almacenado que permita ingresar nuevos productos
--en la tabla productos ingresando los datos del producto por parámetros.
CREATE PROCEDURE i_producto
(@des varchar(50), @pre numeric, @cod_fabri int, @repo int)
AS
BEGIN
INSERT INTO producto(descripcion, precio_costo, codigo_fabricante, pto_reposicion)
VALUES (@des, @pre, @cod_fabri, @repo)
END
--8. Crear un procedimiento almacenado que informe de cada fabricante o del fabricante cuya razón
--social se ingresa como parámetro, los productos cuya cantidad en stock es menor al punto de reposición,
--indicando cantidad a reponer, costo por producto y costo total por fabricante.
CREATE PROCEDURE p_listas (@val varchar(50)) AS
BEGIN
IF @val IS NULL
BEGIN
SELECT fa.razon_social AS Fabricante, pr.descripcion AS Producto, st.cantidad, st.pto_reposicion,
(st.pto_reposicion - st.cantidad) AS Cant_Reponer, pr.precio_costo AS Cos_unidad,
(pr.precio_costo (st.pto_reposicion - st.cantidad)) AS Cos_tot
FROM fabricante fa, producto pr, stock st
WHERE pr.codigo_fabricante = fa.codigo_fabricante AND
pr.codigo_producto = st.codigo_producto AND
st.cantidad < st.pto_reposicion
END
IF @val IS NOT NULL
BEGIN
SELECT fa.razon_social AS Fabricante, pr.descripcion AS Producto, st.cantidad, st.pto_reposicion,
(st.pto_reposicion - st.cantidad) AS Cant_Reponer, pr.precio_costo AS Cos_unidad,
(pr.precio_costo (st.pto_reposicion - st.cantidad)) AS Cos_tot
FROM fabricante fa, producto pr, stock st
WHERE pr.codigo_fabricante = fa.codigo_fabricante AND
pr.codigo_producto = st.codigo_producto AND
st.cantidad < st.pto_reposicion AND fa.razon_social = @val
END
END
EXEC p_listas @val = 'ABC COMERCIAL'
--9. Crear un procedimiento almacenado que ingresando como parámetros: nombre del producto,
--cantidad comprada, código de fabricante y precio de costo, realice las siguientes
--acciones: En el caso en que el producto sea nuevo darlo de alta en la tabla “producto”,
--en cualquier caso, actualice el stock y el punto de reposición del producto.
--10. Crear un desencadenador (trigger) en la tabla “producto” que, ante el ingreso de un nuevo producto,
--incorpore en la tabla “precio_venta” los precios de venta Página 21of 24mayorista y minorista
--considerando los porcentajes de ganancia indicados en la tabla “lista”
--11. Crear un desencadenador (trigger) que grabe en la tabla auditoría_producto (la que deberá crear),
--los valores anteriores cuando se actualiza un producto.
SELECT FROM producto
CREATE TABLE auditoria_producto
(codigo_producto int,
descripcion varchar(50),
precio_costo money,
codigo_fabricante int,
pto_reposicion int
)
SELECT FROM auditoria_producto
CREATE TRIGGER grabe_act
ON producto FOR UPDATE AS
INSERT INTO auditoria_producto(codigo_producto, descripcion, precio_costo,codigo_fabricante, pto_reposicion)
SELECT del.codigo_producto, del.descripcion, del.precio_costo, del.codigo_fabricante,
del.pto_reposicion
FROM deleted del
UPDATE producto SET
precio_costo = 5
WHERE codigo_producto = 1001
--12. Crear un desencadenador (trigger) en la tabla "detalle_pedido" para la acción de inserción que
--controle si la cantidad que se intenta pedir sea menor o igual al stock del producto.
--En el caso en que la cantidad pedida sea menor o igual al stock, debe permitir el insert y actualizar
--el stock del producto. En el caso en que el stock no sea suficiente, debe informar que no es posible
--la operación y abortarla.
---------------------------TP3------------------------------------------------------------------ --1-------------------------------------------------------------------------------------------- --Ejercicio 1 Cree una base de datos llamada EMPRESA que contenga las siguientes tablas: CREATE DATABASE empresa2 USE empresa2
create table lista
( codigo_lista int primary key identity(1,1), descripcion char(20) not null, ganancia_porcentaje numeric(5,2) not null
);
create table oficina ( codigo_oficina int primary key identity(1,1), codigo_director int, descripcion char(50) not null
);
create table documento ( codigo_documento int primary key identity(1,1), descripcion char(50) );
create table fabricante ( codigo_fabricante int primary key identity(1,1), razon_social char(50) not null );
create table cliente ( codigo_cliente int primary key identity(1,1), codigo_lista int not null, razon_social char(50) not null, foreign key(codigo_lista) references lista(codigo_lista) on update cascade on delete no action );
create table empleado
( codigo_empleado int primary key, apellido char(50)not null, nombre char(50) not null, fecha_nacimiento datetime, codigo_jefe int not null, codigo_oficina int not null, numero_documento char(8)not null, codigo_documento int not null, foreign key (codigo_oficina) references oficina(codigo_oficina) on update cascade on delete no action, foreign key(codigo_documento) references documento(codigo_documento) on update cascade on delete no action
);
create table datos_contrato
( codigo_empleado int primary key, fecha_contrato datetime not null, cuota money not null, ventas money, foreign key(codigo_empleado) references empleado(codigo_empleado) on update cascade on delete no action );
create table producto
( codigo_producto int primary key identity(1001,1), descripcion char(50), precio_costo money default 0, codigo_fabricante int, foreign key(codigo_fabricante) references fabricante(codigo_fabricante) on update cascade on delete no action );
create table stock
( codigo_producto int primary key, cantidad numeric not null default 0, pto_reposicion numeric default 0, foreign key (codigo_producto) references producto(codigo_producto) on update cascade on delete no action
);
create table precio_venta ( codigo_producto int , codigo_lista int , precio money not null default 0, primary key(codigo_producto,codigo_lista), foreign key(codigo_producto) references producto(codigo_producto) on update cascade on delete no action, foreign key(codigo_lista) references lista(codigo_lista) on update cascade on delete no action );
create table pedido ( codigo_pedido int primary key identity(1,1), fecha_pedido datetime not null, codigo_empleado int not null, codigo_cliente int not null, foreign key(codigo_empleado) references empleado(codigo_empleado) on update cascade on delete no action, foreign key(codigo_cliente) references cliente(codigo_cliente) on update cascade on delete no action
);
create table detalle_pedido
( codigo_pedido int, numero_linea int, codigo_producto int not null, primary key(codigo_pedido,numero_linea), foreign key(codigo_pedido) references pedido(codigo_pedido) on update cascade on delete no action, foreign key(codigo_producto ) references producto(codigo_producto) on update cascade on delete no action );
--2------------------------------------------------------------------------------- --Ejercicio 2 Modificar diseño de tablas: --1. En la tabla “detalle_pedido” agregue la columna “cantidad” con formato numérico. ALTER TABLE detalle_pedido ADD cantidad NUMERIC
--2. En la tabla “cliente” agregue la columna “limite_credito” con formato moneda, --valor predeterminado 0 . ALTER TABLE cliente ADD limite_credito MONEY DEFAULT 0 SP_HELP documento
--3. En la tabla “lista”, check en la columna “ganancia_porcentaje” > 0. ALTER TABLE lista ADD CONSTRAINT checkeo CHECK (ganancia_porcentaje >0)
--4. En la tabla “oficina” agregue la columna “ciudad” carácter de 50. ALTER TABLE oficina ADD ciudad CHAR(50)
--5. Elimine la columna “ciudad” de la tabla “oficina”. ALTER TABLE oficina DROP COLUMN ciudad
--6. En la tabla pedido agregue una “columna calculada” llamada “fecha_entrega” cuyo --contenido se obtenga de sumar 30 a la columna “fecha_pedido”. ALTER TABLE pedido ADD fecha_entrega AS(fecha_pedido + 30)
--3--------------------------------------------------------------------------------- --Ejercicio 3 Cargar los siguientes datos mediante instrucciones insert, (puede utilizar el script --que se encuentra en el grupo). insert into documento(descripcion)values('Documento Nacional de Identidad') insert into documento(descripcion)values('Cédula de Identidad') insert into documento(descripcion)values('Pasaporte') insert into documento(descripcion)values('Libreta de Enrolamiento') SELECT * FROM documento
insert into oficina(descripcion,codigo_director)values('Presidencia',101) insert into oficina(descripcion,codigo_director)values('Gerencia',102) insert into oficina(descripcion,codigo_director)values('Ventas Interior',104) insert into oficina(descripcion)values('Ventas Exterior')
insert into fabricante(razon_social)values('ABC Comercial') insert into fabricante(razon_social)values('General de Negocios S.A.') insert into fabricante(razon_social)values('Tomasti Hnos.') insert into fabricante(razon_social)values('Basic') insert into fabricante(razon_social)values('Ferreteria Sudamericana') insert into fabricante(razon_social)values('Pampas S.R.L.')
insert into lista(descripcion,ganancia_porcentaje)values('Mayorista',20) insert into lista(descripcion,ganancia_porcentaje)values('Minorista',30)
insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(101,'Viguer','Antonio','02/05/56',12456897,0,1,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(102,'Jaumes','Alvaro','03/03/55',12654897,101,2,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(103,'Rovira','Juan','01/06/60',15789546,102,3,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(104,'Gonzales','Analia','03/04/58',13456789,102,3,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(105,'Pantalla','Marcela','02/12/63',14875987,102,3,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(106,'Bustamante','Jorge','05/05/63',4756984,102,3,2) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(107,'Sunta','Maria','02/06/70',16789547,101,2,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(108,'Victor','Juan','01/09/66',13854789,107,4,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(109,'Alvarez ','Adriana','03/09/56',12465879,107,4,1) insert into empleado(codigo_empleado,apellido,Nombre,fecha_nacimiento,numero_documento,codigo_jefe,codigo_oficina,codigo_documento)values(110,'Clavel','Maria','02/07/64',99875987,107,4,3) SELECT * FROM empleado
insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(101,'02/01/98',57000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(102,'01/03/98',57000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(103,'01/06/99',57000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(104,'02/04/97',45000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(105,'03/05/98',45000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(106,'05/06/99',45000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(107,'05/06/97',45000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(108,'03/03/95',120000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(109,'09/03/87',120000) insert into datos_contrato(codigo_empleado,fecha_contrato,cuota)values(110,'03/06/88',90000)
insert into producto(descripcion,precio_costo,codigo_fabricante)values('Arandela',0.50,1) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Bisagra',1,2) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Extractor',135,3) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Junta',21,2) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Cubo',350,4) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Perno',21,5) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Red',821,3) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Escofina',35,2) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Bomba V6',1012,1) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Rele',35,3) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Bagueta A3',48,5) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Ventilador',289,1) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Linterna 2X',28,2) insert into producto(descripcion,precio_costo,codigo_fabricante)values('Linterna 3X',38,2)
insert into stock(codigo_producto,cantidad,pto_reposicion)values(1001,1000,1000) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1002,1500,1000) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1003,450,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1004,830,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1005,180,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1006,1000,1000) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1007,500,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1008,300,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1009,450,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1010,320,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1011,750,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1012,450,1000) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1013,150,500) insert into stock(codigo_producto,cantidad,pto_reposicion)values(1014,300,500)
insert into precio_venta(codigo_producto,codigo_lista)values(1001,1) insert into precio_venta(codigo_producto,codigo_lista)values(1001,2) insert into precio_venta(codigo_producto,codigo_lista)values(1002,1) insert into precio_venta(codigo_producto,codigo_lista)values(1002,2) insert into precio_venta(codigo_producto,codigo_lista)values(1003,1) insert into precio_venta(codigo_producto,codigo_lista)values(1003,2) insert into precio_venta(codigo_producto,codigo_lista)values(1004,1) insert into precio_venta(codigo_producto,codigo_lista)values(1004,2) insert into precio_venta(codigo_producto,codigo_lista)values(1005,1) insert into precio_venta(codigo_producto,codigo_lista)values(1005,2) insert into precio_venta(codigo_producto,codigo_lista)values(1006,1) insert into precio_venta(codigo_producto,codigo_lista)values(1006,2) insert into precio_venta(codigo_producto,codigo_lista)values(1007,1) insert into precio_venta(codigo_producto,codigo_lista)values(1008,1) insert into precio_venta(codigo_producto,codigo_lista)values(1009,1) insert into precio_venta(codigo_producto,codigo_lista)values(1010,1) insert into precio_venta(codigo_producto,codigo_lista)values(1010,2) insert into precio_venta(codigo_producto,codigo_lista)values(1011,1) insert into precio_venta(codigo_producto,codigo_lista)values(1011,2) insert into precio_venta(codigo_producto,codigo_lista)values(1012,1) insert into precio_venta(codigo_producto,codigo_lista)values(1012,2) insert into precio_venta(codigo_producto,codigo_lista)values(1013,1) insert into precio_venta(codigo_producto,codigo_lista)values(1014,1)
insert into cliente(codigo_lista,razon_social)values(1,'Luis garcia Antón') insert into cliente(codigo_lista,razon_social)values(1,'Jaime LLorens') insert into cliente(codigo_lista,razon_social)values(1,'El Triángulo') insert into cliente(codigo_lista,razon_social)values(1,'Bujes y Pernos') insert into cliente(codigo_lista,razon_social)values(1,'Carlos Tena') insert into cliente(codigo_lista,razon_social)values(2,'La Manivela') insert into cliente(codigo_lista,razon_social)values(2,'Juan Bolto') insert into cliente(codigo_lista,razon_social)values(2,'Alvarez Hnos.') insert into cliente(codigo_lista,razon_social)values(1,'ORSAN S.A.') insert into cliente(codigo_lista,razon_social)values(2,'Cristóbal García') insert into cliente(codigo_lista,razon_social)values(1,'La Fontella') insert into cliente(codigo_lista,razon_social)values(1,'Sunshine Ready') insert into cliente(codigo_lista,razon_social)values(2,'San Antonio') insert into cliente(codigo_lista,razon_social)values(2,'Castelnuovo S.A.') insert into cliente(codigo_lista,razon_social)values(1,'BlueMoon') insert into cliente(codigo_lista,razon_social)values(1,'Las Cabañas') insert into cliente(codigo_lista,razon_social)values(2,'Electrosur')
insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/03/02',101,1) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/03/02',101,2) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('04/03/02',102,3) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',104,4) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/03/02',104,1) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',104,5) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',105,4) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/03/02',108,7) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/03/02',108,6) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('04/03/02',104,6) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/03/02',101,1) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/03/02',110,4) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/04/02',110,12) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('01/04/02',102,12) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('02/04/02',104,15) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('03/04/02',108,15) insert into pedido(fecha_pedido,codigo_empleado,codigo_cliente)values('04/04/02',101,12)
insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(1,1,1003,10) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(1,2,1005,12) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(1,3,1007,45) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(2,1,1001,78) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(3,1,1008,32) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(3,2,1009,5) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(3,3,1003,46) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(4,1,1004,12) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(4,2,1005,45) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(5,1,1002,23) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(6,1,1007,30) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(6,2,1008,14) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(7,1,1006,7) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(7,2,1005,65) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(7,3,1004,120) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(8,1,1004,32) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(8,2,1006,74) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(9,1,1009,115) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(9,2,1003,89) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(10,1,1004,10) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(10,2,1001,12) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(11,1,1003,12) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(11,2,1008,6) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(11,3,1009,38) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,1,1005,65) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,2,1001,56) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,3,1009,45) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,4,1004,18) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(12,5,1003,14) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(13,1,1011,21) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(13,2,1012,32) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(14,1,1013,100) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(14,2,1014,6) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(14,3,1010,18) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(15,1,1009,9) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(15,2,1012,32) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(16,1,1014,5) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(17,1,1011,10) insert into detalle_pedido(codigo_pedido,numero_linea,codigo_producto,cantidad)values(17,2,1001,12) SELECT * FROM pedido
--4----------------------------------------------------------------------------------------------- --Ejercicio 4 Resolver las siguientes consultas utilizando sentencias SQL. --Consultas simples (una sola tabla).--------- --1. Obtener una lista con los nombres de las distintas oficinas de la empresa. SELECT descripcion AS nom_oficinas FROM oficina
--2. Obtener una lista de todos los productos indicando descripción del producto, su precio --de costo y su precio de costo IVA incluido (tomar el IVA como 21%). SELECT descripcion, precio_costo, (precio_costo + precio_costo * 21/100) AS precio_iva FROM producto
--3. Obtener una lista indicando para cada empleado apellido, nombre, fecha de --cumpleaños y edad. SELECT apellido, nombre, fecha_nacimiento, (YEAR(GETDATE()) - YEAR(fecha_nacimiento)) AS Edad FROM empleado
--4. Listar todos los empleados que tiene un jefe asignado. SELECT * FROM empleado --veo q codigo es el jefe SELECT nombre, apellido, codigo_jefe FROM empleado WHERE codigo_jefe != 0
--5. Listar los empleados de nombre “María” ordenando por apellido. SELECT nombre FROM empleado WHERE nombre='Maria'
--6. Listar los clientes cuya razón social comience con “L” ordenando por código de --cliente SELECT razon_social FROM cliente WHERE razon_social LIKE 'L%' ORDER BY codigo_cliente
--7. Listar toda la información de los pedidos de marzo ordenando por fecha de pedido. SELECT * FROM pedido WHERE MONTH(fecha_pedido)=3 ORDER BY fecha_pedido
--8. Listar las oficinas que no tienen asignado director. SELECT * FROM oficina WHERE codigo_director IS NULL
--9. Listar los cuatro productos de menor precio de costo. SELECT TOP 4 * FROM producto ORDER BY precio_costo
--10. Listar los códigos de empleados de los tres que tengan la mayor cuota. SELECT TOP 3 codigo_empleado FROM datos_contrato ORDER BY cuota DESC
--Consultas multitabla.---------- --1. De cada producto listar descripción, razón social del fabricante y stock ordenando por --razón social y descripción. SELECT p.descripcion, f.razon_social AS Fabricante, s.cantidad FROM producto p, fabricante f, stock s WHERE p.codigo_fabricante = f.codigo_fabricante AND p.codigo_producto = s.codigo_producto ORDER BY f.razon_social , p.descripcion
--2. De cada pedido listar código de pedido, fecha de pedido, apellido del empleado y --razón social del cliente. SELECT p.codigo_pedido, p.fecha_pedido, e.apellido AS empleado, c.razon_social AS Cliente FROM pedido p, empleado e, cliente c WHERE p.codigo_empleado = e.codigo_empleado AND p.codigo_cliente = c.codigo_cliente
--3. Listar por cada empleado apellido, cuota asignada, oficina a la que pertenece y --apellido de su jefe ordenando en forma descendente por cuota. SELECT e.apellido, dc.cuota, o.descripcion AS Oficina, e.codigo_jefe FROM empleado e, datos_contrato dc, oficina o WHERE e.codigo_empleado = dc.codigo_empleado AND e.codigo_oficina = o.codigo_oficina ORDER BY dc.cuota DESC --falta ape del jefe
--4. Listar sin repetir la razón social de todos aquellos clientes que hicieron pedidos en --abril. SELECT DISTINCT c.razon_social, p.fecha_pedido FROM cliente c, pedido p WHERE c.codigo_cliente = p.codigo_cliente AND MONTH(fecha_pedido) = 3
--5. Listar sin repetir los productos que fueron pedidos en marzo. SELECT DISTINCT p.descripcion, pe.fecha_pedido FROM producto p, pedido pe, detalle_pedido dp WHERE p.codigo_producto = dp.codigo_producto AND pe.codigo_pedido = dp.codigo_pedido AND MONTH(pe.fecha_pedido) =3
--6. Listar aquellos empleados que están contratados por más de diez años ordenando por --cantidad de años en forma descendente. SELECT e.nombre, e.apellido, dc.fecha_contrato, (YEAR(getdate()) - YEAR(dc.fecha_contrato)) AS Antiguedad FROM empleado e, datos_contrato dc WHERE e.codigo_empleado = dc.codigo_empleado AND (YEAR(getdate()) - YEAR(dc.fecha_contrato)) > 10 ORDER BY Antiguedad DESC
--7. Obtener una lista de los clientes mayoristas ordenada por razón social. SELECT * FROM lista --primero verifico q codigo es mayorista SELECT c.razon_social, l.descripcion FROM cliente c, lista l WHERE c.codigo_lista = l.codigo_lista AND l.descripcion = 'mayorista' ORDER BY c.razon_social
--8. Obtener una lista sin repetir que indique que productos compró cada cliente, ordenada --por razón social y descripción. SELECT DISTINCT c.razon_social, p.descripcion FROM cliente c, producto p, pedido pe, detalle_pedido dp WHERE c.codigo_cliente = pe.codigo_cliente AND p.codigo_producto = dp.codigo_producto AND pe.codigo_pedido = dp.codigo_pedido ORDER BY c.razon_social, p.descripcion
--9. Obtener una lista con la descripción de aquellos productos cuyo stock está por debajo --del punto de reposición indicando cantidad a comprar y razón social del fabricante --ordenada por razón social y descripción. ALTER TABLE producto ADD pto_reposicion INT DEFAULT 500 SELECT * FROM producto
SELECT p.descripcion, s.cantidad, f.razon_social AS Fabricante FROM producto p, stock s, fabricante f WHERE p.codigo_fabricante = f.codigo_fabricante AND p.codigo_producto = s.codigo_producto ORDER BY f.razon_social, p.descripcion
--10. Listar aquellos empleados cuya cuota es menor a 50000 o mayor a 100000. SELECT * FROM datos_contrato SELECT e.apellido, dc.cuota FROM empleado e, datos_contrato dc WHERE e.codigo_empleado = dc.codigo_empleado AND dc.cuota < 50000 OR dc.cuota > 100000
--Consultas de actualización de datos-------- --1. Crear una tabla llamada “aux_empleado” que contenga las filas de la tabla empleados. SELECT INTO aux_empleado FROM empleado SELECT FROM empleado SELECT * FROM aux_empleado
--2. Calcular los precios de venta mayorista y minorista de todos los productos. SELECT FROM precio_venta SELECT p.descripcion, p.precio_costo, l.descripcion, (p.precio_costo + p.precio_costo l.ganancia_porcentaje/100) AS Precio_venta FROM producto p, lista l, precio_venta pv WHERE p.codigo_producto = pv.codigo_producto AND pv.codigo_lista = l.codigo_lista
--2. ACTUALIZAR los precios de venta mayorista y minorista de todos los productos. UPDATE precio_venta SET precio = p.precio_costo + p.precio_costo * l.ganancia_porcentaje/100 FROM producto p, lista l, precio_venta pv WHERE p.codigo_producto = pv.codigo_producto AND pv.codigo_lista = l.codigo_lista
--3. Eliminar los fabricantes que no tengan productos en la tabla “productos”. --SELECT codigo_fabricante, razon_social Primero corroboro DELETE FROM fabricante WHERE codigo_fabricante NOT IN (SELECT codigo_fabricante FROM producto)
SELECT * FROM fabricante --corroboro lo q borre
--4. Eliminar a los clientes que no haya realizado ningún pedido. --SELECT codigo_cliente, razon_social Primero corroboro DELETE FROM cliente WHERE codigo_cliente NOT IN (SELECT codigo_cliente FROM pedido)
SELECT * FROM cliente --coorroboro lo q borre
--5. Aumentar en un 5% la cuota de aquellos empleados con contrato anterior al 01/01/1999. SELECT FROM datos_contrato --SELECT codigo_empleado Corroboro UPDATE datos_contrato SET cuota=cuota + cuota 5/100 WHERE (YEAR(fecha_contrato) < 1999)
--6. Cambiar el código de empleado del empleado 110 a 112 verificando la actualización en cascada. SELECT * FROM empleado UPDATE empleado SET codigo_empleado=112 WHERE codigo_empleado = 110
--7. Crear una tabla llamada “aux_producto” que contenga todas las filas de la tabla producto. SELECT INTO aux_producto FROM producto SELECT FROM aux_producto --corroboro
--8. Actualizar el precio de costo de cada producto incrementándolo en un 10%. SELECT * FROM producto
UPDATE producto SET precio_costo=precio_costo + precio_costo * 10/100
--9. Recuperar los precios originales de los productos a partir de la tabla “aux_producto”. SELECT FROM producto SELECT FROM aux_producto
INSERT INTO producto(precio_costo) --NO FUNCIONA!!!!! SELECT precio_costo FROM aux_producto
--10. Actualizar el punto de reposición de todos los productos del fabricante “Tomasti HNOS.” a 600. SELECT p.descripcion, p.pto_reposicion, f.razon_social --CORROBORO FROM producto p, fabricante f WHERE p.codigo_fabricante = f.codigo_fabricante AND f.razon_social = 'Tomasti Hnos.'
UPDATE producto SET pto_reposicion = 600 FROM producto p, fabricante f WHERE p.codigo_fabricante = f.codigo_fabricante AND f.razon_social = 'Tomasti Hnos.'
--Consultas de resumen ------------------ --1. Hallar el precio medio de los productos del fabricante “ABC Comercial”. SELECT f.razon_social, AVG(p.precio_costo) AS Promedio FROM fabricante f, producto p WHERE f.codigo_fabricante = p.codigo_fabricante AND f.razon_social ='ABC Comercial' GROUP BY f.razon_social
--2. Indicar la cantidad de productos que provee cada fabricante. SELECT f.razon_social, COUNT(p.codigo_producto) AS Tot_Productos FROM fabricante f, producto p WHERE f.codigo_fabricante = p.codigo_fabricante GROUP BY f.razon_social
--3. Informar por cada pedido: código de pedido, fecha de pedido, razón social del cliente e importe total. SELECT pe.codigo_pedido, pe.fecha_pedido, c.razon_social, SUM(dp.cantidad * pv.precio) AS Total FROM pedido pe, cliente c, detalle_pedido dp, precio_venta pv WHERE pe.codigo_cliente = c.codigo_cliente AND pe.codigo_pedido = dp.codigo_pedido AND dp.codigo_producto = pv.codigo_producto GROUP BY pe.codigo_pedido, pe.fecha_pedido, c.razon_social
--4. Actualizar la columna ventas en la tabla “datos_contrato” con el importe correspondiente al total --de pedidos de cada empleado SELECT * FROM datos_contrato
SELECT p.codigo_empleado, SUM(dp.cantidad pv.precio) AS Importe -- NO FUNC EL UPDATE --UPDATE datos_contrato SET --ventas=SUM(dp.cantidad pv.precio) FROM pedido p, detalle_pedido dp, precio_venta pv, datos_contrato dt WHERE p.codigo_pedido= dp.codigo_pedido AND dp.codigo_producto = pv.codigo_producto AND p.codigo_empleado = dt.codigo_empleado GROUP BY p.codigo_empleado
--5. Informar cuántos empleados tiene cada oficina. SELECT o.descripcion, COUNT(e.codigo_empleado) AS Cantidad_Empleados FROM oficina o, empleado e WHERE o.codigo_oficina = e.codigo_oficina GROUP BY o.descripcion
--6. Hallar en que fecha se realizó el primer pedido. SELECT TOP 1 * FROM pedido ORDER BY fecha_pedido
--7. Informar cuales son las medias de las columnas “cuota” y “ventas”. SELECT AVG(cuota) AS Media_cuota, AVG(ventas) AS Media_ventas -- NO LO entiendo FROM datos_contrato
--8. Informar la cantidad de pedidos de cada cliente SELECT c.razon_social, COUNT(p.codigo_pedido) AS Cant_Pedidos FROM cliente c, pedido p WHERE c.codigo_cliente = p.codigo_cliente GROUP BY c.razon_social
--9. Informar la cantidad pedida de cada producto en el mes de marzo. SELECT p.descripcion, COUNT(dp.codigo_producto) AS Cant_Pedidos, pe.fecha_pedido FROM producto p, detalle_pedido dp, pedido pe WHERE p.codigo_producto = dp.codigo_producto AND dp.codigo_pedido = pe.fecha_pedido AND MONTH(pe.fecha_pedido) = 3 GROUP BY p.descripcion, pe.fecha_pedido SELECT * FROM pedido --NO FUNCIONA
--10. Informar la cantidad de productos con stock bajo el punto de reposición. SELECT * FROM stock SELECT p.descripcion, s.cantidad, p.pto_reposicion FROM producto p, stock s WHERE p.codigo_producto = s.codigo_producto AND s.cantidad < p.pto_reposicion
--Subconsultas -------------------------------- --1. Listar razón social de los clientes atendidos por el empleado “González Analía”. SELECT FROM detalle_pedido SELECT FROM pedido
SELECT cl.razon_social, em.apellido, em.nombre FROM cliente cl, empleado em, pedido pe WHERE cl.codigo_cliente = pe.codigo_cliente AND em.codigo_empleado = pe.codigo_empleado AND em.apellido = 'Gonzales' AND em.nombre = 'Analia'
--2. Listar los empleados que no están en la oficina “Ventas Interior”. SELECT * FROM empleado WHERE codigo_oficina NOT IN (SELECT codigo_oficina FROM oficina WHERE descripcion = 'Ventas Interior')
--3. Listar las oficinas en las que haya por lo menos un empleado cuyos pedidos --representen un 10% más que su cuota
--4. Listar los productos que por los cuáles no se hayan pedido 200 o más unidades durante el mes de marzo. SELECT pr.descripcion, pe.fecha_pedido, SUM(dp.cantidad) AS Total_Unidades FROM producto pr, pedido pe, detalle_pedido dp WHERE pr.codigo_producto = dp.codigo_producto AND pe.codigo_pedido = dp.codigo_pedido AND MONTH(pe.fecha_pedido) = 3 GROUP BY pr.descripcion, pe.fecha_pedido HAVING SUM(dp.cantidad) < 100
--5. Listar sin repetir la razón social de aquellos clientes que hicieron por lo menos un pedido cuyo --importe total sea mayor que $850. SELECT razon_social FROM cliente --NO SALE WHERE codigo_cliente IN (SELECT pe.codigo_cliente FROM pedido pe, precio_venta pv, detalle_pedido dp WHERE dp.codigo_pedido = pe.codigo_pedido AND dp.codigo_producto = pv.codigo_producto AND SUM(pv.precio * dp.cantidad) > 850 GROUP BY dp.codigo_producto)
--6. Listar la razón social de aquellos fabricantes cuyos productos no fueron pedidos en el mes de abril. SELECT razon_social FROM fabricante WHERE codigo_fabricante NOT IN (SELECT p.codigo_fabricante FROM producto p, pedido pe, detalle_pedido dp WHERE p.codigo_producto = dp.codigo_producto AND pe.codigo_pedido = dp.codigo_pedido AND MONTH(pe.fecha_pedido) = MONTH(4))
--7. Listar la razón social de aquellos fabricantes cuyos productos fueron pedidos en el mes de abril SELECT razon_social FROM fabricante WHERE codigo_fabricante IN (SELECT p.codigo_fabricante FROM producto p, pedido pe, detalle_pedido dp WHERE p.codigo_producto = dp.codigo_producto AND pe.codigo_pedido = dp.codigo_pedido AND MONTH(pe.fecha_pedido) = MONTH(1))
--8. Listar sin repetir los productos que figuren con por lo menos una cantidad pedida --mayor a 70 en el mes de marzo.
SELECT p.descripcion, dp.cantidad, pe.fecha_pedido FROM producto p, detalle_pedido dp, pedido pe WHERE p.codigo_producto = dp.codigo_producto AND dp.codigo_pedido = pe.codigo_pedido AND dp.cantidad > 70 AND MONTH(pe.fecha_pedido) = 3
--9. Listar los productos de los cuales no se tiene precio minorista. SELECT FROM lista SELECT FROM producto SELECT * FROM precio_venta
SELECT pr.descripcion, li.descripcion, pv.precio FROM producto pr, lista li, precio_venta pv WHERE pr.codigo_producto = pv.codigo_producto AND li.codigo_lista = pv.codigo_lista AND pv.codigo_lista = 2 AND pv.precio IS NULL
--10. Listar los clientes de Adriana Alvarez que no hayan --hecho un pedido con importe total superior a $1000. SELECT c.razon_social FROM cliente c, empleado e, pedido pe -- sin terminar WHERE pe.codigo_empleado = e.codigo_empleado AND pe.codigo_cliente = c.codigo_cliente AND e.apellido = 'Viguer'
----------------------------TP4---------------------------------------------------------- --1. Crear una Vista que muestra de cada fabricante sus productos y la cantidad en stock. CREATE VIEW fabri_prod AS SELECT f.razon_social, p.descripcion, s.cantidad FROM fabricante f, producto p, stock s WHERE f.codigo_fabricante = p.codigo_fabricante AND p.codigo_producto = s.codigo_producto
--la uso SELECT * FROM fabri_prod ORDER BY razon_social
--2. Listar mediante el uso de cursores, para cada oficina nombre de la oficina, apellido y --nombre del director y apellido y nombre de cada uno de los empleados asignados a la oficina. --Me falto los nombre de empleados
--1° declaro variables a emplear DECLARE @nom_oficina varchar(20), @ape_director varchar(20), @nom_director varchar(20)
--2° declaro cursor DECLARE c_listar CURSOR FOR SELECT o.descripcion, e.apellido, e.nombre FROM oficina o, empleado e WHERE o.codigo_oficina = e.codigo_oficina AND e.codigo_empleado IN (SELECT o.codigo_director FROM oficina)
--3° Abro el puntero OPEN c_listar
--4° Lo recorro FETCH c_listar INTO @nom_oficina, @ape_director, @nom_director WHILE (@@FETCH_STATUS=0) BEGIN PRINT @nom_oficina+'DIRECTOR = '+TRIM(@ape_director)+' '+@nom_director FETCH c_listar INTO @nom_oficina, @ape_director, @nom_director END
--5° Cierro el puntero CLOSE c_listar
--6° Libero memoria DEALLOCATE c_listar
--3. Listar mediante el uso de cursores, de cada pedido los datos del encabezado y su detalle valorizado. SELECT FROM pedido SELECT FROM detalle_pedido
DECLARE @nom_producto varchar(20), @nom_cant varchar(20), @nom_total varchar(20)
DECLARE c_listar2 CURSOR FOR SELECT p.descripcion, dt.cantidad, (dt.cantidad * pv.precio) AS valor_total FROM producto p, detalle_pedido dt, precio_venta pv WHERE dt.codigo_producto = p.codigo_producto AND p.codigo_producto = pv.codigo_producto
OPEN c_listar2
FETCH c_listar2 INTO @nom_producto, @nom_cant, @nom_total WHILE(@@FETCH_STATUS=0) BEGIN PRINT TRIM(@nom_producto)+'='+ TRIM(@nom_cant)+' '+'$'+TRIM(@nom_total)+'.' FETCH c_listar2 INTO @nom_producto, @nom_cant, @nom_total END
CLOSE c_listar2
DEALLOCATE c_listar2
--4. Modificar la tabla cliente incorporando el campo "clave" y utilice cursores --para completarlo con el nombre de usuario. SELECT * FROM cliente ALTER TABLE cliente DROP COLUMN clave ALTER TABLE cliente ADD clave varchar(20) DEFAULT 0
DECLARE @nom_usuario varchar(20)
DECLARE c_listar3 CURSOR FOR SELECT razon_social FROM cliente FOR UPDATE
OPEN c_listar3
FETCH c_listar3 INTO @nom_usuario WHILE(@@FETCH_STATUS=0) BEGIN UPDATE cliente SET clave='COD'+@nom_usuario PRINT TRIM(@nom_usuario)+'actualizado' FETCH c_listar3 INTO @nom_usuario END
CLOSE c_listar3
DEALLOCATE c_listar3
--5. Crear un procedimiento almacenado que actualice los precios de venta de todos aquellos --productos cuyo precio de venta es menor al precio de venta promedio. --Ingresar por parámetro la lista a actualizar (mayorista o minorista) y el porcentaje de incremento. CREATE PROCEDURE act_precio --HECHO UNA PARTE (@porcen int) AS BEGIN UPDATE precio_venta SET precio=p.precio_costo + p.precio_costo* @porcen / 100 FROM producto p, precio_venta pv WHERE p.codigo_producto = pv.codigo_producto END
--lo ejecuto EXEC act_precio @porcen=10
--lo elimino DROP PROCEDURE act_precio
SELECT * FROM precio_venta
--6. Crear un procedimiento almacenado que devuelva los datos de todos los productos --o de aquel producto cuya descripción se ingresa por parámetro. CREATE PROCEDURE d_art (@descrp varchar(20)) AS BEGIN
IF(@descrp IS NULL) BEGIN
SELECT * FROM producto END
IF(@descrp IS NOT NULL) BEGIN SELECT * FROM producto WHERE descripcion = @descrp END
END
--lo ejecuto EXEC d_art @descrp = NULL
--lo elimino DROP PROCEDURE d_art
--7. Crear un procedimiento almacenado que permita ingresar nuevos productos --en la tabla productos ingresando los datos del producto por parámetros. CREATE PROCEDURE i_producto (@des varchar(50), @pre numeric, @cod_fabri int, @repo int) AS BEGIN INSERT INTO producto(descripcion, precio_costo, codigo_fabricante, pto_reposicion) VALUES (@des, @pre, @cod_fabri, @repo) END
--lo ejecuto EXEC i_producto @des='el agregado', @pre=110, @cod_fabri=5, @repo=200
--8. Crear un procedimiento almacenado que informe de cada fabricante o del fabricante cuya razón --social se ingresa como parámetro, los productos cuya cantidad en stock es menor al punto de reposición, --indicando cantidad a reponer, costo por producto y costo total por fabricante. CREATE PROCEDURE p_listas (@val varchar(50)) AS BEGIN IF @val IS NULL BEGIN SELECT fa.razon_social AS Fabricante, pr.descripcion AS Producto, st.cantidad, st.pto_reposicion, (st.pto_reposicion - st.cantidad) AS Cant_Reponer, pr.precio_costo AS Cos_unidad, (pr.precio_costo (st.pto_reposicion - st.cantidad)) AS Cos_tot FROM fabricante fa, producto pr, stock st WHERE pr.codigo_fabricante = fa.codigo_fabricante AND pr.codigo_producto = st.codigo_producto AND st.cantidad < st.pto_reposicion END IF @val IS NOT NULL BEGIN SELECT fa.razon_social AS Fabricante, pr.descripcion AS Producto, st.cantidad, st.pto_reposicion, (st.pto_reposicion - st.cantidad) AS Cant_Reponer, pr.precio_costo AS Cos_unidad, (pr.precio_costo (st.pto_reposicion - st.cantidad)) AS Cos_tot FROM fabricante fa, producto pr, stock st WHERE pr.codigo_fabricante = fa.codigo_fabricante AND pr.codigo_producto = st.codigo_producto AND st.cantidad < st.pto_reposicion AND fa.razon_social = @val END END
EXEC p_listas @val = 'ABC COMERCIAL'
--9. Crear un procedimiento almacenado que ingresando como parámetros: nombre del producto, --cantidad comprada, código de fabricante y precio de costo, realice las siguientes --acciones: En el caso en que el producto sea nuevo darlo de alta en la tabla “producto”,
--en cualquier caso, actualice el stock y el punto de reposición del producto.
--10. Crear un desencadenador (trigger) en la tabla “producto” que, ante el ingreso de un nuevo producto, --incorpore en la tabla “precio_venta” los precios de venta Página 21of 24mayorista y minorista --considerando los porcentajes de ganancia indicados en la tabla “lista”
--11. Crear un desencadenador (trigger) que grabe en la tabla auditoría_producto (la que deberá crear), --los valores anteriores cuando se actualiza un producto. SELECT FROM producto CREATE TABLE auditoria_producto (codigo_producto int, descripcion varchar(50), precio_costo money, codigo_fabricante int, pto_reposicion int ) SELECT FROM auditoria_producto
CREATE TRIGGER grabe_act ON producto FOR UPDATE AS INSERT INTO auditoria_producto(codigo_producto, descripcion, precio_costo,codigo_fabricante, pto_reposicion) SELECT del.codigo_producto, del.descripcion, del.precio_costo, del.codigo_fabricante, del.pto_reposicion FROM deleted del
UPDATE producto SET precio_costo = 5 WHERE codigo_producto = 1001
--12. Crear un desencadenador (trigger) en la tabla "detalle_pedido" para la acción de inserción que --controle si la cantidad que se intenta pedir sea menor o igual al stock del producto. --En el caso en que la cantidad pedida sea menor o igual al stock, debe permitir el insert y actualizar --el stock del producto. En el caso en que el stock no sea suficiente, debe informar que no es posible --la operación y abortarla.
--13. Activar o desactivar desencadenadores.