Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 131:

  See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:

  

  The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables,

  respectively.

  Evaluate the following the CREATE TABLE command:

  Exhibit:

  

  Which statement is true regarding the above command?

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

  ---

  Creating a Table Using a Subquery

  Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.

  CREATE TABLE table

  [(column, column...)]

  AS subquery;

  Match the number of specified columns to the number of subquery columns. Define columns with column names and default values.

  Guidelines

  The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.

  The column definition can contain only the column name and default value. If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.

  If no column specifications are given, the column names of the table are the same as the column names in the subquery.

  The column data type definitions and the NOT NULL constraint are passed to the new table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY column will not pass the NOT NULL feature to the new column.

  Any other constraint rules are not passed to the new table. However, you can add constraints in the column definition.

• Question 132:

  Which three SQL statements would display the value 1890.55 as $1, 890.55?

  

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

• Question 133:

  Examine the following Entity Relationship Model diagram:

  

  Which three statements are true? (Choose three.)

  A. SKILL is a multi-valued attribute.
  B. YEARS_EMPLOYED is a derived attribute.
  C. YEARS_EMPLOYED is a key attribute.
  D. SKILL is a composite attribute.
  E. EMP_ID is a key attribute.
  F. EMPLOYEE is a weak entity.
  A. SKILL is a multi-valued attribute.
  B. YEARS_EMPLOYED is a derived attribute.
  E. EMP_ID is a key attribute.

• Question 134:

  View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.

  Exhibit 1:

  

  Exhibit 2:

  

  Evaluate the following SQL statements:

  

  Which statement is true regarding the output of the above query?

  A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not.
  B. It gives the details of promos for which there have been no sales.
  C. It gives the details of promos for which there have been sales.
  D. It gives details of all promos irrespective of whether they have resulted in a sale or not.
  D. It gives details of all promos irrespective of whether they have resulted in a sale or not.

• Question 135:

  Examine the data in the CUSTOMERS table:

  

  You want to list all cities that have more than one customer along with the customer details.

  Evaluate the following query:

  SQL>SELECT c1.custname, c1.city

  FROM Customers c1 __________________ Customers c2

  ON (c1.city=c2.city AND c1.custname<>c2.custname);

  Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

  A. JOIN
  B. NATURAL JOIN
  C. LEFT OUTER JOIN
  D. FULL OUTER JOIN
  E. RIGHT OUTER JOIN
  A. JOIN
  E. RIGHT OUTER JOIN

• Question 136:

  Which of these is a defining characteristic of a complex view, rather than a simple view? (Choose one or more correct answers.)

  A. Restricting the projection by selecting only some of the table's columns
  B. Naming the view's columns with column aliases
  C. Restricting the selection of rows with a WHERE clause
  D. Performing an aggregation
  E. Joining two tables
  D. Performing an aggregation
  E. Joining two tables

• Question 137:

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

  You want to display only those product names with their list prices where the list price is at least double the minimum price.

  The report should start with the product name having the maximum list price satisfying this condition.

  Evaluate the following SQL statement:

  SQL>SELECT prod_name, prod_list_price

  FROM products

  WHERE prod_list_price >= 2 * prod_min_price

  Which ORDER BY clauses can be added to the above SQL statement to get the correct output?

  (Choose all that apply.)

  

  A. ORDER BY prod_list_price DESC, prod_name;
  B. ORDER BY (2*prod_min_price)DESC, prod_name;
  C. ORDER BY prod_name, (2*prod_min_price)DESC;
  D. ORDER BY prod_name DESC, prod_list_price DESC;
  E. ORDER BY prod_list_price DESC, prod_name DESC;
  A. ORDER BY prod_list_price DESC, prod_name;
  E. ORDER BY prod_list_price DESC, prod_name DESC;

  ---

  Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.) DESC orders the rows in descending order If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

• Question 138:

  Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

  A. The BLOB data type column is used to store binary data in an operating system file
  B. The minimum column width that can be specified for a VARCHAR2 data type column is one
  C. A TIMESTAMP data type column stores only time values with fractional seconds
  D. The value for a CHAR data type column is blank-padded to the maximum defined column width
  E. Only One LONG column can be used per table
  B. The minimum column width that can be specified for a VARCHAR2 data type column is one
  D. The value for a CHAR data type column is blank-padded to the maximum defined column width
  E. Only One LONG column can be used per table

  ---

  LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should

  be converted to CLOB.

  There can only be one LONG column in a table.

  DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column.

  If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size)

  Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4, 000.)

  BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.

  BFILE A locator pointing to a file stored on the operating system of the database server.

  The size of the files is limited to 4GB.

  TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified.

  Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.

• Question 139:

  Evaluate the following query:

  SELECT INTERVAL '300' MONTH,

  INTERVAL '54-2' YEAR TO MONTH,

  INTERVAL '11:12:10.1234567' HOUR TO SECOND

  FROM dual;

  What is the correct output of the above query?

  A. +25-00, +54-02, +00 11:12:10.123457
  B. +00-300, +54-02, +00 11:12:10.123457
  C. +25-00, +00-650, +00 11:12:10.123457
  D. +00-300, +00-650, +00 11:12:10.123457
  A. +25-00, +54-02, +00 11:12:10.123457

  ---

  Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes, and seconds

• Question 140:

  Examine the structure of the EMPLOYEES table:

  

  Which UPDATE statement is valid?

  A. UPDATE employeesSET first_name = `John'SET last_name = `Smith'WHERE employee_id = 180;
  B. UPDATE employeesSET first_name = `John',SET last_name = `Smoth'WHERE employee_id = 180;
  C. UPDATE employeeSET first_name = `John'AND last_name = `Smith'WHERE employee_id = 180;
  D. UPDATE employeeSET first_name = `John', last_name = `Smith'WHERE employee_id = 180;
  D. UPDATE employeeSET first_name = `John', last_name = `Smith'WHERE employee_id = 180;