Thursday, September 9, 2010

ORACLE DATABASE ADMINISTRATION


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 elvisboateng@yahoo.com

SQL PRACTICE

NOTE: You are to use the hr schema

Let us refresh our minds on what a table is, in terms of columns, rows and field.
before we start with the drill, you should be able to know all the columns a table has by issuing this instruction: desc table_name example desc employees
1. select * from employees;
This displays all the columns and rows in the table employees
2. select last_name, salary from employees;
This will display two columns ie last_name and salary and all their
corresponding rows in the table
3. select last_name, salary+200 from employees;
This will display two columns ie last_name and 200 will be added to all the
values in the salary column giving a new column salary + 200
4. select last_name, 12*(salary+100) from employees;
Kindly describe the result
5. Read on NULL value
select first_name,commission_pct from employees where last_name in ('Hall','Bull');
select first_name,commission_pct*2 from employees where last_name in ('Hall','Bull');
6. select last_name name from employees;
the column name last_name is replaced with name
7. select salary*12 "Annual Salary" from employees;
8. select last_name||' '||job_id AS "Employees" from employees;

select department_name||',it's assigned manager id:'||manager_id as
"department and manager" from departments;

select department_name||q'[,it's assigned manager id: ]' || manager_id as
"department and manager" from departments

9. select department_id from employees;
10. select distinct department_id from employees;
11. describe employees
12. desc employees
13. select employee_id, last_name from employees where department_id=90;
14. select last_name, department_id from employees where last_name='Whalen';
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%';
23. select employee_id, last_name, from employees where salary>10000 OR job_id like '%MAN%';
24. select employee_id, last_name, from employees where job_id NOT IN ('IT_PROG','SA_REP');
25. select last_name, job_id from employees order by hire_date;
26. select last_name, job_id from employees order by hire_date desc;
27. select last_name, job_id from employees order by department_id, salary DESC;
28. select employee_id, last_name from employees where employee_id=&emp_num;
29. select employee_id,last_name, &colum_name from employees where &condition order by &order_column;
30. select lower('SQL Course') from dual;
31. select upper('SQL Course') from dual;
32. select initcap('SQL Course') from dual;
33. select concat('Hello','World') from dual;
34. select substr('HelloWorld',1,5) from dual;
35. select substr(concat('Hello','World'),1,5) from dual;
36. select length('Mother') from dual;
37. select length(substr(concat('Hello','World'),1,5)) from dual;
38. select instr('monkey','k') from dual;
39. select lpad('elvis',10,'¢') from dual;
40. select rpad('elvis',10,'¢') from dual;
41. select replace ('Pencil','n','a') from dual;
42. select trim('H' from 'HelloWorld') from dual;
43. select trim('d' from 'HelloWorld') from dual;
44. select trim('W' from 'HelloWorld') from dual;
45. select trim('World' from 'HelloWorld') from dual;
46. select upper(replace(substr(trim('a' from rpad(lpad('Kenkey',12,'a'),15,'a')),1,3),'K','P')) from dual;
47. select round(45.926,2) from dual;
48. select trunc(45.926,2) from dual;
49. select mod(5,2) from dual;
50. select sysdate from dual;
51. select to_char(hire_date,'dd-mm-yy') from employees;
52. select to_char(hire_date,'day-mm-yy') from employees;
53. select to_char(hire_date,'dy-mm-yy') from employees;
54. select to_char(hire_date,'dd-mon-yy') from employees;
55. select to_char(hire_date,'dd-month-yy') from employees;
56.select to_char(hire_date,'dd-month-yyyy') from employees;
57.select to_char(hire_date,'dd-month-year') from employees;
58.select to_char(hire_date,'month-yyyy') from employees;
59.select to_char(hire_date,'fmmonth-yyyy') from employees;
60.select to_char(hire_date,'month/yyyy') hiredate from employees;
61.select to_char(hire_date,'fmmonthyyyy') from employees;
62.select to_char(salary,'99,999.99') salary from employees;
63.select last_name,commission_pct commission from employees where last_name like 'A%'
64.select last_name,commission_pct+100 commission from employees where last_name like 'A%'
65.select last_name,nvl(commission_pct,0) commission from employees where last_name like 'A%'
66.select last_name,nvl(commission_pct,0)+100 commission from employees where last_name like 'A%'
67. select last_name,commission_pct,nvl2(commission_pct,'Earned Commission','No Commission') from employees where last_name like 'A%'
68.create table test
(
name varchar2(10),
69.salary number,
allowance number
);
70.select * from test;
71.insert into test values('Kofi',120,100);
72.insert into test values('Paul',90,90);
73.insert into test values('Raymoon',60,60);
74.insert into test values('Elvis',110,110);
75.insert into test values('Baby',80,150);
commit;
76.select * from test;
77.select name,salary,allowance, nullif(salary,allowance) from test;
78select name,salary,allowance, nullif(allowance,salary) from test;
79.select last_name,coalesce(commission_pct,1) from employees where last_name like 'A%'
80.select last_name,salary,job_id,
case job_id when 'IT_PROG' THEN 1.1*salary
when 'ST_CLERK' THEN 1.15*salary
when 'SA_REP' THEN 1.2*salary
else salary
end "Revised Salary"
from employees;
81.select last_name,salary,job_id,
decode(job_id,'IT_PROG',1.1*salary,
'ST_CLERK',1.15*salary,
'SA_REP',1.2*salary,
salary) "Revised Salary"
from employees;

82.select avg(salary),max(salary),min(salary),sum(salary),stddev(salary),variance(salary) from employees;
83.select count(*) from employees;
84.select count(commission_pct) from employees;
85.select count(employee_id) from employees;
86.select count(department_id) from employees;
87.select count(distinct department_id) from employees;

Questions

1.Create a report to display the manager number and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000 or less. Sort the output in descending order
of salary.


88. select department_id,department_name,location_id,city
from departments
natural join locations;


89. select department_id,department_name,a.location_id,city
from departments a, locations b
where a.location_id=b.location_id;


90. select employee_id,last_name,department_id,manager_id,department_name
from employees
natural join departments;


91. select employee_id,last_name,a.department_id,a.manager_id,department_name
from employees a, departments b
where a.department_id=b.department_id
and a.manager_id=b.manager_id;


92. select employee_id,last_name,department_id,b.manager_id,department_name
from employees a join departments b
using(department_id);


93. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
using(department_id);


94. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
using(department_id)
where a.department_id=50;


95. select employee_id,last_name,a.department_id,manager_id,department_name
from employees a join departments b
using(manager_id);


96. select employee_id,last_name,department_id,a.manager_id,department_name
from employees a join departments b
on (a.department_id=b.department_id);


97. select employee_id,last_name,a.department_id,a.manager_id,department_name
from employees a join departments b
on (a.department_id=b.department_id);


98. select e.last_name,m.last_name
from employees e join employees m
on (e.manager_id=m.employee_id);


99. select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id);


100. select e.last_name,e.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id=d.department_id);


101. select e.last_name,e.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id);


102. select last_name,department_name
from employees
cross join departments;


103. select last_name
from employees
where salary=
(
select min(salary) from employees
);


104. select last_name,department_id,salary
from employees
where salary =
(select min(salary) from employees group by department_id);


105. select last_name,department_id,salary
from employees
where salary in
(select min(salary) from employees group by department_id);


106. select last_name,department_id,salary
from employees
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'));


107. select name,salary from test
where salary > all(select salary from test where name in('Kofi','Paul'));


108. select last_name,department_id,salary
from employees
where salary < id="1;" id="1;" id="1;">500));

156. insert into box values(1,200);

157. insert into box values(1,500);

158. insert into box values(1,501);

159. create table dept100
as
select employee_id,salary from employees where department_id=100;



160. create table dept101
as
select employee_id,salary*10 from employees where department_id=101;


161. create table dept101
as
select employee_id,salary*10 sal from employees where department_id=101;


162. select * from dept101;

163. create table dept110 (emp, sal)
as
select employee_id,salary*10 from employees where department_id=110;

164. select * from dept110;

165. alter table dept110
add (job_id varchar2(10),hire_date date default sysdate);

166. select * from dept110;

167. desc dept110

168. alter table dept110 modify (emp varchar2(10));

169. alter table dept110 modify (job_id number(10));

170. alter table dept110 modify (job_id varchar2(10));

171. truncate table dept110;

172. alter table dept110 modify (emp varchar2(10));

173. desc dept110

174. select * from dept110;

175. alter table dept110 drop column job_id;

176. select * from dept110;

177. alter table dept110 set unused column sal;

178. select * from dept110;

179. alter table dept110 drop unused columns;

180. select * from user_unused_col_tabs;

181. log in as sys and create table emp

182. create table hr.emp
as select * from scott.emp;

183. create table hr.dept
as select * from scott.dept;

184. Now log in as hr

185. CREATE VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emp
WHERE deptno=20;

186. CREATE force VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emps
WHERE deptno=20;

187. select * from myview;

188. CREATE or replace VIEW myview
AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
FROM emp
WHERE deptno=20;

/* the same as

189. CREATE VIEW myview(EMLOYEE_NO,NAME,SALARY,JOB)
AS SELECT empno,ename,sal,job
FROM emp
WHERE deptno=20;

*/

190. select * from myview;

191. CREATE VIEW v2
AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;


192. CREATE VIEW v2
AS SELECT d.dname, MIN(e.sal) mini, MAX(e.sal) maxi , AVG(e.sal) aver
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

193. select * from v2

COURSE OUTLINE

Oracle Database 10g: Database Administration Workshop

What you will learn:

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.

Course Topics:

Writing SQL SELECT statements

  • Define projection, selection, and join terminology
  • Review the basic SQL SELECT statement syntax
  • Select all columns using a wildcard notation from a table
  • State simple rules and guidelines for writing SQL statements
  • Write a query containing the arithmetic operators
  • Use aliases to customize column headings
  • Create a character expression with the concatenation operator
  • Use the quote operator q to control text strings containing the quote character

Restricting Data

  • Limit Rows Using a Selection
  • Use the WHERE Clause
  • List the main Comparison Conditions
  • Use the LIKE Condition to Compare Literal Values
  • List the Logical Conditions AND, OR, NOT
  • Use Multiple Conditions in the WHERE clause
  • Describe the Rules of Precedence

Sorting Data

  • Sort Rows with the ORDER BY Clause
  • Use the && Substitution Variable
  • Use the iSQL*Plus DEFINE Command
  • Use the VERIFY Command

SQL Functions

  • Differentiate between Single Row and Multiple Row SQL Functions
  • Categorize the Character Functions into Case Manipulation and Character Manipulation types
  • Explain the Numeric Functions ROUND, TRUNC, and MOD
  • List the Rules for Applying the Arithmetic Operators on Dates
  • Use the Arithmetic Operators with Dates in the SELECT Clause
  • Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
  • Explain Implicit and Explicit conversion
  • Nest Functions to Perform Multiple Tasks in One Statement

Conditional Expressions

  • Use the CASE Expression
  • Explain the DECODE Expression

Aggregating Data Using Group Functions

  • Categorize the Types of Group Functions
  • Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
  • Utilize the DISTINCT Keyword with the Group Functions
  • Describe how Nulls are handled with the Group Functions
  • Create Groups of Data with the GROUP BY Clause
  • Group Data by more than one column
  • Avoid Illegal Queries with the Group Functions
  • Exclude Groups of Data with the HAVING Clause

Displaying Data from Multiple Tables

  • Identify Types of Joins
  • Retrieve Records with Natural Joins
  • Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
  • Create a Join with the USING clause to identify specific columns between tables
  • Use the ON clause to specify arbitrary conditions or specify columns to Join
  • Create a Three-way join with the ON clause to retrieve information from 3 tables
  • List the Types of Outer Joins LEFT, RIGHT, and FULL
  • Generating a Cartesian Product

Using Subqueries

  • Use a sub query to solve a problem
  • Identify where sub queries can be placed in a SELECT statement
  • Describe the types of sub queries (single row, multiple row)
  • Show the single row sub query operators
  • Use the group functions in a sub query
  • Identify illegal statements with sub queries
  • Show the multiple row sub query operators
  • Explain how null values are handled in sub queries

Using the Set Operators

  • Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
  • Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
  • Describe the INTERSECT operator
  • Use the INTERSECT operator
  • Explain the MINUS operator
  • Use the MINUS operator
  • List the SET operator guidelines
  • Order results when using the UNION operator

Inserting and Updating Data

  • Write INSERT statements to add rows to a table
  • Insert Special Values
  • Copy Rows from Another Table
  • Update Rows in a Table

Deleting Data

  • Use DELETE statements to remove rows from a table
  • Delete Rows Based on Another Table
  • Describe the TRUNCATE Statement

Database Transactions

  • Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
  • Show how Read Consistency works

Using DDL Statements

  • List the main database objects
  • Identify the Naming Rules
  • Display the basic Syntax for Creating a Table
  • Show the DEFAULT option
  • List the Data Types that are available for Columns

Managing Tables

  • Explain the different types of constraints
  • Show resulting exceptions when constraints are violated with DML statements
  • Create a table with a sub query
  • Describe the ALTER TABLE functionality
  • Remove a table with the DROP statement

Creating Other Schema Objects

  • Categorize simple and complex views and compare them
  • Create a view
  • Retrieve data from a view
  • Explain a read-only view
  • List the rules for performing DML on complex views
  • Create a sequence
  • List the basic rules for when to create and not create an index
  • Create a synonym

Managing Objects with Data Dictionary Views

  • Describe the structure of each of the dictionary views
  • List the purpose of each of the dictionary views
  • Write queries that retrieve information from the dictionary views on the schema objects
  • Use the COMMENT command to document objects


Introduction (Database Architecture)

Describe course objectives
Explore the Oracle 10g database architecture

Installing the Oracle Database Software on UNIX

Explain core DBA tasks and tools
Plan an Oracle installation
Use optimal flexible architecture
Install software with the Oracle Universal Installer (OUI)

Creating an Oracle Database

Create a database with the Database Configuration Assistant (DBCA)
Create a database design template with the DBCA
Generate database creation scripts with the DBCA

Managing the Oracle Instance

Start and stop the Oracle database and components
Use Enterprise Manager (EM)
Access a database with SQL*Plus and iSQL*Plus
Modify database initialization parameters
Understand the stages of database startup
View the Alert log
Use the Data Dictionary

Managing Database Storage Structures

Describe table data storage (in blocks)
Define the purpose of tablespaces and data files
Understand and utilize Oracle Managed Files (OMF)
Create and manage tablespaces
Obtain tablespace information

Administering User Security

Create and manage database user accounts
Authenticate users
Assign default storage areas (tablespaces)
Grant and revoke privileges
Create and manage roles
Create and manage profiles

Configuring the Oracle Network Environment

Use Enterprise Manager for configuring the Oracle network environment
Create additional listeners
Create Net Service aliases
Configure connect-time failover
Control the Oracle Net Listener
Test Oracle Net connectivity
Identify when to use shared versus dedicated servers

Automatic Performance Management

The Use of 10g Database Advisors
Identifying Tunable Components
Advisory Framework
Using the SQL Tuning and SQL Access Advisor
Manage the Automatic Workload Repository (AWR)
Use the Automatic Database Diagnostic Monitor (ADDM)
Describe advisory framework

Using AWR
Create and manage AWR snapshots
Generate AWR reports
Create snapshot sets and compare periods
Generate ADDM reports
Generate ASH reports

Backup and Recovery Concepts
Identify the types of failure that may occur in an Oracle Database
Describe ways to tune instance recovery
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARCHIVELOG mode

Performing Database Backups

Create consistent database backups
Back your database up without shutting it down
Create incremental backups
Automate database backups
Monitor the flash recovery area

Performing Database Recovery

Recover from loss of a control file
Recover from loss of a redo log file
Perform complete recovery following the loss of a data file


Configuring Recovery Manager
Recovery Manager Features and Components
Using a Flash Recovery Area with RMAN
Configuring RMAN
Control File Autobackups
Retention Policies and Channel Allocation
Using Recovery Manager to connect to a target database in default NOCATALOG mode
Displaying the current RMAN configuration settings
Altering the backup retention policy for a database

Using Recovery Manager

RMAN Command Overview
Parallelization of Backup Sets
Compressed Backups
Image Copy
Whole Database and Incremental Backups
LIST and REPORT commands
Enable ARCHIVELOG mode for the database
Use Recovery Manager

Recovering from Non-critical Losses

Recovery of Non-Critical Files
Creating New Temporary Tablespace
Recreating Redo Log Files, Index Tablespaces, and Indexes
Read-Only Tablespace Recovery
Authentication Methods for Database Administrators
Loss of Password Authentication File
Creating a new temporary tablespace
Altering the default temporary tablespace for a database

Incomplete Recovery

Recovery Steps
Server and User Managed Recovery commands
Recovering a Control File Autobackup
Creating a New Control File
Incomplete Recovery Overview
Incomplete Recovery Best Practices
Simplified Recovery Through RESETLOGS
Point-in-time recovery using RMAN

Flashback
Flashback Database Architecture
Configuring and Monitoring Flashback Database
Backing Up the Flash Recovery Area
Flashback Database Considerations
Using Flashback Database EM Interface
Managing and monitoring Flashback Database operations