tag:blogger.com,1999:blog-43324449625239741222024-03-04T20:15:53.243-08:00ORACLE DATABASE ADMINISTRATIONElvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-4332444962523974122.post-27965718635895572832011-04-02T01:03:00.000-07:002011-04-08T03:18:54.918-07:00DATABASE OBJECTS1. creating a sequence<br /> create sequence cust_no_seq<br /> maxvalue 110<br /> start with 100<br /> increment by 1<br /> ;<br /><br />2. creating a table<br /> create table customer<br /> (ac_no number(5),<br /> ac_name varchar2(30),<br /> ac_open_date date,<br /> contact_no number(10),<br /> area varchar2(20)<br /> ,constraint ac_no_pk primary key(ac_no)<br /> );<br /><br />3.creating another table<br /> create table transaction<br /> (<br /> transact_id number(5),<br /> transact_date date,<br /> ac_no number(5) constraint ac_no_fk references customer (ac_no),<br /> transact_type varchar2(1) default 'C',<br /> amount number(7),<br /> constraint transact_id_pk primary key(transact_id)<br /> );<br /><br />4.using the sequence created<br /> insert into customer values(cust_no_seq.currval,'Kwasi Ofori',sysdate-2,0233333333,'Accra');<br /><br />5.what to do to start using the sequence<br />select cust_no_seq.nextval from dual;<br /><br />6.repeating query 4 above <br />insert into customer values(cust_no_seq.currval,'Kwasi Ofori',sysdate-2,0233333333,'Accra');<br /><br />7.select everything from the table<br /> select * from customer;<br /><br />8.format the columns well <br /> col ac_name format a12<br /><br />9.run the following scripts<br /> <br /><br />insert into customer values(cust_no_seq.nextval,'Atta Mills',sysdate-289,0236633333,'Tema'); <br /><br />insert into customer values(cust_no_seq.nextval,'K Kumi',sysdate-289,0200633333,'Tema');<br /><br />insert into customer values(cust_no_seq.nextval,'Alhassan Musa',sysdate-89,0270633333,'Takoradi');<br /><br />insert into customer values(cust_no_seq.nextval,'Love Listowell',sysdate-89,02698765432,'Accra');<br /><br />insert into customer values(cust_no_seq.nextval,'Kafui Lands',sysdate-100,02698765444,'Nkawkaw');<br /><br />insert into customer values(cust_no_seq.nextval,'Akoto Osei',sysdate-88,02444765444,'Kumasi');<br /><br />insert into customer values(cust_no_seq.nextval,'Moses Anim',sysdate-8,02444700444,'Kumasi');<br /><br />insert into customer values(cust_no_seq.nextval,'James Okai',sysdate-776,02444707767,'Kumasi');<br /><br />insert into customer values(cust_no_seq.nextval,'Alhaji Ali',sysdate-776,02777364567,'Takoradi');<br /><br />insert into customer values(cust_no_seq.nextval,'Mumuni Anim',sysdate-776,02000364567,'Accra');<br /><br />insert into customer values(cust_no_seq.nextval,'Afua Asiedua',sysdate-776,02888364567,'Nkawkaw');<br /><br />10.commit and check the information in the customer table<br /><br />11.alter the sequence cust_no_seq<br />alter sequence cust_no_seq maxvalue 500;<br /><br />12.continue with the insert and commit afterwards<br />insert into customer values(cust_no_seq.nextval,'Afua Asiedua',sysdate-776,02888364567,'Nkawkaw');<br /><br />13.check the content of the customer table<br />select * from customer;<br /><br />14.increase the pagesize to 50 <br /><br />15.check the structure of the customer table and insert into only the ac_no column<br /> <br />desc customer<br />insert into customer(ac_no) values(cust_no_seq.nextval);<br /><br />16.check the content of the table<br />select * from customer;<br /><br />17.delete the last row you inserted <br />delete from customer where ac_no=112;<br /><br />18.let us enforce not null constraints on the rest of the columns<br />alter table customer modify (ac_name not null);<br /><br />alter table customer modify (ac_open_date not null);<br /><br />alter table customer modify (contact_no not null);<br /><br />alter table customer modify (area not null);<br /><br />18.let us check whether the newly created constraints is working<br />insert into customer(ac_no) values(cust_no_seq.nextval);<br /><br />19.what is your observation and desc the table<br /><br />20.create another sequence<br /><br />create sequence transact_id_seq<br /> maxvalue 110<br /> start with 1<br /> increment by 1;<br /><br />21.what should we do before we can use the sequence<br /><br />22.let us start inserting into the table transaction<br />insert into transaction values(transact_id_seq.currval,sysdate,101,'D',300);<br /><br />insert into transaction values(transact_id_seq.nextval,sysdate-1,102,'C',1300);<br /><br />insert into transaction values(transact_id_seq.nextval,sysdate-5,106,'X',700);<br /><br />23.check the content of the table transaction, format the columns where necessary<br /> select * from transaction;<br /><br />24. adding a new column to the table transaction<br /> alter table transaction add (del_stat varchar2(1));<br /><br />25. format the columns well<br /><br />26.how to rename column name<br /> alter table transaction rename column transact_type to type;<br /><br />27.ASSIGNMENT<br />I want you to change the data type of contact_no to varchar2(10).<br />send your result to elvisboateng@gmail.com<br /><br />28.let us increase the length of the column contact_no<br />alter table customer modify (contact_no number(11));<br /><br />29.how to drop a column<br />alter table customer add (land varchar2(2));<br /><br />alter table customer drop column land;<br /><br />30.how to set a column to unused<br />alter table customer add (land varchar2(10));<br /><br />alter table customer set unused column land;<br /><br /> <br />I WANT US TO CHANGE THE COLUMN AREA ON THE CUSTOMER TABLE TO AREA_CODE TO REFERENCE ANOTHER PARENT TABLE AREA<br /><br />31. create the table area<br /> create table area<br /> (<br /> area_code varchar2(10),<br /> name varchar2(30)<br /> );<br /><br />32.insert into the table area<br /><br />insert into area values('001','Accra');<br /><br />insert into area values('002','Tema');<br /><br />insert into area values('003','Takoradi');<br /><br />insert into area values('004','Kumasi');<br /><br />insert into area values('005','Nkawkaw');<br /><br />commit;<br /><br />select * from area;<br /><br />33.change the values in the area column<br /><br />update customer set area='001' where area='Accra';<br /><br />update customer set area='002' where area='Tema';<br /><br />update customer set area='003' where area='Takoradi';<br /><br />update customer set area='004' where area='Kumasi';<br /><br />update customer set area='005' where area='Nkawkaw';<br /><br />select * from customer;<br /><br />34.how to add contraint to a column already created<br /><br />alter table area modify area_code primary key;<br /><br />35 i want to know the name of the constraint<br />SQL> insert into area values('001','JJ');<br /><br />36.how to drop a constraint<br />SQL> alter table area drop constraint SYS_C005468;<br /><br />37.another way to add a contraint to a column<br /> alter table area add constraint area_code_pk primary key (area_code);<br /><br />38.let us verify the name again<br />insert into area values('001','JJ');<br /><br />39.let us change the area column to area_code<br />alter table customer rename column area to area_code;<br /><br />40.let us add another constraint<br /><br />alter table customer add constraint area_code_fk foreign key(area_code) references area(area_code);<br /><br />41.let us check whether it is working<br /><br />update area set area_code='111' where area_code='001';<br /><br />42.let us test ON DELETE CASCADE<br />alter table customer drop constraint area_code_fk;<br /><br />alter table customer add constraint area_code_fk foreign key(area_code) references area on delete cascade;<br /><br /> delete from area where area_code='005';<br /><br /> select * from area;<br /> select * from customer;<br /><br />43.looking at deferable initially deferred<br /><br />alter table customer drop constraints area_code_fk;<br /><br />alter table customer add constraint area_code_fk foreign key(area_code) references area(area_code) deferrable initially deferred;<br /><br />update area set area_code='111' where area_code='001';<br /><br />commit;<br /><br />update area set area_code='111' where area_code='001';<br /><br />update customer set area_code='111' where area_code='001';<br /><br />commit;<br /><br />Commit complete.<br /><br />44.drop the primary key cascade<br /><br />alter table area drop constraint area_code_pk;<br /><br />alter table area drop constraint area_code_pk cascade;<br /><br />45.let us add the primary key again<br />alter table area add constraint area_code_pk primary key(area_code);<br /><br />46.how to disable a constraint<br /><br />insert into area values ('001','Nkawkaw');<br /><br />insert into area values ('001','Post');<br /><br />alter table area disable constraint area_code_pk;<br /><br />insert into area values ('001','Post');<br /><br />alter table area enable constraint area_code_pk;<br /><br />update area set area_code='005' where name='Nkawkaw';<br /><br />commit;<br /><br />47.creating a synonym<br />create synonym cust for customer;<br /><br />select * from cust;<br /><br />48.creating a view<br />create view empvu10<br />as select employee_id,last_name<br />from employees where department_id=80;<br /><br />create or replace view empvu10<br />as select employee_id,last_name,salary<br />from employees where department_id=80;<br /><br />create or replace view empvu10(id,name,sal)<br />as select employee_id,last_name,salary<br />from employees where department_id=80;<br /><br />49.creating complex view<br /><br />50.Manipulating views<br />select * from empvu10;<br /><br />select last_name from employees where employee_id=145;<br /><br />update empvu10 set name='Elvis' where id=145;<br /><br />select * from empvu10;<br /><br />select last_name from employees where employee_id=145;<br /><br />51.creating read-only views<br />create or replace view empvu10(id,name,sal)<br />as select employee_id,last_name,salary<br />from employees where department_id=80<br />with read only;<br /><br />update empvu10 set name='Elvis' where id=145;<br /><br />delete from empvu10;<br /><br />52.WITH CHECK OPTION CONSTRAINTS<br />create or replace view kk10<br />as select last_name name,employee_id id,department_id d_id from employees<br />where department_id=20<br />with check option constraint kk10_ck;<br /><br />update kk10 set name='Boat' where name='Fay';<br /><br />update kk10 set id=1 where name='Boat';<br /><br />alter table employees disable constraint EMP_EMP_ID_PK;<br /><br />alter table employees disable constraint EMP_EMP_ID_PK cascade;<br /><br />update kk10 set id=1 where name='Boat';<br /><br />update kk10 set d_id=1 where name='Boat';<br /><br />53. CREATING INDEX<br />Indexes can be created explicitly or automatically<br />Indexes are logically and physically independent of the table that they index<br />when you drop a table, corresponding indexes are also dropped<br />The name of the index is the name that is given to the constraint<br />You cannot modify indexes.<br />To change an index, you must dro it and then re-create it.<br />if you drop a table, indexes and constraints are automatically dropped but views and sequences remain<br /><br />how to remove NOT NULL<br /><br />create table index_test as select * from employees;<br /><br />create index emp_id_idx on index_test(employee_id);<br /><br />54.alter table customer add (close_status varchar2(1));<br /><br />55.update customer set close_status='C' where ac_no >110;<br /><br />56.update customer set close_status='O' where ac_no <111;<br /><br />57.let us put restriction on the column close_status<br /><br />alter table customer add constraint close_status_check check(close_status IN ('C','O'));<br /><br />58.insert into customer values(cust_no_seq.nextval,'Okudzeto Ablakwa',sysdate-789,0288124567,'003','B');<br /><br />59.create table cust_name_hist<br />(<br />ac_no number(5),<br />ac_name varchar2(30)<br />);<br /><br />add constraints<br /><br />60.create table cust_contact_hist<br />(<br />ac_name varchar2(30),<br />ac_open_date date,<br />contact_no number(10)<br />);<br /><br />add constraints<br /><br />-UNCONDITIONAL INSERT<br />61. <br />insert all<br />into cust_name_hist values (ac_no,ac_name)<br />into cust_contact_hist values (ac_name,ac_open_date,contact_no)<br />select ac_no,ac_name,ac_open_date,contact_no<br />from customer<br />where close_status='C';<br /><br />62.check the contents of cust_name_hist and cust_contact_hist<br /><br />63.truncate the tables cust_namr_hist anf cust_contact_hist<br /><br />64.<br />insert all<br />into cust_name_hist values (ac_no,ac_name)<br />into cust_contact_hist values (ac_name,ac_open_date,contact_no)<br />select ac_no,ac_name,ac_open_date,contact_no<br />from customer<br /><br />65.check the contents of cust_name_hist and cust_contact_hist<br /><br />66.truncate the tables cust_namr_hist anf cust_contact_hist<br /><br />67.<br />insert all<br />into cust_name_hist values (ac_no,ac_name)<br />into cust_contact_hist values (ac_name,ac_open_date,contact_no)<br />select *<br />from customer<br /><br />68.create table cust_kumasi as select * from cust_name_hist;<br /><br />69.alter table cust_kumasi add (area_code varchar2(20));<br /><br />70.create table cust_accra as select * from cust_kumasi;<br /><br />--CONDITIONAL INSERT ALL<br />71.<br />insert all<br />when area_code='001' then<br />into cust_accra values(ac_no,ac_name,area_code)<br />when area='003' then<br />into cust_kumasi values(ac_no,ac_name,area_code)<br />select * from customer ;<br /><br /><br />---PROJECT ON EXTERNAL TABLES<br />---MERGING<br /><br />PIVOTING INSERT<br /><br /> <br /><br />WE WILL CONTINUE, I HOPE U ENJOYED THE SHOW<br />ANY COMMENT, GIVE ME A SHOUT AT elvisboateng@gmail.comElvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com0tag:blogger.com,1999:blog-4332444962523974122.post-3406562947792077762010-09-09T03:24:00.001-07:002010-09-09T03:35:29.641-07:00ORACLE DATABASE ADMINISTRATION<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCGmZT67-pYFToK64mMnqHE5vDirXqf1bXsLwjeVXxxhB3gHVEKhTDmChHC9_UVMVtX3bPP2lXEMkA3gAvcds59AczhmJxGiD_g1L4p8NEebctZZJTrI_Wt2wJvZPVquiZZT6gqLJm6cM/s1600/SDC10159.JPG"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 320px; height: 240px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCGmZT67-pYFToK64mMnqHE5vDirXqf1bXsLwjeVXxxhB3gHVEKhTDmChHC9_UVMVtX3bPP2lXEMkA3gAvcds59AczhmJxGiD_g1L4p8NEebctZZJTrI_Wt2wJvZPVquiZZT6gqLJm6cM/s320/SDC10159.JPG" alt="" id="BLOGGER_PHOTO_ID_5514858049475146290" border="0" /></a><br /><div style="text-align: justify;"><span style="color: rgb(255, 0, 0);font-size:100%;" ><span style="font-weight: bold;">I WILL LIKE TO WELCOME YOU TO THIS WONDERFUL BLOG, IF THIS IS YOUR FIRST TIME OF VISITING THIS SITE. I PROMISE YOU WILL ENJOY THIS SITE IF YOU WANT TO MASTER ORACLE DATABASE ADMINISTRATION. I WILL BE UPDATING THIS BLOG VERY OFTEN, SO I ENTREAT YOU TO BE VISITING THIS BLOG VERY OFTEN FOR MORE WONDERFUL THINGS TO COME. FOR ANY CRITICISM OR PRAISE, GIVE ME A SHOUT AT</span></span> <span style="font-style: italic; color: rgb(51, 102, 255);">elvisboateng@yahoo.com</span></div>Elvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com2tag:blogger.com,1999:blog-4332444962523974122.post-85166986021156799392010-09-09T01:43:00.000-07:002010-09-17T04:55:56.386-07:00SQL PRACTICENOTE: You are to use the<span style="font-weight: bold;"> hr</span> schema<br /><br /> Let us refresh our minds on what a <span style="font-weight: bold;">table</span> is, in terms of columns, rows and field.<br /> before we start with the drill, you should be able to know all the columns a table has by issuing this instruction: <span style="font-weight: bold;">desc table_name example desc employees</span><br />1. select * from employees;<br /> This displays all the columns and rows in the table employees<br />2. select last_name, salary from employees;<br /> This will display two columns ie last_name and salary and all their <br /> corresponding rows in the table<br />3. select last_name, salary+200 from employees;<br /> This will display two columns ie last_name and 200 will be added to all the<br /> values in the salary column giving a new column salary + 200<br />4. select last_name, 12*(salary+100) from employees;<br /> Kindly describe the result<br />5. Read on NULL value<br /> select first_name,commission_pct from employees where last_name in ('Hall','Bull');<br /> select first_name,commission_pct*2 from employees where last_name in ('Hall','Bull');<br />6. select last_name name from employees;<br /> the column name last_name is replaced with name<br />7. select salary*12 "Annual Salary" from employees;<br />8. select last_name||' '||job_id AS "Employees" from employees;<br /><br /> select department_name||',it's assigned manager id:'||manager_id as <br /> "department and manager" from departments;<br /> <br /> select department_name||q'[,it's assigned manager id: ]' || manager_id as <br /> "department and manager" from departments<br /><br />9. select department_id from employees;<br />10. select distinct department_id from employees;<br />11. describe employees<br />12. desc employees<br />13. select employee_id, last_name from employees where department_id=90;<br />14. select last_name, department_id from employees where last_name='Whalen';<br />15. select last_name, salary from employees where salary <=3000; 16.select last_name, salary from employees where salary between 2500 and 3500; 17. select last_name, salary from employees where manager_id in (100,101,201); 18. select last_name from employees where first_name like 'S%'; 19. select last_name from employees where last_name like '_o%'; 20. select employee_id, last_name, job_id from employees where job_id like '%SA\_%' ESCAPE '\'; 21. select last_name, manager_id from employees where manager_id is NULL; 22. select employee_id, last_name, from employees where salary>10000 AND job_id like '%MAN%';<br />23. select employee_id, last_name, from employees where salary>10000 OR job_id like '%MAN%';<br />24. select employee_id, last_name, from employees where job_id NOT IN ('IT_PROG','SA_REP');<br />25. select last_name, job_id from employees order by hire_date;<br />26. select last_name, job_id from employees order by hire_date desc;<br />27. select last_name, job_id from employees order by department_id, salary DESC;<br />28. select employee_id, last_name from employees where employee_id=&emp_num;<br />29. select employee_id,last_name, &colum_name from employees where &condition order by &order_column;<br />30. select lower('SQL Course') from dual;<br />31. select upper('SQL Course') from dual;<br />32. select initcap('SQL Course') from dual;<br />33. select concat('Hello','World') from dual;<br />34. select substr('HelloWorld',1,5) from dual;<br />35. select substr(concat('Hello','World'),1,5) from dual;<br />36. select length('Mother') from dual;<br />37. select length(substr(concat('Hello','World'),1,5)) from dual;<br />38. select instr('monkey','k') from dual;<br />39. select lpad('elvis',10,'¢') from dual;<br />40. select rpad('elvis',10,'¢') from dual;<br />41. select replace ('Pencil','n','a') from dual;<br />42. select trim('H' from 'HelloWorld') from dual;<br />43. select trim('d' from 'HelloWorld') from dual;<br />44. select trim('W' from 'HelloWorld') from dual;<br />45. select trim('World' from 'HelloWorld') from dual;<br />46. select upper(replace(substr(trim('a' from rpad(lpad('Kenkey',12,'a'),15,'a')),1,3),'K','P')) from dual;<br />47. select round(45.926,2) from dual;<br />48. select trunc(45.926,2) from dual;<br />49. select mod(5,2) from dual;<br />50. select sysdate from dual;<br />51. select to_char(hire_date,'dd-mm-yy') from employees;<br />52. select to_char(hire_date,'day-mm-yy') from employees;<br />53. select to_char(hire_date,'dy-mm-yy') from employees;<br />54. select to_char(hire_date,'dd-mon-yy') from employees;<br />55. select to_char(hire_date,'dd-month-yy') from employees;<br />56.select to_char(hire_date,'dd-month-yyyy') from employees;<br />57.select to_char(hire_date,'dd-month-year') from employees;<br />58.select to_char(hire_date,'month-yyyy') from employees;<br />59.select to_char(hire_date,'fmmonth-yyyy') from employees;<br />60.select to_char(hire_date,'month/yyyy') hiredate from employees;<br />61.select to_char(hire_date,'fmmonthyyyy') from employees;<br />62.select to_char(salary,'99,999.99') salary from employees;<br />63.select last_name,commission_pct commission from employees where last_name like 'A%'<br />64.select last_name,commission_pct+100 commission from employees where last_name like 'A%'<br />65.select last_name,nvl(commission_pct,0) commission from employees where last_name like 'A%'<br />66.select last_name,nvl(commission_pct,0)+100 commission from employees where last_name like 'A%'<br />67. select last_name,commission_pct,nvl2(commission_pct,'Earned Commission','No Commission') from employees where last_name like 'A%'<br />68.create table test<br />(<br />name varchar2(10),<br />69.salary number,<br />allowance number<br />);<br />70.select * from test;<br />71.insert into test values('Kofi',120,100);<br />72.insert into test values('Paul',90,90);<br />73.insert into test values('Raymoon',60,60);<br />74.insert into test values('Elvis',110,110);<br />75.insert into test values('Baby',80,150);<br />commit;<br />76.select * from test;<br />77.select name,salary,allowance, nullif(salary,allowance) from test;<br />78select name,salary,allowance, nullif(allowance,salary) from test;<br />79.select last_name,coalesce(commission_pct,1) from employees where last_name like 'A%'<br />80.select last_name,salary,job_id,<br /> case job_id when 'IT_PROG' THEN 1.1*salary<br /> when 'ST_CLERK' THEN 1.15*salary<br /> when 'SA_REP' THEN 1.2*salary<br /> else salary<br /> end "Revised Salary"<br /> from employees;<br />81.select last_name,salary,job_id,<br /> decode(job_id,'IT_PROG',1.1*salary,<br /> 'ST_CLERK',1.15*salary,<br /> 'SA_REP',1.2*salary,<br /> salary) "Revised Salary"<br /> from employees; <br /><br />82.select avg(salary),max(salary),min(salary),sum(salary),stddev(salary),variance(salary) from employees;<br />83.select count(*) from employees;<br />84.select count(commission_pct) from employees;<br />85.select count(employee_id) from employees;<br />86.select count(department_id) from employees;<br />87.select count(distinct department_id) from employees;<br /><br />Questions<br /><br />1.Create a report to display the manager number and the salary of the lowest paid employee for that manager.<br />Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000 or less. Sort the output in descending order<br />of salary.<br /><br /><br />88. select department_id,department_name,location_id,city<br />from departments<br />natural join locations;<br /><br /><br />89. select department_id,department_name,a.location_id,city<br />from departments a, locations b<br />where a.location_id=b.location_id;<br /><br /><br />90. select employee_id,last_name,department_id,manager_id,department_name<br />from employees<br />natural join departments;<br /><br /><br />91. select employee_id,last_name,a.department_id,a.manager_id,department_name<br />from employees a, departments b<br />where a.department_id=b.department_id<br />and a.manager_id=b.manager_id;<br /><br /><br />92. select employee_id,last_name,department_id,b.manager_id,department_name<br />from employees a join departments b<br />using(department_id);<br /><br /><br />93. select employee_id,last_name,department_id,a.manager_id,department_name<br />from employees a join departments b<br />using(department_id);<br /><br /><br />94. select employee_id,last_name,department_id,a.manager_id,department_name<br />from employees a join departments b<br />using(department_id)<br />where a.department_id=50;<br /><br /><br />95. select employee_id,last_name,a.department_id,manager_id,department_name<br />from employees a join departments b<br />using(manager_id);<br /><br /><br />96. select employee_id,last_name,department_id,a.manager_id,department_name<br />from employees a join departments b<br />on (a.department_id=b.department_id);<br /><br /><br />97. select employee_id,last_name,a.department_id,a.manager_id,department_name<br />from employees a join departments b<br />on (a.department_id=b.department_id);<br /><br /><br />98. select e.last_name,m.last_name<br />from employees e join employees m<br />on (e.manager_id=m.employee_id);<br /><br /><br />99. select e.last_name,e.department_id,d.department_name<br />from employees e left outer join departments d<br />on (e.department_id=d.department_id);<br /><br /><br />100. select e.last_name,e.department_id,d.department_name<br />from employees e right outer join departments d<br />on (e.department_id=d.department_id);<br /><br /><br />101. select e.last_name,e.department_id,d.department_name<br />from employees e full outer join departments d<br />on (e.department_id=d.department_id);<br /><br /><br />102. select last_name,department_name<br />from employees<br />cross join departments;<br /><br /><br />103. select last_name<br />from employees<br />where salary=<br />(<br />select min(salary) from employees<br />);<br /><br /><br />104. select last_name,department_id,salary<br />from employees<br />where salary =<br />(select min(salary) from employees group by department_id);<br /><br /><br />105. select last_name,department_id,salary<br />from employees<br />where salary in<br />(select min(salary) from employees group by department_id);<br /><br /><br />106. select last_name,department_id,salary<br />from employees<br />where salary < (select min(salary) from employees group by department_id); select name,salary from test where salary > any(select salary from test where name in('Kofi','Paul'));<br /><br /><br />107. select name,salary from test<br />where salary > all(select salary from test where name in('Kofi','Paul'));<br /><br /><br />108. select last_name,department_id,salary<br />from employees<br />where salary < id="1;" id="1;" id="1;">500));<br /><br />156. insert into box values(1,200);<br /><br />157. insert into box values(1,500);<br /><br />158. insert into box values(1,501);<br /><br />159. create table dept100<br /> as<br /> select employee_id,salary from employees where department_id=100;<br /><br /><br /><br />160. create table dept101<br /> as<br /> select employee_id,salary*10 from employees where department_id=101;<br /><br /><br />161. create table dept101<br /> as<br /> select employee_id,salary*10 sal from employees where department_id=101;<br /><br /><br />162. select * from dept101;<br /><br />163. create table dept110 (emp, sal)<br /> as<br /> select employee_id,salary*10 from employees where department_id=110;<br /><br />164. select * from dept110;<br /><br />165. alter table dept110<br /> add (job_id varchar2(10),hire_date date default sysdate);<br /><br />166. select * from dept110;<br /><br />167. desc dept110<br /><br />168. alter table dept110 modify (emp varchar2(10));<br /><br />169. alter table dept110 modify (job_id number(10));<br /><br />170. alter table dept110 modify (job_id varchar2(10));<br /><br />171. truncate table dept110;<br /><br />172. alter table dept110 modify (emp varchar2(10));<br /><br />173. desc dept110<br /><br />174. select * from dept110;<br /><br />175. alter table dept110 drop column job_id;<br /><br />176. select * from dept110;<br /><br />177. alter table dept110 set unused column sal;<br /><br />178. select * from dept110;<br /><br />179. alter table dept110 drop unused columns;<br /><br />180. select * from user_unused_col_tabs;<br /><br />181. log in as sys and create table emp<br /><br />182. create table hr.emp<br /> as select * from scott.emp;<br /><br />183. create table hr.dept<br /> as select * from scott.dept;<br /><br />184. Now log in as hr<br /><br />185. CREATE VIEW myview<br /> AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB<br /> FROM emp<br /> WHERE deptno=20;<br /><br />186. CREATE force VIEW myview<br /> AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB<br /> FROM emps<br /> WHERE deptno=20;<br /><br />187. select * from myview;<br /><br />188. CREATE or replace VIEW myview<br /> AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB<br /> FROM emp<br /> WHERE deptno=20;<br /><br />/* the same as<br /><br />189. CREATE VIEW myview(EMLOYEE_NO,NAME,SALARY,JOB)<br /> AS SELECT empno,ename,sal,job<br /> FROM emp<br /> WHERE deptno=20;<br /><br />*/ <br /><br />190. select * from myview;<br /><br />191. CREATE VIEW v2<br /> AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)<br /> FROM emp e, dept d<br /> WHERE e.deptno = d.deptno<br /> GROUP BY d.dname;<br /><br /><br />192. CREATE VIEW v2<br /> AS SELECT d.dname, MIN(e.sal) mini, MAX(e.sal) maxi , AVG(e.sal) aver<br /> FROM emp e, dept d<br /> WHERE e.deptno = d.deptno<br /> GROUP BY d.dname;<br /><br />193. select * from v2Elvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com0tag:blogger.com,1999:blog-4332444962523974122.post-2283324133240523242010-09-09T01:41:00.000-07:002010-09-09T01:43:35.793-07:00COURSE OUTLINE<p style="margin: 0in 0in 0.0001pt;"><span class="topstoryhead1"><span style="font-size: 9pt;">Oracle Database 10<i>g</i>: Database Administration Workshop </span></span><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal"><span class="parahead21"><span style="font-size: 9pt;"><o:p> </o:p></span></span></p> <p class="MsoNormal"><span class="parahead21"><span style="font-size: 9pt;">What you will learn:</span></span><span style="font-size: 9pt; font-family: "Arial","sans-serif";"> <o:p></o:p></span></p> <p style="margin: 0in 0in 0.0001pt; text-align: justify;"><span style="font-size: 9pt; font-family: "Arial","sans-serif"; color: black;">This course is your first step towards success as an Oracle professional, designed to give you a firm foundation in basic database administration. In this class, you'll learn how to install and maintain an Oracle database. You will gain a conceptual understanding of the Oracle database architecture and how its components work and interact with one another. You will also learn how to create an operational database and properly manage the various structures in an effective and efficient manner including performance monitoring, database security, user management, and backup/recovery techniques. The lesson topics are reinforced with structured hands-on practices. This course is designed to prepare you for the corresponding Oracle Certified Associate exam.<o:p></o:p></span></p> <p class="MsoNormal"><span class="bodycopy1"><b><span style="font-size: 9pt;"><o:p> </o:p></span></b></span></p> <p class="MsoNormal"><span class="parahead11"><span style="font-size: 9pt;">Course Topics: <o:p></o:p></span></span></p> <p class="MsoNormal"><span class="parahead11"><span style="font-size: 9pt;"><o:p> </o:p></span></span></p> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Writing SQL SELECT statements<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Define projection, selection, and join terminology<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Review the basic SQL SELECT statement syntax<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Select all columns using a wildcard notation from a table<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">State simple rules and guidelines for writing SQL statements<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Write a query containing the arithmetic operators<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use aliases to customize column headings<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a character expression with the concatenation operator<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the quote operator q to control text strings containing the quote character<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Restricting Data<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Limit Rows Using a Selection<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the WHERE Clause<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the main Comparison Conditions<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the LIKE Condition to Compare Literal Values<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the Logical Conditions AND, OR, NOT<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use Multiple Conditions in the WHERE clause<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe the Rules of Precedence<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Sorting Data<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Sort Rows with the ORDER BY Clause<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the && Substitution Variable<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the iSQL*Plus DEFINE Command<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the VERIFY Command<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">SQL Functions<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Differentiate between Single Row and Multiple Row SQL Functions<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Categorize the Character Functions into Case Manipulation and Character Manipulation types<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain the Numeric Functions ROUND, TRUNC, and MOD<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the Rules for Applying the Arithmetic Operators on Dates<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the Arithmetic Operators with Dates in the SELECT Clause<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain Implicit and Explicit conversion<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Nest Functions to Perform Multiple Tasks in One Statement<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Conditional Expressions<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the CASE Expression<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain the DECODE Expression<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Aggregating Data Using Group Functions<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Categorize the Types of Group Functions<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Utilize the DISTINCT Keyword with the Group Functions<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe how Nulls are handled with the Group Functions<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create Groups of Data with the GROUP BY Clause<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Group Data by more than one column<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Avoid Illegal Queries with the Group Functions<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Exclude Groups of Data with the HAVING Clause<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Displaying Data from Multiple Tables<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Identify Types of Joins<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Retrieve Records with Natural Joins<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use Table Aliases to write shorter code and explicitly identify columns from multiple tables<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a Join with the USING clause to identify specific columns between tables<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the ON clause to specify arbitrary conditions or specify columns to Join<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a Three-way join with the ON clause to retrieve information from 3 tables<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the Types of Outer Joins LEFT, RIGHT, and FULL<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Generating a Cartesian Product<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Using Subqueries<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use a sub query to solve a problem<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Identify where sub queries can be placed in a SELECT statement<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe the types of sub queries (single row, multiple row)<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Show the single row sub query operators<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the group functions in a sub query<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Identify illegal statements with sub queries<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Show the multiple row sub query operators<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain how null values are handled in sub queries<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Using the Set Operators<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the UNION ALL operator to return all rows from multiple tables (with duplicates)<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe the INTERSECT operator<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the INTERSECT operator<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain the MINUS operator<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the MINUS operator<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the SET operator guidelines<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Order results when using the UNION operator<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Inserting and Updating Data<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Write INSERT statements to add rows to a table<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Insert Special Values<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Copy Rows from Another Table<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Update Rows in a Table<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Deleting Data<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use DELETE statements to remove rows from a table<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Delete Rows Based on Another Table<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe the TRUNCATE Statement<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Database Transactions<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Show how Read Consistency works<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></h4> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Using DDL Statements<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the main database objects<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Identify the Naming Rules<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Display the basic Syntax for Creating a Table<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Show the DEFAULT option<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the Data Types that are available for Columns<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Managing Tables<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain the different types of constraints<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Show resulting exceptions when constraints are violated with DML statements<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a table with a sub query<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe the ALTER TABLE functionality<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Remove a table with the DROP statement<o:p></o:p></span></li></ul> <h4><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Creating Other Schema Objects<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Categorize simple and complex views and compare them<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a view<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Retrieve data from a view<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Explain a read-only view<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the rules for performing DML on complex views<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a sequence<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the basic rules for when to create and not create an index<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Create a synonym<o:p></o:p></span></li></ul> <h4><a name="hw"></a><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Managing Objects with Data Dictionary Views<o:p></o:p></span></h4> <ul type="disc"><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Describe the structure of each of the dictionary views<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">List the purpose of each of the dictionary views<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Write queries that retrieve information from the dictionary views on the schema objects<o:p></o:p></span></li><li class="MsoNormal" style=""><span style="font-size: 9pt; font-family: "Arial","sans-serif";">Use the COMMENT command to document objects<o:p></o:p></span></li></ul> <p class="MsoNormal"><b><span style="font-size: 9pt; font-family: "Arial","sans-serif"; color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Introduction (Database Architecture) </span></span></span></b><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><br /><span class="bodycopy1"><span style="">Describe course objectives </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Explore the Oracle 10g database architecture </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Installing the Oracle Database Software on UNIX </span></span></span></b><br /><span class="bodycopy1"><span style="">Explain core DBA tasks and tools </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Plan an Oracle installation </span></span><br /><span class="bodycopy1"><span style="">Use optimal flexible architecture </span></span><br /><span class="bodycopy1"><span style="">Install software with the Oracle Universal Installer (OUI) </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Creating an Oracle Database </span></span></span></b><br /><span class="bodycopy1"><span style="">Create a database with the Database Configuration Assistant (DBCA) </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Create a database design template with the DBCA </span></span><br /><span class="bodycopy1"><span style="">Generate database creation scripts with the DBCA </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Managing the Oracle Instance </span></span></span></b><br /><span class="bodycopy1"><span style="">Start and stop the Oracle database and components </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Use Enterprise Manager (EM) </span></span><br /><span class="bodycopy1"><span style="">Access a database with SQL*Plus and iSQL*Plus </span></span><br /><span class="bodycopy1"><span style="">Modify database initialization parameters </span></span><br /><span class="bodycopy1"><span style="">Understand the stages of database startup </span></span><br /><span class="bodycopy1"><span style="">View the Alert log </span></span><br /><span class="bodycopy1"><span style="">Use the Data Dictionary </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Managing Database Storage Structures </span></span></span></b><br /><span class="bodycopy1"><span style="">Describe table data storage (in blocks) </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Define the purpose of tablespaces and data files </span></span><br /><span class="bodycopy1"><span style="">Understand and utilize Oracle Managed Files (OMF) </span></span><br /><span class="bodycopy1"><span style="">Create and manage tablespaces </span></span><br /><span class="bodycopy1"><span style="">Obtain tablespace information </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Administering User Security </span></span></span></b><br /><span class="bodycopy1"><span style="">Create and manage database user accounts </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Authenticate users </span></span><br /><span class="bodycopy1"><span style="">Assign default storage areas (tablespaces) </span></span><br /><span class="bodycopy1"><span style="">Grant and revoke privileges </span></span><br /><span class="bodycopy1"><span style="">Create and manage roles </span></span><br /><span class="bodycopy1"><span style="">Create and manage profiles </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Configuring the Oracle Network Environment </span></span></span></b><br /><span class="bodycopy1"><span style="">Use Enterprise Manager for configuring the Oracle network environment </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Create additional listeners </span></span><br /><span class="bodycopy1"><span style="">Create Net Service aliases </span></span><br /><span class="bodycopy1"><span style="">Configure connect-time failover </span></span><br /><span class="bodycopy1"><span style="">Control the Oracle Net Listener </span></span><br /><span class="bodycopy1"><span style="">Test Oracle Net connectivity </span></span><br /><span class="bodycopy1"><span style="">Identify when to use shared versus dedicated servers </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Automatic Performance Management </span></span></span></b><br /><b><span style="color: rgb(102, 102, 102);">The Use of 10g Database Advisors<span class="parahead21"><span style=""> </span></span></span></b><br /><span class="bodycopy1"><span style="">Identifying Tunable Components </span></span><br /><span class="bodycopy1"><span style="">Advisory Framework </span></span><br /><span class="bodycopy1"><span style="">Using the SQL Tuning and SQL Access Advisor </span></span><br /><span class="bodycopy1"><span style="">Manage the Automatic Workload Repository (AWR) </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Use the Automatic Database Diagnostic Monitor (ADDM) </span></span><br /><span class="bodycopy1"><span style="">Describe advisory framework </span></span><br /> <!--[if !supportLineBreakNewLine]--><br /> <!--[endif]--><o:p></o:p></span></span></p> <p class="MsoNormal"><span class="parahead21"><span style="font-size: 9pt;">Using AWR </span></span><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><br /><span class="bodycopy1"><span style="">Create and manage AWR snapshots </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Generate AWR reports </span></span><br /><span class="bodycopy1"><span style="">Create snapshot sets and compare periods </span></span><br /><span class="bodycopy1"><span style="">Generate ADDM reports </span></span><br /><span class="bodycopy1"><span style="">Generate ASH reports </span></span><br /> <!--[if !supportLineBreakNewLine]--><br /> <!--[endif]--><o:p></o:p></span></span></p> <p class="MsoNormal"><span class="parahead21"><span style="font-size: 9pt;">Backup and Recovery Concepts </span></span><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><br /><span class="bodycopy1"><span style="">Identify the types of failure that may occur in an Oracle Database </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Describe ways to tune instance recovery </span></span><br /><span class="bodycopy1"><span style="">Identify the importance of checkpoints, redo log files, and archived log files </span></span><br /><span class="bodycopy1"><span style="">Configure ARCHIVELOG mode </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Performing Database Backups </span></span></span></b><br /><span class="bodycopy1"><span style="">Create consistent database backups </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Back your database up without shutting it down </span></span><br /><span class="bodycopy1"><span style="">Create incremental backups </span></span><br /><span class="bodycopy1"><span style="">Automate database backups </span></span><br /><span class="bodycopy1"><span style="">Monitor the flash recovery area </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Performing Database Recovery </span></span></span></b><br /><span class="bodycopy1"><span style="">Recover from loss of a control file </span></span><span style="color: black;"><br /><span class="bodycopy1"><span style="">Recover from loss of a redo log file </span></span><br /><span class="bodycopy1"><span style="">Perform complete recovery following the loss of a data file </span></span><br /></span><b><span style="color: rgb(102, 102, 102);"><br /> <!--[if !supportLineBreakNewLine]--><br /> <!--[endif]--></span></b><span style="display: none;"><o:p></o:p></span></span></p> <p class="MsoNormal" style=""><span class="parahead21"><span style="font-size: 9pt;">Configuring Recovery Manager </span></span><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><br /><span class="bodycopy1"><span style="">Recovery Manager Features and Components </span></span><br /><span class="bodycopy1"><span style="">Using a Flash Recovery Area with RMAN </span></span><br /><span class="bodycopy1"><span style="">Configuring RMAN </span></span><br /><span class="bodycopy1"><span style="">Control File Autobackups </span></span><br /><span class="bodycopy1"><span style="">Retention Policies and Channel Allocation </span></span><br /><span class="bodycopy1"><span style="">Using Recovery Manager to connect to a target database in default NOCATALOG mode </span></span><br /><span class="bodycopy1"><span style="">Displaying the current RMAN configuration settings </span></span><br /><span class="bodycopy1"><span style="">Altering the backup retention policy for a database </span></span><br /><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Using Recovery Manager </span></span></span></b><br /><span class="bodycopy1"><span style="">RMAN Command Overview </span></span><br /><span class="bodycopy1"><span style="">Parallelization of Backup Sets </span></span><br /><span class="bodycopy1"><span style="">Compressed Backups </span></span><br /><span class="bodycopy1"><span style="">Image Copy </span></span><br /><span class="bodycopy1"><span style="">Whole Database and Incremental Backups </span></span><br /><span class="bodycopy1"><span style="">LIST and REPORT commands </span></span><br /><span class="bodycopy1"><span style="">Enable ARCHIVELOG mode for the database </span></span><br /><span class="bodycopy1"><span style="">Use Recovery Manager </span></span><br /><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Recovering from Non-critical Losses </span></span></span></b><br /><span class="bodycopy1"><span style="">Recovery of Non-Critical Files </span></span><br /><span class="bodycopy1"><span style="">Creating New Temporary Tablespace </span></span><br /><span class="bodycopy1"><span style="">Recreating Redo Log Files, Index Tablespaces, and Indexes </span></span><br /><span class="bodycopy1"><span style="">Read-Only Tablespace Recovery </span></span><br /><span class="bodycopy1"><span style="">Authentication Methods for Database Administrators </span></span><br /><span class="bodycopy1"><span style="">Loss of Password Authentication File </span></span><br /><span class="bodycopy1"><span style="">Creating a new temporary tablespace </span></span><br /><span class="bodycopy1"><span style="">Altering the default temporary tablespace for a database </span></span><br /><b><span style="color: rgb(102, 102, 102);"><br /><span class="parahead21"><span style="">Incomplete Recovery </span></span></span></b><br /><span class="bodycopy1"><span style="">Recovery Steps </span></span><br /><span class="bodycopy1"><span style="">Server and User Managed Recovery commands </span></span><br /><span class="bodycopy1"><span style="">Recovering a Control File Autobackup </span></span><br /><span class="bodycopy1"><span style="">Creating a New Control File </span></span><br /><span class="bodycopy1"><span style="">Incomplete Recovery Overview </span></span><br /><span class="bodycopy1"><span style="">Incomplete Recovery Best Practices </span></span><br /><span class="bodycopy1"><span style="">Simplified Recovery Through RESETLOGS </span></span><br /><span class="bodycopy1"><span style="">Point-in-time recovery using RMAN </span></span><br /> <!--[if !supportLineBreakNewLine]--><br /> <!--[endif]--><o:p></o:p></span></p> <p class="MsoNormal"><span class="parahead21"><span style="font-size: 9pt;">Flashback </span></span><span style="font-size: 9pt; font-family: "Arial","sans-serif";"><br /><span class="bodycopy1"><span style="">Flashback Database Architecture </span></span><br /><span class="bodycopy1"><span style="">Configuring and Monitoring Flashback Database </span></span><br /><span class="bodycopy1"><span style="">Backing Up the Flash Recovery Area </span></span><br /><span class="bodycopy1"><span style="">Flashback Database Considerations </span></span><br /><span class="bodycopy1"><span style="">Using Flashback Database EM Interface </span></span><br /><span class="bodycopy1"><span style="">Managing and monitoring Flashback Database operations</span></span><o:p></o:p></span></p>Elvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com0tag:blogger.com,1999:blog-4332444962523974122.post-34567804847986935842010-08-13T06:02:00.000-07:002010-08-16T04:34:50.781-07:00PROJECTPROJECT<br />1. Create a folder ( C:\database ), in the database folder create folders data, log, flash, backup<br />2(a). Create a transaction processing database named PROJECT, run a script mcctb.sql while creating the database. Try to add your own scripts<br />2(b). During the creation of the database, kindly answer the following questions:<br />i. Difference among custom, data warehouse, general purpose and transactional databases<br />j. The purposes of the following users: sys, sysman, system, dbsnmp<br />k. The differences among the various storage mechanism: file system, automatic storage management (ASM) and raw devices<br />l. What is Oracle-Managed Files<br /><br />3. (Write a script to) answer the following questions:<br />a. What is the oracle base, oracle home<br />b. What is the instance name, database name and global database name<br />c. What is the flash recovery area<br />d. What is the name and location of the alert log<br />e. What are the names and locations of all datafiles, temporary files, online redo log files and control files.<br />f. What is the name and location of parameter file<br />4. Look into the mcctb.sql script and log in as the user specified in the script<br />5. Always look into the alert log at each stage<br />6. Make sure you have both pfile and spfile. If only one exist, create the other one. Note: Make sure you can create pfile from spfile and vice versa.<br />7. Shutdown immediate<br />8. Remove all the parameter files from their location<br />9. Startup nomount and note the error<br />10. Bring back the spfile and startup nomount<br />11. What is Total System Global Area ?<br />12. Briefly describe the components of the SGA based on what is on your screen.<br />13. Determine the status of the database.<br />14. What goes into startup nomount ….. ?<br />15. To check the content of the parameter file, issue show parameter<br />16. Example to check the value of db_name, type show parameter db_name. This means that, to check the value of a particular parameter issue show parameter parameter_name<br />17. The locations of the control files can be located in the parameter file, so find these locations.<br />18. Alternatively, issue the following statements: select name from v$controlfile; and select name from v$datafile;. What did you observed?<br />19. Remove the locations control files from their locations<br />20. Let us move into the mount state, alter database mount and note down the error.<br />21. Bring back all the control files to their respective locations and mount the database<br />22. Determine the status of the database.<br />23. After mounting the database, issue the statements: select name from v$controlfile; and select name from v$datafile;.<br />24. What going into mounting a database<br />25. Open the database, alter database open;.<br />26. Determine the status of the database.<br />27. The name format of the password file is PWDsid.ora, it is in the same locations as with the parameter file. Find the one for this database<br />28. All those with sysdba and sysoper roles can be found in the password file.<br />29. Let us check those with sysdba and sysoper roles: select * from v$pwfile_users;<br />30. grant sysdba to mcctb<br />31. issue the query : select * from v$pwfile_users;<br />32. grant sysoper to mcctb<br />33. issue the query : select * from v$pwfile_users;<br />34. Shutdown immediate<br />35. Move the password file from its location<br />36. startup the database<br />37. issue the query : select * from v$pwfile_users;<br />38. How can you recreate the password file<br />39. What is your observation about password file<br />40. Summarize differences among shutdown normal, transactional, immediate and abort with examples<br />41. When do we use startup force<br />42. Create an sqlplus shortcut on the desktop to log into user account mcctb<br />43. Open a fresh command prompt, set oracle_sid=project<br />44. Issue: emctl status dbconsole, to check the status of the enterprise manager (EM)<br />45. If the controller is down, issue : emctl start dbconsole<br />46. Copy the URL onto internet explorer address bar<br />47. Log in into the Enterprise Manager<br />48. List all data dictionary and dynamic performance views which has something to do with control files, datafiles, tablespace, online and archive redo log files <br />49. Check the default permanent and temporary tablespaces and also the default undo tablespace<br />50. Use the enterprise manager to create one permanent , temporary and undo tablespaces<br />51. Make the above the default permanent, temporary and undo tablespaces<br />52. Change the locations of all the datafiles to c:\database\data, the alert log to c:\database\log and flash recovery area to c:\database\flash<br />53. Using the Enterprise Manager, go onto the Maintenance tab, click on the Schedule Backup, fill in the Host Credentials using the user and password of the Operating System, click on Schedule Oracle-Suggested Backup, check Disk, click next, next. Read with patience to the final stage and submit<br /><br /><br /><br />content of script mcctb.sql in notepad<br /><br />create user mcctb identified by mcctb;<br />grant dba to mcctb;Elvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com0tag:blogger.com,1999:blog-4332444962523974122.post-71336521632861251822010-06-08T11:09:00.000-07:002010-06-08T11:17:20.497-07:00USERSLOG IN AS SYS AND RUN THE FF<br /><br />set pagesize 100<br />col profile format a10<br />col temporary_tablespace format a10<br />col default_tablespace format a10<br />set linesize 200<br />col account_status format a10<br />col username format a10<br />col grantee format a10<br />col owner format a10<br />col table_name format a10<br />col grantor format a10<br />col privilege format a15<br />col column_name format a11<br />col role format a10<br /><br />AFTERWARDS THAT GO THROUGH THE FOLLOWING STEPS:<br /><br /><br /><br /><br />1. Connect as sys<br />2. Create a permanent tablespace (obo) of size 5m<br />3. Create a temporary tablespaces (marian) of size 5m<br />4. Create a user <br /> create user cipd identified by cipd<br /> default tablespace obo<br /> temporary tablespace marian<br /> profile default<br /> password expire<br /> account lock<br /> quota 1m on obo;<br /><br />5. Run the query below to check the properties of the users<br /> Select username,account_status,lock_date,expiry_date,default_tablespace,<br /> temporary_tablespace,profile from dba_users;<br />6. Conn as the user created in (4) above<br />7. What is the error encountered. Take closely look at step 4<br />8. Connect as sys<br />9. Run the query below and repeat step 6. What were your observations<br /> alter user cipd account unlock;<br />10. Log in as sys and run the query below, repeat step 6 and show user<br />grant create session to cipd;<br />11. Repeat step 5. What were your observations<br />12. Run queries below:<br /><br />select * from user_sys_privs;<br />select * from user_role_privs;<br />select * from user_tab_privs_recd;<br />select * from user_tab_privs_made;<br />select * from user_tab_privs;<br />select * from user_col_privs_recd;<br />select * from user_col_privs_made;<br />select * from user_col_privs;<br />select * from role_role_privs;<br />select * from role_sys_privs;<br />select * from role_tab_privs;<br />select * from session_privs;<br />select * from session_roles;<br /><br /><br /><br />What is your observation <br />NB: You will be running the queries above several times, kindly observe the rows that will be return as the queries are being run.<br />13. Create a table by using the query below.<br /> create table ttb ( id number );<br /> Note the error<br />14. run insert into ttb values (1); and commit<br />15. Repeat step 12<br />16. Log in as hr and select * from cipd.ttb, note down the error<br />17. Log in as cipd and run the query below<br />grant select on ttb to hr;<br />repeat step 16<br />18. Repeat step 12<br />19. Create another table ssb<br /> Create table ssb (id number, name varchar2(10));<br />20. Log in as hr and select * from cipd.ssb<br />21. Log in as cipd and issue: grant insert (id) on ssb to hr; <br />22. Log in as hr and issue: insert into cipd.ssb(id) values(1); and again <br /> select * from cipd.ssb<br />23. Log in as cipd and select from ssb<br />24. Log in again as hr and issue: insert into cipd.ssb values(2,'KKK'). <br /> Note down the error. Issue this other statement: insert into cipd.ssb(id) values(2);<br />25. Connect as cipd and repeat step 12<br />26. Connected as cipd create a user kwame.<br />27. If the above fail, conn as sys and issue: grant create user to cipd and repeat 26. Please make sure you can log in as Kwame.<br />28. Log in as cipd and issue: grant create table to Kwame, what happened..?<br />29. Connect as sys and issue: grant create table to cipd with admin option<br />30. Repeat step 28<br />31. Connect as hr and issue: grant select on employees to cipd;<br />32. Connect as cipd and repeat step 12<br />33. Connect as scott and issue : grant insert (empno) on emp to cipd;<br />34. Connect as cipd and repeat step 12<br />35. Connected as cipd issue: grant insert (empno) on scott.emp to kwame;<br />36. Note the error<br />37. Connect as scott and issue : grant insert (empno) on emp to cipd with grant option;<br />38. Repeat step 35<br />39. Connected as cipd run step 12<br />40. Connected as cipd issue: create role mama identified by mama;<br />41. Connect as sys and grant create role to cipd. And repeat step 40<br />42. Connect as cipd and issue: grant create table to mama;<br />43. Run step 12<br />44. Connected as cipd issue: grand select on ttb to mama; and repeat step 12<br />45. As cipd issue: create role papa identified by papa and repeat step 12<br />46. AS cipd issue: grant mama to papa and repeat step 12<br />47. As sys issue: grant connect to cipd and repeat step 12Elvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com1tag:blogger.com,1999:blog-4332444962523974122.post-52535406039439442082010-06-03T01:31:00.000-07:002010-06-03T01:51:18.934-07:00<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxECp6LaXJc7MmvYTmwh3Vtw5Zx04xkkbT3YYhrjdK0Snzrr609GgtZnB-WhIPK0aNjCSvu1NAA9B-t2j54jcWd8ZbrQcenlTZ4ZxyXWgsZS0PMRFuHOsVIu3FmmBwttniHQtoHa2Uoig/s1600/SDC10159.JPG"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 320px; height: 240px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxECp6LaXJc7MmvYTmwh3Vtw5Zx04xkkbT3YYhrjdK0Snzrr609GgtZnB-WhIPK0aNjCSvu1NAA9B-t2j54jcWd8ZbrQcenlTZ4ZxyXWgsZS0PMRFuHOsVIu3FmmBwttniHQtoHa2Uoig/s320/SDC10159.JPG" alt="" id="BLOGGER_PHOTO_ID_5478464861005537922" border="0" /></a>This practice will help you understand or appreciate tablespace better. Use the command prompt to undertake this project<br />.<br /><br />Let me know your worries for additions and omissions.<br /><br /><br /><br /><br /><br /><br /><br /><ol style="margin-top: 0in;" start="1" type="1"><li class="MsoNormal" style="">Log in as sys and start your database with an spfile</li><li class="MsoNormal" style="">Check whether the spfile was used to start the database ---refer to query 28. If the value of the spfile is NULL. Then try to figure out how to perform step 1 well. </li><li class="MsoNormal" style="">Create a user named “AMA” – refer to query 1</li><li class="MsoNormal" style="">Grant a dba role to user ama ---refer to query 2</li><li class="MsoNormal" style="">Log in as ama</li><li class="MsoNormal" style="">Create a small file permanent tablespace <span style=""> </span>--refer to query 3</li><li class="MsoNormal" style="">Create a big file permanent tablespace --- refer to query 4</li><li class="MsoNormal" style="">Create a temporary tablespace --- refer to query 5 ----try to bypass this level </li><li class="MsoNormal" style="">Create an undo tablespace named CAIN – refer to query 24</li><li class="MsoNormal" style="">Check the default undo tablespace --- refer to query 25</li><li class="MsoNormal" style="">Run query 26</li><li class="MsoNormal" style="">Repeat step 10</li><li class="MsoNormal" style="">Run any DML or DCL statement<span style=""> </span>---refer to query 33</li><li class="MsoNormal" style="">Is there any error?</li><li class="MsoNormal" style="">Log in as sys and repeat step 11, query 34 and commit. Is there any error?</li><li class="MsoNormal" style="">Run query 27</li><li class="MsoNormal" style="">Log in as ama and run queries 33, 34 and commit</li><li class="MsoNormal" style="">What is your observation and conclusion from steps 9 to 17</li><li class="MsoNormal" style="">Check the properties of the tablespaces created above ---refer to query 6 to 8</li><li class="MsoNormal" style="">Take note of the number of blocks allocated to the datafiles and tempfiles</li><li class="MsoNormal" style="">Check the default permanent and temporary tablespaces<span style=""> </span>and also some other properties of the database---- refer to query 9</li><li class="MsoNormal" style="">Change the default permanent and temporary tablespaces to mama and gimpa respectively<span style=""> </span>---refer to query 10</li><li class="MsoNormal" style="">Repeat step 21, to confirm the change made in step 22</li><li class="MsoNormal" style="">create a table (segment) by name transact<span style=""> </span>--- refer to query 11</li><li class="MsoNormal" style="">Check the properties of the segment (transact) , write the detail somewhere---refer to query 12</li><li class="MsoNormal" style="">Calculate the space in KB occupied by the segment (transact) based on the query above. write the detail somewhere</li><li class="MsoNormal" style="">Check the detail number of extents allocated to the segment (transact) --- refer to query 13. write the detail somewhere</li><li class="MsoNormal" style="">Create another index segment transact_idx<span style=""> </span>--- refer to query 14</li><li class="MsoNormal" style="">Repeat steps 25 to 27 to check the properties of the index segments, transact_idx</li><li class="MsoNormal" style="">Create <span style=""> </span>sequences seqq01,seqq02 and seqq03 ---refer to query 15 </li><li class="MsoNormal" style="">Check all the objects owned by AMA ---refer to query 16</li><li class="MsoNormal" style="">Also check the spaced used on the tablespace MAMA<span style=""> </span>--- refer to query 17</li><li class="MsoNormal" style="">Insert rows into the table transact --- refer to query 18</li><li class="MsoNormal" style="">If there is an error ---refer to queries 35 and 36---state your observation and make sure step 33 goes through (you can look at query 21).</li><li class="MsoNormal" style="">Repeat steps 25 to 27 and step 32 to check the properties of the segments(transact and transact_idx)</li><li class="MsoNormal" style=""><span style=""> </span>Insert another rows into transact --- refer to query 19 and if an error occur refer to query 21 for solution.</li><li class="MsoNormal" style="">Repeat step 35 and how many extents are free in tablespace mama</li><li class="MsoNormal" style="">Insert another rows into transact ---refer to query 20</li><li class="MsoNormal" style="">Repeat step 35</li><li class="MsoNormal" style="">An error will be return, refer to query 21 to resolve the error</li><li class="MsoNormal" style=""><span style=""> </span>Repeat step 35 and run query 22 afterwards</li><li class="MsoNormal" style="">How many segments does AMA have</li><li class="MsoNormal" style="">How many extents are contain in each segments mention in step 42 above</li><li class="MsoNormal" style="">What is the size in MB of each segment mention in step 42 above</li><li class="MsoNormal" style="">What is your observation about segments spanning datafiles</li><li class="MsoNormal" style="">Describe the table transact. This table contains information about the transactions which took place for a financial institution with nine branches. The column transact_id contains transaction id of each transaction. The first number of the transact_id indicates the branch the transaction took place, eg transact_id 2004 means the transaction took place in branch 002. The column ac_no indicates the account numbers of the customers. The initial number indicates the branch a customer belongs. Eg ac_no 1000, means that customer belongs to branch 001. The next column indicates whether the transaction is a credit (C) or debit (D). The last column, amount shows the amount involve. Now, relax for Questions relating to SQL statements ie Write a query to solve the following questions.</li></ol> <p class="MsoNormal" style="margin-left: 0.5in;">NB: The branch number should have two zeros preceding the number ie 001, 002</p> <ol style="margin-top: 0in;" start="47" type="1"><li class="MsoNormal" style="">What is the total number of colums in the table transact</li><li class="MsoNormal" style="">What is the total credit</li><li class="MsoNormal" style="">What is the total debit</li><li class="MsoNormal" style="">Write a single query to determine the total credit and total debit</li><li class="MsoNormal" style="">What is the balance for the day ie credit – debit</li><li class="MsoNormal" style="">Write a single query to determine total credit and total debit for the nine branches.</li><li class="MsoNormal" style="">Write a single query to determine the balances for the nine branches</li><li class="MsoNormal" style="">How many distinct customers performed transaction for that day</li><li class="MsoNormal" style="">Create table boddy ---refer to query 31</li><li class="MsoNormal" style="">Drop tablespace mama --- refer to query 29</li><li class="MsoNormal" style="">How will be bypass the error you had from step 56. Make sure you perform step 53 successfully.</li><li class="MsoNormal" style="">Drop tablespace papa ---refer to query 30</li><li class="MsoNormal" style="">How will you bypass the error you had from step 58. Make sure you perform step 58 successfully.</li><li class="MsoNormal" style="">Drop tablespace cain ---refer to query 32</li><li class="MsoNormal" style="">How will you bypass the error you had from step 60. Make sure you perform step 60 successfully.</li><li class="MsoNormal" style="">How will you move a datafile from one location onto a different location.</li></ol> ---------------------------------------<br /><br />QUERIES:<br /><br />QUERIES<br />=======<br /><br />1. create user ama identified by ama;<br />2. grant dba to ama;<br />3. create tablespace mama datafile 'c:\mama01.bdf' size 1m;<br />4. create bigfile tablespace papa datafile 'c:\papa.bdf' size 1m;<br />5. create temporary tablespace gimpa tempfile 'c:\gimpa01.dbf' size 1m;<br />6. select tablespace_name,block_size,contents,bigfile from dba_tablespaces;<br />7. col file_name format a15<br /> select file_name,tablespace_name,bytes,blocks from dba_data_files where tablespace_name IN ('MAMA','PAPA','CAIN');<br />8. select file_name,tablespace_name,bytes,blocks from dba_temp_files;<br />9. col property_name format a18<br /> col property_value format a20<br /> col description format a40 <br /> select * from database_properties;<br />10.alter database default tablespace mama;<br /> alter database default temporary tablespace gimpa;<br />11. create table transact<br /> (<br /> transact_id varchar2(5),<br /> ac_no varchar2(10),<br /> cr_debit varchar2(1),<br /> amount number(5)<br /> );<br />12.col segment_name format a12<br /> col tablespace_name format a15<br /> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from dba_segments where owner='AMA';<br />13.select segment_name,segment_type,tablespace_name,bytes,blocks from dba_extents where owner='AMA' ;<br />14.create unique index transact_idx on transact(transact_id);<br />15.create sequence seqq01 minvalue 100 increment by 1;<br /> create sequence seqq02 minvalue 1000 increment by 5;<br /> create sequence seqq03 minvalue 1000 increment by 10; <br />16.col object_name format a20<br /> select object_name,object_type from dba_objects where owner='AMA';<br />17.select * from dba_tablespace_usage_metrics;<br />18. begin<br /> for i in 1..7000<br /> loop<br /> insert into transact values(seqq01.nextval,seqq02.nextval,'C',i+24);<br /> end loop;<br /> commit;<br /> end;<br /> /<br />19.begin<br /> for i in 1..7000<br /> loop<br /> insert into transact values(seqq01.nextval,seqq02.nextval,'D',i+10);<br /> end loop;<br /> commit;<br /> end;<br /> /<br />20.begin<br /> for i in 1..7000<br /> loop<br /> insert into transact values(seqq01.nextval,seqq03.nextval,'C',i+60);<br /> end loop;<br /> commit;<br /> end;<br /> /<br /><br />21.alter tablespace mama add datafile 'c:\mama02.dbf' size 1m;<br /><br />22.begin<br /> for i in 1..6000<br /> loop<br /> insert into transact values(seqq01.nextval,seqq03.nextval,'D',i+50);<br /> end loop;<br /> commit;<br /> end;<br /> /<br /><br />23. select a.segment_name,a.segment_type,a.tablespace_name,b.file_name,a.bytes,a.blocks,a.extent_id<br /> from dba_extents a, dba_data_files b<br /> where a.relative_fno=b.file_id<br /> and owner='AMA';<br /><br />24.create undo tablespace cain datafile 'c:\cain01.dbf' size 1m;<br />25.show parameter undo_tablespace;<br />26.alter system set undo_tablespace='';<br />27.alter system set undo_tablespace='CAIN';<br />28.show parameter spfile;<br />29.DROP TABLESPACE mama INCLUDING CONTENTS;<br />30.DROP TABLESPACE papa;<br />31.create table boddy<br /> (<br /> id varchar2(5),<br /> amount number(5)<br /> ) tablespace papa;<br />32.DROP TABLESPACE cain including contents;<br />33.create table ttb<br /> (<br /> id number(5)<br /> ) ;<br />34.insert into ttb values(5);<br />35.alter tablespace cain add datafile 'c:\cain02.dbf' size 1m;<br />36.alter database datafile 'c:\cain02.dbf' resize 5m;Elvis Boatenghttp://www.blogger.com/profile/13979914091609752904noreply@blogger.com0