Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 41:

  Evaluate the SQL statement DROP TABLE DEPT:

  Which four statements are true of the SQL statement? (Choose four)

  A. You cannot roll back this statement.
  B. All pending transactions are committed.
  C. All views based on the DEPT table are deleted.
  D. All indexes based on the DEPT table are dropped.
  E. All data in the table is deleted, and the table structure is also deleted.
  F. All data in the table is deleted, but the structure of the table is retained.
  G. All synonyms based on the DEPT table are deleted.
  A. You cannot roll back this statement.
  B. All pending transactions are committed.
  D. All indexes based on the DEPT table are dropped.
  E. All data in the table is deleted, and the table structure is also deleted.

• Question 42:

  Which object privileges can be granted on a view?

  A. none
  B. DELETE, INSERT,SELECT
  C. ALTER, DELETE, INSERT, SELECT
  D. DELETE, INSERT, SELECT, UPDATE
  D. DELETE, INSERT, SELECT, UPDATE

• Question 43:

  The COMMISSION column shows the monthly commission earned by the employee. Exhibit

  

  Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

  A. listing the employees who earn the same amount of commission as employee 3
  B. finding the total commission earned by the employees in department 10
  C. finding the number of employees who earn a commission that is higher than the average commission of the company
  D. listing the departments whose average commission is more that 600
  E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
  F. listing the employees whose annual commission is more than 6000
  A. listing the employees who earn the same amount of commission as employee 3
  C. finding the number of employees who earn a commission that is higher than the average commission of the company

• Question 44:

  View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

  

  A. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
  B. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', andandcustomer_id, 1000):
  C. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id.
  D. ordertotal FROM orders o. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
  E. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600).order_total) VALUES(l.'10-mar-2007\ 'direct'. andcustomer_id. 1000):
  A. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);

• Question 45:

  View the Exhibit for the structure of the STUDENT and FACULTY tables.

  

  You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:

  

  Which statement is true regarding the outcome?

  A. Only statement 1 executes successfully and gives the required result.
  B. Only statement 2 executes successfully and gives the required result.
  C. Both statements 1 and 2 execute successfully and give different results.
  D. Both statements 1 and 2 execute successfully and give the same required result.
  D. Both statements 1 and 2 execute successfully and give the same required result.

• Question 46:

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

  

  Which two tasks would require subqueries? (Choose two.)

  A. Display the minimum list price for each product status.
  B. Display all suppliers whose list price is less than 1000.
  C. Display the number of products whose list price is more than the average list price.
  D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
  E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.
  C. Display the number of products whose list price is more than the average list price.
  E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

• Question 47:

  You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.

  

  Evaluate the following SQL statement: Exhibit:

  

  Which statement is true regarding the execution of the above UPDATE statement?

  A. It would execute and restrict modifications to only the column specified in the SELECT statement
  B. It would not execute because two tables cannot be used in a single UPDATE statement
  C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
  D. It would not execute because the SELECT statement cannot be used in place of the table name
  A. It would execute and restrict modifications to only the column specified in the SELECT statement

• Question 48:

  Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

  A. The ON clause can be used to join tables on columns that have different names but compatible data types
  B. A maximum of one pair of columns can be joined between two tables using the ON clause
  C. Both USING and ON clause can be used for equijoins and nonequijoins
  D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause
  A. The ON clause can be used to join tables on columns that have different names but compatible data types
  D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

• Question 49:

  The STUDENT_GRADES table has these columns:

  STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3)

  The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date. Which statement accomplish this?

  A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
  B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC
  C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
  D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
  E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
  F. SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC
  F. SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC

• Question 50:

  Examine the structure and data in the PRICE_LIST table:

  Name Null Type

  PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE

  100 $234.55 101 $6,509.75 102 $1,234

  You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE. Which SQL statement would give the required result?

  A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
  B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
  C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
  D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
  B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;