1 / 77

Week 6 October 3

Week 6 October 3. SQL: Joins, Set Operations, Aggregation Functions, Subqueries ANY/ALL and EXIST/NOT EXIST, Table Insert, Delete and Drop Oracle: Format Models, Substring, Truncation and Modulus Functions . Using DISTINCT. SELECT DISTINCT(manfuacturer_code) FROM products

Audrey
Download Presentation

Week 6 October 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Week 6October 3 SQL: Joins, Set Operations, Aggregation Functions, Subqueries ANY/ALL and EXIST/NOT EXIST,Table Insert, Delete and Drop Oracle: Format Models, Substring, Truncation and Modulus Functions

  2. Using DISTINCT SELECT DISTINCT(manfuacturer_code) FROM products Result: The non-duplicated manufacturer_codes will be retrieved.

  3. select distinct(manufacturer_code) from products; MAN --- AIW BOS CRV DA GMI HVC JBL JVC MIT PAN PIN PIO SAM SHE SON TEA TEC THN YAM 19 rows selected.

  4. Equi-Join • A resulting relation that contains tuples satisfying the predicate of equality between two attributes of the same domain from the Cartesian product of R and S RR.aj S.bj S

  5. Equi-Join SQL> select product_code, p.manufacturer_code, 2 m.manufacturer_code 3 from products p, manufacturers m 4 where p.manufacturer_code = m.manufacturer_code; PRODUCT_CO MAN MAN ---------- --- --- RS1B INF INF SM165 INF INF CC1M INF INF 100 BOS BOS 201-IV BOS BOS VS-100 BOS BOS 6 rows selected. Aliases Products Manufacturers RS1B INF SM165 INF CC1M INF 100 BOS 201-IV BOS VS-100 BOS ADV Advent BOS Bose INF Infinity

  6. Outer Joins in Oracle • An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. • Such rows are not returned by a simple join. • To perform an outer join of tables A and B and return all rows from A, apply the outer join operator (+) to all columns of B in the join condition. • For all rows in A that have no matching rows in B, a NULL is returned for any select list expressions containing columns of B.

  7. Left Outer Join • All rows on the left table (i.e., products) are kept SQL> select product_code, product_description, 2 p.manufacturer_code, m.manufacturer_code 3 from products p, manufacturers m 4 where p.manufacturer_code = m.manufacturer_code(+); PRODUCT_CO PRODUCT_DESCRIPTION MAN MAN ---------- ----------------------------------- --- --- 100 Compact Speakers BOS BOS 201-IV Direct/Reflecting Speakers BOS BOS VS-100 Center Channel Mini Speaker BOS BOS RS1B 2-way Bookshelf Speakers INF INF SM165 Bookshelf-sized Speakers INF INF CC1M Center Channel Speaker INF INF 6 rows selected.

  8. Right Outer Join • All rows on the right table (i.e., manufacturers) are kept SQL> select product_code, product_description, 2 p.manufacturer_code, m.manufacturer_code 3 from products p, manufacturers m 4 where p.manufacturer_code(+) = m.manufacturer_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN MAN ---------- ----------------------------------- --- --- ADV 100 Compact Speakers BOS BOS 201-IV Direct/Reflecting Speakers BOS BOS VS-100 Center Channel Mini Speaker BOS BOS RS1B 2-way Bookshelf Speakers INF INF SM165 Bookshelf-sized Speakers INF INF CC1M Center Channel Speaker INF INF 7 rows selected. 

  9. Cartesian Product SQL> select * from products, manufacturers; RS1B 2-way Bookshelf Speakers INF 188 PR ADV Advent SM165 Bookshelf-sized Speakers INF 205 PR ADV Advent CC1M Center Channel Speaker INF 164 EA ADV Advent 100 Compact Speakers BOS 205 PR ADV Advent 201-IV Direct/Reflecting Speakers BOS 205 PR ADV Advent VS-100 Center Channel Mini Speaker BOS 116.96 EA ADV Advent RS1B 2-way Bookshelf Speakers INF 188 PR INF Infinity SM165 Bookshelf-sized Speakers INF 205 PR INF Infinity CC1M Center Channel Speaker INF 164 EA INF Infinity 100 Compact Speakers BOS 205 PR INF Infinity 201-IV Direct/Reflecting Speakers BOS 205 PR INF Infinity VS-100 Center Channel Mini Speaker BOS 116.96 EA INF Infinity RS1B 2-way Bookshelf Speakers INF 188 PR BOS Bose SM165 Bookshelf-sized Speakers INF 205 PR BOS Bose CC1M Center Channel Speaker INF 164 EA BOS Bose 100 Compact Speakers BOS 205 PR BOS Bose 201-IV Direct/Reflecting Speakers BOS 205 PR BOS Bose VS-100 Center Channel Mini Speaker BOS 116.96 EA BOS Bose 18 rows selected.

  10. Cartesian Product SQL> select * from products, manufacturers; RS1B 2-way Bookshelf Speakers INF 188 PR ADV Advent SM165 Bookshelf-sized Speakers INF 205 PR ADV Advent CC1M Center Channel Speaker INF 164 EA ADV Advent 100 Compact Speakers BOS 205 PR ADV Advent 201-IV Direct/Reflecting Speakers BOS 205 PR ADV Advent VS-100 Center Channel Mini Speaker BOS 116.96 EA ADV Advent RS1B 2-way Bookshelf Speakers INF 188 PR INF Infinity SM165 Bookshelf-sized Speakers INF 205 PR INF Infinity CC1M Center Channel Speaker INF 164 EA INF Infinity 100 Compact Speakers BOS 205 PR INF Infinity 201-IV Direct/Reflecting Speakers BOS 205 PR INF Infinity VS-100 Center Channel Mini Speaker BOS 116.96 EA INF Infinity RS1B 2-way Bookshelf Speakers INF 188 PR BOS Bose SM165 Bookshelf-sized Speakers INF 205 PR BOS Bose CC1M Center Channel Speaker INF 164 EA BOS Bose 100 Compact Speakers BOS 205 PR BOS Bose 201-IV Direct/Reflecting Speakers BOS 205 PR BOS Bose VS-100 Center Channel Mini Speaker BOS 116.96 EA BOS Bose 18 rows selected. • IJrows (tuples), N + M columns (attributes) Products RS1B 2-way Bookshelf Speakers INF 188 PR SM165 Bookshelf-sized Speakers INF 205 PR CC1M Center Channel Speaker INF 164 EA 100 Compact Speakers BOS 205 PR 201-IV Direct/Reflecting Speakers BOS 205 PR VS-100 Center Channel Mini Speaker BOS 116.96 EA Manufacturers ADV Advent INF Infinity BOS Bose 3 x 6 = 18 rows

  11. R  S Union, Intersection, Set Difference SELECT colunn-name1, ..., column-namen FROM table1 {UNION | INTERSECT | MINUS} SELECT column-name1, ..., column-namen FROM table2 attribute-1, ..., attribute-n(R) { |  | -}attribute-1, ..., attribute-n(S) Columns must correspond to one another R S

  12. Example Tables TEAC_200_products; W-410C Double Cassette Deck TEA 89.96 EA W-525R Auto-Reverse Double Cassette TEA 170.96 EA V-370 Cassette Deck TEA 63.86 EA V-390CHX Cassette Deck TEA 89.96 EA EQA-3 Stereo 10-Band Equalizer TEA 62.96 EA PD-555 CD Player TEA 116.96 EA PD-C400 Remote CD Player TEA 152.96 EA 7 Rows TEAC_150_250_products W-525R Auto-Reverse Double Cassette TEA 170.96 EA W-585R Double Cassette Deck TEA 224.96 EA PD-C400 Remote CD Player TEA 152.96 EA 3 Rows

  13. Union attribute-1, ..., attribute-n(R) attribute-1, ..., attribute-n(S) SQL> select * from teac_200_products 2 union 3 select * from teac_150_250_products; EQA-3 Stereo 10-Band Equalizer TEA 62.96 EA PD-555 CD Player TEA 116.96 EA PD-C400 Remote CD Player TEA 152.96 EA V-370 Cassette Deck TEA 63.86 EA V-390CHX Cassette Deck TEA 89.96 EA W-410C Double Cassette Deck TEA 89.96 EA W-525R Auto-Reverse Double Cassette TEA 170.96 EA W-585R Double Cassette Deck TEA 224.96 EA 8 rows selected. No duplicates

  14. Intersection attribute-1, ..., attribute-n(R) attribute-1, ..., attribute-n(S) SQL> select * from teac_200_products 2 intersect 3 select * from teac_150_250_products; PD-C400 Remote CD Player TEA 152.96 EA W-525R Auto-Reverse Double Cassette TEA 170.96 EA Common between the two tables

  15. Set Difference • Performed on two union compatible tables (i.e., same columns) • Displays the rows unique to one of the two tables (i.e., found in one but not the other) • The order of the tables matters! attribute-1, ..., attribute-n(R) attribute-1, ..., attribute-n(S)

  16. Set Difference attribute-1, ..., attribute-n(R) attribute-1, ..., attribute-n(S) SQL> select * from teac_200_products 2 minus 3 select * from teac_150_250_products; EQA-3 Stereo 10-Band Equalizer TEA 62.96 EA PD-555 CD Player TEA 116.96 EA V-370 Cassette Deck TEA 63.86 EA V-390CHX Cassette Deck TEA 89.96 EA W-410C Double Cassette Deck TEA 89.96 EA Why? SQL>select * from teac_150_250_products 2 minus 3 select * from teac_200_products; W-585R Double Cassette Deck TEA 224.96 EA

  17. Set Difference attribute-1, ..., attribute-n(R) attribute-1, ..., attribute-n(S) SQL> select * from teac_200_products 2 minus 3 select * from teac_150_250_products; EQA-3 Stereo 10-Band Equalizer TEA 62.96 EA PD-555 CD Player TEA 116.96 EA V-370 Cassette Deck TEA 63.86 EA V-390CHX Cassette Deck TEA 89.96 EA W-410C Double Cassette Deck TEA 89.96 EA TEAC_200_products; W-410C W-525R V-370 V-390CHX EQA-3 PD-555 PD-C400 7 Rows Duplicates TEAC_150_250_products W-525R W-585R PD-C400 These rows are unique to TEAC_200_products 3 Rows

  18. Set Difference attribute-1, ..., attribute-n(R) attribute-1, ..., attribute-n(S) SQL> select * from teac_150_250_products 2 minus 3 select * from teac_200_products; W-585R Double Cassette Deck TEA 224.96 EA TEAC_150_250_products W-525R W-585R PD-C400 3 Rows TEAC_200_products; W-410C W-525R V-370 V-390CHX EQA-3 PD-555 PD-C400 This rows is unique to TEAC_150_250_products Duplicates 7 Rows

  19. Calculation and Format Models (Masks) Concatenation SQL> select product_code, 2 manufacturer_name || ' - ' || product_description 3 as "Description", 4 number_on_hand as "Stock on Hand", 5 to_char(product_retail_price,'$9,990.00') as "Selling Price", 6 to_char(number_on_hand * product_retail_price,'$9,990.00') 7 as "Inventory Value" 8 from products p, manufacturers m 9 where p.manufacturer_code = m.manufacturer_code 10 and product_retail_price < 100; Alias Format model Converts numeric or date data type to character (required for a format mask)

  20. Calculation and Format Models (Masks)(Results) PRODUCT_CO Description Stock on Hand Selling Pr Inventory ---------- --------------------------------------------- ------------- XL-BD10 Gemini - Semi-Auto Belt-Dr Turntable 1 $80.96 $80.96 V-370 Teac - Cassette Deck 1 $63.86 $63.86 V-390CHX Teac - Cassette Deck 1 $89.96 $89.96 W-410C Teac - Double Cassette Deck 1 $89.96 $89.96 SH-8017 Technics - Graphic Equalizer 1 $62.96 $62.96 SL-BD20 Technics - Belt-Drive Semi-Auto Turntabl 1 $89.96 $89.96 EQA-3 Teac - Stereo 10-Band Equalizer 1 $62.96 $62.96 CD-1000C Sherwood - Compact Disc Changer 1 $89.96 $89.96 8 rows selected. (Edited to fit the slide)

  21. Applicable Columns SQL> describe products; Name Null? Type --------------------------------------- -------- ------------- PRODUCT_CODE NOT NULL VARCHAR2(10) PRODUCT_DESCRIPTION VARCHAR2(35) PRODUCT_COST NUMBER(8,2) PRODUCT_MSRP NUMBER(8,2) PRODUCT_RETAIL_PRICE NUMBER(8,2) RETAIL_UNIT CHAR(2) MANUFACTURER_CODE CHAR(3) ACTIVE_DATE DATE NUMBER_ON_HAND NUMBER(6) NUMBER_ON_ORDER NUMBER(6) NUMBER_COMMITTED NUMBER(6) INACTIVE_DATE DATE SQL> describe manufacturers; Name Null? Type --------------------------------------- -------- ------------- MANUFACTURER_CODE NOT NULL CHAR(3) MANUFACTURER_NAME NOT NULL VARCHAR2(30)

  22. SQL> select manufacturer_name, sum(number_on_hand), 2 max(product_retail_price),min(product_retail_price), 3 avg(product_retail_price),count(product_code) 4 from products p, manufacturers m 5 where p.manufacturer_code = m.manufacturer_code 6 group by manufacturer_name 7 having sum(number_on_hand) > 25 8 order by manufacturer_name; MANUFACTURER_NAME SUM(NUMBER_ON_HAND) MAX(PRODUCT_RETAIL_PRICE) -------------------- ------------------- ------------------------- MIN(PRODUCT_RETAIL_PRICE) AVG(PRODUCT_RETAIL_PRICE) COUNT(PRODUCT_CODE) ------------------------- ------------------------- ------------------- JVC 30 1266 116.96 417.31867 30 Polk 26 1614 135 427.07692 26 Sony 68 2474 116.96 561.34426 68 Technics 32 629.96 62.96 200.97625 32 Do not follow this query too closely for your homework assignment

  23. SQL> select manufacturer_name manufacturer, 2 to_char(sum(number_on_hand),'9,990')"Total on Hand", 3 to_char(max(product_retail_price),'$9,990.00') "Max Price", 4 to_char(min(product_retail_price),'$9,990.00') "Min Price", 5 to_char(avg(product_retail_price),'$9,990.00') 6 "Average Price", 7 to_char(count(product_code),'9,990') "Total Products" 8 from products p, manufacturers m 9 where p.manufacturer_code = m.manufacturer_code 10 group by manufacturer_name 11 having sum(number_on_hand) > 25 12 order by manufacturer_name; MANUFACTURER Total Max Price Min Price Average Pr Total ----------------- ------ ---------- ---------- ---------- ------ JVC 30 $1,266.00 $116.96 $417.32 30 Polk 26 $1,614.00 $135.00 $427.08 26 Sony 68 $2,474.00 $116.96 $561.34 68 Technics 32 $629.96 $62.96 $200.98 32 Output edited to fit slide Do not follow this query too closely for your homework assignment

  24. Et cetera: Changing Column TitlesUsing a Column Alias SQL> select po_number as "PO", 2 to_char(po_date,'fmMonth dd, yyyy') as "PO Date", 3 to_char(total_amount,'999,990.00') as "Total Amount" 4 from purchase_orders 5 where po_date > (sysdate - 45); PO PO Date Total Amoun ----- ------------------ ----------- 10018 September 11, 1999 36,189.05 10019 September 18, 1999 5,394.40 10020 September 25, 1999 15,313.65 10021 October 2, 1999 25,470.15 10022 October 9, 1999 1,949.70

  25. Substring, Truncation, Modulus:SUBSTR, TRUN, MOD SQL> select product_code, 2 substr(manufacturer_name||' - '||product_description, 3 1,35) as "Product", 4 substr(to_char(active_date,'fmMonth yyyy'),1,12) 5 as "Active Date", 6 substr(to_char(sysdate,'fmMonth yyyy'),1,12) 7 as "Current Date", 8 trunc((sysdate-active_date)/365.25,0) as "Years", 9 trunc(mod(sysdate-active_date,365.25)/30,0) 10 as "Months" 11 from products p, manufacturers m 12 where p.manufacturer_code=m.manufacturer_code 13 and lower(product_description) like '%cd%';

  26. Substring, Truncation, Modulus:SUBSTR, TRUN, MOD SQL> select product_code, 2 substr(manufacturer_name||' - '||product_description, 3 1,35) as "Product", 4 substr(to_char(active_date,'fmMonth yyyy'),1,12) 5 as "Active Date", 6 substr(to_char(sysdate,'fmMonth yyyy'),1,12) 7 as "Current Date", 8 trunc((sysdate-active_date)/365.25,0) as "Years", 9 trunc(mod(sysdate-active_date,365.25)/30,0) 10 as "Months" 11 from products p, manufacturers m 12 where p.manufacturer_code=m.manufacturer_code 13 and lower(product_description) like '%cd%'; SUBSTR (substring): Selects a portion of the string as specified by the beginning position and length arguments SUBSTR(string,begin-position,length)

  27. Substring, Truncation, Modulus:SUBSTR, TRUN, MOD SQL> select product_code, 2 substr(manufacturer_name||' - '||product_description, 3 1,35) as "Product", 4 substr(to_char(active_date,'fmMonth yyyy'),1,12) 5 as "Active Date", 6 substr(to_char(sysdate,'fmMonth yyyy'),1,12) 7 as "Current Date", 8 trunc((sysdate-active_date)/365.25,0) as "Years", 9 trunc(mod(sysdate-active_date,365.25)/30,0) 10 as "Months" 11 from products p, manufacturers m 12 where p.manufacturer_code=m.manufacturer_code 13 and lower(product_description) like '%cd%'; TRUNC (truncate): Prevents rounding in arithmetic operations. TRUNC(arithmetic-operation,decimal-places)

  28. Substring, Truncation, Modulus:SUBSTR, TRUN, MOD SQL> select product_code, 2 substr(manufacturer_name||' - '||product_description, 3 1,35) as "Product", 4 substr(to_char(active_date,'fmMonth yyyy'),1,12) 5 as "Active Date", 6 substr(to_char(sysdate,'fmMonth yyyy'),1,12) 7 as "Current Date", 8 trunc((sysdate-active_date)/365.25,0) as "Years", 9 trunc(mod(sysdate-active_date,365.25)/30,0) 10 as "Months" 11 from products p, manufacturers m 12 where p.manufacturer_code=m.manufacturer_code 13 and lower(product_description) like '%cd%'; MOD (modulus): Retains the remainder in division. MOD(numerator,denominator) Part of TRUNC argument

  29. PRODUCT_CO Product Active Date Current Date Years Months ---------- ----------------------------------- ------------ ------------ --------- --------- DVD-A110 Panasonic - DVD/CD Player May 1998 March 2001 2 10 DV-505 Pioneer - DVD/CD Player May 1998 March 2001 2 10 XV-1000BK JVC - DVD/CD Player May 1998 March 2001 2 10 DVL-909 Pioneer - DVD/CD/Laserdisc Player May 1998 March 2001 2 10 DVP-S7000 Sony - DVD/CD Player May 1998 March 2001 2 10 DVP-S3000 Sony - DVD/CD Player May 1998 March 2001 2 10 DVP-S500D Sony - DVD/CD Player May 1998 March 2001 2 10 SC-TC430 Panasonic - Compact System w/CD Cha August 1996 March 2001 4 6 CCS-350 Pioneer - Compact System w/CD Playe August 1996 March 2001 4 6 CCS-450 Pioneer - Compact System w/CD Playe August 1996 March 2001 4 6 CCS-550 Pioneer - 75-watt System w/CD Chang August 1996 March 2001 4 6 CDP-C225 Sony - Disc Jockey CD Changer August 1996 March 2001 4 6 CDP-C325 Sony - Disc Jockey CD Changer August 1996 March 2001 4 6 CDP-C425 Sony - Disc Jockey CD Changer August 1996 March 2001 4 6 CDP-C525 Sony - Disc Jockey CD Changer August 1996 March 2001 4 6 PD-F907 Pioneer - 100+1 Disc CD Changer May 1998 March 2001 2 10 PD-F957 Pioneer - 100+1 Disc CD Changer May 1998 March 2001 2 10 CDC-502 Yamaha - 5-Disc Carousel CD Changer May 1998 March 2001 2 10 CDC-902 Yamaha - 5-Disc Carousel CD Changer May 1998 March 2001 2 10 XL-F154 JVC - 5-Disc CD Changer May 1998 March 2001 2 10 XL-F254 JVC - 5-Disc CD Changer May 1998 March 2001 2 10 XL-MC222 JVC - 200-Disc CD Changer May 1998 March 2001 2 10 XL-MC334 JVC - 200-Disc CD Changer May 1998 March 2001 2 10 CD-223M Kenwood - 200-Disc CD Changer May 1998 March 2001 2 10 CD-224M Kenwood - 200-Disc CD Changer May 1998 March 2001 2 10 SL-PD788 Technics - 5-Disc CD Changer May 1998 March 2001 2 10 SL-PD888 Technics - 5-Disc CD Changer May 1998 March 2001 2 10 SL-PD988 Technics - 5-Disc CD Changer May 1998 March 2001 2 10 . . . PD-F407 Pioneer - 25-Disc CD Changer May 1998 March 2001 2 10 PD-F507 Pioneer - 25-Disc CD Changer May 1998 March 2001 2 10 PD-F607 Pioneer - 25-Disc CD Changer May 1998 March 2001 2 10 48 rows selected.

  30. Applicable Columns SQL> describe products; Name Null? Type --------------------------------------- -------- ------------- PRODUCT_CODE NOT NULL VARCHAR2(10) PRODUCT_DESCRIPTION VARCHAR2(35) PRODUCT_COST NUMBER(8,2) PRODUCT_MSRP NUMBER(8,2) PRODUCT_RETAIL_PRICE NUMBER(8,2) RETAIL_UNIT CHAR(2) MANUFACTURER_CODE CHAR(3) ACTIVE_DATE DATE NUMBER_ON_HAND NUMBER(6) NUMBER_ON_ORDER NUMBER(6) NUMBER_COMMITTED NUMBER(6) INACTIVE_DATE DATE SQL> describe manufacturers; Name Null? Type --------------------------------------- -------- ------------- MANUFACTURER_CODE NOT NULL CHAR(3) MANUFACTURER_NAME NOT NULL VARCHAR2(30)

  31. Aggregation Functions • Summation (SUM) • Count (COUNT) • Average value (AVG) • Maximum value (MAX) • Minimum value (MIN)

  32. SQL> select manufacturer_name, sum(number_on_hand), 2 max(product_retail_price),min(product_retail_price), 3 avg(product_retail_price),count(product_code) 4 from products p, manufacturers m 5 where p.manufacturer_code = m.manufacturer_code 6 group by manufacturer_name 7 having sum(number_on_hand) > 25 8 order by manufacturer_name; MANUFACTURER_NAME SUM(NUMBER_ON_HAND) MAX(PRODUCT_RETAIL_PRICE) -------------------- ------------------- ------------------------- MIN(PRODUCT_RETAIL_PRICE) AVG(PRODUCT_RETAIL_PRICE) COUNT(PRODUCT_CODE) ------------------------- ------------------------- ------------------- JVC 30 1266 116.96 417.31867 30 Polk 26 1614 135 427.07692 26 Sony 68 2474 116.96 561.34426 68 Technics 32 629.96 62.96 200.97625 32 Do not follow this query too closely for your homework assignment

  33. SQL> select manufacturer_name manufacturer, 2 to_char(sum(number_on_hand),'9,990')"Total on Hand", 3 to_char(max(product_retail_price),'$9,990.00') "Max Price", 4 to_char(min(product_retail_price),'$9,990.00') "Min Price", 5 to_char(avg(product_retail_price),'$9,990.00') 6 "Average Price", 7 to_char(count(product_code),'9,990') "Total Products" 8 from products p, manufacturers m 9 where p.manufacturer_code = m.manufacturer_code 10 group by manufacturer_name 11 having sum(number_on_hand) > 25 12 order by manufacturer_name; MANUFACTURER Total Max Price Min Price Average Pr Total ----------------- ------ ---------- ---------- ---------- ------ JVC 30 $1,266.00 $116.96 $417.32 30 Polk 26 $1,614.00 $135.00 $427.08 26 Sony 68 $2,474.00 $116.96 $561.34 68 Technics 32 $629.96 $62.96 $200.98 32 Output edited to fit slide Do not follow this query too closely for your homework assignment

  34. Subquery • Embeds a complete SELECT statement (inner query) within another SELECT statement (outer query) • Subquery types: • Scalar - returns a single column and row (i.e., single value) • Row - returns multiple columns, but a single row • Table - returns one or more columns and multiple rows

  35. Scalar Subquery SQL> select product_code, product_description 2 from products 3 where manufacturer_code = 4 (select manufacturer_code 5 from manufacturers 6 where manufacturer_name = 'Bose'); Returns only one value (i.e., one manufacturer_code)

  36. As Single Queries SQL> select manufacturer_code 2 from manufacturers 3 where manufacturer_name = 'Bose'; MAN --- BOS

  37. SQL> select product_code, product_description 2 from products 3 where manufacturer_code = 'BOS'; PRODUCT_CO PRODUCT_DESCRIPTION ---------- ----------------------------------- VS-100 Center Channel Mini Speaker 301-III Direct/Reflecting Speakers 901Classic Direct/Reflecting Speaker System AM3 Acoustimass Speaker System AM5 Acoustimass 5 Speaker System AM7 Acoustimass 7 Speaker System AM5II Accoustimass-5 Series II Speakers AM6 Accoustimass-6 Speaker System AM10 Accoustimass-10 Speaker System LS5 Lifestyle 5 II Speaker System LS12 Lifestyle 12 Compact Speaker System LS25 Lifestyle 25 Home Theater Speakers 100 Compact Speakers 201-IV Direct/Reflecting Speakers 301-IV Direct/Reflecting Speakers 501-V Floor Direct/Reflecting Speakers 701 Tower Speakers 17 rows selected.

  38. SQL> select product_code, product_description 2 from products 3 where manufacturer_code = 4 (select manufacturer_code 5 from manufacturers 6 where manufacturer_name = 'Bose'); PRODUCT_CO PRODUCT_DESCRIPTION ---------- ----------------------------------- VS-100 Center Channel Mini Speaker 301-III Direct/Reflecting Speakers 901Classic Direct/Reflecting Speaker System AM3 Acoustimass Speaker System AM5 Acoustimass 5 Speaker System AM7 Acoustimass 7 Speaker System AM5II Accoustimass-5 Series II Speakers AM6 Accoustimass-6 Speaker System AM10 Accoustimass-10 Speaker System LS5 Lifestyle 5 II Speaker System LS12 Lifestyle 12 Compact Speaker System LS25 Lifestyle 25 Home Theater Speakers 100 Compact Speakers 201-IV Direct/Reflecting Speakers 301-IV Direct/Reflecting Speakers 501-V Floor Direct/Reflecting Speakers 701 Tower Speakers 17 rows selected. Outer query Results of the inner query are ‘fed’ to the outer query Inner query All Bose products

  39. Non-scalar Subquery SQL> select product_code, product_description 2 from products 3 where manufacturer_code = 4 (select manufacturer_code 5 from manufacturers 6 where manufacturer_name in ('Bose','JBL','Advent')); (select manufacturer_code * ERROR at line 4: ORA-01427: single-row subquery returns more than one row Returns more than one value

  40. Table Subqueries • EXISTS and NOT EXISTS • EXISTS - at least east one row exists in the subset returned by the subquery • NOT EXISTS - all rows that do not exist in the subset returned by the subquery • ANY/SOME and ALL • ANY - any value produced by the subquery (i.e., one or more rows) can be satisfied • ALL - all values of the subquery must be satisfied

  41. NOT EXISTS versus EXISTS NOT EXISTS SQL> select product_code, product_description, manufacturer_code 2 from products p 3 where not exists 4 (select * from manufacturers m 5 where p.manufacturer_code = m.manufacturer_code 6 and manufacturer_code > 'C') 7 order by p.manufacturer_code; EXISTS SQL> select product_code, product_description, manufacturer_code 2 from products p where EXISTS 3 (select * from manufacturers m 4 where manufacturer_name like 'C%' and 5 p.manufacturer_code = m.manufacturer_code) 6 order by p.manufacturer_code, product_code; Note. These queries can also be accomplished without the subquery.

  42. SQL> select product_code, product_description, manufacturer_code 2 from products p 3 where not exists 4 (select * from manufacturers m 5 where p.manufacturer_code = m.manufacturer_code 6 and manufacturer_code > 'C') 7 order by p.manufacturer_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN ---------- ----------------------------------- --- XK-S9000 Cassette Deck AIW NSX-D2 Mini Component System AIW 310HOB High-output Tower Speakers, Black AR 310HOM High-output Tower Speakers, Maple AR 310HOC High-output Tower Speakers, Cherry AR 266HOB 2-way Tower Speakers, Black AR 266HOM 2-way Tower Speakers, Maple AR 266HOC 2-way Tower Speakers, Cherry AR ••• LS12 Lifestyle 12 Compact Speaker System BOS LS25 Lifestyle 25 Home Theater Speakers BOS DISC Discount DIS S/H Shipping and Handling S/H CA TAX California Sales Tax TAX 44 rows selected. Table Subquery Creates a subset of rows for all manufacturers with code greater than “C” Not found in Manufacturers Note. This query can also be accomplished without the subquery.

  43. SQL> select product_code, product_description, manufacturer_code 2 from products p 3 where not exists 4 (select * from manufacturers m 5 where p.manufacturer_code = m.manufacturer_code 6 and manufacturer_code > 'C') 7 order by p.manufacturer_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN ---------- ----------------------------------- --- XK-S9000 Cassette Deck AIW NSX-D2 Mini Component System AIW 310HOB High-output Tower Speakers, Black AR 310HOM High-output Tower Speakers, Maple AR 310HOC High-output Tower Speakers, Cherry AR 266HOB 2-way Tower Speakers, Black AR 266HOM 2-way Tower Speakers, Maple AR 266HOC 2-way Tower Speakers, Cherry AR ••• LS12 Lifestyle 12 Compact Speaker System BOS LS25 Lifestyle 25 Home Theater Speakers BOS DISC Discount DIS S/H Shipping and Handling S/H CA TAX California Sales Tax TAX 44 rows selected. Table Subquery Exists Set Not Exists Set All manufacturer names > ‘C’ All rows are drawn from the not exists set Not found in Manufacturers All manufacturer names <= ‘C’ Note. This query can also be accomplished without the subquery.

  44. SQL> select product_code, product_description, manufacturer_code 2 from products p where EXISTS 3 (select * from manufacturers m 4 where manufacturer_name like 'C%' and 5 p.manufacturer_code = m.manufacturer_code) 6 order by p.manufacturer_code, product_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN ---------- ----------------------------------- --- AT-10 Loudspeakers CRV AT-15 Three-way Speaker CRV SW-12B Subwoofer Speaker System CRV Note. This query can also be accomplished without the subquery.

  45. SQL> select product_code, product_description, manufacturer_code 2 from products p where EXISTS 3 (select * from manufacturers m 4 where manufacturer_name like 'C%' and 5 p.manufacturer_code = m.manufacturer_code) 6 order by p.manufacturer_code, product_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN ---------- ----------------------------------- --- AT-10 Loudspeakers CRV AT-15 Three-way Speaker CRV SW-12B Subwoofer Speaker System CRV Exists Set Not Exists Set All manufacturer names like ‘C’ All rows are drawn from the not exists set All manufacturer names not like ‘C’ Note. This query can also be accomplished without the subquery.

  46. SQL> select product_code, product_description 2 from products p 3 where exists 4 (select manufacturer_code 5 from manufacturers m 6 where manufacturer_name in ('Bose', 'JBL', 'Advent') 7 and p.manufacturer_code = m.manufacturer_code); PRODUCT_CO PRODUCT_DESCRIPTION ---------- ----------------------------------- VS-100 Center Channel Mini Speaker PROIIIPLUS Pro III Plus Mini Speaker System 301-III Direct/Reflecting Speakers 3800 Three-way Speaker System 4312 Studio Monitors 901Classic Direct/Reflecting Speaker System AM3 Acoustimass Speaker System AM5 Acoustimass 5 Speaker System AM7 Acoustimass 7 Speaker System AM5II Accoustimass-5 Series II Speakers ••• 301-IV Direct/Reflecting Speakers 501-V Floor Direct/Reflecting Speakers 701 Tower Speakers 20 rows selected. From scalar example Exists Set Bose, JBL, Advent Not Exists Set

  47. ANY vs. ALL Example Set of all products whose manufacturer_code = ‘PAN’ SQL> select product_code, product_description, 2 product_retail_price 3 from products where manufacturer_code = 'PAN'; PRODUCT_CO PRODUCT_DESCRIPTION PRODUCT_RETAIL_PRICE ---------- -------------------------------- -------------------- CT-27G33 27" 2-Tuner PIP TV 467 CT-32G23 32" 1-Tuner PIP TV 637 CT-32G33 32" 2-Tuner PIP TV 722 CT-32S35 32" Super-flat Tube TV 934 DVD-A110 DVD/CD Player 382 PV-2201 HQ VHS Video Cassette Recorder 206.96 PV-4210 4-Head VHS Video Cass Recorder 269.96 PV-4250 HiFi VHS Video Cass Recorder 314.96 SC-T095 Compact Stereo System 125.96 SC-TC430 Compact System w/CD Changer 386.96 10 rows selected.

  48. ANY vs. ALL Example Set of all entities (rows) whose manufacturer_code = ‘TEA’ SQL> select product_code, product_description, 2 product_retail_price 3 from products where manufacturer_code = 'TEA'; PRODUCT_CO PRODUCT_DESCRIPTION PRODUCT_RETAIL_PRICE ---------- -------------------------------- -------------------- W-410C Double Cassette Deck 89.96 W-525R Auto-Reverse Double Cassette 170.96 W-585R Double Cassette Deck 224.96 V-370 Cassette Deck 63.86 V-390CHX Cassette Deck 89.96 EQA-3 Stereo 10-Band Equalizer 62.96 PD-555 CD Player 116.96 PD-C400 Remote CD Player 152.96 8 rows selected. Highest and lowest prices

  49. ALL SQL> select product_code, product_retail_price 2 from products 3 where manufacturer_code = 'PAN' and 4 product_retail_price > ALL 5 (select product_retail_price from products 6 where manufacturer_code = 'TEA'); PRODUCT_CO PRODUCT_RETAIL_PRICE ---------- -------------------- CT-27G33 467 CT-32G23 637 CT-32G33 722 CT-32S35 934 DVD-A110 382 PV-4210 269.96 PV-4250 314.96 SC-TC430 386.96 8 rows selected. Returned set of ‘PAN’ products with product_retail_price > ALL ‘TEA’ product_product _price (i.e., all ‘PAN’ prices exceed every ‘TEA’ price or the highest ‘TEA’price)

  50. ANY SQL> select product_code, product_retail_price 2 from products 3 where manufacturer_code = 'PAN' and 4 product_retail_price > ANY 5 (select product_retail_price from products 6 where manufacturer_code = 'TEA'); PRODUCT_CO PRODUCT_RETAIL_PRICE ---------- -------------------- CT-27G33 467 CT-32G23 637 CT-32G33 722 CT-32S35 934 DVD-A110 382 PV-2201 206.96 PV-4210 269.96 PV-4250 314.96 SC-T095 125.96 SC-TC430 386.96 10 rows selected. Returned set of ‘PAN’ products with product_retail_price > ANY ‘TEA’ product_product _price (i.e., any ‘PAN’ prices that exceeds a ‘TEA’ price or the lowest ‘TEA’ price)

More Related