Oracle 9i SQL Plus Guide

Oracle 9i

Agenda • Start and exit SQL Plus (General) • Start and exit SQL Plus (Tah 1006) • Syntax • Create a new user • Create a new table • Enter data into a new table • Export & import data

Start and Exit SQL Plus • Start SQL Plus • Start • Select Program • Select Oracle-ORACLE_9i • Select Application Development • Select SQL Plus • Enter your name for User Name (system) • Enter Enter your password for Password (manager) • Exit SQL Plus • Type exit at SQL prompt

Start and Exit SQL Plus (Tah 1006) • Start Database • Start the computer • Wait for couple minutes • Select Oracle-XP-SP1 • Press Enter for password (no password is needed) • Double clicks the SQL Plus icon • Enter your name for User Name (system) • Enter your password for Password (manager) • Exit SQL Plus • Type exit at SQL prompt • Close the database window

Syntax - General • Use semicolon (;) to terminate a statement • SQL is not case-sensitive • SQL statement can be entered into several lines • Use comma (,) to separate attributes • Use a pair of single quotes (‘) for any character string • Use a pair of double quotes (“”) for a single (‘) character string

Syntax - General • Use percentage sign (%) for a group of wildcard characters • Use underscore (_) for one wildcard character • Escape character • Select…from….where city like ‘%s/_f%’ escape ‘/’; • Underscore _ after / will be interpreted as a regular meaning underscore • Subquery must be enclosed in a pair of parentheses

Syntax - Name • Table name or attribute name limited to 30 characters (characters, number, special symbols $, _, and #) • Name has to begin with a character • No blank • Not case sensitive • No reserved word

Syntax - Data Types • Characters • Varchar2(n): max 4,000 characters (ASCII, 8-digit) • Char(n): max 2,000 characters (ASCII, 8-digit) • Nvarchar2(n): as char (Unicode, 16-digit) • Nchar(n): as char (Unicode, 16-digit) • Numbers • Integer: number(n) • Fixed-point: number(5,2), 999.99 • Floating-point: number

Syntax - Data Types • Date • Date:DD-MMM-YY HH:MI:SS • Timestamp (fractional seconds precision) with 6 as default value • Large object (LOB): max 4 gigabytes for digitized sounds, images, and binary files

Syntax - Editor • L or LIST - displays most current SQL statements from buffer • Line number - changes the current line number to the entered line number and shows the listed line number • LIST line number or nn nn - show the indicated line number text or between the nn and nn

Syntax - Editor • A or APPEND text - adds the entered text to the end of the current line • I or INPUT text – create a new line after the current line • C or CHANG/old text/new text/ - replaces an existing text string (old text) in the line with a new text string (new text) • DEL or DELETE - deletes the current line • START filename – execute the contents of the SQL Plus command file

Syntax - Editor • SAVE file name - saves the most current SQL statements to a file (filename.sql) • GET file name - retrieves SQL statements from the file name (filename.sql) • R or / - runs the most current statements • SPOOL file name - saves SQL statements, its query, and other results to a file • SPOOL OFF - terminates SPOOL

Spool File - Menu • SPOOL file • Select File • Select Spool • Select Spool file • Type drive (such as a:) • Type file name (such as f1) • Select spool file (*.lst) • SPOOL off • File • Spool • Spool off

Save File Menu • Select File • Select Save • Select Create to create a *.sql file • Select Replace to replace a *.sql file • Select Append to append a *.sql file

Syntax - View Table Structure • View all tables • SELECT TABLE_NAME FROM USER_TABLES; • SELECT TABLE_NAME FROM ALL_TABLES; • View field definitions of a table • DESCRIBE table name • DESCRIBE student; • View all constraints of a table • SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS; • SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘student’;

Oracle Constraint Type Identifier • Primary key: p • Foreign key: r • Check condition: c • Not null: n • Unique: u

Error Message • Type http://otn.oracle.com for the Internet address • Click ‘Search’ • Check ‘Oracle Technology Network Documentation’ box • Type ‘ORA-error messages’ in the search field • Click ‘Search icon’

Create A New Table • CREATE TABLE student (stuid char(5) NOT NULL, stuname char(10), major char(10), credit number(3), CONSTRAINT pkstudent PRIMARY KEY (stuid));

Create A New Table • CREATE TABLE faculty (facid char(5) NOT NULL, facname char(10), dept char(10), rank char(10) check (rank in (‘F’,’Aso’, ‘Ast’)), CONSTRAINT pkfaculty PRIMARY KEY (facid));

Create A New Table • CREATE TABLE class (course# char(5) NOT NULL, facid char(5), sched char(10), room char(10), CONSTRAINT pkclass PRIMARY KEY (course#), CONSTRAINT fkclassfaculty FOREIGN KEY (facid) REFERENCES faculty (facid));

Create A New Table • CREATE TABLE enrollment (course# char(5) NOT NULL, stuid char(5) NOT NULL, grade char(10), CONSTRAINT pkenroll PRIMARY KEY (course#, stuid), CONSTRAINT fkenrollclass FOREIGN KEY (course#) REFERENCES class (course#), CONSTRAINT fkenrollstudent FOREIGN KEY (stuid) REFERENCES student (stuid));

Delete A Record or A Table • DELETE FROM tablename • DELETE FROM student; • DROP TABLE tablename • DROP TABLE student;

Create A New User • CREATE USER username IDENTIFIED BY password • CREATE USER tsai IDENTIFIED BY tsai • GRANT privilege1, privilege2,…TO username or role name • GRANT CREATE USER, CREATE TABLE, DROP TABLE TO tsai; (current user’s schema – user level) • GRANT role name TO username • GRANT DBA TO tsai; (any user’s schema –DBA level)

Delete An User • REVOKE privilege FROM role name • REVOKE CREATE USER form tsai; • REVOKE role name FROM username • REVOKE DBA from tsai; • DROP USER username • DROP USER tsai;

Input Data Into A Table • The ampersand (&) signal the SQL compiler to prompt the user for a value that is then substituted into the query • &prompt_variable is the name of the attribute for which the user is prompted to specify a value • Example INSERT INTO student VALUES (&stuid, &stuname, &major, &credit);

Load Data Into A Table • Tsaitest.dat file contains 11111,Ching,MIS,100 22222,Anderson,MIS,200 33333,Martin,ACT,300 • Tsaitest.ctl contains • LOAD DATA • INFILE ‘tsaitest.dat’ • APPEND • INTO TABLE student • FIELDS TERMINATED BY ‘,’ • (stuid, stuname, major, credit) • The following command should be issued in command line • SQLLDR USERID=tsai/tsai CONTROL= tsaitest.ctl LOG=tsaitest.LOG • Tsaitest.dat, tsaitest.ctl, and tsaitest.log have to be in the same directory such as C:

Export Data • Program • MS-DOS Prompt • Type exp user name/password (such as exp nancy/nancy) • Specify export file (such as a:extf1.dmp where a: is the drive) • Answer the questions to execute the expert program

Import Data • Program • MS-DOS Prompt • Type imp user name/password (such as imp nancy/nancy) • Specify Import file name (such as a:extf1.dmp) • Answer the questions to execute the import program

Process for Oracle Homework • Use notepad to prepare the sql codes for creating tables, data, problems, and control files • Save each control file in an individual file with the ctl as file extension • Save each table data in an individual file with the dat as the file extension • Use copy and paste to move the sql codes of create table from notepad to oracle then press / to run • Copy every data file and control file in the root of C drive

Process for Oracle Homework • Type sqlldr statement at C:> to load the data into each table (one at the time) • Use copy and paste to move the sql codes of problem from notepad to oracle then press / to run • Use save command to store the sql solution for each problem • Use spool to put every problem together • Use get command to get the saved solution of each problem and press / to run • Use spool off to turn off the spooling • Use Word to add the question before each solution and print the saved spool file

Process for Oracle Homework • Use Imp and exp to import and export records and tables between each work session

Oracle 9i SQL Plus Guide