Transcript Personal Oracle7

Personal Oracle8i
•
•
•
•
•
•
Create a new user
Create a new table
Enter data into a new table
Export & import data
Start and exit SQL Plus
SQL Plus Syntax
Create A New User - 1
•
•
•
•
•
•
•
•
•
•
Start
Select Program
Select Oracle-oracle8i_home
Select Application Development
Select Oracle8i Navigator
Select Oracle8i Personal Edition
Select Local Database
Select User icon
Right click mouse
Select New
Create A New User - 2
•
•
•
•
•
•
•
Enter your name for the Name
Enter your password for the New
Enter your password again for the Confirm
Select Role/Privilege tab
Select Role
Select <<
Select Privilege
Create A New User - 3
• Select <<
• Select OK
Create a New Table - 1
•
•
•
•
•
•
•
•
•
•
Start
Select Program
Select Oracle oracle8i_home
Select Application Development
Select Oracle8i Navigator
Select Oracle8i Personal Edition
Select Local Database
Select Table icon
Right Click mouse
Select New
Create a New Table - 2
•
•
•
•
•
•
•
Select create table manually
Select OK
Select General tab
Enter Faculty for the Name
Select your name for the Owner
Select Design tab
Enter fid as first column name
Create a New Table - 3
•
•
•
•
•
•
•
Select number for fid type
Enter 4 as the size for fid
Make sure the can be null box is clear
Make sure the unique box is checked
Select fid as primary key for the table
Enter flname as second column name
Select char for flname type
Create a New Table - 4
•
•
•
•
•
•
Enter 10 for the size
Make sure the can be null box is clear
Make sure the unique box is clear
Enter ffname for the third column
Select char for the ffname type
Enter 10 for the size
Create a New Table - 5
•
•
•
•
•
•
•
Make sure the can be null box is clear
Make sure the unique box is clear
Enter locid as fourth column
Select number for the locid type
Enter 4 for the size
Make sure the can be null box is clear
Make sure the unique box is clear
Create a New Table - 6
•
•
•
•
•
Enter fphone for the fifth column
Select number for the fphone type
Enter 10 for the size
Make sure the can be null box is clear
Make sure the unique box is clear
Create a New Table - 7
•
•
•
•
•
•
Enter frank for the fifth column
Select char for the frank type
Enter 4 for the size
Make sure the can be null box is clear
Make sure the unique box is clear
Select OK
Input Data Into A Table -1
•
•
•
•
•
•
•
•
•
Start
Select Program
Select Oracle oracle8i_home
Select Application Development
Select Oracle8i Navigator
Select Oracle8i Personal Edition
Select Local Database
Select Table icon
Highlight and right click table named faculty
Input Data Into A Table - 2
• Select Open
• Enter the first record
• Select the row label for row1 to select the
row and right click
• Select Insert after, a blank row appear under
row 1
• Enter the second record
Input Data Into A Table - 3
• Repeat the above processes until you finish
entering data
• Select close button (X icon in the upper
right corner)
• Select Yes to commit the data
Export Data - Method 1
• 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
Export Data - Method 2
•
•
•
•
•
•
•
•
•
Start
Select Program
Select Oracle oracle8i_home
Select Application Development
Select Oracle8i Navigator
Select Oracle8i Personal Edition
Select Local Database
Select Table icon
Highlight and right click table named faculty
Export Data - Method 2
• Select Export to File
• Type a: for Save in
• Type a file name (such as f1baup) for File
name
• Select Text for Save as type
• Click Save
Import Data - Method 1
• 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
Import Data - Method 2
•
•
•
•
•
•
•
•
•
Start
Select Program
Select Oracle oracle8i_home
Select Application Development
Select Oracle8i Navigator
Select Oracle8i Personal Edition
Select Local Database
Select Table icon
Highlight and right click table named faculty
Import Data - Method 2
• Select Import from File
• Type a: for Look in
• Type a file name (such as f1baup) for File
name
• Select Text for Files of type
• Click Open
Start and Exit SQL Plus
• Start SQL Plus
–
–
–
–
–
–
–
Start
Select Program
Select Oracle oracle8i_home
Select Application Development
Select SQL
Enter your name for User Name
Enter Enter your password for Password
• Exit SQL Plus
– Enter exit at SQL prompt
SQL Plus Syntax - 1
• 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 ll to concatenate strings
• Use a pair of single quotes (‘) for any
character string
SQL Plus Syntax - 2
• Use percentage sign (%) for a group of
wildcard characters
• Use underscore (_) for one wildcard
character
• Subquery must be enclosed in a pair of
parentheses
• Table name limited to 30 characters, no
reserved word
SQL Plus Syntax - 3
• Attribute name limited to 30 characters, no
reserved word
SQL Plus Statement Syntax - 1
• 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 - show the indicated line
number text
SQL Plus Statement Syntax - 2
• APPEND text - adds the entered text to the
end of the current line
• CHANG/old text/new text/ - replaces an
existing text string (old text) in the line with
a new text string (new text)
• DELETE - deletes the current line
• INSERT text - create a new line after the
current line
SQL Plus Statement Syntax - 3
• SAVE file name - saves the most current SQL
statements to a file
• GET file name - retrieves SQL statements from
the file name
• R or / - runs the most current statements
• SPOOL - saves SQL statements, its query, and
other results to a file
– SPOOL file name
– SPOOL OFF - terminates SPOOL
SQL Plus 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
SQL Plus View Table Structure
• View all of the tables
– Select * form user_tables;
• View field definitions of a table
– Describe table name
– Describe student;