SQL Commands with Examples in Oracle

SQL Commands with Examples in Oracle is our topic in this post. Our idea in the post SQL Commands with Examples in Oracle is to give some hands on practice to readers of this post.

SQL- STRUCTURED QUERY LANGUAGE

sql commands with examples in oracle

SQL stands for structured query language. SQL is a non procedural language. Non procedural languages are those languages in which stress is given on what to do, not on how to do. SQL is popular from last few decades. SQL is basically a tool for creating database objects, dropping database objects, tool for data manipulation operations.

Components of SQL

Based on various operations performed by SQL, it can be divided into following components

DDL Data definition Language

DML Data manipulation Language

DCL Data control Language
QL Query Language

DDL- Data definition Language

In Data definition Language part of SQL , new database objects can be created, existing data base objects can be dropped. We can also alter database objects in this part of SQL.

We can create a table in SQL using Create table command. By using Drop table command , existing table can be dropped. We can add new columns in existing tables using Alter Table command.

DML- Data manipulation Language

In Data manipulation Language, we can perform various data manipulation operations on database objects. We can insert records in the tables, we can delete records from database and we can update records of the tables. To insert new records or rows, Insert command can be used. For deletion of records from database tables, Delete command can be used. To update records of table, Update table command can be used.

DCL Data control Language

In Data control Language part of SQL, new users can be created. We can Grant privileges to the users and also can revoke privileges from the users. For creating new users, we can use Create User command can be used. For grating privileges to the users, Grant command can be used.

Transactions can also be controlled in this part of SQL. Transactions can be controlled using Rollback and commit commands of SQL.

QL- Query Language

It is most popular components of SQL. In this component of SQL we can query from database objects. For querying, Select From Where clause can be used.

DDL Data definition Language

In Data definition Language part of SQL , new database objects can be created, existing data base objects can be dropped. We can also alter database objects in this part of SQL.

How to create a table in SQL

Create Table Command

We can create tables in SQL using Create table command of SQL. The general syntax of Create Table command is given below

Create table table_name( colum1 specification, colum2 specification, …);

Lets us create a table named employee, let this table have three columns namely ecode, ename and ecity.

ecode for employee code- specification is char(4)

ename for employee name- specification is varchar2(20)

ecity for employee city- specification is varchar2(20)

SQL> Create table employee( ecode char(4),ename varchar2(20), ecity varchar2(2));

This will create table employee in database. In this way you can create any table

How to Drop a table in SQL

Drop Table Command

We can drop an existing table in SQL, by using Drop Table Command.

The general syntax of Drop Table command is as given below

DROP Table Table_Name;

Suppose we have table named employee in database, we can drop this table as

SQL> Drop table employee;

This will drop employee table from the database.

How to alter a table in SQL

Alter Table Command

A table in SQL can be altered by using alter table command. By using alter table command , we can add new columns to an existing table. We can also change specification of the existing columns of a table using Alter Table command.

Adding New Columns using Alter Table command.

To add new columns in an existing table, we can use add option.

The general syntax is given below:

Alter table tablename add( new column specification);

Suppose we want to add zipcode column in employee table.

SQL>Alter table employee add(zipcode number(10));

This will add a new column zipcode to employee table.

Modifying existing columns of a table

To modify specification of existing columns of a table ,we can use modify option with alter table command.

The general syntax for modifying existing columns is given below

Alter table tablename modify( column new_specification);

Suppose we wants to chamge filed width of ecode column from 4 to 6.

SQL>Alter Table employee modify( ecode char(6));

Note: Be careful while changing column specifications

ETH Times