Oracle

 to view all the table

Select * from all_tables;

Structure Query language

select * from schema.table_name;

Oracle is RDMS

number of record in table

select count(*) from schema.table_name

where clause

select * from table_name where fieldName='field value'

distinct

select count(distinct salary) from table.name

select * from tablename order by fieldname1 ,fieldname 2 asc/desc;

-- null will be shown in last by default null value will be shown in first

select * from table order by field desc null last;

in

select distinct field names from tablesname 

where fieldname in (a,b,c)

order by 1,2,3;

and or

in and all condition must be true i.e both condition must be true

in or any one of the condition is satisfied i.e. on or all condition are applied

between 

select from table name where salary between 20000 and 50000

20000 and 50000 both will be included 

Sub query 

Is query inside query

Select * from hr.employee where employ_id in

(Select employee from department where manager_id is not null)

--exist return true or false

-- in looks inside certain tuple

Exist doesn't 

Correlated

Sub query doenot run

Select * from hr.employee a where 

Exist(

Select * from hr.department b where

a.departmen_id=b.department_id

)

Non correlated

Sub query runs

select * from hr.employee a where

department_id in 

(

select department_id from hr.department

)

in not correlated inner query run first

in correlated outer query is run first

 

---- alias

select

emp.firstname as firstName

emp.lastname lastname

from hr.employee emp;

--group by

must have aggregate functon

select 

emp.first_name as firstname

emp.last_name lastname,

sum(emp.salary) as total salary 

from hr.emplouee emp

group by first_name , last_name, email

select distinct 

emp.first_name, emp.last_name, email, emp.department_id

from hr.employee emp

left join

hr.department b

on 

emp.department_id= b.department_id

--wild card

select * from hr.employee

where upper(firstname) like "N%";

upper, lower, initcap are function that changes into upper case, lowercase and first letter capital

--Null handling function

select distinct 

manager_id,

nvl(manager_id,0)

from hr.employees

where

manager_id is null

coalesce( )

nvl2( )

select coalesce('ab'.'jkl'.'123','na') from dual;

select nullif('s','s') from dual --output null

select nullif('abc','steven') from dual --output abc

--case when

select distinct

first_name,

last_name,

salary,

case

    when salary  <=- 5000 then  'LOW'

    when salary  >- 15000 then  'HIGH'

else 'medium'

end as sal_cat

from hr.employee

select distnct

first_name,

last_name,

department_id,

case

when department_id='90' then 'dep90'

when department_id='10 then 'dep10'

end as dep)case

from hr.empoyees;

decode 

decode(department_id,90,'dep90,10,'dep10')

-- sub string

select 

first_name,

substr(first_name,1,3)

from hr.employee;

instr(firstname,j)

select

'sourceflename_20220101' from dual;

substr('sourceflename_20220101',1,instr('sourceflename_20220101','_')-1)

--replace

select 

replace(first_name,"S")

remove S from first name

--trim

-- date

select distinct

hire_date,

tochar(hire_date, 'yyyymmdd'),

to_date('20010112,'yyyymmdd'),

last_day(hire_date)

from hr.employees;

last_day()

next_day()

create table  table_name

(

col1 datatype,

col2 datatype,

...

);

--delete table

drop table table_name

drop delete entire table , truncate empty the table

condition can be applied in drop, no condition can be applied in truncate

-- create table with primary key

create table table_name

(

col1 datatype,

col2 datatype,

...

constraints coln primary key (col1,col2)

);

Foreign key

foreign key shows the relationship between 2 table

create table table_namep

(

std_id number not null,

section char(1) not null,

full name varchar2(100)

constraint pk_child primary key (std_id,section),

foreign key(std_id, section)

refrences table_namep

--insert

insert into table_name value (data);

-- union and union all

union doesn't allow duplicate value

union all allow duplicate values as well

In excel

="select ' "&a2&" ',' "&b2" ',' "c2" ' from dual union"

precaution

its better to use select statement before using delete statement

--change table name

alter table table_name

reaname to table_altname;

-- change column name

alter table table_name

rename column coln to colm;

--add column to table

alter table tablename 

add gender char(1)