1 / 46

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). Click on Start and enter cmd into search and hit Enter.

alida
Download Presentation

Command-line Oracle

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 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)

  2. Click on Start and enter cmd into search and hit Enter.

  3. Click on the menu at the top and choose Properties from the drop-down list

  4. Under the Colors tab, change the background color to white.

  5. Under the Colors tab, change the screen text color to black.

  6. Under the Layout tab, make the widths 120. Click OK.

  7. Use the title command, to give your Command Window a title.

  8. 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)

  9. Result so far.

  10. Start Notepad by entering notepad into the start search box

  11. Enter the SQL DDL (Data Definition Language) code for creating a table

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

  13. Copy the code written in Notepad and use the command prompt’s Edit/Paste to enter the code into SQLPLUS

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

  15. Code to get rid of table if you made a mistake

  16. You can also change your table structure using the ALTER TABLE SQL command

  17. The command describe table_name shows the fields and their and types.

  18. Use the SQL command INSERT to add a new record.

  19. Use SQL SELECT command to see record in table

  20. Use SQL’s set linesize and set pagesize

  21. 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”.

  22. Insert another record

  23. Create another table.

  24. 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);

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

  26. Enter data into WorkOfArt table

  27. Query to show art works

  28. 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;

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

  30. Exit for SQL

  31. The SELECT * FROM TABshows the user’s tables

  32. Insert another record of artist data and Close (Not Exit)

  33. Get back in and query the artist table, where’s Paul Gauguin?

  34. INSERT again, then COMMIT, then close

  35. This time Paul Gauguin survives

  36. INSERT a new artist, then ROLLBACK, then query artists table

  37. INSERT two artists, ROLLBACK, then query artist table Both new rows disappear, rollback gets rid of things up to the last commit.

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

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

  40. Create a table, rollback, do a select query on the tab table FAKE appears despite rollback, creates cannot be rolled back.

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

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

  43. Place a few INSERT commands and a COMMIT into a Notepad file and save it with an sql extension

  44. Go to Control Panel (Classic View) Folder Option and under View tab make sure Hide extensions is not checked

  45. To run the script type the @ symbol by the path of the sql script file, make a screen capture

  46. Also make a screen capture of an outer join after running your insert script. Send a document with the two screen captures.

More Related