Oracle Quiz-1
- 1.
The DBA issues this SQL command: CREATE USER scott IDENTIFIES by tiger; What privileges does the user Scott have at this point?
- A.
No privileges.
- B.
Only the SELECT privilege
- C.
Only the CONNECT privilege.
- D.
All the privileges of a default user
Correct Answer
A. No privileges. -
- 2.
Which statement creates a new user?
- A.
. CREATIVE USER susan;
- B.
CREATIVE OR REPLACE USER susan;
- C.
CREATE NEW USER susan DEFAULT
- D.
CREATE USER susan IDENTIFIED BY blue;
Correct Answer
D. CREATE USER susan IDENTIFIED BY blue; -
- 3.
Which of these are optional in a PL/SQL block
- A.
A. declare
- B.
B. Executable
- C.
C. Exception Handling
Correct Answer
C. C. Exception Handling -
- 4.
.Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?
- A.
SELECT COUNT(*) FROM employees WHERE last_name='Smith';
- B.
SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
- C.
SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
- D.
SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
- E.
SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith'
Correct Answer
D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith'; -
- 5.
You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create? . . .
- A.
A. A cursor for loop
- B.
B. A multiple selection cursor
- C.
C. A cursor for each active set
- D.
D. A cursor that uses parameters
Correct Answer
D. D. A cursor that uses parameters -
- 6.
Correct Ordering of the clause ‘Where, having, group by,distinct,union,order by’
Correct Answer
distinct, Where ,group by, having, order by, union - 7.
When creating a function in SQL *Plus, you receive this message: .Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?
- A.
A. SHOW FUNCTION_ERROR
- B.
B. SHOW USER_ERRORS
- C.
C. SHOW ERRORS
- D.
D. SHOW ALL_ERRORS
Correct Answer
C. C. SHOW ERRORS -
- 8.
Where should the group by clause be placed in the SQL query..............
Correct Answer
After Where clause - 9.
Will Delete from tablename work without where clause…
Correct Answer
Ans—Yes - 10.
Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?
- A.
A. EXECUTE UPD_BAT_STAT(V_ID);
- B.
B. UPD_BAT_STAT(V_ID);
- C.
C. RUN UPD_BAT_STAT(V_ID);
- D.
D. START UPD_BAT_STAT(V_ID);
Correct Answer
B. B. UPD_BAT_STAT(V_ID); -
- 11.
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 discounts?
- A.
A. A stored procedure on the server.
- B.
B. A block of code in a PL/SQL library.
- C.
C. A standalone procedure on the client machine.
- D.
D.A block of code in the body of the program unit ORDERTOTAL
- E.
E. A local subprogram defined within the program unit ORDERTOTAL
Correct Answer
E. E. A local subprogram defined within the program unit ORDERTOTAL -
- 12.
. What is true about stored procedures? . Answer C
- A.
A. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.
- B.
B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification
- C.
C. A stored procedure must have at least one executable statement in the procedure body.
- D.
D. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
Correct Answer
C. C. A stored procedure must have at least one executable statement in the procedure body. -
- 13.
. Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID EXCEPTION WHEN STATS_EXI TS_EXCEPTI ON THEN DBMS_OUTPUT. PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table);END; What prevents this procedure from being created successfully?
- A.
A. A comma has been left after the STATS_EXIST_EXCEPTION exception.
- B.
B. The STATS_EXIST_EXCEPTION has not been declared as a number.
- C.
C. The STATS_EXIST_EXCEPTION has not been declared as an exception.
- D.
D. Only predefined exceptions are allowed in the EXCEPTION section.
Correct Answer
C. C. The STATS_EXIST_EXCEPTION has not been declared as an exception. -
- 14.
Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?
- A.
A. The value displayed in the CALC_VALUE column will be lower.
- B.
B. The value displayed in the CALC_VALUE column will be higher.
- C.
C. There will be no difference in the value displayed in the CALC_VALUE column.
- D.
An error will be reported.
Correct Answer
C. C. There will be no difference in the value displayed in the CALC_VALUE column. -
- 15.
32. Which two statements about sequences are true? (Choose two)
- A.
A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.
- B.
B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
- C.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence
- D.
D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column
- E.
E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
- F.
F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Correct Answer(s)
B. B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C. C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence -
- 16.
. Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two)
- A.
A. SUM(start_date)
- B.
B. AVG(start_date)
- C.
C. COUNT(start_date)
- D.
D. AVG(start_date, end_date)
- E.
E. MIN(start_date)
- F.
F. MAXIMUM(start_date)
Correct Answer(s)
C. C. COUNT(start_date)
E. E. MIN(start_date) -
- 17.
Mark for review The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8,2) Evaluate this SQL statement: SELECT * FROM PRODUCTS ORDER BY price, product_name; What is true about the SQL statement?
- A.
A. The results are not sorted.
- B.
B. The results are sorted numerically.
- C.
C. The results are sorted alphabetically.
- D.
D. The results are sorted numerically and then alphabetically.
Correct Answer
D. D. The results are sorted numerically and then alphabetically. -
- 18.
Mark for review Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?
- A.
A. 46 and 45
- B.
B. 46 and 45.93
- C.
C. 50 and 45.93
- D.
D. 50 and 45.9
- E.
E. 45 and 45.93
- F.
F. 45.95 and 45.93
Correct Answer
C. C. 50 and 45.93 -
- 19.
What is true about updates through a view? . .
- A.
A. You cannot update a view with group functions
- B.
B. When you update a view group functions are automatically computed
- C.
C. When you update a view only the constraints on the underlying table will be in effect.
- D.
D. When you update a view the constraints on the views always override the constraints on the underlying tables.
Correct Answer
A. A. You cannot update a view with group functions -
- 20.
What are two reasons to create synonyms? (Choose two.) . . . Answer: C,E
- A.
A. You have too many tables
- B.
B. Your tables are too long.
- C.
C. Your tables have difficult names
- D.
D. You want to work on your own tables.
- E.
E. You want to use another schema's tables.
- F.
F. You have too many columns in your tables
Correct Answer(s)
C. C. Your tables have difficult names
E. E. You want to use another schema's tables. -
- 21.
24.Which SQL statement returns a numeric value?
- A.
A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
- B.
B. SELECT ROUND(hire_date)FROM EMP; ;
- C.
C. SELECT sysdate-hire_date FROM EMP
- D.
D. SELECT TO_NUMBER(hire_date + 7)FROM EMP;
Correct Answer
C. C. SELECT sysdate-hire_date FROM EMP -
- 22.
In which four clauses can a subquery be used? (Choose four.)
- A.
A. in the INTO clause of an INSERT statement
- B.
B. in the FROM clause of a SELECT statement
- C.
C. in the GROUP BY clause of a SELECT statement
- D.
E. in the SET clause of an UPDATE statement
- E.
F. in the VALUES clause of an INSERT statement
Correct Answer(s)
A. A. in the INTO clause of an INSERT statement
B. B. in the FROM clause of a SELECT statement
D. E. in the SET clause of an UPDATE statement
E. F. in the VALUES clause of an INSERT statement -
- 23.
What is true of using group functions on columns that contain NULL values?
- A.
A. Group functions on columns ignore NULL values
- B.
B. Group functions on columns returning dates include NULL values.
- C.
C. Group functions on columns returning numbers include NULL values
- D.
D. Group functions on columns cannot be accurately used on columns that contain NULL values
- E.
E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
Correct Answer
A. A. Group functions on columns ignore NULL values -
- 24.
Examine the SQL statement 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')), PROD_ID NUMBER 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
Correct Answer(s)
A. A. SER_NO
F. F. composite index on ORDER_ID and ORDER_DATE -
- 25.
Which object privileges can be granted on a view?
- A.
A. none
- B.
B. DELETE, INSERT,SELECT
- C.
C. ALTER, DELETE, INSERT, SELECT
- D.
D. DELETE, INSERT, SELECT, UPDATE
Correct Answer
D. D. DELETE, INSERT, SELECT, UPDATE -
- 26.
19.Which statement describes the ROWID data type?
- A.
A. binary data up to 4 gigabytes
- B.
B. character data up to 4 gigabytes
- C.
C. raw binary data of variable length up to 2 gigabytes
- D.
D. binary data stored in an external file, up to 4 gigabytes
- E.
E. a hexadecimal string representing the unique address of a row in its table
Correct Answer
E. E. a hexadecimal string representing the unique address of a row in its table -
- 27.
Which four are attributes of single row functions? (Choose four.)
- A.
A. cannot be nested
- B.
B. manipulate data items
- C.
C. act on each row returned
- D.
D. return one result per row
- E.
E. accept only one argument and return only one value
- F.
F. accept arguments which can be a column or an expression
Correct Answer(s)
B. B. manipulate data items
C. C. act on each row returned
D. D. return one result per row
F. F. accept arguments which can be a column or an expression -
- 28.
Which describes the default behavior when you create a table?
- A.
A. The table is accessible to all users.
- B.
B. Tables are created in the public schema
- C.
C. Tables are created in your schema
- D.
D. Tables are created in the DBA schema
- E.
E. You must specify the schema when the table is created.
Correct Answer
C. C. Tables are created in your schema -
- 29.
Which four are valid Oracle constraint types? (Choose four.)
- A.
A. CASCADE
- B.
B. UNIQUE
- C.
C. NONUNIQUE
- D.
D. CHECK
- E.
E. PRIMARY KEY
- F.
F. CONSTANT
- G.
G. NOT NULL
Correct Answer(s)
B. B. UNIQUE
D. D. CHECK
E. E. PRIMARY KEY
G. G. NOT NULL -
- 30.
You own a table called EMPLOYEES with this table structure: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement? DELETE employees; . .
- A.
A. You get an error because of a primary key violation.
- B.
B. The data and structure of the EMPLOYEES table are deleted.
- C.
C. The data in the EMPLOYEES table is deleted but not the structure
- D.
D. You get an error because the statement is not syntactically correct
Correct Answer
C. C. The data in the EMPLOYEES table is deleted but not the structure -
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 02, 2012Quiz Edited by
ProProfs Editorial Team -
Mar 01, 2012Quiz Created by
323378
Back to top