Thursday, February 25, 2010

Which situation requires a before update statement level trigger on the table?

Which situation requires a before update statement level trigger on the table?


A. When you need to populate values of each updated row into another table.
B. When a trigger must fire for each row affected by the triggering statement.
C. When you need to make sure that user making modifications to the table as necessary privileges.
D. When you need to store the information of the use who successfully modified tables and in audit table.







ANSWER : C

Examine this function.

Examine this function.

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID IN PLAYER_BAT_STAT. PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT HITS/AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID=V_ID;
RETURN(V_AVG);
END;


Which statement will successfully invoke this function in SQL Plus?
A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
B. EXECUTE CALC_PLAYER_AVG(31);
C. CALC_PLAYER('RUTH'):
D. CALC_PLAYER_AVG(31);
E. START CALC_PLAYER_AVG(31)



Answer: B

Which system privileges must you have to manually recompile a stored procedure owned by another application developer?


A. ALTER PROCEDURE
B. ALTER ANY PROCEDURE
C. ALTER ALL PROCEDURE
D. COMPILE ANY PROCEDURE





Answer: B

Wednesday, February 24, 2010

You want to send a message to another session connected to the same instance. Which Oracle supplied package will you use to achieve this task?


A. DBMS_JOB
B. DBMS_PIPES
C. DBMS_OUTPUT
D. DBMS_MESSAGE
E. SEND MESSAGE




Answer: B

Which part of a database trigger determines the number of times the trigger body executes?

Which part of a database trigger determines the number of times the trigger body executes?

A. TRIGGER TYPE
B. TRIGGER BODY
C. TRIGGER EVENT
D. TRIGGER TIMING



Answer: A

When creating procedures, local variables should be placed after which key words?

When creating procedures, local variables should be placed after which key words?


A. IS
B. BEGIN
C. DECLARED
D. PROCEDURE


Answer : A

There is a customer table in the schema that has a public synonym and you are granted all object privileges on it. You have a process_customer that pr

There is a customer table in the schema that has a public synonym and you are granted all object privileges on it. You have a process_customer that processes customer information that is in the public synonym customer table. You have just created a new table called customer within your schema. Which statement is true?


A. Creating the table has no effect and procedure process_custoiner still access data from public synonym customer table.
B. If the structure of your customer table is the same as the public synonym customer table then the procedure process_customer is invalidated and gives compilation errors.
C. The structure of your customer table is entirely different from the public synonym customer table, then the procedure process_customer successfully recompiles and accesses your customer table.
D. If the structure of your customer table is the same as the public synonym customer table then the procedure process_custoiner successfully recompiles when invoked and accesses your customer table.



Answer: D

You are about to change the arguments of the CALC_TEAM_AVG function. Which table can you query to determine the names of the procedures and functions?

You are about to change the arguments of the CALC_TEAM_AVG function. Which table can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?


A. USER_PROC_DEPENDS
B. USER_DEPENDENCIES
C. USER_REFERENCES
D. USER SOURCE




Answer: B

When creating a function in a SQL Plus You receive this message "Warning: function created with compilation errors". Which command can you issue ?

When creating a function in a SQL Plus You receive this message "Warning: function created with compilation errors". Which command can you issue to see the actual error message?


A. SHOW FUNCTION_ERRORS
B. SHOW USER_ERRORS
C. SHOW ERRORS
D. SHOW ALL ERRORS



Answer: C

Examine this trigger.

CREATE OR REPLACE TRIGGER I PD_TEAM_SALARY
AFTER INSERT OR UPDATE OR DELETE ON PLAYER
FOR EACH ROW
BEGIN
UPDATE TEAM
SET TOT_SALARY=TOT_SALARY+:NEW SALARY.
WHERE ID=:NEW:TEAM_ID;


You will be adding additional coat later but for now you want the current block to fire when updated the salary column. Which solution should you use to verify that the user is performing an update on the salary column?


A. ROWUPDATEC SALARY')
B. UPDATING('SALARY')
C. CHANGING('SALARY')
D. COLUMN_UPDATE(' SALARY')



Answer: B

You need to drop a table from within a stored procedure. How do you implement this?


A. You cannot drop a table from a stored procedure.
B. Use the DROP command in the procedure to drop the table.
C. Use the DBMS_DDL packaged routines in the procedure to drop the table.
D. Use the DBMS_SQL packaged routines in the procedure to drop the table.
E. Use the DBMS_DROP packaged routines in the procedure to drop the table.




Answer: D

Which Oracle supply package allows you to run jobs at use defined times?


A. DBMS_JOB
B. DBMS_RUN
C. DBMS_PIPE
D. DBMS_SQL



Answer: A

What is the name of the private procedure in the package?


A. CALCJTAX
B. INSERT_EMP
C. UPDATE_SAL
D. DELETE_EMP
E. UPDATE_EMP
F. MANAGE EMP


Answer: C

code successfully calculates tax?

A. CREATE OR REPLACE PROCEDURE calc (p_no IN NUMBER)
RETURN tax IS
V_sal NUMBER:
Tax NUMBER;
BEGIN
SELECT sal rNTO v_sal
FROM emp
WHERE EMPNO=p_no;
Tax:=v_sal * 0. 05;
END;


B. CREATE OR REPLACE FUNCTION calctax(p_no NUMBER)
RETURN NUMBER IS
V_sal NUMBER:
BEGIN
SELECT sal rNTO v_sal
FROM emp
WHERE empno =p_no;
RETURN(v_sal* 0. 05);
END;


C. CRETAE OR REPLACE FUNCTION calctax(p_no NUMBER) RETURN NUMBER IS
V_sal NUMBER:
Tax NUMBER;
BEGIN
SELECT sal INTO v_sal
FROM emp
WHERE empno =p_no;
Tax:=v_sal * 0. 05;
END:


D. CREATE OR REPLACE FUNCTION calctax(p_no NUMBER)IS V_sal NUMBER:
Tax NUMBER;
BEGIN
SELECT sal rNTO v_sal
FROM emp
WHERE empno =p_no;
Tax :=v_sal * 0. 05;
RETURN(tax);
END:



Answer: B

Under which situation do you create a server side procedure?

A. When the procedure contains no SQL statements.
B. When the procedure contains no PL SQL commands.
C. When the procedure needs to be used by many client applications accessing several remote databases.
D. When the procedure needs to be used by many users accessing the same schema objects on a local database.


Answer: D

Under which situation do you create a server side procedure?

A. When the procedure contains no SQL statements.
B. When the procedure contains no PL SQL commands.
C. When the procedure needs to be used by many client applications accessing several remote databases.
D. When the procedure needs to be used by many users accessing the same schema objects on a local database.



Answer: D

You need to create a stored procedure, which deletes rows from a table. The name of the table from which the rows are to be deleted is unknown until r

You need to create a stored procedure, which deletes rows from a table. The name of the table from which the rows are to be deleted is unknown until run time. Which method do you implement while creating such a procedure?


A. Use SQL command delete in the procedure to delete the rows.
B. Use DBMS_SQL packaged routines in the procedure to delete the rows.
C. Use DBMS_DML packaged routines in the procedure to delete the rows.
D. Use DBMSDELETE packaged routines in the procedure to delete the rows.
E. You cannot have a delete statement without providing a table name before compile time.



Answer: B

In order for you to create run a package \IAINTAIN_DATA which privilege do you need?

A. EXECUTE privilege on the MAINTAIN_DATA package.
B. INVOKE privilege on the MAINTAINJ3ATA package.
C. EXECUTE privilege on the program units in the MAINTAINED ATA package.
D. Object privilege on all of the objects that the MAINTAIN_DATA package is accessing.
E. Execute privilege on the program units inside the MAINTAIN_DATA package and execute privilege on the MAINTAIN_DATA package.






Answer: A

You need to recompile several program units you have recently modified through a PL/SQL program. Which statement is true?

A. You cannot recompile program units using a PL SQL program.
B. You can use the DBMS_DDL. REOMPELE package procedure to recompile the program units.
C. You can use the DBMS_ALTER. COMPILE packaged procedure to recompile the program units.
D. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program units.
E. You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to recompile the program units.




Answer: D

Which two tables are fused track object dependencies? (Choose two)

A. USERJDEPENDENSES.
B. USERJDEPTREE.
C. IDEPTREE.
D. USER_DEPTREE.
E. USER DEPENDS.




Answer: A, C

You need to remove database trigger BUSINESS_RULE. Which command do you use to remove the trigger in the SQL*Plus environment?

A. DROP TRIGGER business rale;
B. DELETE TRIGGER businessrale;
C. REMOVE TRIGGER business_rale;
D. ALTER TRIGGER business rale;
E. DELETE FROM USERJRIGGER
F. WHERE TRIGGER_NAME= 'BUSINESS_RULE':


Answer: A

When creating store procedures and functions which construct allows yon to transfer values to and from the calling environment?

A. Local variables.
B. Formal arguments.
C. Boolean variables.
D. Substitution variables.
Answer: B

Why do stored procedures and functions improve performance? (Chose two)

  • They reduce network round trips.
  • They postpone PL SQL parsing until run tune.
  • They allow the application to perform high speed processing locally.
  • Thev reduce the number of calls to the database and decrease network traffic by bundling commands.
  • Thev reduce the number of calls to the database and decrease network traffic by using the local PL SQL engine.
B & D

Tuesday, February 23, 2010

Which type of package construct must be declared both within the package specification and package body?

A. All package variables.
B. Boolean variables.
C. Private procedures and functions.
D. Public procedures and functions.



Answer: D

The number of cascading triggers is limited by which data base initialization parameter?

The number of cascading triggers is limited by which data base initialization parameter?

A. CASCADE_TRIGGER_CNT.
B. OPEN_CURSORS.
C. OPEN_TRIGGERS.
D. OPEN DB TRIGGERS.



Answer is : B

Exams Mcse Mcp Microsoft Oracle Ocp Ccsa Ccse Solaris Dump Display


1. The number of cascading triggers is limited by which data base
initialization parameter?

a. CASCADE_TRIGGER_CNT
b. OPEN_CURSORS ****
c. OPEN_TRIGGERS
d. OPEN_DB_TRIGGERS

2. Which type of package construct must be declared both within the
package specifications and package body?

a. All package variables.
b. Boolean variables.
c. Private procedures and functions.
d. Public procedures and functions. ****

3. Why do stored procedures and functions improve performance?
(choose two)

a. They reduce network round trips. ****
b. They postpone PL/SQL parsing until run time. ****
c. They allow the application to perform high speed processing
locally.
d. They reduce the number of calls to the database and decrease
network traffic by bundling commands.
e. They reduce the number of calls to the database and decrease
network traffic by using the local PL/SQL engine.

4. When creating stored procedures and functions, which construct
allows you to transfer values to and from the calling environment?

a. Local variables.
b. Formal arguments. ****
c. Boolean variables.
d. Substitution variables.

5. You need to remove the database trigger, BUSINESS_RULE. Which
command do you use to remove the trigger in the SQL*Plus
environment?

a. DROP TRIGGER business_rule; ****
b. DELETE TRIGGER business_rule;
c. REMOVE TRIGGER business_rule;
d. ALTER TRIGGER business_rule;
e. DELETE FROM USER_TRIGGER
WHERE TRIGGER_NAME= ‘BUSINESS_RULE’;

6. Which two tables are fused track object dependencies?

a. USER_DEPENDENSIES. ****
b. USER_IDEPTREE.
c. IDEPTREE. ****
d. USER_DEPTREE.
e. USER_DEPENDS.

7. The QUERY_PRODUCT procedure directly references the product
table. There is a NEW_PRODUCT_VIEW view created based on the NOT
NULL columns of the table. The ADD_PRODUCT procedure updates the
table indirectly by the way of NEW_PRODUCT_VIEW view. Under which
circumstances does the procedureADD_PRODUCT get invalidated but
automatically get complied when invoked?

a. When the NEW_PRODUCT_VIEW is dropped.
b. When rows of the product table are updated through SQL Plus.
c. When the internal logic of the QUERY_PRODUCT procedure is
modified.
d. When a new column that can contain null values is added to the
product table. ****
e. When a new procedure is created that updates rows in the product
table directly.

8. You need to recompile several program units you have recently
modified through a PL/SQL program. Which statement is true?

a. You cannot recompile program units using a PL/SQL program.
b. You can use the DBMS_DDL. REOMPILE package procedure to recompile
the program units.
c. You can use the DBMS_ALTER. COMPILE packaged procedure to
recompile the program units.
d. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to
recompile the program units. ****
e. You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to
recompile the program units.

9. Which type of argument passes a value from a calling environment?

a. VARCHER2
b. BOOLEAN
c. OUT ****
d. IN

10. You need to create a trigger on the EMP table that monitors
every row that is changed and places this information into the
AUDIT_TABLE. Which type of trigger do you create?

a. Statement-level trigger on the EMP table.
b. For each row trigger on the EMP table. ****
c. Statement-level trigger on the AUDIT_TABLE table.
d. For each row statement level trigger on the EMP table.
e For each row trigger on the AUDIT_TABLE table.

11. In order for you to create a run package, MAINTAIN_DATA, which
privilege do you need?

a. EXECUTE privilege on the MAINTAIN_DATA package. ****
b. INVOKE privilege on the MAINTAIN_DATA package.
c. EXECUTE privilege on the program units in the MAINTAIN_DATA
package.
d. Object privilege on all of the objects that the MAINTAIN_DATA
package is accessing.
e. Execute privilege on the program units inside the MAINTAIN_DATA
package and execute privilege on the MAINTAIN_DATA package.

12. You have created a script file EMP_PROC.SQL that holds text to
create a procedure, PROCESS_EMP. You have compiled the procedure for
the SQL Plus environment by running the script file EMP_PROC.SQL.
What happens if there are syntax errors in the procedure
PROCESS_EMP?

a. The errors are stored in the EMP_PROC.ERR file.
b. The errors are displayed to the screen when the script file is
run.
c. The errors are stored in the PROCEDURE_ERRORS data dictionary
view.
d. You need to issue the SHOWERRORS command in the SQL Plus
environment to see the errors. ****
e. You need to issue the DISPLAY ERRORS command in the SQL Plus
environment to see the errors.

13. Which statement about local dependent objects is true?

a. They are on different nodes.
b. They are in a different database.
c. They are on the same node in the same database. ****
d. They are on the same node in a different database.

14. You need to create a stored procedure that deletes rows from a
table. The name of the table from which the rows are to be deleted
is unknown until run time. Which method do you implement while
creating such a procedure?

a. Use SQL command DELETE in the procedure to delete the rows.
b. Use DBMS_SQL packaged routines in the procedure to delete the
rows. ****
c. Use DBMS_DML packaged routines in the procedure to delete the
rows.
d. Use DBMSDELETE packaged routines in the procedure to delete the
rows.
e. YOU cannot have a delete statement without providing a table name
before compile time.

15. Under which situation do you create a server-side procedure?

a. When the procedure contains no SQL statements.
b. When the procedure contains no PL/SQL commands.
c. When the procedure needs to be used by many client applications
accessing several remote databases.
d. When the procedure needs to be used by many users accessing the
same schema objects on a local database. ****

16. Examine this procedure:

CREATE OR REPLACE PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHER2)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME).
VALUES(V_ID,V_LAST_NAME);
COMMIT;
END;

This procedure must invoke the UPD-STAT procedure and pass a
parameter. Which statement will successfully invoke this procedure?

a. EXECUTE UPD_BAT_STAT(V_ID);
b. UPD_BAT_STAT(V_ID); ****
c. RUN UPD_BAT_STAT(V_ID);
d. START UPD_BAT_STAT(V_ID);

17. Match the purity levels to their correct definitions:

Terms
RNTS
RNPS
WNDS
WNPS

Definitions
The function cannot modify the database tables.
The function cannot change the values of the package variables.
The function cannot query database tables.
The function cannot reference the value of public packaged
variables.

18. Examine this function:

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT. PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
SELECTS HITS/AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID_V_ID;
RETURN(V_AVG);
END;

This function must be moved to a package. Which additional statement
must be added to the function to allow you to continue using the
function in the GROUP BY clause of a SELECT statement?

a. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS); ****
b. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);
c. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);
d. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY);

19. A programmer develops a procedure, ACCOUNT_TRANSACTION, and has
left your company. You are assigned a task to modify this procedure.
You want to find all the program units invoking the
ACCOUNT_TRANSACTION procedure. How can you find this information?

a. Query the USER_SOURCE data dictionary view.
b. Query the USER_PROCEDURES data dictionary view.
c. Query the USER_DEPENDENCIES data dictionary views. ****
d. Set the SQL Plus environment variable trade code=true and run the
ACCOUNT_TRANSACTION procedure.
e. Set the SQL Plus environment variable DEPENDENCIES=TRUE and run
the Account_Transaction procedure.

20. Examine this package.

CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID NUMBER,V_LAST_NAME
VARCHER2,V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER,V_AB_IN NUMBER DEFAULT4,V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER,V_LAST_NAME VARCHER2,V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME,SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD PLAYER;
END BB_PACK;

Which statement successfully assigns $75000000 to the
V_MAX_TEAM_SALARY variable from within a stand alone procedure?

a. V_MAX_TEAM_SALARY := 75000000;
b. BB_PACK.ADD_PLAYER. V_MAX_TEAM_SALARY := 75000000;
c. BB_PACK.V_MAX_TEAM_SALARY := 75000000; ****
d. This variable cannot be assigned a value from outside the
package.

21. Which two statements about the overloading feature of packages
are true?

a. Only local or packaged sub programs can be overloaded. ****
b. Overloading allows different functions with the same name that
differ only in their return types.
c. Overloading allows different subprograms with the same number,
type and order of the parameter.
d. Overloading allows different subprograms with the same name and
same number or type of the parameters.
e. Overloading allows different subprograms with the same name but
different in either number or type or order of parameters. ****


22. Examine this package:

CREATE OR REPLACE manag emps
IS
Tax_rate CONSTANT NUMBER(5,2):= . 28:,
V_id NUMBER;
PROCEDURE insert_emp(p-deptno NUMBER,p-sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp:
FUNCTION calc_tax(o_sal NUMBER)
RETURN NUMBER;
END manag_emps;
/
CREATE REPLACE PACKAGE BODY manage_emps
IS
BEGIN
Update emp.
SET sal=|sal+p-raise_amt)+sal
WHERE empno= v_id;
END;
PROCEDURE insert_emp
(p_deptno NUMBER, p-sal NUMBER)
IS
BEGIN
INSERT INTO emp(empno, deptno,sal)
VALUES(v_id, p_deptno, p_sal);
END insert emp;
PROCEDURE delete_emp
IS
BEGIN
DELETE FROM emp
WHERE empno=v_id
END delete_emp;
PROCEDURE. Update_emp.
IS
V_sal NUMBER (10,2);
V_raise NUMBER(10,2);
BEGIN
SELECT Sal
INTO v_sal
FROM emp
WHERE empno=v_id;
IF v_sal<500>
V_raise:=0. 05;
ELSIF v_sal<1000>
V_raise:=0. 07;
ELSE
V_raise:=0. 04
END IF;
Update_sal(v_raise);
END update_emp.
FUNCTION calc_tax
(p_sal NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_sal* tax-rate;
END calc_tax;
END MANAGE_emp;
/

What is the name of the private procedure in the package?

a. CALC_TAX
b. INSERT_EMP
c. UPDATE_SAL ****
d. DELETE_EMP
e. UPDATE_EMP
f. MANAGE_EMP

23. Examine the code:

CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, audited)
VALUES(USER, SYSDATE);
END;

You issue an update command on the EMP table that results in
changing ten rows. How many rows are inserted into the AUDIT_TABLE?

a. 1 ****
b. 10
c. none
d. Value equal to the number of rows in the EMP table

24. All users currently have INSERT privileges on the PLAYER table.
You want only your users to insert into this table using the
ADD_PLAYER procedure. Which two actions must you take?

a. GRANT SELECT ON ADD_PLAYER TO PUBLIC;
b. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC; ****
c. GRANT INSERT ON PLAYER TO PUBLIC;
d. GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;
e. REVOKE INSERT ON PLAYER FROM PUBLIC; ****

25. Which oracle supply package allows you to run jobs that use
defined times?

a. DBMS_JOB ****
b. DBMS_RUN
c. DBMS_PIPE
d. DBMS_SQL

26. You need to drop a table from within a stored procedure. How do
you implement this?

a. You cannot drop a table from a stored procedure.
b. Use the DROP command in the procedure to drop the table.
c. Use the DBMS_DDL packaged routines in the procedure to drop the
table.
d. Use the DBMS_SQL packaged routines in the procedure to drop the
table. ****
e. Use the DBMS_DROP packaged routines in the procedure to drop the
table.

27. Which data dictionary view gives you the names and the source
code of all the procedures you have created?

a. USER_SOURCE ****
b. USER_OBJECTS
c. USER_PROCEDURES
d. USER_SUBPROGRAMS

28. Examine this package:

CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME)
VARCHAR2(V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
V_PLAYER_AVG NUMBER(4,3);
PROCEDURE UPD_PLAYER_STAT
V_ID IN NUMBER, V_AB IN NUMBER DEFAULT4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET ADD_BAT=ADD_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME, VARCHAR2, V_SALARY IN NUMBER);
IS
BEGIN
INSERT INTO PLAYER (ID, LAST_NAME, SALARY)
VALUES(V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;

Which kind of packaged variable is V_MAX_TEAM_SALARY?

a. PRIVATE
b. PUBLIC ****
c. IN
d. OUT

29. Examine this trigger:

CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY
AFTER INSERT OR UPDATE OR DELETE ON PLAYER
FOR EACH ROW
BEGIN
UPDATE TEAM
SET TOT_SALARY=TOT_SALARY+:NEW SALARY.
WHERE ID=:NEW:TEAM_ID;

You will be adding additional code later but for now you want the
current block to fire when updating the salary column. Which
solution should you use to verify that the user is performing an
update on the salary column?

a. ROW_UPDATE(‘SALARY’)
b. UPDATING(‘SALARY’) ****
c. CHANGING(‘SALARY’)
d. COLUMN_UPDATE(‘SALARY’)

30. Examine this package:

CREATE OR REPLACE PACKAGE discounts IS
G_ID NUMBER:=7839;
DISCOUNT_RATE NUMBER O. 00;
PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER);
END DISCOUNTS;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER)
IS
BEGIN DBMS_OUTPUT.PUT_LINE(‘DISCOUNTED||2_4
(V_PRICE*NVL(DISCOUNT_RATE, 1)))
END DISPLAY_PRICE;
BEGIN DISCOUNT_RATE;=0. 10;
END DISCOUNTS;
/

Which statement is true?

a. The value of DISCOUNT_RATE always remain 0.00 in a session.
b. The value of DISCOUNT_RATE is set to 0.10 each time the package
is invoked in a session.
c. The value of DISCOUNT_RATE is set to 1 each time the procedure
DISPLAY_PRICE is invoked.
d. The value of DISCOUNT_RATE is set to 0.10 when the package is
invoked for the first time in a session. ****

31. Examine this package:

CREATE OR REPLACE PACKAGE BB_PACK
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME
VARCHAR2, V_SALARY NUMBER);
DB_PACK;/ CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
V_WHERE_AVG NUMBER(4,3);
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AVG IN NUMBER DEFAULT 4,V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS=AT_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD-PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID, LAST_NAME, SALARY)
VALUES(V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;

An outside procedure VALIDATE_PLAYER_STAT is executed from this
package. What will happen when this procedure changes?

a. The package specification is dropped.
b. The package specification is invalidated.
c. The package is invalid to begin with.
d. The package body is invalidated ****

Monday, January 4, 2010

Returning Nulls in the Resulting Set of a Subquery

The SQL statement on the slide attempts to display all the employees who do not have any subordinates.

Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows. The reason is that all conditions that compare a null value result in a null. So whenever null values are likely to be part of the results set of a subquery, do not use the NOT IN operator. The NOT IN operator is equivalent to <> ALL.

Notice that the null value as part of the results set of a subquery is not a problem if you use the IN
operator. The IN operator is equivalent to =ANY. For example, to display the employees who have subordinates, use the following SQL statement:

SELECT emp.last_name FROM employees emp WHERE emp.employee_id IN

(SELECT mgr.manager_id
FROM employees mgr);

Alternatively, a WHERE clause can be included in the subquery to display all employees who do not have any subordinates:

SELECT last_name FROM employees

WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Illegal Queries Using Group Functions

The WHERE clause cannot be used to restrict groups. The SELECT statement on the slide results in an error because it uses the WHERE clause to restrict the display of average salaries of those departments that have an average salary greater than $8,000.

You can correct the slide error by using the HAVING clause to restrict groups.


SELECT department_id, AVG(salary)
FROM employees
HAVING AVG(salary) > 8000
GROUP BY department_id;

Creating Groups of Data: The GROUP BY Clause Syntax

The GROUP BY Clause
You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.
In the syntax:

group_by_expression specifies columns whose values determine the basis for
grouping rows Guidelines

• If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
• Using a WHERE clause, you can exclude rows before dividing them into groups.
• You must include the columns in the GROUP BY clause.
• You cannot use a column alias in the GROUP BY clause.
• By default, rows are sorted by ascending order of the columns included in the GROUP BY list.

You can override this by using the ORDER BY clause.

Using the DISTINCT Keyword

Use the DISTINCT keyword to suppress the counting of any duplicate values within a column.

The example on the slide displays the number of distinct department values in the EMPLOYEES table

All group functions ignore null values in the column. In the slide example, the average is calculated based only on the rows in the table where a valid value is stored in the COMMISSION_PCT column.

The average is calculated as the total commission paid to all employees divided by the number of employees receiving a commission (four).

Using the AVG and SUM Functions

Group Functions

You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example on the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.

Creating Three-Way Joins with the ON ClauseCreating Three-Way Joins with the ON Clause

A three-way join is a join of three tables. In SQL: 1999 compliant syntax, joins are performed from left to right so the first join to be performed is EMPLOYEES JOIN DEPARTMENTS. The first join condition can reference columns in EMPLOYEES and DEPARTMENTS but cannot reference columnsin LOCATIONS. The second join condition can reference columns from all three tables.


This can also be written as a three-way equijoin:

SELECT employee_id, city, department_name
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id;


SELECT e.employee_id, l.city, d.department_name
FROM employees e JOIN departments d USING (department_id)
JOIN locations l USING (location_id);

Creating Joins with the ON Clause

• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specify arbitrary conditions or specify columns to join, the ON clause is used.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand.

The ON Condition

Use the ON clause to specify a join condition. This lets you specify join conditions separate from any search or filter conditions in the WHERE clause.

Retrieving Records with the USING Clause

The example shown joins the DEPARTMENT_ID column in the EMPLOYEES and DEPARTMENTS tables, and thus shows the location where an employee works.

This can also be written as an equijoin:


SELECT employee_id, last_name,
employees.department_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Creating Joins with the USING Clause

• If several columns have the same names but the data types do not match, the NATURAL JOIN
clause can be modified with the USING clause to specify the columns that should be used for an equijoin.
• Use the USING clause to match only one column when more than one column matches.
• Do not use a table name or alias in the referenced columns.
• The NATURAL JOIN and USING clauses are mutually exclusive.

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

For example, this statement is valid:

SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;


This statement is invalid because the LOCATION_ID is qualified in the WHERE clause:

SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;

ORA-25154: column part of USING clause cannot have qualifier


The same restriction applies to NATURAL joins also. Therefore columns that have the same name in both tables have to be used without any qualifiers.

Retrieving Records with Natural Joins

In the example on the slide, the LOCATIONS table is joined to the DEPARTMENT table by the LOCATION_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.

Equijoins

The natural join can also be written as an equijoin:


SELECT department_id, department_name,
departments.location_id, city
FROM departments, locations
WHERE departments.location_id = locations.location_id;

Natural Joins with a WHERE Clause
Additional restrictions on a natural join are implemented by using a WHERE clause. The example below limits the rows of output to those with a department ID equal to 20 or 50.

SELECT department_id, department_name,

location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);

Creating Natural JoinsCreating Natural Joins


• The NATURAL JOIN clause is based on all columns in the two tables that have the same name.


• It selects rows from the two tables that have equal values in all matched columns.

• If the columns having the same names have different data types, an error is returned.

Joining a Table to Itself

Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of Whalen’s manager, you need to:

• Find Whalen in the EMPLOYEES table by looking at the LAST_NAME column.

• Find the manager number for Whalen by looking at the MANAGER_ID column. Whalen’s manager number is 101.

• Find the name of the manager with EMPLOYEE_ID 101 by looking at the LAST_NAME column. Kochhar’s employee number is 101, so Kochhar is Whalen’s manager.

In this process, you look in the table twice. The first time you look in the table to find Whalen in the LAST_NAME column and MANAGER_ID value of 101. The second time you look in the EMPLOYEE_ID column to find 101 and the LAST_NAME column to find Kochhar.

Returning Records with No Direct Match with Outer Joins

If a row does not satisfy a join condition, the row will not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, employee Grant does not appear because there is no department ID recorded for her in the EMPLOYEES table. Instead of seeing 20 employees in the result set, you see 19 records.


SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;

The DECODE Function

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.


In the preceding SQL statement, the value of JOB_ID is tested. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary.

The same statement can be expressed in pseudocode as an IF-THEN-ELSE statement:

IF job_id = ’IT_PROG’ THEN salary = salary*1.10
IF job_id = ’ST_CLERK’ THEN salary = salary*1.15
IF job_id = ’SA_REP’ THEN salary = salary*1.20
ELSE salary = salary

Using the COALESCE Function

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.

• If the first expression is not null, it returns that expression; otherwise, it does a COALESCE of the remaining expressions.


The COALESCE Function.

The COALESCE function returns the first non-null expression in the list.

Syntax

COALESCE (expr1, expr2, ... exprn)


In the syntax:


expr1 returns this expression if it is not null
expr2 returns this expression if the first expression is null and this expression is not null exprn returns this expression if the preceding expressions are null

The TO_NUMBER and TO_DATE Functions (continued)

Display the names and hire dates of all the employees who joined on May 24, 1999. Because the fx modifier is used, an exact match is required and the spaces after the word ‘May’ are not recognized.

SELECT last_name, hire_date FROM employees
WHERE hire_date = TO_DATE(’May 24, 1999’, ’fxMonth DD, YYYY’);

Sorting by Multiple Columns

You can sort query results by more than one column. The sort limit is the number of columns in the given table.

In the ORDER BY clause, specify the columns, and separate the column names using commas. If you want to reverse the order of a column, specify DESC after its name. You can also order by columns that are not included in the SELECT clause.

Example

Display the last names and salaries of all employees. Order the result by department number, and then in descending order by salary.

SELECT last_name, salary FROM employees ORDER BY department_id, salary DESC;

Example of the Precedence of the AND Operator

In the slide example, there are two conditions:

• The first condition is that the job ID is AD_PRES and the salary is greater than 15,000.


• The second condition is that the job ID is SA_REP.

Therefore, the SELECT statement reads as follows:

“Select the row if an employee is a president and earns more than $15,000, or if the employee is a sales representative.”

Logical Conditions in SQL

A logical condition combines the result of two component conditions to produce a single result based on them or inverts the result of a single condition. A row is returned only if the overall result of the condition is true. Three logical operators are available in SQL:

• AND
• OR
• NOT

All the examples so far have specified only one condition in the WHERE clause. You can use several conditions in one WHERE clause using the AND and OR operators.

Using the LIKE Condition in SQL

• Use the LIKE condition to perform wild card searches of valid search string values.
• Search conditions can contain either literal characters or numbers:


– % denotes zero or many characters.
– denotes one character.

SELECT first_name
FROM employees
WHERE first_name LIKE ’S%’;
What is RDBMS?

In recent years, database management systems (DBMS) have established themselves as the primary means of data storage for information systems ranging from large commercial transaction processing applications to PC-based desktop applications. At the heart of most of today's information systems is a relational database management system (RDBMS). RDBMSs have been the workhorse for data management operations for over a decade and continue to evolve and mature, providing sophisticated storage, retrieval, and distribution functions to enterprise-wide data processing and information management systems. Compared to the file systems, relational database management systems provide organizations with the capability to easily integrate and leverage the massive amounts of operational data into meaningful information systems. The evolution of high-powered database engines such as Oracle7 has fostered the development of advanced "enabling" technologies including client/server, data warehousing, and online analytical processing, all of which comprise the core of today's state-of-the-art information management systems.

Examine the components of the term relational database management system. First, a database is an integrated collection of related data. Given a specific data item, the structure of a database facilitates the access to data related to it, such as a student and all of his registered courses or an employee and his dependents. Next, a relational database is a type of database based in the relational model; non-relational databases commonly use a hierarchical, network, or object-oriented model as their basis. Finally, a relational database management system is the software that manages a relational database. These systems come in several varieties, ranging from single-user desktop systems to full-featured, global, enterprise-wide systems, such as Oracle7.

This blog discusses the basic elements of a relational database management system, the relational database, and the software systems that manage it. Also included is a discussion of nonprocedural data access. If you are a new user to relational database technology, you'll have to change your thinking somewhat when it comes to referencing data nonprocedurally.

The Relational Database Model

Most of the database management systems used by commercial applications today are based on one of three basic models: the hierarchical model, the network model, or the relational model. The following sections describe the various differences and similarities of the models.

Hierarchical and Network Models


The first commercially available database management systems were of the CODASYL type, and many of them are still in use with mainframe-based, COBOL applications. Both network and hierarchical databases are quite complex in that they rely on the use of permanent internal pointers to relate records to each other. For example, in an accounts payable application, a vendor record might contain a physical pointer in its record structure that points to purchase order records. Each purchase order record in turn contains pointers to purchase order line item records.

The process of inserting, updating, and deleting records using these types of databases requires synchronization of the pointers, a task that must be performed by the application. As you might imagine, this pointer maintenance requires a significant amount of application code (usually written in COBOL) that at times can be quite cumbersome.

Elements of the Relational Model


Relational databases rely on the actual attribute values as opposed to internal pointers to link records. Instead of using an internal pointer from the vendor record to purchase order records, you would link the purchase order record to the vendor record using a common attribute from each record, such as the vendor identification number.

Although the concepts of academic theory underlying the relational model are somewhat complex, you should be familiar with are some basic concepts and terminology. Essentially, there are three basic components of the relational model: relational data structures, constraints that govern the organization of the data structures, and operations that are performed on the data structures.

Relational Data Structures

The relational model supports a single, "logical" structure called a relation, a two-dimensional data structure commonly called a table in the "physical" database. Attributes represent the atomic data elements that are related by the relation. For example, the Customer relation might contain such attributes about a customer as the customer number, customer name, region, credit status, and so on.


Key Values and Referential Integrity


Attributes are grouped with other attributes based on their dependency on a primary key value. A primary key is an attribute or group of attributes that uniquely identifies a row in a table. A table has only one primary key, and as a rule, every table has one. Because primary key values are used as identifiers, they cannot be null. Using the conventional notation for relations, an attribute is underlined to indicate that it is the primary key of the relation. If a primary key consists of several attributes, each attribute is underlined.

You can have additional attributes in a relation with values that you define as unique to the relation. Unlike primary keys, unique keys can contain null values. In practice, unique keys are used to prevent duplication in the table rather than identify rows. Consider a relation that contains the attribute, United States Social Security Number (SSN). In some rows, this attribute may be null in since not every person has a SSN; however for a row that contains a non-null value for the SSN attribute, the value must be unique to the relation.

Linking one relation to another typically involves an attribute that is common to both relations. The common attributes are usually a primary key from one table and a foreign key from the other. Referential integrity rules dictate that foreign key values in one relation reference the primary key values in another relation. Foreign keys might also reference the primary key of the same relation. Figure illustrates two foreign key relationships.



Oracle and Client/Server


Oracle Corporation's reputation as a database company is firmly established in its full-featured, high-performance RDBMS server. With the database as the cornerstone of its product line, Oracle has evolved into more than just a database company, complementing its RDBMS server with a rich offering of well-integrated products that are designed specifically for distributed processing and client/server applications. As Oracle's database server has evolved to support large-scale enterprise systems for transaction processing and decision support, so too have its other products, to the extent that Oracle can provide a complete solution for client/server application development and deployment. This chapter presents an overview of client/server database systems and the Oracle product architectures that support their implementation.

An Overview of Client/Server Computing

The premise of client/server computing is to distribute the execution of a task among multiple processors in a network. Each processor is dedicated to a specific, focused set of subtasks that it performs best, and the end result is increased overall efficiency and effectiveness of the system as a whole. Splitting the execution of tasks between processors is done through a protocol of service requests; one processor, the client, requests a service from another processor, the server. The most prevalent implementation of client/server processing involves separating the user interface portion of an application from the data access portion.

On the client, or front end, of the typical client/server configuration is a user workstation operating with a Graphical User Interface (GUI) platform, usually Microsoft Windows, Macintosh, or Motif. At the back end of the configuration is a database server, often managed by a UNIX, Netware, Windows NT, or VMS operating system.

Client/server architecture also takes the form of a server-to-server configuration. In this arrangement, one server plays the role of a client, requesting database services from another server. Multiple database servers can look like a single logical database, providing transparent access to data that is spread around the network.

Designing an efficient client/server application is somewhat of a balancing act, the goal of which is to evenly distribute execution of tasks among processors while making optimal use of available resources. Given the increased complexity and processing power required to manage a graphical user interface (GUI) and the increased demands for throughput on database servers and networks, achieving the proper distribution of tasks is challenging. Client/server systems are inherently more difficult to develop and manage than traditional host-based application systems because of the following challenges:

The components of a client/server system are distributed across more varied types of processors. There are many more software components that manage client, network, and server functions, as well as an array of infrastructure layers, all of which must be in place and configured to be compatible with each other.

The complexity of GUI applications far outweighs that of their character-based predecessors. GUIs are capable of presenting much more information to the user and providing many additional navigation paths to elements of the interface.

Troubleshooting performance problems and errors is more difficult because of the increased number of components and layers in the system.

Databases in a Client/Server Architecture

Client/server technologies have changed the look and architecture of application systems in two ways. Not only has the supporting hardware architecture undergone substantial changes, but there have also been significant changes in the approach to designing the application logic of the system.

Prior to the advent of client/server technology, most Oracle applications ran on a single node. Typically, a character-based SQL*Forms application would access a database instance on the same machine with the application and the RDBMS competing for the same CPU and memory resources. Not only was the system responsible for supporting all the database processing, but it was also responsible for executing the application logic. In addition, the system was burdened with all the I/O processing for each terminal on the system; each keystroke and display attribute was controlled by the same processor that processed database requests and application logic.

Client/server systems change this architecture considerably by splitting all of the interface management and much of the application processing from the host system processor and distributing it to the client processor.

Combined with the advances in hardware infrastructure, the increased capabilities of RDBMS servers have also contributed to changes in the application architecture. Prior to the release of Oracle7, Oracle's RDBMS was less sophisticated in its capability to support the processing logic necessary to maintain the integrity of data in the database. For example, primary and foreign key checking and enforcement was performed by the application. As a result, the database was highly reliant on application code for enforcement of business rules and integrity, making application code bulkier and more complex. Figure 2.1 illustrates the differences between traditional host-based applications and client/server applications. Client/server database applications can take advantage of the Oracle7 server features for implementation of some of the application logic.