INSERTING ROWS TO THE TABLE

A).To Add Data (Rows) to the Table

Syntax: INSERT INTO <table-name> VALUES (V1,V2,V3…);

Example: INSERT INTO STUDENT VALUES (1,’Dolly’);

B).To Add Fields Interactively

INSERT INTO STUDENT VALUES (&stud_no, ’&stud_name’, ’&add1’, ’&add2’, ’&city’, ’&state’, ’&pin’, ’&birth_date’);

Note - that the names need not match the column names, the type and order is important

C).To Add Only Specific Fields

If only certain data items are to be input, then the order and name of the fields must be typed before the VALUE clause.

Syntax: INSERT INTO STUDENT(STUD_NO, FIRST_NAME, ADD1)

                        VALUES (1008,’DOLLY’,’PATEL COLONY’);

4.FILTERING TABLE DATA

A). Selected columns and all Rows:-

Example:-

            Select ename,sal from emp;

B). Selected row and all columns:-

Example:-

            Select *from emp where sal=15000;

C). Selected columns and selected rows:-

Example:-       Select ename, sal from emp where sal=15000;

5.ELIMINATING DUPLICATE ROWS WHEN USING A SELECT STATEMENT

Use:-

            A table could hold duplicate rows. In such a case to view only unique rows the distinct clause can be used. The DISTINCT clause allows removing duplicates from the result set. The DISTINCT clause can only be used with select statement.

Syntax:-

            Select DISTINCT <columnname> from <tablename>;

Example:-Select distinct *from emp;

6.SORTING DATA IN A TABLE

Use:-

            Oracle allows data from a table to be viewed in a sorted order. The rows retrieved from the table will be sorted in either Ascending or Descending depending on the condition specified in the select statement.

Syntax:-

            Select * from <tablename> ORDER BY <columnname> <[sort order]>;

Example:-

            Select *from emp ORDER BY ename DESC;

7.CREATING A TABLE FROM A TABLE

Use:-

            This command is used to create new table based on another table. The source table is the table identified in the select section of this SQL statement. The target table is one identified in the create section of this SQL sentence.

Syntax:-

            Create table <tablename> (<columnname1>,<columnnanme2>) AS SELECT <columnname1>,<columnname2> from <tablename>;

Example:-

            Create table empcopy (eno,ename) as select eno,ename from emp;

8.INSERTING DATA INTO A TABLE FROM ANOTHER TABLE

Use:-

            To inserting data one row at a time into a table , it is quite possible to populate a table with data that already exist in another table.

Syntax:-

            Insert into <tablename> select <columnname1>,<columnnameN> from <tablename>;

Example:-

            Insert into emp1(eno,ename) select eno,ename from emp;

9.DELETE OPERATION

Use:-

            The delete command deletes rows from the table that satisfies the condition provided by its where clause and returns the number of record deleted.

A). Removal of all rows:-

            Syntax:-

                        Delete from <tablename>;

            Example:-

                        Delete from emp1;

B). Removal of specific row:-

            Syntax:-

                        Delete from <tablename> where <condition>;

            Example:-

                        Delete from emp1 where eno=001;

C). Use of ‘EXIST’ clause (condition from another table):-

Use:-

            Sometimes it is desired to delete records in one table based on values in another table. Since it is not possible to list more than one table in the FORM clause while performing a delete , the EXIST clause can be used.

Syntax:-

Delete from <tablename> where EXIST (select <columnname> from <tablename> where <condition>);

            Example:-

                        Delete from em2 where EXIST (select name from emp where name=’SMITH’);

10. UPDATING RECORDS CONDITIONALLY:-

Use:-

            The update command is used to change or modify data values in a table. To update 1) all the rows from a table and 2) to update select set of rows from a tbale.

A). Updating all rows:-

            Syntax:-

                        Update <tablename> set <columnname>=<expression>;

            Example:-

                        Update emp1 set pincode=361008;

B). Update selected rows from a table:-

            Syntax:-

                        Update <tablename> set <columnname>=<expression> where <condition>;

            Example:-

                        Update emp2 set eno=002 where ename=’SMITH’;