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.
First lets understand in point what you will learn after reading this blog of Oracle pl/sql :
Now lets start with our first point.
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.
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.
alter table employee_master add state varchar2(255)
this will the add column state with type varchar2 in the table
alter table employee_master drop column <u>address
this will the drop column address from the table.
alter table employee_master modify state varchar2(50)
this will modify the column state varchar2(255) to varchar2(50)
Also read :
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.
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 .
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 .
Constraint on a table is used to specify rules for data on a table .
For creating constraints on a table there are two ways :
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.
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 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.