Q1. - (Topic 1) 

User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database. 

Which SQL statement can Mary use to accomplish that task? 

A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu; 

B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu); 

C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu; 

D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER; 

E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu; 

F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS; 

View Answer

Q2. - (Topic 2) 

The DBA issues this SQL command: 

CREATE USER Scott 

IDENTIFIED 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. 

View Answer

Q3. - (Topic 2) 

Which two statements are true regarding tables? (Choose two.) 

A. A table name can be of any length. 

B. A table can have any number of columns. 

C. A column that has a DEFAULT value cannot store null values. 

D. A table and a view can have the same name in the same schema. 

E. A table and a synonym can have the same name in the same schema. 

F. The same table name can be used in different schemas in the same database. 

View Answer

Q4. - (Topic 2) 

View the Exhibit and examine the data in the PROMOTIONS table. 

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr. 

You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000. 

Which SQL statement would you use? 

A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00' 

B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000' 

C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00' 

D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00' 

View Answer

Q5. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. 

You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task? 

A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC; 

B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC; 

C. SELECT cust_city, cust_income_level, cust_last_name 

FROM customers 

ORDER BY (cust_city, cust_income_level) DESC; 

D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC; 

View Answer

Q6. - (Topic 2) 

View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables. 

Evaluate the following SQL command: 

SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id; 

Which two statements are true regarding the outcome of the above query? (Choose two.) 

A. It locks all the rows that satisfy the condition in the statement. 

B. It locks only the columns that satisfy the condition in both the tables. 

C. The locks are released only when a COMMIT or ROLLBACK is issued. 

D. The locks are released after a DML statement is executed on the locked rows. 

View Answer

Q7. - (Topic 1) 

Examine the structure of the MARKS table: 

Exhibit: 

Which two statements would execute successfully? (Choose two.) 

A. SELECT student_name,subject1 

FROM marks 

WHERE subject1 > AVG(subject1); 

B. SELECT student_name,SUM(subject1) 

FROM marks 

WHERE student_name LIKE 'R%' 

C. SELECT SUM(subject1+subject2+subject3) 

FROM marks 

WHERE student_name IS NULL; 

D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) 

FROM marks 

WHERE subject1 > subject2; 

View Answer

Q8. - (Topic 2) 

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns: 

CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150) 

CUST_PHONEVARCHAR2(20) 

Which SELECT statement accomplishes this task? 

A. SELECT * FROM customers 

B. SELECT name, address FROM customers; 

C. SELECT id, name, address, phone FROM customers; 

D. SELECT cust_name, cust_address FROM customers; 

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers; 

View Answer

Q9. - (Topic 2) 

Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) 

A. 

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL; 

B. 

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL; 

C. 

SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL; 

D. 

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL; 

E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL; 

View Answer

Q10. - (Topic 2) 

In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the 

CUST_FIRST_NAME 'ABIGAIL'. 

Evaluate the following query: 

What would be the outcome? 

A. Abigail PA B. Abigail Pa 

C. Abigail IS 

D. an error message 

View Answer