Online Tutorials & Training Materials | STechies.com
Register Login

Oracle Certified Professional Exams IZ007 Questions Set 36

|| || 1

Oracle Certified Professional Exams IZ007 Questions Set 36
Stechies

Q. What does the TRUNCATE statement do?

A. removes the table

B. removes all rows from a table

C. shortens the tale to 10 rows

D. removes all columns from a table

E. removes foreign keys from a table

Answer;  B


Q. the ORDERS table has these columns

ORDER_ID            NUMBER(4) NOT NULL
CUSTOMER_ID    NUMBER(12) NOT NULL
ORDER_TOTAL   NUMBER(10,2) 

The ORDERS table tracks the Order number, the order total and the customer to whom the Order belongs.  Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 200.00 dollars? (Choose Two).

A.  SELECT customer_id, order_id, order_total
FROM  orders
RANGE ON order_total (100 AND 2000) INCLUSIVE

B.  SELECT customer_id, order_id, order_total
FROM orders
HAVING order total BETWEEN 100 and 2000

C.  SELECT customer_id, order_id, order_total
FROM orders
WHERE order_total BETWEEN 100 and 2000

D.  SELECT customer_id, orde_id, order_total
FROM  orders
WHERE  order_total >= 100 and <=2000

E.  SELECT customer_id, order_id, order _total
FROM orders
WHERE order_total>= 100 and order_total <=2000.
 

Answer: CE


Q. Which is an /SQL * Plus command?

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer; D


Q. Which SELECT statement should you use to extract the year form the system date and display it in the format “1998”?

A. SELECT TO_CHAR(SYSDATE, ‘yyyy’)
FROM dual

B. SELECT TO_DATE(SYSDATE,’yyyy’)
FROM dual

C. SELECT DECODE (SUBSTR (SYSDATE, 8), ‘YYYY’)
FROM dual

D. SELECT DECODE  (SUBSTR (SYSATE, 8),’year’)
FROM dual

E. SELECT TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’)
FROM dual

Answer:  A


Q. The EMPLOYEES table contains these columns:
LAST_NAME                  VARCHAR2(25)
SALARY                           NUMBER(6,2)
COMMISSION_PCT      NUMBER(6)

You need to write a query that will produce these results:

1. Display the salary multiplied by the commission_pct
2. Exclude employees with a zero commission_pct
3. Display a zero for employees with a null commission value

Evaluate the SQL statement:
SELECT LAST_NAME, SALARY * COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL; What does the statement provide?

A. all of the desired results

B. two of the desired results

C. one of the desired results

D. an error statement

Answer: C


Q. A subquery can be used to _________.

A. create groups of data

B. sort data in a specific order

C. convert data to a different format

D. retrieve data based on an unknown condition

Answer:  D


Q. Which clause should you use to exclude group results?

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY

Answer:  B


Q. Scott issues the SQL statements:
CREATE TABLE dept
(deptno  number(2)
dname  VARCHAR2(14)
loc  VARCHAR2(13)
);
GRANT SELECT
ON DEPT
TO SUE;
If Sue needs to select from Scott’s DEPT table, which command should she use?

A. SELECT *
FROM DEPT

B. SELECT *
FROM SCOTT. DEPT.

C. SELECT *
FROM DBA.SCOTT.DEPT.

D. SELECT *
FROM ALL_USERS
WHERE USER_NAME = ‘SCOTT’
AND TABLE NAME= ‘DEPT’;

Answer:  B


Q. What is true about joining tables through an equation?

A. you can join a maximum of two tables through an equation

B. you can join a maximum, of two columns through an equation

C. you specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.

D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

Answer: E


Q. You need to calculate the total of all salaries in the accounting department.  Which group function should you use?

A. MAX

B. MIN

C. SUM

D. COUNT

E. TOTAL

F. LARGEST

Answer: C


Q. The EMP table has these columns:
ENAME   VARCHAR2(35)
SALARY  NUMBER (8,2)
HIRE_DATE DATE

Management wants a list of names of employees who have been with the company for more than five yeas.  Which SQL statement displays the required results?

A. SELECT ENAME
FROM EMP
WHERE SYSDATE-HIRE_DATE>5

B. SELECT ENAME
FROM EMP
WHERE HIRE_DATE-SYSDATE > 5

C. SELECT ENAME
FROM EMP
WHERE (SYSDATE-_DATE)/365 > 5

D. SELECT ENAME
FROM EMP
WHERE (SYSDATE-HIRE_DATE)* 365 > 5

Answer:  C


Q. You would like to display the system date in the format *Monday, 01 June, 2001*
Which SELECT statement  should you use?

A. SELECT TO_DATE (SYSDATE, ‘FMDAY, DD Month, YYYY’)
FROM dual

B. SELECT TO_CHAR(SYSDATE, ‘FMDD, DY Month ‘YYY’)
FROM dual

C. SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’)
FROM dual

D. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDD Month, YYYY’)
FROM dual

E. SELECT TO_DATES(SYSDATE,’FMDY, DDD Month, YYYY’)
FROM dual

Answer:  C


Q. The CUSTOMERS table has these columns:
CUSTOMER_ID               NUMBER (4) NOT NULL
CUSTOMER_NAME        VARCHAR2(100)
STREET_ADDRESS       VARCHAR2(150)
CITY_ADDRESS              VARCHAR2(50)
STATE_ADDRESS           VARCHAR2(50)
PROVINCE_ADDRESS   VARCHAR2(50)
COUNTRY_ADDRESS    VARCHAR2(50)
POSTAL_CODE                VARCHAR2(12)
CUSTOEMR_PHONE      VARCHAR2(20)
Which statement finds the rows in the CUSTOMERS table that do not have a postal code

A. SELECT customer_id, customer_name
FROM customers
WHERE postal_code CONTAINS NULL

B. SELECT  customer_id, customer name
FROM  customers
WHERE posta_code=’______________’

C. SELECT customer_id, customer_name
FROM customers
WHERE postal_code IS NULL

D. SELECT customer_id, customer_name
FROM customers
WHERE postal code IS NVL

E. SELECT customer_id, customer_name
FROM customers
WHERE postal_code=NULL

Answer:  C


Q. 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 al the parentheses from the calculation?

A. The value displayed in the CALC_VALUE column will be lower

B. The value displayed in the CALC_VALUE column will be higher

C. There will be no difference in the value displayed in the CALC_VALUE column

D. An error will be reported.

Answer:  C


Q. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator”=” What happens when the main query is executed?

A. the main query executes with the first value returned by the subquery

B. the main query executes with the last value returned by the subquery

C. the main query executes with all the values returned by the subquery

D. the main query fails because the multiple-row subquery cannot be used with the comparison operator.

E. You cannot define multiple-row subquery in the WHERE clause of a SQL query

Answer:  D


Q. which three statements correctly describe the functions and use of constraints? (Choose three)

A. constraints provide data independence

B. constraint make complex queries easy

C. constraints enforce rules at the view level

D. constraints enforce rules at the table level

E. constraints prevent the deletion of a table if there are dependencies

F. constraints prevent the deletion of an index if there are dependencies

Answer:  ACD


Q. Which two are character manipulation functions? (Choose two)

A. TRIM

B. REPLACE

C. TRUNC

D. TO_DATE

E. MOD

F. CASE

Answer:  AB


Q. You need to create a view EMP_VU.  The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use tocrete the view EMP_VU?

A. CREATE VIEW emp_vu AS SELECT  employees WHERE department_id 1N (10,20)

B. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH READ ONLY

C. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION

D. CREATE FORCE VIEW emp_vu AS SELECT  * FROM employees WHERE department_id IN (10, 20) NO UPDATE

Answer:  C


Q. Evaluate these two SQL statemens
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC

What is true about them?

A. the two statements produce identical results

B. the second statement returns a syntax error

C. there is no need to specify DESC because the results are sorted in descending order by default

D. the two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statements

Answer: A


Q. Which two statements accurately describe a role? (Choose two)

A. a role can be given to a maximum of 1000 users

B. a user can have access to a maximum of 10 roles

C. a role can have a maximum of 100 privileges contained in it.

D. Privileges are given to a role by using the CREATE ROLE statement.

E. A role is a named group of related privileges that can be granted to the user

F. A user can have access to several roles, and several users can be assigned the same role.

Answer:  EF


Q. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

A. MERGE

B. INSERT

C. UPDATE

D. ADD

E. ENTER

F. You cannot enter the phone number for the existing employee records

Answer: C

 

 

Continue.....


Comments

  • 26 Oct 2010 1:28 am
    there are some answer of question which are incorrect..............

Related Articles