Examples and Notes by Week

↑ Up to Parent directory
View create_mebesr_committees.sql code.	Open/Save create_mebesr_committees.sql.
View cte.sql code.	Open/Save cte.sql.
View customer_payments.sql code.	Open/Save customer_payments.sql.
View customers_with_no_sales.sql code.	Open/Save customers_with_no_sales.sql.
View derived1.sql code.	Open/Save derived1.sql.
View dervied2.sql code.	Open/Save dervied2.sql.
View fullName.sql code.	Open/Save fullName.sql.
View join.sql code.	Open/Save join.sql.
View join_four.sql code.	Open/Save join_four.sql.
View join_three.sql code.	Open/Save join_three.sql.
View left_join.sql code.	Open/Save left_join.sql.
View order_over_60000.sql code.	Open/Save order_over_60000.sql.
View order_totals.sql code.	Open/Save order_totals.sql.
View right_join.sql code.	Open/Save right_join.sql.
View right_rewritten_as_left.sql code.	Open/Save right_rewritten_as_left.sql.
View subquery1.sql code.	Open/Save subquery1.sql.
View subquery2.sql code.	Open/Save subquery2.sql.
View subquery_corrilated.sql code.	Open/Save subquery_corrilated.sql.
View subquery_from.sql code.	Open/Save subquery_from.sql.
View total_customer_orders.sql code.	Open/Save total_customer_orders.sql.

The code for dervied2.sql is:
SELECT customerGroup, COUNT(cg.customerGroup) AS groupCount FROM (SELECT customerNumber, ROUND(SUM(quantityOrdered * priceEach)) sales, (CASE WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver' WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold' WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum' END) customerGroup FROM orderdetails INNER JOIN orders USING (orderNumber) WHERE YEAR(shippedDate) = 2003 GROUP BY customerNumber) cg GROUP BY cg.customerGroup;