Oracle/SQL Tutorial: Get Started Quickly

OracleSQLtutorialThis Oracle/SQL tutorial offers a quick way to learn how to use SQL to interact with an Oracle database. Newcomers to programming often consider learning a new programming language to be a difficult process. The best way to learn a new language is to start with the basics and then move to more advanced topics. If you’re interested in demystifying the concepts relating to programming, an introductory course designed to teach non-programmers the basics is a good place to start.

Let’s get started!

What is the Relationship Between Oracle and SQL?

To understand Oracle SQL, you first need to understand SQL. SQL is an acronym that stands for Structured Query Language, which is a type of programming language. Like all programming languages, SQL has two distinct characteristics: (1) it is comprised of a set of instructions and (2) it is used to solve problems. The instructions you write in SQL are called statements. Programming languages such as C++ and Java are considered general purposes languages because they can solve a variety of different problems. SQL is designed to focus on one—managing data that is stored in a relational database management system or RDMS.

There are numerous flavors of SQL that differ in how they implement the programming language. The list includes DB2, MS SQL Server, MySQL, PostgreSQL, Informix, and of course Oracle. Oracle was the first of these implementations to be offered for commercial purposes (it was initially sold to government agencies). Today, Oracle SQL is considered the standard implementation for interacting with RDBMS. Oracle 11g is the most recent version of Oracle that was released in 2007. Once you learn the basics in this tutorial, you can then extend your skills to include database administration. A comprehensive course in Oracle 11g, which includes the basics for administering an Oracle 11g database, is an excellent choice.

Key Concepts

Before jumping into using the Oracle implementation of SQL, there are some important concepts you should understand because you will undoubtedly come across them when using the language. These concepts are common to other query languages, but some have specific uses in Oracle.

Database – A collection of data that is organized in a specific order.

Table – An object that stores data or information in columns and rows organized in a database.

Datatype – The kind of data stored in a table or column. Oracle includes the following basic datatypes: char, varchar, number, date, and long.

Schema – The structure of an entire database (tables and fields), and how they relate to one another.

Command/Query – An operation that you perform on a database. The most basic operations you can perform on an Oracle database include the following:

  • SELECT – Retrieve data

  • CREATE – Create a table

  • INSERT – Add data

  • UPDATE – Replace a row value with the value identified in the SQL expression

  • DELETE – Remove rows from a table

  • DROP – Remove a table

Query Statement – A series of elements that are executed against a database to perform a specific command.

If you’re interested in learning more about these concepts, you could do so in under a week by taking a quick course in SQL.

Querying an Oracle Database with SQL

Learning a new programming language is a lot like learning a new spoken language. You first have to learn the rules of the language. In programming terms, the rules are called syntax. It’s important that you follow the rules to prevent errors during processing.

The most common SQL command is to query a database for specific data. You use the SELECT statement to do this:

SELECT * FROM Customers;

This is what you need to know about this statement:

  • SELECT is the operation that instructs SQL that we want to extract data from the database.

  • The asterisk is a shortcut for referring to all columns in the table. If you want to query a single column, you would use the name of the column instead of an asterisk.

  • FROM Customers indicates where SQL should get the data.

  • The semicolon at the end of the statement signals SQL the conclusion of the statement.

  • SQL reserved words such as SELECT and FROM are not case sensitive, but common practice is to use all capital letters for readability.

SQL commands have specific purposes, but they have a similar structure. Here are more examples:

Example Query 1: CREATE

CREATE TABLE Movies

(

MovieID int,

Title varchar(255)

Genre varchar(255)

Rating varchar(255)

);

This is what you need to know about this query:

  • CREATE TABLE Movies is the operation that indicates the name of the new table we want created.

  • The information between the open and close parentheses identifies the columns in the new table and their datatypes.

Example Query 2: INSERT

INSERT INTO Movies (MovieID, Title, Genre, Rating) VALUES (‘0001’, ‘Matrix Reloaded’, ‘Science Fiction’, ‘PG’);

This is what you need to know about this query:

  • INSERT INTO Movies (MovieID, Title, Genre, Rating) is the operation that informs SQL of the data we want to add. The data in the first set of parentheses indicate the columns we want to populate.

  • VALUES (‘0001’, ‘Matrix Reloaded’, ‘Science Fiction’, ‘PG’) indicates the list of values we want for each column.

Example Query 3: UPDATE

UPDATE Orders SET CustomerName= ‘Jane Done’ WHERE CustomerName= ‘John Doe’;

This is what you need to know about this query:

  • UPDATE Orders is the operation that instructs SQL which table we want to update.

  • SET CustomerName= ‘Jane Doe’ indicates the updated data

  • WHERE CustomerName= ‘John Doe’ indicates the data we want to update.

Example Query 4: DELETE

DELETE FROM Contractors WHERE ContractorName= ‘Smith Contracting, Inc.’;

This is what you need to know about this query:

  • DELETE FROM Contractors is the operation that indicates where we want to make a deletion.

  • WHERE ContractorName= ‘Smith Contracting, Inc.’ indicates the data we want to delete.

Example Query 5: DROP

DROP TABLE Movies;

This is what you need to know about this query:

  • DROP TABLE Movies is the operation that indicates which table we want to delete.

These are just the basics of using SQL with an Oracle RDBMS. The query language includes other elements that extend basic SQL functions. You are now ready to put your knowledge to work and take a course that teaches the basics of developing a local database.