oracle

A subtype is a subset of an existing data type that may place a constraint on its base type~True

An internal LOB is ~ stored in the database

Assuming today is Monday, 10 July 2000, what is returned by this statement\: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual; ~17-JUL-00

Because they should not contain SQL manipulation statements, what kind of trigger is typically used to enforce complex integrity constraints? ~BEFORE ROW triggers

Because they should not contain SQL manipulation statements, what kind of trigger is typically used to enforce complex integrity constraints?\nI. AFTER ROW triggers.\nII. BEFORE ROW triggers.\nIII. INSTEAD OF triggers. II only

Before reopening a cursor variable what should you do?~Nothing. You do not need to close a cursor variable before reopening.

BFILENAME function can not be used to\: ~Create a BFILE column

Create a PL/SQL block that selects the maximum department ID in the departments table and stores it in the v_max_deptno variable. Display the maximum department ID. Declare a variable v_max_deptno of type NUMBER in the declarative section. Include a SELECT statement to retrieve the maximum department_id from the departments table~DECLARE v_max_deptno NUMBER; BEGIN SELECT MAX(department_id) FROM departments INTO v_max_deptno; DBMS_OUTPUT.PUT_LINE('The maximum department_id is \: ' || v_max_deptno); END;#

Create and execute a simple anonymous block that outputs "Hello World."~SET SERVEROUTPUT ON\nBEGIN\nDBMS_OUTPUT.PUT_LINE('Hello World');\nEND;

Create bind variables basic_percent and pf_percent of type NUMBER.~VARIABLE b_basic_percent NUMBER VARIABLE b_pf_percent NUMBER

Delete rows that belong to department 10 from the employees table.~Declare deptno employees.department_id%type\:\~10;\nBegin delete from employees\nWhere department_id\~deptno;\nEnd;

Examine the code examples. Which one is correct? ~ CREATE OR REPLACE TRIGGER authorize_action BEFORE INSERT ON EMPLOYEES CALL log_execution /

Examine the trigger heading: CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF sal, job ON emp FOR EACH ROW Under which condition does this trigger fire? ~ when the value of the SAL or JOB column in a row is updated in the EMP table

Examine this code\:\n\nCREATE OR REPLACE PROCEDURE audit_emp\n(p_id IN emp.empno%TYPE) IS\nv_id NUMBER; PROCEDURE log_exec IS\nBEGIN\nINSERT INTO log_table (user_id, log_date) VALUES (USER, SYSDATE);\nEND log_exec;\nv_name VARCHAR2(20); BEGIN\nDELETE FROM emp WHERE empno \~ p_id; log_exec;\nSELECT ename, empno INTO v_name, v_id FROM emp\nWHERE empno \~ p_id; END audit_emp;\n\nWhy does this code cause an error when compiled?~The v_name variable should be declared before declaring the LOG_EXEC procedure.

Examine this function\: CREATE OR REPLACE FUNCTION CAL_PLAYER_AVG (V_ID in PLAYER_NAT_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?~ SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

Examine this package specification\:\nCREATE OR REPLACE PACKAGE concat_all\nIS\nv_string VARCHAR2(100);\nPROCEDURE combine (p_num_val NUMBER);\nPROCEDURE combine (p_date_val DATE);\nPROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);\nEND concat_all;\n/\nWhich overloaded COMBINE procedure declaration can be added to this package specification?~PROCEDURE combine;

Examine this procedure\: \nCREATE OR REPLACE PROCEDURE find_cpt \n (v_movie_id \{argument mode\} NUMBER, v_cost_per_ticket \{argument mode\} \nNUMBER) \nIS \nBEGIN \n IF v_cost_per_ticket > 8.50 THEN \n SELECT cost_per_ticket \n INTO v_cost_per_ticket \n FROM gross _receipt \n WHERE movie_id \~ v_movie_id; \n END IF; \nEND; \nWhich argument mode should be used for V_MOVIE_ID?~IN

Examine this procedure\: \nCREATE OR REPLACE PROCEDURE find_seats_sold \n (v_movie_id IN NUMBER) \nIS \n v_seats_sold gross_receipt.seats_sold%TYPE; \nBEGIN \n SELECT seats_sold \n INTO v_seats_sold \n FROM gross_receipt \n WHERE movie_id \~ v_movie_id; \nEND; \nThe value of V_SEATS_SOLD must be returned to the calling environment. Which change should \nyou make to the code?~Declare V_SEATS_SOLD as an OUT argument.

Examine this procedure\: \nCREATE OR REPLACE PROCEDURE find_seats_sold (v_movie_id IN NUMBER, v_seats_sold \nOUT gross_receipt.seats_sold%TYPE) \nIS \nBEGIN \n SELECT seats_sold \n INTO v_seats_sold \n FROM gross_receipt \n WHERE movie_id \~ v_movie_id; \nEND; \nWhich set of commands will successfully invoke this procedure? ~VARIABE g_seats_sold NUMBER EXECUTIVE find_seats_sold(34, \:g_seats_sold);

Examine this procedure\: \nCREATE OR REPLACE PROCEDURE update_employee \n (v_emp_id IN NUMBER) \nIS \n v_comm NUMBER; \n PROCEDURE calc_comm \n IS \n v_total NUMBER; \n BEGIN \n SELECT SUM(ord.total) \n INTO v_total \n FROM ord,customer \n WHERE ord.custid \~ customer.custid \n AND customer.repid \~ v_emp_id; \n v_comm \:\~ v_total * .20; \n END calc_comm; \n v_percentage NUMBER; \nBEGIN \n SELECT percentage \n INTO v_percentage \n FROM daily_figures \n WHERE TRUNC(figure_date) \~ TRUNC(SYSDATE); \n IF v_percentage > 33 THEN \n calc_comm; \n END IF; \nEND; \nWhy does this code cause an error when compiled?~CALC_COMM must be declared after all local variable declarations.

Examine this procedure\:\n\nCREATE OR REPLACE PROCEDURE ADD_PLAYER\n(V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS\nBEGIN\nINSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT;\nEND;This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. \n\n Which statement will successfully invoke this procedure? UPD_BAT_STAT(V_ID)

Examine this procedure\:\n\nCREATE OR REPLACE PROCEDURE INSERT_TEAM\n(V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2)\nIS BEGIN\nINSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT;\nEND;\nWhich statement will successfully invoke this procedure.~EXECUTE INSERT_TEAM;~EXECUTE INSERT_TEAM (3,'LONGHORNS');

Examine this procedure\:\nCREATE OR REPLACE PROCEDURE ADD_PLAYER\n(V_ID IN NUMBER, V_LAST_NAME VARCHAR2(30)) IS\nBEGIN\nINSERT INTO PLAYER (ID,LAST_NAME)\nVALUES (V_ID, V_LAST_NAME); COMMIT;\nEND;\nWhy does this command fail when executed?~When declaring arguments, length is not allowed.

Examine this procedure\:\nCREATE OR REPLACE PROCEDURE DELETE_PLAYER\n(V_ID IN NUMBER) IS\nBEGIN\nDELETE FROM PLAYER WHERE V_ID \~ 31; EXCEPTION\nWHEN STATS_EXIST_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE\n('Cannot delete this player, child records exist in PLAYER_BAT_STAT table'); \nEND;\nWhat prevents this procedure from being created successfully? ~The STATS_EXIST_EXCEPTION has not been declared as an exception.

Examine this trigger\:\nCREATE OR REPLACE TRIGGER UPD_TEAM_SALARY\nAFTER INSERT OR UPDATE OR DELETE ON PLAYER\nFOR EACH ROW\nBEGIN\nUPDATE TEAM\nSET TOT_SALARY \~ TOT_SALARY + \:NEW.SALARY\nWHERE ID \~ \:NEW.TEAM_ID;\nEND;\nYou will be adding additional code later but for now you only want the current code in the block to\nfire when updating the salary column. Which function should you use to verify that the user is performing an update on the salary column?~UPDATING ('SALARY')

Find wrong answer about Strong REF CURSOR~Strong REF CURSOR Is nonrestrictive

For declaring a REF CURSOR you can not ~Fetch into a record

For more complex procedures and functions involving iteration through multiple rows, PL/SQL supports ~All of the above

For more complex procedures and functions involving iteration through multiple rows, PL/SQL supports\: <br />I. Explicit cursors. <br />II. Implicit cursors. <br />III. Dynamic cursors.~I, II, III#

Functions can have these types of parameters~only IN parameters

Given a function CALCTAX\: \: CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL*Plus prompt, which statement is true?~ You need to create a SQL*Plus environment variable X and issue the command EXECUTE \:X \:\~ CALCTAX(1000);

How do you show a list of all users of scheme?~select * from DBA_USERS#

How many columns are presented after executing this query\: \nSELECT address1||','||address2||','||address2 "Adress" FROM employee~1

How to change a password of user?~ALTER USER scott IDENTIFIED BY password#

How to create a table My_emp_table as a copy of employees table?~BEGIN EXECUTE IMMEDIATE 'CREATE TABLE My_emp_table AS SELECT * FROM employees';END;

How to write the UPDATE statement to set the location_id to 3000 for the department with dept_id of 280 in the departments table. ~UPDATE departments SET location_id\~3000 WHERE\ndepartment_id\~280;

Identify invalid identifier names:~number\#

Identify invalid variable declaration and initialization\:~PRINTER_NAME constant VARCHAR2(10);

Identify valid variable declaration and initialization\:~number_of_copies PLS_INTEGER;

In a stored procedure, when specifying a string parameter you should use which of these data types~VARCHAR2

In a stored procedure, when specifying a string parameter you should use which of these data types~VARCHAR2

In the executable section of the PL/SQL block, assign the values 45 and 12 to bind variables basic_percent and pf_percent, respectively.~\:b_basic_percent\:\~45;\n\:b_pf_percent\:\~12;

In which situation you can not Execute Functions? ~A SELECT statement that contains DML statements

PROCEDURE ADD_PRODUCT (p_prodno NUMBER, p_prodname VARCHAR2);Which procedure declaration cannot be added to the package specification?PROCEDURE add_product(p_price NUMBER, p_description VARCHAR2)

Remove the department of 280.~DELETE FROM departments WHERE department_id\~280;

The CALL statement inside the trigger body enables you to call ~ a stored procedure

The common SQL*Plus command which causes the results of PL/SQL statements to be displayed is ~ SET SERVEROUTPUT ON

The declarative section begins with the keyword _____________and ends when the executable section starts. ~DECLARE

The exception section is nested within the ____________~executable section

The following data exists in the PRODUCTS table\: PROD_ID PROD_LIST_PRICE 123456 152525.99 You issue the following query\: SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*') FROM products WHERE prod_id \~ 123456; What would be the outcome?~ 152526 ****

The MODIFY_PAYROLL procedure contains many SQL statements and will be executed from \nmultiple client applications. Where should this procedure be stored?~server only

The procedure ADD_PRODUCT is defined within a package specification as follows:

This statement fails when executed\: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BAT_STAT (PLAYER_ID, SEASON_YEAR, AT_BATS,HITS) VALUES (\:NEW.ID, 1997, 0,0); END; To which type must you convert the trigger to correct the error? ~ row

To process implicit cursors you use these commands\:~None of OPEN, FETCH, CLOSE commands

Update table employees and salary to 2800 and id is 115~UPDATE employees SET salary \~ 2800 WHERE employee_id \~ 115;

What does the RAISE_APPLICATION_ERROR procedure do? ~It enables you to issue user-defined error messages from subprograms.

What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations? ~ The SQL statement is run and the number of rows processed is returned.

What is a conditional predicate in a DML trigger? ~ A conditional predicate allows you to combine several DML triggering events into one in the trigger body.

What is a record? ~A record is a composite type that has internal components, which can be manipulated individually

What is declared inline at the point in an application where it is to be executed~Anonymous block

What is nested tables?~NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes

What is PLSQL (abbreviation)?~Structured Query Language Procedural Language#

What is the EXCEPTION section?~Specifies the actions to perform when errors and abnormal conditions arise in the executable section

What is the Executable (BEGIN..END) section?~Contains SQL statements to retrieve data from the database

What program constructs has that description\: "Unnamed PL\SQL blocks that are embedded within an application or are issued interactively"?~Anonymous blocks

What type of exceptions is implicitly raised? ~both of them

When creating a function, in which section will you typically find the RETURN keyword?~EXECUTABLE AND HEADER

When creating procedures, local variables should be placed after which key word?~IS

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

When using a packaged function in a query, what is true?~ The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

Which character is used to continue a statement in SQL*Plus? ~/

Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased? ~ CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary nor increase by more than 10%'); END;

Which code successfully calculates tax~ CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) RETURN NUMBER IS v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno \~ p_no; RETURN (v_sal * 0.05); END;

Which code can you use to ensure that the salary is neither increased by more than 10% at a time nor is it ever decreased? ~CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary nor increase by more than 10%'); END;

Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on the TEAM table?~ GRANT SELECT,INSERT,UPDATE,DELETE ON TEAM TO PUBLIC;

Which CREATE TABLE statement is valid? ~CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL);

Which CREATE TABLE statement is valid?~ CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Which cursor attribute evaluates to TRUE if the most recent SQL statement affects one or more rows?~SQL%FOUND

Which four triggering events can cause a trigger to fire? (Choose four.)~%-25% A user executes a SELECT statement with an ORDER BY clause.~%-25% A user executes a JOIN statement that uses four or more tables.

Which is the valid CREATE TABLE statement?~ CREATE TABLE emp9$\# (emp_no NUMBER (4));

Which of following approvals does not belong to procedures?~Procedures are called as an expression embedded within another command

Which of the answers false in comparing cursor variables with static cursors?~Don't give access to query work areas after a block completes

Which of the answers is true?~the stored procedure is a dependent object, whereas the table is a referenced object.

Which of the following answers is false?~You cannot assign a not null value to cursor variables

Which of the following can be a valid column name?~Catch_\#22

Which of the following collections can be stored inline?~Varray

Which of the following collections can not be stored in the database?~Associative arrays

Which of the following collections is a set of key-value pairs, where each key is unique and is used to locate a corresponding value in the collection?~Associative arrays

Which of the following examples is weak ref cursor?~type emprefcur is ref cursor;#

Which of the following exhibits the proper syntax for a CURSOR FOR loop (choose one)? DECLARE CURSOR my_employees IS SELECT * FROM employee; my_name VARCHAR2(30); my_title VARCHAR2(30); BEGIN OPEN my_employees; FOR csr_rec IN my_employees LOOP INSERT INTO my_emps (my_empname, my_emptitle) VALUES (my_name, my_title); END LOOP; CLOSE my_employees; END;

Which of the following is false ~Fetching into a multiple variables when fetching from a cursor gives you the advantage of automatic usage of the structure of the SELECT column list.

Which of the following is not a common cursor attribute? ~%IsNotOpen

Which of the following is not an iteration statement supported by PL/SQL\:~The LOOP UNTIL statement.

Which of the following PL/SQL blocks execute successfully?~DECLARE\namount INTEGER(10);\nBEGIN\nDBMS_OUTPUT.PUT_LINE(amount);\nEND;

Which of the following statements are true?~all of the answers.

Which of the following statements is true?~a cursor always refers to the same work area, a cursor variable can refer to different work areas

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

Which statement about triggers is true?~ You use a database trigger to fire when an INSERT statement occurs.

Which statement about triggers is true?~ You use a database trigger to fire when an INSERT statement occurs.

Which statement about procedure is true?~They promote reusability and maintainability

Which statement are true about associative arrays and nested tables? ~Only nested tables can be used as column types in database tables

Which statement are true about cursor variables? ~Cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) can be applied to a cursor variable.

Which statement is true about removing packages? ~ Removing a package specification removes the body too.

Which statement is true? ~ Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.

Which statement is true?~ Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.

Which statement is valid when removing procedures?::Which statement is valid when removing procedures?~ Use a drop procedure statement to drop a standalone procedure.

Which table should you query to determine when your procedure was last compiled? ~ USER_OBJECTS

Which two describe a stored procedure? (Choose two.) ~%50% A stored procedure is a named PL/SQL block that can accept parameters ~%-50% The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment.

Which two dictionary views track dependencies? (Choose two.)~%50% DEPTREE_TEMPTAB~%50% USER_DEPENDENCIES

Which two does the INSTEAD OF clause in a trigger identify? (Choose two.)~%50% The view associated with the trigger.~%50% The event associated with the trigger.

Which two statements about functions are true? (Choose two.){~%50% A function must have a return statement in its body to execute successfully.~%50% A stored function increases efficiency of queries by performing functions on the server rather than in the application.

Which two statements about object dependencies are accurate? (Choose two.) ~%50% When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid.~%50% When referencing a package procedure or function from a stand-alone procedure or function, If the package specification changes, the stand-alone procedure referencing a package construct as well as the package body become invalid.

Which two statements about packages are true? (Choose two.) ~%50% The package specification is required, but the package body is optional. ~%50% The specification and body of the package are stored separately in the database.

Which two statements about packages are true? (Choose two.)~%50% The contents of packages can be shared by many applications.~%50% You can achieve information hiding by making package constructs private.

Which two statements about the overloading feature of packages are true? (Choose two.)~%50% Only local or packaged subprograms can be overloaded. ~%50% Overloading allows different subprograms with same name, but different in either number, type or order of parameters.

Which two statements are true?~A function must return a value.

Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two.)~%50% It persists across transactions within a session.~%50% It does not persist from session to session for the same user.

Why do stored procedures and functions improve performance? ~They reduce network round trips.

Why do you use an INSTEAD OF trigger? ~ to modify data in which the DML statement has been issued against an inherently non-updateable view

You are developing a PL/SQL block designed for bulk data operations. When attemping to store table data for multiple rows in a PL/SQL variable, which one of the following choices identifies the mechanism best suited for this task (choose one)?~Cursor

You create a DML trigger. For the timing information, which are valid with a DML trigger? (Choose all that apply.)~ BEFORE

You disabled all triggers on the EMPLOYEES table to perform a data load. Now, you need to enable all triggers on the EMPLOYEES table. Which command accomplishes this?~ ALTER TABLE employees ENABLE ALL TRIGGERS;

You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?~ The trigger fails because a SELECT statement on the table being updated is not allowed.

You have an AFTER UPDATE row-level trigger on the table EMP . This trigger queries the EMP table and inserts the updating user's information into the AUDIT_TABLE . What happens when the user updates rows on the EMP table?~ A runtime error occurs. The effect of trigger body and the triggering statement are rolled back.

You need to create a DML trigger. Which five pieces need to be identified? (Choose five.)~%20% table~%20% DML event~%20% trigger body ~%20% trigger name~%20% trigger timing

You need to create a trigger to ensure that information in the EMP table is only modified during business hours, Monday to Friday from 9\:00am to 5\:00pm. Which types of trigger do you create? (Choose two.)~%50% row level BEFORE INSERT OR UPDATE OR DELETE ON EMP~%50% statement level BEFORE INSERT OR UPDATE OR DELETE ON EMP

You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position. Which query would give the required output?~ SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, -2, 1)\~'a';

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get the required output?~ WHERE prod_id LIKE '%\_D123%' ESCAPE '\'

You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, t he customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase . Which statement would accomplish this requirement?~ SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;

You need to remove the database trigger BUSINESS_HOUR. Which command do you use to remove the trigger in the SQL*Plus environment?~ DROP TRIGGER business_hour;

You want to create a PL/SQL block of code that calculates discounts on customer orders. This code will be invoked from several places, but only within the program unit ORDERTOTAL . What is the most appropriate location to store the code that calculates the discount? ~ a local subprogram defined within the program unit ORDERTOTAL

You want to create procedures, functions and packages. Which privilege do you need?~ CREATE PROCEDURE system privilege