In this video I'll show you how to set up the sample HR schema in an Oracle database.
We'll download the SQL files, connect to a database, and run the SQL files to create
the objects and populate the data.
What is the HR schema?
It's a set of sample tables and other objects created by Oracle for use on their database.
It's often quoted on websites in examples that demonstrate queries.
The HR schema comes with some databases and can be unlocked.
But what if you don't have it?
We'll learn how to get it in this video.
Here's the ERD of the sample schema.
I'll have a link to this image in the description below.
We have several tables, each with a primary key, and joined on foreign keys.
It's centered on the employee table, with departments and jobs and tables for other
concepts.
So why this video?
First, I was not able to find any good instructions or guide on how to set it up.
The pages that do exist are quite vague or assume a certain level of knowledge that not
everyone has.
Secondly, the existing repository that stores the SQL files has not been updated in 6 years
and doesn't work with the new pluggable database architecture.
It's also not friendly to those of us who use IDEs.
I've also been asked many times on how to set up this schema, so I thought I'd create
this video to demonstrate how to do it.
The solution in this video includes several scripts in my own GitHub repository.
I've based them on the Oracle SQL files and reformatted them to be easier to read and
run.
So, enough of this background, let's get started.
Here are the high level steps we'll follow.
First, we'll download the SQL files from a GitHub repository.
Next, we'll open an IDE and connect to the database we want to set up the data on.
Then, we'll run script number 1 to create the HR account,.
Then, we'll connect to the database as this HR account.
Finally, we'll run the remaining 3 scripts as this HR account to create the data.
Open a web browser and navigate to the github repository here: bbrumm/oracle_sample.
I'll have a link to this in the description below.
Alternatively you can use a command line to run a GitHub command to download this repository
onto your own computer.
In this repository is a Readme file that explains the process in this video, along with the
SQL scripts to run.
Download the SQL scripts from here manually by opening each of them and saving them to
your computer.
This may be a simpler way to get the schema up and running if you don't want to use the
github commands.
Once you've got the files downloaded, open your IDE.
In this video, we'll use SQL Developer, but you can use any IDE that connects to an Oracle
database.
Connect to your database as a system account.
In this example, I'll connect to the container database, as that's the way I've use to get
this working.
This connection here is one that I've set up for my own Oracle database.
If you want to know how to set up an Oracle connection, check out my other videos linked
in the description.
Open the first script file, the one titled "01".
You can do this by clicking on the folder icon here and browsing to the SQL file.
Once the file is opened, select the connection on the drop-down box on the top right if it's
not already set.
This script will create the HR account on the pluggable database, which is then used
to create all of the other tables.
Take note of the password here, mentioned after the Identified By.
It is hrpass, all one word.
You'll need this to connect in the next step.
If there are any issues with running this script, let me know in the comments below.
I'll be able to update the script on GitHub, but not this video, so if the script looks
a little different to this video then it's because we've found and fixed a problem.
Run the script by clicking Run Script.
Once the script is run, you'll see this output here.
Now it's time to connect as this HR user.
Create a new connection to the database.
Enter the database details, and enter the username of HR and the password.
The username may need to be in uppercase.
If you get any errors, take a look at the article in the description below where I share
some solutions to common Oracle connection issues.
So, once you've got the connection to the database as the new HR account set up, you
can connect.
Open the script with the filename 02.
Run this script using the Run Script button.
This will drop and create the required tables.
You'll likely get a range of messages at the start saying that the table does not exist.
This is OK.
The table being deleted does not exist, as we're creating it in this script.
These Drop Table statements are there so that you can run the script again if needed without
having to manually delete objects.
Once script 02 is finished, open script 03, and run it.
This should populate the tables you have just created.
If you ever need to restart this process again, you can start from script 02, which deletes
the tables and any data in it.
Once script 03 is done, open script 04 and run it.
This will create several other objects that are used.
Once script 04 is done, then the sample schema is successfully loaded.
Well done!
You can now run queries against it and practice your SQL.
And that brings us to the end of this guide on setting up the sample HR schema in Oracle.
We downloaded the SQL files, connected to our database, created the HR user, and created
and populated our data.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit [databasestar.com](http://databasestar.com/).
That’s where I share my best database-related content.
Which part of this sample data tutorial was the most helpful for you?
Was it having a location to download the files from?
Or watching the scripts being run on the database?
Or something else?
Thanks for watching.