ddl and dml commands in sql

ddl and dml commands in sql

SQL Tutorial 

In this post ddl and dml commands in sql , we will learn basics of SQL. This post ddl and dml commands in sql  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.

In this part of the post ddl and dml commands in sql, we will discuss about ddl commands,later on in the post we will discuss about various dml commands of sql.

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;

oracle sql tutorial online free 

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 ofSQL 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

This part of SQL Tutorial is for learning delete command of 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

This part of SQL Tutorial is for learning UPDATE command of 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

This part of SQL Tutorial is for learning Insertion of Data in to Tables.

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 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 ddl and dml commands in sql, is informative for you.Using alter table command, one can add column in sql as well as can alter the existing columns.

Read More