What is the difference between Oracle, SQL and PL/SQL?

What is the difference between Oracle, SQL and PL/SQL?

The article is very interesting. Too Good! And also please describe what is the difference between SQL and Oracle and PL/SQL.
Sudhat, thanks for the comments. I love to hear feedback.
And your question is a common one so I thought I would address it as a blog entry instead of a reply to your comment. I'm going to expand on it a little bit also. In addition to SQL and PL/SQL, I hear people refer to SQL commands so I am going to address the question, "What is the difference between SQL, SQL Commands and PL/SQL.

What is Oracle?

I cover this answer in the entry, Learn Oracle - What is Oracle?. I also cover SQL and PL/SQL very briefly in that article. I'll try to be a little more explicit in this entry.

What is SQL?

SQL is the Structured Query Language. Actually, I think I covered SQL pretty nicely in the article Sudhat commented on, Intro to basic SQL.

I'll just add that SQL is composed of DML and DDL. DML are the keywords you use to access and manipulate data, hence the name Data Manipulation Language. DDL are the keywords you use to create objects such as views, tables and procedures, hence the name Data Definition Language. Examples of DML are SELECT, UPDATE, INSERT, MERGE, DELETE, etc. Examples of DDL are CREATE TABLE, ALTER VIEW, CREATE OR REPLACE PROCEDURE, etc.

Example DDL:

CREATE TABLE whatever ( column1 NUMBER NOT NULL PRIMARY KEY, column2 VARCHAR2(10), column3 DATE );
Example DML:

INSERT INTO whatever (column1, column2, column3) VALUES (1, 'Text Data', sysdate ); SELECT column1, column2, column3 FROM whatever; COMMIT;

What are SQL Commands?

SQL commands are commands that aren't actually a part of the SQL standard but are supported by the tools that support SQL. For example, SQL*Plus has been around a long time and many tools that allow scripting also allow SQL*Plus commands. When someone asks me about a SQL command, I first clarify if the mean a SQL keyword or a SQL*Plus (or some other tool) command.

SQL commands are usually meant to help format output: BREAK, BTITLE, COLUMN, PRINT or they are meant to create or store data or scripts: COMPUTE, DEFINE, STORE, SAVE. There are also commands that interact with the database: SHUTDOWN, CONNECT, COPY. And there is at least one that interacts with data: XQUERY.

So when you hear someone refer to a SQL command, first ask if they really mean SQL or if they mean the SQL tool they are using.

What is PL/SQL?

The quick answer is from the PL/SQL User Guide:

PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.

But what does that mean? The key here are the words: procedural extension of SQL. PL/SQL is a procedural language like C++, Java, ADA, etc. If has variables and variable declarations, conditional controls like IF and CASE. It has looping structures such as LOOP, FOR LOOP and the WHILE LOOP. PL/SQL uses SQL to use, manipulate and save data to the database.

If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.

Example PL/SQL:

BEGIN -- A PL/SQL cursor FOR c1 IN ( SELECT * FROM whatever ) -- This is SQL called by PL/SQL LOOP DBMS_OUTPUT.PUT_LINE( 'Column1 is: ' || TO_CHAR(c1.column1) || ', Column2 is: ' || c1.column2 || ', Column3 is: ' || TO_CHAR(c1.column3 ) ); END LOOP; END;

And now the answer to the main question: what is the difference between SQL and PL/SQL? SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don't normally have a "SQL application". You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.

Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.

I hope that clears it up some!, I may continue the series.

Take care

download it here.

for further queries

Email me shraddha_garg@rediffmail.com

SQL Tutorial

SQL (Structured Query Language) is a computer language aimed to store, manipulate, and retrieve data stored in relational databases. The first incarnation of SQL appeared in 1974, when a group in IBM developed the first prototype of a relational database. The first commercial relational database was released by Relational Software (later becoming Oracle).

Standards for SQL exist. However, the SQL that can be used on each one of the major RDBMS today is in different flavors. This is due to two reasons: 1) the SQL standard is fairly complex, and it is not practical to implement the entire standard, and 2) each database vendor needs a way to differentiate its product from others. In this tutorial, such differences are noted where appropriate.

This SQL programming help site lists commonly-used SQL statements, and is divided into the following sections:

• SQL Commands: Basic SQL statements for storing, retrieving, and manipulating data in a relational database.
• Table Manipulation: How SQL statements are used to manage tables inside the database.
• Advanced SQL: Advanced SQL commands.
• SQL Syntax: A single page that lists the syntax for all the SQL commands in this tutorial.

For each command, the SQL syntax will first be presented and explained, followed by an example. By the end of this tutorial, you should have a good general understanding of the SQL syntax, and be able to write SQL queries using the correct syntax. My experience is that understanding the basics of SQL is much easier than mastering all the intricacies of this database language, and I hope you will reach the same conclusion as well.

If you are interested in how to retrieve data using SQL, we recommend that you start with the SQL Commands section. If you are interested in understanding how SQL can be used to manipulate database tables, we recommend that you start with the Table Manipulation section. If you are looking for help on a specific SQL command, you can use the Site Map to find the command you are looking for.

Okay, enough introduction. Bookmark this site now and start now to learn SQL!