SQL++ Tutorial for SQL Users

A tutorial by Don Chamberlin
Download Now
Query Example Data
(Q1)
SELECT custid, name, address.zipcode, rating
FROM customers
ORDER BY custid;

(Q2)
SELECT c.name, c.rating	
FROM customers AS c
WHERE c.custid = "C41";

(Q3)
SELECT name
FROM customers
WHERE rating > 650;

(Q4)
SELECT VALUE name
FROM customers
WHERE rating > 650;

(Q5)
SELECT VALUE [name, rating]
FROM customers
WHERE rating > 650;

(Q6)
SELECT VALUE 
  {"high-rated customers, ordered by rating":
      (SELECT c.rating, c.custid, c.name
       FROM customers AS c
       WHERE c.rating > 650
       ORDER BY c.rating DESC),
  "high-rated customers, ordered by zipcode":
      (SELECT c.address.zipcode, c.custid, c.name
       FROM customers AS c
       WHERE c.rating > 650
       ORDER BY c.address.zipcode)
  };

(Q7)
SELECT name
FROM customers
WHERE rating = 
  (SELECT MAX(rating) FROM customers);
  
(Q8)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating = 
   (SELECT MAX(c2.rating)
    FROM customers AS c2);
  
(Q9)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating IN
   (SELECT VALUE MAX(c2.rating) FROM customers AS c2);

(Q10)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating >
   (SELECT VALUE AVG(c2.rating) FROM customers AS c2)[0];

(Q11)
SELECT VALUE c1.name
FROM customers AS c1
WHERE c1.rating = 
   (SELECT VALUE MAX(c2.rating) FROM customers AS c2)[0];

(Q12)
SELECT VALUE c1.name
FROM customers AS c1
WHERE EVERY r IN
   (SELECT VALUE c2.rating FROM customers AS c2)
SATISFIES c1.rating >= r;

(On N1QL, add END after SATISFIES clause)

(Q13)
SELECT VALUE c1.name
FROM customers AS c1
WHERE EVERY r IN
   (SELECT VALUE c2.rating
    FROM customers AS c2
    WHERE c2.rating IS KNOWN)
SATISFIES c1.rating >= r;

(On N1QL, add END after SATISFIES clause)

(Q14)
FROM customers
WHERE address.zipcode = "63101"
SELECT custid AS customer_id, name
ORDER BY customer_id;

(Q15) 
FROM customers AS c 
WHERE c.address.zipcode = "63101"
SELECT c.custid AS customer_id, c.name
ORDER BY customer_id;


 (Q16)
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND o.orderno = 1001
SELECT o.orderno,
       c.name AS customer_name, 
       c.address,
       o.items AS items_ordered;

(Q17)
FROM customers AS c JOIN orders AS o 
  ON c.custid = o.custid
WHERE o.orderno = 1001
SELECT o.orderno, 
       c.name AS customer_name,
       c.address,
       o.items AS items_ordered;

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

(Q19) 
FROM orders AS o, o.items AS 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 orders AS o UNNEST o.items AS 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;
       
(Q21a)
FROM orders AS o, o.items AS i, customers AS 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;  
       
(Q21b) 
FROM orders AS o, customers AS 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.order_date AS date
ORDER BY c.custid, date;   
 
(Q22)
FROM orders AS o
LET days = DATE_DIFF_STR(o.ship_date, o.order_date, "day")
WHERE days > 2
SELECT o.orderno, days - 2 AS days_late
ORDER BY days_late DESC;

(Q23)
FROM orders AS o
GROUP BY o.custid
SELECT o.custid, COUNT(o.orderno) AS `order count`
ORDER BY o.custid;

(Q24)
FROM customers AS c 
     LEFT OUTER JOIN orders AS o ON c.custid = o.custid
GROUP BY c.custid, c.name
SELECT c.custid, c.name, COUNT(o.orderno) AS `order count`
ORDER BY c.custid;

(Q25)
FROM orders AS o
WHERE DATE_PART_STR(o.order_date, "year") = 2017
GROUP BY DATE_PART_STR(o.order_date, "month") AS month
SELECT month, COUNT(*) AS order_count
ORDER BY month;

(Q26)
FROM orders 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 orders AS o, o.items as i
GROUP BY o.orderno
LET revenue = SUM(i.qty * i.price)
HAVING revenue > 1000
SELECT o.orderno, revenue
ORDER BY revenue DESC;

(Q28)
FROM orders AS o
LET revenue = 
   (FROM o.items AS i
   SELECT VALUE SUM(i.qty * i.price))[0]
WHERE revenue > 1000
SELECT o.orderno, revenue
ORDER BY revenue DESC;

(Q29)
FROM orders AS o, o.items AS i, customers AS c
WHERE o.custid = c.custid
GROUP BY o.orderno AS `order no.`, 
         o.order_date AS date, c.name, c.address
SELECT `order no.`, date, c.name, c.address,
   	SUM(i.qty * i.price) AS `amount due`
ORDER BY `order no.`;

(Q30)
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.order_date
HAVING SUM(i.qty * i.price) > 10000
SELECT o.orderno AS order_number, o.order_date, 
       SUM(i.qty * i.price) AS revenue
ORDER BY revenue DESC;

 (Q31)
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.order_date
LET revenue =  SUM(i.qty * i.price) 
HAVING revenue > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY revenue DESC;

(Q32)
FROM customers AS c
GROUP BY c.address.zipcode AS zip
SELECT zip, AVG(c.rating) AS `avg credit rating`
ORDER BY zip;

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

(Q34)
SELECT ARRAY_AVG(
   (SELECT VALUE c.rating
    FROM customers AS c) ) AS `avg credit rating`;
    
(Q35)
FROM orders AS o
LET revenue = ARRAY_SUM(
       (FROM o.items AS i
        SELECT VALUE i.qty * i.price) )
WHERE revenue > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY revenue DESC;

(Q36)
FROM orders AS o
WHERE DATE_PART_STR(o.order_date, "year") = 2017
AND DATE_PART_STR(o.order_date, "month") = 9
SELECT o.orderno, ARRAY_COUNT(o.items) AS line_items
ORDER BY o.orderno; 

(Q37) 
FROM orders AS o, o.items AS i
WHERE DATE_PART_STR(o.order_date, "year") = 2017
AND DATE_PART_STR(o.order_date, "month") = 9
GROUP BY o.orderno
SELECT o.orderno, COUNT(i) AS line_items
ORDER BY o.orderno; 

(Q38a) 
FROM customers 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) AS `local customers`
ORDER BY zip;

(Q38b)
FROM customers 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) AS `local customers`
ORDER BY zip;

(Q39) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `best rating` = MAX(c.rating)
SELECT zip, `best rating`,
   (FROM g AS gi
    WHERE gi.c.rating = `best rating`
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) AS `best customers`
ORDER BY zip;

(Q40) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `best rating` = MAX(c.rating),
    `best customers` =
       (FROM g AS gi
        WHERE gi.c.rating = `best rating`
        SELECT gi.c.custid, gi.c.name
        ORDER BY gi.c.custid)
SELECT zip, `best rating`, `best customers`
ORDER BY zip;

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

(Q42)
FROM orders AS o, o.items AS i
GROUP BY o.order_date
GROUP AS g
LET revenue = SUM(i.price * i.qty)
HAVING revenue > 1000.00
SELECT o.order_date AS `good day`, revenue,
   (FROM g AS gi 
    WHERE gi.i.price > 100.00
    SELECT gi.i.itemno, gi.i.price 
    ORDER BY gi.i.itemno) AS `expensive items`
ORDER BY o.order_date;

(Q43)
FROM customers AS c, orders AS o, o.items AS 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 customers AS c LEFT OUTER JOIN
   /* This subquery unnests the items in each order, 
  	 returning an array of results named sq */
   (FROM orders AS o, o.items AS i
    SELECT o.custid, o.order_date, i.itemno, i.qty) AS 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.order_date, gi.sq.itemno, gi.sq.qty
       ORDER BY gi.sq.order_date, gi.sq.itemno) 
       AS recent_items
ORDER BY c.custid;

(Q45)
FROM orders AS o, o.items AS 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.order_date, gi.o.custid
     ORDER BY gi.o.order_date) AS purchasers
ORDER BY i.itemno;

 (Q46) 
SELECT DISTINCT VALUE i.itemno
FROM orders AS o, o.items AS i
WHERE o.custid IN 
    (SELECT VALUE c.custid 
     FROM customers AS c 
     WHERE c.name = "T. Cruise");
   
(Q47) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
SELECT zip, ROUND(AVG(c.rating)) AS avg_rating
ORDER BY zip;
   
(Q48)
SELECT VALUE { "Average credit rating by zipcode" :
   (FROM customers AS c 
    WHERE c.address.zipcode IS KNOWN
    GROUP BY c.address.zipcode AS zip
    SELECT VALUE { zip : ROUND(AVG(c.rating)) }
    ORDER BY zip 
   )
};

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

(Q50)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT c.*;

(Q51)
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND c.address.zipcode = "02115"
SELECT *;

(Q52)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT c.*, "Northeast" AS region;

(Q53)
FROM customers AS c
LET credit =
    CASE 
      WHEN c.rating > 650 THEN "Good"
      WHEN c.rating BETWEEN 500 AND 649 THEN "Fair"
      ELSE "Poor" 
    END
WHERE c.address.zipcode = "02115"
SELECT c.*, credit; 
      
(Q54) 
WITH order_revenue AS
  (FROM orders AS o, o.items AS i
   WHERE DATE_PART_STR(o.order_date, "year") = 2017 
   GROUP BY o.orderno
   SELECT SUM(i.qty * i.price) AS revenue
  )
FROM order_revenue
SELECT ROUND(MIN(revenue)) AS minimum,
       ROUND(MAX(revenue)) AS maximum,
       ROUND(AVG(revenue)) AS average;
   
(Q55) 
SELECT c.custid, "Unknown zipcode" AS reason
FROM customers AS c
WHERE c.address.zipcode IS NOT KNOWN

UNION ALL

SELECT o.custid, "Big order" AS reason
FROM orders AS o
WHERE ARRAY_COUNT(o.items) > 3
ORDER BY reason;
<