CS 342 - Introduction

We will use Oracle Express Edition (XE) as the primary database management system for this course. OracleXE is a limited, but functional version of Oracles flagship database management system. See the documentation linked from the policies page.

OracleXE 11g Release 2 and the other tools required for the course are installed on the Windows partition of the lab.

Getting Started with OracleXE

Do this…

Do the following to get started with OracleXE

Start the Oracle database server by choosing

Start→All Apps→Oracle Database 11g Express Edition→Start Database

This starts the local server and brings up a command window that you can use to interact with Oracle. Though Oracle provides various application development tools, we’ll generally stick with the command line. If the server doesn’t start properly, you may need to manually start the service by opening the Windows “Services” tool and start “OracleServiceXE” and “OracleXETNSListener”.
Move to the (network!) directory in which you plan to store your command files.

% cd network:\yourCourseDirectoryPath\lab01\

This allows you to create a command file, e.g., lab01_1.sql, in which you store your SQL code, and to run that file directly from within Oracle, by saying @lab01_1.

Start sqlplus and login to the system account.
```
% sqlplus system/oraclePassword
```

Unlock the sample human resources database, hr, as follows.

SQL> ALTER USER hr ACCOUNT UNLOCK;
SQL> ALTER USER hr IDENTIFIED BY password;

Logout (exit) and log back in as hr/password.

There is nothing submit for this exercise.

See the documentation referenced on the policies page for more details on both user administration and SQL.

Working with Oracle Tables

In this section, you work with Oracle tables using basic SQL. The first exercise focuses on hr, the sample human resources database you activated above. Before starting the exercise, explore the HR database schema, using these helpful hints as needed.

If needed, you can find examples of simple SQL commands documented in the text, Section 6.3, “Basic Retrieval Queries in SQL”. Use a text editor, e.g., Notepad++, or even an IDE, e.g., Intellij IDEA, to create the command file and run it from within SQLplus as described above. Include comments in your command file indicating which query is for which lab exercise using a SQL comment specification: -- your comment... .

Exercise 1.1

Create a command file, lab01_1.sql, and add SQL queries to:

list all the rows of the departments table.
find the number of employees in the database (nb., use the COUNT() aggregate function for this).
list the employees who:
1. make more than $15,000 per year.
2. were hired from 2002–2004 (inclusive).
3. have a phone number that doesn’t have the area code 515 (nb., use NOT LIKE with the wildcard %).
Implement this as three separate queries.
list the names of the employees who are in the finance department. Try to format the names as “firstname lastname” using concatenation (i.e., || ) and order them alphabetically.
list the city, state and country name for all locations in the Asian region.
list the locations that have no state or province specified in the database (nb. use the NULL value for this query).

Save your command file for submission.

Oracle provides a default table named dual , which you can use to verify that OracleXE is up and running and to do other simple tasks.

Exercise 1.2

Try to figure out the schema and the data values stored in dual. Give examples of why one might want to use such a table.

Create a second command file, lab01_2.sql, with your explanation in SQL comments and the SQL commands you use.

Stopping OracleXE

OracleXE tends to use up system resources so it’s a good idea to stop the server when it’s not needed.

Do this…

Stop the Oracle database server by exiting sqlplus, closing the database command window and then choosing

Start→All Apps→Oracle Database 11g Express Edition→Stop Database

This stops the local server (which includes stopping the “OracleServiceXE” and “OracleXETNSListener” services.

Checking in

Submit your solution files to your GitHub repo; see the policies page for details. This includes your solutions both to this unit’s guide and these lab exercises. We will grade your work according to the following criteria: