(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;