Oracle SQL Overview

Main Page >> Oracle and SQL >> Workbook >> SQL Overview

Introduction

SQL is the standard language for manipulating data in a relational DBMS, becoming "the de facto standard in the relational database world" (Chris Date). It is English-like and non-procedural and can be used to create, query and update data, or control access to the database. It has a small number of statement types, yet is very powerful and flexible.

SQL can be used in a number of ways:

• Interactively. Whereby the user directly types in a command to the database.
• Embedded. Where the commands are embedded in a host language, such as C, Java or COBOL.

For the purposes of this workbook, we shall be using SQL in interactive mode.

SQL processes data a set-at-a-time, which means it works with groups of data items, e.g., the entire DEPT table. Traditional data processing usually works a record-at-a-time, which means to process more than one record, e.g., to update the salary of all lecturers in the Computing Department, the programmer would have to code loops in the program to process all records. SQL eliminates the need to write loops, making it a very powerful language.

SQL has three major components:

• a data definition language (DDL) for defining the database structure
• a data manipulation language (DML) for retrieving and updating data
• a data control language (DCL) for controlling access to the data.

With some types of database management systems (DBMS), typically hierarchical and network databases, the database user would need to know a separate language for each of these components.

SQL combines all three within one language; however, we will be concentrating on the DDL and DML commands initially.

The basic structure of the SQL command is

SELECT ................................
FROM ..................................
WHERE .................................

• The SELECT clause indicates the columns (attributes) to be retrieved.
• The FROM clause indicates the tables from which the columns are to be retrieved.
• The WHERE clause is optional and is used to specify a selection condition.

For example type in:

SELECT  * FROM EMP; 

Or you can type the same command over 3 lines:

SELECT 
*  
FROM EMP;

⇒ What do these commands retrieve?

Note: a command can extend from one line to another; however you must put a semi-colon (;) at the end of the command. This tells Oracle to go and do something. If you forget to add it on the end of the command just simply type it on the next blank line.

The results to both commands will look similar to below:

http://mi-linux.wlv.ac.uk/~oracle/wiki/sqlPlus.png

In summary SQL can be used to:

• Get information from a database
• Add new information to a database
• Revise information in a database
• Remove information from a database
• Create new tables

Next Step

Setting the Environment Settings.