Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 261:

  The STUDENT_GRADES table has these columns: Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?

  

  A. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN '01- JAN-2001' AND '31-DEC-2001'OR gpa > 3.;
  B. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN '01- JAN-2001' AND '31-DEC-2001'AND gpa gt 3.0;
  C. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN '01- JAN-2001' AND '31-DEC-2001'AND gpa > 3.0;
  D. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN '01- JAN-2001' AND '31-DEC-2001'OR gpa > 3.0;
  E. SELECT student_id, gpaFROM student_gradesWHERE semester_end > '01-JAN-2001' OR semester_end < '31-DEC-2001'AND gpa >= 3.0;
  C. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN '01- JAN-2001' AND '31-DEC-2001'AND gpa > 3.0;

• Question 262:

  Which three statements are true regarding subqueries? (Choose three.)

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

  ---

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

  Examine the structure and data in the PRICE_LIST table: Name . Null . Type

  PROD_ID . NOT NULL . NUMBER(3) PROD_PRICE . VARCHAR2(10) PROD_ID PROD_PRICE

  100 $234.55 101 $6, 509.75 102 $1, 234

  You plan to give a discount of 25% on the product price and need to display the discount amount 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 PRICE_LIST;
  B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, '$99, 999.00')FROM PRICE_LIST;
  C. SELECT TO_CHAR(TO_NUMBER(prod_price, '$99, 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;
  B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, '$99, 999.00')FROM PRICE_LIST;

  ---

  Use TO_NUMBER on the prod_price column to convert from char to number to be able to multiply it with 0.25. Then use the TO_CHAR function (with formatting'$99, 999.00') to convert the number back to char.

  Incorrect:

  Not C: Use the formatting'$99, 999.00' with the TO_CHAR function, not with the TO_NUMBER function.

  Note:

  *

  Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single

  *

  Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

• Question 264:

  The STUDENT_GRADES table has these columns:

  STUDENT_ID NUMBER(12)

  SEMESTER_END DATE

  GPA NUMBER(4, 3)

  The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.

  Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

  A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
  B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
  C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
  D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
  E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
  F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
  F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

  ---

  Sorted by highest to lowest is DESCENDING order Incorrect answer:

  A. result in ascending order

  B. wrong syntax with SORT keyword

  C. wrong syntax with SORT keyword

  D. default value for ORDER by is in ascending order E. wrong syntax with SORT keyword Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

• Question 265:

  View the Exhibit and examine the data in the costs table.

  

  You need to generate a report that displays the IDs of all products in the costs table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:

  

  Which statement is true regarding the above query?

  A. It executes and produces the required result.
  B. It produces an error because an expression cannot be used in the order by clause.
  C. It produces an error because the DESC option cannot be used with an expression in the order by clause.
  D. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.
  A. It executes and produces the required result.

• Question 266:

  You own a table called EMPLOYEES with this table structure:

  EMPLOYEE_ID NUMBER Primary Key

  FIRST_NAME VARCHAR2(25)

  LAST_NAME VARCHAR2(25)

  HIRE_DATE DATE

  What happens when you execute this DELETE statement?

  DELETE employees;

  A. You get an error because of a primary key violation.
  B. The data and structure of the EMPLOYEES table are deleted.
  C. The data in the EMPLOYEES table is deleted but not the structure.
  D. You get an error because the statement is not syntactically correct.
  C. The data in the EMPLOYEES table is deleted but not the structure.

  ---

  You can remove existing rows from a table by using the DELETE statement.

  DELETE [FROM] table

  [WHERE. condition];

  Incorrect answer:

  A. Statement will not cause error

  B. Delete statement will not delete the table structure D. Statement will not cause error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19

• Question 267:

  Here is the structure and data of the CUST_TRANS table:

  Exhibit:

  

  Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.

  Which three SQL statements would execute successfully? (Choose three.)

  A. SELECT transdate + '10' FROM cust_trans;
  B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
  C. SELECT transamt FROM cust_trans WHERE custno > '11';
  D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
  E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
  A. SELECT transdate + '10' FROM cust_trans;
  C. SELECT transamt FROM cust_trans WHERE custno > '11';
  D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';

• Question 268:

  Which is a valid CREATE TABLE statement?

  A. CREATE TABLE EMP9$# AS (empid number(2));
  B. CREATE TABLE EMP*123 AS (empid number(2));
  C. CREATE TABLE PACKAGE AS (packid number(2));
  D. CREATE TABLE 1EMP_TEST AS (empid number(2));
  A. CREATE TABLE EMP9$# AS (empid number(2));

  ---

  Table names and column names must begin with a letter and be 1-30 characters long. Characters A-Z, a-z, 0-9, _, $ and # (legal characters but their use is discouraged). Incorrect answer:

  B. Non alphanumeric character such as "*" is discourage in Oracle table name.

  D. Table name must begin with a letter.

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4

• Question 269:

  Evaluate the following SQL statements: Exhibit:

  

  You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. Exhibit:

  

  Which two statements are true regarding the above view? (Choose two.)

  A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
  B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
  C. It allows you to insert rows into the EMPLOYEES table
  D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements
  B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
  D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

• Question 270:

  Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

  A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
  B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
  C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
  D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
  E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
  F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;
  C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;