Getting Oracle’s HR Schema

SQL> @hr_quick_start.sql

Follow the instructions, pressing Enter when prompted. IMPORTANT: Be sure to note your password when prompted. You should see a lot of messages. The final message should be:

**** INSTALLATION COMPLETE ****

SQL> SELECT * FROM Regions;

 REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 4 rows selected.

Connect to the HR Schema from SQL Developer

1. Open SQL Developer.
2. Click the + icon under Connections :
3. Enter the following:
   1. Name: HR
   2. Username: HR
   3. Password: Enter the password you noted when running hr_quick_start.sql .
   4. Hostname: localhost
   5. Port: 1521
   6. Service Name: XEPDB1
   Click Test . You should see a Success message in the bottom left of the dialog.
4. Click Connect .
5. Close the dialog.
6. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet :
7. Enter the following SQL query in the new worksheet:

   SELECT * FROM Regions;

   

8. Run the query by clicking on the left green triangle. You should get 4 rows in the Query Result: If the query worked, you are all set.

Installing the HR Schema in the CDB

Be warned!

The instructions below show how to install the HR schema in the root (i.e, the CDB or container database). As a rule, you don’t want to do this. Rather, you want to install all your schemas in PDBs (pluggable databases). Historically, this was tricky to do, so we used the CDB method as a simpler way of getting students set up to learn SQL and PL/SQL. But the one-and-done script for installing the HR schema has simplified things, so the below is no longer necessary. I’m keeping the documentation in case anyone runs into issues with the run-and-done script and just needs to get the HR database in place, so they can start learning SQL.

If your main purpose is to learn/practice SQL and PL/SQL, the method below is fine. It won’t hurt anything. It’s just not the recommended way of doing things.

There are two pieces involved:

1. Setting up the HR Account.
2. Getting the HR Schema.

For an overview of the HR schema, see Oracle’s Demo HR Schema.

Set Up the HR Account

1. In SQL Developer, add a new connection:
   1. Click the + icon under Connections :
   2. Enter “HR” as the Name and “system” for both the Username and Password, and click Connect :
   3. Close the dialog.
2. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet :
3. Enter the following code:

   DROP USER C##HR CASCADE; CREATE USER C##HR IDENTIFIED BY HR DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK; ALTER USER C##HR ACCOUNT UNLOCK; ALTER USER C##HR QUOTA UNLIMITED ON USERS; GRANT CONNECT, RESOURCE, CREATE VIEW TO C##HR;

4. Select HR from the connection dropdown in the upper right:
5. Click the second green play button to run the script. This will create the HR user and assign it the appropriate privileges:

Get the HR Schema

1. Go to https://github.com/oracle/db-sample-schemas/releases/latest and download the Source code (zip) to get the sample schemas.
2. Unzip the sample schemas to the root of you C drive). That will create a C:\db-sample-schemas-21.1 folder.
3. Run the Command Prompt as administrator:
4. Run the following commands:
   1. Change directory to the human_resources folder within the db-sample-schemas folder:

   cd \db-sample-schemas-21.1\human_resources

   sqlplus C##HR/HR

   SQL> @hr_cre.sql

   SQL> @hr_popul.sql

   You should see a lot of messages. The final message should be:

   Commit complete.

5. Open SQL Developer and open the properties of the HR connection by right-clicking it and selecting Properties :
6. Change the username from “system” to “C##HR” and change to password to “HR”:
7. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet :
8. Enter the following SQL query in the new worksheet:

   SQL> SELECT * FROM Regions;

   

9. Run the query by clicking on the left green triangle. You should get 4 rows in the Query Result: If the query worked, you are all set.

Related Articles

1. How to Unlock the HR User in XEPDB1
2. Oracle Live SQL Instructions
3. Getting Oracle’s HR Schema (this article)
4. How to Install Oracle Express Edition and SQL Developer
5. Oracle’s Demo HR Schema
6. How to choose between a procedure and a function in PL/SQL