1 / 42

Creating and Modifying Database Tables

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

ophira
Download Presentation

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. Creating and ModifyingDatabase Tables Chapter 2 A Guide to Oracle9i

  2. Objectives • Become acquainted with Structured Query Language (SQL) • Learn about Oracle9i user schemas • Learn how to define Oracle9i database tables • Create database tables using SQL*Plus A Guide to Oracle9i

  3. Objectives • Learn how to debug Oracle9i SQL commands and use Oracle Corporation online help resources • Learn how to view information about your database tables using Oracle9i data dictionary views • Modify and delete database tables using SQL*Plus A Guide to Oracle9i

  4. Introduction to SQL • Structured Query Language (SQL): the standard query language for relational databases • Data definition language (DDL) • Create new database objects • Modify or delete existing objects. • Data manipulation language (DML) • Insert, update, delete, and view database data. A Guide to Oracle9i

  5. Oracle9i 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 A Guide to Oracle9i

  6. Defining Oracle9i Database Tables • To create a new table specify: • Table name • Name of each data field • Data type and size of each data field • Constraints: restrictions on the data values that a field can store • Oracle Naming Standard: • Series of rules Oracle Corporation established for naming all database objects • Objects must be from 1 to 30 characters long • Can contain letters, numbers, and the special symbols $, _, and # • Must begin with a character A Guide to Oracle9i

  7. Creating a Table CREATE TABLE tablename (fieldname1data_type, (fieldname2 data_type, …) A Guide to Oracle9i

  8. Oracle9i 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 A Guide to Oracle9i

  9. Character Data Types • 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) A Guide to Oracle9i

  10. Character Data Types • CHAR • Fixed-length character data up to a maximum size of 2,000 characters • 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)] A Guide to Oracle9i

  11. Character Data Types • NVARCHAR2 and NCHAR • Analogous to VARCHAR2 and CHAR but use Unicode rather than ASCII • Used to hold character data in languages other than English A Guide to Oracle9i

  12. Number Data Types • Stores negative, positive, fixed, and floating point numbers between 10–130 and 10125, 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 A Guide to Oracle9i

  13. Date and Time Data Types • DATE • Dates from December 31, 4712 BC to December 31, 4712 AD • Default format DD-MON-YY • Default time format HH:MI:SS A.M. • fieldname DATE • TIMESTAMP • Similar to DATE but stores fractional seconds • fieldname TIMESTAMP (fractional_seconds_precision) A Guide to Oracle9i

  14. Date and Time Data Types • 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 • 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)] A Guide to Oracle9i

  15. Large Object (LOB) Data Types A Guide to Oracle9i

  16. Constraints • Rules that restrict the data values that you can enter into a field in a database table • 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 A Guide to Oracle9i

  17. Oracle Constraint Naming Convention • tablename_ fieldname_constraintID A Guide to Oracle9i

  18. Integrity Constraints • Define primary key fields • Specify foreign keys and their corresponding table and column references • Specify composite keys A Guide to Oracle9i

  19. Value Constraints • 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 A Guide to Oracle9i

  20. Creating Database Tables Using SQL*Plus • Type SQL commands at the SQL prompt • End each command with a semicolon (;) • Not case sensitive A Guide to Oracle9i

  21. Log On to SQL*Plus A Guide to Oracle9i

  22. SQL*Plus Program Window A Guide to Oracle9i

  23. Create a Table A Guide to Oracle9i

  24. Table Creation Sequence • When creating tables with foreign key references, create referenced tables first • Always create tables without foreign keys before those with foreign keys A Guide to Oracle9i

  25. Using Notepad • Useful to use Notepad to edit sql commands • Commands can be edited without retyping • Commands can be saved • Saving multiple sql commands in a file creates a script A Guide to Oracle9i

  26. Errors • When an error occurs error information is displayed: • Line number • Position on line • Error code • Description of error • Error codes • 3 letter prefix (I.e. ORA) • 5 digit code • More information on errors can be found at http://otn.oracle.com A Guide to Oracle9i

  27. Exiting SQL*Plus • Three ways to exit SQL*Plus: • Type exit at the SQL prompt • Click File on the menu bar, and then click Exit • Click the Close button on the program window title bar • Database session ends when SQL*Plus exits A Guide to Oracle9i

  28. Create Table with Foreign Key Constraint A Guide to Oracle9i

  29. Viewing Information About Tables • describe tablename: displays column names and data types • Data dictionary: tables that contain information about the structure of the database. • USER: shows the objects in the current user’s schema • ALL: shows both objects in the current user’s schema and objects that the user has privileges to manipulate • DBA: allows users who are database administrators to view information about all database objects A Guide to Oracle9i

  30. Viewing Tables in the Database A Guide to Oracle9i

  31. Viewing Constraints on One Table A Guide to Oracle9i

  32. Modifying and Deleting Database Tables • Modify existing database tables by: • Changing the name of a table • Adding new columns • Deleting columns that are no longer needed • Changing the data type or maximum size of an existing column • Unrestricted action: some specifications can always be modified • Restricted action: specifications modified only in certain situations A Guide to Oracle9i

  33. Unrestricted Action A Guide to Oracle9i

  34. Restricted Actions A Guide to Oracle9i

  35. Deleting and Renaming Tables • To delete: • Drop table [tablename] • Use with caution • To delete foreign key constraints, add “cascade constraints” • To rename: • Rename old_tablename to new_tablename • DBMS automatically transfers to the new table integrity constraints, indexes, and privileges that referenced the old table. A Guide to Oracle9i

  36. Adding Fields to Existing Tables • To add a field: • ALTER TABLE tablename ADD(fieldname data_declaration constraints); A Guide to Oracle9i

  37. Modifying Existing Field Data Definitions • Can only change datatype to compatible data type (i.e. varchar2 to char) • ALTER tablename MODIFY(fieldname new_data_declaration); A Guide to Oracle9i

  38. Deleting a Field • ALTER TABLE tablename DROP COLUMN fieldname; • Can be used to rename a field – first drop, then add A Guide to Oracle9i

  39. Adding and Deleting Constraints • Add a constraint: ALTER TABLE tablename ADD CONSTRAINT constraint_name constraint_definition; • Remove a constraint: ALTER TABLE tablename DROP CONSTRAINT constraint_name; A Guide to Oracle9i

  40. Enabling and Disabling Constraints • When modifying a database it can be useful to disable constraints • Constraints are enabled by default • To disable a constraint: ALTER TABLE tablename DISABLE CONSTRAINT constraint_name; • To enable a constraint: ALTER TABLE tablename ENABLE CONSTRAINT constraint_name; A Guide to Oracle9i

  41. Summary • SQL commands include: • Data description language (DDL) commands: create, modify, Deleted database objects • Data manipulation language (DML) commands: insert, update, delete, view database data • To create a table: • specify the table name, the name of each data field, and the data type and size of each data field • Data types ensure correct data values • Constraints restrict values of database fields • SQL*Plus commands are not case sensitive A Guide to Oracle9i

  42. Summary • Errors include line number, position, error code • Use DESCRIBE command to display a table’s fieldnames and data types • Tables can be modified or deleted but some changes are restricted A Guide to Oracle9i

More Related