oracle turorials(simple pl/sql program)

In this topic first we download oracle database and after complete

download process and go to database home page.

first write this simple  program

first we create table

1.create table student(no number(10),name varchar2(10),city varchar2(10));

after create this table click run button we see structure write this code

desc student;

and click on run button

table as bellow

 completed table. now we are insert data in table.

write this code.

2.insert into student(no,name,city)values(1,'pdv','rajkot');

inserted data in student and click the run  button.

select *from student;and click run button.

you see bellow picture in database.

 also you select you like column in this way

select no,name,city from student;

 In this topic we create new table employee.

1.create table employee(no number(10),name varchar2(20),salary number(30),city varchar2(20));
desc employee;

To see table as bellow

 now we insert data  in employee.

2.insert into employee(no,name,salary,city)values(1,'abc',6500,'delhi');
select *from employee;

and see structure of employee as bellow.

 you see full picture of  employee.

 In this topic we create table student 2.

1.create table student2(id number(10),name varchar2(20),gender varchar2(10),state varchar2(10));
desc student2;

you insert multiple values like this way insert one value and click the run button. 

insert into student2(id,name,gender,state)values(1,'abc','male','gujarat');
insert into student2(id,name,gender,state)values(2,'xyz','female','delhi');
select *from student2;

 Multiple insert data and show picture like this way.

 also we see only selected name 

select *from student2 where name=abc;

select id and name.

 select id||'-'|| name||'-'|| state from student2;

 select *from student2 where gender='male' and state='gujarat';

 select *from student2 order by id desc;

 you see full structure of program

 5.create table employees(no number(10),name varchar2(20),city varchar2(9),designation varchar2(10));
desc employees; and see table structure.

 After created table and insert data

insert into employees(no,name,city,designation)values(1,'abc','rajkot','manager');
insert into employees(no,name,city,designation)values(2,'cyt','mumbai','staff');
insert into employees(no,name,city,designation)values(3,'xyz','mahva','junior');
insert into employees(no,name,city,designation)values(4,'ab','rajkot','manager');
insert into employees(no,name,city,designation)values(5,'bc','pune','staff');

show table

select *from employees;

 select *from employees where name like 'a%';

 select *from employees where name like '%b%';

 select *from employees where name like '_c%';

 select *from employees where name like '__c%';

 select *from employees where no between 3 and 5;

Create new table country.

 6.create table country(no number(5),name varchar2(20));
desc country;

 insert into country(no,name)values(1,'India');
select *from country;

 If you want to insert new row,then insert that way using alter.

alter table country add capital varchar2(10);

7.create table product (id number(10),name varchar2(10),sales varchar2(10));

desc product;

 

 you want to see sum 

insert into product(id,name,sales)values(1,'keybord',300);
insert into product(id,name,sales)values(2,'mouse',100);
insert into product(id,name,sales)values(3,'keybord',200);

 

 sum of this items 

select sum(sales) from product;

select name,sum(sales)from product group by name having sum(sales)

 

 8.create table sale(id number(10),sale_name varchar2(20),qty number(20));
desc sale;

 insert into sale(id,sale_name,qty)values(12,'dress',100);
insert into sale(id,sale_name,qty)values(13,'dress',100);

update query like this way.

 

 update sale set qty=120 where id=13;

we can add new row 

alter table sale add Rs decimal(8,4) default 200;