Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 231:

  Which two are true about the USING clause when joining tables?

  A. All column names in a USING clause must be qualified with a table name or table alias.
  B. It can never be used with onatural join.
  C. It is used to specify an equijoin of columns that have the same name in both tables.
  D. It can never be used with a full outer join.
  E. It is used to specify an explicit join condition involving operators.
  B. It can never be used with onatural join.
  E. It is used to specify an explicit join condition involving operators.

• Question 232:

  Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS: You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses. Which SQL statement must you use?

  

  A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
  B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id)
  C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)
  D. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_idd. department_id)
  C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)

• Question 233:

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

  

  You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.

  Which query would give you the desired result?

  A. SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROM orders WHERE customer_id = 101);
  B. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
  C. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
  D. SELECT order_id, order_date FROM orders WHERE order_date > IN (SELECT order_date FROM orders WHERE customer_id = 101);
  C. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);

• Question 234:

  Examine the description of the PRODUCTS table:

  

  Which three queries use valid expressions?

  A. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
  B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
  C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;
  D. SPLECT product_id, expiry_date * 2 FROM products; E. SELEGT product_id, unit_price, unit_price + surcharge FROM products;
  F. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;
  B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
  C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;

• Question 235:

  View the exhibit and examine the structure of the EMPLOYEES table.

  

  You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.

  Which SQL statement would you execute?

  A. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE m.manager_id = 100;
  B. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id = 100;
  C. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON e.employee_id = m.manager_id WHERE m.manager_id = 100;
  D. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e WHERE m.employee_id = e.manager_id AND e.manager_id = 100
  B. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id = 100;

• Question 236:

  Which two statements are true about the COUNT function?

  A. It can only be used for NUMBER data types.
  B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column
  C. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
  D. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
  E. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
  B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column
  C. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

• Question 237:

  Examine this statement which executes successfully:

  

  Which statement will violate the CHECK constraint?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  D. Option D

• Question 238:

  Which two statements are true about views? (Choose two.)

  A. The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view.
  B. The WITH CHECK clause prevents certain rows from being displayed when querying the view.
  C. Views can be indexed.
  D. Views can be updated without the need to re-grant privileges on the view.
  E. Tables in the defining query of a view must always exist in order to create the view.
  A. The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view.
  D. Views can be updated without the need to re-grant privileges on the view.

• Question 239:

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

  The columns ORDER_MODE and ORDER_TOTAL have the default values `direct' and 0 respectively.

  

  Which two INSERT statements are valid? (Choose two.)

  A. INSERT INTO orders VALUES (1,'09-mar-2007', `online','', 1000);
  B. INSERT INTO orders (order_id,order_date,order_mode, (customer_id,order_total) VALUES (1,TO_DATE(NULL), `online', 101, NULL);
  C. INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,'09-mar-2007', 101);
  D. INSERT INTO orders VALUES (1,'09-mar-2007', DEFAULT, 101, DEFAULT);
  E. INSERT INTO orders (order_id,order_date,order_mode,order_total) VALUES (1,'10-mar-2007',`online',1000);
  C. INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,'09-mar-2007', 101);
  D. INSERT INTO orders VALUES (1,'09-mar-2007', DEFAULT, 101, DEFAULT);

• Question 240:

  View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

  

  Evaluate the following CREATE TABLE command:

  CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)

  AS

  SELECT order_id.order_date,customer_id

  FROM orders;

  Which statement is true regarding the above command?

  A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
  B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
  C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
  D. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
  B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.