Oracle Model Test 04
Settings
26/11/2011
- 1.Examine the structure of student table: Name Null Type STU ID NAME VARCHER2( 2N5O) T NULL NUMBER(3) ADDRESS VARCHER2(50) GRADUATION DATE Currently the table is empty. You have decided that null values should not be allowed for the NAME column. Which statement restricts NULL values from being entered into column?
- A.
A.ALTER TABLE student ADD CONSTRAINT name(NOT NULL);
- B.
B. ALTER TABLE student ADD CONSTRAINT NOT NULL (name);
- C.
C. ALTER TABLE student MODIFY(name varcher2(25) NOT NULL);
- D.
D. ALTER TABLE student MODIFY CONSTRAINT name(NOT NULL);
- 2.The employee table contains these columns. LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER(9) You need to display the names of the employees that are not assigned to the department. Evaluate this SQL statement. SELECT last_name, first_name FROM employee WHERE dept_id is NULL Which change should you make to achieve the desired result?
- A.
A. Create an outer join.
- B.
B. Change the column in the where condition.
- C.
C. Change the operator in the where condition
- D.
D. Add a second condition to the where condition
- 3.Evaluate this PL/SQL block. BEGIN FOR i IN 1..10 LOOP IF I=4 OR I=6 THEN null; ELSE INSERT INTO test(result) VALUES END IF; (I) ; COMMIT; END LOOP; ROLL BACK; END. How many values will be inserted into the TEST table?
- A.
A. 0
- B.
B. 4
- C.
C. 6
- D.
D. 8
- E.
E. 10
- 4.Examine the code: SET SERVER OUTPUT ON DECLARE v_char_val varchar2(100); BEGIN v_char_val:= ‘Hello World’, DBMS_OUTPUT.PUT_LINE(v_char_val); END SET SERVER OUTPUT OFF This code is stored in a script file name “myproc,sql”. Which statement executes the code in the script file?
- A.
A. Myproc.sql
- B.
B. RUN myproc,sql
- C.
C. START myproc.sql
- D.
D. EXECUTE myproc.sql
- E.
E. BEGIN myproc.sql END;
- 5.Which statement is valid within the executable section of Pl/SQL block?
- A.
A. BEGIN emp_rec emp%ROWTYPE END;
- B.
B. WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT.LINE(‘No records found’);
- C.
C. Select ename,sal into v_ename,v_sal from emp where empno=101;
- D.
D. Procedure cal_max(n1 NUBER n2 NUMBER, p_max OUT NUMBER) IBS EGIN If n1>n2 then p_max:=n1; Else p_max=n2; END.
- 6.Your company wants to get each employee a $100 salary increment. You need to evaluate the results, from the EMP table prior to actual modification. If you do not want to store the result in the database which statement is valid?
- A.
A. You need to add a column to the EMP table.
- B.
B. You need to give the arithmetic expression that involves the salary increment in the set clause of the update statement.
- C.
C. You need to give the arithmetic expression that involves the salary increment in the select clause of the select statement.
- D.
D. You need to give the arithmetic expression that involves the salary increment in the update clause of the select statement
- E.
E. You need to give the arithmetic expression that involves the salary increment in the display clause of the select statement
- 7.Examine this block of code: SET OUTPUT 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_Sal); DBMS_OUTPUT.PUT_LINE(‘Value of V_Sal is ‘|| TO_CHAR (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 server OUTPUT if What is the result of executing this block of code?
- A.
A. PLS-00201: identifier ‘Y’ must be declared.
- B.
B. Value of V_found is YES Value of V_sal is 1000 Value of V_found is TRUE
- C.
C. Value of V_found is YES Value of V_found is 1000 Value of V_found is TRUE Value of Y is 20
- D.
D. PLS-00201: identifier ‘V_sal’ must be declared PLS-00201: identifier ‘Y’ must be declared
- E.
E. Value of V_found is YES Value of V_sal is 1000 Value of V_found is TRUE Value of Y is 20
- 8.Which statement about multiple sub-queries is True?
- A.
A. A pair wise comparison produces a cross product.
- B.
B. A non-pair wise comparison produces a cross product.
- C.
C. In a pair wise subquery, the values returned from the subquery are compared individually to the values in the outer query.
- D.
D. In a non-pair wise subquery, the values returned from the subquery are compared as a group to the values in the outer query.
- 9.The employee table contains these columns: ID NUMBER(9) LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) COMMISSION NUMBER(7,2) You need to display the current commission for all employees. Desired results are: 1. Display the commission multiplied by 1.5 48 2. Exclude employees with zero commission. 3. Display a zero for employees with null commission value. Evaluate this SQL statement. SELECT id, last_name, first_name, commission*1. 5 FROM employee WHERE commission <>0; Which of the desired results does the statement provide?
- A.
A. All the desired results.
- B.
B. Two of the desired results.
- C.
C. One of the desired results
- D.
D. A syntax error.
- 10.Given the executable section of a PL/SQL block FROM 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.
A. The cursor needs to be opened.
- B.
B. Terminating conditions are missing.
- C.
C. No FETCH statements were issued.
- D.
D. The cursor does not need to be explicitly closed.
- 11.The structure of the DEPT table as: Name Null? Type DEPT NO Not NULL Number(25) DNAME VARCHER2(14) LOC VARCHER2(13) Examine the declaration section. DECLARE TYPE dept_record_type is RECORD (dno NUMBER, name VARCHER(20)); dept_rec dept_record_type; How do you retrieve an entire row of the DEPT table using the DEPT-REC variable?
- A.
A. SELECT* INTO dept_rec FROM dept WHERE dept no=10;
- B.
B. SELECT deptno,dname,loc INTO dept_rec FROM dept WHERE dept no=10;
- C.
C. You can’t retrieve the entire row using the DEPT_REC variable declared in the code
- D.
D. SELECT* INTO dept_rec.dno,dept_rec.name,dept_rec. FROM dept WHERE dept no=10;
- 12.Which alter command would you use to reinstate a disabled primary constraint?
- A.
A. ALTER TABLE ENABLE PRIMARY KEY(ID)
- B.
B. ALTER TABLE CARS ENABLE CONSTRAINT cars_id_pk.
- C.
C. ALTER TABLE CARS ENABLE PRIMARY KEY(id)CASCADE;
- D.
D. ALTER TABLE CARS ADD CONSTRAINT cards_id_pk PRIMARY KEY(id);
- 13.In the declarative section of a PL/SQL block, you created but did not initialize a number variable. When the block executes what will be the initial value of the variable?
- A.
A. 0.
- B.
B. Null.
- C.
C. It depends on the scale and precision of the variable
- D.
D. The block will not execute because the variable was not initialized.
- 14.Examine the code: DECLARE. CURSOR emp_cursor IS SELECT ename,deptno FROM emp; emp_rec emp_cursor %ROWTYPE BEGIN OPEN emp_cursor LOOP FETCH emp_cursor INTO emp_rec EXIT WHEN emp_cursor NOT FOUND; INSERT INTO temp_emp(name’dno) VALUES(emp_rec.ename,emp_rec deptno); END LOOP; CLOSE emp_cursor; END; Using a cursor FOR loop,which PL/SQL block equivalent to the above code?
- A.
A. DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; BEGIN FOR emp-rec IN emp-cursor LOOP INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp-re.deptno); END LOOP END;
- B.
B. DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; BEGIN FOR emp-rec IN emp-cursor LOOP OPEN emp-cursor; INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp_rec.deptno); END LOOP END;
- C.
C. DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; BEGIN FOR emp-rec IN emp-cursor LOOP OPEN emp-cursor; INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp-re.deptno); END LOOP CLOSE emp-cursor; END;
- D.
D. DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; emp-rec emp-cursor%ROWTYPE; BEGIN FETCH emp-cursor INTO emp-rec; FOR emp-recIN emp-cursor LOOP INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp-re.deptno); END LOOP END;
- 15.Which data dictionary view contains the definition of a view?
- A.
A. MY_VIEWS.
- B.
B. USER_VIEWS.
- C.
C. SYSTEM_VIEWS.
- D.
D. USER_TAB_VIEWS
- 16.Examine this code : SELECT employee.ename FROM employee WHERE employee.empno NOT IN (SELECT manager.mgr FROM emp manager); What is not in operator equivalent to the above query?
- A.
A. !=
- B.
B. ALL
- C.
C. !=ALL
- D.
D. NOT LIKE.
- 17.The employee table has ten columns. Since you often query the table with condition based on four or more columns, you created an index on all the columns in the table. Which result will occur?
- A.
A. Updates on the table will be slower.
- B.
B. The speed of inserts will be increased.
- C.
C. All queries on the table will be faster.
- D.
D. The size of the employee table will be increased.
- 18.Examine the table instance chart for the patient table. Column name Id_number Last_name first_name birth_date doctor_id Key type PK Nulls/Unique NN, UU FK table NN NN FK column Data type Length DOCTOR ID_NUMBER NUM 10 25 25 VARCHAR2 VARCHAR2 DATE NUM 10 You created the patient_vu view based on the id_number and last_name columns from the patient table. What is the best way to modify the view to contain only those patients born in 1997?
- A.
A. Replace the view adding a WHERE clause.
- B.
B. Use the ALTER command to add WHERE clause to verify the time.
- C.
C. Drop the patient_vu then create a new view with a WHERE clause.
- D.
D. Drop the patient_vu then create a new view with a HAVING clause
- 19.Which script would you use to query the data dictionary to view only the names of the primary key constraints using a substitution parameter for the table name?
- A.
Which script would you use to query the data dictionary to view only the names of the primary key constraints using a substitution parameter for the table name?
- B.
B. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’) SELECT constraint_name FROM user_constraint WHERE table_name=upper(‘&table’) AND constraint_type= ‘PRIMARY’;
- C.
C. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’) SELECT constraint_name,constraint_type FROM user_constraint WHERE table_name=upper(‘&table’);
- D.
D. SELECT AcoCnCstEraPiTnt TnAaBmLeE PROMPT(‘table to view primary key constraint:’) FROM user_cons_columns WHERE table_name=upper(‘&table’) AND constraint_type= ‘P’;
- 20.Examine the structure of the product and the part tables. There points a line from id PK to product_id PRODUCT id PK Name PART id PK Name Product_id Cost You issue this SQL statement: SELECT pr.name FROM part pt, product pr WHERE pt.product_id(+)=pr.id; What is the result?
- A.
A. An error is generated
- B.
B. A list of product names is displayed
- C.
C. A list of all products is displayed for product with parts.
- D.
D. A list of product is displayed for parts that have product assigned
- 21.Which operator is not appropriate in the joined condition of none equijoin select statement?
- A.
A. In operator.
- B.
B. Like operators.
- C.
C. Equal operators
- D.
D. Between x and y operator.
- E.
E. Greater than and equal to operator.
- 22.You issue this command: CREATE SYNONYM emp FOR ed.employee; Which task has been accomplished?
- A.
A. The need to qualify an object name with its schema was eliminated for user Ed.
- B.
B. The need to qualify an object name with its schema was eliminated for only you.
- C.
C. The need to qualify an object name with its schema was eliminated for all users.
- D.
D. The need to qualify an object name with its schema was eliminated for users with access.
- 23.Evaluate these two SQL Commands 1 SELECT distinct object_type FROM user_object; 2 SELECT object_type FROM all_objects; How will the results differ?
- A.
A. Statement1 will display the distinct object types in the database. Statement2 will display all the object types in the database.
- B.
B. Statement1 will display the distinct types owned by the user. Statement2 will display all the object types in the database.
- C.
C. Statement1 will display the distinct object type owned by the user. Statement2 will display the object types the user can access
- D.
D. Statement1 will display the distinct object types that user can access. Statement2 will display all the object types that the user owns
- 24.Which table name is valid?
- A.
A. #_667.
- B.
B. Number.
- C.
C. Catch_#22.
- D.
D. 1996_invoices
- E.
E. Invoices-1996.
- 25.As a DBA you have just created a user account for employee Smith by using the create user comma nd. Smith should be able to create tables and packages in his schema. Which command will the DBA need to execute next so that Smith can perform his task successfully?
- A.
A. GRANT CREATE TABLE, CREATE PACKAGE TO smith;
- B.
B. GRANT CREATE TABLE, CREATE PROCEDURE TO smith
- C.
C. GRANT CREATE SESSION,CREATE TABLE,CREATE PROCEDURE TO smith
- D.
D. GRANT CREATE CONNECT,CREATE TABLE,CREATE PROCEDURE TO smith;
Back to top