Enhanced Guide to Oracle

1. Enhanced Guide to Oracle Creating and Modifying Database Objects

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 • DDL 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, 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. Creating New User Accounts • Done by DBA • Syntax: CREATE username IDENTIFIED BY password;

11. Example Oracle System Privileges

12. Granting System Privileges • Done by DBA • Syntax: GRANT privilege1, privilege2, … TO username;

13. Database Roles • Role is a database object that can be assigned system privileges • Role is then assigned to a user, and the user inherits the role’s privileges • Used to easily assign groups of related privileges to users

14. Creating Roles • Syntax: CREATE ROLE role_name;

15. Assigning Privileges to a Role • Syntax: GRANT privilege1, privilege2, … TO role_name;

16. Assigning a Role to a User • Syntax: GRANT role_name TO user_name;

17. Revoking System Privileges • Syntax: REVOKE privilege1, privilege2, … FROM username;

18. Administering System Privileges • To be able to grant system privileges to other users, a user account must have been granted the privilege WITH ADMIN OPTION

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

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

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

22. 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)

23. 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)

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

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

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

27. 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)

28. 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)

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

30. Date Date 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

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

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

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

34. 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));

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

36. 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’

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

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

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

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

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

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

43. 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)

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

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

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

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

48. 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)

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

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