DML commands

DML commands

Some more DML commands,

·       Inserting records into all fields

·       Inserting records into selective fields

·       Continuous insertions

·       Inserting records using SELECT statement

 

Inserting records into all fields:

Table_name:  Student

Id	Stud_name	Join_date	Class

Syntax:

INSERT INTO table_name VALUES (value1,value2,……);

Example:

    INSERT INTO Student VALUES (101,’Thara’,’10-jun-2010’,10);

Output:

Id	Stud_name	Join_date	Class
101	Thara	10-Jun-2010	10

 

Inserting records into selective fields:

Syntax:

INSERT INTO table_name (Column1,column2,..) VALUES (value1,value2,…);

 

Example:

    INSERT INTO student (id,stud_name) VALUES (102,’Lara’);

 Output:

Id	Stud_name	Join_date	Class
101	Thara	10-Jun-2010	10
102	Lara

        

Continuous insertions:

·       It insert record continuously

 Syntax:                   

INSERT INTO table_name VALUES (&column1,’&column2’….);

 Example:

  INSERT INTO student  VALUES (&no,’&name’);

Inserting Records Using SELECT statement

1.             1.    insert into employees  select empno, ename from emp;

  2.    insert into employees select empno,ename from emp where deptno=20;

  3.    insert into employees (no,name)  select empno,ename from emp where    deptno=40;

Select Command:

Select * from table_name;

Select query for list all tables and views and synonyms:

Select * from tab;

Display selective columns:

·       select ename, sal, job from emp;

Using Alias Name:

·       select ename Employee_name, sal  Salary from emp;

   Here, Employee_name , salary are alias Name of the column.

Select statement with condition:

Syntax:

     Select   *  from  table_name where <condition>;

Example:

1.    Select * from emp where deptno=10;

2.    Select * from emp where sal>=1250;

3.    Select * from emp where sal>=1250 and deptno=30;

4.    Select * from emp where comm is null;

5.    Select * from emp where ename=‘BLAKE’;

Sorting the Data

Ascending Order

select ename,sal from emp order by sal;

select ename,sal from emp order by sal asc;

Descending Order

select ename,sal from emp order by sal desc;

Copying the structure and records of a Table

A new Table created from existing table

Syntax:

Create table table_name as select column_name from table_name [where  <condition> ];

Example:

1.           Create table emp2 as select * from emp;
2.           Create table emp3 as select ename, sal from emp;

Copying the structure

Here we give any false condition only structure will be copied.

Example:

     Create table emp2 as select * from emp where 1=2;

Inserting Records Using SELECT statement

Syntax:  

INSERT INTO table_name SELECT column_name FROM table_name;

 Example: 

1. Insert into emp3 select empno, ename from emp;

2.  Insert into emp4 select empno, ename from emp where deptno=20;

Share: