Introduction to Oracle CSC 240 Blum 1 StartProgramsOracle

Introduction to Oracle CSC 240 (Blum) 1

Start/Programs/Oracle – Ora. Home 90/ Application Development/SQL Plus CSC 240 (Blum) 2

Alternatively, go to the F drive, Oracle folder, ora 90 subfolder, bin subfolder and double click on the sqlplusw exe file. CSC 240 (Blum) 3

Logon, your username should be your last name followed by your first initial. Your password is your first name followed by underscore and pw. For example, James Smith would have a username smithj and a password james_pw. The host string is: cscora 2. lasalle. edu CSC 240 (Blum) 4

After successful log in CSC 240 (Blum) 5

You can enter SQL commands at the SQL prompt CSC 240 (Blum) These line numbers were supplied automatically. The line will execute (or attempt execution) after the semicolon and the Enter key are struck. 6

Uses DDL to create a table consisting of four fields. CSC 240 (Blum) 7

Confirmation of table creation CSC 240 (Blum) 8

D’Oh forgot to establish Artist. ID as the primary key, can drop this table and start again. CSC 240 (Blum) 9

Recreate Artist table with primary key established. CSC 240 (Blum) 10

Use DDL to create a Work. Of. Art table. CSC 240 (Blum) 11

Use the INSERT command to enter data into the Artist table. CSC 240 (Blum) 12

Use the INSERT command to enter data into the Artist table. CSC 240 (Blum) 13

Query the Artist table CSC 240 (Blum) 14

Query the Work. Of. Art table. CSC 240 (Blum) 15

Perform a query that joins the Artist table and Work. Of. Art table. CSC 240 (Blum) 16

Exit Oracle CSC 240 (Blum) 17

Start Oracle again and do a select query on the table which shows a user’s tables I had also created a Names table, you should only have Artist and Work. Of. Art. CSC 240 (Blum) 18

Add another row of data and close (not exit) CSC 240 (Blum) 19

Start Oracle again and run Select query on the Artist table. Where’s Paul Gauguin? CSC 240 (Blum) 20

Insert Paul Gauguin again and this type follow that with COMMIT, then close. CSC 240 (Blum) 21

Start Oracle again and do a select query on Artist CSC 240 (Blum) This time Paul Gauguin stayed because the insert transaction was committed. 22

Insert another row, rollback, then do a select query on the Artist table CSC 240 (Blum) Jackson Pollok does not appear because that entry transaction was rolled back. 23

Add two rows, rollback and do a select query CSC 240 (Blum) Both new rows disappear, rollback gets rid of things up to the last commit. 24

Create a table, rollback, do a select query on the table Temp appears despite rollback, creates cannot be rolled back. CSC 240 (Blum) 25

Repeat the query joining the Artist and Work. Of. Art tables, but use a RIGHT JOIN. CSC 240 (Blum) 26

Use Notepad to create an. sql file with SQL commands CSC 240 (Blum) 27

Go to Windows Explorer, select Tools/Folder Options CSC 240 (Blum) 28

Select the View tab, choose not to hide file extensions CSC 240 (Blum) 29

If your file has an additional. txt, rename it CSC 240 (Blum) 30

Use the keyword START followed by the path of the sql file you created CSC 240 (Blum) 31

Do not forget to commit the transaction CSC 240 (Blum) 32