command line oracle n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Command-line Oracle PowerPoint Presentation
Download Presentation
Command-line Oracle

Loading in 2 Seconds...

play fullscreen
1 / 46

Command-line Oracle - PowerPoint PPT Presentation


  • 85 Views
  • Uploaded on

Command-line Oracle. Logon to your ORACLE account using the instructions contained in this slideshow. Create the tables with your last name in place of Smith (no spaces in table names). Click on Start and enter cmd into search and hit Enter.

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 'Command-line Oracle' - alida


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
command line oracle

Command-line Oracle

Logon to your ORACLE account using the instructions contained in this slideshow. Create the tables with your last name in place of Smith (no spaces in table names)

enter the sqlplus command
Enter the sqlplus command

sqlplusscott/tiger@csc-srv1.lasalle.edu:1521/ORCL

In the example above scott is the username. Your username should be your usual La Salle username.

In the example above tiger is the password. Your password should be the first three letters of your username followed by an underscore followed by your La Salle id number (e.g. smi_1234567)

ddl reminder
DDL Reminder
  • CREATE TABLE is the SQL for making a new table, it should have a unique name
  • CHAR(6) means that the ArtistID must be 6 characters long
  • NOT NULL means that each record must have an ArtistID
  • VARCHAR(30) means that the ArtFName can be up to 30 characters long
slide13
Copy the code written in Notepad and use the command prompt’s Edit/Paste to enter the code into SQLPLUS
recommendation
Recommendation
  • I recommend making a txt file with examples of all of the different types of SQL commands including the log on.
  • You will be able to use it on any test we have involving Oracle. It will speed things up.
sql display commands
SQL Display commands
  • SET LINESIZE 300 makes the width of the line displaying the results of an SQL command 300 characters long
  • SET PAGESIZE 0 makes the height of a page displaying the results of an SQL command “infinitely long”.
slide24

CREATE TABLE Smith_WorkOfArt(

WorkID CHAR(6) NOT NULL,

WorkTitle VARCHAR(60),

WorkMedium VARCHAR(30),

ArtistID CHAR(6),

PRIMARY KEY (WorkID),

FOREIGN KEY (ArtistID) REFERENCES Smith_Artist);

sql ddl to establish foreign key
SQL DDL to establish foreign key
  • The last line

FOREIGN KEY (ArtistID) REFERENCES Smith_Artist);

establishes that the field ArtistID in the SmithWorkOrArt table is a foreign key that comes from the table Smith_Artist.

query joining artworks and artists
Query joining artworks and artists

SELECT Smith_WorkOfArt.WorkTitle, Smith_Artist.ArtFName,

Smith_Artist.ArtLName

FROM Smith_WorkOfArt INNER JOIN Smith_Artist

ON Smith_WorkOfArt.ArtistID = Smith_Artist.ArtistID;

a variation on the query joining artworks and artists
A variation on the query joining artworks and artists

SELECT Smith_WorkOfArt.WorkTitle, Smith_Artist.ArtFName,

Smith_Artist.ArtLName

FROM Smith_WorkOfArt, Smith_Artist

WHERE Smith_WorkOfArt.ArtistID = Smith_Artist.ArtistID;

Instead of INNER JOIN between table names and ON before condition, one can use a comma between table names and a WHERE before condition

insert two artists rollback then query artist table
INSERT two artists, ROLLBACK, then query artist table

Both new rows disappear, rollback gets rid of things up to the last commit.

another version of insert
Another version of insert

This version of an insert allows you to name specific fields for the input data rather than relying on the order of the fields.

naming the fields allows you to change their order
Naming the fields allows you to change their order

Above we enter the last name first, but the data goes in correctly because we named the fields.

create a table rollback do a select query on the tab table
Create a table, rollback, do a select query on the tab table

FAKE appears despite rollback, creates cannot be rolled back.

redo artwork artist join query as right join
Redo ArtWork-Artist join query as RIGHT JOIN

Recall a RIGHT JOIN is a type of OUTER JOIN and will keep records that don’t have a math in the other table – in this case we have no art work by Paul Gauguin but he appears in the query results above.

second version of outer join
Second version of outer join

In this version you place (+) next to the field that you may not have. We may not have the ArtistID in the WorkOfArt table, but we still want to keep the artist.

place a few insert commands and a commit into a notepad file and save it with an sql extension
Place a few INSERT commands and a COMMIT into a Notepad file and save it with an sql extension
slide44
Go to Control Panel (Classic View) Folder Option and under View tab make sure Hide extensions is not checked
slide46
Also make a screen capture of an outer join after running your insert script. Send a document with the two screen captures.