1.
SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN (SELECT *
FROM employee_expense
WHERE emp_id = 7782) ee
ON m.year = ee.year AND m.month = ee.month
ORDER BY m.month;
2.
SELECT NVL(ee.emp_id, 7369), m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN (SELECT *
FROM employee_expense
WHERE emp_id = 7369) ee
ON m.year = ee.year AND m.month = ee.month
ORDER BY m.month
UNION ALL
SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN (SELECT *
FROM employee_expense
WHERE emp_id = 7782) ee
ON m.year = ee.year AND m.month = ee.month
ORDER BY m.month;
3.
SELECT supplier_id, name
FROM supplier s
WHERE EXISTS (SELECT *
FROM part p
WHERE p.inventory_qty < 10
AND p.supplier_id = s.supplier_id);
4.SELECT STDDEV_POP(DISTINCT sale_price)
FROM cust_order;
5.SELECT SUBSTR(lname,1,1), COUNT(*)
FROM employee
GROUP BY SUBSTR(lname,1,1);
6.SELECT manager_emp_id || job_id, COUNT(*)
FROM employee
GROUP BY manager_emp_id || job_id;
7.SELECT ROWNUM, cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY ROWNUM, cust_nbr;
8.
SELECT ROWNUM, v.*
FROM (SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order GROUP BY cust_nbr) v;
9.
SELECT sale_price, COUNT(order_nbr)
FROM cust_order
GROUP BY sale_price;
10.SELECT sale_price, COUNT(order_nbr)
FROM cust_order
GROUP BY sale_price
ORDER BY sale_price DESC;
11.
SELECT sale_price, COUNT(order_nbr)
FROM cust_order
GROUP BY sale_price
ORDER BY sale_price NULLS FIRST;
12.SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr
HAVING COUNT(order_nbr) > 2;
13.SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
HAVING COUNT(order_nbr) > 2
GROUP BY cust_nbr;
14.SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
WHERE sale_price > 25
GROUP BY cust_nbr
HAVING COUNT(order_nbr) > 1;
15SELECT MAX(sal) FROM
(SELECT dept_id, SUM(salary) sal
FROM employee
GROUP BY dept_id);
16.SELECT MAX(SUM(salary))
FROM employee
GROUP BY dept_id;
17.SELECT MIN(SUM(salary)), AVG(SUM(salary))
FROM employee
GROUP BY dept_id;
18.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total,
GROUPING(o.year) y, GROUPING(o.month) m, GROUPING(r.name) r,
GROUPING_ID (o.year, o.month, r.name) gid
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name);
YEAR MONTH REGION TOTAL Y M R GID
---- --------- -------------- ---------- --- ---- --- ------
2000 January Mid-Atlantic 1221394 0 0 0 0
2000 January New England 1018430 0 0 0 0
2000 January Southeast US 758042 0 0 0 0
2000 January 2997866 0 0 1 1
2000 February Mid-Atlantic 857352 0 0 0 0
2000 February New England 1231492 0 0 0 0
2000 February Southeast US 1236846 0 0 0 0
2000 February 3325690 0 0 1 1
2000 March Mid-Atlantic 1274062 0 0 0 0
2000 March New England 1132966 0 0 0 0
2000 March Southeast US 1311986 0 0 0 0
2000 March 3719014 0 0 1 1
2000 Mid-Atlantic 3352808 0 1 0 2
2000 New England 3382888 0 1 0 2
2000 Southeast US 3306874 0 1 0 2
2000 10042570 0 1 1 3
2001 January Mid-Atlantic 610697 0 0 0 0
2001 January New England 509215 0 0 0 0
2001 January Southeast US 379021 0 0 0 0
2001 January 1498933 0 0 1 1
2001 February Mid-Atlantic 428676 0 0 0 0
2001 February New England 615746 0 0 0 0
2001 February Southeast US 618423 0 0 0 0
2001 February 1662845 0 0 1 1
2001 March Mid-Atlantic 637031 0 0 0 0
2001 March New England 566483 0 0 0 0
2001 March Southeast US 655993 0 0 0 0
2001 March 1859507 0 0 1 1
2001 Mid-Atlantic 1676404 0 1 0 2
2001 New England 1691444 0 1 0 2
2001 Southeast US 1653437 0 1 0 2
2001 5021285 0 1 1 3
19.SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name);
20.SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY GROUPING SETS (o.year, o.month), GROUPING SETS (r.name);
21.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY GROUPING SETS (o.year, o.month), GROUPING SETS (o.year, r. name);
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
1: 2000 10042570
2: 2001 5021285
3: 2000 January 2997866
4: 2000 February 3325690
5: 2000 March 3719014
6: 2001 January 1498933
22.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY GROUPING SETS (ROLLUP (o.year),
ROLLUP (o.month),
ROLLUP (r. name));
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
Mid-Atlantic 5029212
New England 5074332
Southeast US 4960311
January 4496799
February 4988535
23.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total,
GROUPING(o.year) y, GROUPING(o.month) m, GROUPING(r.name) r,
GROUPING_ID (o.year, o.month, r.name) gid
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name);
YEAR MONTH REGION TOTAL Y M R GID
---- --------- -------------- ---------- --- ---- --- ------
2000 January Mid-Atlantic 1221394 0 0 0 0
2000 January New England 1018430 0 0 0 0
2000 January Southeast US 758042 0 0 0 0
2000 January 2997866 0 0 1 1
2000 February Mid-Atlantic 857352 0 0 0 0
2000 February New England 1231492 0 0 0 0
2000 February Southeast US 1236846 0 0 0 0
2000 February 3325690 0 0 1 1
2000 March Mid-Atlantic 1274062 0 0 0 0
2000 March New England 1132966 0 0 0 0
2000 March Southeast US 1311986 0 0 0 0
2000 March 3719014 0 0 1 1
2000 Mid-Atlantic 3352808 0 1 0 2
2000 New England 3382888 0 1 0 2
2000 Southeast US 3306874 0 1 0 2
2000 10042570 0 1 1 3
2001 January Mid-Atlantic 610697 0 0 0 0
2001 January New England 509215 0 0 0 0
2001 January Southeast US 379021 0 0 0 0
2001 January 1498933 0 0 1 1
2001 February Mid-Atlantic 428676 0 0 0 0
2001 February New England 615746 0 0 0 0
2001 February Southeast US 618423 0 0 0 0
2001 February 1662845 0 0 1 1
2001 March Mid-Atlantic 637031 0 0 0 0
2001 March New England 566483 0 0 0 0
2001 March Southeast US 655993 0 0 0 0
2001 March 1859507 0 0 1 1
2001 Mid-Atlantic 1676404 0 1 0 2
24.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name)
HAVING GROUPING_ID (o.year, o.month, r.name) > 0;
YEAR MONTH REGION TOTAL
--------- --------- -------------------- ----------
15063855
New England 5074332
Mid-Atlantic 5029212
Southeast US 4960311
January 4496799
January New England 1527645
January Mid-Atlantic 1832091
January Southeast US 1137063
February 4988535
February New England 1847238
February Mid-Atlantic 1286028
February Southeast US 1855269
March 5578521
March New England 1699449
March Mid-Atlantic 1911093
March Southeast US 1967979
2000 10042570
2000 New England 3382888
2000 Mid-Atlantic 3352808
2000 Southeast US 3306874
2000 January 2997866
2000 February 3325690
2000 March 3719014
2001 5021285
2001 New England 1691444
2001 Mid-Atlantic 1676404
2001 Southeast US 1653437
25.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name)
HAVING GROUPING(o.year) > 0
OR GROUPING(o.month) > 0
OR GROUPING(r.name) > 0;
YEAR MONTH REGION TOTAL
------- --------- -------------------- ----------
15063855
New England 5074332
Mid-Atlantic 5029212
Southeast US 4960311
January 4496799
January New England 1527645
January Mid-Atlantic 1832091
January Southeast US 1137063
February 4988535
February New England 1847238
February Mid-Atlantic 1286028
February Southeast US 1855269
26.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name)
HAVING GROUPING(o.year) > 0
OR GROUPING(o.month) > 0
OR GROUPING(r.name) > 0;
YEAR MONTH REGION TOTAL
------- --------- -------------------- ----------
15063855
New England 5074332
Mid-Atlantic 5029212
Southeast US 4960311
January 4496799
January New England 1527645
January Mid-Atlantic 1832091
January Southeast US 1137063
February 4988535
February New England 1847238
February Mid-Atlantic 1286028
February Southeast US 1855269
27.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY CUBE (o.year, o.month, r.name)
HAVING GROUPING(o.year) > 0
OR GROUPING(o.month) > 0
OR GROUPING(r.name) > 0;
YEAR MONTH REGION TOTAL
------- --------- -------------------- ----------
15063855
New England 5074332
Mid-Atlantic 5029212
Southeast US 4960311
January 4496799
January New England 1527645
January Mid-Atlantic 1832091
January Southeast US 1137063
February 4988535
February New England 1847238
February Mid-Atlantic 1286028
February Southeast US 1855269
28.
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) total
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY o.year, ROLLUP (o.year, o.month, r.name)
HAVING GROUP_ID( ) = 0;
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 January New England 1018430
2000 January Mid-Atlantic 1221394
2000 January Southeast US 758042
2000 January 2997866
29.
SELECT cust_sales.cust_nbr cust_nbr, cust_sales.region_id region_id,
cust_sales.tot_sales cust_sales, region_sales.tot_sales region_sales
FROM
(SELECT o.region_id region_id, SUM(o.tot_sales) tot_sales
FROM orders o
WHERE o.year = 2001
GROUP BY o.region_id) region_sales INNER JOIN
(SELECT o.cust_nbr cust_nbr, o.region_id region_id,
SUM(o.tot_sales) tot_sales
FROM orders o
WHERE o.year = 2001
GROUP BY o.cust_nbr, o.region_id) cust_sales
ON cust_sales.region_id = region_sales.region_id
WHERE cust_sales.tot_sales > (region_sales.tot_sales * .2);
CUST_NBR REGION_ID CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
4 5 1878275 6585641
6 6 1788836 6307766
14 7 1929774 6868495
17 8 1944281 6854731
20 8 1413722 6854731
25 9 2232703 6739374
30.
SELECT c.name cust_name,
big_custs.cust_sales cust_sales, r.name region_name,
100 * ROUND(big_custs.cust_sales /
big_custs.region_sales, 2) percent_of_region
FROM
(SELECT cust_sales.cust_nbr cust_nbr, cust_sales.region_id region_id,
cust_sales.tot_sales cust_sales,
region_sales.tot_sales region_sales
FROM
(SELECT o.region_id region_id, SUM(o.tot_sales) tot_sales
FROM orders o
WHERE o.year = 2001
GROUP BY o.region_id) region_sales INNER JOIN
(SELECT o.cust_nbr cust_nbr, o.region_id region_id,
SUM(o.tot_sales) tot_sales
FROM orders o
WHERE o.year = 2001
GROUP BY o.cust_nbr, o.region_id) cust_sales
ON cust_sales.region_id = region_sales.region_id
WHERE cust_sales.tot_sales > (region_sales.tot_sales * .2)) big_custs INNER JOIN
customer c
ON big_custs.cust_nbr = c.cust_nbr
INNER JOIN region r
ON big_custs.region_id = r.region_id;
CUST_NAME CUST_SALES REGION_NAME PERCENT_OF_REGION
---------------------- ---------- -------------------- -----------------
Flowtech Inc. 1878275 New England 29
Spartan Industries 1788836 Mid-Atlantic 28
Madden Industries 1929774 Southeast US 28
Evans Supply Corp. 1944281 Southwest US 28
Malden Labs 1413722 Southwest US 21
Worcester Technologies 2232703 Northwest US 33
Alpha Technologies 1808949 Central US 29
Phillips Labs 1322747 Central US 21
31.
SELECT o.region_id region_id, o.cust_nbr cust_nbr,
SUM(o.tot_sales) tot_sales,
SUM(SUM(o.tot_sales)) OVER (PARTITION BY o.region_id) region_sales
FROM orders o
WHERE o.year = 2001
GROUP BY o.region_id, o.cust_nbr;
REGION_ID CUST_NBR TOT_SALES REGION_SALES
---------- ---------- ---------- ------------
5 1 1151162 6585641
5 2 1224992 6585641
5 3 1161286 6585641
5 4 1878275 6585641
5 5 1169926 6585641
6 6 1788836 6307766
6 7 971585 6307766
6 8 1141638 6307766
6 9 1208959 6307766
6 10 1196748 6307766
7 11 1190421 6868495
7 12 1182275 6868495
7 13 1310434 6868495
7 14 1929774 6868495
7 15 1255591 6868495
8 16 1068467 6854731
8 17 1944281 6854731
8 18 1253840 6854731
8 19 1174421 6854731
8 20 1413722 6854731
9 21 1020541 6739374
9 22 1036146 6739374
9 23 1224992 6739374
9 24 1224992 6739374
9 25 2232703 6739374
10 26 1808949 6238901
10 27 1322747 6238901
10 28 986964 6238901
10 29 903383 6238901
10 30 1216858 6238901
32.
SELECT c.name cust_name,
cust_sales.tot_sales cust_sales, r.name region_name,
100 * ROUND(cust_sales.tot_sales /
cust_sales.region_sales, 2) percent_of_region
FROM
(SELECT o.region_id region_id, o.cust_nbr cust_nbr,
SUM(o.tot_sales) tot_sales,
SUM(SUM(o.tot_sales)) OVER (PARTITION BY o.region_id) region_sales
FROM orders o
WHERE o.year = 2001
GROUP BY o.region_id, o.cust_nbr) cust_sales INNER JOIN region r
ON cust_sales.region_id = r.region_id
INNER JOIN customer c
ON cust_sales.cust_nbr = c.cust_nbr
WHERE cust_sales.tot_sales > (cust_sales.region_sales * .2);
CUST_NAME CUST_SALES REGION_NAME PERCENT_OF_REGION
---------------------- ---------- -------------------- -----------------
Flowtech Inc. 1878275 New England 29
Spartan Industries 1788836 Mid-Atlantic 28
Madden Industries 1929774 Southeast US 28
Evans Supply Corp. 1944281 Southwest US 28
Malden Labs 1413722 Southwest US 21
Worcester Technologies 2232703 Northwest US 33
Alpha Technologies 1808949 Central US 29
Phillips Labs 1322747 Central US 21
33.
SELECT region_id, cust_nbr,
SUM(tot_sales) cust_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank,
DENSE_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_dense_rank,
ROW_NUMBER( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr
ORDER BY sales_number;
REGION_ID CUST_NBR CUST_SALES SALES_RANK SALES_DENSE_RANK SALES_NUMBER
---------- ---------- ---------- ---------- ---------------- ------------
9 25 2232703 1 1 1
8 17 1944281 2 2 2
7 14 1929774 3 3 3
5 4 1878275 4 4 4
10 26 1808949 5 5 5
6 6 1788836 6 6 6
8 20 1413722 7 7 7
10 27 1322747 8 8 8
7 13 1310434 9 9 9
7 15 1255591 10 10 10
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15
6 9 1208959 16 14 16
6 10 1196748 17 15 17
7 11 1190421 18 16 18
7 12 1182275 19 17 19
8 19 1174421 20 18 20
5 5 1169926 21 19 21
5 3 1161286 22 20 22
5 1 1151162 23 21 23
6 8 1141638 24 22 24
8 16 1068467 25 23 25
34.
SELECT region_id, cust_nbr,
SUM(tot_sales) cust_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank,
DENSE_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_dense_rank,
ROW_NUMBER( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr
ORDER BY sales_number;
REGION_ID CUST_NBR CUST_SALES SALES_RANK SALES_DENSE_RANK SALES_NUMBER
---------- ---------- ---------- ---------- ---------------- ------------
9 25 2232703 1 1 1
8 17 1944281 2 2 2
7 14 1929774 3 3 3
5 4 1878275 4 4 4
10 26 1808949 5 5 5
6 6 1788836 6 6 6
8 20 1413722 7 7 7
10 27 1322747 8 8 8
7 13 1310434 9 9 9
7 15 1255591 10 10 10
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15
6 9 1208959 16 14 16
6 10 1196748 17 15 17
7 11 1190421 18 16 18
7 12 1182275 19 17 19
8 19 1174421 20 18 20
5 5 1169926 21 19 21
5 3 1161286 22 20 22
5 1 1151162 23 21 23
6 8 1141638 24 22 24
8 16 1068467 25 23 25
35.
SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC NULLS LAST) sales_rank
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr;
36.
SELECT s.name, sp.sp_sales total_sales
FROM
(SELECT salesperson_id, SUM(tot_sales) sp_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank
FROM orders
WHERE year = 2001
GROUP BY salesperson_id) sp INNER JOIN salesperson s
ON sp.salesperson_id = s.salesperson_id
WHERE sp.sales_rank <= 5
ORDER BY sp.sales_rank;
NAME TOTAL_SALES
------------------------------------------------- -----------
Jeff Blake 1927580
Sam Houseman 1814327
Mark Russell 1784596
John Boorman 1768813
Carl Isaacs 1761814
Tim McGowan 1761814
37.
SELECT
MIN(region_id)
KEEP (DENSE_RANK FIRST ORDER BY SUM(tot_sales) DESC) best_region,
MIN(region_id)
KEEP (DENSE_RANK LAST ORDER BY SUM(tot_sales) DESC) worst_region
FROM orders
WHERE year = 2001
GROUP BY region_id;
BEST_REGION WORST_REGION
----------- ------------
7 10
38.
SELECT
MIN(region_id)
KEEP (DENSE_RANK FIRST ORDER BY SUM(tot_sales) DESC) min_best_region,
MAX(region_id)
KEEP (DENSE_RANK FIRST ORDER BY SUM(tot_sales) DESC) max_best_region,
MIN(region_id)
KEEP (DENSE_RANK LAST ORDER BY SUM(tot_sales) DESC) min_worst_region,
MAX(region_id)
KEEP (DENSE_RANK LAST ORDER BY SUM(tot_sales) DESC) max_worst_region
FROM orders
WHERE year = 2001
GROUP BY region_id;
MIN_BEST_REGION MAX_BEST_REGION MIN_WORST_REGION MAX_WORST_REGION
--------------- --------------- ---------------- ----------------
7 7 10 10
39.
SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,
NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr
ORDER BY sales_quartile, cust_sales DESC;
REGION_ID CUST_NBR CUST_SALES SALES_QUARTILE
---------- ---------- ---------- --------------
9 25 2232703 1
8 17 1944281 1
7 14 1929774 1
5 4 1878275 1
10 26 1808949 1
6 6 1788836 1
40.
SELECT r.name region, c.name customer, cs.cust_sales
FROM
(SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,
NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr) cs INNER JOIN customer c
ON cs.cust_nbr = c.cust_nbr
INNER JOIN region r
ON cs.region_id = r.region_id
WHERE cs.sales_quartile = 1
ORDER BY cs.cust_sales DESC;
REGION CUSTOMER CUST_SALES
-------------------- ------------------------------ ----------
Northwest US Worcester Technologies 2232703
Southwest US Evans Supply Corp. 1944281
Southeast US Madden Industries 1929774
New England Flowtech Inc. 1878275
Central US Alpha Technologies 1808949
Mid-Atlantic Spartan Industries 1788836
Southwest US Malden Labs 1413722
Central US Phillips Labs 1322747
41.
SELECT region_id, cust_nbr,
SUM(tot_sales) cust_sales,
WIDTH_BUCKET(SUM(tot_sales), 1, 3000000, 3) sales_buckets
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr
ORDER BY cust_sales;
REGION_ID CUST_NBR CUST_SALES SALES_BUCKETS
---------- ---------- ---------- -------------
10 29 903383 1
6 7 971585 1
10 28 986964 1
9 21 1020541 2
9 22 1036146 2
8 16 1068467 2
6 8 1141638 2
5 1 1151162 2
5 3 1161286 2
5 5 1169926 2
8 19 1174421 2
7 12 1182275 2
7 11 1190421 2
6 10 1196748 2
6 9 1208959 2
10 30 1216858 2
5 2 1224992 2
9 24 1224992 2
9 23 1224992 2
8 18 1253840 2
7 15 1255591 2
7 13 1310434 2
42。
SELECT region_id, cust_nbr,
SUM(tot_sales) cust_sales,
CUME_DIST( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_cume_dist,
PERCENT_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_percent_rank
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr
ORDER BY cust_sales DESC;
REGION_ID CUST_NBR CUST_SALES SALES_CUME_DIST SALES_PERCENT_RANK
---------- ---------- ---------- --------------- ------------------
9 25 2232703 .033333333 0
8 17 1944281 .066666667 .034482759
7 14 1929774 .1 .068965517
5 4 1878275 .133333333 .103448276
10 26 1808949 .166666667 .137931034
6 6 1788836 .2 .172413793
8 20 1413722 .233333333 .206896552
10 27 1322747 .266666667 .24137931
7 13 1310434 .3 .275862069
7 15 1255591 .333333333 .310344828
8 18 1253840 .366666667 .344827586
5 2 1224992 .466666667 .379310345
9 23 1224992 .466666667 .379310345
43.
SELECT cust_nbr, SUM(tot_sales) cust_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) rank,
DENSE_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) dense_rank,
CUME_DIST( ) OVER (ORDER BY SUM(tot_sales) DESC) cume_dist,
PERCENT_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) percent_rank
FROM orders
WHERE year = 2001
GROUP BY cust_nbr
ORDER BY rank;
CUST_NBR CUST_SALES RANK DENSE_RANK CUME_DIST PERCENT_RANK
---------- ---------- ---------- ---------- ---------- ------------
25 2232703 1 1 .033333333 0
17 1944281 2 2 .066666667 .034482759
14 1929774 3 3 .1 .068965517
4 1878275 4 4 .133333333 .103448276
26 1808949 5 5 .166666667 .137931034
6 1788836 6 6 .2 .172413793
20 1413722 7 7 .233333333 .206896552
27 1322747 8 8 .266666667 .24137931
13 1310434 9 9 .3 .275862069
15 1255591 10 10 .333333333 .310344828
18 1253840 11 11 .366666667 .344827586
2 1224992 12 12 .466666667 .379310345
23 1224992 12 12 .466666667 .379310345
24 1224992 12 12 .466666667 .379310345
30 1216858 15 13 .5 .482758621
9 1208959 16 14 .533333333 .517241379
10 1196748 17 15 .566666667 .551724138
11 1190421 18 16 .6 .586206897
12 1182275 19 17 .633333333 .620689655
19 1174421 20 18 .666666667 .655172414
5 1169926 21 19 .7 .689655172
3 1161286 22 20 .733333333 .724137931
1 1151162 23 21 .766666667 .75862069
8 1141638 24 22 .8 .793103448
16 1068467 25 23 .833333333 .827586207
22 1036146 26 24 .866666667 .862068966
21 1020541 27 25 .9 .896551724
28 986964 28 26 .933333333 .931034483
7 971585 29 27 .966666667 .965517241
29 903383 30 28 1 1
44.
SELECT month,
SUM(tot_sales) monthly_sales
FROM orders
WHERE year = 2001
AND region_id = 6
GROUP BY month
ORDER BY month;
MONTH MONTHLY_SALES
---------- -------------
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
9 392898
10 510117
11 532889
12 492458