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.
- 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;
- 26.
- A.
A. 1.
- B.
B. 2.
- C.
C. 3.
- D.
D. 4.
- E.
E. 5.
- F.
F. 7.
- 27.Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? (Choose four)
- A.
A. You cannot roll back this statement.
- B.
B. All pending transactions are committed.
- C.
C. All views based on the DEPT table are deleted.
- D.
D. All indexes based on the DEPT table are dropped.
- E.
E. All data in the table is deleted, and the table structure is also deleted.
- F.
F. All data in the table is deleted, but the structure of the table is retained.
- G.
G. All synonyms based on the DEPT table are deleted.
- 28.Which three statements correctly describe the functions and use of constraints? (Choose three)
- A.
A.constraints provide data independence
- B.
B. constraint make complex queries easy
- C.
C. constraints enforce rules at the view level
- D.
D. constraints enforce rules at the table level
- E.
E. constraints prevent the deletion of a table if there are dependencies
- F.
F. constraints prevent the deletion of an index if there are dependencies
- 29.Top N analysis requires _____ and _____. (Choose two.)
- A.
A. the use of rowed
- B.
B. a GROUP BY clause
- C.
C. an ORDER BY clause
- D.
D. only an inline view
- E.
E. an inline view and an outer query
- 30.Which two statements are true about WHERE and HAVING clauses? (Choose two)
- A.
A. A WHERE clause can be used to restrict both rows and groups.
- B.
B. A WHERE clause can be used to restrict rows only.
- C.
C. A HAVING clause can be used to restrict both rows and groups.
- D.
D. A HAVING clause can be used to restrict groups only.
- E.
E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
- F.
F. A HAVING clause CANNOT be used in subqueries.
- 31.If errors occur during the compilation of a trigger you can go into SQL* PLUS and query the USER_TRIGGERS data dictionary view to see the compilation errors
- A.
True
- B.
False
- 32.For which three tasks would you use the where clause? (Choose three)
- A.
A. Compare two values
- B.
B. Display only unique data.
- C.
C. Designate a table location.
- D.
D. Restrict the rows displayed.
- E.
E. Restrict the output of the group function.
- F.
F. Only display data greater than a specified value.
- 33.
- A.
A. ‘03-jul-96’ + 7
- B.
B. ‘03-jul-96’ – 12
- C.
C. ‘03-jul-96’ + (12/24)
- D.
D. ‘03-jul-96’ – ‘04-jul-97’
- E.
E. (‘03-jul-96’ – ‘04-jul-97’) /7
- F.
F. (‘03-jul-96’ – ‘04-jul-97’) /12
- 34.Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)
- A.
A. TIMESTAMP
- B.
B. INTERVAL MONTH TO DAY
- C.
C. INTERVAL DAY TO SECOND
- D.
D. INTERVAL YEAR TO MONTH
- E.
E. TIMESTAMP WITH DATABASE TIMEZONE
- 35..Examine the SQL statements that creates ORDERS table: CREATE TABLE orders ( SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL STATUS VARCHAR2(10) CHECK (status IN ('CREDIT','CASH')), p[ REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order id, order date)); For which columns would an index be automatically created when you execute the above SQL statement? (Choose two)
- A.
A. SER_NO
- B.
B. ORDER_ID
- C.
C. STATUS
- D.
D. PROD_ID
- E.
E. ORD_TOTAL
- F.
F. Composite index on ORDER_ID and ORDER_DATE
- 36.Which three SELECT statements displays 2000 in the format "$2,000.00"? (Choose three)
- A.
A. SELECT TO CNAR(2000, '$#,###.##') FROM dual;
- B.
B. SELECT TO CNAR(2000, '$0,000.00') FROM dual;
- C.
C. SELECT TO CNAR(2000, '$9,999.00') FROM dual;
- D.
D. SELECT TO CNAR(2000, '$9,999.99') FROM dual;
- E.
E. SELECT TO CNAR(2000, '$2,000.00') FROM dual;
- F.
F. SELECT TO CNAR(2000, '$N,NNN.NN') FROM dual;
- 37.Contents of the packages can be shared by many applications
- A.
True
- B.
False
- 38.YOU can acheive information hiding by making package constructs private.
- A.
True
- B.
False
- 39.A CALL statement inside the trigger body enables you to call a stored procedure
- A.
True
- B.
False
- 40.Constants , a programmed constructs can be grouped witihn the packages
- A.
True
- B.
False
- 41.You need to analyze how long your orders to be shipped from the date that the order is placed. To do this you must create a report that displays the customer number, date order, date shipped and the number of months in whole numbe rs from the time the order is placed to the time the order is shipped. Which statement produces the required results?
- A.
A. SELECT custid, orderate, shipdate, ROUND(MONTHS_BETWEEN(shipdate,orderate)) “Time Taken” FROM ord;
- B.
B. SELECT custid, orderate, shipdate, ROUND(DAYS_BETWEEN(shipdate,orderate))/30. FROM ord;
- C.
C. SELECT custid, orderate, shipdate, 4 ROUND OFF (shipdate-orderate) “Time Taken” FROM ord;
- D.
D. SELECT custid, orderate, shipdate, MONTHS_BETWEEN (shipdate,orderate) “Time Taken”. FROM ord;
- 42.The employee table contains these columns: FIRST-NAME VARCHER2(25) COMISSION NUMBER(3,2) Evaluate this SQL statement SELECT first-name,commission FROM employee WHERE commission= (SELECTcomission FROM employee WHERE UPPER(first-name)= ‘scott’) Which statement will cause this statement to fail?
- A.
A. Scott has a null commission resolution.
- B.
B. Scott has a zero commission resolution.
- C.
C. There is no employee with the first name Scott.
- D.
D. The first name values in the data base are in the lower case.
- 43.In SQL,Null values are displayed last in the ascending sequences.
- A.
True
- B.
False
- 44.The SQL results are sorted by the first column in the SELECT list, if the ORDER BY clause is not provided.
- A.
True
- B.
False
- 45.You cannot sort query results by a column that is not included in the SELECT list.
- A.
True
- B.
False
- 46.The structure of the DEPT table is as follows: NAME NULL TYPE Deptno Dname VARCHAR2( 1N4o)t Null NUMBER(2) Loc VARCHAR2(13) Examine the code Declare Type dept_record_type is record (dno NUMBER, name VARCHAR2(20)); dept_rec dept_record; Begin Select deptno, dname INTO dept_rec FROM dept WHERE deptno=10; END Which statement displays the name of 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));
- 47.Which privilege concerns with system level security?
- A.
Drop any table.
- B.
DELETE
- C.
ALTER
- D.
INDEX
- E.
UPDATE
- 48.You have a view card ANN_SAL that is based on the employee table. The structure of the ANN_SAL view is: NAME NULL TYPE EMPNO NOT NULL NUMBER(4) YEARLY_SAL NUMBER(9,2) MONTHLY_SAL NUMBER(9,2) Which statement retrieves the data from the ANN_SAL view?
- A.
SELECT * FROM ANN_SAL
- B.
SELECT * FROM EMPLOYEE
- C.
SELECT * FROM VIEW ANN_SAL
- D.
SELECT * FROM VIEW ANN_SAL IS DON EMPLOYEE
- 49.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=250?
- A.
250
- B.
500
- C.
750
- D.
1000
- 50.You do not explicitly open, fetch or close a cursor within a cursor for loop.
- A.
True
- B.
False
Back to top