Atchaya's Check Your Oracle Knowledge

21.Oracle - The self documenting dictionary

noreply@blogger.com (atchaya) — Tue, 22 Dec 2009 07:16:00 +0000

Oracle - The self documenting dictionary

Oracle's Data dictionary is itself self documenting. we can query DICTIONARY AND DICT_COLUMNS views for descriptions of the data dictionary views and their columns.

The following query gives the descriptions of all of the data dictionary views.

SELECT table_name, comments
FROM dictionary
ORDER BY table_name;

It gives the large amount of output. By using where class we can focus a smaller set of views as like the following query.

SELECT table_name, comments
FROM dictionary
WHERE table_name LIKE '%TABLE%'
ORDER BY table_name
It returns all views containing the word 'TABLE'

Like wise We can query the DICT_COLUMNS view also. It gives the descriptions for the columns for all views. Following query retrieves descriptions for the columns in ALL_TAB_COLUMNS.

SELECT column_name, comments
FROM dict_columns
WHERE table_name = 'ALL_TAB_COLUMNS';
This is a sample one. We can learn lot about oracle through data dictionary views, they are not available at any oracle site.

Thanks..!

20. TRIM Function

noreply@blogger.com (atchaya) — Thu, 17 Dec 2009 18:15:00 +0000

Which SELECT statement will the result 'elloworld' from the string 'HelloWorld'?

A. SELECT SUBSTR( 'HelloWorld',1) FROM dual;
B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;
C. SELECT LOWER(SUBSTR('HellowWorld', 1, 1) FROM dual;
D. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual;
E. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;

Answer: E

Explanation:
TRIM function accept a string describing the data you would like to trim from a column value. It can trim from both side of column value i.e. left and right. In the following statement this functionwill trim as
SELECT LOWER(TRIM ('+' FROM 'HelloWorld')) FROM dual;

From the above statement trim function will remove the character 'H' from 'HelloWorld' and LOWER function will convert the remaining character to lower case.

19. Oracle Processes

noreply@blogger.com (atchaya) — Wed, 16 Dec 2009 18:13:00 +0000

Processes

Oracle uses many small (focused) processes to manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology.

Some of these processes include:

PMON - Process Monitor

SMON - System Monitor

ARCn - Redo Log Archiver

LGWR - Redo Log Writer

DBWn - Database Writer

CKPT - Checkpoint process

RECO - Recoverer

CJQn - Job Queue Coordinator

QMNn - Queue-monitor processes

Dnnn - Dispatcher Processes (multiplex server-processes on behalf of users)

Snnn - Shared server processes (serve client-requests)

MMAN - Internal process (used for internal database tasks)

LSP0 - Logical standby coordinator process (controls Data Guard log-application)

MRP - Media-recovery process (detached recovery-server process)

MMON - Memory-monitor process

MMNL - Memory monitor light (gathers and stores AWR statistics)

PSP0 - Process-spawner (spawns Oracle processes)

RFS - Remote file server process (archive to a remote site)

DBRM - DB resource manager (new in 11g)

DIAGn - Diagnosability process (new in 11g)

FBDA - Flashback data archiver process (new in 11g)

VKTM - Virtual Timekeeper (new in 11g)

Wnnn - Space Management Co-ordination process (new in 11g)

SMCn - Space Manager process (new in 11g)

An instance can mount and open one and only one database.
A database can normally only be mounted and opened by one instance. However, when using Real Application Clusters (RAC) a database can be mounted and opened by many instances.

18. ADDING CHECK CONSTRAINT WHILE INSERTING.

noreply@blogger.com (atchaya) — Sun, 22 Nov 2009 10:23:00 +0000

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER

You want to create a SQL script file that contains an INSERT statement.
When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table.
The INSERT statement should pass values to the table columns as specified below:

EMPLOYEE_ID: Next value from the sequence

EMP_ID_SEQEMP_NAME and JOB_ID: As specified by the user during run time, through substitution variables

SAL: 2000

MGR_ID: No value

DEPARTMENT_ID: Supplied by the user during run time throughsubstitutionvariable.

The INSERT statement should fail if the user supplies a value other than20 or 50.

Which INSERT statement meets the above requirements?

A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL,&did);

B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL,&did IN (20,50));

C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES(emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECKOPTION) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename','&jobid', 2000, NULL, &did);

Answer: D

Question 17:How to get the 'n' th maximum value of a column?

noreply@blogger.com (atchaya) — Sun, 27 Sep 2009 18:50:00 +0000

We can get the nth maximum value through query by using the combination of order by clause and rownum pseudo column.

The Query following.

SELECT MIN(columnname)
FROM ( SELECT coumnname FROM tablename ORDER BY columnname DESC)
WHERE rownum <= n

Question 16.Inserting more than 1 row/table with single query.

noreply@blogger.com (atchaya) — Mon, 31 Aug 2009 12:38:00 +0000

Can we insert more than 1 row with 1 qyery in same table/diff tables?

Yes, we insert more than 1 row with 1 qyery by using the following method?

syntax
INSERT ALL
INTO tablename1 (fieldlist) VALUES (valuelist)
INTO tablename2 (fieldlist) VALUES (valuelist)
INTO tablename3 (fieldlist) VALUES (valuelist)
SELECT * FROM DUAL;

Example:

insert all into student (sno, name) values (1, 'Raja')
into student (sno, name) values (2 'Ravi')
into student (sno, name) values (3, 'Balu')
select * from dual;

We can use more than 1 table also.
insert all into student (sno, name) values (1, 'Raja')
into student (sno, name) values (2 'Ravi')
into emp (eno, name) values (1, 'Mani')
select * from dual;

Interview quesitions and answers, objective type questions with answers : http://atchaya.com/ora/orahome.html
Oracle forum : http://atchaya.wikidot.com/forum/c-91091/discussion-about-oracle

Question 15

noreply@blogger.com (atchaya) — Mon, 10 Aug 2009 08:35:00 +0000

Write a PL/SQL Program for the following output
--*--
-***-
*****
-***-
--*--
Answer

The output based on the value of 'n'
declare n number(2) default 20;
i number(2);
j number(2);
h number(2);
s number(2);
b varchar2(1) default 'I';
begin
s := 1;
if n mod 2 = 0 then
n := n+1;
end if;
h := n/2 -1;
for i in 1..n loop
for j in 1..h loop
dbms_output.put('-');
end loop;
for j in 1..s loop
dbms_output.put('*');
end loop;
for j in 1..h loop
dbms_output.put('-');
end loop;
dbms_output.put_line(' ');
if n = s then
b := 'D';
end if;
IF b='I' then
s := s+2;
h := h-1;
else
s := s-2;
h := h+1;
end if;
end loop;
end;

Question: 14: Drop Table - Dependency

noreply@blogger.com (atchaya) — Mon, 10 Aug 2009 01:03:00 +0000

Evaluate the SQL statement
DROP TABLE DEPT

Which four statements are true of the SQL statement?
A. You cannot roll back this statement.
B. All pending transactions are committed.
C. Al views based on the DEPT table are deleted.
D. All indexes based on the DEPT table are dropped.
E. All data in the table is deleted, and the table structure is also deleted.
F.All data in the table is deleted, but the structure of the table is retained.
G.All synonyms based on the DEPT table are deleted.

Answer:
A, B, D and E

Explanation:
A. You cannot roll back DROP TABLE statement.

B. All pending transactions related on this table arecommitted.

D. If the table is dropped, Oracle automatically drops any index, trigger and constraintassociated with the table as well.

E. All data in the table is deleted, and the table structure is also deleted.

Incorrect Answers
C: All views based on the DEPT table become invalid, but they are not deleted.

F: All data in the table is deleted, and the table structure is also deleted. Command TRUNCATE deletes all data in the table, but does not delete the structure of the table.

G: All synonyms based on the DEPT table are not deleted after dropping the table.

Note: Go to lot of interview quesitions and answers, objective type questions with answers click http://atchaya.com/ora/orahome.html Goto Oracle forum click http://atchaya.wikidot.com/forum/c-91091/discussion-about-oracle

Question 13: DDL and Commit

noreply@blogger.com (atchaya) — Sat, 08 Aug 2009 20:04:00 +0000

I have updated some tables and before commit if I will give one DDL command means what will happen?

For example
CREATE TABLE test(fldname number(10));
insert into test (fldname) values (10);
insert into test (fldname) values (20);

After that without giving Commit statement, I issued one DDL command
CREATE TABLE test1( sno number(10))
What aobut the previous inserts? Is it committed or not? explain.

Answer:
I checked this and found the following 3 points.

Point No. 1:
If we give a DDL command with syntax error means it will not commit and gives the error. For example the following statement

CREATE TABLE test(fldname number(10), );

note that the comma, gives "invalid identifier.. " error and without committing the INSERT statements.

Point No. 2:
If the given DDL command executes without any error means it will auto commit the INSERT statements.

Point No. 3:

If we give a DDL command without any syntax error and that statement not execute also auto commit the INSERT statements and gives the exception of thee DDL statement. For example the following statement

CREATE TABLE test(fldname number(10) );

gives "name is already used by an existing object" exception after auto commiting the insert statements.

Question 12: What is PRAGMA AUTONOMOUS_TRANSACTION?

noreply@blogger.com (atchaya) — Thu, 06 Aug 2009 11:20:00 +0000

You are running a pl/sql block or procedure called parent

In that procedure you called another one procedure namely child and that procedure contains commit statement.

After calling the child procedure the transactions done in parent procedure are also committed due to the child procedure commit statement.

But you want to rollback or commit the parent procedure whole transactions based on your requirement after the calling procedure child.

In this situation, you should add the "pragma autonomus transaction" in child procedure.
If you add this statement then it acts as a separate. If you use commit in child procedure it will not affect the parent procedure.

Example :
CREATE OR REPLACE PROCEDURE parent()
AS
BEGIN
UPDATE tab1 SET fieldname = value WHERE condition;
UPDATE tab2 SET fieldname = value WHERE condition;
child();
UPDATE tab4 SET fieldname = value WHERE condition;
IF parentcondition
COMMIT;
ELSE
ROLLBACK;
END IF;
END.

CREATE OR REPLACE PROCEDURE child()
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE tab3 SET fieldname = value WHERE condition;
COMMIT;
END.

In above case tab1, tab2 and tab4 tables transactions will commit or rollback based on the parentcondition. but tab3 updation will always commits, that is not consider the parent procedure.

lot of interview quesitions and answers, objective type questions with answers also available. To see click http://atchaya.com/ora/orahome.html

Question 11: Oracle Tables Last Modifying Time

noreply@blogger.com (atchaya) — Sat, 01 Aug 2009 19:21:00 +0000

Suppose Database D1 contains 100 tables..
How 2 select the tables which are modified at 10am today?

1. SELECT table_name, table_owner FROM all_tab_modifications
WHERE to_char(timestamp,'YYYYMMDDHH24MM') = to_char(sysdate,'YYYYMMDD')'1000'
This will gives the table names which are modified (insert, update, delete, truncated) at 10 AM.

2. SELECT scn_to_timestamp(max(ora_rowscn))
FROM ;

Question 10. Inserting Rows

noreply@blogger.com (atchaya) — Thu, 30 Jul 2009 00:21:00 +0000

How many number of rows, I can insert into a table simultaenously?

Answer :

Method 1:

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table.

Example:

INSERT INTO tablename1

SELECT fieldnamelist FROM tablename WHERE condition

Question 9: null in where clause

noreply@blogger.com (atchaya) — Wed, 10 Jun 2009 21:07:00 +0000

The EMP table contains these columns:

LAST NAME VARCHAR2(25)
SALARY NUMBER(6,2)
DEPARTMENT_ID NUMBER(6)

You need to display the employees who have not been assigned to any department.
You write the SELECT statement:

SELECT LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMP WHERE DEPARTMENT_ID = NULL;

What is true about this SQL statement?

A. The SQL statement displays the desired results.

B. The column in the WHERE clause should be changed to display the desired results.

C. The operator in the WHERE clause should be changed to display the desired results.

D. The WHERE clause should be changed to use an outer join to display the desired results.

Answer: C

Explanation:
The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL.
Oracle provides this functionality with a special function, called NVL().
You can use operation equal with the keywords IS NULL, or you can achieve desired results using NVL() function after the WHERE clause.

Incorrect Answers

A: The SQL statement will generate an error because you cannot use operation equal with NULL.

B: The column in the WHERE clause should not be changed to display the desired results.

D: Since there is only one table used in this query you don't need to use outer join to display the desired results.

Question 8: Aggregate Functions

noreply@blogger.com (atchaya) — Tue, 09 Jun 2009 18:19:00 +0000

Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHARD2(10)
START_DATE DATE
END_DATE DATE

Which two aggregate functions are valid on the START_DATE column? (Choose two)

A. SUM(start_date)

B. AVG(start_date)

C. COUNT(start_date)

D. AVG(start_date, end_date)

E. MIN(start_date)

F. MAXIMUM(start_date)

Answer: C & E

Explanation:
It is possible to apply COUNT() and MIN() functions on the column with DATE data type.

Incorrect Answers
A: Function SUM() cannot be used with DATE data type column.

B: Function AVG() cannot be used with DATE data type column.

D: Function AVG() cannot be used with DATE data type column, and function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement.

F: There is no MAXIMUM() function in Oracle, only MAX() function exists.

Question 7: Group By

noreply@blogger.com (atchaya) — Tue, 09 Jun 2009 18:12:00 +0000

Examine the description of the EMPLOYEES table:

EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHARD2(30)
SALARY NUMBER(8,2)

Which statement shows the maximum salary paid in each job category of each department?

A.SELECT dept_id, job_cat, MAX(salary) FROM employees
WHERE salary > MAX(salary);

B.SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id, job_cat;

C. SELECT dept_id, job_cat, MAX(salary) FROM employees;

D. SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id;

Answer: B

Explanation:
This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.

Incorrect Answers

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.

C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.

D: The GROUP BY clause is missing JOB_ID column.

E: You don't need to group results of query by SALARY in the GROUP BY column.

Question 6: ORDER BY with NULL

noreply@blogger.com (atchaya) — Mon, 08 Jun 2009 18:31:00 +0000

You are sorting data in a table in you SELECT statement in descending order.
The column you are sorting on contains NULL records, where will the NULL record appears?

A.At the beginning of the list.

B.At the end of the list.

C.In the middle of the list.

D.At the same location they are listed in the unordered table.

Answer: A

Explanation:
When sorting a column with null values in ascending order then the oracle places the Null values
at the end of the list if the sorting is in descending order the oracle places the null values at the
start of the list.

Question 5: Where and having clauses

noreply@blogger.com (atchaya) — Mon, 08 Jun 2009 18:25:00 +0000

Which two statements are true about WHERE and HAVING clauses? (Choose two)

A. A WHERE clause can be used to restrict both rows and groups.

B. A WHERE clause can be used to restrict rows only.

C. A HAVING clause can be used to restrict both rows and groups.

D. A HAVING clause can be used to restrict groups only.

Answer: B, C

Explanation :

HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information.

The Oracle server performs the following steps when youuse the Having clause

1. rows are grouped

2. the group function is applied to the group

3. the group that match the criteria in the Having clause are displayed.

WHERE clause cannot be use to restrict groups

HAVING clause use to restrict groups

WHERE clause cannot be use when there is group functions.

Incorrect Answers :

A. Where clause cannot be use to restrict groups

D. When HAVING clause is use rows are grouped as well.

Question: 4 - WHERE clause

noreply@blogger.com (atchaya) — Sat, 06 Jun 2009 14:10:00 +0000

The ORDERS table has these columns:

ORDER_ID NUMBER(4) NOT NULL
CUSTOMER_ID NUMBER(12) NOT NULL
ORDER_TOTAL NUMBER(10,2)

The ORDERS table tracks the Order number, the order total, and the customer to whom the Order belongs.

Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 2000.00 dollars?

A.SELECT customer_id, order_id, order_total
FROM orders
RANGE ON order_total (100 AND 2000) INCLUSIVE;

B.SELECT customer_id, order_id, order_total
FROM orders
HAVING order_total BETWEEN 100 and 2000;

C. SELECT customer_id, order_id, order_total
FROM orders
WHERE order_total BETWEEN 100 and 2000;

D. SELECT customer_id, order_id, order_total
FROM orders
WHERE order_total >= 100 and <= 2000;

Answer:C

Explanation:

Answers C provides correct results to show. You can use BETWEEN or comparison operations to retrieve data.

Incorrect Answers

A: There is no RANGE ON or INCLUSIVE keyword in Oracle.
B: HAVING clause can be use only in conjunction with the GROUP BY clause.
D: Syntax 'order_total >= 100 and <= 2000' is incorrect.

noreply@blogger.com (atchaya) — Sat, 06 Jun 2009 14:10:00 +0000

The ORDERS table has these columns:

ORDER_ID NUMBER(4) NOT NULL
CUSTOMER_ID NUMBER(12) NOT NULL
ORDER_TOTAL NUMBER(10,2)

The ORDERS table tracks the Order number, the order total, and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between
100.00 and 2000.00 dollars?

A.SELECT customer_id, order_id, order_total
FROM orders
RANGE ON order_total (100 AND 2000) INCLUSIVE;

B.SELECT customer_id, order_id, order_total
FROM orders
HAVING order_total BETWEEN 100 and 2000;

C. SELECT customer_id, order_id, order_total
FROM orders
WHERE order_total BETWEEN 100 and 2000;

D. SELECT customer_id, order_id, order_total
FROM orders
WHERE order_total >= 100 and <= 2000;

Answer:C
Explanation:
Answers C provides correct results to show. You can use BETWEEN or comparison
operations to retrieve data.

Incorrect Answers
A: There is no RANGE ON or INCLUSIVE keyword in Oracle.
B: HAVING clause can be use only in conjunction with the GROUP BY clause.
D: Syntax 'order_total >= 100 and <= 2000' is incorrect.

Order By clause

noreply@blogger.com (atchaya) — Fri, 05 Jun 2009 17:10:00 +0000

The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)

The registrar has requested a report listing the students grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date.

Which statement accomplishes this?

A.SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end DESC, gpa DESC;

B.SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end ASC, gpa ASC;

C. SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end, gpa DESC;

D. SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY gpa DESC, semester_end DESC;

Answer:C

Explanation:This answer shows correct syntax and semantics to receive desired result.

Incorrect Answers
A: Semesters will be sorted started from the oldest date, not the earliest.
B: GPA data will be sorted in ascending order, what is opposite to our task.
D: Semesters will be sorted started from the oldest date, not the earliest. Only difference with answer A is order of columns in the ORDER BY clause.

noreply@blogger.com (atchaya) — Thu, 04 Jun 2009 17:54:00 +0000

Question: 2.

You want to display the titles of books that meet these criteria:

1. Purchased before January 21, 20012.

Price is less then $500 or greater than $900

You want to sort the results by their data of purchase, starting with the most recently boughtbook.

Which statement should you use?

A. SELECT book_title FROM books

WHERE price between 500 and 900

AND purchase_date < '21-JAN-2001'

ORDER BY purchase_date;

B. SELECT book_titleFROM books

WHERE price IN (500,900)

AND purchase_date < '21-JAN-2001'

ORDER BY purchase date ASC;
C. SELECT book_titleFROM books

WHERE price <> 900

AND purchase_date < '21-JAN-2001'

ORDER BY purchase date DESC;

D. SELECT book_titleFROM books

WHERE (price <> 900)

AND purchase_date < '21-JAN-2001'

ORDER BY purchase date DESC;

Answer: D
Explanation:

This statement provides required results.

Incorrect Answers:

A: This query will show books with price in range $500 and $900, not less then $500 or greater than $900.

B: This query will show books with prices exactly $500 or $900, not less then $500 or greaterthan $900.

C: This order will not show correct rows because of incorrect syntax in the WHERE clause.

noreply@blogger.com (atchaya) — Thu, 04 Jun 2009 17:40:00 +0000

Question 1.
You need to display the last names of those employees who have the letter "A" as the second character in their names.
Which SQL statement displays the required results?

A. SELECT last_nameFROM EMPWHERE last_name LIKE '_A%';
B. SELECT last_nameFROM EMPWHERE last name ='*A%'
C. SELECT last_nameFROM EMPWHERE last name ='_A%';
D. SELECT last_nameFROM EMPWHERE last name LIKE '*A%'

Answer: A
Explanation:
Statement in this answer will show correct results because usage of operator LIKE and format mask '_A%' extract the last names of those employees who have the letter 'A' as the second character in their names. Symbol '_' in format mask substitute exactly one symbol and cannot be NULL.
Incorrect Answers
B: This statement will return only names starting from symbol '*'. It cannot be used as substitution symbol.
C: Usage of equity operator here is not appropriate in this case: query will look exact for first symbol '_', it will not be considered as substitution symbol.
D: This statement will return only names starting from symbol ' *'. It cannot be used as substitution symbol.