1 / 76

Chapter 2: Creating and Modifying Database Tables

Chapter 2: Creating and Modifying Database Tables. Objectives. Become acquainted with Structured Query Language (SQL) Learn about Oracle10g user schemas Learn how to define Oracle10g database tables Create database tables using SQL*Plus. Objectives (cont.).

socorro-xovi
Download Presentation

Chapter 2: Creating and Modifying Database Tables

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. Chapter 2:Creating and ModifyingDatabase Tables Mr. Ashraf Yaseen

  2. Objectives • Become acquainted with Structured Query Language (SQL) • Learn about Oracle10guser schemas • Learn how to define Oracle10gdatabase tables • Create database tables using SQL*Plus Mr. Ashraf Yaseen

  3. Objectives (cont.) • Learn how to debug Oracle10gSQL commands and use Oracle Corporation online help resources • Learn how to view information about your database tables using Oracle10gdata dictionary views • Modify and delete database tables using SQL*Plus Mr. Ashraf Yaseen

  4. Database Objects and Queries • An Oracle database consists of multiple user accounts • Each user account owns database objects • Tables • Views • Stored programs • Etc. • Query: command to perform operation on database object • Structured Query Language (SQL) • Industry standard query language for most of relational databases Mr. Ashraf Yaseen

  5. Basic SQL Concepts and Commands SQL (Structured Query Language) is used to manipulate the database. There are two basic types of SQL commands: DDL commands work with the structure of the objects (tables, indexes, views) in the database. DML commands work with the data in the database (i.e.,manipulate the data). • Data Definition Language (DDL) • Data Manipulation Language (DML) Mr. Ashraf Yaseen

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

  7. 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 • SAVEPOINT Mr. Ashraf Yaseen

  8. Security -Granting Table Privileges • Security is the prevention of unauthorized access to the database. Within an organization, the database administrator determines the types of access various users need for the database. • Some users might be able to retrieve and update data in the database. Other users might be able to retrieve any data from the database but not make any changes to it. Still other users might be able to access only a portion of the database. Mr. Ashraf Yaseen

  9. Oracle10g User Accounts • User account - identified bya unique username and password • User schema - all of the objects that the user creates and stores in the database • Object owner has privileges to perform all possible actions on an object Mr. Ashraf Yaseen

  10. Break Time: SQL Plus • Oracle SQL command line utility for issuing SQL commands • Starting SQL Plus LOGON to YOUR Oracle Account Mr. Ashraf Yaseen

  11. How to Access Your Oracle Account User Name: Password: Host string: 1. Click the START button, point to Programs 2. Select Oracle –Oracle10g, then 3. Click Application Development, then 4. Select SQL PLUS Mr. Ashraf Yaseen

  12. Types of Database Privileges • System Privileges • Control the operations that the user can perform within the database • Create user accounts • 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 Mr. Ashraf Yaseen

  13. Creating New User Accounts • Done by DBA • Syntax: CREATE username IDENTIFIED BY password; Mr. Ashraf Yaseen

  14. Defining Oracle10g Database Tables • To create a table, you must specify: • Table name • Field names • Field data types • Field sizes • Constraints • restrictions on the data values that a field can store Mr. Ashraf Yaseen

  15. Creating a Table CREATE TABLEtablename (fieldname1 data_type, (fieldname2 data_type, …) Mr. Ashraf Yaseen

  16. Oracle Naming standers and Conventions Naming standards are Series of rules Oracle Corporation established for naming all database objects • From 1 to 30 characters • Only alphanumeric characters, and special characters ($ , _, #) • Must begin with a letter and can not contain blank spaces or hyphens • Are the following names valid? Why?customer ordercustomer-order#order Mr. Ashraf Yaseen

  17. Oracle10g Data Types • Data type:specifies the kind of data that a field stores • Assigning a data type provides a means for error checking • Data types enable the DBMS to use storage space more efficiently by internally storing different types of data in different ways Mr. Ashraf Yaseen

  18. Data Types • Built-in • provided by the system • Library • built by the software vendor or a third party • User-defined • built by users Mr. Ashraf Yaseen

  19. Oracle Data Types • Data type: specifies type of data stored in a field • Date, character, number, etc. • Uses/Purposes • Error checking • Efficient use of storage space Mr. Ashraf Yaseen L

  20. Basic Built-In Data Types • Character • VARCHAR2 • CHAR • NVARCHAR2 / NCHAR • Numeric • NUMBER • Date/Time • Others: • LONG, RAW, LONG RAW, BLOB Mr. Ashraf Yaseen

  21. Character Data Types • 1. VARCHAR2 • Stores variable-length character data up to a maximum of 4,000 characters • Values in different records can have a different number of characters • fieldname VARCHAR2(maximum_size) • (e.g.) emp_name VARCHAR2(20); • an instance: ‘Jason Chen’ Mr. Ashraf Yaseen

  22. [optional] Character Data Types (cont.) • 2. CHAR • Fixed-length character data (<= 2000 characters) • default is 1 if no field size is specified • Data values for different records all have the same number of characters • DBMS adds trailing blank spaces to the end of the entry to make the entry fill the maximum_sizevalue • Data longer than maximum_size causes an error • fieldname CHAR[(maximum_size)] • pros: use data storage space more efficiently and processed faster • cons: causes inconsistent query results in other Oracle applications • e.g. s_class CHAR(2); • ‘SR’ ‘JR’ ‘SO’ ‘FR’ ‘GR’ • s_state CHAR(2) DEFAULT ‘WI’; • student_gender CHAR; Mr. Ashraf Yaseen

  23. Character Subtypes • Examples: • VARCHAR2(5) ‘Smith’ or ‘Smi’ • CHAR(5) ‘Smith’ or ‘Smi ’ Mr. Ashraf Yaseen

  24. Question: Which query will possibly generate student information? • What data type should be used if there is any chance that all column spaces will NOT be filled? • Answer: VARCHAR2 s_last VARCHAR2(15); SELECT s_last, s_first, s_address FROM student WHERE s_last = ‘Smith’; s_last CHAR(15); SELECT s_last, s_first, s_address FROM student WHERE s_last = ‘Smith’; Mr. Ashraf Yaseen L

  25. When use Query: SELECT s_last, s_first, ssn, telephone FROM student WHERE s_last = ‘Smith’; • Case is sensitive within the single quotation. • SQL Plus commands are NOT case sensitive, but Query within the single quotation are case sensitive. Mr. Ashraf Yaseen

  26. Character Data Types (cont.) • 3. NVARCHAR2 and NCHAR • Analogous to VARCHAR2 and CHAR but use Unicode rather than ASCII • Used to hold character data in languages other than English Mr. Ashraf Yaseen

  27. 4. Number Data Types • Stores negative, positive, fixed, and floating point numbers between 10 -130 and10 +125 with precision up to 38 decimal places • General Syntax: fieldname NUMBER [([precision,] [scale])] • Integer: fieldname NUMBER(precision) • Fixed point: fieldname NUMBER[([precision],[scale])] • Floating point: fieldname NUMBER Mr. Ashraf Yaseen

  28. Number Data Types (examples) • a) Integer: Number (n) • e.g. s_id NUMBER(5) • 12345 • b) Fixed-point numbers • e.g. current_price NUMBER (5, 2); • 259.99 33.89 • c) Fixed-point numbers (cont.) • e.g. total_mileage NUMBER (5, 1); • 259.9 33.8 • d) Floating-point Number – with a variable number of decimal places • e.g. s_gpa NUMBER; • 3.89 2.7569 3.2 Mr. Ashraf Yaseen

  29. 5. Date and Time Data Types • DATE • Dates from December 31, 4712 BC to December 31, 4712 AD • Stores the century, year, month, day, hour, minute and second. • Default format DD-MON-YY • Default time format HH:MI:SS A.M. • fieldname DATE Sample declaration: s_dob DATE; Use one of the following format masks: • TO_DATE (‘ ’, ‘MM/DD/YY’) • TO_DATE (‘ ‘, ‘DD-MON-YYYY’) • TO_DATE (‘ ‘, ‘HH:MI AM’) Mr. Ashraf Yaseen

  30. Date and Time Data Types (cont.) • TIMESTAMP • Similar to DATE but stores fractional seconds fieldname TIMESTAMP (fractional_seconds_precision) • If you omit the fractional_seconds_precision specification, the default value is 6 decimal places. • E.g: s1_date_received TIMESTAMP(2); Mr. Ashraf Yaseen

  31. Date and Time Data Types (cont.) • INTERVAL YEAR TO MONTH • Time interval, expressed in years and months • +02-11 specifies a positive time interval of 2 years and 11 months • fieldname INTERVAL YEAR[(year_precision)] TO MONTH • e.g., Software Expert database (p.48) elapsed_time INTERVAL YEAR(2) TO MONTH; • INTERVAL DAY TO SECOND • Time interval, expressed in days, hours, minutes, and seconds • -04 03:20:32.00: 4 days, 3 hours, 20 minutes, and 32 seconds • fieldname INTERVAL DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)] • e.g. • DDL: • elapsed_time INTERVAL DAY(6) TO SECOND, • DML: • TO_DSINTERVAL('53 00:00:00.00') Mr. Ashraf Yaseen

  32. 6. Large Object (LOB) Data Types Mr. Ashraf Yaseen

  33. What is a Constraint? • A rule that restricts the values that can be inserted into a field • A mechanism used to protect • the relationship between data within an Oracle table, or • the correspondence between data in two different tables. • For example, the state entered must be one of the 50 states in the U.S. Mr. Ashraf Yaseen

  34. Types of Constraints • Integrity constraints: define primary and foreign keys • Value constraints: define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL • Table constraint: restricts the data value with respect to all other values in the table • Field constraint: limits the value that can be placed in a specific field, irrespective of values that exist in other table records Mr. Ashraf Yaseen

  35. Creating a Table CREATE TABLEtablename (fieldname1 data_type [CONSTRAINT constraint_name] CONSTRAINT_TYPE, fieldname2 data_type [CONSTRAINT constraint_name] CONSTRAINT_TYPE,,…) CREATE TABLEtablename (fieldname1 data_type, fieldname2 data_type, [CONSTRAINT constraint_name] CONSTRAINT_TYPE(coulmn_name)); Mr. Ashraf Yaseen

  36. Integraity constraint Value constraint I. Naming conventions for constraints <tablename>_<fieldname>_<constraint id> Where <constraint id> is: • pk PRIMARY KEY • fk REFERENCES <tablename> (pk) • cc CHECK <condition to be checked> (note that cc stands for CHECKCONDITION) • nn NOT NULL • uk UNIQUE e.g., s_id NUMBER (6) CONSTRAINT student_s_id_pkPRIMARY KEY; Mr. Ashraf Yaseen

  37. Oracle Constraint Naming Convention • tablename_ fieldname_constraintID Mr. Ashraf Yaseen

  38. Integrity Constraints • Define primary key fields • Specify foreign keys and their corresponding table and column references • Specify composite keys Mr. Ashraf Yaseen

  39. Primary Key Constraints • Table-level • Can be defined when field is declared • Syntax: CONSTRAINT constraint_name PRIMARY KEY • SQL> CREATE TABLE my_students • 2 (s_id NUMBER(6) CONSTRAINT my_students_s_id_pk PRIMARY KEY, • 3 s_name VARCHAR2(30), • s_class CHAR(2), • s_dob DATE); Mr. Ashraf Yaseen

  40. Primary Key Constraints (cont.) • Can also be defined after all table field definitions are completed • SQL> CREATE TABLE my_students • (s_id NUMBER(6) CONSTRAINT my_students_s_id_pkPRIMARY KEY, • 3 s_name VARCHAR2(30), • s_class CHAR(2), • s_dob DATE); • SQL> CREATE TABLE my_students • 2 (s_id NUMBER(6), • 3 s_name VARCHAR2(30), • s_class CHAR(2), • s_dob DATE), • CONSTRAINT student_s_id_pkPRIMARY KEY (s_id)); Mr. Ashraf Yaseen

  41. Primary Key Constraints (cont.) You will learn how to create the following SQL (DDL) command: CREATE TABLE location (loc_id NUMBER(6), bldg_code VARCHAR2(10), room VARCHAR2(6), capacity NUMBER(5), CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id)); Mr. Ashraf Yaseen

  42. 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) Mr. Ashraf Yaseen

  43. Foreign Key Constraints • Can be defined when field is declared CREATE TABLE faculty (f_id NUMBER(6) CONSTRAINT faculty_f_id_pkPRIMARY KEY(f_id), f_last VARCHAR2(30), f_first VARCHAR2(30), f_mi CHAR(1), loc_id NUMBER(5) CONSTRAINT faculty_loc_id_fk REFERENCES location(loc_id) f_phone VARCHAR2(10), f_rank VARCHAR2(8), f_pin NUMBER(4), f_image BLOB, ); SQL> SELECT TABLE_NAME FROM USER_TABLES; N Mr. Ashraf Yaseen

  44. Foreign Key Constraints (cont.) • Can also be defined after all table field definitions are completed CREATE TABLE faculty (f_id NUMBER(6), f_last VARCHAR2(30), f_first VARCHAR2(30), f_mi CHAR(1), loc_id NUMBER(5), f_phone VARCHAR2(10), f_rank VARCHAR2(8), f_pin NUMBER(4), f_image BLOB, CONSTRAINT faculty_f_id_pkPRIMARY KEY(f_id), CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id)); SQL> SELECT TABLE_NAME FROM USER_TABLES; Mr. Ashraf Yaseen

  45. 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 Mr. Ashraf Yaseen

  46. Types of Value Constraints • Check condition: restricts to specific values • CONSRAINT student_s_class_cc CHECK ((s_class='FR') OR (s_class='SO') OR (s_class='JR') OR (s_class=’SR')); • CONSTRAINT course_credits_cc CHECK ((credits>0) AND (credits <=18)); • 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 • Must be created in column declaration. • s_last VARCHAR2(30) CONSTRAINT student_s_last_nn NOT NULL; • CONSTRAINT my_students_s_dob_nn NOT NULL; Mr. Ashraf Yaseen

  47. Types of Value Constraints (cont.) • Unique • Table constraint • Specifies that a non-primary key field must have a unique value • CONSTRAINT term_term_desc_uk UNIQUE (term_desc); • Default: specifies a default value that is inserted automatically • Must be created in the column declaration • s_state CHAR(2) DEFAULT ‘WI’; Mr. Ashraf Yaseen

  48. Summary on Value Constraints (cont.) • Check conditions: field value must be a specific value or fall within a range of values • NOT NULL constraints: specify whether a field value can be NULL • Default constraints: specify that a field has a default value that the DBMS automatically inserts for every record, unless the user specifies an alternate value • Unique constraints: specify that a field must have a unique value for every table record Mr. Ashraf Yaseen

  49. Creating Database Tables Using SQL*Plus • Type SQL commands at the SQL prompt • End each command with a semicolon (;) • Not case sensitive Mr. Ashraf Yaseen

  50. Oraclelab Log On to SQL*Plus Mr. Ashraf Yaseen

More Related