Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 241:

  Using the customers table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

  

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

  ---

  Duplicate Rows

  Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows.

  To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.

  You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.

• Question 242:

  View the Exhibit and examine the structure of the CUSTOMERS and GRADES tables:

  

  You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

  A. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endval;
  B. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endvalAND cust_credit_limit BETWEEN startval AND endval;
  C. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)FROM customers)AND cust_credit_limit BETWEEN startval AND endval;
  D. SELECT custname, gradeFROM customers , gradesWHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)FROM customers)AND MAX(cust_credit_limit) BETWEEN startval AND endval;
  B. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endvalAND cust_credit_limit BETWEEN startval AND endval;
  C. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)FROM customers)AND cust_credit_limit BETWEEN startval AND endval;

• Question 243:

  Which two statements about sub queries are true? (Choose two.)

  A. A sub query should retrieve only one row.
  B. A sub query can retrieve zero or more rows.
  C. A sub query can be used only in SQL query statements.
  D. Sub queries CANNOT be nested by more than two levels.
  E. A sub query CANNOT be used in an SQL query statement that uses group functions.
  F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.
  B. A sub query can retrieve zero or more rows.
  F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

  ---

  sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column. Incorrect answer:

  A. sub query can retrieve zero or more rows

  C. sub query is not SQL query statement

  D. sub query can be nested

  E. group function can be use with sub query

• Question 244:

  Examine the statement:

  Create synonym emp for hr.employees;

  What happens when you issue the statement?

  A. An error is generated.
  B. You will have two identical tables in the HR schema with different names.
  C. You create a table called employees in the HR schema based on you EMP table.
  D. You create an alternative name for the employees table in the HR schema in your own schema.
  D. You create an alternative name for the employees table in the HR schema in your own schema.

• Question 245:

  You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.

  Which set of statements accomplishes this?

  A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
  B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
  C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
  D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
  E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
  D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

  ---

  this is the correct solution for the answer. GRANT role_name to users;

  Incorrect answer:

  A. there is no such MODIFY keyword

  B. invalid CREATE command, there is no such NEW keyword C. invalid GRANT command, there is no such ROLE keyword E. invalid GRANT command, there is no such CHANGE keyword

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

• Question 246:

  Study the following exhibit: Assuming that the sequence SEQ1 was created with the option ORDER and INCREMENT BY set to 1, what value will be returned by the final SELECT statement?

  

  A. 2
  B. 3
  C. 4
  D. It will depend on whether any other sessions are selecting from the sequence while the statements in the exhibit are being run.
  D. It will depend on whether any other sessions are selecting from the sequence while the statements in the exhibit are being run.

• Question 247:

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

  You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use?

  A. Compare Period ADDM report
  B. AWR Compare Period report
  C. Active Session History (ASH) report
  D. Taking a new snapshot and comparing it with a preserved snapshot
  B. AWR Compare Period report

  ---

  The awrddrpt.sql report is the Automated Workload Repository Compare Period Report. The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.

  Incorrect:

  Not A:Compare Period ADDM

  Use this report to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. Only workload replays that contain at least 5 minutes of database time can be compared using this report.

• Question 249:

  Examine the structure of the sales table:

  

  Evaluate the following create table statement:

  

  Which two statements are true about the creation of the SALES1 table?

  A. The SALES1 table is created with no rows but only a structure.
  B. The SALES1 table would have primary key and unique constraints on the specified columns.
  C. The SALES1 table would not be created because of the invalid where clause.
  D. The SALES1 table would have not null and unique constraints on the specified columns.
  E. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,
  A. The SALES1 table is created with no rows but only a structure.
  D. The SALES1 table would have not null and unique constraints on the specified columns.

  ---

  A: the WHERE clause 1=2 is false so no rows will be inserted into the new table.

  D: The NOT NULL constraints will be included in the new table. There are no UNIQUE constraints.

• Question 250:

  You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

  

  Evaluate the following query: Exhibit:

  

  The above query produces an error on execution. What is the reason for the error?

  A. An alias cannot be used in an expression
  B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
  C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
  D. The alias NAME should not be enclosed within double quotation marks
  A. An alias cannot be used in an expression