Práctico 12

CREATE DATABASE joins;
use joins;

CREATE TABLE EMPLEADOS(
    NumEmp int NOT NULL,
    Nombre VARCHAR(30),
    FecNacimiento DATE,
    Oficina varchar(20),
    Cargo varchar(20),
    FecIngreso DATE,
    Jefe int,
    MinimoVenta Float,
    VentaAnual Float,
    PRIMARY KEY(NumEmp),
    FOREIGN KEY(Oficina) REFERENCES OFICINAS(Oficina)
);

DROP TABLE EMPLEADOS;

ALTER TABLE EMPLEADOS
ADD CONSTRAINT fk_emp_ofi
FOREIGN KEY(OFICINA) REFERENCES OFICINAS(OFICINA);

CREATE TABLE OFICINAS(
    Oficina VARCHAR(15) NOT NULL PRIMARY KEY,
    Ciudad VARCHAR(15),
    Region VARCHAR(15),
    Encargado int,
    ObjetivoVentas Float,
    Ventas Float,
    FOREIGN KEY(Encargado) REFERENCES EMPLEADOS(NumEmp)
);

CREATE TABLE CLIENTES(
    NumCli int NOT NULL,
    Nombre VARCHAR(15),
    Representante int,
    Ventas Float,
    PRIMARY KEY(NumCli),
    FOREIGN KEY(Representante) references EMPLEADOS(NumEmp)
);

CREATE TABLE PRODUCTOS(
    IdFab int,
    IdProducto int,
    Descripcion VARCHAR(50),
    Precio Float,
    Stock boolean,
    CONSTRAINT cnt PRIMARY KEY (IdFab, IdProducto)
);

CREATE TABLE PEDIDOS(
    NumPEDIDO INT NOT NULL,
    IDFAB INT NOT NULL,
    IDPRODUCTO INT NOT NULL,
    FECHAPEDIDO DATE,
    NUMCLI INT,
    NUMEMP int,
    CANTIDAD FLOAT,
    IMPORTE FLOAT,
    CONSTRAINT pk_pedidos PRIMARY KEY (NUMPEDIDO, idfab, idproducto),
    foreign key(IDFAB, IDPRODUCTO) REFERENCES PRODUCTOS(IDFAB, IDPRODUCTO)
    /*CONSTRAINT IMPORTEPEDIDOS check (importe = precio * cantidad)*/
);

/*ALTER TABLE PEDIDOS ADD CONSTRAINT IMPORTEPEDIDOS CHECK (importe = productos.precio * cantidad);*/

select Oficina from OFICINAs where Encargado is null;

select IdFab, idproducto, descripcion, precio, precio*1.22 as 'Precio con IVA' from productos;

/*select nombre, NumEmp, DATEDIFF(CURDATE(), FecIngreso) as 'Dias desde que ingresó', YEAR(CURDATE()) - YEAR(FECNACIMIENTO) as 'Años' from empleados;*/

select nombre, NumEmp, DATEDIFF(CURDATE(), FecIngreso) as 'Dias desde que ingresó', TIMESTAMPDIFF(YEAR, FECNACIMIENTO, CURDATE()) as 'Años' from empleados;

select emp.nombre, emp.NumEmp, DATEDIFF(CURDATE(), emp.FecIngreso) as 'Dias desde que ingresó', TIMESTAMPDIFF(YEAR, emp.FECNACIMIENTO, CURDATE()) as 'Años', ofi.ciudad, ofi.region
from empleados emp, OFICINAS ofi
where emp.Oficina = ofi.oficina;

SELECT oficina
from oficinas
ORDER BY region, ciudad, oficina;


SELECT ped.*, pro.descripcion, cli.nombre
from pedidos ped, productos pro, clientes cli
where FeCHAPEDIDO >= '2024-03-01';


select ofi.oficina, ofi.ciudad, (select count(emp.NumEmp) from empleados) as 'Cantidad de empleados' from oficinas ofi, empleados emp
where ofi.region = 'este' and emp.oficina = ofi.Oficina
GROUP BY oficina;

select ped.* from pedidos ped, productos prod where (select count(*) from productos) > 5 and prod.IdFab = ped.idfab and prod.idproducto = ped.idproducto;

select prod.*, ped.fechaPedido from productos prod, pedidos ped where prod.idfab = ped.idfab and prod.idproducto = ped.idproducto;

select avg(ped.importe) as Promedio, SUM(ped.importe) as'Total de pedidos', AVG(prod.precio) as 'Promedio de precio de producto vendido' from productos prod, pedidos ped where prod.idfab = ped.idfab and prod.idproducto = ped.idproducto;



insert into EMPLEADOS (nombre, NumEmp, fecingreso, FecNacimiento) VALUES ('Juan', 25, '2023-03-06', '2004-12-01');

delete from empleados WHERE numEmp = 25