Learn Oracle PL/SQL : Create & Use Tables

Hello  friends , I hope you understood my last blog Learn Oracle PL/SQL with Live Project Part-1  In that blog I teach you that variable type which we usually use in Oracle pl/sql. Now in this blog i will tell you about tables , where and how to use them.

Learn Oracle PL/SQL :Create & Use Tables

 

First lets understand in point what you will learn after reading this blog of Oracle pl/sql :

  1. Create Table in Oracle pl/sql
  2. Alter Table in Oracle pl/sql
  3. Drop Table in Oracle pl/sql
  4. Truncate Table in Oracle pl/sql
  5. Delete Table in Oracle pl/sql
  6. Add Constraint to Table in Oracle pl/sql
  7. Insert in Table in Oracle pl/sql
  8. Select Table in Oracle pl/sql

Now lets start with our first point.

Create Table

Now we will create table in our database .

 CREATE TABLE Employee_master (
PersonID NUMBER(14,2),
LastName varchar2(250),
FirstName varchar2(255),
Address varchar2(255),
City varchar2(255)
);

Here in this query to create table  we are creating the Employee_master table where PersonID in number type which will employee numerical id.

Lastname,firstname ,address city is of type varchar2 which will hold variable length string .

The table will look like this.

Alter Table

We have created the table now we have alter table. Means we need to change something in our table . In this you can add , delete or modify columns in a created table. And  add & drop of constraints.

Lets see how we will do it.

Add Column

alter table employee_master add state varchar2(255)

this will the add column state with type varchar2 in the table

Drop Column

 alter table employee_master drop column <u>address

 

this will the drop column address from the table.

 

Modify Column

 alter table employee_master modify state varchar2(50)

this will modify the column state varchar2(255) to varchar2(50)

Also read :

Learn Oracle PL/SQL : Create & Use View
Learn Oracle PL/SQL : Variables
Top 10 ORACLE PL/SQL Interview Questions

Drop table

The drop table is used to drop existing table from the database in Oracle pl/sql .

 DROP TABLE employee_master

This table will deleted from database & you will lose all your data.

 

Truncate table

The truncate table is used to delete all the data from the table .  It is different from delete table .

 TRUNCATE TABLE employee_master

This query will delete entire table data ,because we cannot use where clause in TRUNCATE TABLE .

 

Delete table

The delete  table is used to delete  the data from the table with where condition .  In this data is deleted row wise & log is maintained row wise.

delete  employee_master where PERSONID=1

This query will delete  table data ,because we  use where clause  .

 

Add Constraint to Table

Constraint on a table is used to specify rules for data on a table .

For creating constraints on a table there are two ways :

  1. When you create table in Oracle pl/sql.

  2. Alter table on existing table in Oracle pl/sql.

Lets see on creating table first.

CREATE TABLE EMPLOYEE_MASTER

(

PERSONID   NUMBER(14,2) PRIMARY KEY,

AADHAR     NUMBER (12) NOT NULL UNIQUE,

LASTNAME   VARCHAR2(255 BYTE) NOT NULL,

FIRSTNAME  VARCHAR2(255 BYTE) NOT NULL,

CITY       VARCHAR2(255 BYTE) DEFAULT 'DEHRADUN',

STATE      VARCHAR2(50 BYTE)

)

Here we created a table PERSONID with primary key constraint, Aadhar with not null & unique, last name, firstname with not null constraint & city with default value ‘DEHRADUN’.

Let us move on to alter table, we will put constraint on an existing table.

ALTER TABLE EMPLOYEE_MASTER ADD PRIMARY KEY (PERSONID)

We are adding primary key on column PERSONID.

ALTER TABLE EMPLOYEE_MASTER ADD CONSTRAINT PK_Person PRIMARY KEY (PERSONID, LastName);

In this query we adding primary key as constraint by name & making two columns as primary key.

Insert in table

We have seen how to create and alter table, but know we will see how to insert data in a table

insert into EMPLOYEE_MASTER (PERSONID,AADHAR,LASTNAME,FIRSTNAME,CITY,STATE)VALUES(1,822010259644, 'GOSWAMI','RAJIV','DEHRADUN','UTTARAKHAND')

Select in table

Select statement is used to select the data in table.

select * from EMPLOYEE_MASTER

This query will show all the data of table.

SELECT * FROM EMPLOYEE_MASTER WHERE LASTNAME=<u>'GOSWAMI'

 

This query will select the data from the table by filtering it by lastname.

We have a lot of other things we can do with select statements will discuss it afterwards like group by ,all type joins .

 

This is all we do with a table  if I left something in this please remind me I will add that thing to my blog. Hope you learned all the things regarding table. In our next blog we will study Views. Which also an important topic in in Oracle pl/sql.

  •  
    12
    Shares
  • 12
  •  
  •  
  •  
  •  
  •  

About the Author

Prashant Goswami

I am the blog writer who will teach how to love programming. My experience in programming is about 6 years. And i am the lover of programming , so i will make you people.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: