making table in oracle (With Primary Key and Not NULL)
create table stu(roll number(3) primary key,
name char(20) not null,
city char(20))
Create Table from an existing table
create table student as (select * from staff)
[This query will make a table named student with all row of staff table.Student table will take table structure as in staff table.You can make sure it by using desc student statement.You can also apple condition to get selected record from staff table]
Insert record from an existing table
Syntax
insert into <table_name> (select column_list from <table_name> [Condition]);
Example
insert into Student (select * from stu);
[ You can add condition for getting specific row from source table ]
ORDER BY
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
Example
Select * from stu order by city
Select * from stu order by city desc
[ Second query will show records in descending order ]
ALTER TABLE STATEMENT
Alter statement is used to alter a table i.e. to add/remove
or modify a column
Modify a column
Syntax :
modify <column_name> <data_type>;
Example :
alter table stu
modify name char(30);
Add a column
Syntax :
add column_name Data_type
Example :
alter table student
add sub varchar(20);
Drop Column:
Syntax:
drop column <column_name>
Example :
alter table Student
drop column sub;
Rename a Column
Syntax :
rename column Old_Name to New_Name
Example :
alter table ali
rename column city to place
Rename a Table
Syntax:
alter table <table_name>
rename to new_name
Example :
alter table Stu
rename to Student
Getting Last Row
Oracle associate a column rowid with every record.This column put a unique number of a row in increasing order.We can apply min and max aggregate function on rowid.
you can see it by using following SQL Statement
select rowid from student
Last Row
select * from t1 where rowid=(select max(rowid) from t1)
First Row
select * from t1 where rowid=(select min(rowid) from t1)
Delete two identical column.
If any table has two identical row then rowid is useful to delete particular row row because rowid always has a unique value
Group and Having
Group- The Group by clause is another section of the select statement . This option clause tells Oracle to group Rows based on distinct values that exist for specified column i.e. it create data set content several sets of records group together based on a condition
Select sum(marks),class from the student
group by class
( This query adds all the marks of related class )
Having -The Having Clause can be used in conjunction with group by clause. Having imposes a condition on a group by clause which further filter the groups created by the group by clause
Tips ! ( Use Rowid and find the unique rowid .Use this row id in delete statement)
SQL does not have not procedure capability that is SQL does not provide
the programming techniques of conditional checking looping and
branching that is vital for data testing before storage.
Select sum(marks),class from the student
group by class
having sum(marks)>50
(This query will show all the class name which sum is greater than 50)
Find the class marks which got highest marks
select max(stumark) from (select max(marks) stumark from student group by class)
(This query will show the sum of highest marks form all classes )
Change Date format
select to_date(date_column,'YYYY-MM-DD') from table;
- SQL statement are passed to the oracle engine one each time an SQL statement is executed
a call is made to the engine resources.This adds to the traffic on the network there by deceasing,
the speed of data processing, specially in a multi user environment.
- While processing an SQL sentence if an error occurs the oracle engine displays its own Error messages. SQL has no facility for program handling of errors that arise during manipulation of data.
Steps for run PL/SQL
- First type your PL /SQL code on oracle command prompt
- Run code using /
- If you want to edit your code use ed to open SQL Editor
Print Your name in PL/SQL
begin
dbms_output.put_line('Hello PL/SQL');
end;
/
