[INF-272] PROYECTO: 25 CONSULTAS

--*****************************************************************************************-
--*REALIZAR 25 CONSULTAS CON FUNCIONES AGREGADAS, SUBCONSULTAS Y FUNCIONES DE AGRUPAMIENTO*-
--****************************************************************************************-

--1.- ORDENAR, CONTAR Y  MOSTRAR LAS SALAS SEGUN LA CANTIDAD DE MESAS DISPONIBLES
SELECT M.ID_SALA, S.NOMBRE, COUNT(M.ID_SALA)
FROM MESAS M, SALAS S
WHERE M.ESTADO='DISPONIBLE' AND M.ID_SALA=S.ID_SALA
GROUP BY M.ID_SALA, S.NOMBRE
ORDER BY COUNT (M.ID_SALA) DESC


--2.- MOSTRAR  LOS PRODUCTOS QUE SOLICITO EL CLIENTE 'GONZALO PEREZ LOPEZ' EN EL CONTRATO 1
SELECT  P.NOMBRE, LC.CANTIDAD
FROM CLIENTES C,CONTRATOS CO, LINEA_COTIZACION LC, PRODUCTOS P
WHERE CO.ID_CONTRATO=1 AND CO.ID_CLIENTE =(SELECT ID_CLIENTE FROM CLIENTES WHERE NOMBRE='GONZALO PEREZ LOPEZ')
AND LC.ID_CONTRATO=CO.ID_CONTRATO AND P.ID_PRODUCTO=LC.ID_PRODUCTO


--3.- MOSTRAR LOS 3 CLIENTES (EMPRESA) QUE HICIERON MAYOR CANTIDAD QUE CONTRATOS
SELECT TOP 3 CO.ID_CLIENTE,C.NOMBRE ,COUNT(*) AS nroCONTRATOS
FROM CONTRATOS CO, CLIENTES C
WHERE C.RAZON_SOCIAL IS NOT NULL AND CO.ID_CLIENTE = C.ID_CLIENTE
GROUP BY CO.ID_CLIENTE,C.NOMBRE
ORDER BY COUNT (*) DESC

--4.- CONTAR LA CANTIDAD DE EMPLEADOSE SEGUN EL TIPO QUE CARGO QUE TIENE SIN CONTAR AL GERENTE Y QUE TENGAN UN SUELDO MAYOR A 4000
SELECT  E.CARGO, COUNT(E.CARGO)
FROM EMPLEADOS E
WHERE E.CARGO <> 'GERENTE' AND E.LIQUIDO_PAGABLE >4000
GROUP BY E.CARGO

--5.- CONTAR LA CANTIDAD DE PRODUCTOS QUE TIENE CADA ALMACEN
SELECT A.NOMBRE, COUNT(P.ID_ALMACEN) AS NroPRODUCTOS
FROM ALMACENES A, PRODUCTOS P
WHERE A.ID_ALMACEN = P.ID_ALMACEN
GROUP BY A.NOMBRE,P.ID_ALMACEN


--6.- DETERMINAR LA/LAS COMANDA(PEDIDO) CON LA CANTIDAD DE PRODUCTOS
SELECT LP.ID_COMANDA,COUNT(LP.ID_COMANDA) AS CANTIDAD_PRODUCTOS
FROM PEDIDOS PE , PRODUCTOS P, LINEA_PEDIDO LP
WHERE PE.ID_COMANDA=LP.ID_COMANDA AND LP.ID_PRODUCTO=P.ID_PRODUCTO
GROUP BY LP.ID_COMANDA


--7.- VER LOS PRODUCTOS QUE TRAPASO EL ALMACEN 'LA PAZ AL ALMACEN ORURO'
SELECT DISTINCT P.NOMBRE
FROM TRASPASOS T, PRODUCTOS P
WHERE T.ID_PRODUCTO=P.ID_PRODUCTO AND
T.ID_ALMACEN =(SELECT ID_ALMACEN FROM ALMACENES WHERE NOMBRE='LA PAZ') AND
T.ID_ALMACEN_DESTINO =(SELECT ID_ALMACEN FROM ALMACENES WHERE NOMBRE='ORURO')


--8.- MOSTRAR LOS CLIENTES (PERSONA) QUE HICIERON MAYOR CANTIDAD QUE CONTRATOS
SELECT TOP 3 CO.ID_CLIENTE,C.NOMBRE ,COUNT(*) AS nroCONTRATOS
FROM CONTRATOS CO, CLIENTES C
WHERE C.RAZON_SOCIAL IS NULL AND CO.ID_CLIENTE = C.ID_CLIENTE
GROUP BY CO.ID_CLIENTE,C.NOMBRE
ORDER BY COUNT (*) DESC


--9.- DETERMINAR EL MESERO QUE ATENDIÓ MAYOR NUMERO DE PEDIDOS
SELECT TOP 1 M.NOMBRE,PE.ID_MESERO,COUNT (PE.ID_MESERO) AS nROCOMANDAS
FROM PEDIDOS PE, MESEROS M
WHERE M.ID_MESERO=PE.ID_MESERO
GROUP BY M.NOMBRE,PE.ID_MESERO
ORDER BY (COUNT(*)) DESC


--10.- MOSTRAR LOS PROVEDORES QUE PROPORCIONAN EL PRODUCTO 'ARROZ'
SELECT PR.NOMBRE, PR.NIT
FROM PROVEDORES PR, SUMINISTROS S, PRODUCTOS P
WHERE PR.ID_PROVEEDOR = S.ID_PROVEEDOR AND P.ID_COMPROBANTE=S.ID_COMPROBANTE


--11.- MOSTRAR LAS SALAS QUE TIENEN TODAS LAS MESAS OCUPADAS
SELECT S.NOMBRE
FROM SALAS S
WHERE S.ID_SALA IN (SELECT M.ID_SALA
           FROM SALAS S, MESAS M
           WHERE S.ID_SALA=M.ID_SALA AND M.ESTADO='NO DISPONIBLE'
           GROUP BY (M.ID_SALA)
           HAVING COUNT(*) =6
           )


--12.- MOSTRAR DE QUE ESTA COMPUESTO EL PRODUCTO 'HAMBURGUESA'
SELECT NOMBRE
FROM PRODUCTOS
WHERE ID_PRODUCTO IN (SELECT C.ID_INSUMO
           FROM PRODUCTOS P, COMPOSICIONES C
           WHERE P.ID_PRODUCTO=C.ID_PRODUCTO AND P.NOMBRE='HAMBURGUESA')


--13.- MOSTRAR LA CETEGORIA QUE PERTENECE EL PRODUCTO 'TOMATE'
SELECT C.NOMBRE
FROM CATEGORIAS C, PRODUCTOS P, CATEGORIA_ITEM_PRODUCTO CIP
WHERE P.ID_PRODUCTO=CIP.ID_PRODUCTO AND CIP.ID_CATEGORIA=C.ID_CATEGORIA


--14.- MOSTRAR LOS CONTRATOS QUE SE HICIERON CON LA TARIFA 'NORMAL'
SELECT C.NRO_CONTRATO
FROM CONTRATOS C, TARIFA T
WHERE T.NOMBRE='NORMAL' AND C.ID_TARIFA=T.ID_TARIFA


--15.- MOSTRAR LOS DATOS DEL CLIENTE QUE HIZO EL CONTRATO MAS CARO
SELECT C.NOMBRE, C.NIT, C.RAZON_SOCIAL
FROM CLIENTES C, CONTRATOS CO
WHERE C.ID_CLIENTE=CO.ID_CLIENTE AND CO.MONTO_TOTAL IN(
               SELECT MAX(CO.MONTO_TOTAL)
               FROM CONTRATOS CO
               )


--16.- MOSTRAR LAS ORDENES DE DESPACHO QUE TRANSPORTARON VAJILLA 'BLANCA' O MANTELES 'AMARILLOS'
SELECT O.ID_ORDEN, O.FECHA
FROM ORDENES_DESPACHO O, LINEA_DESPACHO_VAJILLA LDV, VAJILLA V
WHERE O.ID_ORDEN=LDV.ID_ORDEN AND LDV.ID_VAJILLA=V.ID_VAJILLA AND  V.COLOR='BLANCO'
UNION
SELECT O.ID_ORDEN, O.FECHA
FROM ORDENES_DESPACHO O, LINEA_DESPACHO_MANTELES LDM, MANTELES M
WHERE O.ID_ORDEN=LDM.ID_ORDEN AND LDM.ID_MANTEL=M.ID_MANTEL AND  M.COLOR='BLANCO'


--17.- DETERMINAR EL PRODUCTO CON MAYOR NUMERO DE UNIDADES SEGUN EL ALMACEN
SELECT A.NOMBRE,P.NOMBRE, MAX(AP.CANTIDAD_ALMACEN) AS CANTIDAD
FROM ALMACENES A, ALMACEN_PRODUCTO AP, PRODUCTOS P
WHERE A.ID_ALMACEN=AP.ID_ALMACEN AND P.ID_PRODUCTO=AP.ID_PRODUCTO
GROUP BY A.NOMBRE,P.NOMBRE,AP.ID_PRODUCTO


--18.- MOSTRAR LAS VENTAS QUE SE HICIERON A CLIENTES EMPRESAS Y EL MONTO TOTAL QUE PAGARON
SELECT V.NRO_FACTURA, C.NOMBRE, CO.MONTO_TOTAL
FROM CLIENTES C, CONTRATOS CO, VENTAS V
WHERE C.RAZON_SOCIAL IS NOT NULL AND C.ID_CLIENTE=V.ID_CLIENTE AND V.ID_CONTRATO=CO.ID_CONTRATO

--19.- MOSTRAR EL NOMBRE DE LOS MESEROS QUE ATIENDEN EN LA SALA DENOMINADA 'COCHABAMBA'
SELECT DISTINCT M.ID_MESERO,M.NOMBRE, M.APATERNO
FROM MESEROS M, PEDIDOS P, MESAS ME, SALAS S
WHERE M.ID_MESERO=P.ID_MESERO AND P.ID_MESA=ME.ID_MESA AND ME.ID_SALA=S.ID_SALA AND S.NOMBRE='COCHABAMBA'  


--20.- MOSTRAR LA VAJILLA UTILIZADA EN EL CONTRATO DE CODIGO '2'
SELECT V.NOMBRE, V.TIPO, V.COLOR
FROM VAJILLA V, LINEA_DESPACHO_VAJILLA LDV, ORDENES_DESPACHO OD, CONTRATOS C
WHERE  C.ID_CONTRATO=2 AND C.ID_ORDEN=OD.ID_ORDEN AND OD.ID_ORDEN=LDV.ID_ORDEN AND LDV.ID_VAJILLA=V.ID_VAJILLA

--21.- MOSTRAR LOS MANTELES UTILIZADOS EN LOS CONTRATOS QUE SE REALIZARON EN 'LA PAZ'
SELECT DISTINCT M.ID_MANTEL,M.NOMBRE, M.TIPO, M.COLOR
FROM MANTELES M, LINEA_DESPACHO_MANTELES LDM, ORDENES_DESPACHO OD, CONTRATOS C
WHERE  C.LUGAR='LA PAZ' AND C.ID_ORDEN=OD.ID_ORDEN AND OD.ID_ORDEN=LDM.ID_ORDEN AND LDM.ID_MANTEL=M.ID_MANTEL


--22.- DETERMINAR EL PRODUCTO MAS CARO DEL ALMACEN LA PAZ Y MOSTRAR LA CANTIDAD EXISTENTE
SELECT P.NOMBRE,P.CANTIDAD,P.PRECIO_COMPRA
FROM PRODUCTOS P, ALMACENES A
WHERE P.NOMBRE='LA PAZ' AND P.ID_ALMACEN=A.ID_ALMACEN AND P.PRECIO_COMPRA=
(SELECT MAX (P.PRECIO_COMPRA)
FROM ALMACENES A,PRODUCTOS P
WHERE P.NOMBRE='LA PAZ' AND P.ID_ALMACEN=A.ID_ALMACEN)


--23.- MOSTRAR LOS DATOS DEL CLIENTE QUE HIZO EL CONTRATO MAS BARATO
SELECT C.NOMBRE, C.NIT, C.RAZON_SOCIAL
FROM CLIENTES C, CONTRATOS CO
WHERE C.ID_CLIENTE=CO.ID_CLIENTE AND CO.MONTO_TOTAL IN(
               SELECT MIN(CO.MONTO_TOTAL)
               FROM CONTRATOS CO
               )


--24.- MOSTRAR LAS SALAS QUE TIENEN TODAS LAS MESAS DESOCUPADAS
SELECT S.NOMBRE
FROM SALAS S
WHERE S.ID_SALA IN (SELECT M.ID_SALA
           FROM SALAS S, MESAS M
           WHERE S.ID_SALA=M.ID_SALA AND M.ESTADO='NO DISPONIBLE'
           GROUP BY (M.ID_SALA)
           HAVING COUNT(*) =0
           )


--25.- DETERMINAR EL PRODUCTO CON MENOR NUMERO DE UNIDADES SEGUN EL ALMACEN
SELECT A.NOMBRE,P.NOMBRE, MIN(AP.CANTIDAD_ALMACEN) AS CANTIDAD
FROM ALMACENES A, ALMACEN_PRODUCTO AP, PRODUCTOS P
WHERE A.ID_ALMACEN=AP.ID_ALMACEN AND P.ID_PRODUCTO=AP.ID_PRODUCTO
GROUP BY A.NOMBRE,P.NOMBRE,AP.ID_PRODUCTO