Oracle SQL Commands: Part 1

While using SQL there are some queries we can use on a frequent basis, since these queries play a vital role in storing the data into the database. But, before explaining these queries, let us first understand what "SQL" is.

Structured Query Language (SQL)

SQL stands for Structured Query Language, and is a standard computer language for querying Relational Database Management Systems (RDBMSs) and to store, manipulate and retrieve the data stored in a relational database. All the RDBMS systems use SQL as the standard database language.

Now the question is, what is a RDBMS?

As said above, it stands for Relational Database Management System. It is a database engine that is based on relational model invented by E. F. Codd and uses SQL to access the database.

The data in a RDBMS is stored in database objects called tables. It is the basis for SQL and other modern database systems like:

MySQL
Microsoft Access
ORACLE Sybase
Informix
Postgres
SQL Server
DB2

SQL commands are mainly divided into six parts and these are as follows.

1. Data Definition Language Commands (DDL)

DDL includes:

CREATE TABLE	CREATE SEQUENCE	DROP USER
CREATE VIEW	CREATE SYNONYM	DROP INDEX
CREATE USER	ALTER TABLE	DROP TRIGGER
CREATE DATABASE	ALTER VIEW	DROP PROCEDURE
CREATE INDEX	ALTER USER	DROP SCHEMA
CREATE TRIGGER	ALTER TRIGGER	DROP FUNCTION
CREATE PROCEDURE	ALTER SEQUENCE	DROP SEQUENCE
CREATE SCHEMA	ALTER SESSION	DROP SYNONYM
CREATE FUNCTION	DROP TABLE	DROP JAVA
CREATE JAVA	DROP VIEW	TRUNCATE TABLE

CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE TABLE are the commands that are generally used.

2. Data Manipulation Language (DML)

DML includes:

DELETE

INSERT

SELECT

UPDATE

EXPLAIN PLAN

3. Data Control Language (DCL)

DCL includes:

GRANT

REVOKE

4. Transaction Control Commands (TCC)

TCC includes:

COMMIT

ROLLBACK

SAVE POINT

SET TRANSACTION

5. Clauses

Clauses includes:

CONSTRAINT CLAUSE

DROP CLAUSE

6. Pseudocolumns

Pseudocolumns include:

LEVEL PSEUDOCOLUMN

ROWNUM PSEUDOCOLUMN

CURRVAL and NEXTVAL PSEUDOCOLUMN

ROWID PSEUDOCOLUMN

OL ROW STATUS PSEUDOCOLUMN

Now, assume the following table to understand the examples.

DDL Commands

1. Create Table Command

It is one of the most fundamental components of SQL. We need to create a table when we want to store some data, and the CREATE TABLE command helps us to do that. And it is compulsory that each table must have its name. Integrity Constraints like primary key, foreign key and unique key can be defined for the columns while creating a table, we can also specify a default value for a column. Different RDBMS systems have different CREATE statements.

Syntax

CREATE TABLE table_name

(

Col1 Datatype Constraint

Col2 Datatype Constraint

Col1 Datatype Constraint

Constraint-list

)

Example

CREATE TABLE Student

(

Stu_ID Number(10),

Stu_Name varchar2(20),

RollNo Number(10),

Stream varchar2(20)

);

Result

Student Table

Stu_ID	Stu_Name	RollNo	Stream
101	Shreya	5	Commerce
102	Rohan	9	Science
103	Ronit	17	Science

While creating a table, the following characteristics may be assigned INDEXes, auto incrementing columns, and constraints like:

NOT NULL
DEFAULT
UNIQUE
CHECK
Primary Key
Foreign Key and so on.

2. CREATE VIEW Command

A new view is created by a CREATE VIEW command. A view looks like a table since it also has rows and columns. As in a table, its fields are also the same as the table fields but the difference is when we create a view it extracts the given fields and records from one or more related tables.

It provides the flexibility, security and simplified database queries and the complexity of the database is hidden.

Syntax

CREATE View View_Name AS "SQL Statement";

Example

Employee Table

Emp_ID	Stu_Name	Emp_Name	ContactNo	Designation	Salary
111	Jimmy	8751845624	CommercelNo	Soft. Eng	50Km
222	Alberta	8655146564	ScienceNo	Tech Lead	65KM
333	Harrison	6774526673	Scienceo	Sr. Soft. Engg	60km

CREATE View View_Employee;
AS SELECT Emp_ID, Designation, salary
FROM Employee;

Result

Emp_ID	Designation	Salary
111	Soft. Eng	50Km
222	Tech Lead	65KM
333	Sr. Soft. Engg	60km

3. Create User Command

This command adds a user to the current database, in other words it creates a database account through which you can log into the database using a username that is unique.

Syntax

CREATE USER username

IDENTIFIED { By Password | EXTERNALLY |GLOBALLY AS }

Example

CREATE USER Joseph

IDENTIFIED BY JSH123;

In the preceding example we are creating a user called "Joseph" in the Oracle database whose password is JSH123, in other words that the identification of Joseph is his Password.

4. Create Database Command

The Create database command is the simplest SQL command to create a database in Oracle. To use the RDBMS for storing the data we need to first create the database that is a collection of organized data.

Syntax

CREATE DATABASE "database_name";

Example: Suppose we want to create a new database known as "Quotation" then the command will be:

CREATE DATABASE Quotation;

5. Create Index Command

This command creates the indexes in the tables, so that the information can be quickly and efficiently retrieved from the SQL database without reading the entire table.

Syntax

CREATE INDEX index_name

ON table_name (Col1, Col2, Col3);

Example: Assuming a Customer Table as in the following:

Cust_ID	Stu_Name	Cust_Name	City
1001	Jimmy	vikas	Jalandar

CREATE INDEX MyIndex

ON Customer (Cust_Name);

This query will create an index called "MyIndex" on the Customer table.

Next Article: Oracle Commands : Part 2