Database Tutorial

1. You should see the following login screen when you launch the Fedora environment. You should enter the password for the student account and enter the return key.

2. You should see the following screen after clicking the space bar. Enter the password for the student user and click the return key.



You will see the following list of icons on the top right of the desktop:

Oracle XE

The Getting Started with Oracle XE icon launches a small tutorial for using Oracle XE.

Home

The Home icon launches the file manager in the student user’s home directory.

KDevelop

The KDevelop icon launches a full Integrated Development Environment (IDE). You can use the IDE to edit and save your script files.

Konsole

The Konsole icon launches the Linux Terminal session. You can edit your files with vim, launch Oracle’s SQL*Plus Command-Line Interface (CLI) with the sqlplus executable, and with sqlplus you can run SQL commands.

KWrite

The KWrite icon launches a text editor. You can edit your files with the text editor.

SQL Developer

The SQL Developer icon launches Oracle’s SQL Developer, which is an IDE specifically designed to help developers navigate the complexity of the Oracle XE database server.

Trash

The Trash icon launches a folder where you can place files for deletion from the system.

[student@localhost ~]$ pwd

It will display:

/home/student

You can list the contents of the directory (also known as a folder) with the ls command, which is an abbreviation list.

[student@localhost ~]$ ls

It will display the following directories in blue text:

Code     Documents  Music             Pictures  Templates  workspace
Data     Downloads  NetBeans          Projects  Public    Videos
Desktop  eclipse    oradiag_student   Stage     vmware

You change directories with the cd command, which is stands for change directory. You can change to a subdirectory of the current directory with the following syntax. You should note that there isn’t a forward slash before the first level subdiretory.

[student@localhost ~]$ cd Data/cit225/oracle/lab1

Then, issue a pwd command to ensure you’re in the right place:

[student@localhost ~]$ pwd

It will displays:

/home/student/Data/cit225/oracle/lab1

You can list the contents of the /home/student/Data/cit225/oracle/lab1 directory with the ls command. You should see the following file:

[student@localhost ~]$ ls

It will display the following file in green text if you created it with the instructions in the Lab Description, Step 4.

apply_oracle_lab1.sql

Before you connect with the sqlplus program, you should verify that it’s in your environment’s $PATH variable. You can do that with the which command, like this:

[student@localhost ~]$ which -a sqlplus

It will display:

sqlplus ()
{ 
    if [ "$RLWRAP" = "0" ]; then
        sqlplus "$@";
    else
        rlwrap sqlplus "$@";
    fi
}
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus

You can connect to the Oracle instance with the sqlplus utility:

[student@localhost ~]$ sqlplus student/student

It will display:

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 17 22:31:32 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
SQL>

You can check for the existence of the file with the following command:

SQL> !ls

It will display:

apply_oracle_lab1.sql

You call the file in sqlplus like this:

SQL> @apply_oracle_lab1.sql

Now, to display data you will run two more scripts but don’t put them in the apply_oracle_lab1.sql script file.

SQL> @../lib1/preseed/preseed_oracle_store.sql
SQL> @../lib1/seed/seeding.sql

It will display:

                                              Rent
Member Account    Name                  Rent  Item
  ID # Number     (Last, First MI)      ID #  ID # Item Title
------ ---------- -------------------- ----- ----- --------------------------
  1002 B293-71446 Vizquel, Oscar        1001  1001 Star Wars I
  1002 B293-71446 Vizquel, Oscar        1001  1002 Star Wars II
  1002 B293-71446 Vizquel, Oscar        1001  1003 Star Wars III
  1002 B293-71446 Vizquel, Doreen       1002  1005 Camelot
  1002 B293-71446 Vizquel, Doreen       1002  1004 I Remember Mama
  1003 B293-71447 Sweeney, Meaghan      1003  1006 Hook
  1003 B293-71447 Sweeney, Ian M        1004  1007 Cars
  1001 B293-71445 Winn, Brian           1005  1009 The Hunt for Red October
  1001 B293-71445 Winn, Brian           1005  1008 RoboCop
 
9 rows selected.

SQL> SELECT   table_name
  2  FROM     user_tables
  3  WHERE    table_name NOT IN ('EMP','DEPT')
  4  AND NOT  table_name LIKE 'DEMO%'
  5  AND NOT  table_name LIKE 'APEX%'
  6  ORDER BY table_name;

It will display:

TABLE_NAME
------------------
ADDRESS
COMMON_LOOKUP
CONTACT
ITEM
MEMBER
RENTAL
RENTAL_ITEM
STREET_ADDRESS
SYSTEM_USER
TELEPHONE
 
10 rows selected.