Start Demo 1 SELECT e.name emp_name 2 , e.salary salary 3 FROM employee e 4* ORDER BY e.salary Employee Salary -------------------- --------- Thomas Walton $25,000 Eric Kraus $27,000 Larry Wilton $30,000 Allison Dietrich $30,000 Shelly Walton $30,000 Henry Parry $30,000 Billy Yolto $30,000 Bev George $30,000 William Dietrich $30,000 Bobby Harris $30,000 Oscar Perry $45,000 Stan Marsh $45,000 John Dennis $46,000 Vincent Johns $50,000 Doug Harris $50,000 Erika Deeter $60,000 Andy Schmidt $70,000 Julie Johnson $75,000 Tina Walton $80,000 Allison Ballinger $90,000 Rachel Middleton $850,000 Note - Default sort order is ASCending Sorting by DESCending order 1 SELECT e.name emp_name 2 , e.salary salary 3 FROM employee e 4* ORDER BY e.salary DESC Employee Salary -------------------- --------- Rachel Middleton $850,000 Allison Ballinger $90,000 Tina Walton $80,000 Julie Johnson $75,000 Andy Schmidt $70,000 Erika Deeter $60,000 Doug Harris $50,000 Vincent Johns $50,000 John Dennis $46,000 Oscar Perry $45,000 Stan Marsh $45,000 Allison Dietrich $30,000 Shelly Walton $30,000 Henry Parry $30,000 Bev George $30,000 William Dietrich $30,000 Billy Yolto $30,000 Bobby Harris $30,000 Larry Wilton $30,000 Eric Kraus $27,000 Thomas Walton $25,000 RANK() 1 SELECT e.name emp_name 2 , e.salary salary 3 , RANK() OVER (ORDER BY salary DESC) rank 4 FROM employee e 5* ORDER BY e.salary DESC Employee Salary Rank -------------------- --------- ----- Rachel Middleton $850,000 1 Allison Ballinger $90,000 2 Tina Walton $80,000 3 Julie Johnson $75,000 4 Andy Schmidt $70,000 5 Erika Deeter $60,000 6 Doug Harris $50,000 7 Vincent Johns $50,000 7 John Dennis $46,000 9 Oscar Perry $45,000 10 Stan Marsh $45,000 10 Allison Dietrich $30,000 12 Shelly Walton $30,000 12 Henry Parry $30,000 12 Bev George $30,000 12 William Dietrich $30,000 12 Billy Yolto $30,000 12 Bobby Harris $30,000 12 Larry Wilton $30,000 12 Eric Kraus $27,000 20 Thomas Walton $25,000 21 Note - RANK() and ties DENSE_RANK() 1 SELECT e.name emp_name 2 , e.salary salary 3 , DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank 4 FROM employee e 5* ORDER BY e.salary DESC Dense Employee Salary Rank -------------------- --------- ----- Rachel Middleton $850,000 1 Allison Ballinger $90,000 2 Tina Walton $80,000 3 Julie Johnson $75,000 4 Andy Schmidt $70,000 5 Erika Deeter $60,000 6 Doug Harris $50,000 7 Vincent Johns $50,000 7 John Dennis $46,000 8 Oscar Perry $45,000 9 Stan Marsh $45,000 9 Allison Dietrich $30,000 10 Shelly Walton $30,000 10 Henry Parry $30,000 10 Bev George $30,000 10 William Dietrich $30,000 10 Billy Yolto $30,000 10 Bobby Harris $30,000 10 Larry Wilton $30,000 10 Eric Kraus $27,000 11 Thomas Walton $25,000 12 Note - DENSE_RANK() and ties RANK(), DENSE_RANK and ROW_NUMBERs 1 SELECT e.name emp_name 2 , e.salary salary 3 , RANK() OVER (ORDER BY salary DESC) rank 4 , DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank 5 , ROW_NUMBER() OVER (ORDER BY salary DESC) row_num 6 FROM employee e 7* ORDER BY e.salary DESC Dense Row Employee Salary Rank Rank Num -------------------- --------- ----- ----- ----- Rachel Middleton $850,000 1 1 1 Allison Ballinger $90,000 2 2 2 Tina Walton $80,000 3 3 3 Julie Johnson $75,000 4 4 4 Andy Schmidt $70,000 5 5 5 Erika Deeter $60,000 6 6 6 Doug Harris $50,000 7 7 7 Vincent Johns $50,000 7 7 8 John Dennis $46,000 9 8 9 Oscar Perry $45,000 10 9 10 Stan Marsh $45,000 10 9 11 Allison Dietrich $30,000 12 10 12 Shelly Walton $30,000 12 10 13 Henry Parry $30,000 12 10 14 Bev George $30,000 12 10 15 William Dietrich $30,000 12 10 16 Billy Yolto $30,000 12 10 17 Bobby Harris $30,000 12 10 18 Larry Wilton $30,000 12 10 19 Eric Kraus $27,000 20 11 20 Thomas Walton $25,000 21 12 21 Return to Presentation No Partition 1 SELECT e.name emp_name 2 , d.name dept_name 3 , e.salary salary 4 , DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank 5 FROM employee e 6 , department d 7 WHERE d.dept_id = e.dept_id 8* ORDER BY d.dept_id, e.salary DESC Dense Employee Department Salary Rank -------------------- -------------------- --------- ----- Rachel Middleton Administration $850,000 1 Allison Ballinger Administration $90,000 2 William Dietrich Administration $30,000 10 Julie Johnson Sales and Marketing $75,000 4 Andy Schmidt Sales and Marketing $70,000 5 Oscar Perry Sales and Marketing $45,000 9 Larry Wilton Sales and Marketing $30,000 10 Shelly Walton Sales and Marketing $30,000 10 Allison Dietrich Sales and Marketing $30,000 10 Henry Parry Sales and Marketing $30,000 10 Bobby Harris Sales and Marketing $30,000 10 Bev George Sales and Marketing $30,000 10 Erika Deeter Accounting $60,000 6 John Dennis Accounting $46,000 8 Stan Marsh Accounting $45,000 9 Tina Walton Logistics and Supply $80,000 3 Vincent Johns Logistics and Supply $50,000 7 Doug Harris Logistics and Supply $50,000 7 Billy Yolto Logistics and Supply $30,000 10 Eric Kraus Logistics and Supply $27,000 11 Thomas Walton Logistics and Supply $25,000 12 Partition by Department 1 SELECT e.name emp_name 2 , d.name dept_name 3 , e.salary salary 4 , DENSE_RANK() OVER 5 (PARTITION BY d.name 6 ORDER BY salary DESC) dense_rank 7 FROM employee e 8 , department d 9 WHERE d.dept_id = e.dept_id 10* ORDER BY d.dept_id, e.salary DESC Dense Employee Department Salary Rank -------------------- -------------------- --------- ----- Rachel Middleton Administration $850,000 1 Allison Ballinger Administration $90,000 2 William Dietrich Administration $30,000 3 Julie Johnson Sales and Marketing $75,000 1 Andy Schmidt Sales and Marketing $70,000 2 Oscar Perry Sales and Marketing $45,000 3 Bev George Sales and Marketing $30,000 4 Larry Wilton Sales and Marketing $30,000 4 Henry Parry Sales and Marketing $30,000 4 Allison Dietrich Sales and Marketing $30,000 4 Shelly Walton Sales and Marketing $30,000 4 Bobby Harris Sales and Marketing $30,000 4 Erika Deeter Accounting $60,000 1 John Dennis Accounting $46,000 2 Stan Marsh Accounting $45,000 3 Tina Walton Logistics and Supply $80,000 1 Doug Harris Logistics and Supply $50,000 2 Vincent Johns Logistics and Supply $50,000 2 Billy Yolto Logistics and Supply $30,000 3 Eric Kraus Logistics and Supply $27,000 4 Thomas Walton Logistics and Supply $25,000 5 Partition by Department and Job Title 1 SELECT e.name emp_name 2 , d.name dept_name 3 , e.salary salary 4 , DENSE_RANK() OVER 5 (PARTITION BY d.name 6 ORDER BY salary DESC) dense_rank 7 , e.job_title job_title 8 , DENSE_RANK() OVER 9 (PARTITION BY e.job_title 10 ORDER BY salary DESC) dense_rank 11 FROM employee e 12 , department d 13 WHERE d.dept_id = e.dept_id 14* ORDER BY d.dept_id, e.salary DESC Dense Dense Employee Department Salary Rank Job Rank -------------------- -------------------- --------- ----- ------------------------- ----- Rachel Middleton Administration $850,000 1 IT Director 1 Allison Ballinger Administration $90,000 2 CEO 1 William Dietrich Administration $30,000 3 Administrative Assistant 1 Julie Johnson Sales and Marketing $75,000 1 Sales Manager 1 Andy Schmidt Sales and Marketing $70,000 2 Marketing Director 1 Oscar Perry Sales and Marketing $45,000 3 Sales Clerk 1 Shelly Walton Sales and Marketing $30,000 4 Sales 1 Allison Dietrich Sales and Marketing $30,000 4 Sales 1 Larry Wilton Sales and Marketing $30,000 4 Sales 1 Henry Parry Sales and Marketing $30,000 4 Sales 1 Bobby Harris Sales and Marketing $30,000 4 Sales 1 Bev George Sales and Marketing $30,000 4 Sales 1 Erika Deeter Accounting $60,000 1 Accountant 1 John Dennis Accounting $46,000 2 Accounting Clerk 1 Stan Marsh Accounting $45,000 3 Accounting Clerk 2 Tina Walton Logistics and Supply $80,000 1 COO 1 Vincent Johns Logistics and Supply $50,000 2 Warehouse Manager 1 Doug Harris Logistics and Supply $50,000 2 Shipping Supervisor 1 Billy Yolto Logistics and Supply $30,000 3 Receiving Clerk 1 Eric Kraus Logistics and Supply $27,000 4 Shipping Clerk 1 Thomas Walton Logistics and Supply $25,000 5 Shipping Clerk 2 Composite Partition 1 SELECT name emp_name 2 , type_description product_type_desc 3 , order_month 4 , month_type_sales month_sales 5 , RANK() OVER 6 (PARTITION BY type_description 7 ORDER BY month_type_sales DESC) rank 8 , RANK() OVER 9 (PARTITION BY order_month 10 ORDER BY month_type_sales DESC) rank 11 , RANK() OVER 12 (PARTITION BY order_month, type_description 13 ORDER BY month_type_sales DESC) rank 14 , RANK() OVER 15 (PARTITION BY type_description, order_month 16 ORDER BY month_type_sales DESC) rank 17 FROM monthly_sales 18 WHERE TO_CHAR(order_month, 'YYYYMM') IN ('200812', '200811') 19 AND type_description IN ('Boots', 'Tent') 20* ORDER BY type_description, order_month, month_type_sales DESC Employee Category Month Month Sales Rank Rank Rank Rank -------------------- -------------------- ---------- ------------- ----- ----- ----- ----- Bev George Boots 01-NOV-08 $26,189,769 1 1 1 1 Allison Dietrich Boots 01-NOV-08 $25,995,707 2 2 2 2 Henry Parry Boots 01-NOV-08 $25,980,015 3 3 3 3 Bobby Harris Boots 01-NOV-08 $25,813,698 4 4 4 4 Larry Wilton Boots 01-NOV-08 $25,645,383 5 5 5 5 Shelly Walton Boots 01-NOV-08 $25,372,580 6 6 6 6 Larry Wilton Boots 01-DEC-08 $16,100,212 7 1 1 1 Bev George Boots 01-DEC-08 $15,967,436 8 2 2 2 Shelly Walton Boots 01-DEC-08 $15,900,037 9 3 3 3 Henry Parry Boots 01-DEC-08 $15,809,999 10 4 4 4 Bobby Harris Boots 01-DEC-08 $15,433,250 11 5 5 5 Allison Dietrich Boots 01-DEC-08 $15,315,079 12 6 6 6 Bobby Harris Tent 01-NOV-08 $7,476,891 1 7 1 1 Henry Parry Tent 01-NOV-08 $7,376,087 2 8 2 2 Larry Wilton Tent 01-NOV-08 $7,286,804 3 9 3 3 Allison Dietrich Tent 01-NOV-08 $7,279,021 4 10 4 4 Bev George Tent 01-NOV-08 $7,204,995 5 11 5 5 Shelly Walton Tent 01-NOV-08 $7,153,379 6 12 6 6 Larry Wilton Tent 01-DEC-08 $4,512,045 7 7 1 1 Shelly Walton Tent 01-DEC-08 $4,475,716 8 8 2 2 Henry Parry Tent 01-DEC-08 $4,454,412 9 9 3 3 Allison Dietrich Tent 01-DEC-08 $4,430,946 10 10 4 4 Bev George Tent 01-DEC-08 $4,419,658 11 11 5 5 Bobby Harris Tent 01-DEC-08 $4,253,373 12 12 6 6 Return to Presentation Sort by Analytical Function Column 1 SELECT e.name emp_name 2 , d.name dept_name 3 , e.salary salary 4 , DENSE_RANK() OVER 5 (PARTITION BY d.name 6 ORDER BY salary DESC) sal_rank 7 FROM employee e 8 , department d 9 WHERE d.dept_id = e.dept_id 10* ORDER BY d.dept_id, sal_rank Employee Department Salary Rank -------------------- -------------------- --------- ----- Rachel Middleton Administration $850,000 1 Allison Ballinger Administration $90,000 2 William Dietrich Administration $30,000 3 Julie Johnson Sales and Marketing $75,000 1 Andy Schmidt Sales and Marketing $70,000 2 Oscar Perry Sales and Marketing $45,000 3 Bev George Sales and Marketing $30,000 4 Larry Wilton Sales and Marketing $30,000 4 Henry Parry Sales and Marketing $30,000 4 Allison Dietrich Sales and Marketing $30,000 4 Shelly Walton Sales and Marketing $30,000 4 Bobby Harris Sales and Marketing $30,000 4 Erika Deeter Accounting $60,000 1 John Dennis Accounting $46,000 2 Stan Marsh Accounting $45,000 3 Tina Walton Logistics and Supply $80,000 1 Doug Harris Logistics and Supply $50,000 2 Vincent Johns Logistics and Supply $50,000 2 Billy Yolto Logistics and Supply $30,000 3 Eric Kraus Logistics and Supply $27,000 4 Thomas Walton Logistics and Supply $25,000 5 Sort by Analytical Function 1 SELECT e.name emp_name 2 , d.name dept_name 3 , e.salary salary 4 FROM employee e 5 , department d 6 WHERE d.dept_id = e.dept_id 7 ORDER BY d.dept_id 8 , DENSE_RANK() OVER 9 (PARTITION BY d.name 10* ORDER BY salary DESC) Employee Department Salary -------------------- -------------------- --------- Rachel Middleton Administration $850,000 Allison Ballinger Administration $90,000 William Dietrich Administration $30,000 Julie Johnson Sales and Marketing $75,000 Andy Schmidt Sales and Marketing $70,000 Oscar Perry Sales and Marketing $45,000 Bev George Sales and Marketing $30,000 Larry Wilton Sales and Marketing $30,000 Henry Parry Sales and Marketing $30,000 Allison Dietrich Sales and Marketing $30,000 Shelly Walton Sales and Marketing $30,000 Bobby Harris Sales and Marketing $30,000 Erika Deeter Accounting $60,000 John Dennis Accounting $46,000 Stan Marsh Accounting $45,000 Tina Walton Logistics and Supply $80,000 Doug Harris Logistics and Supply $50,000 Vincent Johns Logistics and Supply $50,000 Billy Yolto Logistics and Supply $30,000 Eric Kraus Logistics and Supply $27,000 Thomas Walton Logistics and Supply $25,000 Return to Presentation Aggregation 1 SELECT d.name dept_name 2 , SUM(e.salary) dept_sal 3 FROM employee e 4 , department d 5 WHERE d.dept_id = e.dept_id 6 GROUP BY d.name 7* ORDER BY dept_sal DESC Department Salary -------------------- --------- Administration $970,000 Sales and Marketing $370,000 Logistics and Supply $262,000 Accounting $151,000 Analytical Function Aggregation 1 SELECT d.name dept_name 2 , SUM(e.salary) dept_sal 3 , RANK() OVER (ORDER BY SUM(e.salary) DESC) AS rank 4 FROM employee e 5 , department d 6 WHERE d.dept_id = e.dept_id 7 GROUP BY d.name 8* ORDER BY dept_sal DESC Department Salary Rank -------------------- --------- ----- Administration $970,000 1 Sales and Marketing $370,000 2 Logistics and Supply $262,000 3 Accounting $151,000 4 Monthly Sales Differential for Allison Dietrich #1 - Raw Data 1 SELECT order_month 2 , sales_month month_sales 3 FROM monthly_sales_person 4 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 5 AND name = 'Allison Dietrich' 6* ORDER BY order_month Month Month Sales ---------- ------------- 01-JAN-07 $68,758,209 01-FEB-07 $58,800,431 01-MAR-07 $52,176,691 01-APR-07 $53,277,171 01-MAY-07 $58,060,187 01-JUN-07 $61,552,921 01-JUL-07 $54,592,592 01-AUG-07 $67,768,354 01-SEP-07 $51,576,097 01-OCT-07 $51,955,799 01-NOV-07 $53,710,486 01-DEC-07 $62,757,245 Monthly Sales Differential for Allison Dietrich #2 - Raw Data + LAG 1 SELECT order_month 2 , sales_month month_sales 3 , LAG(sales_month) OVER 4 (ORDER BY order_month) 5 prev_month_sales 6 FROM monthly_sales_person 7 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 8 AND name = 'Allison Dietrich' 9* ORDER BY order_month Month Month Sales Prev Sales ---------- ------------- ------------- 01-JAN-07 $68,758,209 01-FEB-07 $58,800,431 $68,758,209 01-MAR-07 $52,176,691 $58,800,431 01-APR-07 $53,277,171 $52,176,691 01-MAY-07 $58,060,187 $53,277,171 01-JUN-07 $61,552,921 $58,060,187 01-JUL-07 $54,592,592 $61,552,921 01-AUG-07 $67,768,354 $54,592,592 01-SEP-07 $51,576,097 $67,768,354 01-OCT-07 $51,955,799 $51,576,097 01-NOV-07 $53,710,486 $51,955,799 01-DEC-07 $62,757,245 $53,710,486 Monthly Sales Differential for Allison Dietrich #3 - Raw Data + LAG + Delta 1 SELECT order_month 2 , sales_month month_sales 3 , LAG(sales_month) OVER 4 (ORDER BY order_month) 5 prev_month_sales 6 , sales_month - LAG(sales_month) OVER 7 (ORDER BY order_month) 8 diff_month_sales 9 FROM monthly_sales_person 10 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 11 AND name = 'Allison Dietrich' 12* ORDER BY order_month Month Month Sales Prev Sales Sales Delta ---------- ------------- ------------- ------------- 01-JAN-07 $68,758,209 01-FEB-07 $58,800,431 $68,758,209 -$9,957,778 01-MAR-07 $52,176,691 $58,800,431 -$6,623,740 01-APR-07 $53,277,171 $52,176,691 $1,100,481 01-MAY-07 $58,060,187 $53,277,171 $4,783,015 01-JUN-07 $61,552,921 $58,060,187 $3,492,734 01-JUL-07 $54,592,592 $61,552,921 -$6,960,328 01-AUG-07 $67,768,354 $54,592,592 $13,175,762 01-SEP-07 $51,576,097 $67,768,354 -$16,192,258 01-OCT-07 $51,955,799 $51,576,097 $379,703 01-NOV-07 $53,710,486 $51,955,799 $1,754,686 01-DEC-07 $62,757,245 $53,710,486 $9,046,759 Monthly Sales Differential for Allison Dietrich #4 - Raw Data + LAG + Delta + % 1 SELECT order_month 2 , sales_month month_sales 3 , LAG(sales_month) OVER 4 (ORDER BY order_month) 5 prev_month_sales 6 , sales_month - LAG(sales_month) OVER 7 (ORDER BY order_month) 8 diff_month_sales 9 , ((sales_month / LAG(sales_month) OVER 10 (ORDER BY order_month)) - 1) * 100 pct_diff 11 FROM monthly_sales_person 12 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 13 AND name = 'Allison Dietrich' 14* ORDER BY order_month Month Month Sales Prev Sales Sales Delta % Delta ---------- ------------- ------------- ------------- ------- 01-JAN-07 $68,758,209 01-FEB-07 $58,800,431 $68,758,209 -$9,957,778 -14.48 01-MAR-07 $52,176,691 $58,800,431 -$6,623,740 -11.26 01-APR-07 $53,277,171 $52,176,691 $1,100,481 2.11 01-MAY-07 $58,060,187 $53,277,171 $4,783,015 8.98 01-JUN-07 $61,552,921 $58,060,187 $3,492,734 6.02 01-JUL-07 $54,592,592 $61,552,921 -$6,960,328 -11.31 01-AUG-07 $67,768,354 $54,592,592 $13,175,762 24.13 01-SEP-07 $51,576,097 $67,768,354 -$16,192,258 -23.89 01-OCT-07 $51,955,799 $51,576,097 $379,703 .74 01-NOV-07 $53,710,486 $51,955,799 $1,754,686 3.38 01-DEC-07 $62,757,245 $53,710,486 $9,046,759 16.84 Monthly Sales Differential for Allison Dietrich #5 - Add monthly range 1 SELECT order_month 2 , LAG(order_month) OVER 3 (ORDER BY order_month) 4 prev_order_month 5 , sales_month month_sales 6 , LAG(sales_month) OVER 7 (ORDER BY order_month) 8 prev_month_sales 9 , sales_month - LAG(sales_month) OVER 10 (ORDER BY order_month) 11 diff_month_sales 12 , ((sales_month / LAG(sales_month) OVER 13 (ORDER BY order_month)) - 1) * 100 pct_diff 14 FROM monthly_sales_person 15 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 16 AND name = 'Allison Dietrich' 17* ORDER BY order_month Month Month Month Sales Prev Sales Sales Delta % Delta ---------- ---------- ------------- ------------- ------------- ------- 01-JAN-07 $68,758,209 01-FEB-07 01-JAN-07 $58,800,431 $68,758,209 -$9,957,778 -14.48 01-MAR-07 01-FEB-07 $52,176,691 $58,800,431 -$6,623,740 -11.26 01-APR-07 01-MAR-07 $53,277,171 $52,176,691 $1,100,481 2.11 01-MAY-07 01-APR-07 $58,060,187 $53,277,171 $4,783,015 8.98 01-JUN-07 01-MAY-07 $61,552,921 $58,060,187 $3,492,734 6.02 01-JUL-07 01-JUN-07 $54,592,592 $61,552,921 -$6,960,328 -11.31 01-AUG-07 01-JUL-07 $67,768,354 $54,592,592 $13,175,762 24.13 01-SEP-07 01-AUG-07 $51,576,097 $67,768,354 -$16,192,258 -23.89 01-OCT-07 01-SEP-07 $51,955,799 $51,576,097 $379,703 .74 01-NOV-07 01-OCT-07 $53,710,486 $51,955,799 $1,754,686 3.38 01-DEC-07 01-NOV-07 $62,757,245 $53,710,486 $9,046,759 16.84 OOPS! Wrong range... Monthly Sales Differential for Allison Dietrich #6 - Correct monthly range...almost 1 SELECT order_month 2 , ( LEAD(order_month) OVER 3 (ORDER BY order_month) - 1 ) 4 prev_order_month 5 , sales_month month_sales 6 , LAG(sales_month) OVER 7 (ORDER BY order_month) 8 prev_month_sales 9 , sales_month - LAG(sales_month) OVER 10 (ORDER BY order_month) 11 diff_month_sales 12 , ((sales_month / LAG(sales_month) OVER 13 (ORDER BY order_month)) - 1) * 100 pct_diff 14 FROM monthly_sales_person 15 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 16 AND name = 'Allison Dietrich' 17* ORDER BY order_month Month Month Month Sales Prev Sales Sales Delta % Delta ---------- ---------- ------------- ------------- ------------- ------- 01-JAN-07 31-JAN-07 $68,758,209 01-FEB-07 28-FEB-07 $58,800,431 $68,758,209 -$9,957,778 -14.48 01-MAR-07 31-MAR-07 $52,176,691 $58,800,431 -$6,623,740 -11.26 01-APR-07 30-APR-07 $53,277,171 $52,176,691 $1,100,481 2.11 01-MAY-07 31-MAY-07 $58,060,187 $53,277,171 $4,783,015 8.98 01-JUN-07 30-JUN-07 $61,552,921 $58,060,187 $3,492,734 6.02 01-JUL-07 31-JUL-07 $54,592,592 $61,552,921 -$6,960,328 -11.31 01-AUG-07 31-AUG-07 $67,768,354 $54,592,592 $13,175,762 24.13 01-SEP-07 30-SEP-07 $51,576,097 $67,768,354 -$16,192,258 -23.89 01-OCT-07 31-OCT-07 $51,955,799 $51,576,097 $379,703 .74 01-NOV-07 30-NOV-07 $53,710,486 $51,955,799 $1,754,686 3.38 01-DEC-07 $62,757,245 $53,710,486 $9,046,759 16.84 Missing DECEMBER Monthly Sales Differential for Allison Dietrich #7 - Use DEFAULT clause to avoid NULL in month end 1 SELECT order_month 2 , ( LEAD(order_month,1,ADD_MONTHS(order_month,1)) OVER 3 (ORDER BY order_month) - 1 ) 4 prev_order_month 5 , sales_month month_sales 6 , LAG(sales_month) OVER 7 (ORDER BY order_month) 8 prev_month_sales 9 , sales_month - LAG(sales_month) OVER 10 (ORDER BY order_month) 11 diff_month_sales 12 , ((sales_month / LAG(sales_month) OVER 13 (ORDER BY order_month)) - 1) * 100 pct_diff 14 FROM monthly_sales_person 15 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 16 AND name = 'Allison Dietrich' 17* ORDER BY order_month Month Month Month Sales Prev Sales Sales Delta % Delta ---------- ---------- ------------- ------------- ------------- ------- 01-JAN-07 31-JAN-07 $68,758,209 01-FEB-07 28-FEB-07 $58,800,431 $68,758,209 -$9,957,778 -14.48 01-MAR-07 31-MAR-07 $52,176,691 $58,800,431 -$6,623,740 -11.26 01-APR-07 30-APR-07 $53,277,171 $52,176,691 $1,100,481 2.11 01-MAY-07 31-MAY-07 $58,060,187 $53,277,171 $4,783,015 8.98 01-JUN-07 30-JUN-07 $61,552,921 $58,060,187 $3,492,734 6.02 01-JUL-07 31-JUL-07 $54,592,592 $61,552,921 -$6,960,328 -11.31 01-AUG-07 31-AUG-07 $67,768,354 $54,592,592 $13,175,762 24.13 01-SEP-07 30-SEP-07 $51,576,097 $67,768,354 -$16,192,258 -23.89 01-OCT-07 31-OCT-07 $51,955,799 $51,576,097 $379,703 .74 01-NOV-07 30-NOV-07 $53,710,486 $51,955,799 $1,754,686 3.38 01-DEC-07 31-DEC-07 $62,757,245 $53,710,486 $9,046,759 16.84 Monthly Sales Differential for Allison Dietrich and Henry Parry #1 - Modify current query 1 SELECT name emp_name 2 , order_month 3 , ( LEAD(order_month,1,ADD_MONTHS(order_month,1)) OVER 4 (ORDER BY order_month) - 1 ) prev_order_month 5 , sales_month month_sales 6 , LAG(sales_month) OVER 7 (ORDER BY order_month) prev_month_sales 8 , sales_month - LAG(sales_month) OVER 9 (ORDER BY order_month) diff_month_sales 10 , ((sales_month / LAG(sales_month) OVER 11 (ORDER BY order_month)) - 1) * 100 pct_diff 12 FROM monthly_sales_person 13 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 14 AND name IN ('Allison Dietrich', 'Henry Parry') 15* ORDER BY emp_name, order_month Employee Month Month Month Sales Prev Sales Sales Delta % Delta -------------------- ---------- ---------- ------------- ------------- ------------- ------- Allison Dietrich 01-JAN-07 31-DEC-06 $68,758,209 Allison Dietrich 01-FEB-07 28-FEB-07 $58,800,431 $58,939,875 -$139,445 -.24 Allison Dietrich 01-MAR-07 31-MAR-07 $52,176,691 $52,641,681 -$464,990 -.88 Allison Dietrich 01-APR-07 31-MAR-07 $53,277,171 $52,176,691 $1,100,481 2.11 Allison Dietrich 01-MAY-07 30-APR-07 $58,060,187 $54,955,352 $3,104,835 5.65 Allison Dietrich 01-JUN-07 30-JUN-07 $61,552,921 $62,457,620 -$904,700 -1.45 Allison Dietrich 01-JUL-07 31-JUL-07 $54,592,592 $54,180,696 $411,896 .76 Allison Dietrich 01-AUG-07 31-JUL-07 $67,768,354 $54,592,592 $13,175,762 24.13 Allison Dietrich 01-SEP-07 30-SEP-07 $51,576,097 $50,065,549 $1,510,548 3.02 Allison Dietrich 01-OCT-07 30-SEP-07 $51,955,799 $51,576,097 $379,703 .74 Allison Dietrich 01-NOV-07 30-NOV-07 $53,710,486 $53,499,020 $211,466 .40 Allison Dietrich 01-DEC-07 30-NOV-07 $62,757,245 $53,710,486 $9,046,759 16.84 Henry Parry 01-JAN-07 31-JAN-07 $68,466,199 $68,758,209 -$292,009 -.42 Henry Parry 01-FEB-07 31-JAN-07 $58,939,875 $68,466,199 -$9,526,324 -13.91 Henry Parry 01-MAR-07 28-FEB-07 $52,641,681 $58,800,431 -$6,158,750 -10.47 Henry Parry 01-APR-07 30-APR-07 $54,955,352 $53,277,171 $1,678,181 3.15 Henry Parry 01-MAY-07 31-MAY-07 $58,040,399 $58,060,187 -$19,788 -.03 Henry Parry 01-JUN-07 31-MAY-07 $62,457,620 $58,040,399 $4,417,222 7.61 Henry Parry 01-JUL-07 30-JUN-07 $54,180,696 $61,552,921 -$7,372,224 -11.98 Henry Parry 01-AUG-07 31-AUG-07 $68,401,132 $67,768,354 $632,777 .93 Henry Parry 01-SEP-07 31-AUG-07 $50,065,549 $68,401,132 -$18,335,583 -26.81 Henry Parry 01-OCT-07 31-OCT-07 $50,607,818 $51,955,799 -$1,347,981 -2.59 Henry Parry 01-NOV-07 31-OCT-07 $53,499,020 $50,607,818 $2,891,202 5.71 Henry Parry 01-DEC-07 31-DEC-07 $64,245,690 $62,757,245 $1,488,445 2.37 Henry's January 2007 numbers are wrong Monthly Sales Differential for Allison Dietrich and Henry Parry #2 - Set up proper partition 1 SELECT name emp_name 2 , order_month 3 , ( LEAD(order_month,1,ADD_MONTHS(order_month,1)) OVER 4 (PARTITION BY name ORDER BY order_month) - 1 ) prev_order_month 5 , sales_month month_sales 6 , LAG(sales_month) OVER 7 (PARTITION BY name ORDER BY order_month) prev_month_sales 8 , sales_month - LAG(sales_month) OVER 9 (PARTITION BY name ORDER BY order_month) diff_month_sales 10 , ((sales_month / LAG(sales_month) OVER 11 (PARTITION BY name ORDER BY order_month)) - 1) * 100 pct_diff 12 FROM monthly_sales_person 13 WHERE TO_CHAR(order_month, 'YYYY') = '2007' 14 AND name IN ('Allison Dietrich', 'Henry Parry') 15* ORDER BY emp_name, order_month Employee Month Month Month Sales Prev Sales Sales Delta % Delta -------------------- ---------- ---------- ------------- ------------- ------------- ------- Allison Dietrich 01-JAN-07 31-JAN-07 $68,758,209 Allison Dietrich 01-FEB-07 28-FEB-07 $58,800,431 $68,758,209 -$9,957,778 -14.48 Allison Dietrich 01-MAR-07 31-MAR-07 $52,176,691 $58,800,431 -$6,623,740 -11.26 Allison Dietrich 01-APR-07 30-APR-07 $53,277,171 $52,176,691 $1,100,481 2.11 Allison Dietrich 01-MAY-07 31-MAY-07 $58,060,187 $53,277,171 $4,783,015 8.98 Allison Dietrich 01-JUN-07 30-JUN-07 $61,552,921 $58,060,187 $3,492,734 6.02 Allison Dietrich 01-JUL-07 31-JUL-07 $54,592,592 $61,552,921 -$6,960,328 -11.31 Allison Dietrich 01-AUG-07 31-AUG-07 $67,768,354 $54,592,592 $13,175,762 24.13 Allison Dietrich 01-SEP-07 30-SEP-07 $51,576,097 $67,768,354 -$16,192,258 -23.89 Allison Dietrich 01-OCT-07 31-OCT-07 $51,955,799 $51,576,097 $379,703 .74 Allison Dietrich 01-NOV-07 30-NOV-07 $53,710,486 $51,955,799 $1,754,686 3.38 Allison Dietrich 01-DEC-07 31-DEC-07 $62,757,245 $53,710,486 $9,046,759 16.84 Henry Parry 01-JAN-07 31-JAN-07 $68,466,199 Henry Parry 01-FEB-07 28-FEB-07 $58,939,875 $68,466,199 -$9,526,324 -13.91 Henry Parry 01-MAR-07 31-MAR-07 $52,641,681 $58,939,875 -$6,298,194 -10.69 Henry Parry 01-APR-07 30-APR-07 $54,955,352 $52,641,681 $2,313,671 4.40 Henry Parry 01-MAY-07 31-MAY-07 $58,040,399 $54,955,352 $3,085,047 5.61 Henry Parry 01-JUN-07 30-JUN-07 $62,457,620 $58,040,399 $4,417,222 7.61 Henry Parry 01-JUL-07 31-JUL-07 $54,180,696 $62,457,620 -$8,276,924 -13.25 Henry Parry 01-AUG-07 31-AUG-07 $68,401,132 $54,180,696 $14,220,435 26.25 Henry Parry 01-SEP-07 30-SEP-07 $50,065,549 $68,401,132 -$18,335,583 -26.81 Henry Parry 01-OCT-07 31-OCT-07 $50,607,818 $50,065,549 $542,269 1.08 Henry Parry 01-NOV-07 30-NOV-07 $53,499,020 $50,607,818 $2,891,202 5.71 Henry Parry 01-DEC-07 31-DEC-07 $64,245,690 $53,499,020 $10,746,670 20.09 Henry's January 2007 numbers are correct Monthly and Yearly Sales Differential for Allison Dietrich 1 SELECT TO_CHAR(order_month, 'Mon-YY') order_mon 2 , sales_month month_sales 3 , LAG(sales_month) OVER 4 (ORDER BY order_month) prev_month_sales 5 , sales_month - LAG(sales_month) OVER 6 (ORDER BY order_month) diff_month_sales 7 , ((sales_month / LAG(sales_month) OVER 8 (ORDER BY order_month)) - 1) * 100 pct_diff 9 , LAG(sales_month,12) OVER 10 (ORDER BY order_month) prev_month_sales 11 , sales_month - LAG(sales_month,12) OVER 12 (ORDER BY order_month) diff_month_sales 13 , ((sales_month / LAG(sales_month,12) OVER 14 (ORDER BY order_month)) - 1) * 100 pct_diff 15 FROM monthly_sales_person 16 WHERE TO_CHAR(order_month, 'YYYY') IN ('2007', '2006') 17 AND name = 'Allison Dietrich' 18* ORDER BY order_month Month Month Sales Prev Sales Sales Delta % Delta Prev Sales Sales Delta % Delta ------ ------------- ------------- ------------- ------- ------------- ------------- ------- Jan-06 $59,950,940 Feb-06 $57,219,605 $59,950,940 -$2,731,335 -4.56 Mar-06 $55,442,979 $57,219,605 -$1,776,626 -3.10 Apr-06 $67,192,209 $55,442,979 $11,749,231 21.19 May-06 $59,088,672 $67,192,209 -$8,103,537 -12.06 Jun-06 $63,305,120 $59,088,672 $4,216,448 7.14 Jul-06 $57,533,274 $63,305,120 -$5,771,846 -9.12 Aug-06 $61,247,322 $57,533,274 $3,714,048 6.46 Sep-06 $61,167,494 $61,247,322 -$79,828 -.13 Oct-06 $72,895,937 $61,167,494 $11,728,443 19.17 Nov-06 $64,106,805 $72,895,937 -$8,789,132 -12.06 Dec-06 $53,930,157 $64,106,805 -$10,176,648 -15.87 Jan-07 $68,758,209 $53,930,157 $14,828,052 27.49 $59,950,940 $8,807,268 14.69 Feb-07 $58,800,431 $68,758,209 -$9,957,778 -14.48 $57,219,605 $1,580,825 2.76 Mar-07 $52,176,691 $58,800,431 -$6,623,740 -11.26 $55,442,979 -$3,266,288 -5.89 Apr-07 $53,277,171 $52,176,691 $1,100,481 2.11 $67,192,209 -$13,915,038 -20.71 May-07 $58,060,187 $53,277,171 $4,783,015 8.98 $59,088,672 -$1,028,485 -1.74 Jun-07 $61,552,921 $58,060,187 $3,492,734 6.02 $63,305,120 -$1,752,199 -2.77 Jul-07 $54,592,592 $61,552,921 -$6,960,328 -11.31 $57,533,274 -$2,940,681 -5.11 Aug-07 $67,768,354 $54,592,592 $13,175,762 24.13 $61,247,322 $6,521,032 10.65 Sep-07 $51,576,097 $67,768,354 -$16,192,258 -23.89 $61,167,494 -$9,591,397 -15.68 Oct-07 $51,955,799 $51,576,097 $379,703 .74 $72,895,937 -$20,940,138 -28.73 Nov-07 $53,710,486 $51,955,799 $1,754,686 3.38 $64,106,805 -$10,396,319 -16.22 Dec-07 $62,757,245 $53,710,486 $9,046,759 16.84 $53,930,157 $8,827,088 16.37 Return to Presentation 2007 Monthly sales with year to date 1 SELECT name emp_name 2 , order_month 3 , sales_month month_sales 4 , SUM(sales_month) OVER 5 (ORDER BY order_month 6 ROWS UNBOUNDED PRECEDING ) ytd_sales 7 FROM monthly_sales_person 8 WHERE order_month BETWEEN '01-JAN-07' AND '01-JAN-08' 9 AND name = 'Allison Dietrich' 10* ORDER BY order_month Employee Month Month Sales Year To Date -------------------- ---------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $68,758,209 Allison Dietrich 01-FEB-07 $58,800,431 $127,558,639 Allison Dietrich 01-MAR-07 $52,176,691 $179,735,330 Allison Dietrich 01-APR-07 $53,277,171 $233,012,501 Allison Dietrich 01-MAY-07 $58,060,187 $291,072,688 Allison Dietrich 01-JUN-07 $61,552,921 $352,625,609 Allison Dietrich 01-JUL-07 $54,592,592 $407,218,201 Allison Dietrich 01-AUG-07 $67,768,354 $474,986,555 Allison Dietrich 01-SEP-07 $51,576,097 $526,562,652 Allison Dietrich 01-OCT-07 $51,955,799 $578,518,452 Allison Dietrich 01-NOV-07 $53,710,486 $632,228,937 Allison Dietrich 01-DEC-07 $62,757,245 $694,986,182 Allison Dietrich 01-JAN-08 $44,197,286 $739,183,468 2007 Monthly sales with Sliding 3 month average 1 SELECT name emp_name 2 , order_month 3 , sales_month month_sales 4 , AVG(sales_month) OVER 5 (ORDER BY order_month 6 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) avg_sales 7 FROM monthly_sales_person 8 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 9 AND name = 'Allison Dietrich' 10* ORDER BY order_month Employee Month Month Sales Average Sales -------------------- ---------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093 Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127 Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177 Allison Dietrich 01-DEC-07 $62,757,245 $58,233,865 Return to Presentation 2007 Monthly sales with year to date 1 SELECT name emp_name 2 , order_month 3 , sales_month month_sales 4 , SUM(sales_month) OVER 5 (ORDER BY name, order_month 6 ROWS UNBOUNDED PRECEDING ) ytd_sales 7 FROM monthly_sales_person 8 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 9 AND name IN ('Allison Dietrich', 'Henry Parry') 10* ORDER BY name, order_month Employee Month Month Sales Year To Date -------------------- ---------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $68,758,209 Allison Dietrich 01-FEB-07 $58,800,431 $127,558,639 Allison Dietrich 01-MAR-07 $52,176,691 $179,735,330 Allison Dietrich 01-APR-07 $53,277,171 $233,012,501 Allison Dietrich 01-MAY-07 $58,060,187 $291,072,688 Allison Dietrich 01-JUN-07 $61,552,921 $352,625,609 Allison Dietrich 01-JUL-07 $54,592,592 $407,218,201 Allison Dietrich 01-AUG-07 $67,768,354 $474,986,555 Allison Dietrich 01-SEP-07 $51,576,097 $526,562,652 Allison Dietrich 01-OCT-07 $51,955,799 $578,518,452 Allison Dietrich 01-NOV-07 $53,710,486 $632,228,937 Allison Dietrich 01-DEC-07 $62,757,245 $694,986,182 Henry Parry 01-JAN-07 $68,466,199 $763,452,381 Henry Parry 01-FEB-07 $58,939,875 $822,392,257 Henry Parry 01-MAR-07 $52,641,681 $875,033,938 Henry Parry 01-APR-07 $54,955,352 $929,989,290 Henry Parry 01-MAY-07 $58,040,399 $988,029,688 Henry Parry 01-JUN-07 $62,457,620 ############# Henry Parry 01-JUL-07 $54,180,696 ############# Henry Parry 01-AUG-07 $68,401,132 ############# Henry Parry 01-SEP-07 $50,065,549 ############# Henry Parry 01-OCT-07 $50,607,818 ############# Henry Parry 01-NOV-07 $53,499,020 ############# Henry Parry 01-DEC-07 $64,245,690 ############# Incorrect results - YTD spans Salesperson 2007 Monthly sales with year to date 1 SELECT name emp_name 2 , order_month 3 , sales_month month_sales 4 , SUM(sales_month) OVER 5 ( PARTITION BY name 6 ORDER BY order_month 7 ROWS UNBOUNDED PRECEDING ) ytd_sales 8 FROM monthly_sales_person 9 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 10 AND name IN ('Allison Dietrich', 'Henry Parry') 11* ORDER BY name, order_month Employee Month Month Sales Year To Date -------------------- ---------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $68,758,209 Allison Dietrich 01-FEB-07 $58,800,431 $127,558,639 Allison Dietrich 01-MAR-07 $52,176,691 $179,735,330 Allison Dietrich 01-APR-07 $53,277,171 $233,012,501 Allison Dietrich 01-MAY-07 $58,060,187 $291,072,688 Allison Dietrich 01-JUN-07 $61,552,921 $352,625,609 Allison Dietrich 01-JUL-07 $54,592,592 $407,218,201 Allison Dietrich 01-AUG-07 $67,768,354 $474,986,555 Allison Dietrich 01-SEP-07 $51,576,097 $526,562,652 Allison Dietrich 01-OCT-07 $51,955,799 $578,518,452 Allison Dietrich 01-NOV-07 $53,710,486 $632,228,937 Allison Dietrich 01-DEC-07 $62,757,245 $694,986,182 Henry Parry 01-JAN-07 $68,466,199 $68,466,199 Henry Parry 01-FEB-07 $58,939,875 $127,406,075 Henry Parry 01-MAR-07 $52,641,681 $180,047,756 Henry Parry 01-APR-07 $54,955,352 $235,003,108 Henry Parry 01-MAY-07 $58,040,399 $293,043,506 Henry Parry 01-JUN-07 $62,457,620 $355,501,127 Henry Parry 01-JUL-07 $54,180,696 $409,681,823 Henry Parry 01-AUG-07 $68,401,132 $478,082,955 Henry Parry 01-SEP-07 $50,065,549 $528,148,504 Henry Parry 01-OCT-07 $50,607,818 $578,756,322 Henry Parry 01-NOV-07 $53,499,020 $632,255,341 Henry Parry 01-DEC-07 $64,245,690 $696,501,031 Return to Presentation 2007 Monthly sales with Sliding 3 month average 1 SELECT name emp_name 2 , order_month 3 , sales_month month_sales 4 , AVG(sales_month) OVER 5 (ORDER BY order_month 6 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) avg_sales 7 , AVG(sales_month) OVER 8 (ORDER BY order_month 9 RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING ) avg_sales 10 FROM monthly_sales_person 11 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 12 AND name = 'Allison Dietrich' 13* ORDER BY order_month Employee Month Month Sales Average Sales Average Sales -------------------- ---------- ------------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093 $57,630,093 Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567 $58,068,567 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127 $52,414,127 Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177 $56,141,177 Allison Dietrich 01-DEC-07 $62,757,245 $58,233,865 $58,233,865 Delete one months of sales 1 DELETE FROM monthly_sales_person 2 WHERE name = 'Allison Dietrich' 3* AND order_month = '01-MAY-07' Return to Presentation #2 - 2007 Monthly sales with Sliding 3 month average 1 SELECT name emp_name 2 , order_month 3 , sales_month month_sales 4 , AVG(sales_month) OVER 5 (ORDER BY order_month 6 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) avg_sales 7 , AVG(sales_month) OVER 8 (ORDER BY order_month 9 RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING ) avg_sales 10 FROM monthly_sales_person 11 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 12 AND name = 'Allison Dietrich' 13* ORDER BY order_month Employee Month Month Sales Average Sales Average Sales -------------------- ---------- ------------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $55,668,928 $52,726,931 Allison Dietrich 01-JUN-07 $61,552,921 $56,474,228 $58,072,756 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127 $52,414,127 Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177 $56,141,177 Allison Dietrich 01-DEC-07 $62,757,245 $58,233,865 $58,233,865 Note that April and June are different Rollback delete so it does not impact the rest of the demo 1* ROLLBACK Return to Presentation Top 3 Salespeople for 2007 1 SELECT name emp_name 2 , SUM(sales_month) year_sales 3 , RANK() OVER (ORDER BY SUM(sales_month) DESC) rank 4 FROM monthly_sales_person 5 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 6 AND RANK() OVER (ORDER BY SUM(sales_month) DESC) >= 3 7 GROUP BY name 8* ORDER BY year_sales DESC AND RANK() OVER (ORDER BY SUM(sales_month) DESC) >= 3 * ERROR at line 6: ORA-30483: window functions are not allowed here Return to Presentation Raw Data - Salespeople for 2007 1 SELECT name emp_name 2 , SUM(sales_month) year_sales 3 , RANK() OVER (ORDER BY SUM(sales_month) DESC) rank 4 FROM monthly_sales_person 5 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 6 GROUP BY name 7* ORDER BY year_sales DESC Employee Year Sales Rank -------------------- ------------- ----- Henry Parry $696,501,031 1 Allison Dietrich $694,986,182 2 Bobby Harris $694,846,271 3 Bev George $694,541,445 4 Shelly Walton $692,968,542 5 Larry Wilton $687,349,575 6 Now construct subquery Salespeople for 2007 1 WITH sales_yearly AS 2 ( SELECT name emp_name 3 , SUM(sales_month) year_sales 4 , RANK() OVER (ORDER BY SUM(sales_month) DESC) rank 5 FROM monthly_sales_person 6 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 7 GROUP BY name 8 ORDER BY year_sales DESC 9 ) 10 SELECT * 11 FROM sales_yearly 12* ORDER BY rank Employee Year Sales Rank -------------------- ------------- ----- Henry Parry $696,501,031 1 Allison Dietrich $694,986,182 2 Bobby Harris $694,846,271 3 Bev George $694,541,445 4 Shelly Walton $692,968,542 5 Larry Wilton $687,349,575 6 Now add Predicate Top 3 Salespeople for 2007 1 WITH sales_yearly AS 2 ( SELECT name emp_name 3 , SUM(sales_month) year_sales 4 , RANK() OVER (ORDER BY SUM(sales_month) DESC) rank 5 FROM monthly_sales_person 6 WHERE order_month BETWEEN '01-JAN-07' AND '01-DEC-07' 7 GROUP BY name 8 ORDER BY year_sales DESC 9 ) 10 SELECT emp_name 11 , year_sales 12 , rank 13 FROM sales_yearly 14 WHERE rank <= 3 15* ORDER BY rank Employee Year Sales Rank -------------------- ------------- ----- Henry Parry $696,501,031 1 Allison Dietrich $694,986,182 2 Bobby Harris $694,846,271 3 Return to Presentation Finding employes with LIKE 1 SELECT name emp_name 2 FROM employee 3 WHERE name LIKE '%Parry%' 4* OR name LIKE '%Perry%' Employee -------------------- Henry Parry Oscar Perry Finding employes with REGEXP_LIKE 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'P(a|e)rry$') Employee -------------------- Henry Parry Oscar Perry Finding employes with 'dd' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'dd') Employee -------------------- Rachel Middleton Finding employes with 2 'd's 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '(d){2}') Employee -------------------- Rachel Middleton Finding employes with 2 'D's 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '(D){2}') no rows selected Finding employes with 2 'D's or 2 'd's 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '(D|d){2}') Employee -------------------- Rachel Middleton Finding employes with 2 'D's or 2 'd's 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '(d){2}','i') Employee -------------------- Rachel Middleton Return to Presentation Find employees with an 'h' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'h') Employee -------------------- Julie Johnson Stan Marsh Thomas Walton Shelly Walton John Dennis William Dietrich Allison Dietrich Andy Schmidt Rachel Middleton Vincent Johns Find employees with an 'h' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '.h.') Employee -------------------- Julie Johnson Thomas Walton Shelly Walton John Dennis Andy Schmidt Rachel Middleton Vincent Johns Notice the names not present Find employees whose name starts with 'h' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '^h') no rows selected No one starts with 'h', so do a case insensitive search 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '^h', 'i') Employee -------------------- Henry Parry Find employees whose name ends with 'h' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'h$', 'i') Employee -------------------- Stan Marsh William Dietrich Allison Dietrich Find employees whose name begins with 'th' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '^(th)', 'i') Employee -------------------- Thomas Walton Find employees whose name begins with 't' or 'h' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, '^(T|H)', 'i') Employee -------------------- Thomas Walton Tina Walton Henry Parry OR operator Find employees whose name has 'll' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'll', 'i') Employee -------------------- Allison Ballinger Shelly Walton William Dietrich Allison Dietrich Billy Yolto 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'l{2}', 'i') Employee -------------------- Allison Ballinger Shelly Walton William Dietrich Allison Dietrich Billy Yolto Find employees whose name has 'll' but not 'lly' 1 SELECT name emp_name 2 FROM employee 3* WHERE REGEXP_LIKE(name, 'l{2}[^y]', 'i') Employee -------------------- Allison Ballinger William Dietrich Allison Dietrich Return to Presentation Find all comments that end in a lower case character 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '[[:lower:]]$') Order Comments ------------------------------------------------------------------------------------- Vendor replacement for cancelled order Vendor needs to confirm Ship with signature required for delivery. Do not leave package by door 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '[a-z]$') Order Comments ------------------------------------------------------------------------------------- Vendor replacement for cancelled order Vendor needs to confirm Ship with signature required for delivery. Do not leave package by door Find all comments that contain a number 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '[[:digit:]]') Order Comments ------------------------------------------------------------------------------------- Must ship within 10 days or they will cancel. If tent is not available, please call (783)555-2327 If shoes do not come in wide, please call 872.555.8730 Call 872 555 8730 for delivery instructions. Gift for friend. Call 3245558728 If size 10 out of stock, please ship size 10.5 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '[0-9]') Order Comments ------------------------------------------------------------------------------------- Must ship within 10 days or they will cancel. If tent is not available, please call (783)555-2327 If shoes do not come in wide, please call 872.555.8730 Call 872 555 8730 for delivery instructions. Gift for friend. Call 3245558728 If size 10 out of stock, please ship size 10.5 Find all comments that contain a new line 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '[[:cntrl:]]') Order Comments ------------------------------------------------------------------------------------- Vendor replacement for cancelled order Vendor needs to confirm Return to Presentation Find all order comments with phone numbers 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '(\(| )[0-9]{3}(\)| |.)[0-9]{3}(-| |.)[0-9]{4}') Order Comments ------------------------------------------------------------------------------------- If tent is not available, please call (783)555-2327 If shoes do not come in wide, please call 872.555.8730 Call 872 555 8730 for delivery instructions. Gift for friend. 1 SELECT comments 2 FROM order_comments 3* WHERE REGEXP_LIKE(comments, '(\(| )?[0-9]{3}(\)| |.)?[0-9]{3}(-| |.)?[0-9]{4}') Order Comments