700 likes | 712 Views
Learn about control files, data types, and creating a table in Oracle. Understand integrity constraints and SQL terminology.
E N D
Week 5September 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 {
Oracle Control Files • Loads data into tables in batch mode • Two parts • Control language • Data
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
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
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
Basic Data Types in Oracle Other data types exist
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
Integrity Constraints • Imposed to protect the database from becoming inconsistent • Types: • Required data • Domain constraints • Entity constraints • Referential integrity • Enterprise constraints
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.
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.
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.
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
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
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
Integrity Constraints • Enterprise constraints • Organizational constraints (i.e., business rules)
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
Example of Creating a Table with Referential Integrity Constraints Two ways of imposing constraints Table name Column name Referential integrity constraints Constraint name (index)
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
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
Adding a Column ALTER TABLEtable-nameADD (column-name data-type) Table name Optional constraint Data type Column name
Modifying a Column Definition ALTER TABLEtable-nameMODIFY (column-name data-type) Table name New definition Column name
Deleting a Column ALTER TABLEtable-nameDROP (column-name) Table name Column name
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)
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)
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
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.
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)
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%’
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
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’
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.
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))
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.
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.
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
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.
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
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.
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.
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.
Pattern Match with LIKE and UPPER Function • SELECT product_code, product_descriptionFROM productsWHERE UPPER(product_description) LIKE UPPER('%casset%'); Changes to upper case
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.
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
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
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.
Using DISTINCT SELECT DISTINCT(manfuacturer_code) FROM products Result: The non-duplicated manufacturer_codes will be retrieved.
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.
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