Sql Fundamental-II For OCA

1

SQL Fundamental II

==================================================================================== //* Lession 1 Controling User *// ==================================================================================== create user amit identified by amit; select username,account_status from dba_users; connect amit/amit(connect permission to amit is not available) connect satish/satish grant connect,resource to amit; create role manager; grant connect,resource,create table,create view to manager; grant manager to amit; alter user amit identified by kumar; grant select on panchkurwa to amit; grant update on panchkurwa to amit; grant delete on panchkurwa to amit; grant select,insert on panchkurwa to amit with grant option; select grantee,granted_role from dba_role_privs where grantee='AMIT'; select grantee,granted_role from user_tab_privs_recd where grantee='AMIT'; desc USER_TAB_PRIVS_RECD; select owner,table_name,privilege from user_tab_privs_recd where owner='AMIT'; select * from user_tab_privs_recd; revoke select on panchkurwa from amit; revoke manager from amit; select grantee,granted_role from dba_role_privs where grantee='AMIT'; ==================================================================================== //* Lession 2 Managing Schema Object *// ==================================================================================== create table ruby (sno number(2) constraint ruby_sno_pk primary key ,color varchar2(25) constraint ruby_col_uk unique); insert into ruby values(&1,'&redish'); select * from ruby; //* alter table add,modify,drop table *// alter table ruby add (price number(4)); desc ruby; update ruby set price=4000 where sno=1; commit; alter table ruby modify (sno number(3),color varchar2(20),price number(5)); desc ruby; alter table ruby drop (price); desc ruby; alter table ruby add (price number(5)); alter table ruby modify (price number(4)); desc ruby; alter table ruby drop column price; desc ruby; clear screen; alter table ruby add (price number(4)); SQL Fundamental II

2 alter table ruby set unused (price); desc ruby; alter table ruby drop unused column; desc ruby; desc user_cons_columns; select table_name,column_name,constraint_name from user_cons_columns where table_name='RUBY'; alter table ruby add (price number(4)); desc ruby; alter table ruby modify price unique; alter table ruby add (weight number(3,2)); desc ruby; select * from ruby; alter table ruby modify weight check (weight>0); delete from ruby where sno=1; alter table ruby drop constraint sys_C007061; alter table ruby drop constraint sys_C007062; alter table ruby drop primary key cascade; alter table ruby disable constraint ruby_col_uk; alter table ruby enable constraint ruby_col_uk; alter table ruby drop column price cascade constraint; drop table ruby purge; ==================================================================================== //* Lession 3 manupulating Large Data Set *// ==================================================================================== clear screen; create table sales_reps(id number(2),name varchar2(20),salary number(5),commission_pct number(3)); desc sales_reps; insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct from hr.employees where job_id like '%REP%'; desc hr.employees; alter table sales_reps modify (id number(6),name varchar2(25),salary number(8,2),commission_pct number(2,2)); desc sales_reps; insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct from hr.employees where job_id like '%REP%'; select * from sales_reps; delete from sales_reps; insert into (select employee_id,last_name,email,hire_date,job_id,salary,department_id from emp13 where department_id=50) values (99999,'Tylor','Dylor',to_date('07-june-99','dd-mmrr'),'ST_CLERK',5000,50); create table emp13 as select * from hr.employees; delete from emp13; select * from emp13; delete from emp13; insert into emp13 select * from hr.employees; clear screen; update emp13 set job_id =(select job_id from hr.employees where employee_id=205),salary =(select salary from hr.employees where employee_id = 168) where employee_id =114; select * from hr.employees where employee_id=114; SQL Fundamental II

3

==================================================================================== //* Lession 4 Generating Reports by Grouping Related Data *// =================================================================================== SELECT AVG(salary), STDDEV(salary),COUNT(commission_pct),MAX(hire_date) FROM hr.employees WHERE job_id LIKE 'SA%'; SELECT department_id, job_id, SUM(salary),COUNT(employee_id)FROM hr.employees GROUP BY department_id, job_id ; SELECT department_id, job_id, SUM(salary)FROM hr.employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id); SELECT department_id, job_id, SUM(salary) FROM hr.employees WHERE department_id < 60 GROUP

BY CUBE (department_id, job_id) ; SELECT department_id DEPTID, job_id JOB,SUM(salary),GROUPING(department_id) GRP_DEPT,GROUPING(job_id) GRP_JOB FROM hr.employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id); SELECT department_id, job_id, manager_id,avg(salary) FROM hr.employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id)); SELECT department_id, job_id, manager_id,SUM(salary)FROM hr.employees GROUP BY ROLLUP( department_id,(job_id, manager_id)); SELECT department_id, job_id, manager_id,SUM(salary)FROM hr.employees GROUP BY department_id,ROLLUP(job_id),CUBE(manager_id); ==================================================================================== //*This practice covers using the following:• ROLLUP operators • CUBE operators • GROUPING ==================================================================================== functions • GROUPING SETS *// ==================================================================================== //* Lession 5 Managing Data in Different Time Zones * // ==================================================================================== //* • TZ_OFFSET • FROM_TZ • TO_TIMESTAMP • TO_TIMESTAMP_TZ • TO_YMINTERVAL • ==================================================================================== TO_DSINTERVAL • CURRENT_DATE • CURRENT_TIMESTAMP • LOCALTIMESTAMP • DBTIMEZONE • ==================================================================================== SESSIONTIMEZONE • EXTRACT *// ==================================================================================== ALTER SESSION SET TIME_ZONE=LOCAL; select sessiontimezone ,current_timestamp from dual; alter session set time_zone ='-5:0'; select sessiontimezone ,current_date from dual; alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; sessiontimezone,current_date from dual; select sessiontimezone,current_timestamp,localtimestamp from dual; select dbtimezone from dual; select sessiontimezone from dual; create table emp10 (od_id number(2) constraint emp10_od_id_pk primary key ,order_date timestamp with time zone); insert into emp10 values(4,current_date); select * from emp10; alter session set time_zone=local; create table emp11 (od_id number(2) constraint emp11_od_id_pk primary key ,delivry_time timestamp with local time zone); SQL Fundamental II

4 insert into emp11 values (2,current_timestamp); select * from emp11; create table warranty (prod_id number(2),warranty_time interval year(3) to month); insert into warranty values(11,interval '5' month); insert into warranty values(12,interval '200' month); insert into warranty values(13,interval '500' year(3)); select * from warranty; create table emp12 (exp_id number(2),test_time interval day (2) to second); desc emp12; insert into emp12 values(10,'90 00:00:00'); insert into emp12 values(11,interval '6 03:30:16' day to second); select * from emp12; select extract (year from sysdate) from dual; select first_name,last_name,hire_date,extract(month from hire_date) from hr.employees where manager_id=100; select tz_offset ('us/Eastern') from dual; SELECT FROM_TZ(TIMESTAMP'2000-03-28 08:00:00','3:00') FROM DUAL; SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', 'Australia/North') FROM DUAL; SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM hr.employees WHERE department_id = 20; SELECT last_name,TO_CHAR(hire_date, 'mm-dd-yy:hh:mi:ss') hire_date, TO_CHAR(hire_date + TO_DSINTERVAL('100 10:00:00'), 'mm-dd-yy:hh:mi:ss') hiredate2 FROM hr.employees; ==================================================================================== • TZ_OFFSET • FROM_TZ • TO_TIMESTAMP • TO_TIMESTAMP_TZ • TO_YMINTERVAL • CURRENT_DATE • CURRENT_TIMESTAMP • LOCALTIMESTAMP • DBTIMEZONE • SESSIONTIMEZONE • EXTRACT =================================================================================== //* Lession 6 Retrieving Data Using Subqueries *// =================================================================================== SELECT employee_id, manager_id, department_id FROM hr.employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM hr.employees WHERE first_name = 'John') AND first_name <> 'John'; SELECT employee_id, manager_id, department_id FROM hr.employees WHERE manager_id IN (SELECT manager_id FROM hr.employees WHERE first_name = 'John') AND department_id IN (SELECT department_id FROM hr.employees WHERE first_name = 'John') AND first_name <> 'John'; ==================================================================================== //* Lession 7 Heirachical Retrieval *// ==================================================================================== SELECT employee_id, last_name, job_id, manager_id FROM hr.employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id ; SELECT last_name||' reports to '|| PRIOR last_name "Walk Top Down" FROM hr.employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ; ==================================================================================== //* Lession 8 Regular Expression Support *// ==================================================================================== SELECT first_name, last_name FROM hr.employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$'); SELECT first_name, last_name FROM hr.employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

SQL Fundamental II