Sql Commands For Oracle |
|
|
|
|
|
|
CREATE TABLE:- |
Syntax : Create table table_name (col_1 datatype,
Col_2 datatype,
.....,
.....) |
|
|
Example :-
Create table employees ( name
varchar2(25) not null,
Emp_id varchar2(10),
Dept_no number(2),
Salary number(6),
Hire_date date
); |
|
ALTER TABLE:- |
To Modify A Column:- |
|
|
Syntax:- Alter table
table_name modify col_name datatype.
Example:- Alter table employees modify varchar2(25); |
|
|
|
|
|
To Rename A Table:- |
|
|
Syntax:- Alter table
table_name rename to new_name;
Example:- Alter table
employees rename to emp; |
|
|
|
|
|
To Rename A Column:- |
|
|
Syntax:- Alter
table table_name RENAME COLUMN col_name to New_name
Example:- Alter table employees RENAME COLUMN dept_no to Dept_id; |
|
|
|
CREATE
INDEX:- |
Syntax:- Create index
index_name ON table_name(col_1,col_2,…….);
Example:- Create index emp_idx ON employees(emp_id); |
|
|
|
|
COMPERISION CONDITION:- |
Between condition:-
Example:- Select * from employees where
salary between 5000 and 15000; |
|
|
|
|
|
NULL Condition:-
Example:- Select * from employees where dept_no IS NULL; |
|
|
|
|
|
Like Operator:-
Example:- Select * from employees where name like ‘A%’;
The above query will show the rows having name starting with letter ‘A’ |
|
|
|
|
|
In Condition:-
Example:- Select * from employees where salary
IN(5000,10000,15000); |
|
|
|
|
|
NOT IN Condition:-
Example:- Select * from employees where salary NOT
IN(5000,10000); |
|
|
|
|
CREATING USER:- |
Syntax:- Create user
user_name identified by password;
Example:- Create user sysadm identified by sysadm; |
|
|
Granting Privilges To A User:- |
|
|
Syntax:- Grant priv_name
to user_name;
Example:- Grant DBA to SYSADM; |
|
|
|
|
GROUP FUNCTIONS AND GROUP BY
CLAUSE:- |
Max(), Min(), Avg() and count() functions:-
Example:-
Select max(salary),
min(salary), avg(salary) from employees;
Example:- Select count(*) from
employees;
Example:- Select count(dept_no) from employees; |
|
|
Group by clause:-
Example:- Select dept_no, max(salary) from employees group by dept_no; |
|
|
|
|
|
Having Clause:-
Example:- Select dept_no,max(salary) from
employees group by dept_no
Having sum(salary)<50000; |
|
|
|
|
INSERTING AND UPDATING TABLE:- |
Insert Into Tables:-
Syntax:- Insert into table table_name(col_1,
col_2,…..)
Values(value1,value2,…….);
Example:- Insert into employees values(‘abc’,’sys_1’,1,10000,’
10-mar-2004’); |
|
|
|
|
|
Updating tables:- |
|
|
Syntax:- Update table
table_name col_1=value, col_2=value……;
Example:- Update table employees
Set emp_id=’sys_1’, dept_no=1 where name=’TOM’; |
|
|
|
|
SELECTING ROWS FROM MULTIPLE TABLES:- |
Example:- Select name,
emp_id,salary,d.dept_no, dept_name
From employees E, departments D
Where e.dept_no=d.dept_no;
The above query will show the employee name, salary, department no and
department name of all the employees. |
|
|
Aliases E for table employees and D for table
departments is used for avoiding error of column ambiguity because dept_no is
common in both tables. |
|
|
The condition e.dept_no=d.dept_no is used for
avoiding duplicate records |
|
|
|
|
DELETING DUPLICATE RECORDS FROM A TABLE:- |
Example1:- Suppose you want to delete the rows from
employees table having duplicate values for NAME. Then use the following query:-
Delete from employees e1 where
rowid!=(select max(rowed) from employees e2 where e1.name=e2.name); |
|
|
.And if you want to delete duplicate emp_id also
then:-. |
|
|
.Delete from employees e1 where rowid!=(select
max(rowed) from employees e2 where e1.name=e2.name AND e1.emp_id=e2.emp_id);. |
|
|
|
|
QUERING INFORMATION OF TABLES AND
INDEXES:- |
.Tables’s information:-
Example:- Select table_name from user_tables;
The above query will show the names of all the tables in the schema.
Select table_name from user_tables where table_name like ‘A%’;
The above query will show the tables having name starting with letter ‘A’.. |
|
|
|
|
|
.Tables’s structure information:-.
.Example Suppose you want to get
structure(column name, data types etc) of a table Trnmast, then use the
following command:-. |
|
|
.Select column_name, data_type, data_length,
data_precision from user_tab_columns where table_name=’TRNMAST’;. |
|
|
|
|
|
.Index’s Information:-.
.Example:- Select index_name from user_indexes where
table_name=’TRNMAST’;
The above query will show all the indexes on the table TRNMAST.. |
|
|
|
|
|
.If u want to know the information of columns on which
index is created for a particular table then :-. |
|
|
.Select index_name, column_name, column_position from
user_ind_columns where table_name=’TRNMAST’;. |
|
|
|
|
ADDING DATAFILES:- |
.Alter tablespace SYSTEM add datafile
‘d:\oracle\oradata\db_name\system02.dbf’ size 50M;. |
|
|
|
CREATING ROLLBACK SEGMENTS
AND VIEWING INFO:- |
Example:- Create rollback
segment rbs0 storage(maxextents unlimited);
Select segment_name from dba_segments
where
segment_type=’ROLLBACK’;
Extending Rollback Segment :-
Alter rollback segment rbs0 storage(maxextents
unlimited); |
|
|
|
|
INSERTING ROWS FROM ONE TABLE TO
ANOTHER: |
Example:- Create table
emp1 as Select * from employees;
You can also create a table by selecting specified columns from a table By the
following statement:-
Create table emp_nm as Select name from employees;. |
|
|
|
|
CHARECTER FUNCTION:- |
Lower(column/expression)
Converts alpha character values to Lowercase. |
|
|
|
|
|
UPPER(column/expression)
Converts alpha character values to Uppercase. |
|
|
|
|
|
INITCAP(column/expression)
Converts alpha character values to Uppercase for the first
letter of each
World, All other letters in Lowercase. |
|
|
|
|
|
SUBSTR(column/exp,m[,n])
Returns specified characters from Character value
starting at character
Position m, n characters long. If m is Negative, the count starts
from the
end of character value. |
|
|
|
|
|
LENGTH(column/exp)
Returns the number of characters in the Expression. |
|
|
|
|
|
INSTR(column/exp,’string’, [m],[n])
Returns the numeric position of a named string. Optionally you can
provide a position m to start searching and occurrence n of
the string. |
|
|
|
|
|
LPAD(column,/exp,n,’string’)
Pads the character value right justified to a Total width of
n character
positions. |
|
|
|
|
|
RPAD(column/exp,n,’string’)
Pads the character value left justified to a Total width of
n character
positions. |
|
|
|
|
|
TRIM(leading/trailing/both, character from source)
Enable you to
trim heading or trailing |
|
|
|
|
NUMBER FUNCTION:- |
ROUND(column/exp,n)
Round the column, expression or value to n decimal
places. If n is
omitted, no decimal places. If n is is negative numbers to the
left of
decimal point are rounded. |
|
|
|
|
|
TRUNC(column/exp,n)
Truncates the column, expression or value to n decimal
places. If n is
omitted then n Defaults to zero. |
|
|
|
|
|
MOD(m,n)
Returns the remainder of m divided n. |
|
|
|
|
|
CEIL(column/exp)
Round the number to the upper limit. |
|
|
|
|
DATE FUNCTION:- |
MONTHS_BETWEEN(date1,date2)
Finds the number between date1 and date2 Result can be negative
or
positive. |
|
|
|
|
|
ADD_MONTHS(date,n)
Adds n number of months to date. N must be
Integer negative or
positive. |
|
|
|
|
|
NEXT_DAY(date,’char’)
Finds the date of the next specified day of the week following
date. |
|
|
|
|
|
LAST_DAY(date)
Finds the date of the last day of the month that contains date. |
|
|
|
|
|
ROUND(date[,’fmt’])
Returns date rounded to the unit specified by The format
model fmt. Fmt
may be day, Month or year. |
|
|
|
|
|
TRUNC(date[,’fmt’])
Returns date with the time portion of the day Truncated
to the unit
specified by fmt. |
|
|
|
|
CONVERSION FUNCTION:- |
TO_CHAR(number/date,[fmt])
Convert a number or date value to character String with
format model
fmt. |
|
|
|
|
|
TO_CHAR(char,[fmt])
Converts a character string containing digits to a number |
|
|
|
|
|
TO_DATE(char,[fmt])
Converts a character string representing a date to a
date value
according to the fmt |
|
|
|
|
GERNAL FUNCTION:- |
NVL(expr1,expr2)
Converts a null value to an actual value. |
|
|
|
|
|
NVL2(expr1,expr2,expr3)
If expr1 is not null, it returns expr2. If expr1 is null, it
returns expr3. |
|
|
|
|
|
NULLIF(expr1,expr2)
Compares two expressions and returns null if they
are equal or the first
exp if they are not equal. |
|
|
|
|
|
COALESCE(exp1,exp2,..expn)
Returns the first non-null expression in the expression list |
|
|
|
|
|
DECODE(col/exp,search1,result1,search2,result2…)
The DECODE function decodes an exp in a way similar
to the if-then
-else logic. |
|
|
|