drop synonym emp_p_syn;
create synonym emp_s for emp_anon_v; create synonym dept_s for dept_anon_v; create synonym deptsum_s for dept_sum_v; desc emp_anon_v;
desc emp_s;
select * from deptsum_s;
insert into dept_s values(99,'Temp Dept',1800);
insert into emp_s values(sysdate,'AC_MGR',10000,0,99); update emp_s set salary=salary*1.1;
select * from emp_s; rollback;
select max(salaries/staff) from deptsum_s; drop view emp_anon_v;
select * from dept_sum_v;
alter view dept_sum_v compile; drop view dept_anon_v;
drop view dept_sum_v; select * from emp_s;
alter synonym emp_s compile; drop synonym emp_s;
drop synonym dept_s; drop synonym deptsum_s;
create table orders(order_number number(4) primary key,order_date date,customer_ no number(4));
create table order_lines(order_number number(4) references orders,line_number nu mber(4),
item_number number(4),quantity number(4,1)); create sequence order_seq start with 10; create sequence line_seq start with 10;
INSERT INTO orders VALUES(order_seq.nextval,sysdate,1000 );
insert into order_lines values(order_seq.currval,line_seq.nextval,111,1); insert into order_lines values(order_seq.currval,line_seq.nextval,222,2); commit;
select * from orders;
select * from order_lines;
insert into orders values(order_seq.nextval,sysdate,1002); rollback;
insert into orders values(order_seq.nextval,sysdate,1003); select * from orders;
drop sequence order_seq; drop sequence line_seq; drop table order_lines; drop table orders;
create sequence seq1 start with 10 nocache maxvalue 15 cycle; select seq1.nextval from dual;
select seq1.nextval from dual;
create table seqtest(c1 number,c2 varchar2(10));
alter table seqtest add constraint seqtest_pk primary key(c1); CREATE sequence seqtest_pk_s;
-- execute in session A
insert into seqtest values(seqtest_pk_s.nextval,'First'); commit;
--execute in session B
insert into seqtest values(seqtest_pk_s.nextval,'Second'); -- execute in session A
insert into seqtest values(seqtest_pk_s.nextval,'Third'); commit;
select * from seqtest; --execute in session B select * from seqtest; rollback;
select * from seqtest;
drop sequence seqtest_pk_s; drop sequence seq1;
drop table seqtest; select * from regions;
select rowid,region_id,region_name from regions;
select * from regions where rowid='AAAR5YAAFAAAACPAAC';
select count(*) from employees where last_name between 'A%' and 'Z%'; create table dept(deptno number,dname varchar2(20));
create table emp(empno number,surname varchar2(10),forename varchar2(10), dob date,deptno number);
create unique index dept_i1 on dept(deptno); create unique index emp_i1 on emp(empno); create index empi2 on emp(surname,forename); create bitmap index emp_i3 on emp(deptno);
alter table dept add constraint dept_pk primary key(deptno); alter table emp add constraint emp_pk primary key(empno);
alter table emp add constraint emp_fk foreign key(deptno) references dept; drop index empi2;
create index empi2 on emp(surname,forename,dob); drop index emp_i3;
drop index empi2;
drop index emp_i1;--generates error alter table emp drop constraint emp_pk; drop index emp_i1;
drop table emp; drop table dept;
create table emps as select * from employees; desc emps;
create unique index emp_empid_i on emps(employee_id);
insert into emps(employee_id,last_name,email,hire_date,job_id)
values(198,'Watson','[email protected]',sysdate,'IT_PROG');--generates error create index emps_name_i on emps(first_name,last_name);
create index emps_tel_i on emps(phone_number);
create bitmap index emps_mgr_i on emps(manager_id); create bitmap index emps_dept_i on emps(department_id);
alter table emps add constraint emps_empid_pk primary key(employee_id); alter table emps add constraint emps_email_uk unique(email);
alter table emps add constraint emps_phone_uk unique(phone_number); select index_name,index_type,uniqueness from user_indexes
where table_name='EMPS'; drop table emps;
select * from tab;
select object_type from user_object group by object_type; select object_type from all_objects group by object_type; create table "tab"(col varchar2(4));
create table "with space"("-hyphen" date); insert into "with space" values(sysdate); select "-hyphen" from "with space";
select distinct owner from all_objects;
select object_type,count(*) from user_objects group by object_type;
select table_name,cluster_name,iot_type from user_tables; describe regions;
select column_name,data_type,nullable from user_tab_columns where table_name='REGIONS';
create table typcst(d_col date,n_col number(4,2),c_col varchar2(20));
insert into typcst values(to_date('30-08-99'),to_number('20.209'),'Inserted corr ectly');
insert into typcst values('23-08-98',44.3,'Casted automatically'); select * from typcst;
desc employees; desc departments;
select column_name, data_type, nullable, data_length, data_precision, data_scale from user_tab_columns where table_name='EMPLOYEES';
create table emp_dept as select first_name||' '||last_name ename, department_name dNAME,round(sysdate-hire_date+1) services from employees natural join departments order by dname,ename;
select * from emp_dept where rownum < 4; alter table emp_dept drop unused columns;
alter table emp_dept rename column services to no_of_days; select * from emp_dept where rownum < 6;
alter table emp_dept read write;
insert into emps select employee_id,last_name,salary,department_id from employee s;
commit;
deptno number(2,0) constraint dept_deptno_pk primary key constraint dept_deptno_ck check (deptno between 10 and 90), dname varchar2(20) constraint dept_dname_nn not null);
create table emp_cpy(
empno number(4,0) constraint emp_c_empno_pk primary key, ename varchar2(20) constraint emp_c_ename_nn not null,
mgr number (4,0) constraint emp_c_mgr_fk references emp_cpy (empno), dob date,
hiredate date,
deptno number(2,0) constraint emp_c_deptno_fk references dept(deptno) on delete set null,
email varchar2(30) constraint emp_c_email_uk unique,
constraint emp_c_hiredate_chk check (hiredate >= dob + 365*16), constraint emp_c_email_chk
check ((instr(email,'@') > 0) and (instr(email,'.') > 0))); create table emp as
select employee_id empno, last_name ename, department_id deptno from employees;
create table dept as
select department_id deptno, department_name dname from departments; alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key (deptno); alter table emp add constraint
dept_fk foreign key (deptno) references dept on delete set null; insert into dept values(10,'New Department');//generates err insert into emp values(9999,'New emp',99);//generates err truncate table dept;//generates err
drop table emp; drop table dept;
create view hr.emp_fin as select hire_date,job_id,salary, commission_pct,department_id from employees;
select * from emp_fin;
select department_name,sum(salary) from departments natural join emp_fin GROUP by department_name;
create view dept_dal as select department_name,sum(salary) total_sal from departments left outer join employees using(department_id)
group by department_name order by department_name; select * from dept_dal order by total_sal;
create or replace view emp_dept as select /*+USE_HASH (EMPLOYEES DEPARTMENTS)*/ department_name,last_name from departments natural join employees;
select * from emp_dept;
create or replace view loc1800 as
select department_id,department_name,location_id from departments where location_id=1800 with check option;
select to_char(sysdate,'ddth Yyyysp') from dual; select to_char(sysdate,'ddth Yyyysp') from dual;
select to_char(sysdate,'fmmonth,yyyyspth') from dual; select to_char(sysdate,'fmmonth,yyyyspth') from dual; select first_name,last_name, to_char(h
select first_name,last_name, to_char(hire_date,'fmDay, " the " fmddth " of " ire_date,'fmDay, " the " fmddth " of " fmMfmM onth, Yyyysp "."') START_DATE
onth, Yyyysp "."') START_DATE from employees
from employees where
where upper(to_char(hire_date,upper(to_char(hire_date,'FMDAY'))='SATURDAY';'FMDAY'))='SATURDAY'; select to_date('25-dec-2010') from dual;
select to_date('25-dec-2010') from dual;
select to_date('25-DEC') from dual;--encounter an error select to_date('25-DEC') from dual;--encounter an error select to_date('25-DEC','DD-MON') from dual;
select to_date('25-DEC','DD-MON') from dual;
select to_date('25-dec-2010 18:03:45','dd-mon-yyyy HH24:MI:SS')from dual; select to_date('25-dec-2010 18:03:45','dd-mon-yyyy HH24:MI:SS')from dual; select to_date('25-dec-2010 18/03/45','dd-mon-yyyy hh24/MI/ss') from dual; select to_date('25-dec-2010 18/03/45','dd-mon-yyyy hh24/MI/ss') from dual; select to_date('25-dec-2010 18/03/45','dd-mon-yyyy hh24:MI:ss') from dual; select to_date('25-dec-2010 18/03/45','dd-mon-yyyy hh24:MI:ss') from dual; select to_date('25-dec-10','fxDD-MON-Y
select to_date('25-dec-10','fxDD-MON-YYYY') from YYY') from dual;--generates an errordual;--generates an error select to_date('25-dec-2010','fxDD-MON-YYY
select to_date('25-dec-2010','fxDD-MON-YYYY') from Y') from dual;dual; select
select first_name,last_name,hifirst_name,last_name,hire_datere_date from employees
from employees where
where hire_date>to_char('01/12hire_date>to_char('01/12/2000')/2000') order by hire_date;
order by hire_date;
select first_name from employees select first_name from employees where
where to_char(hire_date,'dd-moto_char(hire_date,'dd-mon')=to_char(sysdate,'dd-n')=to_char(sysdate,'dd-mon');mon'); select to_number('$1,000.55') FROM dual;--generates error select to_number('$1,000.55') FROM dual;--generates error select to_number('$1,000.55','$999,999.99'
select to_number('$1,000.55','$999,999.99') from ) from dual;dual; select
select first_name,phone_numberfirst_name,phone_number,to_number(substr(phone_,to_number(substr(phone_number,5),'999.9999')*100number,5),'999.9999')*10000 0 LOCAL_NUMBER 0 LOCAL_NUMBER from employees from employees where department_id=30; where department_id=30; select
select to_number(123.45,'999.9to_number(123.45,'999.9') ') from from dual;dual; select to_char(123.45,'999.9') FROM dual; select to_char(123.45,'999.9') FROM dual;
select length(to_char(to_date('28/10/09','dd/m
select length(to_char(to_date('28/10/09','dd/mm/rr'),'fmMonth')) from m/rr'),'fmMonth')) from dual;dual; select to_char(salary,'$99,999') salary from employees;
select to_char(salary,'$99,999') salary from employees; select to_char(salary,'$99G999') salary from employees; select to_char(salary,'$99G999') salary from employees; select
select '$'||substr(salary,1,mo'$'||substr(salary,1,mod(length(salary),3))||',d(length(salary),3))||','||substr(salary,mod(leng'||substr(salary,mod(lengtt h(salary),3)+1)
h(salary),3)+1) from employees; from employees;
select nvl(1234) from dual;--generates error select nvl(1234) from dual;--generates error select nvl(null,1234) from dual;
select nvl(null,1234) from dual;
select nvl(substr('abc',4),'No Substring exist!') from dual; select nvl(substr('abc',4),'No Substring exist!') from dual;
select Last_name, salary, commission_pct, (nvl(commission_pct,0)*salary+100) "Mo select Last_name, salary, commission_pct, (nvl(commission_pct,0)*salary+100) "Mo nthly Commission"
nthly Commission" from employees from employees
where last_name like 'E%'; where last_name like 'E%';
select Last_name,salary, commission_pct, commission_pct*100+1000 Montly_Salary select Last_name,salary, commission_pct, commission_pct*100+1000 Montly_Salary from employees
from employees
where last_name like 'E%'; where last_name like 'E%';
select nvl2(1234,1,'Not Null') from dual;--generates error select nvl2(1234,1,'Not Null') from dual;--generates error select nvl2(null,124,4231) from dual;
select nvl2(124,135,4) from dual;
select nvl2(substr('abc',2),'Not bc','No Substring.') from dual; select nullif(123,123) from dual;
select nullif(124,123) from dual; select nullif(234,233+1) from dual;
select nullif('24-JUL-2009','24-JUL-09') from dual;
-- the two strings above are not same be carefull that those are consodered as s tring not date
select nullif(to_date('24-JUL-2009'),to_date('24-JUL-09')) from dual; select last_name,salary,commission_pct,
nvl2(commission_pct,'Commission Earner','Not a Commission Earner') Employee_type from employees
where last_name like 'G%';
select first_name,last_name,email,
nvl2(nullif(substr(first_name,1,1)||upper(last_name), email),'Email does not mat ch Pattern','Match Found!') pattern
from employees
where length(first_name)=4;
SELECT first_name,last_name, Department_id,
nvl2(nullif(length(first_name),length(last_name)),'Different Length','Same Lengt h') Name_lengths
from employees
where department_id=100;
select coalesce(null,null,null) from dual;
select coalesce(null,null,null,'a string') from dual;
select coalesce(substr('abc',4),'Not bc','No substring') from dual;
select coalesce(state_province,postal_code,city),postal_code,state_province,city from locations
where country_id in ('UK','IT','JP');
SELECT decode(1234,123,'123 is a match.') from dual;
select decode(1234,123,'123 is a match.','123 is not a Match.') from dual; select decode('search','comp1','true1','comp2','true2','search','true3',
substr('2search',2,6),'true4','false') from dual;
select distinct country_id,decode(country_id,'DR','Southern Hemisphere','AU','So uthern Hemisphere'
,'Northern Hemisphere') Hemisphere from countries order by hemisphere;
select case substr('1234',1,3)
when '134' then '134 is a Match.' when '124' then '124 is a Match.'
when concat('1','23') then concat('1','23') || ' is a Match.' else 'No match found!'
end
from dual;
select last_name,hire_date,trunc(months_between(sysdate,hire_date)/12) Years, trunc(months_between(sysdate,hire_date)/60) "Years Divide by 5",
case trunc(months_between(sysdate,hire_date)/60) when 0 then 'Intern'
when 3 then 'Senior'
when 2 then 'Intermediate' else 'Furniture'
end Loyalty from employees
where department_id in (60,10,30);
select last_name,hire_date,trunc(months_between(sysdate,hire_date)/12) years, trunc(months_between(sysdate,hire_date)/60) "Year Divide by 5",
case
when trunc(months_between(sysdate,hire_date)/60) <1 then 'Intern' when trunc(months_between(sysdate,hire_date)/60) <2 THEN 'Junior'
when trunc(months_between(sysdate,hire_date)/60) <3 THEN 'Intermediate' when trunc(months_between(sysdate,hire_date)/60) <4 THEN 'Senior'
else 'Furniture' END Loyalty FROM employees where department_id in (60,10,30); select state_province, decode(state_province,'Washington','Headquarters','Texas','Oil Wells','Californi a', city,'New Jersey', street_address)
Location_info from locations
where country_id='US' order by location_info;
select to_number(123.45,'999999.9') from dual;//It will generates an err select to_number(123.45,'999999.99') from dual;
select to_char(123.45,'999999.9') from dual;
select to_char(sysdate,'fmMONTH,YEAR') FROM dual; select to_char(sysdate,'DDth MONTH') from dual;
select to_char(to_date(to_char(sysdate,'DD'),'DD'),'YEAR') from dual; select nvl2(nullif('CODA','SID'),'SPANIEL','TERRIER') from dual;
select nvl(substr('AM I NULL',10),'YES I AM') FROM dual;
select decode(to_char(sysdate,'MM'),'02','TAX DUE','PARTY') FROM dual; -- RUN THE FOLLOWING QUERY IN OE USER
select cust_first_name, cust_last_name, cust_email, date_of_birth,
decode(to_char(date_of_birth,'dd') - to_char(sysdate,'dd'),-2,'Day Before Yester day',-1,'Yesterday',
0,'Today',1,'Tomorrow',2,'Day after Tomorrow','Later this week') Birthday from customers
where to_char(date_of_birth,'MM')=to_char(sysdate,'MM')
and to_number(to_char(date_of_birth,'dd')) between to_char(sysdate,'dd')-2 and t o_char(sysdate,'dd')+7
order by to_char(date_of_birth,'dd,mm'); -- Above query in different way
select cust_first_name, cust_last_name, cust_email, date_of_birth, case to_char(date_of_birth,'dd')-to_char(sysdate,'dd')
when -2 then 'Day Before Yesterday' when -1 THEN 'Yesterday'
when 0 then 'Today' when 1 then 'Tomorrow'
when 2 then 'Day after Tomorrow' else 'Later in this Week'
end Birthday from customers
where to_char(date_of_birth,'MON')=TO_CHAR(sysdate,'MON') AND
to_char(date_of_birth,'dd') - to_char(sysdate,'dd') between -2 and 7 order by to_char(date_of_birth,'DDMM');
---select count(*), department_id
from employees
group by department_id order by department_id;
select count(distinct department_id) from employees; select count(all department_id) from employees;
select count(*) from employees;
select count(commission_pct) from employees;
select count(distinct commission_pct) from employees; select count(hire_date),count(manager_id) from employees;
select count(*),count(distinct nvl(department_id,0)),count(distinct nvl(job_id,0 )) from employees;
select avg(department_id) from employees;
select avg(distinct department_id) from employees; select avg(all department_id) from employees;
select avg(commission_pct) from employees;
select avg(nvl(commission_pct,0)) from employees; select avg(nvl(commission_pct,1)) from employees;
select avg(salary),avg(distinct salary) from employees; select avg(2) from employees;
select last_name,job_id,(Sysdate-hire_date)/365.25 "Years Worked" from employees where job_id='IT_PROG';
select avg((sysdate-hire_date)/365.25) FROM employees where job_id='IT_PROG';
select sum(distinct department_id) from employees; select sum(department_id) from employees;
select sum(commission_pct) from employees;
select sum(nvl(commission_pct,0)) from employees; select sum(nvl(commission_pct,1)) from employees; SELECT sum(3) from employees;
select sum(salary),sum(DISTINCT salary) FROM employees;
select sum(sysdate-hire_date)/365.25 "Total Year worked by All" from employees; select sum(hire_date) from employees;-- Generates error
select max(distinct department_id),min(department_id) from employees; select max(hire_date),min(hire_date) from employees;
select max(end_date),min(end_date) from job_history;
select max(commission_pct),min(commission_pct) FROM employees; select max(job_id),min(job_id) from employees;
select min(salary),max(salary) FROM employees where job_id='SA_REP'; select round(avg(length(country_name))) from countries;
