Command-line Oracle

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.