Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 121:

  Which three statements are true about multiple-row sub queries? (Choose three.)

  A. They can contain a subquery within a sub query.
  B. They can return multiple columns as well as rows.
  C. They cannot contain a subquery within a subquery.
  D. They can return only one column but multiple rows.
  E. They can contain group functions and GROUP BY and HAVING clauses.
  F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
  A. They can contain a subquery within a sub query.
  B. They can return multiple columns as well as rows.
  E. They can contain group functions and GROUP BY and HAVING clauses.

• Question 122:

  Which three statements are true regarding sub queries? (Choose three.)

  A. Multiple columns or expressions can be compared between the main query and sub query
  B. Main query and sub query can get data from different tables
  C. Sub queries can contain GROUP BY and ORDER BY clauses
  D. Main query and sub query must get data from the same tables
  E. Sub queries can contain ORDER BY but not the GROUP BY clause
  F. Only one column or expression can be compared between the main query and subqeury
  A. Multiple columns or expressions can be compared between the main query and sub query
  B. Main query and sub query can get data from different tables
  C. Sub queries can contain GROUP BY and ORDER BY clauses

• Question 123:

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

  

  Examine the following two SQL statements:

  

  Which statement is true regarding the above two SQL statements?

  A. statement 1 gives an error, statement 2 executes successfully
  B. statement 2 gives an error, statement 1 executes successfully
  C. statement 1 and statement 2 execute successfully and give the same output
  D. statement 1 and statement 2 execute successfully and give a different output
  D. statement 1 and statement 2 execute successfully and give a different output

• Question 124:

  Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

  

  Which DELETE statement is valid?

  A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
  B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
  C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = `Carrey');
  D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = `Carrey');
  C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = `Carrey');

• Question 125:

  View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)

  

  A. . SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
  B. . SELECT promo_name. promo_begiii_date FROM promotionsWHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
  C. . SELECT promo_name. promo_begin_date FROM promotionsWHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
  D. . SELECT promo_name, promo_begin_date "START DATE" FROM promotionsWHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
  A. . SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
  D. . SELECT promo_name, promo_begin_date "START DATE" FROM promotionsWHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;

• Question 126:

  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 127:

  Examine the structure and data in the PRIC E_LIST table: Name Null? Type

  PROD_D NOT NULL NUMBER(3)

  PROD_PRICE VARCHAR2(10)

  PROD_ID PROD PRICE

  100 $234.55

  101 $6, 509.75

  102 $1, 234

  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 PRICEJLIST:
  B. . SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
  C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.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:
  C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM PRICE_LIST:

• Question 128:

  Which two statements are true regarding subqueries?

  A. A subquery can retrieve zero or more rows.
  B. Only two subqueries can be placed at one level.
  C. A subquery can be used only in SQL query statements.
  D. A subquery can appear on either side of a comparison operator.
  E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
  A. A subquery can retrieve zero or more rows.
  D. A subquery can appear on either side of a comparison operator.

  ---

  Using a Subquery to Solve a Problem

  Suppose you want to write a query to find out who earns a salary greater than Abel's salary.

  To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query.

  The inner query (or subquery) returns a value that is used by the outer query (or main query).

  Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

  Subquery Syntax

  A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a

  table with a condition that depends on the data in the table itself.

  You can place the subquery in a number of SQL clauses, including the following:

  WHERE clause

  HAVING clause

  FROM clause

  In the syntax:

  operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to

  as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.

  Guidelines for Using Subqueries

  Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row

  subqueries and multiple- row operators with multiple-row subqueries.

  Subqueries can be nested to an unlimited depth in a FROM clause but to "only" 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

• Question 129:

  The CUSTOMERS table has these columns:

  CUSTOMER_ID NUMBER(4) NOT NULL

  CUSTOMER_NAME VARCHAR2(100) NOT NULL

  CUSTOMER_ADDRESS VARCHAR2(150)

  CUSTOMER_PHONE VARCHAR2(20)

  You need to produce output that states "Dear Customer customer_name, ".

  The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table.

  Which statement produces this output?

  A. SELECT dear customer, customer_name, FROM customers;
  B. SELECT "Dear Customer", customer_name || ', ' FROM customers;
  C. SELECT 'Dear Customer ' || customer_name ', ' FROM customers;
  D. SELECT 'Dear Customer ' || customer_name || ', ' FROM customers;
  E. SELECT "Dear Customer " || customer_name || ", " FROM customers;
  F. SELECT 'Dear Customer ' || customer_name || ', ' || FROM customers;
  D. SELECT 'Dear Customer ' || customer_name || ', ' FROM customers;

  ---

  Concatenation operator to create a resultant column that is a character expression. Incorrect answer:

  A. no such dear customer column

  B. invalid syntax

  C. invalid syntax

  E. invalid syntax

  F. invalid syntax Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-18

• Question 130:

  The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

  A. GRANT select ON deptTO ALL_USERS;
  B. GRANT select ON deptTO ALL;
  C. GRANT QUERY ON deptTO ALL_USERS
  D. GRANT select ON deptTO PUBLIC;
  D. GRANT select ON deptTO PUBLIC;

  ---

  view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

  Incorrect answer:

  A. table to view all constraints definition and names B. show all object name belong to user

  C. does not display column associated

  E. no such view

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25