Tutorial de SQL++ para usuarios de SQL

 (Q16)
FROM clientes COMO c, pedidos COMO o
WHERE c.custid = o.custid
AND o.orderno = 1001
SELECT o.orderno,
       c.nombre COMO nombre_cliente,
       c.address,
       o.items AS items_ordered;

(Q17)
FROM clientes COMO c JOIN pedidos COMO o
  ON c.custid = o.custid
WHERE o.orderno = 1001
SELECT o.orderno,
       c.nombre COMO nombre_cliente,
       c.address
       o.items AS items_ordered;

(Q18)
FROM clientes AS c
     LEFT OUTER JOIN pedidos AS o ON c.custid = o.custid
SELECT c.custid, c.name, o.orderno, o.order_date
ORDER BY c.custid, o.fecha_pedido;

(Q19)
FROM pedidos COMO o, o.artículos COMO i
WHERE i.qty > 100
SELECT o.orderno, o.order_date, i.itemno AS item_number,
       i.qty AS quantity
ORDER BY o.orderno, item_number;
       
(Q20)
FROM pedidos COMO o UNNEST o.artículos COMO i
WHERE i.cantidad > 100
SELECT o.orderno, o.order_date, i.itemno AS item_number,
       i.qty AS quantity
ORDER BY o.orderno, item_number;
       
(Q21a)
FROM pedidos COMO o, o.artículos COMO i, clientes COMO c
WHERE o.custid = c.custid
AND i.itemno = 680
SELECT c.custid, c.name, o.order_date AS date
ORDER BY c.custid, date;
       
(P21b)
FROM pedidos COMO o, clientes COMO c
WHERE o.custid = c.custid
AND EXISTS
     (SELECT i.itemno
	  FROM o.items AS i
	  WHERE i.itemno = 680)
SELECT c.custid, c.name, o.fecha_pedido COMO fecha
ORDER BY c.custid, date;
 
(Q22)
FROM pedidos AS o
LET days = DATE_DIFF_STR(o.fecha_envío, o.fecha_pedido, "día")
WHERE días > 2
SELECT o.orderno, days - 2 AS days_late
ORDER BY days_late DESC;

(Q23)
FROM pedidos AS o
GROUP BY o.custid
SELECT o.custid, COUNT(o.orderno) AS `order count` (número de pedidos)
ORDER BY o.custid;

(Q24)
FROM clientes AS c
     LEFT OUTER JOIN pedidos AS o ON c.custid = o.custid
GROUP BY c.custid, c.name
SELECT c.custid, c.name, COUNT(o.orderno) AS `conteo de pedidos`.
ORDER BY c.custid;

(Q25)
FROM pedidos AS o
WHERE DATE_PART_STR(o.fecha_pedido, "año") = 2017
GROUP BY DATE_PART_STR(o.order_date, "month") AS month
SELECT mes, COUNT(*) COMO recuento_pedidos
ORDER BY mes;

(Q26)
FROM pedidos AS o, o.items as i
GROUP BY o.orderno
SELECT o.orderno, SUM(i.qty * i.price) AS revenue
ORDER BY o.orderno;

(Q27)
FROM pedidos COMO o, o.artículos como i
GROUP BY o.orderno
LET revenue = SUM(i.qty * i.price)
HAVING ingresos > 1000
SELECT o.orderno, revenue
ORDER BY ingresos DESC;

(Q28)
FROM pedidos AS o
LET ingresos =
   (FROM o.items AS i
   SELECT VALUE SUMA(i.cantidad * i.precio))[0])
WHERE ingresos > 1000
SELECT o.orderno, revenue
ORDER BY ingresos DESC;

(Q29)
FROM pedidos COMO o, o.artículos COMO i, clientes COMO c
WHERE o.custid = c.custid
GROUP BY o.orderno AS `order no.`,
         o.order_date AS date, c.name, c.address
SELECT `no.pedido`, fecha, c.nombre, c.dirección,
   	SUM(i.qty * i.price) COMO `importe pendiente`.
ORDER BY `número de pedido`;

(Q30)
FROM pedidos COMO o, o.artículos COMO i
GROUP BY o.orderno, o.order_date
HAVING SUM(i.cantidad * i.precio) > 10000
SELECT o.orderno AS order_number, o.order_date,
       SUM(i.qty * i.price) COMO ingresos
ORDER BY ingresos DESC;

 (Q31)
FROM pedidos COMO o, o.artículos COMO i
GROUP BY o.orderno, o.order_date
LET ingresos = SUM(i.cantidad * i.precio)
HAVING ingresos > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY ingresos DESC;

(Q32)
FROM clientes COMO c
GROUP BY c.address.zipcode AS zip
SELECT código postal, AVG(c.calificación) COMO `calificación crediticia media
ORDER BY código postal;

(Q33)
FROM clientes AS c
SELECT AVG(c.rating) AS `avg credit rating`;

(Q34)
SELECT ARRAY_AVG(
   (SELECT VALUE c.rating
    FROM clientes AS c) ) AS `avg credit rating`;
    
(Q35)
FROM pedidos AS o
LET ingresos = ARRAY_SUM(
       (FROM o.items AS i
        SELECT VALUE i.cantidad * i.precio) )
WHERE ingresos > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY ingresos DESC;

(Q36)
FROM pedidos AS o
WHERE DATE_PART_STR(o.fecha_pedido, "año") = 2017
AND DATE_PART_STR(o.fecha_pedido, "mes") = 9
SELECT o.orderno, ARRAY_COUNT(o.items) AS line_items
ORDER BY o.orderno;

(Q37)
FROM pedidos COMO o, o.artículos COMO i
WHERE DATE_PART_STR(o.fecha_pedido, "año") = 2017
AND DATE_PART_STR(o.fecha_pedido, "mes") = 9
GROUP BY o.orderno
SELECT o.orderno, COUNT(i) AS line_items
ORDER BY o.orderno;

(Q38a)
FROM clientes AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
SELECT zip, AVG(c.rating) AS `avg credit rating`,
   (FROM g AS gi
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) como "clientes locales
ORDER BY zip;

(Q38b)
FROM clientes AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
SELECT zip, AVG(c.rating) AS `avg credit rating`,
   (FROM g
    SELECT c.custid, c.name
    ORDER BY c.custid) como "clientes locales
ORDER BY código postal;

(Q39)
FROM clientes COMO c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `mejor valoración` = MAX(c.valoración)
SELECT código postal, `mejor valoración`,
   (FROM g AS gi
    WHERE gi.c.puntuación = `mejor puntuación
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) como "mejores clientes
ORDER BY zip;

(Q40)
FROM clientes AS c
GROUP BY c.address.zipcode COMO zip
GROUP AS g
LET `mejor valoración` = MAX(c.valoración),
    `mejores clientes` =
       (FROM g AS gi
        WHERE gi.c.valoración = `mejor valoración`)
        SELECT gi.c.custid, gi.c.name
        ORDER BY gi.c.custid)
SELECT código postal, `mejor valoración`, `mejores clientes`
ORDER BY zip;

(Q41)
FROM clientes AS c
GROUP BY c.address.zipcode COMO zip
GROUP AS g
SELECT código postal,
   ARRAY_AVG((FROM g AS gi SELECT VALUE gi.c.rating))
       AS `avg credit rating`
ORDER BY código postal;

(Q42)
FROM pedidos AS o, o.items AS i
GROUP BY o.fecha_pedido
GROUP AS g
LET ingresos = SUM(i.precio * i.cantidad)
HAVING ingresos > 1000.00
SELECT o.order_date AS `buen día`, ingresos,
   (FROM g AS gi
    WHERE gi.i.precio > 100.00
    SELECT gi.i.itemno, gi.i.price
    ORDER BY gi.i.itemno) AS "artículos caros
ORDER BY o.fecha_pedido;

(Q43)
FROM clientes COMO c, pedidos COMO o, o.artículos COMO i
WHERE c.custid = o.custid
GROUP BY c.custid, c.name
GROUP AS g
SELECT c.custid, c.name,
	(FROM g AS gi
 	 SELECT gi.o.order_date, gi.i.itemno, gi.i.qty
 	 ORDER BY gi.o.order_date, gi.i.itemno) AS recent_items
ORDER BY c.custid;

(Q44)
FROM clientes AS c LEFT OUTER JOIN
   /* Esta subconsulta desglosa los artículos de cada pedido,
  	 devuelve un array de resultados llamado sq */
   (FROM pedidos COMO o, o.artículos COMO i
    SELECT o.custid, o.order_date, i.itemno, i.qty) COMO sq
ON c.custid = sq.custid
GROUP BY c.custid, c.name
GROUP AS g
SELECT c.custid, c.name,
      (FROM g AS gi
       SELECT gi.sq.fecha_pedido, gi.sq.articulo, gi.sq.cantidad
       ORDER BY gi.sq.fecha_pedido, gi.sq.articulo)
       AS recent_items
ORDER BY c.custid;

(Q45)
FROM pedidos COMO o, o.artículos COMO i
GROUP BY i.itemno
GROUP AS g
LET total_on_order = SUM(i.qty)
SELECT i.itemno, total_on_order,
    (FROM g AS gi
     SELECT gi.o.fecha_pedido, gi.o.custid
     ORDER BY gi.o.fecha_pedido) AS purchasers
ORDER BY i.itemno;

 (Q46)
SELECT DISTINCT VALUE i.itemno
FROM pedidos COMO o, o.artículos COMO i
WHERE o.custid IN
    (SELECT VALUE c.custid
     FROM clientes AS c
     WHERE c.name = "T. Cruise");
   
(Q47)
FROM clientes COMO c
GROUP BY c.address.zipcode AS zip
SELECT código postal, ROUND(AVG(c.rating)) AS avg_rating
ORDER BY código postal;
   
(Q48)
SELECT VALUE { "Calificación crediticia media por código postal" :
   (FROM clientes AS c
    WHERE c.address.zipcode ES CONOCIDO
    GROUP BY c.address.zipcode AS zip
    SELECT VALUE { código postal : ROUND(AVG(c.rating)) }
    ORDER BY zip
   )
};

(Q49)
FROM clientes AS c
WHERE c.address.zipcode = "02115"
SELECT *;

(Q50)
FROM clientes COMO c
WHERE c.dirección.código.postal = "02115"
SELECT c.*;

(Q51)
FROM clientes COMO c, pedidos COMO o
WHERE c.custid = o.custid
AND c.address.zipcode = "02115"
SELECT *;

(Q52)
FROM clientes AS c
WHERE c.address.zipcode = "02115"
SELECT c.*, "Noreste" AS región;

(Q53)
FROM clientes AS c
LET crédito =
    CASE
      WHEN c.rating > 650 THEN "Good" (bueno)
      WHEN c.rating BETWEEN 500 AND 649 THEN "Fair" (aceptable)
      ELSE "Deficiente
    END
WHERE c.dirección.código postal = "02115"
SELECT c.*, crédito;
      
(Q54)
WITH ingreso_pedido AS
  (FROM pedidos AS o, o.artículos AS i
   WHERE DATE_PART_STR(o.fecha_pedido, "año") = 2017
   GROUP BY o.orderno
   SELECT SUM(i.qty * i.price) AS ingresos
  )
FROM ingresos_pedido
SELECT ROUND(MIN(ingresos)) AS minimum,
       ROUND(MAX(revenue)) AS máximo,
       ROUND(AVG(revenue)) COMO media;
   
(Q55)
SELECT c.custid, "Código postal desconocido" AS reason
FROM clientes COMO c
WHERE c.dirección.código postal NO SE CONOCE

UNION ALL

SELECT o.custid, "Pedido grande" AS reason
FROM pedidos AS o
WHERE ARRAY_COUNT(o.items) > 3
ORDER BY motivo;