Oracle SQL Fundamentals/Introduction

This lesson introduces Oracle SQL database concepts, the SQL language, client tools, and sample schemas.

Objectives and Skills

Objectives and skills for Oracle SQL Fundamentals I certification are covered in detail in other lessons. This lesson helps you:

• Understand Oracle server technologies
• Understand relational database concepts
• Summarize the SQL language
• Use Oracle client tools
• Create example schemas for use in the course

Readings

1. Read Wikipedia: Oracle Database.
2. Read Wikipedia: Relational database.
3. Read Wikipedia: Database normalization.
4. Read Wikipedia: SQL.
5. Read Wikipedia: SQL*Plus.
6. Read Wikipedia: Oracle SQL Developer.

Multimedia

1. YouTube: Oracle 11g SQL Tutorial & Exam 1Z0-051 : Introduction
2. YouTube: How To Prepare For Your Oracle Certification Exam

Activities

1. Install Oracle Express.
   1. Review Oracle: Oracle Database Express Edition Documentation and the the installation guide for your platform (Linux or Windows).
   2. On the system you plan to use as your database server, download and install Oracle Database Express.
2. Use SQL*PLUS to verify the installation.
   1. Review Oracle: Identifying Your Oracle Database Software Release.
   2. At a command prompt on the database server, enter sqlplus system.
   3. At the prompt, enter the password specified during installation.
   4. To display current version information, run the query SELECT * FROM PRODUCT_COMPONENT_VERSION;
   5. Enter exit to exit SQL*PLUS.
3. Use SQL*PLUS to create the HR and OE sample schemas.
   1. Review Oracle: Installing the Sample Schemas.
   2. Download and extract the sample schemas from Oracle:sample-schem-scripts.zip.
   3. At a command prompt, navigate to the schema/hr folder you just extracted.
   4. In the schema/hr folder, enter sqlplus / as sysdba to start SQL*PLUS.
   5. Run the command @hr_main.sql.
   6. Enter the system password. When the script finishes, if necessary enter exit to exit SQL*PLUS and return to the command prompt.
   7. Navigate to the schema/oe folder you extracted.
   8. In the schema/oe folder, enter sqlplus / as sysdba to start SQL*PLUS.
   9. Run the command @oe_main.sql.
   10. Enter the system password. When the script finishes, if necessary enter exit to exit SQL*PLUS and return to the command prompt.
4. Use SQL*PLUS to verify sample schema installation.
   1. At a command prompt, enter sqlplus hr/hr.
   2. Run the query show user; to show that you are currently connected as hr.
   3. Run the query SELECT table_name FROM user_tables; to see the tables that were created.
   4. Run the command connect OE/OE to connect as OE.
   5. Run the query show user; to show that you are currently connected as OE.
   6. Run the query SELECT table_name FROM user_tables; to see the tables that were created.
   7. Enter exit to exit SQL*PLUS.
5. Enable network connections to the server.
   1. Review Oracle: Database Postinstallation Tasks (Linux) or Oracle: Postinstallation Configuration Tasks on Windows.
   2. Configure the server firewall to allow access to the Oracle Listener on TCP port 1521.
6. Install and test SQL*PLUS on a client computer.
   1. Review Oracle: SQL*Plus Instant Client.
   2. Download and install the Instant Client package for your system.
   3. Download and install the SQL*PLUS package for your system.
   4. At a command prompt, navigate to the folder where you installed the Instant Client and SQL*PLUS.
   5. Enter either sqlplus system@hostname or sqlplus system@ip address, replacing hostname or ip address with the hostname or address of your server. If you are unable to connect, check the firewall settings on the server.
   6. At the prompt, enter the password specified during installation.
   7. To display current version information, run the query SELECT * FROM PRODUCT_COMPONENT_VERSION;
   8. Enter exit to exit SQL*PLUS.
7. Install and test Oracle SQL Developer on a client computer.
   1. Review Oracle: Installing Oracle SQL Developer.
   2. Download and install Java SE Development Kit.
   3. Download and install Oracle SQL Developer.
   4. Run SQL Developer.
   5. Add a connection to your server. Include username, password, hostname or ip address, and port number. Test and save the connection.
   6. Double-click on the connection to connect.
   7. In the connection worksheet, enter SELECT * FROM PRODUCT_COMPONENT_VERSION;
   8. Run the query.
   9. Close SQL Developer.
8. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

Lesson Summary

• Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system produced and marketed by Oracle Corporation.
• A relational database is a digital database whose organization is based on the relational model of data.
• The various software systems used to maintain relational databases are known as a relational database management system (RDBMS).
• The relational model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row. Rows are also called records or tuples.
• Each table/relation represents one "entity type". The rows represent instances of that type of entity and the columns representing values attributed to that instance.
• Database normalization, or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy.
• Normalization involves decomposing a table into less redundant (and smaller) tables without losing information, and then linking the data back together by defining foreign keys in the old table referencing the primary keys of the new ones.
• The objective of normalization is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.
• Virtually all relational database systems use SQL (Structured Query Language) as the language for querying and maintaining the database.
• SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
• SQL was originally based upon relational algebra and tuple relational calculus and consists of a data definition language, data manipulation language, and data control language.
• Data Definition Language (DDL) commands include CREATE, ALTER, DROP, and RENAME.
• Data Manipulation Language (DML) commands include SELECT, INSERT, UPDATE, and DELETE.
• Data Control Language (DCL) commands include GRANT and REVOKE.
• Transaction Control Language (TCL) commands include COMMIT, ROLLBACK, and SAVEPOINT.
• Tools available for managing Oracle databases include SQL*Plus, Oracle SQL Developer, and Oracle Enterprise Manager.

Key Terms

entity relationship diagram

A diagram that demonstrates the relationships between entities in a relational database.

foreign key

A field in a relational table that matches the primary key column of another table.

grid computing

The collection of computer resources from multiple locations to reach a common goal.

instance

Refers to any running process.

Oracle Enterprise Manager (OEM or EM)

A set of web-based tools aimed at managing software and hardware produced by Oracle Corporation as well as by some non-Oracle entities.

Oracle SQL Developer (SQLDeveloper)

An Integrated development environment (IDE) for working with SQL in Oracle databases using the Java Development Kit.

PGA (Program Global Area)

Memory-area of an Oracle instance that contains data and control-information for Oracle's server-processes.

PL/SQL (Procedural Language/Structured Query Language)

Oracle Corporation's procedural extension for SQL and the Oracle relational database.

primary key

A field or fields that uniquely specifies a record or tuple within a table.

schema

The structure of a database described in a formal language supported by the database management system (DBMS). An Oracle database associates a separate schema with each database user.

SGA (System Global Area)

Forms the part of the system memory (RAM) shared by all the processes belonging to a single Oracle database instance.

SQL*Plus

An Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - General

See Also

• Oracle: Database 2 Day Developers Guide - Introduction

References

Oracle SQL Fundamentals

Selecting Data →