INTRODUCTION TO ORACLE

MUHAMMAD HAMIZ MOHD RADZI

HR
OBJECTIVES
At the end of this lesson, students should be able to:

▪ Understand the goals of the course

▪ Introduction to Oracle Database 23ai Free
▪ Install the Oracle Database 23ai Free
▪ Describe and use the key features of Oracle SQL Developer
▪ Describe the database and schema used in this course

HR
GOALS OF THIS COURSE
▪ The lab of this course requires students to learn about the environment of creating
and manipulate the data in the database.

▪ It is listed in course outcome of the course which students need to:

▪ Construct the command of fundamental and advanced Structured Query

Language (SQL) statements.
▪ In this course, we are going to use the Oracle 23ai Free as the software to manage
the data in the database.

HR
ORACLE DATABASE 23AI FREE
▪ Every day, in one way or another, we come in contact with computer applications.
▪ If you checked your email today, it was probably done using an application.
▪ If you bought an item at a grocery store, the clerk scanned the item using an
application that calculated your bill and updated the store inventory.

HR
▪ Application programs allowed the end user—people like you and me—to be able
to buy fully developed, ready-to-use programs.
▪ It was no longer necessary to know how the program worked, just that it did work
and accomplished what we wanted it to do.
▪ Application system software is different from system software.

HR
▪ System software is designed to interact with the computer hardware, such as
operating system and compilers.
▪ Application software includes programs for word processing, databases, gaming,
email, and graphics.
▪ For example, Yahoo.com uses Oracle database to store data.

HR
▪ As a user, with a few mouse clicks, we can
read, reply or delete the emails.
▪ This is because the email application has
been pre-programmed with the Structured
Query Language (SQL) set by the
programmers behind it.
▪ User does not need to know the details of
how the programming behind it, as long as
the task that they want to do is done.

HR
 The "Oracle 23ai Free" version is a free
trial of the Oracle Database 23ai software,
allowing users to:
▪ experience the full suite of features
without cost, primarily intended for
developers, data scientists, and anyone
interested in exploring the latest
database capabilities,
▪ including AI-powered functionalities,
with limitations on resource usage like
CPU and storage capacity;
▪ essentially a way to try out the full Oracle
Database 23ai without paying for a
commercial license
HR
INSTALLATION OF ORACLE 23AI FREE
▪ The first step is to download the software at:
https://www.oracle.com/database/free/get-started/
▪ Download this free version to your computer.

HR
▪ Extract the folder and when you open the extracted folder, scroll to find the SETUP
application.

▪ Double click the SETUP application to run the installation.

HR
 Let it run, do not cancel it Click Next once you get to this screen

HR
Accept the term and agreement and click This is the recommended folder
Next where the Oracle will be installed,
which I prefer not to change it. Then,
click Next
HR
You are then required to specify the Then click Install to let it starts the
password, my suggestion is, put 'oracle' as installation.
your password to make it easy to
remember.
HR
 Just let it finish the installation. It takes time depending on
Just Allow when you get this screen. your computer's memory

HR
Click finish to end the installation. To confirm the installation, once you
type SQL in your Windows search
bar, you can see SQL PLus.

HR
SQL DEVELOPER
▪ Oracle SQL Developer is a free graphical user interface which allows database
users and administrators to do their database tasks in fewer clicks and keystrokes.
▪ SQL Developer's main objective is to help the end user save time and maximize the
return on investment in the Oracle Database technology stack.
▪ SQL Developer provides powerful editors for working with SQL, PL/SQL, Stored
Java Procedures, and XML. Run queries, generate execution plans, export data to
the desired format (XML, Excel, HTML, PDF, etc.), execute, debug, test, and
document your database programs, and much more with SQL Developer.
▪ Go to this website to download it:
https://www.oracle.com/my/database/sqldeveloper/technologies/download/

HR
 Download the SQL Developer zipped file with JDK 17 included

HR
 After the download is finished, extract the folder. Open the extracted folder and
scroll until you see the SQLDEVELOPER application. No need to install the
application, just double click to RUN the application every time you want to use it.

My suggestion is, send the SHORTCUT of the application to your DESKTOP for easy
access.
HR
 The loading screen of SQL Developer Just click No when you get this screen
and let it finish

HR
 The Welcome Page once SQL Developer is loaded.

HR
INSTALLATION OF HR SCHEMA
▪ Now, to learn about SQL, we need to make sure Human Resource (HR) schema is
installed in our Oracle database that has been installed.
▪ Go to this website to download all the schemas that can be used for learning
purposes.
▪ https://github.com/oracle-samples/db-sample-schemas/releases/tag/v23.3

HR
 Download the Source code (zip) as per shown on screen

HR
 Once the download is finished, extract the folder and open it. You will see a
lot of database schemas that you can use. Copy the human_resources folder.

HR
 Now, go to your Local Disk C, where you install the Oracle and find schema
folder. The path is as shown.

C:\app\YOUR_USERNAME\product\23ai\dbhomeFree\demo\schema

Paste the human_resources folder that you have copied into this SCHEMA
folder as shown

HR
 Now, open SQL Plus from your Window's search bar to install the HR Schema.

When SQL Plus is loaded, it asked for a username, type:

/ as sysdba

and click Enter. Then you will see a successful message of connecting to the
database.
HR
Then type:

show pdbs;

and click Enter

Then you will get the exact result as shown on screen. Make sure
the FREEPDB1 has open mode of READ WRITE.

HR
 Next, type:

ALTER PLUGGABLE DATABASE ALL OPEN;

and click Enter

Then you will get the exact result as shown on screen.

HR
 Next, type:

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

and click Enter

Then you will get the exact result as shown on screen.

HR
 Next, type:

connect sys/Admin@//localhost:1521/FREEPDB1 as sysdba;

and click Enter

Then you will get the exact result as shown on screen to say that you are Connected

HR
 Next, type:

@?/demo/schema/human_resources/hr_install.sql

and click Enter to install the HR schema based on the folder inside schema
folder that you have pasted in the previous steps.

Then you will get the exact result as shown on screen. You are asked to Enter a
password for HR user. Use 'oracle' as your password to make it easy to remember.
HR
Then click Enter.
 Then, when you are needed to enter a tablespace, just type 'users'.

HR
 Then, when asked about to overwrite the schema, just type YES and click Enter.

Once done, the SQL Plus is closed automatically.

HR
RUNNING HR SCHEMA IN SQL DEVELOPER
▪ Now, your HR Schema is now accessible through your SQL Developer.
▪ At Welcome Page of the SQL Developer, see the left panel, and you can see that
there is no connection being done yet.

Click the Green + button to create a connection

HR
• Enter this information to create
the connection.

• Name: system
• Username: system
• Password: password during the
installation. In my case, oracle.

• Then, change the Details.

• Make sure the radio button is

clicked at Service Name and
enter: FREEPDB1

• When you click Test, the Status

must be Success.

• Then you can click Connect.

HR
• As you can see, the system
connection has been created.

• If you click the + button beside

the system connection, you can
see all the objects inside system
connection.

• Scroll the objects until you see

Other Users object and click the
+ button beside it

HR
• Scroll the Other Users and you
can see the HR schema that we
have installed just now.

• Now, we need to create New

Connection to access HR
Schema.

HR
• Enter this information to create
the HR connection.

• Name: HR
• Username: HR
• Password: password during the
installation. In my case, oracle.

• Then, change the Details.

• Make sure the radio button is

clicked at Service Name and
enter: FREEPDB1

• When you click Test, the Status

must be Success.

• Then you can click Connect.

HR
• As you can see, the HR connection is established.

• You can click the + button beside HR connection to see the objects.

• Inside Tables object, when the + button is clicked, you can see all the tables that we will use
throughout the semester for SQL lesson.

• On the right-hand side, the HR worksheet is opened, and we will write all the SQL inside the
worksheet to get the data from the database.
HR
HR
HR SCHEMA

HR
REFERENCES
▪ Oracle Academy, Database Programming with SQL

HR