How to Managing Tables and Data in Oracle

1.CREATING A TABLE

In order to create a table, the DDL, create is used and the columns of the table along with the data typ0es and the width are to be specified. The names given to the tables and its columns must follow certain rules. When a table is created, Oracle puts the table definition into the data dictionary.

Rules for Table and Column Names

·         Names can be up to 30 characters.

·         Names must begin with an alphabet.

·         Names cannot contain quotes.

·         Names are NOT case sensitive.

·         Names can contain characters, a-z, 0-9,_, $ and #.

·         Database names can be up to 8 characters.

·         Names cannot be reserved words.

·         The above rules can be bypassed by using double quotes.

Syntax 1

                        CREATE TABLE <table-name>

                        (Field-name1 Field-type (width),

                        Field-name2 Field-type (width),

                        Field-name3 Field-type (width) );

Example: - Let us create a table to store STUDENT data like the roll number, name and the birth date.

SQL>CREATE TABLE student (roll_no number (5),name  char (20), Sbirth_date date);

Note: For the data type of date, width must not be specified.

Table created 

Syntax 2

As the fields are names, certain constraints can be specified. Constraints are rules to control the data in a column. The common constraints include Primary key, Not Null, and Check constraint.

CREATE TABLE <table-name>

(

            Field-name1 Field-type  (width)  PRIMARY KEY,

            Field-name2 Field-type  (width)  NOT NULL,

            Field-name3 Field-type  (width)  CHECK (field-name in (‘a’,’b’,’c’))

);

PRIMARY KEY :- This is a constraint for the column of a table. The Primary key constraint ensures that the column cannot be null and that the values in the column will be unique.

NOT NULL :- The NOT null constraint ensured that the user always type the data for that column.

CHECK :- The check constraint ensures that when data is entered, the data in the column is limited to specific values, like a, b or c.

2.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’);

3.VIEWING DATA IN THE TABLES

            A.) All Rows and all columns:-

            Select *from emp;

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’;

11. MODIFY THE STRUCTURE OF TABLE:-

Use:-

            The structure of a table can be modified by using the ALTER TABLE command. Alter table allow changing the structure of an existing table with ALTER TABLE it is possible to add or delete columns , create or destroy index , change the data type of existing columns , or rename column or the table itself.

A). Adding a new column:-

            Syntax:-

                        Alter table <tablename> ADD (<newcolumnname> <datatype> (<size>));

Example:-       Alter table emp2 add (sal number(5));

B). Dropping (removing) a column from a table:-  

            Syntax:-

                        Alter table <tablename> DROP column <columnname>;

            Example:-

                        Alter table emp2 drop column sal;

C). Modifying existing column:-

            Syntax:-

                        Alter table <tablename> MODIFY (<columnname> <newdatatype><newsize>);

            Example:-

                        Alter table emp2 modify(sal number(6));

12. RENAMING TABLE:-

Use:-

            Oracle allows renaming of tables. The rename operation is done automatically, which means that no other thread can access any of the table while the rename process is running.

            Syntax:-

                        Rename <tablename> to <newname>;

            Example:-

                        Rename emp2 to emp3;

13. TRUNCATING TABLES:-

Use:-

            Truncate table empties a table completely. Truncate option drop and re create the table, which is much faster than deleting rows one by one.

Syntax:-

            Truncate table <tablename>;

Example:-Truncate table emp2;

14. DESTROYING TABLES:-

Use:-

            For removing whole table.

            Syntax:-

                        Drop table <tablename>;

            Example:-

                        Drop table emp2;

REFERENTIAL INTEGRITY

Referential integrity is the property that guarantees that values from on column depend on values in another column. This property can be enforced through the referential integrity rule. In order to maintain this referential integrity, certain rules must be applied:

1. The master table or the Parent table, which contains the linking column, MUST be a primary key.
2. The linking column in the transaction table or the child table REFERENCES the Parent table. This column in the child table, which references a Primary key of the Parent table, is referred to as a Foreign key.

To apply the above two rules to set up a referential integrity between the Student table and the Marks table the following steps are followed:

CREATE TABLE STUDENT (ROLLNO NUMBER(3) PRIMARY KEY, NAME CHAR(20), CITY CHAR(20));

CREATE TABLE MARKS (ROLLNO NUMBER(3) REFERENCES STUDENT, SUB1 NUMBER(3), SUB2 NUMBER(3));

ON DELETE CASCADE

This is referential integrity rule which allows the user to delete rows from the master and at the same time it will delete the dependent rows in all the child tables.

CREATE TABLE STUDENT (ROLLNO NUMBER(3) PRIMARY KEY, NAME CHAR(20), CITY CHAR(20));

CREATE TABLE MARKS (ROLLNO NUMBER(3) REFERENCES STUDENT ON DELETE CASCADE, SUB1 NUMBER(3), SUB2 NUMBER(3));

ON UPDATE CASCADE

This is referential integrity rule which allows the user to update rows from the master and at the same time it will update the dependent rows in all the child tables.

CREATE TABLE STUDENT (ROLLNO NUMBER(3) PRIMARY KEY, NAME CHAR(20), CITY CHAR(20));

CREATE TABLE MARKS (ROLLNO NUMBER(3) REFERENCES STUDENT ON update CASCADE, SUB1 NUMBER(3), SUB2 NUMBER(3));

CLAUSES

1)WHERE:-

Use:-

            Oracle provides the option of using a WHERE CLAUSE in an SQL query to apply a filter on the row retrieved.

            Syntax:-

                        Select *from <tablename> where <condition>;

            Example:-

                        Select *from emp2 where eno=2;

2)ORDER BY:-

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 order.

Syntax:-

Select *from <tablename> order by <columnname> <sortorder>;

Example:-

Select *from emp order by name desc;

3)GROUP BY WITH HAVING:-

The GROUP BY clause is used to produce summary information for a subset of the table. The GROUP BY clause will produce a single row for all the rows that meet a particular condition.

Syntax:-

Select <columnname1>,<columnname2>, AGGREGET FUNCTION (<expression>) from <tablename> where <condition> GROUP BY<column1>;

Examples:-

Consider the table, SALARY:

EMP_NO	BASIC	COMM	DEDUCT	SALARY_DATE
1001	3000	200	250	30-JUN-96
1002	2500	120	200	30-JUN-96
1003	3000	500	290	30-JUN-96
1004	2500	200	300	30-JUN-96
1005	2800	100	250	30-JUN-96
1001	3000	200	250	31-JUL-96
1002	2500	120	200	31-JUL-96
1003	3000	500	290	31-JUL-96
1004	2500	200	300	31-JUL-96
1005	2800	100	150	31-JUL-96

To SUM the Salary of Each Employee:

SQL> SELECT EMP_NO,SUM(BASIC) FROM SALARY GROUP BY EMP_NO;

EMP_NO	SUM(BASIC)
1001	6000
1002	5000
1003	6000
1004	5000
1005	5600

To SUM the Salary of Each Employee and Sort it on the Sum of Basic:

SQL> SELECT EMP_NO, SUM (BASIC) FROM SALARY GROUP BY EMP_NO ORDER BY SUM (BASIC);

EMP_NO	SUM(BASIC)
1002	5000
1004	5000
1005	5600
1001	6000
1003	6000

To SUM the Salary of Each Employee and Sort it in the Descending Order of the Sum of Basic:

SQL> SELECT EMP_NO, SUM(BASIC) FROM SALARY GROUP BY EMP_NO ORDER BY SUM(BASIC) DESC;

4)HAVING:-

HAVING Clause:

The HAVING clause can also be used with a join query that includes a GROUP BY clause. The HAVING is useful for specifying a condition for the group. The WHERE clause is used to specify conditions to retrieve Rows of a table, and the HAVING clause is used to specify a condition to a retrieve group data.

Syntax:-

Select <columnname1>,<columnname2>, AGGREGET FUNCTION (<expression>) from <tablename> where <condition> GROUP BY<column1> HAVING <condition>;

Example:

SQL> select pname, sum(order_qty) from product group by pname having pname=”cpu”;