Chapter 3: Table Creation and Management: Creating and Modifying Database Tables

1. Chapter 3: Table Creation and Management: Creating and ModifyingDatabase Tables Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu

2. Objectives • Become acquainted with Structured Query Language (SQL) • Identify the table name and structure • Create a new table using the CREATE TABLE command • Use a subquery to create a new table • Add a column to an existing table • Modify the definition of a column in an existing table • Delete a column from an existing table • Mark a column as unused and then delete it at a later time • Rename a table • Truncate a table • Drop a table

3. 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 • Consists of about 30 commands

4. 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). Reserved words - SQL command words • Data Definition Language (DDL) • Data Manipulation Language (DML)

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

6. Oracle11g 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 • Database objects • Also called schema objects • Objects in user schema • Object owner has privileges to perform all possible actions on an object

7. A. Names and PropertiesWhy need a name?

8. Creating a Table CREATE TABLE tablename (fieldname1 data_type (size), fieldname2 data_type (size), …);

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

10. I. Names and Properties: Conventions 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 • Must be unique and No reserved words are allowed • Are the following names valid? Why? customer order customer-order #order Customer_# Customer#

11. Oracle 11g Data Types • Data type • Specifies kind of data that column stores • Provides means for error checking • Enable DBMS to use storage space more efficiently by internally storing different types of data in different ways • Basic types • Character • Number • Date/time

12. II. Data Types • Built-in • provided by the system • Library • built by the software vendor or a third party • User-defined • built by users

13. Basic Built-In Data Types • Character • VARCHAR2 • CHAR • Numeric • NUMBER • DATE • OTHERS: • LOB, BLOB, LONG, RAW, LONG RAW

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

15. [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’ • State CHAR(2) DEFAULT ‘WA’; • student_gender CHAR;

16. Character Subtypes • Examples: • VARCHAR2(5) ‘Smith’ or ‘Smi’ • CHAR(5) ‘Smith’ or ‘Smi ’ • LONG ‘Smith...’ • Note that you do not need to specify a size for LONG. • To include a single quote in a literal character string, use two in a row: • ‘This is Herald’’s string.’

17. 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: ______________ 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’; VARCHAR2 L

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

19. 3. Number Data Types • Stores negative, positive, fixed, and floating point numbers between 10 -130 <= <=10 +126 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

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

21. 4. Date and Time Data Types • Date, time data subtypes • Store actual date and time values • 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 • Sample declaration: • OrderDate DATENOT NULL; • Use one of the following format masks: • TO_DATE (‘ ’, ‘MM/DD/YY’) • TO_DATE (‘ ‘, ‘DD-MON-YYYY’) • TO_DATE (‘ ‘, ‘HH:MI AM’)

22. Table Design (continued) Table 3-2 Oracle 11g Datatypes

23. Table Creation Figure 3-1 CREATE TABLE syntax Defining Columns • Column definition list must be enclosed in parentheses • Datatype must be specified for each column • Maximum of 1,000 columns

24. Exercise: Create a new table of “acctmanager” based on the following information acctmanager acctmanager CREATE TABLE ( … NOT NULL); amid GROUP WORK Complete the CREATE command manually!

25. Exercise: Create a new table of “acctmanager” based on the following information acctmanager acctmanager CREATE TABLE ( amfirst VARCHAR2(12) NOT NULL, amlast VARCHAR2(12) NOT NULL, amedate DATE DEFAULT SYSDATE, amsal NUMBER(8,2), amcomm NUMBER(7,2) DEFAULT 0, region CHAR(2) NOT NULL); (4) amid VARCHAR2 PRIMARY KEY, SELECT * FROM acctmanager;

26. Refresh the Database • 1. Create a new folder on c:\ as follows: c:\oradata\chapter3 • 2. Go to Blackboard and download data files from Oracle chapter3 and save under c:\oradata\chapter3\ • 3. Run the following script file • Start c:\oradata\chapter3\JLDB_Build_3.sql

27. Exercise – Your Turn • Type the following commands: 1) SELECT TABLE_NAME FROM USER_TABLES; 2) DROP TABLE ACCTMANAGER CASCADE CONSTRAINTS; 3) DROP TABLE ACCTMANAGER2 CASCADE CONSTRAINTS; 4) SELECT TABLE_NAME FROM USER_TABLES; You now are able to create acctmanager table

28. CREATE TABLE Command Example What is the difference between these two versions: Virtual Column/ (Derived/computed) Is this a good approach? Figure 3-2 The creation of the ACCTMANAGER table

29. Viewing Table Structures: DESCRIBE • DESCRIBE displays the structure of a specified table Figure 3-4 The DESCRIBE command

30. Table Creation through Subqueries • You can use subqueries to retrieve data from an existing table • Requires use of AS keyword • New column names can be assigned CREATE TABLE…AS Figure 3-8 CREATE TABLE … AS command syntax

31. CREATE TABLE…AS Command Example Figure 3-7 Creating a table based on a subquery SELECT * FROM cust_mkt;

32. Modifying Existing Tables • Accomplished through the ALTER TABLE command • Use an ADD clause to add a column • Use a MODIFY clause to change a column • Use a DROP COLUMN to drop a column • both “column” and its “data values” are deleted ALTER TABLE Command Syntax Figure 3-10 Arithmetic operations with NULL values

33. ALTER TABLE…ADD Command Figure 3-12 The ALTER TABLE … ADD command

34. ALTER TABLE…MODIFY Command Figure 3-18 The ALTER TABLE … MODIFY command to increase the column width

35. Modification Guidelines • Column must be as wide as the data it already contains • If a NUMBER column already contains data, size cannot be decreased • Adding or changing default data does not affect existing data

36. ALTER TABLE…SET UNUSED Command • Once marked for deletion, a column cannot be restored • Storage space is freed at a later time 36

37. ALTER TABLE…DROP UNUSED Command • Frees up storage space from columns previously marked as unused However, once a table is set “UNUSED” it can’t be DROPPED using the following (regular) command: ALTER TABLE tablename DROP COLUMN colname; Then, what command can drop the “UNUSED” columns? ALTER TABLE author DROP UNUSED COLUMNS; Show: test_UNUSED.sql and test_UNUSED.docx

38. AUTHOR SQL> SELECT * FROM author; AUTH LNAME FNAME ---- ---------- ---------- S100 SMITH SAM J100 JONES JANICE A100 AUSTIN JAMES M100 MARTINEZ SHEILA K100 KZOCHSKY TAMARA P100 PORTER LISA A105 ADAMS JUAN B100 BAKER JACK P105 PETERSON TINA W100 WHITE WILLIAM W105 WHITE LISA AUTH LNAME FNAME ---- ---------- ---------- R100 ROBINSON ROBERT F100 FIELDS OSCAR W110 WILKINSON ANTHONY 14 rows selected.

39. Show: test_UNUSED.sql and test_UNUSED.doc AUTHOR SQL> describe author; Name Null? Type ------------ -------- --------------- AUTHORID NOT NULL VARCHAR2(4) LNAME VARCHAR2(10) FNAME VARCHAR2(10) SQL> SQL> ALTER TABLE author SET UNUSED COLUMN fname; Table altered. SQL> describe author; Name Null? Type ----------- -------- ------------- AUTHORID NOT NULL VARCHAR2(4) LNAME VARCHAR2(10) SQL> SQL> ALTER TABLE author DROP COLUMN lname; Table altered. SQL> ALTER TABLE author DROP COLUMN fname; ALTER TABLE author DROP COLUMN fname * ERROR at line 1: ORA-00904: "FNAME": invalid identifier SQL> ALTER TABLE author 2 DROP UNUSED COLUMNS; Table altered. SQL> describe author; Name Null? Type ----------- -------- ------------- AUTHORID NOT NULL VARCHAR2(4) SQL>

40. AUTHOR SQL> SELECT * FROM author; AUTH ---- A100 A105 B100 F100 J100 K100 M100 P100 P105 R100 S100 AUTH ---- W100 W105 W110 14 rows selected.

41. Practice all the rest of examples in the text. • A Script file is available on the Bb (file name: Ch3Queries.sql) • After completing all examples, do the HW.

42. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch3_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch3 (or Bmis441-02_Oracle_ch3) Read and Practice all examples on Chapters 3 • 1. Run the script files (in the folder \oradata\chapter3\): JLDB_Build_3.sql • 2. Read Oracle assignment and create a script file Oracle_ch3_Lname_Fname.sql for questions (#1 to #8; p.94) on “Hands-on Assignments”. . • 3. Execute and test one problem at a time and make sure they are all running successfully. • 4. When you done, spool the script files (see next slide for spooling instructions) and email the file (Oracle_ch3_Spool_Lname_Fname.txt) to me by the midnight before the next class. Turn in a hardcopy to me in the class.

43. How to Spool your Script and Output Files After you tested the script file of Oracle_ch3_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following script file from SQL*Plus (since you have created JLDB tables) • Start c:\oradata\chapter3J\LDB_Build_3.sql • 1. type the following on SQL> • Spool c:\oradata\Oracle_ch3_Spool_Lname_Fname.txt (make sure your name is entered) • 2. open Oracle_ch3_Lname_Fname.sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the .txt file and saved in C: drive (oradata\ folder) Email me with the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch3 (or Bmis441-02_Oracle_ch3)

44. End of chapter 3