9i Internet Application Developer Introduction to Oracle: SQL and PL/SQL

1.Click on the EXHIBIT button and examine the table instance chart for the cars table. Which SELECT statement will display style, color, and lot number for all cars based on the model entered at the prompt, regardless of case?

A.SELECT style, color, lot_no FROM cars WHERE model = UPPER('&model');
B.SELECT style, color, lot_no FROM cars WHERE model = '&model';
C.SELECT style, color, lot_no FROM cars WHERE UPPER(model) = UPPER('&model');
D.SELECT style, color, lot_no FROM cars WHERE UPPER(model) = '&model';
Correct:C
2.Click on the EXHIBIT button and examine the table instance chart for the patient table. You need to create the patient_id_seq sequence to be used with the patient table's primary key column. The sequence should begin at 1000, have a maximum value of 999999999, never reuse any numbers, and increment by 1. Which statement would you use to complete this task?

A.CREATE SEQUENCE patient_id_seq START WITH 1000 MAXVALUE 999999999 NOCYCLE;
B.CREATE SEQUENCE patient_id_seq START WITH 1000 MAXVALUE 999999999 STEP BY 1;
C.CREATE SEQUENCE patient_id_seq ON patient (patient_id) MINVALUE 1000 MAXVALUE 999999999 INCREMENT BY 1 NOCYCLE;
D.This task cannot be accomplished.
Correct:A
3.You issue this command: CREATE SYNONYM emp FOR ed.employee; Which task has been accomplished?
A.The need to qualify an object name with its schema was eliminated for user Ed.
B.The need to qualify an object name with its schema was eliminated for only you.
C.The need to qualify an object name with its schema was eliminated for all users.
D.The need to qualify an object name with its schema was eliminated for users with access.
Correct:B
4.Click on the EXHIBIT button and examine the structure of the DEPARTMENT and EMPLOYEE tables. Evaluate this SQL statement: CREATE INDEX emp_dept_id_idx ON employee(dept_id); Which result will the statement provide?

A.Store an index in the EMPLOYEE table.
B.Increase the chance of full table scans.
C.May reduce the amount of disk I/O for SELECT statements.
D.May reduce the amount of disk I/O for INSERT statements.
E.Override the unique index created when the FK relationship was defined.
Correct:C
5.Which should you do after each FETCH statement in a PL/SQL block?
A.Open the cursor.
B.Close the cursor.
C.Initialize the loop.
D.Test for rows using a cursor attribute.
Correct:D
6.Given this executable section of a PL/SQL block: BEGIN FOR employee_record IN salary_cursor LOOP employee_id_table(employee_id) := employee_record.last_name; END LOOP; CLOSE salary_cursor; END; Why does this section cause an error?
A.The cursor needs to be opened.
B.No FETCH statements were issued.
C.Terminating conditions are missing.
D.The cursor does not need to be closed.
Correct:D
7.The structure of the DEPT table is as follows: Name Null? Type ------------------------------- -------- ------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) Examine the code: DECLARE TYPE dept_record_type IS RECORD (dno NUMBER, name VARCHAR2(20)); dept_rec dept_record_type; BEGIN SELECT deptno, dname INTO dept_rec FROM dept WHERE deptno = 10; END; Which statement displays the name of the selected department?
A.DBMS_OUTPUT.PUT_LINE(name);
B.DBMS_OUTPUT.PUT_LINE(dname);
C.DBMS_OUTPUT.PUT_LINE(dept_rec.name);
D.DBMS_OUTPUT.PUT_LINE(dept_rec.dname);
E.DBMS_OUTPUT.PUT_LINE(dept_rec(name));
Correct:C
8.The EMPLOYEE table contains these columns: BONUSNUMBER(7,2) DEPT_ID NUMBER(9) There are 10 departments and each department has at least 1 employee. Bonus values are greater than 500; not all employees receive a bonus. Evaluate this PL/SQL block: DECLARE v_bonusemployee.bonus%TYPE := 300; BEGIN UPDATE employee SET bonus = bonus + v_bonus WHERE dept_id IN (10, 20, 30); COMMIT; END; What will be the result?
A.All employees will be given a 300 bonus.
B.A subset of employees will be given a 300 bonus.
C.All employees will be given a 300 increase in bonus.
D.A subset of employees will be given a 300 increase in bonus.
Correct:D
9.Evaluate this IF statement: IF v_value > 100 THEN v_new_value := 2 * v_value; ELSIF v_value > 200 THEN v_new_value := 3 * v_value; ELSIF v_value < 300 THEN v_new_value := 4 * v_value; ELSE v_new_value := 5 * v_value; END IF; What would be assigned to V_NEW_VALUE if V_VALUE is 250?
A.250
B.500
C.750
D.1000
E.1250
Correct:B
10.Which ALTER command would you use to reinstate a disabled primary key constraint?
A.ALTER TABLE cars ENABLE PRIMARY KEY (id);
B.ALTER TABLE cars ENABLE CONSTRAINT cars_id_pk;
C.ALTER TABLE cars ENABLE PRIMARY KEY (id) CASCADE;
D.ALTER TABLE cars ADD CONSTRAINT cars_id_pk PRIMARY KEY (id);
Correct:B
11.You need to perform a major update on the EMPLOYEE table. You have decided to disable the PRIMARY KEY constraint on the empid column and the CHECK constraint on the job column. What happens when you try to enable the constraints after the update is completed?
A.You need to recreate the constraints once they are disabled.
B.Any existing rows that do not confirm with the constraints are automatically deleted.
C.Only the future values are verified to confirm with the constraints, leaving the existing values unchecked.
D.The indexes on both the columns with the PRIMARY KEY constraint and the CHECK constraint are automatically re-created.
E.All the existing column values are verified to confirm with the constraints and an error message is generated if any existing values do not confirm.
Correct:E
12.Which statement is valid within the executable section of a PL/SQL block?
A.BEGIN emp_rec emp%ROWTYPE; END;
B.WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No records found');
C.SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = 101;
D.PROCEDURE calc_max (n1 NUMBER,n2 NUMBER,p_max OUT NUMBER) IS BEGIN IF n1 > n2 THEN p_max := n1; ELSE p_max := n2; END;
Correct:C
13.Examine this block of code: SET SERVEROUTPUT ON DECLARE x NUMBER; v_sal NUMBER; v_found VARCHAR2(10) := 'TRUE'; BEGIN x := 1; v_sal := 1000; DECLARE v_found VARCHAR2(10); y NUMBER; BEGIN IF (v_sal > 500) THEN v_found := 'YES'; END IF; DBMS_OUTPUT.PUT_LINE ('Value of v_found is '|| v_found); DBMS_OUTPUT.PUT_LINE ('Value of v_sal is '|| v_sal); y := 20; END; DBMS_OUTPUT.PUT_LINE ('Value of v_found is '|| v_found); DBMS_OUTPUT.PUT_LINE ('Value of Y is '|| TO_CHAR(y)); END; SET SERVEROUTPUT OFF What is the result of executing this block of code?
A.PLS-00201: identifier 'Y' must be declared
B.Value of v_found is YES Value of v_sal is 1000 Value of v_found is TRUE
C.Value of v_found is YES Value of v_sal is 1000 Value of v_found is YES Value of Y is 20
D.PLS-00201: identifier 'v_sal' must be declared PLS-00201: identifier 'Y' must be declared
E.Value of v_found is YES Value of v_sal is 1000 Value of v_found is TRUE Value of Y is 20
Correct:A
14.Examine this block of code: SET SERVEROUTPUT ON DECLARE x NUMBER; v_sal NUMBER; v_found VARCHAR2(10) := 'TRUE'; BEGIN x := 1; v_sal := 1000; DECLARE v_found VARCHAR2(10); y NUMBER; BEGIN IF (v_sal > 500) THEN v_found := 'YES'; END IF; DBMS_OUTPUT.PUT_LINE ('Value of v_found is '|| v_found); DBMS_OUTPUT.PUT_LINE ('Value of v_sal is '|| v_sal); y := 20; END; DBMS_OUTPUT.PUT_LINE ('Value of v_found is '|| v_found); DBMS_OUTPUT.PUT_LINE ('Value of Y is '|| TO_CHAR(y)); END; SET SERVEROUTPUT OFF Why does this code produce an error when executed?
A.The value of V_FOUND cannot be 'YES'.
B.Variable V_FOUND is declared at more than one location.
C.Variable Y is declared in the inner block and referenced in the outer block.
D.Variable V_SAL is declared in the outer block and referenced in the inner block.
Correct:C
15.Examine the declaration section: DECLARE CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = p_deptno AND job = p_job; BEGIN … Which statement opens this cursor successfully?
A.OPEN emp_cursor;
B.OPEN emp_cursor('Clerk', 10);
C.OPEN emp_cursor(10,'Analyst');
D.OPEN emp_cursor(p_deptno, p_job);
Correct:C
16.Your company wants to give each employee a $100 salary increment. You need to evaluate the results from the EMP table prior to the actual modification. If you do not want to store the results in the database, which statement is valid?
A.You need to add a column to the EMP table.
B.You need to give the arithmetic expression that involves the salary increment in the SET clause of the UPDATE statement.
C.You need to give the arithmetic expression that involves the salary increment in the SELECT clause of the SELECT statement.
D.You need to give the arithmetic expression that involves the salary increment in the UPDATE clause of the SELECT statement.
E.You need to give the arithmetic expression that involves the salary increment in the DISPLAY clause of the SELECT statement.
Correct:C
17.You need to execute a script file named QUERYEMP.SQL from your SQL*Plus environment. Which command do you use?
A.RUN QUERYEMP
B.GET QUERYEMP
C.START QUERYEMP
D.EXECUTE QUERYEMP
Correct:C
18.The PRODUCT table contains these columns: ID NUMBER(9) PK COST NUMBER(7,2) SALE_PRICE NUMBER(7,2) Management has asked you to calculate the net revenue per unit for each product if the cost of each product is increased by 10% and the sale price of each product is increased by 25%. You issue this SQL statement: SELECT id, sale_price * 1.25 - cost * 1.10 FROMproduct; Which conclusion can you draw from the results?
A.Only the required results are displayed.
B.The results provide more information than management requested.
C.A function needs to be included in the SELECT statement to achieve the desired results.
D.The order of the operations in the calculation needs to be changed to achieve the required results.
Correct:A
19.You want to display the average salary for departments 20 and 50, but only if those departments have an average salary of at least 2000. Which statement will produce the required results?
A.SELECT deptno, AVG(sal) FROM emp WHERE deptno IN (20, 50) GROUP BY deptno HAVING AVG(sal) >= 2000;
B.SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >= 2000 AND deptno IN (20, 50);
C.SELECT deptno, AVG(sal) FROM emp WHERE deptno IN (20, 50) AND AVG(sal) >= 2000 GROUP BY deptno;
D.SELECT deptno, AVG(sal) FROM emp WHERE deptno IN (20, 50) GROUP BY AVG(sal) HAVING AVG(sal) >= 2000;
Correct:A
20.Click on the EXHIBIT button and examine the table instance chart for the cars table. You query the database with this command: SELECT lot_no "Lot Number", COUNT(*) "Number of Cars Available" FROM cars WHERE model = 'Fire' GROUP BY lot_no HAVING COUNT(*) > 10 ORDER BY COUNT(*); Which clause restricts which groups are displayed?

A.SELECT lot_no "Lot Number", COUNT(*) "Number of Cars Available"
B.WHERE model = 'Fire'
C.HAVING COUNT(*) > 10
D.GROUP BY lot_no
E.ORDER BY COUNT(*)
Correct:C
21.Click on the EXHIBIT button and examine the employee table. You attempt to query the database with this command: SELECT dept_no, last_name, SUM(salary) FROM employee WHERE salary < 50000 GROUP BY dept_no ORDER BY last_name; Which clause causes an error?

A.FROM employee
B.WHERE salary < 50000
C.GROUP BY dept_no
D.ORDER BY last_name
Correct:C
22.Click on the EXHIBIT button and examine the structure of the BOOK_TITLE, COPY, and CHECK_OUT tables. You need to create the BOOKS_AVAILABLE view. These are the desired results: 1.Include the title of each book. 2.Include the availability of each book. 3.Order the results by the author. Evaluate this SQL statement: CREATE VIEW books_available AS SELECT b.title, c.availability FROMbook_title b, copy c WHEREb.id = c.title_id ORDER BY b.author; What does the statement provide?

A.all of the desired results
B.two of the desired results
C.one of the desired results
D.a syntax error
Correct:D
23.The view EMP_VIEW is created based on the table EMP as follows: CREATE OR REPLACE VIEW emp_view AS SELECT deptno, SUM(sal) TOT_SAL, COUNT(*) NUM_EMP FROM emp GROUP BY deptno; What happens when the following command is issued? UPDATE emp_view SET tot_sal = 20000 WHERE deptno = 10;
A.The base table can not be updated through this view.
B.The TOT_SAL column in EMP table is updated to 20000 for department 10.
C.The TOT_SAL column in EMP_VIEW view is updated to 20000 for department 10.
D.The SAL column in the EMP table is updated to 20000 for employees in department 10.
Correct:A
24.Which statement shows the view definition of the view EMP_VIEW that is created based on the table EMP?
A.DESCRIBE emp
B.DESCRIBE VIEW emp_view
C.SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';
D.SELECT view_text FROM my_views WHERE view_name = 'EMP_VIEW';
E.SELECT view_text FROM TABLE emp WHERE view_name = 'EMP_VIEW';
Correct:C
25.In which section of a PL/SQL block is a user-defined exception raised?
A.heading
B.executable
C.declarative
D.exception handling
Correct:B
26.Examine the table structures: EMP Table Name Null? Type ------------------------------- -------- -------- EMPNO NOT NULL NUMBER(4) NAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SALARY NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2) TAX Table Name Null? Type ------------------------------- -------- --------- TAXGRADE NUMBER LOWSAL NUMBER HIGHSAL NUMBER You want to create a report that displays the employee details along with the tax category of each employee. The tax category is determined by comparing the salary of the employee from the EMP table to the lower and upper salary values in the TAX table. Which SELECT statement produces the required results?
A.SELECT e.name, e.salary, t.taxgrade FROM emp e, tax t WHERE e.salary BETWEEN t.lowsal AND t.highsal;
B.SELECT e.name, e.salary, t.taxgrade FROM emp e, tax t WHERE e.salary >= t.lowsal AND <= t.highsal;
C.SELECT e.name, e.salary, t.taxgrade FROM emp e, tax t WHERE e.salary <= t.lowsal AND e.salary >= t.highsal;
D.SELECT e.name, e.salary, t.taxgrade FROM emp e, tax t WHERE e.salary IN t.lowsal AND t.highsal;
Correct:A
27.The PLAYER table contains these columns: ID NUMBER(9) NAME VARCHAR(2) MANAGER_ID NUMBER(9) In this instance, managers are players and you need to display a list of players. Evaluate these two SQL statements: SELECT p.name, m.name FROMplayer p, player m WHEREm.id = p.manager_id; SELECT p.name, m.name FROMplayer p, player m WHEREm.manager_id = p.id; How will the results differ?
A.Statement 1 will not execute; statement 2 will.
B.Statement 1 will execute; statement 2 will not.
C.Statement 1 is self-join; statement 2 is not.
D.The results will be the same, but the display will be different.
Correct:D
28.Which operator is NOT appropriate in the join condition of a non-equi join SELECT statement?
A.IN operator
B.LIKE operator
C.equal operator
D.BETWEEN x AND y operator
E.greater than or equal to operator
Correct:C
29.In which situation should you use an outer join query?
A.The employee table has two columns that correspond.
B.The employee and region tables have corresponding columns.
C.The employee and region tables have no corresponding columns.
D.The employee table column corresponding to the region table column contains null values for rows that need to be displayed.
Correct:D
30.You need to store currency data and you know that the data will always have two digits to the right of the decimal point. However, the number of digits to the left of the decimal place will vary greatly. Which datatype would be most appropriate to store this data?
A.NUMBER
B.NUMBER(p)
C.LONG
D.LONG RAW
Correct:A