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