Sql Commands for Oracle

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 date2Result 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.