Working with SQL in Oracle

After knowing what databases and SQL are, it is time to begin making our hands dirty. Remember we chose Oracle as our database software platform we’re going to use. Before starting, we need to first know about a few tips on how we can smoothly use the software.

We are going to use the SQL*Plus tool, an Oracle command line tool. In case you don’t have the set up installed on your machine, you download it from here. To use this tool, we must connect to the database. We shall be using the hr sample database that comes with Oracle after the installation. This database has got sample tables to help us learn SQL easily.

To connect to this database, we use the CONNECT keyword followed by the username (hr) and password. We can join the username and password in the same CONNECT statement. The username is separated by a forward slash followed by a semi-colon to end the statement. You press enter key on Windows or return key on Mac after the semi-colon. You’re connected to the hr database.

CONNECT hr/hr;

In case the account is locked, don’t worry. By default all user accounts are locked in Oracle and we need to unlock them. We need to connect to the hr database as administrators for us to be able to unlock them. To connect as administrators, this is the command we issue.

CONNECT system/hr;

After successfully connecting as administrators, we need to unlock the account. This is the command we issue.

ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;

Once we’re done, User Altered message will be displayed. You can now disconnect from the database.

DISCONNECT

Note: This is done only once after installing Oracle. You should never again connect to the database using the administrator account (at least for now ? )

You can now connect to the sample database using the hr account. Remember the command, CONNECT hr/hr; Once you’re connected, congratulations. We’re now fit to go! Let us start learning SQL.