Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 291:

  Where can sub queries be used? (Choose all that apply)

  A. field names in the SELECT statement
  B. the FROM clause in the SELECT statement
  C. the HAVING clause in the SELECT statement
  D. the GROUP BY clause in the SELECT statement
  E. the WHERE clause in only the SELECT statement
  F. the WHERE clause in SELECT as well as all DML statements
  A. field names in the SELECT statement
  B. the FROM clause in the SELECT statement
  C. the HAVING clause in the SELECT statement
  F. the WHERE clause in SELECT as well as all DML statements

  ---

  SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

  A subquery can have any of the usual clauses for selection and projection. The following are required clauses:

  A SELECT list

  A FROM clause

  The following are optional clauses:

  WHERE

  GROUP BY

  HAVING

  The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

• Question 292:

  View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

  

  The above query generates an error on execution.

  Which clause in the above SQL statement causes the error?

  

  A. WHERE
  B. SELECT
  C. GROUP BY
  D. ORDER BY
  C. GROUP BY

• Question 293:

  Evaluate the SQL statement:

  SELECT ROUND(45.953, -1), TRUNC(45.936, 2)

  FROM dual;

  Which values are displayed?

  A. 46 and 45
  B. 46 and 45.93
  C. 50 and 45.93
  D. 50 and 45.9
  E. 45 and 45.93
  F. 45.95 and 45.93
  C. 50 and 45.93

  ---

  ROUND (45.953, -1) will round value to 1 decimal places to the left. TRUNC (45.936, 2) will truncate value to 2 decimal The answer will be 50 and 45.93

• Question 294:

  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

  ---

  Creating Joins with the USING Clause

  If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.

  Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive Using Table Aliases with the USING clause

  When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the

  slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause.

  The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement.

  Creating Joins with the ON Clause

  The join condition for the natural join is basically an equijoin of all columns with the same name.

  Use the ON clause to specify arbitrary conditions or specify columns to join. ?ANSWER C The join condition is separated from other search conditions. ANSWER D The ON clause makes code easy to understand.

• Question 295:

  In which two cases would you use an outer join? (Choose two.)

  A. The tables being joined have NOT NULL columns.
  B. The tables being joined have only matched data.
  C. The columns being joined have NULL values.
  D. The tables being joined have only unmatched data.
  E. The tables being joined have both matched and unmatched data.
  F. Only when the tables have a primary key/foreign key relationship.
  C. The columns being joined have NULL values.
  E. The tables being joined have both matched and unmatched data.

  ---

  You use an outer join to also see rows that do not meet the join condition. Incorrect answer:

  A. meet a join condition

  B. meet a join condition

  D. meet non join condition only

  F. does not take into consideration of primary key and foreign key relationship Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-17

• Question 296:

  Which statement is true regarding the INTERSECT operator?

  A. It ignores NULL values
  B. The number of columns and data types must be identical for all SELECT statements in the query
  C. The names of columns in all SELECT statements must be identical
  D. Reversing the order of the intersected tables the result
  B. The number of columns and data types must be identical for all SELECT statements in the query

  ---

  INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

  The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

• Question 297:

  Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?

  

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

• Question 298:

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

  

  You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit 10,000. Evaluate the following two queries:

  

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

  A. Only the first query gives the correct result.
  B. Only the second query gives the correct result.
  C. Both execute successfully and give the same result.
  D. Both execute successfully but do not give the required result.
  A. Only the first query gives the correct result.

• Question 299:

  You need to create a table for a banking application. One of the columns in the table has the following requirements:

  1) You want a column in the table to store the duration of the credit period.

  2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.

  3) The maximum period of the credit provision in the application is 30 days.

  4) The interest has to be calculated for the number of days an individual has taken a credit for.

  Which data type would you use for such a column in the table?

  A. DATE
  B. NUMBER
  C. TIMESTAMP
  D. INTERVAL DAY TO SECOND
  E. INTERVAL YEAR TO MONTH
  D. INTERVAL DAY TO SECOND

  ---

  INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

  You can perform a number of arithmetic operations on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR

  TO MONTH) data.

  It stores duration of the credit as days

  The format stored is numeric format, and you know that numeric values can be easily added and subtracted with date data type without using conversion functions (i.e. SELECT SYSDATE ?1 FROM DUAL;)

  The interest has to be calculated for the number of days an individual has taken a credit for, so it will be easy to calculate interest by using the interest rate and duration of the the credit which is numeric format.

• Question 300:

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

  You need to generate a report of all promos from the promotions table based on the following conditions:

  1.

  The promo name should not begin with 'T' or 'N'.

  2.

  The promo should cost more than $20000.

  3.

  The promo should have ended after 1st January 2001. Which where clause would give the required result?

  

  A. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > `1-JAN-01'
  B. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost >20000 AND promo_end_date > `1-JAN-01'
  C. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost >20000 OR promo_end_date > `1-JAN-01'
  D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost >20000 AND promo_end_date > `1-JAN-01')
  A. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > `1-JAN-01'

  ---

  We must use the AND operator, not the OR operator, as all the Boolean statements must be true to meet the conditions.