SQL Tutorial
In
this post SQL Tutorial, we will learn basics of SQL. This post SQL
Tutorial is helpful for all those who are keen to learn SQL and also to
those , who are working in the field of SQL databases.
SQL
is one of the most popular language. It performs almost every operation needed
in a database language.
SQL
is basically a non procedural language, by this we mean, we give stress on what
to do, rather than how to do.
According
to various operations performed by SQL, we can divide it into following
components.
- Data definition Language
- Data manipulation Language
- Query Language
- Data Control Language.
SQL Tutorial : How to Create a Table in
Oracle SQL Plus Oracle 10G
In this section
"How to Create a Table in Oracle SQL Plus Oracle 10G" we
will learn how to create a table in Oracle SQL Plus. Before you create table in
Sql Plus , you need to decide the name of the table and various columns names
and data types of the columns.
Lets us take example of Student table ,
here you can decide name of the table as student and it could have columns as
rollno, name, fees . Data type of rollno column could be either number or char
, the data type of name column must be
varchar2 type and datatype of fees column must be number.
The general
syntax of create table in SQL SQL> create table
<table name> ( column1 specification, column2 specification...);
The sql code to create above student table will be like this:
SQL> create
table student (rollno number(3), Name varchar2(20), fees Number(8,2));
This way you can
create any table in SQL.
SQL Tutorial - Desc Table in Oracle
After Creating table, everyone wishes
to see the structure of the table. In this post Desc Table in Oracle ,
we will discuss how to view the structure of an existing table in SQL.
To
view the structure of an existing table in SQL, we have to use DESC Table
Command. After supplying this command you can see the structure of the table.
General
Syntax of DESC Table in Oracle
Desc
table-name;
Example
: Suppose we wish to view the structure of student table, we have to write it
as :
Desc
student;
SQL Tutorial - Add Column SQL
In this section Add Column SQL , we will have an
idea about alter table command of sql. After reading this post Add Column
SQL, readers will be able to add columns to existing
tables as well as they will be able to alter the existing columns of the
tables. Sometimes after the creation of a table, need of addition of a new
column exists, in that case Alter table command is helpful.
Before we discuss add column SQL
, lets us see why to add columns in an existing table. Sometimes while deciding
columns of a table, we forget to include a particular field, in that case we
can add new columns to the existing tables.
In programming , some times need of a
new columns in existing tables arises at a later stage.
In SQL, we can add a new column in an existing table
as well as we can change the specifications of the existing columns.
Suppose you have a table named employee in database as given
below-
Ecode
|
Ename
|
Ecity
|
After creating this table, need of a new column named zip code
arises. You can add this new column using alter table command.
Add Column SQL
Add column SQL requires
knowledge of alter table command of sql. Using alter table command, one can add
new columns as well as can change the specifications of the existing columns.
You can change the data types of the existing columns, one can change the field
width of the existing columns.
Add Column 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.
Whenever you will try to add column in
sql, you have to take care of data.
Suppose you have a table named employee
as given below –
Ecode
|
Ename
|
Ecity
|
E001
|
JOE
|
FLORDA
|
E002
|
JIM
|
SYDNEY
|
If
you add a new column named zip code in the above table, structure of the above
table will be like this-
Ecode
|
Ename
|
Ecity
|
Zip code
|
E001
|
JOE
|
FLORDA
|
|
E002
|
JIM
|
SYDNEY
|
|
Here
you can see Zip code column is having no values, and then what will the values
of Zip code in case of these two entered records.
In
that case you have to use update command to add values to these two
already entered records.
How
to Add Column in sql table using Alter Table command
To add new columns in an existing
table, we can use add option of alter table command.
The general syntax of alter
table command with add option is given below:
Alter
table <table-name> 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 of alter table command.
The general syntax for
modifying existing columns is given below
Alter table <table-name> 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, this is because when you to change the data type of an existing
column, if the table is having data that time, there may be some issues of data
type mismatch. Also in case of column width alteration, if you try to reduce
the width of an existing column , if that column is having values, in that case
truncation of data may happen.
SQL Tutorial - Data Manipulation
Language commands in Oracle
Data Manipulation
Language commands in Oracle includes insert,
delete and update commands. We
will discuss all these Data Manipulation Language commands in
Oracle in this section. We will discuss Data
Manipulation Language commands in Oracle with some simple
examples. Data Manipulation Language is a component of SQL , we
can perform various Data Manipulation Language operations
on database objects. In Data Manipulation Language component
, 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.
How to insert records or rows in a table using SQL
Insert command
The insert command
is used to insert records or rows in database table. Insert command is very
simple in SQL, Insert keyword is used for inserting data in the database
tables. Insert command of SQL inserts one record or row at a time.
The
general syntax of insert command is as given below:
Insert
into table name values( value1,value2,…);
Example
Insert into employee values(‘E001’,’ADAM’, ‘SYDNEY’);
In response to the above insert command , sql will generate
the message “1 row(s) created”.
By
this way you can insert as many records as you wish.
How to delete rows or records from a table in SQL
Delete Command
Delete Command is
used to delete records from a database table, we can delete any number of
records at a time in a single Delete Command. Delete Command of SQL uses Deletekeyword.
The
general syntax of Delete Command is as given below
Delete
table name [where condition];
Example
Delete employee;
This will delete all the records from employee table.
We
can delete particular rows or records of a table.
Delete
employee where ename=’Smith’;
This
will delete record of Smith only.
We can use multiple conditions in Delete Command.
Suppose we want to delete records of all that employees
whose city is Chandigarh and name is Smith.
The
SQL delete statement will be like this:
Delete
employee where ename=’Smith’ and ecity=’Chandigarh’;
How to Update records in SQL
Update Command
Update Command is
used to update existing records or rows of a database table. Update Command is
applied when some changes happens in records. Suppose in case of an employee if
he changes
his city ,then his record in database must be updated. Another example could be
for annual increments in salaries of all the employees, we need update command.
The general syntax of Update Command is
give below
Update table name set column name=new value;
Example
Suppose
you want to change present city of employee named Adam to Chandigarh
Update employee set ecity=’Chandigarh’ where ename=’Adam’;
You
can also use arithmetic operators in update, like increment the salary of all
the employees by 100.
The
update statement will be like this:
Update employee set salary=salary+100;
These
are the three basic data manipulation operations in SQL.
How to Add a Row in SQL
In this section how to add a row
in SQL, we will discuss about inserting rows in sql tables. This
is a very common question how to add a row in SQL. Before
we insert data into tables , We first have to create tables
in sql database. If the tables are already created, then before you enter
data into a SQL database table, you have to know
the name of the table as well as the various columns of the table. Along with
names of the columns, we you have to be familiar with the data types of the various
columns of the table.
How
to know the name of the various columns and their respective data types?
Is this possible to know the name and data
types of various columns of the table, yes this is quite simple to know the
name of the columns and their data types. This can be done by
using desc table command.
Desc
Table Command
The Desc
Table command of SQL is to describe the structure of the table.
For
example if we supply command like this : Desc employee;
This will display the whole structure
of the employee table. It will display the name of the columns along with
column specifications.
When you came to know the names of
various columns and their data types, their widths and constraints, we can add
rows to the table.
While
inserting rows into tables, you have to take care with number of columns and
their data types.
To add
a row in a table in sql, we have to use insert into command. The use of insert
into command is quite simple.
Insert into Command
The insert
into command is helpful for our query “ how to add a row in
sql “ . With the help of insert into command,
we can add rows in a table.
The
General Syntax of Insert into Command
Insert into <table name> values
(value1, value 2,…);
Here
table name is the name of the table in which you wants to add rows.
Suppose
the name of the table is employee, the insert statement will be like this;
Insert
into employee values (101, ‘SAM’);
After
execution of the statement, the following message will be displayed
I
row(s) created.
Here,
you have to be care full, 101 will be added to first column of the table and
SAM will be added to the second column of the table.
It
is necessary to take care of data types of while inserting rows in a table.
With one insert into command, you can
add only one row in a table.
For Multiple rows , we have to use
insert into command multiple times.
Few more examples of
Insert into Command
Given
a table named employee with columns ecode->char(4), ename->varchar2(30),
ecity->varchar2(30)
Insert into employee
values (‘E001’,’SAM’,’Florida’);
Here the first value E001 will be
stored in first column of the table, second value SAM will be stored in second
column of the table and third value Florida will be stored in the
third column of the table.
In this case, user must be aware about the sequence of the columns of employee table. If by mistake he supply insert into command as Insert into employee values (‘SAM’,’E001’,’Florida’);
In this case, user must be aware about the sequence of the columns of employee table. If by mistake he supply insert into command as Insert into employee values (‘SAM’,’E001’,’Florida’);
In this case employee name will be stored in employee code column and employee code will be stored in employee name column.
To avoid such problems, supply insert
into like this:
Insert into employee(ecode, ename,
ecity ) values (‘E001’,’SAM’,’Florida’);
This will add a row in database table,
in this case E001 will be stored in ecode, SAM will be stored in ename and
Florida will be stored in ecity column of the employee table.
Main thing here is , you donot have to
remember the sequence of columns in the table. You can supply the above insert
into command as : Insert into employee(ename, ecode, ecity ) values
(‘SAM’,’E001’,’Florida’);
Here you can see, we had altered the
sequence of values, we had written SAM as first value and E001 as second value,
we had also altered the sequence of column names in the above statement, ename
is given first and then ecode.
How to insert multiple rows in SQL Table
To insert multiple rows in a
table, we have to supply insert into command as
Insert into employee values (&eocde,
&ename,&ecity);
When you supply this command, the
following output will be generate.
Enter Value for Ecode :
Here you have to supply value for ecode
like ‘E001’
After this you will get –
Enter Value for Ename :
Here you have to supply value for ename
like ‘SAM’
After this you will get –
Enter Value for Ecity :
Here you have to supply value for ecity
like ‘Florida’
After this following message will be
generated –
1 row(s) created.
After this sql prompt will come.
Sql>
Here you have to write run
Sql>run;
Run command of sql executes the last
executable statement stored in Sql buffer. In this case it is : Insert into
employee values (&eocde, &ename,&ecity);
Again you have to supply new values,
you can repeat this step for as many times as you want.
How to add data in SQL Table
The answer to this question How
to add data in SQL Table is the use of insert into Command. To add
data in sql table, first thing is to know the name of the table, column names
of the table and data types and constraints of columns of table. Inserting rows
, inserting data, add data and add rows are interchangeable terms.
How to Delete Rows in
Oracle SQL
How to delete rows or records from a
table in SQL
Delete Command
Delete Command is used
to delete records from a database table, we can delete any number of records at
a time in a single Delete Command.
Delete Command of SQL uses Delete keyword.
The general
syntax of Delete Command is as given below
Delete
table name [where condition];
Example
Delete employee;
This will delete all the records from employee table.
We
can delete particular rows or records of a table.
Delete
employee where ename=’Smith’;
This
will delete record of Smith only.
We can use multiple conditions in Delete Command.
Suppose we want to delete records of all that employees
whose city is Chandigarh and name is Smith.
The
SQL delete statement will be like this:
Delete
employee where ename=’Smith’ and ecity=’Chandigarh’;
Summary
Hope
this post SQL Tutorial , is informative for you.Using alter table
command, one can add column in sql as well as can alter the existing columns.
