1.
create table project ( PRONO NUMBER(4), PRONAME VARCHAR2(10) NOT NULL, STARTDATE DATE NOT NULL, DESCRIPTION VARCHAR2(50) ); create table team ( tmno NUMBER(4), tmname VARCHAR2(10) NOT NULL, constraint TEAM_PK primary key (tmno) );
2.
desc project;
desc TEAM;
3.
insert into project (prono, proname, startdate, description) values(100, 'MATRIX', sysdate, NULL); insert into project(prono, proname, startdate, description) values(200, 'ABC', sysdate+1, 'Temporary'); select * from project;
4.
update project set description='Experimental project' where prono=100; select * from project;
5.
delete from project where prono=200; select * from project;
6.
alter table project add tmno NUMBER(4); desc project; select * from project;
7.
alter table project add constraint PRO_PK primary key (prono); desc project;
8.
alter table PROJECT add constraint PRO_TM_FK foreign key (tmno) references team(tmno);
9.
alter table PROJECT add ( PROCODE VARCHAR2(3) default 'XXX' NOT NULL); select * from project; select * from user_constraints where table_name='PROJECT';
10.
drop table PROJECT; drop table TEAM;
11.
select ename || ' is supervised by ' || NVL(to_char(mgr), 'NO SUPERVISOR') "Employees and supervisors" from emp;
12.
select ename, sal from emp where sal < 3000;
13.
select count(*)
from emp
where hiredate between to_date('01-JAN-1980','DD-MON-YYYY') and to_date('01-JAN-1982','DD-MON-YYYY') and deptno in (10, 20);
select empno, ename, job, sal
from emp
where sal between 2000 and 3000 and job in ('ANALYST', 'MANAGER');
14.
select distinct job from emp order by job asc; select ename, job, hiredate from emp order by job asc, hiredate desc;
15.
select ename, deptno from emp where ename like 'C%' or ename like 'M%' and deptno not in (20, 30);
16.
-- in sessions 1 and 2 select ename, job, sal, deptno from emp; -- in session 1 update emp set sal=sal+100 where job!='PRESIDENT' and deptno=10; -- in session 1 select ename, job, sal, deptno from emp; -- in session 2 select ename, job, sal, deptno from emp; --in session 1 commit; select ename, job, sal, deptno from emp; --in session 2 select ename, job, sal, deptno from emp; --undo the changes update emp set sal=sal-100 where job!='PRESIDENT' and deptno=10; commit;
17.
select d.deptno, lower(d.dname) as "Department name", e.ename from emp e, dept d where e.deptno=d.deptno order by d.deptno;
18.
select 'Number of employees being supervised in department '||deptno||' is '||count(*) from emp where mgr is not null group by deptno; select 'Department '||d.deptno||' named '||d.dname||' counts '||count(*)||' employees with salaries: miniumum '||min(e.sal)||' , maximum '||max(e.sal) from dept d, emp e where d.deptno=e.deptno group by d.deptno, d.dname;
19.
select d.dname, sum(e.sal) "Salaries in total" from emp e, dept d where e.deptno=d.deptno group by d.dname having count(*)>3; select d.dname, d.loc, count(*) "Clerks' number" from emp e, dept d where e.deptno=d.deptno and e.job='CLERK' group by d.dname, d.loc having count(*)>1;
20.
select d.dname, e.ename from dept d, emp e where d.deptno=e.deptno(+) order by d.dname;
21.
select to_char(sysdate, 'DD/MM/YYYY HH:MI:SS') "Date and time", to_char(sysdate, 'DAY') "Day of week"
from dual;
select trunc(sysdate, 'YEAR') "Year",
next_day(to_date('01-FEB-2005', 'DD-MON-YYYY'), 'MONDAY') "Monday" from dual;
select deptno, decode(deptno, 10, 'A', 20, 'B', 30, 'C', 'D') "Converted"
from dept;
22.
select e.ename "Employee", nvl(m.ename,'NOT ASSIGNED') "Manager" from emp e, emp m where e.mgr=m.empno(+);
23.
select ename, deptno, job from emp
where job = (select job from emp where ename='JAMES');
select ename, deptno, hiredate from emp
where (deptno, trunc(hiredate,'YEAR'))=(select deptno, trunc(hiredate,'YEAR')
from emp
where ename='KING');
select ename, job, deptno
from emp
where empno in (
select mgr
from emp
where mgr is not null
group by mgr
having count(*) > 1 );
select level, empno, ename, mgr
from emp
start with job='PRESIDENT'
connect by mgr = prior empno;
24.
select ename, deptno, sal, (select avg(sal) from emp where deptno = e.deptno) as avg_sal from emp e;
25.
select ename, e.deptno, sal, avg_sal
from emp e, (select deptno, avg(sal) avg_sal from emp group by deptno) s
where e.deptno=s.deptno;
select e.ename, e.sal, e.deptno
from emp e,
(select sal
from (select distinct sal from emp order by sal desc)
where rownum <=2) s
where e.sal = s.sal ;
26.
create view dept_v as
(
select d.deptno, d.dname, d.loc, min(e.sal) min_sal, avg(e.sal) avg_sal, max(e.sal) max_sal, decode(min(sal), NULL, 0, count(*)) emp_count
from emp e, dept d
where e.deptno (+) = d.deptno group by d.deptno, d.dname, d.loc
);
desc dept_v;
select * from dept_v;
select ename, d.dname, sal, d.avg_sal
from emp e, dept_v d
where e.deptno=d.deptno;
select view_name, text_length, text
from user_views
where view_name='DEPT_V';
drop view dept_v;
27.
create materialized view dept_mv
build immediate
refresh sysdate+1/24
enable query rewrite
as
(
select d.deptno, d.dname, d.loc, min(e.sal) min_sal, avg(e.sal) avg_sal, max(e.sal) max_sal, decode(min(sal), NULL, 0, count(*)) emp_count
from emp e, dept d
where e.deptno(+) = d.deptno group by d.deptno, d.dname, d.loc
);
desc dept_mv;
select * from dept_mv;
select ename, e.deptno, sal, d.avg_sal
from emp e, dept_mv d
where e.deptno=d.deptno;
select view_name, text_length, text
from user_views
where view_name='DEPT_MV';
select mview_name, query_len, query, rewrite_enabled, build_mode, refresh_mode, refresh_method, last_refresh_date
from user_mviews
where mview_name='DEPT_MV';
drop materialized view dept_mv;
28.
--assuming you work in pairs of users: a user A and a user B -- as user A create synonym EMP_A for emp; --from session of user B select * from A.emp; -- from session of user A grant select on emp_a to B; --from session of user B update a.emp set hiredate=sysdate where ename='JAMES'; select * from a.emp; --from session of user A grant update (sal) on emp to B; --from B user's session update a.emp set hiredate=sysdate where ename='JAMES'; update a.emp set sal=sal+1000 where ename='JAMES'; rollback; --from the session of user A revoke update on emp from B; revoke select on emp_a from B; --from session of user B select * from A.emp; --from the user A session select * from user_synonyms; drop synonym emp_a;
29.
create table project_r(
prono number(4),
proname varchar2(10) not null,
startdate date not null,
description varchar2(50),
phase varchar2(2) not null,
constraint PRO_PK primary key (prono),
constraint PHASE_CK check (phase in ('DS','DV','TS'))
)
partition by range(startdate)
(partition pro2000 values less than (to_date('01-JAN-2001','dd-mon-yyyy')),
partition pro2001 values less than (to_date('01-JAN-2002','dd-mon-yyyy')),
partition pro2002 values less than (to_date('01-JAN-2003','dd-mon-yyyy')),
partition pro2003 values less than (to_date('01-JAN-2004','dd-mon-yyyy')),
partition pro2004 values less than (MAXVALUE)
);
select object_name, subobject_name, object_type from user_objects
where object_name='PROJECT_R';
create index projectr_startdate_indx_loc on project_r (startdate) local tablespace training_indx;
select object_name, subobject_name, object_type from user_objects
where object_name='PROJECTR_STARTDATE_INDX_LOC';
drop index projectr_startdate_indx_loc;
create index projectr_startdate_indx_gl on project_r(startdate) tablespace training_indx;
select object_name, subobject_name, object_type from user_objects
where object_name='PROJECTR_STARTDATE_INDX_GL';
create bitmap index projectr_phase_indx_bitmap on project_r(phase) local tablespace training_indx;
select object_name, subobject_name, object_type from user_objects
where object_name='PROJECTR_PHASE_INDX_BITMAP';
select index_name, index_type from user_indexes where index_name like 'PROJECTR%';
create sequence projectr_seq start with 1 increment by 1 nomaxvalue nocycle;
insert into project_r(prono, proname, startdate, phase)
values(projectr_seq.nextval, 'Project 1', sysdate, 'DS');
drop table project_r;
drop sequence projectr_seq;
30.
select deptno, job, count(*) "Total emp" from emp group by rollup(deptno, job); select deptno, job, count(*) as "Total emp" from emp group by cube(deptno, job); select deptno, job, count(*) "Total emp" from emp group by deptno, job union select deptno, NULL, count(*) "Total emp" from emp group by deptno union select NULL, job, count(*) "Total emp" from emp group by job union select NULL, NULL, count(*) "Total emp" from emp;
drop table PROJECT; drop table TEAM; drop view DEPT_V; drop materialized view DEPT_V; drop synonym emp_<user_number>; drop table PROJECT_R; drop sequence PROJECTR_SEQ; revoke update on emp from B; revoke select on emp_a from B;