SQL - The Shortest Route For Beginners (2015)

Chapter 2. Setup Practice Environment

You will perform all the exercises provided in this book on your own computer. For this purpose, you have to download couple of free software: Oracle Database Express Edition 11g and Oracle SQL Developer. You need to open a free Oracle account on Oracle’s site to do the download.

Download, Install, and Test Oracle XE Database

Oracle Database Express Edition (Oracle Database XE) is an entry-level, small-footprint database.  It's free to develop, deploy, and distribute; fast to download; and simple to administer. Follow the instructions mentioned below to download and install Oracle XE Database.

1.      Enter the following address in your browser to download the database:

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

2.      Accept the License Agreement.

3.      Click on the Oracle Database Express Edition 11g Release 2 for Windows x32 link, (if your PC is running 32 bit Windows operating system) otherwise, select the version compatible with your OS.

4.      Enter your Username and Password. If you don't have an account, sign up for a free Oracle Web account and repeat the download process.

5.      Save the zip file to your computer.

6.      Once downloaded, extract the .zip file, and launch the Setup file from the extracted Disk1 folder to start installation.

7.      Follow the on-screen instructions to complete the installation.

NOTE: During the installation process you are required to enter and confirm SYSTEM user password. Provide a password that you can easily remember – I set it to manager. This account is used underneath to test database connectivity. The final installation screen will show a port number - which is usually 1521. Network request is passed to a database through this port. Note down this number, as it is required in the next section.

The XE database installation process creates entries in the program group as show in the following figure.

Test your database installation by clicking the Run SQL Command Line option.  On the SQL prompt type: connect system/manager and press enter. If everything went well during the installation process, you’ll see connected message on the subsequent line, which indicates that your database is up and ready to take requests. Type exit and press enter to quit the command line interface.

Download and Install SQL Developer

SQL Developer is a graphical user interface (GUI) tool that Oracle Corporation supplies to query databases, browse objects, execute reports, and run scripts. It supports Windows, Linux, and Mac OSX. SQL Developer is a non-licensed (free) product. Besides Oracle database, it can be used to connect and access third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, and IBM DB2. Note that SQL Developer doesn't need any installer so it also does not create any registry entries. Similarly, deleting the SQL Developer directory removes it from your system and you do not have to run any uninstaller for it.

1.      You can download it from the following URL.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

2.      Click the  link defined next to the prompt: SQL Developer requires JDK 7 or above.

3.      Save and extract the zip file to a directory of your choice..

4.      Double click sqldeveloper.exe file from the extracted folder to start SQL Developer.

NOTE: One of the first tasks that you may be prompted to do when you start SQL Developer for the first time is to locate the Java Development Kit (JDK). SQL Developer requires JDK 7 or above. If you selected the option to download SQL Developer with the JDK, then java.exe will be included in the jdk sub-folder where you extracted the zip file. If you couldn't locate the jdk sub-folder then you have to download and run the jdk software (jdk-7u71-windows-i586.exe) from: http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

SQL Developer’s Interface

The above figure illustrates the interface of SQL Developer, marked with some important sections of this useful software that you’ll use in this book.

1.      Learn SQL: The connection pane displays all connections you’ve created to connect to different databases. In the next section you’ll create a connection named Learn SQL to connect to the Oracle XE database, that you just installed.

2.      Tables: As a beginner you’ll interact with database tables in this book to learn SQL. Once you connect to your database, you can expand this node to browse all the tables in the database. You can even see structures of tables and the data stored in each table through this node.

3.      Worksheet: This is the pane which you’ll use throughout the exercises presented in this book. Here you’ll enter SQL statements to interact with your database.

4.      Run Statement: You click this button to execute your SQL statements.

5.      Query Result: The result of the executed SQL statement that you enter in the Worksheet will appear in this section.

Creating Connection in SQL Developer

A connection is a SQL Developer object that specifies the necessary information for connecting to a specific database as a specific user of that database. You must have at least one database connection to use SQL Developer.

1.      To create a new database connection, right-click the Connections node  and select New Connection (I named the new connection: Learn SQL (see tag number 1). Fill in the connection form as shown in the following figure.

2.      Enter hr for both username and password. HR is a sample schema, provided with XE database, containing tables and other objects. You’ll use the data held in this schema to perform all the exercises provided in this book.

3.      After completing the form, click the Test button. The Success message in the status section should come up, indicating that you are ready to proceed further.

4.      Click the Save button to save the connection. The new connection should appear in the left pane of the form.

5.      Click Cancel to dismiss the connection form.

6.      Click on the SQL Worksheet icon  and select Learn SQL from the provided list. This will open a worksheet for the selected connection, where you will enter your SQL statements to interact with the corresponding database.

Database Connection Dialog Box in SQL Developer

That’s it! You’ve grasped the most basic concepts about database and SQL, and have also setup the environment for the practice sessions coming ahead. Rest of the chapters in this book will walk you through to practically explore SQL, starting with the data query command.

Test Your Skill

1.      Name any three DBMSs (other than Oracle) which are supported by SQL Developer.

2.      What information do you provide when you invoke SQL Developer for the first time?

3.      Besides SQL Developer what is the other utility in your current practice environment to interact with the database?

4.      You need to provide information for the following six fields when you connect to an Oracle database from SQL Developer: