Session 03

View All Public Scripts Login to Run Script

Script Name Session 03
Visibility Unlisted - anyone with the share link can access
Description Session demonstrating order grouping for just a small number of orders
Contributor agonos@optimum.gr
Created Monday December 09, 2019

Statement 1

Select * from oe.product_information where product_id in (Select distinct product_id from oe.order_items  where product_id< 1900) 
 
Select * from oe.orders where order_id in (Select distinct order_id from oe.order_items  where product_id< 1900) order by order_id

ORA-00933: SQL command not properly ended

More Details: https://docs.oracle.com/error-help/db/ora-00933

Statement 2

with AnaInfo as (Select pi.product_id, o.order_id, oi.quantity   
from (Select * from oe.product_information where product_id in (Select distinct product_id from oe.order_items  where product_id< 1900)) pi   
cross join (Select * from oe.orders where order_id in (Select distinct order_id from oe.order_items  where product_id< 1900)) o    
left outer join oe.order_items oi on (o.order_id = oi.order_id and oi.product_id = pi.product_id))   
Select ai1.order_id, ai2.order_id, count(ai1.Quantity), Count(ai2.quantity), count(ai1.quantity+ ai2.quantity)   
from AnaInfo ai1    
inner join AnaInfo ai2 on (ai1.order_id > ai2.order_id and ai1.product_id= ai2.product_id)   
group by  ai1.order_id, ai2.order_id   
order by 1,2 desc


ORDER_ID ORDER_ID COUNT(AI1.QUANTITY) COUNT(AI2.QUANTITY) COUNT(AI1.QUANTITY+AI2.QUANTITY)
2373 2358 2 5 0
2391 2373 7 2 1
2391 2358 7 5 2
2439 2391 4 7 3
2439 2373 4 2 1
2439 2358 4 5 1

6 rows selected.

ORDER_ID	ORDER_ID	COUNT(AI1.QUANTITY)	COUNT(AI2.QUANTITY)	COUNT(AI1.QUANTITY+AI2.QUANTITY)
2373	2358	2	5	0
2391	2373	7	2	1
2391	2358	7	5	2
2439	2391	4	7	3
2439	2373	4	2	1
2439	2358	4	5	1

Statement 3

Select * from oe.product_information where product_id in (Select distinct product_id from oe.order_items  where product_id< 1900)


PRODUCT_ID PRODUCT_NAME PRODUCT_DESCRIPTION CATEGORY_ID WEIGHT_CLASS WARRANTY_PERIOD SUPPLIER_ID PRODUCT_STATUS LIST_PRICE MIN_PRICE CATALOG_URL
1797 Inkjet C/8/HQ Inkjet printer, color, 8 pages per minute, high resolution (photo quality). Memory: 16MB. Dimensions (HxWxD): 7.3 x 17.5 x 14 inch. Paper size: A4, US Letter, envelopes. Interface: Centronics parallel, IEEE 1284 compliant. 12 3 +02-00 102094 orderable 349 288 http://www.supp-102094.com/cat/hw/p1797.html
1782 Compact 400/DQ 400 characters per second high-speed draft printer. Dimensions (HxWxD): 17.34 x 24.26 x 26.32 inch. Interface: RS-232 serial (9-pin), no expansion slots. Paper size: A4, US Letter. 12 4 +01-06 102088 obsolete 125 108 http://www.supp-102088.com/cat/hw/p1782.html
1791 Industrial 700/HD 700 characters per second dot-matrix printer with harder body and dust protection for industrial uses. Interface: Centronics parallel, IEEE 1284 compliant. Paper size: 3x5 inch to 11x17 inch full bleed wide format. Memory: 4MB. Dimensions (HxWxD): 9.3 x 16.5 x 13 inch. 12 5 +05-00 102086 orderable 275 239 http://www.supp-102086.com/cat/hw/p1791.html
1781 CDW 20/48/E CD Writer, read 48x, write 20x 17 2 +00-09 102060 orderable 233 206 http://www.supp-102060.com/cat/hw/p1781.html
1787 CPU D300 Dual CPU @ 300Mhz. For light personal processing only, or file servers with less than 5 concurrent users. This product will probably become obsolete soon. 15 1 +03-00 102097 orderable 101 90 http://www.supp-102097.com/cat/hw/p1787.html
1799 SPNIX3.3 - SL Operating System Software: SPNIX V3.3 - Base Server License. Includes 10 general licenses for system administration, developers, or users. No network user licensing. 24 1 +01-00 103092 orderable 1000 874 http://www.supp-103092.com/cat/sw/p1799.html
1803 SPNIX3.3 - DL Operating System Software: SPNIX V3.3 - Additional developer license. 24 1 +01-00 103092 orderable 60 51 http://www.supp-103092.com/cat/sw/p1803.html
1806 SPNIX3.3 - UL/C Operating System Software: SPNIX V3.3 - Additional user license class C. 24 1 +01-00 103092 orderable 50 42 http://www.supp-103092.com/cat/sw/p1806.html
1808 SPNIX3.3 - UL/D Operating System Software: SPNIX V3.3 - Additional user license class D. 24 1 +01-00 103092 orderable 55 46 http://www.supp-103092.com/cat/sw/p1808.html
1820 SPNIX3.3 - NL Operating System Software: SPNIX V3.3 - Additional network access license. 24 1 +01-00 103092 orderable 55 45 http://www.supp-103092.com/cat/sw/p1820.html
1822 SPNIX4.0 - SL Operating System Software: SPNIX V4.0 - Base Server License. Includes 10 general licenses for system administration, developers, or users. No network user licensing. 24 1 +01-00 103092 orderable 1500 1303 http://www.supp-103092.com/cat/sw/p1822.html
1825 X25 - 1 Line License X25 network access control system, single user 29 1 +00-06 103093 orderable 25 21 http://www.supp-103093.com/cat/sw/p1825.html

12 rows selected.

PRODUCT_ID	PRODUCT_NAME	PRODUCT_DESCRIPTION	CATEGORY_ID	WEIGHT_CLASS	WARRANTY_PERIOD	SUPPLIER_ID	PRODUCT_STATUS	LIST_PRICE	MIN_PRICE	CATALOG_URL
1797	Inkjet C/8/HQ	Inkjet printer, color, 8 pages per minute, high resolution (photo quality). Memory: 16MB. Dimensions (HxWxD): 7.3 x 17.5 x 14 inch. Paper size: A4, US Letter, envelopes. Interface: Centronics parallel, IEEE 1284 compliant.	12	3	+02-00	102094	orderable	349	288	http://www.supp-102094.com/cat/hw/p1797.html
1782	Compact 400/DQ	400 characters per second high-speed draft printer. Dimensions (HxWxD): 17.34 x 24.26 x 26.32 inch. Interface: RS-232 serial (9-pin), no expansion slots. Paper size: A4, US Letter.	12	4	+01-06	102088	obsolete	125	108	http://www.supp-102088.com/cat/hw/p1782.html
1791	Industrial 700/HD	700 characters per second dot-matrix printer with harder body and dust protection for industrial uses. Interface: Centronics parallel, IEEE 1284 compliant. Paper size: 3x5 inch to 11x17 inch full bleed wide format. Memory: 4MB. Dimensions (HxWxD): 9.3 x 16.5 x 13 inch.	12	5	+05-00	102086	orderable	275	239	http://www.supp-102086.com/cat/hw/p1791.html
1781	CDW 20/48/E	CD Writer, read 48x, write 20x	17	2	+00-09	102060	orderable	233	206	http://www.supp-102060.com/cat/hw/p1781.html
1787	CPU D300	Dual CPU @ 300Mhz. For light personal processing only, or file servers with less than 5 concurrent users. This product will probably become obsolete soon.	15	1	+03-00	102097	orderable	101	90	http://www.supp-102097.com/cat/hw/p1787.html
1799	SPNIX3.3 - SL	Operating System Software: SPNIX V3.3 - Base Server License. Includes 10 general licenses for system administration, developers, or users. No network user licensing.	24	1	+01-00	103092	orderable	1000	874	http://www.supp-103092.com/cat/sw/p1799.html
1803	SPNIX3.3 - DL	Operating System Software: SPNIX V3.3 - Additional developer license.	24	1	+01-00	103092	orderable	60	51	http://www.supp-103092.com/cat/sw/p1803.html
1806	SPNIX3.3 - UL/C	Operating System Software: SPNIX V3.3 - Additional user license class C.	24	1	+01-00	103092	orderable	50	42	http://www.supp-103092.com/cat/sw/p1806.html
1808	SPNIX3.3 - UL/D	Operating System Software: SPNIX V3.3 - Additional user license class D.	24	1	+01-00	103092	orderable	55	46	http://www.supp-103092.com/cat/sw/p1808.html
1820	SPNIX3.3 - NL	Operating System Software: SPNIX V3.3 - Additional network access license.	24	1	+01-00	103092	orderable	55	45	http://www.supp-103092.com/cat/sw/p1820.html
1822	SPNIX4.0 - SL	Operating System Software: SPNIX V4.0 - Base Server License. Includes 10 general licenses for system administration, developers, or users. No network user licensing.	24	1	+01-00	103092	orderable	1500	1303	http://www.supp-103092.com/cat/sw/p1822.html
1825	X25 - 1 Line License	X25 network access control system, single user	29	1	+00-06	103093	orderable	25	21	http://www.supp-103093.com/cat/sw/p1825.html

Statement 4

Select * from oe.orders where order_id in (Select distinct order_id from oe.order_items  where product_id< 1900) order by order_id


ORDER_ID ORDER_DATE ORDER_MODE CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
2358 08-JAN-08 05.03.12.654278 PM direct 105 2 7826 155  - 
2373 27-FEB-08 01.34.51.220065 AM online 120 4 416  -  - 
2391 27-FEB-06 05.03.03.828330 PM direct 153 2 48070.6 156  - 
2439 31-AUG-07 10.19.37.811132 AM direct 105 1 22150.1 159  - 

4 rows selected.

ORDER_ID	ORDER_DATE	ORDER_MODE	CUSTOMER_ID	ORDER_STATUS	ORDER_TOTAL	SALES_REP_ID	PROMOTION_ID
2358	08-JAN-08 05.03.12.654278 PM	direct	105	2	7826	155	-
2373	27-FEB-08 01.34.51.220065 AM	online	120	4	416	-	-
2391	27-FEB-06 05.03.03.828330 PM	direct	153	2	48070.6	156	-
2439	31-AUG-07 10.19.37.811132 AM	direct	105	1	22150.1	159	-

Statement 5

with AnaInfo as (Select pi.product_id, o.order_id, oi.quantity   
from (Select * from oe.product_information where product_id in (Select distinct product_id from oe.order_items  where product_id< 1900)) pi   
cross join (Select * from oe.orders where order_id in (Select distinct order_id from oe.order_items  where product_id< 1900)) o    
left outer join oe.order_items oi on (o.order_id = oi.order_id and oi.product_id = pi.product_id))   
Select ai1.order_id, ai2.order_id, count(ai1.Quantity), Count(ai2.quantity), count(ai1.quantity+ ai2.quantity)   
from AnaInfo ai1    
inner join AnaInfo ai2 on (ai1.order_id > ai2.order_id and ai1.product_id= ai2.product_id)   
group by  ai1.order_id, ai2.order_id   
order by 1,2 desc


ORDER_ID ORDER_ID COUNT(AI1.QUANTITY) COUNT(AI2.QUANTITY) COUNT(AI1.QUANTITY+AI2.QUANTITY)
2373 2358 2 5 0
2391 2373 7 2 1
2391 2358 7 5 2
2439 2391 4 7 3
2439 2373 4 2 1
2439 2358 4 5 1

6 rows selected.

ORDER_ID	ORDER_ID	COUNT(AI1.QUANTITY)	COUNT(AI2.QUANTITY)	COUNT(AI1.QUANTITY+AI2.QUANTITY)
2373	2358	2	5	0
2391	2373	7	2	1
2391	2358	7	5	2
2439	2391	4	7	3
2439	2373	4	2	1
2439	2358	4	5	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database