1 / 70

Week 5 September 26

Week 5 September 26. Oracle: Control Files Basic Data Types SQL: Creating and Altering a Table, Imposing Integrity Constraints, DML. SQL Terminology. ISO terminology Relations (entity type)  Tables Attributes (properties)  Columns Tuples (entities)  Rows. column. Row {.

ryo
Download Presentation

Week 5 September 26

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 5September 26 Oracle: Control Files Basic Data Types SQL: Creating and Altering a Table,Imposing Integrity Constraints,DML

  2. SQL Terminology • ISO terminology • Relations (entity type)  Tables • Attributes (properties)  Columns • Tuples (entities)  Rows column Row {

  3. Oracle Control Files • Loads data into tables in batch mode • Two parts • Control language • Data

  4. Anatomy of a Control FileUsing Displacement (i.e., Column Position) Data contained in control file load data infile * into table videos (category_code position(1:2) char, stock_number position(5:10) char, video_title position(12:57) char, retail_price position(59:66) decimal external, running_time position(67:70) integer external, rating position(71:75) char, distributor_code position(77:79) char, year_released position(82:91) date(10) "mm-dd-yyyy", active_date position(92:101) date(10) "mm-dd-yyyy", stock_on_hand position(102:103) integer external, stock_on_order position(104:105) integer external) begindata SF 000025 Kronos 19.95 78NR BBE 01-01-195908-19-1995 412 DC 000036 Alice in Wonderland 22.95 75NR DIS 01-01-195103-11-1995 410 DC 000100 Little Mermaid, The 26.95 82NR DIS 01-01-198901-12-1991 317 SF 000101 Navy vs. the Night Monsters, The 19.95 90NR BBE 01-01-196609-29-1992 6 0 SF 000102 Monster Mania 9.99 60NR MCA 01-01-199806-01-1998 2 6 1 2 3 4 5 6 7 8 9 10 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345 Table name Data types and format Column names Data

  5. Anatomy of a Control FileUsing Displacement (i.e., Column Position) Data contained in control file load data infile * append into table videos Optional append load data infile * into table videos (category_code position(1:2) char, stock_number position(5:10) char, video_title position(12:57) char, retail_price position(59:66) decimal external, running_time position(67:70) integer external, rating position(71:75) char, distributor_code position(77:79) char, year_released position(82:91) date(10) "mm-dd-yyyy", active_date position(92:101) date(10) "mm-dd-yyyy", stock_on_hand position(102:103) integer external, stock_on_order position(104:105) integer external) begindata SF 000025 Kronos 19.95 78NR BBE 01-01-195908-19-1995 412 DC 000036 Alice in Wonderland 22.95 75NR DIS 01-01-195103-11-1995 410 DC 000100 Little Mermaid, The 26.95 82NR DIS 01-01-198901-12-1991 317 SF 000101 Navy vs. the Night Monsters, The 19.95 90NR BBE 01-01-196609-29-1992 6 0 SF 000102 Monster Mania 9.99 60NR MCA 01-01-199806-01-1998 2 6 1 2 3 4 5 6 7 8 9 10 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345 Table name Data types and format Column names Reading data from a file LOAD DATA INFILE 'manufac.dat' INTO TABLE manufacturers Data

  6. Anatomy of a Control FileUsing a Delimiter Data contained in control file LOAD DATA INFILE * INTO TABLE video_categories FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (category_code, category_title) begindata AC,Action BB,Big Book Best Sellers BC,British Comedy and Drama Table name Declared delimiter Column names Data

  7. Basic Data Types in Oracle Other data types exist

  8. Anatomy of a CREATE TABLE SQL> create table computer_products 2 (model_number varchar2(12) primary key, 3 product_description varchar2(50) default 'N/A', 4 list_price dec(6,2) default 0, 5 retail_price dec(6,2) default 0, 6 retail_unit char(2) default 'EA', 7 stock_on_hand int default 0, 8 stock_on_order int default 0, 9 last_shipment_received date, 10 manufacturer_code varchar2(3)); Table created. Constraint Data type Column name

  9. Integrity Constraints • Imposed to protect the database from becoming inconsistent • Types: • Required data • Domain constraints • Entity constraints • Referential integrity • Enterprise constraints

  10. Integrity Constraints • Required data • A column cannot be null (not null) • Domain constraints • Values assigned to a column must be from a defined domain SQL> CREATE TABLE REVENUES 2 (TRANSACTION_NUMBER ROWID PRIMARY KEY, 3 TRANSACTION_DATE DATE NOT NULL, 4 TRANSACTION_TYPE CHAR(1) CONSTRAINT CK_TRANS_TYPE 5 CHECK (TRANSACTION_TYPE IN('R','S','E','A','X'))); Table created.

  11. Domain Constraint • Using BETWEEN SQL> create table limousines 2 (fleet_number varchar2(10) primary key, 3 vehichle_type varchar2(15) not null, 4 seat_capacity smallint 5 constraint ck_seat_capacity check 6 (seat_capacity between 1 and 12)); Table created.

  12. Integrity Constraints • Entity constraints • Primary key must contain a unique, no null value for each row SQL> CREATE TABLE VIDEO_REVENUES 2 (TRANSACTION_NUMBER ROWID PRIMARY KEY, 3 TRANSACTION_TYPE CHAR(1) CONSTRAINT CK_TRANS_TYPE 4 CHECK (TRANSACTION_TYPE IN('R','S','E','A','X'))); Table created.

  13. Entity IntegrityComposite Key SQL> create table invoice_items 2 (invoice_number int, 3 item_number int constraint ck_invoice_number 4 check (item_number in (1,2,3,4,5,6,7,8,9,10)), 5 product_code varchar2(10), 6 quantity int default 1, 7 price float not null, 8 constraint pk_invoice_items primary key 9 (invoice_number, item_number), 10 constraint fk_invoice_number 11 foreign key (invoice_number) 12 references invoices(invoice_number), 13 constraint fk_product_code 14 foreign key (product_code) 15 references products(product_code)); Table created. Neither column is unique by itself Composite key

  14. Integrity Constraints • Referential integrity • The value of a foreign key must exist in another table (i.e., parent) as at least a candidate key • Normally, when a row in the parent is deleted and a child exists, one of fours actions can be taken: • Cascade • Set null* • Set default* • No action *Supported through triggers

  15. Integrity Constraints SQL> CREATE TABLE REVENUES 2 (TRANSACTION_NUMBER ROWID PRIMARY KEY, 3 TRANSACTION_TYPE CHAR(1) CONSTRAINT CK_TRANS_TYPE 4 CHECK (TRANSACTION_TYPE IN('R','S','E','A','X')), 5 PRODUCT_CODE, 6 CONSTRAINT FK_PRODUCT_CODE FOREIGN KEY (PRODUCT_CODE) 7 REFERENCES PRODUCTS(PRODUCT_CODE) 8ON DELETE CASCADE); Table created. Referential integrity CONSTRAINT index-name FOREIGN KEY (column-name) REFERENCES table-name(key-name) ON DELETE CASADE

  16. Integrity Constraints • Enterprise constraints • Organizational constraints (i.e., business rules)

  17. Another Example of Creating a Table with Referential Integrity Constraints Table name Column names, data types and constraints Table name Column names, data types and constraints  

  18. Example of Creating a Table with Referential Integrity Constraints Two ways of imposing constraints Table name Column name Referential integrity constraints  Constraint name (index)

  19. Example of Creating a Table with Referential Integrity Constraints Two ways of imposing constraints Refers to column name within the table Constraint name (index)  Column name Table name

  20. In Case of Error…ALTER Table • Alter table SQL command • Add a column • Modify a column • Delete a column • A column cannot be renamed • Drop the column • Add the column

  21. Customers table defined

  22. Adding a Column ALTER TABLEtable-nameADD (column-name data-type) Table name Optional constraint Data type Column name

  23. Modifying a Column Definition ALTER TABLEtable-nameMODIFY (column-name data-type) Table name New definition Column name

  24. Deleting a Column ALTER TABLEtable-nameDROP (column-name) Table name Column name

  25. Basic SQL: Data RetrievalFrom Relational Algebra to SQL license, make(color=‘Silver’(Cars)) Columns (attributes) to retrieve (projection) Select license, make from Cars where color = ‘Silver’ SELECTcolumn-list FROMtable-name WHEREcondition Table (relation) specification Row (tuple) specification (selection)

  26. Examples Using Products Table Products (product_code (key), product_description, product_cost, product_MSRP, product_retail_price, retail_unit, manufacturer_code (foreign key), active_date, number_on_hand, number_on_order, number_committed)

  27. Projection product_code, product_description(Products) SELECT product_code, product_description FROM products Result: All rows with only the product_code and product_description columns are retrieved Column-list

  28. SQL> select product_code, product_description from products; PRODUCT_CO PRODUCT_DESCRIPTION ---------- ------------------------------ 301-III Direct/Reflecting Speakers 3800 Three-way Speaker System 4312 Studio Monitors 901Classic Direct/Reflecting Spkr System AM3 Acoustimass Speaker System AM5 Acoustimass 5 Speaker System AM7 Acoustimass 7 Speaker System AT-10 Loudspeakers AT-15 Three-way Speaker CCS-350 Compact System w/CD Player CCS-450 Compact System w/CD Player CCS-550 75-watt System w/CD Changer CD-1000C Compact Disc Changer CDP-297 Compact Disc Player CDP-397 Compact Disc Player CDP-C225 Disc Jockey CD Changer . . . 100 rows selected.

  29. Selection manufacturer_code = ‘SON’ (Products) SELECT * FROM products WHERE manufacturer_code = ‘SON’ Result: Only those rows with SON for their manufacturer_code are retrieved along with all columns. All columns Proposition (predicate)

  30. Four Ways to Build a Selection  • Relational operator (=, <, <=, >, >=, <>) • Logical AND and OR WHEREcolumn-name=xAND column-name = y • Specific range using BETWEEN WHEREcolumn-nameBETWEENxANDy • Specific values using IN WHERE column-nameIN (list-of-values) • Character match using LIKE and wildcards (%, _) • UPPER and LOWER functions WHERE LOWER(column-name) LIKE‘%character-string%’

  31. Four ways to build a SELECTION:1. Relational and Logical Operators • Relational operators: = equals >, < greater than, less than <= less than or equal to >= greater than or equal to <> not equal to • Logical operators AND, OR AND evaluated before OR NOT NOT evaluated before AND and OR

  32. WHERE (Condition) SELECT * FROM products WHERE manufacturer_code = ‘SON’ OR manufacturer_code = ‘PAN’ SELECT * FROM products WHERE product_msrp >= 100 AND product_msrp <= 500 AND product_code = ‘SON’ OR product_code = ‘PAN’

  33. select * from products where manufacturer_code='SON'; PRODUCT_CO PRODUCT_DESCRIPTION PRODUCT_COST PRODUCT_MSRP PRODUCT_RETAIL_PRICE RE MAN ---------- ------------------------------ ------------ ------------ -------------------- -- --- ACTIVE_DA NUMBER_ON_HAND NUMBER_ON_ORDER NUMBER_COMMITTED --------- -------------- --------------- ---------------- CDP-297 Compact Disc Player 84.47 129.95 116.96 EA SON 25-AUG-96 4 0 0 CDP-397 Compact Disc Player 97.47 149.95 134.96 EA SON 25-AUG-96 11 0 0 CDP-C225 Disc Jockey CD Changer 129.97 199.95 179.96 EA SON 25-AUG-96 10 0 0 CDP-C325 Disc Jockey CD Changer 149.47 229.95 206.96 EA SON 25-AUG-96 1 0 0 CDP-C425 Disc Jockey CD Changer 162.47 249.95 224.96 EA SON 25-AUG-96 5 0 0 CDP-C525 Disc Jockey CD Changer 194.97 299.95 269.96 EA SON 25-AUG-96 6 0 0 SL-S600 Super-Beta Video Recorder 214.47 329.95 296.96 EA SON 25-AUG-96 13 0 0 TC-W490 Double Cassette Deck 110.47 169.95 152.96 EA SON 25-AUG-96 6 0 0 12 rows selected.

  34. Projection on a Selection SELECT product_code, product_description FROM products WHERE manufacturer_code = ‘SON’ OR manufacturer_code = ‘PAN’ product_code, product_description (manufacturer_code = ‘SON’ or ‘PAN’ (Products))

  35. SQL> select product_code, product_description from products where 2 manufacturer_code='SON' or manufacturer_code='PAN'; PRODUCT_CO PRODUCT_DESCRIPTION ---------- ------------------------------ CDP-297 Compact Disc Player CDP-397 Compact Disc Player CDP-C225 Disc Jockey CD Changer CDP-C325 Disc Jockey CD Changer CDP-C425 Disc Jockey CD Changer CDP-C525 Disc Jockey CD Changer PV-2201 HQ VHS Video Cassette Recorde PV-4210 4-Head VHS Video Cass Recorde PV-4250 HiFi VHS Video Cass Recorder SC-T095 Compact Stereo System SC-TC430 Compact System w/CD Changer SL-S600 Super-Beta Video Recorder TC-W490 Double Cassette Deck TC-WR590 Double Cassette Deck TC-WR690 Double Cassette Deck TC-WR790 Double Cassette Deck TC-WR875 Double Cassette Deck 17 rows selected.

  36. SQL> select product_code, product_description, product_msrp 2from products 3 where manufacturer_code='SON' or manufacturer_code='PAN' 4 and product_msrp >= 100 and product_msrp <=500; PRODUCT_CO PRODUCT_DESCRIPTION PRODUCT_MSRP ---------- ------------------------------ ------------ CDP-297 Compact Disc Player 129.95 CDP-397 Compact Disc Player 149.95 CDP-C225 Disc Jockey CD Changer 199.95 CDP-C325 Disc Jockey CD Changer 229.95 CDP-C425 Disc Jockey CD Changer 249.95 CDP-C525 Disc Jockey CD Changer 299.95 PV-2201 HQ VHS Video Cassette Recorde 229.95 PV-4210 4-Head VHS Video Cass Recorde 299.95 PV-4250 HiFi VHS Video Cass Recorder 349.95 SC-T095 Compact Stereo System 139.95 SC-TC430 Compact System w/CD Changer 429.95 SL-S600 Super-Beta Video Recorder 329.95 TC-W490 Double Cassette Deck 169.95 TC-WR590 Double Cassette Deck 199.95 TC-WR690 Double Cassette Deck 249.95 TC-WR790 Double Cassette Deck 329.95 TC-WR875 Double Cassette Deck 429.95 17 rows selected.

  37. Four ways to build a SELECTION:2. Range Search with BETWEEN SELECT product_code, product_description, product_msrp FROM products WHERE product_msrp BETWEEN 100 and 500 AND manufacturer_code = ‘SON’ OR manufacturer_code = ‘PAN’ Inclusive

  38. SQL> select product_code, product_description, product_msrp, 2 manufacturer_code 3 from products 4 where manufacturer_code = 'SON' or manufacturer_code = 'PAN' 5 and product_msrp between 100 and 500; PRODUCT_CO PRODUCT_DESCRIPTION PRODUCT_MSRP MAN ---------- ------------------------------ ------------ --- CDP-297 Compact Disc Player 129.95 SON CDP-397 Compact Disc Player 149.95 SON CDP-C225 Disc Jockey CD Changer 199.95 SON CDP-C325 Disc Jockey CD Changer 229.95 SON CDP-C425 Disc Jockey CD Changer 249.95 SON CDP-C525 Disc Jockey CD Changer 299.95 SON PV-2201 HQ VHS Video Cassette Recorde 229.95 PAN PV-4210 4-Head VHS Video Cass Recorde 299.95 PAN PV-4250 HiFi VHS Video Cass Recorder 349.95 PAN SC-T095 Compact Stereo System 139.95 PAN SC-TC430 Compact System w/CD Changer 429.95 PAN SL-S600 Super-Beta Video Recorder 329.95 SON TC-W490 Double Cassette Deck 169.95 SON TC-WR590 Double Cassette Deck 199.95 SON TC-WR690 Double Cassette Deck 249.95 SON TC-WR790 Double Cassette Deck 329.95 SON TC-WR875 Double Cassette Deck 429.95 SON 17 rows selected.

  39. Four ways to build a SELECTION:3. Search for Specific Values Using IN SELECT product_code, product_description FROM products WHERE manufacturer_code IN ('SON', 'PAN', 'BOS'); List of values

  40. SQL> select product_code, product_description 2 from products 3 where manufacturer_code in ('SON', 'PAN', 'BOS'); PRODUCT_CO PRODUCT_DESCRIPTION ---------- ----------------------------------- DVD-A110 DVD/CD Player DVP-S7000 DVD/CD Player DVP-S3000 DVD/CD Player DVP-S500D DVD/CD Player KV-20S40 20" Trinitron TV KV-20V80 20" Digital Comb Filter TV KV-27V22 27" Trinitron TV KV-27V26 27" Trinitron TV KV-27V36 27" Picture-in-Picture TV KV-32V36 32" 1-Tuner PIP TV KV-35V36 35" 1-Tuner PIP TV KV-32XBR48 32" Trinitron XBR TV KV-35XBR48 35" Trinitron XBR TV KV-35XBR88 35" Trinitron XBR TV . . . TC-KE500S Cassette Deck 95 rows selected.

  41. Four ways to build a SELECTION:4. Pattern Match with LIKE • Wildcard characters • % = any sequence of zero or more characters • _ (underscore) = any single character SELECT product_code, product_description FROM products WHERE product_code LIKE ‘C%’ Result: All rows with product codes beginning with C and their corresponding product_description will be retrieved.

  42. SQL> select product_code, product_description from products 2 where product_code like 'C%'; PRODUCT_CO PRODUCT_DESCRIPTION ---------- ------------------------------ CCS-350 Compact System w/CD Player CCS-450 Compact System w/CD Player CCS-550 75-watt System w/CD Changer CD-1000C Compact Disc Changer CDP-297 Compact Disc Player CDP-397 Compact Disc Player CDP-C225 Disc Jockey CD Changer CDP-C325 Disc Jockey CD Changer CDP-C425 Disc Jockey CD Changer CDP-C525 Disc Jockey CD Changer CS-13RX 13" Color Television CS-13SX1 13" Stereo Monitor/Television CS-20SX1 20" Stereo Monitor/Television CT-WN70R 6+1 Cassette Changer 14 rows selected.

  43. Pattern Match with LIKE and UPPER Function • SELECT product_code, product_descriptionFROM productsWHERE UPPER(product_description) LIKE UPPER('%casset%'); Changes to upper case

  44. SQL> select product_code, product_description 2 from products 3 where upper(product_description) like upper('%casset%'); PRODUCT_CO PRODUCT_DESCRIPTION ---------- ----------------------------------- TC-KE400S Cassette Deck TC-KE500S Cassette Deck CT-W606DR Double Cassette Deck CT-W616DR Double Cassette Deck TD-W254 Double Auto-reverse Cassette Deck TD-W354 Double Auto-reverse Cassette Deck TD-W718 Dual Auto-reverse Rec Cassette Deck RS-TR373 Double Auto-reverse Cassette Deck RS-TR575 Double Auto-reverse Cassette Deck K-903 Dual Electronic Cassette Deck TC-WE405 Dual Cassette Deck TC-WE605S Dual Auto-reverse Cassette Deck ••• K-90 Double Cassette Deck 42 rows selected.

  45. Like Without Matching the Case SQL> select product_code, product_description 2 from products 3 where product_description like '%casset%'; no rows selected Cassette cassette

  46. ORDER BY: Sorting the Results SELECT manufacturer_code, product_code, product_description FROM products ORDER BY manufacturer_code, product_code Major (sort) key Minor (sort) key

  47. select manufacturer_code, product_code, product_description 2 from products order by manufacturer_code, product_code; MAN PRODUCT_CO PRODUCT_DESCRIPTION --- ---------- ------------------------------ AIW NSX-D2 Mini Component System AIW XK-S9000 Cassette Deck BOS 301-III Direct/Reflecting Speakers BOS 901Classic Direct/Reflecting Spkr System BOS AM3 Acoustimass Speaker System BOS AM5 Acoustimass 5 Speaker System BOS AM7 Acoustimass 7 Speaker System BOS VS-100 Center Channel Mini Speaker CRV AT-10 Loudspeakers CRV AT-15 Three-way Speaker CRV SW-12B Subwoofer System DA PS-6a Point Source Speaker System DA PS-8c Point Source Speaker Sytem DA PS-9 Point Source Speaker System GMI PVX-31 Single Ch 31/3rd Octave Bands GMI XL-1800QII Prof Manual DJ Turntable GMI XL-BD10 Semi-Auto Belt-Dr Turntable GMI XL-DD20 Semi-Automatic Turntable ... 100 rows selected.

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

  49. 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.

  50. 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

More Related