oracle 9i l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle 9i PowerPoint Presentation
Download Presentation
Oracle 9i

Loading in 2 Seconds...

play fullscreen
1 / 26

Oracle 9i - PowerPoint PPT Presentation


  • 225 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle 9i' - questa


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
agenda
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 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 and Exit SQL Plus (Tah 1006)
  • Start Computer
    • Waite for couple seconds
    • Select Oracle-ORACLE_9i
  • Start SQL Plus
    • Start
    • Select Program
    • Select Oracle-ORACLE_9i
    • Select Application Development
    • Select SQL Plus
    • 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
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 general6
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
  • Subquery must be enclosed in a pair of parentheses
syntax name
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
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 types9
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 (BLOB, CLOB, BFILE, NCLOB)
syntax editor
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 - 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
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
Syntax - View Table Structure
  • View all of the 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 the 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
Oracle Constraint Type Identifier
  • Primary key: p
  • Foreign key: r
  • Check condition: c
  • Not null: n
  • Unique: u
create a new table
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 table16
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 table17
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 table18
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));

create a new user
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
Delete An User
  • REVOKE privilege FROM role name
    • REVOKE CREATE USER form tsai
  • REVOKE role name FROM username
    • REVOKE DBA from tsai
  • DROP USER role name
    • DROP USER tsai
load data into a table
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
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
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
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
  • Save each table data in an individual file
  • Use copy and paste to move the sql codes of create table from notepad to oracle then press / to run
  • Copy 4 data files and control files in the root of C drive
process for oracle homework25
Process for Oracle Homework
  • Type four sqlldr statements at C:> to load the data into the tables (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 solution
  • Use spool to save every problem together
  • Use get command to get the saved solution and press / to run
  • Use spool off to turn off the spooling
  • Use Word to print the saved spool file
process for oracle homework26
Process for Oracle Homework
  • Use Imp and exp to import and export records and tables