SQL Queries

Full text

(1)

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;

(2)

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;

(3)

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','jw@bplc.co.za',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;

(4)

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;

(5)

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;

(6)

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;

(7)

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'

(8)

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'

(9)

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;

Figure

Updating...

References

Updating...

Related subjects :