Guide to Oracle 10g

1. Guide to Oracle 10g Chapter 2: Creating and Modifying Database Tables

2. Database Objects • An Oracle database consists of multiple user accounts • Each user account owns database objects • Tables • Views • Stored programs • Etc.

3. Database Queries • Query: command to perform operation on database object • Create • Modify • View • Delete • Structured Query Language (SQL) • Standard query language for relational databases

4. SQL Command Types • Data Definition Language (DDL) • Used to create and modify the structure of database objects • Data Manipulation Language (DML) • Used to insert, update, delete, and view database data

5. DDL Commands • Used to create and modify the structure of database objects • CREATE • ALTER • DROP • DDL commands execute as soon as they are issued, and do not need to be explicitly saved

6. DML Commands • Used to insert, view, and modify database data • INSERT • UPDATE • DELETE • SELECT • DML commands need to be explicitly saved or rolled back • COMMIT • ROLLBACK

7. User Accounts • Each Oracle database user has a user schema • Area in the database where the user’s database objects are stored • Identified by a unique username and protected by a password • Each user schema is granted specific privileges

8. Types of Database Privileges • System Privileges • Control the operations that the user can perform within the database • Connecting to the database (Create Session), creating new tables, shutting down the database, etc. • Object Privileges • Granted on individual database objects • Controls operations that a user can perform on a specific object (insert data, delete data, etc.) • When you create an object in your user schema, you can then grant object privileges on that object to other database users

9. Oracle Naming Standard • Oracle database objects must adhere to the Oracle Naming Standard • 1 to 30 characters long • Must begin with a character • Can contain characters, numbers, and the symbols $, _, and #

10. Defining Database Tables • To create a table, you must specify: • Table name • Field names • Field data types • Field sizes • Constraints

11. Table and Field Names • Must follow the Oracle Naming Standard • Each table in a user schema must have a unique name within that user schema • Each field in a table must have a unique name within that table

12. Oracle Data Types • Data type: specifies type of data stored in a field • Date, character, number, etc. • Uses • Error checking • Efficient use of storage space

13. Oracle Character Data Types • 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)

14. Character Data Types • CHAR • Fixed-length character data • Maximum size 2000 characters • Must specify maximum width allowed • Adds trailing blank spaces to pad width • Example declaration: student_gender CHAR(1)

15. Character Data Types • NCHAR • Supports 16-digit binary character codes • Used for alternate alphabets

16. Number Data Type • NUMBER • stores values between 10-130 and 10126 • General declaration format: variable_name NUMBER(precision, scale)

17. NUMBER Data Types • 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

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

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

20. Floating Point Numbers • Contain a variable number of decimal places • Precision and scale are omitted • Sample declaration: s_GPA NUMBER

21. Date Data Type • DATE • Stores dates from 1/1/4712 BC to 12/31/4712 AD • Stores both a date and time component • Default date format: DD-MON-YY HH:MI:SS AM • example: 05-JUN-03 12:00:00 AM • Sample declaration: s_dob DATE

22. Specifying Date and Time Values • If no time value is given when a new date is inserted, default value is 12:00:00 AM • If no date value is given when a new time is inserted, default date is first day of current month

23. TIMESTAMP Data Type • The same as Date DT, but it stores also fractional seconds. • Field Timestamp(Fr_Se_Precision) • E.g: ship_dt Timestamp(2) • Fractional Seconds Precision default value is 6 (If omitted).

24. Interval Year to Month Data Type • Field Interval Year(Y_Pr) To Month. • Y_Pr: Year Precision(Default: 6). • E.g: elapsed Interval Year(2) To Month. • Possible Values: +02-11 :add 2 years and 11 months. -11-4:subtract 11 years and 4 months.

25. Interval Day to Second Data Type • Field Interval Day(D_Pr) To Second(Fr_Se_pr). • D_Pr: Day Precision(Default : 2). • Fr_Se_Pr: Fractional Seconds Precision (Default : 6). • Possible value: -04 03:20:32.00 (Days Hours:Minutes:Seconds.Fractions)

26. Large Object (LOB) Data Types • Binary Large Object (BLOB) • Stores up to 4 GB of binary data • Character Large Object (CLOB) • Stores up to 4 GB of character data • BFILE • Stores a reference to a binary file maintained in the operating system • NCLOB • Character LOB that supports 16-bit character code

27. Declaring LOB Data Fields • Item size is not specified • Examples: item_image BLOB item_image BFILE

28. Creating a Database Table • Syntax: CREATE TABLE table_name ( fieldname1 datatype, fieldname2 datatype, …); • Example: CREATE TABLE my_students ( s_id NUMBER(6), s_name VARCHAR2(30), s_dob DATE, s_class CHAR(2));

29. Constraints • Rules that restrict the values that can be inserted into a field • Types of constraints • Integrity: define primary and foreign keys • Value: specify values or ranges of values that can be inserted

30. Constraint Levels • Table constraint • Restricts the value of a field with respect to all other table records • Example: primary key value must be unique for each record • Column constraint • Restricts values in a specific column • Example: values in an S_GENDER field must be ‘M’ or ‘F’

31. Constraint Names • Internal name used by DBMS to identify the constraint • Each constraint name in a user schema must be unique • If you do not name a constraint, the system will automatically generate an unintuitive name

32. Constraint Names • Constraint naming convention: tablename_fieldname_constraintID • Constraint ID values: • Primary key: pk • Foreign key: fk • Check condition: cc • Not NULL: nn • Unique: uk • Example constraint name: my_students_s_id_pk

33. Primary Key Constraints • Table-level • Defining a primary key: CONSTRAINT constraint_name PRIMARY KEY • Example: s_id NUMBER(6) CONSTRAINT student_s_id_pk PRIMARY KEY

34. Primary Key Constraints • Can be defined when field is declared

35. Primary Key Constraints • Can also be defined after all table field definitions are completed

36. Composite Primary Keys • Syntax: CONSTRAINT constraint_name PRIMARY KEY (field1, field2) • Must be defined after fields that compose key are defined

37. Foreign Key Constraints • Table-level • Can only be defined after field is defined as a primary key in another table • Syntax: CONSTRAINT constraint_name REFERENCES primary_key_table_name (field_name)

38. Foreign Key Constraints • Can be defined when field is declared

39. Foreign Key Constraints • Can also be defined after all table field definitions are completed

40. Value Constraints • Column-level • Restricts data values that can be inserted in a field • In general, avoid value constraints because they make the database very inflexible

41. Types of Value Constraints • Check condition: restricts to specific values • Example: s_gender (M or F) CONSTRAINT my_students_s_gender_cc CHECK (s_gender = ‘M’) OR (s_gender = ‘F’) • Not NULL: specifies that a field cannot be NULL • Example: CONSTRAINT my_students_s_dob_nn NOT NULL

42. Types of Value Constraints • Default: specifies a default value that is inserted automatically • Example: s_state CHAR(2) DEFAULT ‘WI’ • Unique • Table constraint • Specifies that a non-primary key field must have a unique value CONSTRAINT consultant_c_email_uk UNIQUE (c_email)

43. SQL*Plus • Oracle SQL command line utility for issuing SQL commands • Starting SQL*Plus

44. Using SQL*Plus • 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’

45. Exiting SQL*Plus • Type exit at SQL> prompt or • Click Close button on SQL*Plus window

46. Oracle Help Resources • Ora.hlp file • Oracle Technology Network (OTN) • http://otn.oracle.com

47. Viewing Table Information • Viewing a table’s structure DESCRIBE table_name;

48. Oracle Data Dictionary • Contains tables that describe the database structure • Is in the SYSTEM user schema • Is automatically updated as users create and modify tables • Cannot be updated directly • Contains views that allow users to retrieve information about the database structure

49. Data Dictionary Views • Views present data in different formats depending on the privileges of the user • USER: shows all objects belonging to the current user • ALL: shows all objects belonging to the current user, as well as objects current user has privileges to manipulate • DBA: allows users with DBA privileges to view objects of all database users

50. Querying the Data Dictionary Views • Syntax: SELECT field1, field2, … FROM privilege_viewname;