1 / 32

A GUIDE TO ORACLE8

CHAPTER 2: Creating and Modifying Database Tables. A GUIDE TO ORACLE8. 2. Query: Command to perform database operation insert modify delete view Structured Query Language (SQL) Standard query language for relational databases. Creating and Modifying Database Tables. 2.

khuong
Download Presentation

A GUIDE TO ORACLE8

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. CHAPTER2: Creating and Modifying Database Tables A GUIDE TO ORACLE8 2

  2. Query: Command to perform database operation insert modify delete view Structured Query Language (SQL) Standard query language for relational databases Creating and Modifying Database Tables 2

  3. Table specifications table name field names field data types field sizes field constraints Oracle Database Tables 2

  4. 1-30 characters Alphanumeric characters and restricted symbols $ _ # Must begin with a character Cannot be a reserved word Oracle Table and Field Names 2

  5. Data type: Specifies type of data stored in a field Error checking Efficient use of storage space Oracle Data Types 2

  6. VARCHAR2 Variable-length character strings Maximum of 4,000 characters Must specify maximum width allowed No trailing blank spaces are added Example declaration: student_name VARCHAR2(30) Character Data Types 2

  7. CHAR Fixed-length character data Maximum size 255 characters Must specify maximum width allowed Adds trailing blank spaces to pad width Example declaration: s_gender CHAR(1) Character Data Types 2

  8. NCHAR Supports 16-digit binary character codes Used for alternate alphabets LONG Stores up to 2 GB of variable-length character data Each table can have only one LONG field Character Data Types 2

  9. NUMBER Stores values between 10-130 and 10126 General declaration format: variablename NUMBER(precision, scale) Number Data Type 2

  10. Number type (integer, fixed point, floating point) specified by precision and scale Precision: Total number of digits on either side of the decimal point Scale: Number of digits to right of decimal point Number Data Type 2

  11. Whole number with no digits to right of decimal point Precision is maximum width Scale is omitted Sample declaration: s_age NUMBER (2) Integer Numbers 2

  12. Contains a specific number of decimal places Precision is maximum width Scale is number of decimal places Sample declaration: item_price NUMBER(5, 2) Fixed-Point Numbers 2

  13. Contains a variable number of decimal places Precision and scale are omitted Sample declaration: s_GPA NUMBER Floating-Point Numbers 2

  14. DATE Stores dates from 1/1/4712 BC to 12/31/4712 AD Default date format: DD-MON-YY Example: 05-JUN-01 Sample declaration: s_dob DATE Date Data Type 2

  15. DATE data type also stores time values If no time value is given when a date is inserted, default value is 12:00:00 AM If no date value is given when a time is inserted, default date is first day of current month Date Data Type 2

  16. BLOB: Binary LOB, up to 4 GB of binary data in database CLOB: Character LOB, up to 4 GB of character data in database BFILE: Reference to binary file stored in operating system NCLOB: Character LOB supporting 16-bit character codes Large Object (LOB) Data Types 2

  17. Specify input and output formats for data values Common NUMBER format masks Format MaskFormatted Data 99,999 12,345 $99,999.99 $12,345.00 99,999PR <12,345> 99,999MI -12,345 $99,999.99PR <$12,345.00> Format Masks 2

  18. Common DATE format masks Format MaskFormatted Data DD-MON-YY 05-JUN-01 DD-MON-YYYY 05-JUN-2001 MM/DD/YY 06/05/2001 HH:MI AM 02:30 PM MONTH DAY, YYYY JUNE 5, 2001 MM/DD/YY HH:MI AM 06/05/01 02:30 PM Format Masks 2

  19. Common character format masks with embedded characters Format MaskFormatted Data Social Security Number: FM999”-”999”-”9999 555-555-5555 Phone Number: FM”(“999”) “999”-”9999 (715) 555-5555 Format Masks 2

  20. Used to define primary and foreign keys Constraint name: Internal name used by DMBS to identify the constraint Constraint name convention: tablename_fieldname_constraintID Constraint ID values: Primary key: PK Foreign key: FK Integrity Constraints 2

  21. Defining a primary key: CONSTRAINT <constraint name> PRIMARY KEY Example: sid NUMBER(6) CONSTRAINT student_sid_pk PRIMARY KEY Primary Key Constraints 2

  22. Defining a composite primary key: CONSTRAINT <constraint name> PRIMARY KEY (field1, field2) Example: sid NUMBER(6), course_id NUMBER(6), grade NUMBER , CONSTRAINT enrollment_sid_course_id_pk PRIMARY KEY (sid, course_id) Primary Key Constraints 2

  23. Defining a foreign key: CONSTRAINT <constraint name> REFERENCES <table where field is a PK>(<field name>) Example: advisorid NUMBER(6) CONSTRAINT student_advisorid_fk REFERENCES faculty(fid) Foreign Key Constraints 2

  24. Restricts data values that can be inserted into a field Types Check condition: Restricts to specific values example: s_gender (M or F) NOT NULL: Specifies that a field cannot be NULL Value Constraints 2

  25. Check condition CONSTRAINT <constraint name> CHECK <values> s_gender CHAR(1) CONSTRAINT student_s_gender_cc CHECK ((s_gender = ‘M’) OR (s_gender = ‘F’)) Not NULL CONSTRAINT <constraint_name> NOT NULL s_name VARCHAR2(30) student_s_name_nn NOT NULL Defining Value Constraints 2

  26. Oracle SQL command line utility Starting SQL*Plus SQL*Plus 2

  27. All commands must be terminated with a semicolon Use a text editor and copy and paste commands Character data is case sensitive and must be in single quotes ‘M’ ‘Sarah’ SQL*Plus 2

  28. CREATE TABLE <tablename> (<field1 declaration>, <field2 declaration, …); CREATE TABLE mystudent (sid NUMBER(6) CONSTRAINT mystudent_sid_pk PRIMARY KEY, s_name VARCHAR2(30) CONSTRAINT mystudent_s_name_nn NOT NULL); Creating a Database Table 2

  29. Viewing a table’s structure DESCRIBE mystudent; Viewing constraint information SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘MYSTUDENT’; Deleting a table DROP TABLE mystudent; Other Table Operations 2

  30. Prohibited Changing the table name Changing a column name Unrestricted Adding a new column Deleting a primary key or foreign key constraint Modifying Tables 2

  31. Restricted (allowed only if existing data fits new specification) Changing a column’s data type, size, and default value Adding a primary key constraint Adding a foreign key constraint Adding a CHECK CONDITION constraint Adding a NOT NULL constraint Modifying Tables 2

  32. Type exit at SQL> prompt or Click Close button Exiting SQL*Plus 2

More Related