1 Week 6 October 3 SQL Joins Set
1 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 R. Ching, Ph. D. • MIS • California State University, Sacramento
Using DISTINCT SELECT DISTINCT(manfuacturer_code) FROM products Result: The non-duplicated manufacturer_codes will be retrieved. R. Ching, Ph. D. • MIS • California State University, Sacramento 2
select distinct(manufacturer_code) from products; MAN --AIW BOS CRV DA GMI HVC JBL JVC MIT PAN PIO SAM SHE SON TEA TEC THN YAM 19 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento 3
Equi-Join 4 R R. aj S. bj S • 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 R. Ching, Ph. D. • MIS • California State University, Sacramento
Equi-Join SQL> 2 3 4 5 select product_code, p. manufacturer_code, m. manufacturer_code Aliases from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code; PRODUCT_CO -----RS 1 B SM 165 CC 1 M 100 201 -IV VS-100 MAN --INF INF INF BOS BOS BOS Products RS 1 B SM 165 CC 1 M 100 201 -IV VS-100 6 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento Manufacturers INF INF BOS BOS ADV BOS INF Advent Bose Infinity
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. R. Ching, Ph. D. • MIS • California State University, Sacramento 6
Left Outer Join 7 • All rows on the left table (i. e. , products) are kept SQL> 2 3 4 select product_code, product_description, p. manufacturer_code, m. manufacturer_code from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code(+); PRODUCT_CO -----100 201 -IV VS-100 RS 1 B SM 165 CC 1 M PRODUCT_DESCRIPTION -----------------Compact Speakers Direct/Reflecting Speakers Center Channel Mini Speaker 2 -way Bookshelf Speakers Bookshelf-sized Speakers Center Channel Speaker 6 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento MAN --BOS BOS BOS INF INF INF
Right Outer Join 8 • All rows on the right table (i. e. , manufacturers) are kept SQL> 2 3 4 select product_code, product_description, p. manufacturer_code, m. manufacturer_code from products p, manufacturers m where p. manufacturer_code(+) = m. manufacturer_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN ----------------------- --ADV 100 Compact Speakers BOS 201 -IV Direct/Reflecting Speakers BOS VS-100 Center Channel Mini Speaker BOS RS 1 B 2 -way Bookshelf Speakers INF SM 165 Bookshelf-sized Speakers INF CC 1 M Center Channel Speaker INF 7 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento
Cartesian Product 9 SQL> select * from products, manufacturers; RS 1 B SM 165 CC 1 M 100 201 -IV VS-100 2 -way Bookshelf Speakers Bookshelf-sized Speakers Center Channel Speaker Compact Speakers Direct/Reflecting Speakers Center Channel Mini Speaker 18 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento INF 188 PR ADV Advent INF 205 PR ADV Advent INF 164 EA ADV Advent BOS 205 PR ADV Advent BOS 116. 96 EA ADV Advent INF 188 PR INF Infinity INF 205 PR INF Infinity INF 164 EA INF Infinity BOS 205 PR INF Infinity BOS 116. 96 EA INF Infinity INF 188 PR BOS Bose INF 205 PR BOS Bose INF 164 EA BOS Bose BOS 205 PR BOS Bose BOS 116. 96 EA BOS Bose
Cartesian Product 10 SQL> select * from products, manufacturers; RS 1 B 2 -way Bookshelf Speakers INF 188 PR ADV SM 165 Bookshelf-sized Speakers INF 205 PR ADV I J rows ( tuples ) , N + M columns (attributes) CC 1 M Center Channel Speaker INF 164 EA ADV 100 Compact Speakers BOS 205 PR ADV 201 -IV Products Direct/Reflecting Speakers BOS 205 PR ADV VS-100 RS 1 B Center 2 -way Channel Mini Speaker BOS 116. 96 Bookshelf Speakers INF EA ADV 188 RS 1 B SM 1652 -way Bookshelf Speakers INF 188 Bookshelf-sized Speakers INF PR INF 205 SM 165 CC 1 M Bookshelf-sized Speakers INF 205 PR INF Center Channel Speaker INF 164 CC 1 M 100 Center Compact Channel Speakers Speaker INF 164 EA INF BOS 205 100 Compact Speakers BOS 205 PR INF 201 -IV Direct/Reflecting Speakers BOS 205 201 -IV VS-100 Direct/Reflecting Speakers BOS 205 PR INF Center Channel Mini Speaker BOS 116. 96 VS-100 Center Channel Mini Speaker BOS 116. 96 EA INF RS 1 B 2 -way Bookshelf Speakers INF 188 PR BOS Manufacturers SM 165 Bookshelf-sized Speakers INF 205 PR BOS ADV Advent CC 1 M Center Channel Speaker INF 164 EA BOS INF Infinity 3 BOS x 6 = 18 100 Compact Speakers 205 rows PR BOS 201 -IV BOS Bose Direct/Reflecting Speakers BOS 205 PR BOS VS-100 Center Channel Mini Speaker BOS 116. 96 EA BOS R. Ching, Ph. D. • MIS • California State University, Sacramento 18 rows selected. Advent Advent PR Infinity EA Infinity Bose Bose
Union, Intersection, Set Difference attribute-1, . . . , attribute-n(R) { | | -} attribute-1, . . . , attribute-n(S) SELECT colunn-name 1, . . . , column-namen FROM table 1 {UNION | INTERSECT | MINUS} SELECT column-name 1, . . . , column-namen FROM table 2 Columns must correspond to one another R R S S R. Ching, Ph. D. • MIS • California State University, Sacramento 11
Example Tables 12 TEAC_200_products; W-410 C W-525 R V-370 V-390 CHX EQA-3 PD-555 PD-C 400 Double Cassette Deck Auto-Reverse Double Cassette Deck Stereo 10 -Band Equalizer CD Player Remote CD Player TEA TEA 89. 96 170. 96 63. 86 89. 96 62. 96 116. 96 152. 96 EA EA TEA TEA 170. 96 EA 224. 96 EA 152. 96 EA 7 Rows TEAC_150_250_products W-525 R W-585 R PD-C 400 Auto-Reverse Double Cassette Deck Remote CD Player 3 Rows R. Ching, Ph. D. • MIS • California State University, Sacramento
Union 13 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 PD-555 PD-C 400 V-370 V-390 CHX W-410 C W-525 R W-585 R Stereo 10 -Band Equalizer CD Player Remote CD Player Cassette Deck Double Cassette Deck Auto-Reverse Double Cassette Deck 8 rows selected. No duplicates R. Ching, Ph. D. • MIS • California State University, Sacramento TEA TEA 62. 96 116. 96 152. 96 63. 86 89. 96 170. 96 224. 96 EA EA
Intersection 14 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-C 400 W-525 R Remote CD Player Auto-Reverse Double Cassette TEA Common between the two tables R. Ching, Ph. D. • MIS • California State University, Sacramento 152. 96 EA 170. 96 EA
Set Difference attribute-1, . . . , attribute-n(R) attribute-1, . . . , attribute-n(S) • 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! R. Ching, Ph. D. • MIS • California State University, Sacramento 15
Set Difference 16 attribute-1, . . . , attribute-n(R) attribute-1, . . . , attribute-n(S) SQL> 2 3 select * from teac_200_products minus select * from teac_150_250_products; EQA-3 PD-555 V-370 V-390 CHX W-410 C Stereo 10 -Band Equalizer CD Player Cassette Deck Double Cassette Deck TEA TEA TEA 62. 96 116. 96 63. 86 89. 96 EA EA EA Why? SQL> select * from teac_150_250_products 2 minus 3 select * from teac_200_products; W-585 R Double Cassette Deck R. Ching, Ph. D. • MIS • California State University, Sacramento TEA 224. 96 EA
Set Difference 17 attribute-1, . . . , attribute-n(R) attribute-1, . . . , attribute-n(S) SQL> 2 3 select * from teac_200_products TEAC_200_products; minus W-410 C select * from teac_150_250_products; EQA-3 PD-555 V-370 V-390 CHX W-410 C Stereo 10 -Band Equalizer CD Player Cassette Deck Double Cassette Deck These rows are unique to TEAC_200_products R. Ching, Ph. D. • MIS • California State University, Sacramento W-525 R V-370 TEA 62. 96 EA 7 Rows V-390 CHX 116. 96 TEA EA EQA-3 TEA 63. 86 EA PD-555 TEA 89. 96 EA Duplicates PD-C 400 89. 96 TEA EA TEAC_150_250_products W-525 R W-585 R PD-C 400 3 Rows
Set Difference 18 attribute-1, . . . , attribute-n(R) attribute-1, . . . , attribute-n(S) SQL> 2 3 select * from teac_150_250_products. TEAC_150_250_products minus select * from teac_200_products; W-525 R W-585 R Double Cassette Deck This rows is unique to TEAC_150_250_products R. Ching, Ph. D. • MIS • California State University, Sacramento W-585 R 3 Rows PD-C 400 TEA 224. 96 EA TEAC_200_products; W-410 C W-525 R V-370 V-390 CHX EQA-3 PD-555 PD-C 400 Duplicates 7 Rows
Calculation and Format Models (Masks) 19 Concatenation SQL> 2 3 4 5 6 7 8 9 10 select product_code, Alias manufacturer_name || ' - ' || product_description as "Description", number_on_hand as "Stock on Hand", to_char(product_retail_price, '$9, 990. 00') as "Selling Price", to_char(number_on_hand * product_retail_price, '$9, 990. 00') as "Inventory Value" from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code and product_retail_price < 100; Format Converts numeric or date data type to character (required for a format mask) R. Ching, Ph. D. • MIS • California State University, Sacramento model
Calculation and Format Models (Masks) (Results) PRODUCT_CO -----XL-BD 10 V-370 V-390 CHX W-410 C SH-8017 SL-BD 20 EQA-3 CD-1000 C 20 Description Stock on Hand Selling Pr Inventory -----------------------Gemini - Semi-Auto Belt-Dr Turntable 1 $80. 96 Teac - Cassette Deck 1 $63. 86 Teac - Cassette Deck 1 $89. 96 Teac - Double Cassette Deck 1 $89. 96 Technics - Graphic Equalizer 1 $62. 96 Technics - Belt-Drive Semi-Auto Turntabl 1 $89. 96 Teac - Stereo 10 -Band Equalizer 1 $62. 96 Sherwood - Compact Disc Changer 1 $89. 96 8 rows selected. (Edited to fit the slide) R. Ching, Ph. D. • MIS • California State University, Sacramento
Applicable Columns SQL> describe products; Name -------------------PRODUCT_CODE PRODUCT_DESCRIPTION PRODUCT_COST PRODUCT_MSRP PRODUCT_RETAIL_PRICE RETAIL_UNIT MANUFACTURER_CODE ACTIVE_DATE NUMBER_ON_HAND NUMBER_ON_ORDER NUMBER_COMMITTED INACTIVE_DATE SQL> describe manufacturers; Name -------------------MANUFACTURER_CODE MANUFACTURER_NAME R. Ching, Ph. D. • MIS • California State University, Sacramento 21 Null? -------NOT NULL Type ------VARCHAR 2(10) VARCHAR 2(35) NUMBER(8, 2) CHAR(3) DATE NUMBER(6) DATE Null? -------NOT NULL Type ------CHAR(3) VARCHAR 2(30)
SQL> 2 3 4 5 6 7 8 select manufacturer_name, sum(number_on_hand), max(product_retail_price), min(product_retail_price), avg(product_retail_price), count(product_code) from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code group by manufacturer_name having sum(number_on_hand) > 25 order by manufacturer_name; 22 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 Sony Technics 135 116. 96 62. 96 26 68 32 427. 07692 561. 34426 200. 97625 1614 2474 629. 96 Do not follow this query too closely for your homework assignment R. Ching, Ph. D. • MIS • California State University, Sacramento 26 68 32
SQL> 2 3 4 5 6 7 8 9 10 11 12 select manufacturer_name manufacturer, to_char(sum(number_on_hand), '9, 990')"Total on Hand", to_char(max(product_retail_price), '$9, 990. 00') "Max Price", 23 to_char(min(product_retail_price), '$9, 990. 00') "Min Price", to_char(avg(product_retail_price), '$9, 990. 00') "Average Price", to_char(count(product_code), '9, 990') "Total Products" from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code group by manufacturer_name having sum(number_on_hand) > 25 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 R. Ching, Ph. D. • MIS • California State University, Sacramento
Et cetera: Changing Column Titles Using a Column Alias SQL> 2 3 4 5 24 select po_number as "PO", to_char(po_date, 'fm. Month dd, yyyy') as "PO Date", to_char(total_amount, '999, 990. 00') as "Total Amount" from purchase_orders where po_date > (sysdate - 45); PO ----10018 10019 10020 10021 10022 PO Date Total Amoun ---------September 11, 1999 36, 189. 05 September 18, 1999 5, 394. 40 September 25, 1999 15, 313. 65 October 2, 1999 25, 470. 15 October 9, 1999 1, 949. 70 R. Ching, Ph. D. • MIS • California State University, Sacramento
Substring, Truncation, Modulus: SUBSTR, TRUN, MOD SQL> 2 3 4 5 6 7 8 9 10 11 12 13 25 select product_code, substr(manufacturer_name||' - '||product_description, 1, 35) as "Product", substr(to_char(active_date, 'fm. Month yyyy'), 1, 12) as "Active Date", substr(to_char(sysdate, 'fm. Month yyyy'), 1, 12) as "Current Date", trunc((sysdate-active_date)/365. 25, 0) as "Years", trunc(mod(sysdate-active_date, 365. 25)/30, 0) as "Months" from products p, manufacturers m where p. manufacturer_code=m. manufacturer_code and lower(product_description) like '%cd%'; R. Ching, Ph. D. • MIS • California State University, Sacramento
Substring, Truncation, Modulus: SUBSTR, TRUN, MOD SQL> 2 3 4 5 6 7 8 9 10 11 12 13 26 select product_code, substr(manufacturer_name||' - '||product_description, 1, 35) as "Product", substr(to_char(active_date, 'fm. Month yyyy'), 1, 12) as "Active Date", substr(to_char(sysdate, 'fm. Month yyyy'), 1, 12) as "Current Date", SUBSTR (substring): Selects a portionas "Years", trunc((sysdate-active_date)/365. 25, 0) trunc(mod(sysdate-active_date, 365. 25)/30, 0) of the string as specified by the as "Months" beginning position and length arguments from products p, manufacturers m where p. manufacturer_code=m. manufacturer_code SUBSTR(string, begin-position , length ) and lower(product_description) like '%cd%'; R. Ching, Ph. D. • MIS • California State University, Sacramento
Substring, Truncation, Modulus: SUBSTR, TRUN, MOD 27 SQL> select product_code, 2 substr(manufacturer_name||' - '||product_description, 3 1, 35) as "Product", 4 substr(to_char(active_date, 'fm. Month yyyy'), 1, 12) 5 as "Active Date", 6 substr(to_char(sysdate, 'fm. Month 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 Prevents rounding in arithmetic operations. 12 TRUNC where (truncate): p. manufacturer_code=m. manufacturer_code 13 and lower(product_description) like '%cd%'; TRUNC(arithmetic-operation, decimal-places) R. Ching, Ph. D. • MIS • California State University, Sacramento
Substring, Truncation, Modulus: SUBSTR, TRUN, MOD SQL> 2 3 4 5 6 7 8 9 10 11 12 13 28 select MODproduct_code, (modulus): Retains the remainder in division. substr(manufacturer_name||' - '||product_description, 1, 35) as "Product", MOD(numerator, denominator)yyyy'), 1, 12) substr(to_char(active_date, 'fm. Month as "Active Date", substr(to_char(sysdate, 'fm. Month yyyy'), 1, 12) as "Current Date", trunc((sysdate-active_date)/365. 25, 0) as "Years", trunc(mod(sysdate-active_date, 365. 25)/30, 0) as "Months" from products p, manufacturers m Part of TRUNC argument where p. manufacturer_code=m. manufacturer_code and lower(product_description) like '%cd%'; R. Ching, Ph. D. • MIS • California State University, Sacramento
PRODUCT_CO -----DVD-A 110 DV-505 XV-1000 BK DVL-909 DVP-S 7000 DVP-S 3000 DVP-S 500 D SC-TC 430 CCS-350 CCS-450 CCS-550 CDP-C 225 CDP-C 325 CDP-C 425 CDP-C 525 PD-F 907 PD-F 957 CDC-502 CDC-902 XL-F 154 XL-F 254 XL-MC 222 XL-MC 334 CD-223 M CD-224 M SL-PD 788 SL-PD 888 SL-PD 988. . . Product -----------------Panasonic - DVD/CD Player Pioneer - DVD/CD Player JVC - DVD/CD Player Pioneer - DVD/CD/Laserdisc Player Sony - DVD/CD Player Panasonic - Compact System w/CD Cha Pioneer - Compact System w/CD Playe Pioneer - 75 -watt System w/CD Chang Sony - Disc Jockey CD Changer Pioneer - 100+1 Disc CD Changer Yamaha - 5 -Disc Carousel CD Changer JVC - 5 -Disc CD Changer JVC - 200 -Disc CD Changer Kenwood - 200 -Disc CD Changer Technics - 5 -Disc CD Changer Active Date ------May 1998 May 1998 August 1996 August 1996 May 1998 May 1998 May 1998 May 1998 Current Date Years Months ------ ----March 2001 2 10 March 2001 2 10 March 2001 4 6 March 2001 4 6 March 2001 2 10 March 2001 2 10 March 2001 2 10 March 2001 2 10 PD-F 407 PD-F 507 PD-F 607 Pioneer - 25 -Disc CD Changer May 1998 March 2001 48 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento 2 2 2 10 10 10 29
Applicable Columns SQL> describe products; Name -------------------PRODUCT_CODE PRODUCT_DESCRIPTION PRODUCT_COST PRODUCT_MSRP PRODUCT_RETAIL_PRICE RETAIL_UNIT MANUFACTURER_CODE ACTIVE_DATE NUMBER_ON_HAND NUMBER_ON_ORDER NUMBER_COMMITTED INACTIVE_DATE SQL> describe manufacturers; Name -------------------MANUFACTURER_CODE MANUFACTURER_NAME R. Ching, Ph. D. • MIS • California State University, Sacramento 30 Null? -------NOT NULL Type ------VARCHAR 2(10) VARCHAR 2(35) NUMBER(8, 2) CHAR(3) DATE NUMBER(6) DATE Null? -------NOT NULL Type ------CHAR(3) VARCHAR 2(30)
Aggregation Functions • • • Summation (SUM) Count (COUNT) Average value (AVG) Maximum value (MAX) Minimum value (MIN) R. Ching, Ph. D. • MIS • California State University, Sacramento 31
SQL> 2 3 4 5 6 7 8 select manufacturer_name, sum(number_on_hand), max(product_retail_price), min(product_retail_price), avg(product_retail_price), count(product_code) from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code group by manufacturer_name having sum(number_on_hand) > 25 order by manufacturer_name; 32 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 Sony Technics 135 116. 96 62. 96 26 68 32 427. 07692 561. 34426 200. 97625 1614 2474 629. 96 Do not follow this query too closely for your homework assignment R. Ching, Ph. D. • MIS • California State University, Sacramento 26 68 32
SQL> 2 3 4 5 6 7 8 9 10 11 12 select manufacturer_name manufacturer, to_char(sum(number_on_hand), '9, 990')"Total on Hand", to_char(max(product_retail_price), '$9, 990. 00') "Max Price", 33 to_char(min(product_retail_price), '$9, 990. 00') "Min Price", to_char(avg(product_retail_price), '$9, 990. 00') "Average Price", to_char(count(product_code), '9, 990') "Total Products" from products p, manufacturers m where p. manufacturer_code = m. manufacturer_code group by manufacturer_name having sum(number_on_hand) > 25 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 R. Ching, Ph. D. • MIS • California State University, Sacramento
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 R. Ching, Ph. D. • MIS • California State University, Sacramento 34
Scalar Subquery SQL> 2 3 4 5 6 35 select product_code, product_description from products where manufacturer_code = (select manufacturer_code Returns only one from manufacturers value (i. e. , one where manufacturer_name = 'Bose'); manufacturer_code) R. Ching, Ph. D. • MIS • California State University, Sacramento manufacturer_code)
As Single Queries SQL> select manufacturer_code 2 from manufacturers 3 where manufacturer_name = 'Bose'; MAN --BOS R. Ching, Ph. D. • MIS • California State University, Sacramento 36
SQL> select product_code, product_description 2 from products 3 where manufacturer_code = 'BOS'; PRODUCT_CO -----VS-100 301 -III 901 Classic AM 3 AM 5 AM 7 AM 5 II AM 6 AM 10 LS 5 LS 12 LS 25 100 201 -IV 301 -IV 501 -V 701 PRODUCT_DESCRIPTION -----------------Center Channel Mini Speaker Direct/Reflecting Speakers Direct/Reflecting Speaker System Acoustimass 5 Speaker System Acoustimass 7 Speaker System Accoustimass-5 Series II Speakers Accoustimass-6 Speaker System Accoustimass-10 Speaker System Lifestyle 5 II Speaker System Lifestyle 12 Compact Speaker System Lifestyle 25 Home Theater Speakers Compact Speakers Direct/Reflecting Speakers Floor Direct/Reflecting Speakers Tower Speakers 17 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento 37
SQL> Outer 2 query 3 4 Inner 5 query 6 select product_code, product_description from products where manufacturer_code = (select manufacturer_code from manufacturers where manufacturer_name = 'Bose'); PRODUCT_CO -----VS-100 301 -III 901 Classic AM 3 AM 5 AM 7 AM 5 II AM 6 AM 10 LS 5 LS 12 LS 25 100 201 -IV 301 -IV 501 -V 701 PRODUCT_DESCRIPTION -----------------Center Channel Mini Speaker Direct/Reflecting Speakers Direct/Reflecting Speaker System Acoustimass 5 Speaker System Acoustimass 7 Speaker System Accoustimass-5 Series II Speakers Accoustimass-6 Speaker System Accoustimass-10 Speaker System Lifestyle 5 II Speaker System Lifestyle 12 Compact Speaker System Lifestyle 25 Home Theater Speakers Compact Speakers Direct/Reflecting Speakers Floor Direct/Reflecting Speakers Tower Speakers 17 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento Results of the inner query are 38 ‘fed’ to the outer query All Bose products
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 * Returns more than one value ERROR at line 4: ORA-01427: single-row subquery returns more than one row R. Ching, Ph. D. • MIS • California State University, Sacramento 39
Table Subqueries • EXISTS and NOT EXISTS – EXISTS - at least 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 R. Ching, Ph. D. • MIS • California State University, Sacramento 40
NOT EXISTS versus EXISTS 41 NOT EXISTS SQL> 2 3 4 5 6 7 select product_code, product_description, manufacturer_code from products p where not exists (select * from manufacturers m where p. manufacturer_code = m. manufacturer_code and manufacturer_code > 'C') order by p. manufacturer_code; EXISTS SQL> 2 3 4 5 6 select product_code, product_description, manufacturer_code from products p where EXISTS (select * from manufacturers m where manufacturer_name like 'C%' and p. manufacturer_code = m. manufacturer_code) order by p. manufacturer_code, product_code; Note. These queries can also be accomplished without the subquery. R. Ching, Ph. D. • MIS • California State University, Sacramento
SQL> 2 3 4 5 6 7 select product_code, product_description, manufacturer_code from products p where not exists (select * from manufacturers m where p. manufacturer_code = m. manufacturer_code Table 42 and manufacturer_code > 'C') Subquery order by p. manufacturer_code; PRODUCT_CO -----XK-S 9000 NSX-D 2 310 HOB 310 HOM 310 HOC 266 HOB 266 HOM 266 HOC • • • LS 12 LS 25 DISC S/H CA TAX Creates a. MAN subset of rows PRODUCT_DESCRIPTION ------------------for all manufacturers with Cassette Deck AIW code greater Mini Component System AIW than “C” High-output Tower Speakers, Black AR High-output Tower Speakers, Maple AR High-output Tower Speakers, Cherry AR 2 -way Tower Speakers, Black AR 2 -way Tower Speakers, Maple AR 2 -way Tower Speakers, Cherry AR Lifestyle 12 Compact Speaker System Lifestyle 25 Home Theater Speakers Discount Shipping and Handling California Sales Tax 44 rows selected. BOS DIS S/H TAX Not found in Manufacturers Note. This query can also be accomplished without the subquery. R. Ching, Ph. D. • MIS • California State University, Sacramento
SQL> 2 3 4 5 6 7 select product_code, product_description, manufacturer_code from products p where not exists (select * from manufacturers m where p. manufacturer_code = m. manufacturer_code Table 43 and manufacturer_code > 'C') Subquery order by p. manufacturer_code; PRODUCT_CO PRODUCT_DESCRIPTION MAN ----------------------- --XK-S 9000 Cassette Deck AIW NSX-D 2 Mini Component System AIW 310 HOB High-output Tower Speakers, Black AR Exists Set Not Exists Set 310 HOM High-output Tower Speakers, Maple AR 310 HOC High-output Tower Speakers, Cherry AR 266 HOB 2 -way Tower Speakers, Black AR 266 HOM 2 -way Tower Speakers, Maple AR All AR 266 HOC 2 -way Tower Speakers, Cherry manufacturer All rows are drawn from • • • LS 12 Lifestyle names >BOS ‘C’ the not exists set 12 Compact Speaker System LS 25 Lifestyle 25 Home Theater Speakers BOS DISC Discount DIS Not found in S/H Shipping and Handling S/H Manufacturers All manufacturer names CA TAX California Sales Tax TAX <= ‘C’ 44 rows selected. Note. This query can also be accomplished without the subquery. R. Ching, Ph. D. • MIS • California State University, Sacramento
44 SQL> 2 3 4 5 6 select product_code, product_description, manufacturer_code from products p where EXISTS (select * from manufacturers m where manufacturer_name like 'C%' and p. manufacturer_code = m. manufacturer_code) order by p. manufacturer_code, product_code; PRODUCT_CO -----AT-10 AT-15 SW-12 B PRODUCT_DESCRIPTION -----------------Loudspeakers Three-way Speaker Subwoofer Speaker System MAN --CRV CRV Note. This query can also be accomplished without the subquery. R. Ching, Ph. D. • MIS • California State University, Sacramento
SQL> 2 3 4 5 6 select product_code, product_description, manufacturer_code from products p where EXISTS 45 (select * from manufacturers m where manufacturer_name like 'C%' and p. manufacturer_code = m. manufacturer_code) order by p. manufacturer_code, product_code; PRODUCT_DESCRIPTION Not Exists Set MANExists Set ------------------ --Loudspeakers CRV Three-way Speaker CRV Subwoofer Speaker System CRV All manufacturer rows are drawn from names like ‘C’ the not exists set PRODUCT_CO -----AT-10 AT-15 SW-12 B All manufacturer names not like ‘C’ Note. This query can also be accomplished without the subquery. R. Ching, Ph. D. • MIS • California State University, Sacramento
SQL> 2 3 4 5 6 7 select product_code, product_description from products p where exists (select manufacturer_code from manufacturers m where manufacturer_name in ('Bose', 'JBL', 'Advent') and p. manufacturer_code = m. manufacturer_code); PRODUCT_CO -----VS-100 PROIIIPLUS 301 -III 3800 4312 901 Classic AM 3 AM 5 AM 7 AM 5 II • • • 301 -IV 501 -V 701 PRODUCT_DESCRIPTION -----------------Center Channel Mini Speaker Pro III Plus Mini Speaker System Direct/Reflecting Speakers Three-way Speaker System Studio Monitors Direct/Reflecting Speaker System Acoustimass 5 Speaker System Acoustimass 7 Speaker System Accoustimass-5 Series II Speakers Direct/Reflecting Speakers Floor Direct/Reflecting Speakers Tower Speakers 20 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento From scalar 46 example Exists Set Bose, JBL, Advent Not Exists Set
ANY vs. ALL Example 47 Set of all products whose manufacturer_code = ‘PAN’ SQL> 2 3 select product_code, product_description, product_retail_price from products where manufacturer_code = 'PAN'; PRODUCT_CO -----CT-27 G 33 CT-32 G 23 CT-32 G 33 CT-32 S 35 DVD-A 110 PV-2201 PV-4210 PV-4250 SC-T 095 SC-TC 430 PRODUCT_DESCRIPTION PRODUCT_RETAIL_PRICE ----------------27" 2 -Tuner PIP TV 467 32" 1 -Tuner PIP TV 637 32" 2 -Tuner PIP TV 722 32" Super-flat Tube TV 934 DVD/CD Player 382 HQ VHS Video Cassette Recorder 206. 96 4 -Head VHS Video Cass Recorder 269. 96 Hi. Fi VHS Video Cass Recorder 314. 96 Compact Stereo System 125. 96 Compact System w/CD Changer 386. 96 10 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento
ANY vs. ALL Example 48 Set of all entities (rows) whose manufacturer_code = ‘TEA’ SQL> 2 3 select product_code, product_description, product_retail_price from products where manufacturer_code = 'TEA'; PRODUCT_CO -----W-410 C W-525 R W-585 R V-370 V-390 CHX EQA-3 PD-555 PD-C 400 PRODUCT_DESCRIPTION PRODUCT_RETAIL_PRICE ----------------Double Cassette Deck 89. 96 Auto-Reverse Double Cassette 170. 96 Double Cassette Deck 224. 96 Cassette Deck 63. 86 Cassette Deck 89. 96 Stereo 10 -Band Equalizer 62. 96 CD Player 116. 96 Remote CD Player 152. 96 8 rows selected. Highest and lowest prices R. Ching, Ph. D. • MIS • California State University, Sacramento
ALL SQL> 2 3 4 5 6 49 select product_code, product_retail_price from products where manufacturer_code = 'PAN' and product_retail_price > ALL (select product_retail_price from products where manufacturer_code = 'TEA'); PRODUCT_CO PRODUCT_RETAIL_PRICE ---------------CT-27 G 33 467 CT-32 G 23 637 CT-32 G 33 722 CT-32 S 35 934 DVD-A 110 382 PV-4210 269. 96 PV-4250 314. 96 SC-TC 430 386. 96 8 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento 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)
ANY SQL> 2 3 4 5 6 50 select product_code, product_retail_price from products where manufacturer_code = 'PAN' and product_retail_price > ANY (select product_retail_price from products where manufacturer_code = 'TEA'); PRODUCT_CO PRODUCT_RETAIL_PRICE ---------------CT-27 G 33 467 CT-32 G 23 637 CT-32 G 33 722 CT-32 S 35 934 DVD-A 110 382 PV-2201 206. 96 PV-4210 269. 96 PV-4250 314. 96 SC-T 095 125. 96 SC-TC 430 386. 96 10 R. rows selected. Ching, Ph. D. • MIS • California State University, Sacramento 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)
IN SQL> 2 3 4 5 6 7 8 51 select product_code "Code", product_description "Product Description", to_char(product_retail_price, '$9, 999. 00') "Price" from products p where p. manufacturer_code IN (select m. manufacturer_code from manufacturers m where upper(manufacturer_name) like '%'||'OS'||'%'); Code -----VS-100 301 -III 901 Classic AM 3 AM 5 AM 7. . . 701 Product Description Price ------------------Center Channel Mini Speaker $116. 96 Direct/Reflecting Speakers $286. 20 Direct/Reflecting Speaker System $1, 259. 90 Acoustimass Speaker System $629. 96 Acoustimass 5 Speaker System $674. 96 Acoustimass 7 Speaker System $809. 96 Tower Speakers 17 rows selected. R. Ching, Ph. D. • MIS • California State University, Sacramento $732. 00
Views • A dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not actually exist in the database but is produced upon request by a particular user, at the time of request. • General syntax: CREATE VIEW view-name AS SELECT. . . R. Ching, Ph. D. • MIS • California State University, Sacramento 52
Horizontal vs. Vertical Views • Horizontal view – Equivalent to a selection create view-name as select * from table-name where condition • Vertical view – Equivalent to a projection or a projection on a selection create view-name as select column-list from table-name where condition R. Ching, Ph. D. • MIS • California State University, Sacramento 53
Creating a View 54 Horizontal View SQL> create view Sony_products as 2 (select * from products where manufacturer_code = 'SON'); View created. Vertical View SQL> 2 3 4 5 6 create view economy_priced_products as (select product_code, product_description, product_retail_price, retail_unit, manufacturer_code, product_MSRP from products where product_retail_price <= 300); View created. R. Ching, Ph. D. • MIS • California State University, Sacramento
Retrieving from a View 55 Creating a join between a view and table SQL> 2 3 4 5 select product_code, manufacturer_name||' - '|| product_description, product_retail_price from economy_priced_products e, manufacturers m where e. manufacturer_code = m. manufacturer_code and product_retail_price between 250 and 260; CS 275 PD-F 907 CS-13 RX PS-8 c Polk - Center Channel Speaker 254 Pioneer - 100+1 Disc CD Changer 255 Mitsubishi - 13" Color TV 251. 96 Design Acoustics - Point Source Speaker Sytem 251. 96 R. Ching, Ph. D. • MIS • California State University, Sacramento
Advantages of Views • Data independence - external/conceptual mapping provides logical independence • Currency • Security • Reduced complexity • Convenience • Customization • Data integrity R. Ching, Ph. D. • MIS • California State University, Sacramento 56
Disadvantages of Views • • • Update restriction - limited to one base table Structure restriction - cannot be altered Performance - can be slow R. Ching, Ph. D. • MIS • California State University, Sacramento 57
Updating the Database • Modifying the data in the database – Insert - Add a new row to a table – Update - Modify existing data in a row or rows – Delete - Remove a row or rows from a table R. Ching, Ph. D. • MIS • California State University, Sacramento 58
INSERT • Insert – Adds a rows into a table INSERT INTO table-name (column-name 1, . . . , column-namen) VALUES (value 1, . . . , valuen) R. Ching, Ph. D. • MIS • California State University, Sacramento 59
SQL> describe products; Name Null? ----------------PRODUCT_CODE NOT NULL PRODUCT_DESCRIPTION PRODUCT_COST PRODUCT_MSRP PRODUCT_RETAIL_PRICE RETAIL_UNIT MANUFACTURER_CODE ACTIVE_DATE NUMBER_ON_HAND NUMBER_ON_ORDER NUMBER_COMMITTED INACTIVE_DATE SQL> 2 3 4 5 Type ---CHAR(10) VARCHAR 2(35) NUMBER(8, 2) CHAR(3) DATE NUMBER(6) DATE 60 insert into products (product_code, product_description, product_retail_price, retail_unit, active_date) values ('NEW', 'New product', 599. 99, 'EA', to_date('10 -08 -99', 'mm-dd-yy')); 1 row created. Values MUST correspond to the column names! R. Ching, Ph. D. • MIS • California State University, Sacramento
Insert SQL> select * from products 2 where product_code = 'NEW'; PRODUCT_CO PRODUCT_DESCRIPTION PRODUCT_COST PRODUCT_MSRP PRODUCT_RETAIL_PRICE RE MAN ----------------------- ---------- -- --ACTIVE_DA NUMBER_ON_HAND NUMBER_ON_ORDER NUMBER_COMMITTED INACTIVE_ -----------------NEW New product 599. 99 EA 08 -OCT-99 0 0 0 All columns that are not assigned a value by the INSERT are assigned null values UNLESS a default value was specified when the table was created. R. Ching, Ph. D. • MIS • California State University, Sacramento 61
UPDATE • Update – Modifies column values in the table UPDATE table-name SET column-name = {expression | literal} [WHERE condition] R. Ching, Ph. D. • MIS • California State University, Sacramento 62
Updating a Single Row SQL> 2 3 update products set number_on_hand = 25 where product_code = 'NEW'; 1 row updated. SQL> select product_code, number_on_hand 2 from products where product_code = 'NEW'; PRODUCT_CO NUMBER_ON_HAND -------------NEW 25 R. Ching, Ph. D. • MIS • California State University, Sacramento 63
Updating Multiple Rows Using a Recursive Arithmetic Operation 64 SQL> update products 2 set product_retail_price = product_retail_price * 1. 1 3 where manufacturer_code = 'BOS'; 17 rows updated. Product retail prices for all Bose (BOS) products are increased by 10 percent. R. Ching, Ph. D. • MIS • California State University, Sacramento
Deleting a Table 65 • Delete (not be confused with Drop) – Delete rows from a table DELETE table-name SQL> delete products; 319 rows deleted. SQL> rollback; Rollback complete. Rollback returns the database to its previous committed state. SQL> select count(*) from products; COUNT(*) ----319 R. Ching, Ph. D. • MIS • California State University, Sacramento
Deleting a Row from a Table 66 DELETE table-name WHERE condition SQL> delete products 2 where product_code = 'NEW'; 1 row deleted. SQL> commit; Commit complete. Commit permanently commits all changes (i. e. , inserts, updates, deletes) to the database. R. Ching, Ph. D. • MIS • California State University, Sacramento
Et cetera: Changing Column Titles Using a Column Alias SQL> 2 3 4 5 67 select po_number as "PO", to_char(po_date, 'fm. Month dd, yyyy') as "PO Date", to_char(total_amount, '999, 990. 00') as "Total Amount" from purchase_orders where po_date > (sysdate - 45); PO ----10018 10019 10020 10021 10022 PO Date Total Amoun ---------September 11, 1999 36, 189. 05 September 18, 1999 5, 394. 40 September 25, 1999 15, 313. 65 October 2, 1999 25, 470. 15 October 9, 1999 1, 949. 70 R. Ching, Ph. D. • MIS • California State University, Sacramento
Embedded SQL • For programming languages (3 GL, 4 GL) • SQL statements encapsulated in EXECs EXEC SQL command END EXEC • Three sections (in most cases) – Host variables - retains values passed to and from the columns of a table – SQLCA (communications area) - retains codes and messages sent between the application and DBMS – Processing (e. g. , procedure division in COBOL) R. Ching, Ph. D. • MIS • California State University, Sacramento 68
Host Variables 69 In COBOL * * Declare Host Variables EXEC SQL BEGIN DECLARE SECTION END-EXEC. * 01 01 01 * USERNAME PASSWORD PRODUCT-CODE PRODUCT-DESCRIPTION RETAIL-PRICE MSRP COST RETAIL-UNIT NUMBER-ON-HAND MANUFACTURER-CODE MANUFACTURER-NAME R. Ching, Ph. D. • MIS • California State University, Sacramento PIC PIC X(20) X(10). X(35). VALUE "mis 108". COMP-1. PIC PIC X(02). S 9(09) X(03). X(30). COMP-5.
SQLCA (Inserted by Precompiler) 70 * EXEC SQL INCLUDE SQLCA END-EXEC. * * * $Header: sqlca. cob 7010200. 1 93/11/30 13: 59: 57 snataraj > * ********************************* * S Q L C A * * (This file in ANSI format. Do NOT bse it). * * MODIFIED * * Clare 12/06/84 - Ch SQLCA to not be an EXTERNAL. * ********************************* 01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S 9(9) COMP-5. 05 SQLCODE PIC S 9(9) COMP-5. 05 SQLERRM. 49 SQLERRML PIC S 9(4) COMP-5. 49 SQLERRMC PIC X(70). R. Ching, Ph. D. • MIS • California State University, Sacramento
SQLCA 71 Cont. 05 05 SQLERRP PIC SQLERRD OCCURS 6 TIMES PIC SQLWARN. 10 SQLWARN 0 PIC 10 SQLWARN 1 PIC 10 SQLWARN 2 PIC 10 SQLWARN 3 PIC 10 SQLWARN 4 PIC 10 SQLWARN 5 PIC 10 SQLWARN 6 PIC 10 SQLWARN 7 PIC SQLEXT PIC R. Ching, Ph. D. • MIS • California State University, Sacramento X(8). S 9(9) COMP-5. X(1). X(8).
Embedded SQL 72 EXEC SQL SELECT MANUFACTURER_NAME Host variables INTO : MANUFACTURER-NAME FROM MANUFACTURERS WHERE MANUFACTURER_CODE = (SELECT MANUFACTURER_CODE FROM PRODUCTS WHERE PRODUCT_CODE = : PRODUCT-CODE) END-EXEC. Note. Host variables are preceded by colon (: ) R. Ching, Ph. D. • MIS • California State University, Sacramento
Sequentially Retrieving Rows Defines a cursor * * EXEC SQL DECLARE PCURSOR FOR SELECT PRODUCT_CODE, PRODUCT_DESCRIPTION, PRODUCT_RETAIL_PRICE, RETAIL_UNIT, NUMBER_ON_HAND FROM PRODUCTS WHERE MANUFACTURER_CODE = 'SON' END-EXEC SQL OPEN PCURSOR END-EXEC. Places the cursor at the beginning row of the SELECT PERFORM 1000 -RETRIEVE-ROWS UNTIL SQLCODE NOT = 0. EXEC SQL CLOSE PCURSOR END-EXEC. Closes the cursor R. Ching, Ph. D. • MIS • California State University, Sacramento 73
74 1000 -RETRIEVE-ROWS. * EXEC SQL FETCH PCURSOR INTO : PRODUCT-CODE, : PRODUCT-DESCRIPTION, : RETAIL-PRICE, : RETAIL-UNIT, : NUMBER-ON-HAND END-EXEC. * IF SQLCODE = 0 DISPLAY PRODUCT-CODE ". " Sequentially retrieves rows from the table based upon the SELECT defined in the DECLARE CURSOR R. Ching, Ph. D. • MIS • California State University, Sacramento
* * * * EXEC SQL DECLARE PCURSOR FOR SELECT PRODUCT_CODE, PRODUCT_DESCRIPTION, PRODUCT_RETAIL_PRICE, RETAIL_UNIT, NUMBER_ON_HAND FROM PRODUCTS WHERE MANUFACTURER_CODE = 'SON' END-EXEC SQL OPEN PCURSOR END-EXEC. CALL "_SQLADR" USING SQ 0001 SQL-STMT MOVE 1 TO SQL-ITERS MOVE 36 TO SQL-OFFSET CALL "_SQLADR" USING After precompiling the SQLCUD SQL-CUD COBOL program CALL "_SQLADR" USING SQLCA SQL-SQLEST MOVE 0 TO SQL-SQLETY CALL "_SQLBEX" USING SQLCTX SQLEXD SQLFPN. R. Ching, Ph. D. • MIS • California State University, Sacramento 75
* EXEC SQL * FETCH PCURSOR INTO : PRODUCT-CODE, : PRODUCT-DESCRIPTION, SQL ** : RETAIL-PRICE, : RETAIL-UNIT, : NUMBER-ON-HAND * END-EXEC. 76 * MOVE 1 TO SQL-ITERS MOVE 80 TO SQL-OFFSET CALL "_SQLADR" USING SQLCUD SQL-CUD CALL "_SQLADR" USING SQLCA SQL-SQLEST Precompiler (Oracle’s Pro*COBOL) MOVE 0 TO SQL-SQLETY converts SQL to COBOL code CALL "_SQLADR" USING PRODUCT-CODE SQL-SQHSTV(1) MOVE 10 TO SQL-SQHSTL(1) MOVE 0 TO SQL-SQINDV(1) MOVE 0 TO SQL-SQHARM(1) CALL "_SQLADR" USING PRODUCT-DESCRIPTION SQL-SQHSTV(2) MOVE 35 TO SQL-SQHSTL(2) MOVE 0 TO SQL-SQINDV(2) MOVE 0 TO SQL-SQHARM(2) CALL "_SQLADR" USING RETAIL-PRICE SQL-SQHSTV(3) R. Ching, Ph. D. • MIS • California State University, Sacramento MOVE 3 TO SQL-SQHSTL(3)
77 R. Ching, Ph. D. • MIS • California State University, Sacramento
- Slides: 77