The UMUC Nova UNIX Server Provides You with an Oracle11g Platform to SQL for This Class
The UMUC Nova UNIX server provides you with an Oracle11g platform to SQL for this class.
You connect to Nova via the Putty utility using SSH secure port 22. You cannot connect via Telnet.
You may connect to Oracle on Nova via SQL*Plus from your Oracle session of the Oracle SQL Developer utility.
You obtain a Nova account by connecting to nova.umuc.edu (port 22) and username “newclass”. Then provide your MyUMUC credentials to obtain a temporary password. You will be forced to change this password on your first entry to Nova with your assigned username.
To enter SQL*Plus you use your Nova username and initial temporary Nova password. Once in SQL*Plus you can change it via the “password” utility.
This lab contains several basic "survival skills" for the homeworks and projects in this class that should all be a review from your CMIS-320 class:
- editing the single command in the SQL*Plus buffer,
- use of SQL scripts,
- the difference between commands and database objects themselves,
- capturing or spooling output files, and
- downloading output files from UMUC servers
Although much of this lab is described in terms of SQL*Plus most functions work the same with the SQL Developer interface.
Oracle/Nova Lab Steps:
1)Connect to the Internet and via a secure shell (SSH) tool such as Putty, connect to Nova at nova.umuc.edu where you will be presented with a login prompt. You can download Putty from UMUC's Web Site.
2)Obtain a Nova account as described earlier. If you are taking multiple classes, be sure you use your CMIS-420 Nova account for this lab since your other ones may expire during this class’s duration.
3)Log onto your Nova account. Upon your first entry to this account you will be asked to change your password.
4)Once logged onto Nova, read any e-mail or bulletins (especially any information on Oracle, pine and pico).
5)Set up a ".forward" file in your home directory to route your e-mail if you will not be reading Nova e-mail on Nova. Create this file via your favorite UNIX editor. A .forward file is simply a one-line file with the e-mail address you’d like your Nova mail forwarded to. Note that you won’t get a copy of your e-mail left on Nova.
6)Log onto Nova's Oracle11g SQL*Plus program via "Nova>sqlplus". Then when prompted enter your Nova username, since this is the same as your Oracle username, and your initial Nova account password as your Oracle password. Once in Oracle's SQL*Plus you can simply change your Oracle password via the "password" utility or the "ALTER USER" command. Note that although your Nova and Oracle usernames are in synch, the passwords are not. Changing one password will not change the other. If you forget either password you can reset them both by logging into Nova as "chgpass".
Note that SQL*Plus is activated differently and behaves differently on Windows operating systems when you use the SQL*Plus utility. You click a Windows icon to begin SQL*Plus and you enter your username, password, and a Local Net Service Name.
7)Explore the Help facility via "SQL>help". Check out some of the SQL*Plus commands.
8)Take a look at the Oracle11g data dictionary structure via:
SQL>DESC dictionary
DESC is short for DESCRIBE, which is an SQL*Plus command and does not need to be terminated with a semi-colon. "dictionary" is aviewof the Oracle11g data dictionary (remember this). You could have also said "DESC dict", since "dict" is a public synonym for "dictionary". The TABLE_NAME column lists all views in the Oracle data dictionary and the COMMENTS column describes what the view is. User "sys" owns the entire Oracle11g database.
9)Look at the views of the Oracle data dictionary you can use via:
SQL> SELECT table_name FROM dictionary ORDER BY table_name;
This is the first actual SQL statement in this lab. Note the semi-colon after this statement since it's an SQL statement, not an SQL*Plus command. SQL is not a case-sensitive language, but I've capitalized the SQL command "SELECT" and the keywords "FROM" and "ORDER BY" by convention (it's a good one to use!). The views starting with "USER_" are for database objects you own. The ones starting with "ALL_" are ones you have access to. The ones starting with "DBA_" are DBA views for all database objects in the database and usually only a DBA has access to them.
10) See if you have any tables in your schema yet via:
SQL> SELECT table_name FROM user_tables;
This is another SQL command and must be terminated by a semi-colon. You should get "no rows returned." since you haven't created any tables yet.
11) Save this useful SQL statement for late use anytime via:
SQL>SAVE mt
SAVE is another SQL*Plus command. This command makes the file mt.sql in your current directory, which contains the single SQL SELECT command you just typed. You can verify this was done via:
SQL> !ls *.sql
SQL> !cat mt.sql
These two UNIX commands are actually run from within SQL*Plus.
12) The file "mt.sql" is actually a one-line SQL script. Run this single-statement SQL script file the following different ways:
SQL>start mt.sql
SQL>start mt
SQL>@mt
All should give you the same result. Note that the last one is the easiest approach to run an SQL script file.
13)Create an Oracle11g table (your first one!) via the following SQL Data Definition Language (DDL) statement. Note that the numbers 2 and 3 are not typed by you; they are returned automatically by SQL*Plus indicating the continuation lines of the SQL statement.
SQL> CREATE TABLE customers (acct_id VARCHAR2(10) PRIMARY KEY,
2 nameVARCHAR2(30) NOT NULL,
3zip_codeCHAR(5) UNIQUE);
This creates a table called CUSTOMERS with three different columns of ACCT_ID, NAME, and ZIP_CODE. Appropriate data types are used. The ACCT_ID column is the primary key of the table. The numbers "2" and "3" are returned by SQL*Plus when you hit enter at the end of one SQL statement line and go to the next line. The SQL statement is not entered, however, until you type a semi-colon.
14) You should get the message "Table created.". If you get an error correct any typos and try again. You correct your errors by editing this SQL statement that currently resides in the SQL buffer. Only one SQL statement can be in the SQL buffer. SQL*Plus commands are not stored in the SQL buffer, and do not overwrite in the SQL buffer the last SQL statement you typed. Note that the helpful "c"hange, "l"ist, "a"ppend, "i"nsert, "del"ete, and "edit" SQL*Plus commands to edit commands in the SQL*Plus buffer.
15) If you needed to edit your CREATE TABLE statement to correct any syntax errors, type "/" to re-execute a command in the buffer via:
SQL> /
You should eventually get the “Table created.” success message from SQL*Plus
16) Once the CUSTOMERS table is successfully created, describe the new table via:
SQL>DESC customers
Note the structure of the table. Notice that the column names are capitalized within the Oracle data dictionary even though you typed this in via lower case in the CREATE TABLE SQL statement.
17) Save the table creation SQL statement via:
SQL>SAVE make_customers
Verify that this actually saved the SQL statement into an SQL script file via running the UNIX ls command from SQL*Plus via:
SQL> !ls *.sql
Note that CUSTOMERS is the name of the table and MAKE_CUSTOMERS.SQL is the name of a one-line SQL script file that can create the CUSTOMERS table. Your tables are stored in the Oracle database under your schema in an Oracle proprietary format, not as operating system files.
18) Drop the CUSTOMERS table from your schema via:
SQL>DROP TABLE customers;
You should get a "Table dropped." message.
19) Retrieve the SQL script file to create the CUSTOMERS table via:
SQL>GET make_customers
This brings the earlier "CREATE TABLE customers…" SQL statement from the SQL script file into the SQL buffer. Remember that the file MAKE_CUSTOMERS actually has an .sql extension and resides in your UNIX home directory. You can have also said SQL@make_customersto execute an SQL script file.
20) Type "/" to re-execute the CREATE TABLE SQL statement in the SQL buffer. The table should be recreated.
21) Execute "SQL>DROP TABLE customers;" again.
22) Recreate the table via "SQL>@make_customers".
23) "Populate" your table with a row of data via:
SQL> INSERT INTO customers (acct_id, name, zip_code)
VALUES ('1234567890', '(your name)', '(your zip code)');
Be sure to substituteyournameandyour5-digit zipcode in the above SQL statement!
24) You should get "1 row created". If not correct any errors by editing the SQL statement in the SQL buffer and retry.
25) Retrieve your data via "SQL>SELECT * FROM customers;". You should get the row just inserted.
26) Retrieve just the account id via "SQL>SELECT acct_id FROM customers;".
This is like the relational algebra "projection".
27) Retrieve the account id and name but for only certain customers via:
SQL>SELECT acct_id, name FROM customers WHERE name > ‘M’;
This is like the relational algebra "projection" of a "restriction" set of operations.
28) INSERT more rows and try more SELECT statements (try ORDER BY, etc.).
29) Change the table structure via:
SQL>ALTER TABLE customers ADD rating CHAR(5);
This should add a new column named RATING to the end of the table. Note that in a learning/development environment, such as this class, you really don't need to run the ALTER TABLE command since you can just re-edit your SQL script file(s) and then re-run them to alter the structure of your tables. In a production environment though, you would need to use the ALTER TABLE command.
30) Type and execute "SQL>DESC customers;" to note the revised structure.
31) Add another row via:
SQL>INSERT INTO customers (name,acct_id,rating)
2 VALUES ('Nancy Nines','9999999999','Good');
32) Execute "SQL>SELECT * FROM customers;" to see all the data (i.e. 2 rows plus whatever was input in Step #28 above) now.
33) Add another row via:
SQL>INSERT INTO customers
VALUES ('3334445555','Anne Goode', '90210', 'Bad');
34) Execute "SQL>SELECT * FROM customers;".
35) Update your data via:
SQL>UPDATE customers
2 SET name=‘John Paysontime’
3 WHERE rating=‘Good’;"
36) Execute:
SQL>SELECT * FROM customers WHERE rating IS NOT NULL;
to see all customers who have a rating value specified.
37) Delete a row via:
SQL>DELETE FROM customers WHERE rating=‘Bad’;
38) See what’s left via:
SQL>SELECT * FROM customers ORDER BY name, acct_id;
39) Try more INSERTs, SELECTs, UPDATEs, DELETEs, etc.
40) Edit the external file "make_customers.sql" to put "DROP TABLE customers CASCADE CONSTRAINTS;" as the first line, before the CREATE TABLE line. Note how this set of two commands will always be successful since the first command drops any table that may exist before the second tries to create the table. You could not have typed both commands in the SQL*Plus buffer, which accepts only one command at a time. The CASCADE CONSTRAINTS clause ensures that the table will be dropped even if there is another table with a foreign key referencing it.
The SQL script file "make_customers.sql" should now look like the following:
DROP TABLE customers CASCADE CONSTRAINTS
/
CREATE TABLE customers…
/
Note that forward slashes are used by convention after each SQL statement in an SQL script file to execute the SQL statement, whereas semi-colons are used when typing in and executing SQL statements in manually via SQL*Plus.
41) Think of how you could create a script of commands to create an entire set of tables in a single file. Expand this thought to include INSERT commands. Expand this even further to have external script files call other script files using a hierarchy of SQL script files. The possibilities are endless! (Remember this strategy for the later assignments since you are required to use SQL script files for these two projects.)
42) From the SQL> prompt, type:
SQL> SPOOL myfile
This begins capturing all SQL statements and SQL*Plus commands into the file named MYFILE.LST. Note that creagint a SPOOL file is only relevant if you are using the command-line SQL*Plus interface to Nova. If you are using SQL Developer you’ll need to take screen snapshots of your SQL and results.
43) From the SQL> prompt, type:
SQL> SPOOL
This shows the status of spooling in SQL*Plus.
44) From the SQL> prompt, type "SET ECHO ON"
This SQL*Plus command will echo all SQL statements run from an SQL script file onto the monitor, and if spooling is in effect, also capture them into the SPOOL file.
45) From the SQL> prompt, type "@mt" (this is for SQL*Plus, with iSQL*Plus SQL script files are loaded from previously-saved files and rerun)
46) From the SQL> prompt, type "SELECT * FROM customers;"
47) From the SQL> prompt, type "SPOOL OFF" to close the SPOOL file and stop spooling.
48) From the SQL> prompt, type "SPOOL" to show that spooling is now stopped.
49) From the SQL> prompt, type "EDIT myfile.lst"
50) This brings up your SPOOL file in your default Nova UNIX editor (i.e. pico). Look at the SPOOL file to see all the SQL statements and SQL*Plus commands you've typed and the Oracle responses after each SQL statement. Using SPOOL files is the best way to capture output from an SQL*Plus session. If you are using Nova you will need to securely ftp these SPOOL files back to your local computer before submitting them for the assignments. The free FileZilla utility is recommended to perform file transfers.
51) Type "SHOW USER" to display your Nova and SQL*Plus username.
52) Exit SQL*Plus via "SQL> EXIT".
53) Exit Nova via "Nova>exit".
54) Congratulations!
55) See the directions below to claim your 5 points for this homework assignment.
Remember the many useful things you learned in this lab for the upcoming SQL assignments.Notice how you can simply edit and rerun SQL script files until you get rid of all Oracle errors. In this way you always know what SQL statements you executed. You are not to type SQL statements into SQL*Plus in an ad hoc manner for the assignments.
To show that you have successfully completedHomework 1and earn credit for it:
1)After the above 55 steps, add yourself and one or more other persons to your CUSTOMERS table.
2) Submit a SPOOL file (or if using SQL Developer a screen snapshot) that includesonly:a)an SQL*Plus "SHOW USER" statement and your username, b) a display of the complete contents of your CUSTOMERS table from Nova, and c) the SQL SELECT statement you used to display the contents of your table.Include only items a) through c)in this step in your SPOOL file (or HTML output file).DoNOTinclude the results of any of the 55 practice stepsor Step 1)above.
To create a screen snapshot use the PrtScrn button on your keyboard and then Paste into a Microsoft Word file and submit this file.