Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 231:

  Which two statements about creating constraints are true? (Choose two)

  A. Constraint names must start with SYS_C.
  B. All constraints must be defined at the column level.
  C. Constraints can be created after the table is created.
  D. Constraints can be created at the same time the table is created.
  E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.
  C. Constraints can be created after the table is created.
  D. Constraints can be created at the same time the table is created.

  ---

  Constraints can be created after the table is created. Use ALTER TABLE command for that.

  Constraints can be created at the same time the table is created (CREATE TABLE command).

  Incorrect Answers

  A:. There is no requirements in Oracle that constraint names must start with SYS_C. Oracle can use prefix "SYS" to build indexes for UNIQUE and NOT NULL constraints, but it is not required for user to follow this naming rule.

  B:. Not all constraints must be defines at the column level. Only NOT NULL constraint must be.

  E:. There is no VIEW_CONSTRAINTS dictionary view in Oracle.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects

• Question 232:

  The CUSTOMERS table has these columns:

  

  A promotional sale is being advertised to the customers in France.

  Which WHERE clause identifies customers that are located in France?

  A. WHERE lower(country_address) = "france"
  B. WHERE lower(country_address) = 'france'
  C. WHERE lower(country_address) IS 'france'
  D. WHERE lower(country_address) = '%france%'
  E. WHERE lower(country_address) LIKE %france%
  B. WHERE lower(country_address) = 'france'

  ---

  WHERE lower(country_address)='france'

  Incorrect answer:

  A. invalid use of symbol ""

  C. invalid use of IS keyword

  D. invalid use of % in condition

  E. invalid use of condition Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-12

• Question 233:

  Examine the structure of the STUDENTS table:

  

  You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.

  Which SQL statement accomplishes this task?

  A. SELECT . student_ id, marks, ROWNUM "Rank"FROM . studentsWHERE . ROWNUM
  B. SELECT . student_id, marks, ROWID "Rank". FROM . students. WHERE . ROWID
  C. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, marks. FROM . students. WHERE . ROWNUM
  D. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, marks. FROM . students. WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99'. AND course_id = `INT_SQL'. ORDER BY marks DESC). WHERE . ROWNUM
  E. SELECT. student id, marks, ROWNUM "Rank". FROM. (SELECT student_id, marks. FROM students. ORDER BY marks). WHERE. ROWNUM
  D. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, marks. FROM . students. WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99'. AND course_id = `INT_SQL'. ORDER BY marks DESC). WHERE . ROWNUM

• Question 234:

  You create a sequence as follows:

  create sequence seq1 start with 1;

  After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated.

  How can you do this?

  A. You must drop and re-create the sequence.
  B. You can't. Under no circumstances can numbers from a sequence be reissued once they have been used.
  C. Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1.
  D. Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.
  A. You must drop and re-create the sequence.

  ---

  It is not possible to change the next value of a sequence, so you must re-create it.

• Question 235:

  Examine the structure of the EMPLOYEES table:

  

  You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

  

  Which INSERT statement meets the above requirements?

  A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid IN (20, 50));
  C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20, 50)) VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20, 50) WITH CHECK OPTION)VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20, 50) WITH CHECK OPTION)VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);

• Question 236:

  You have created an index with this statement:

  create index ename_i on employees(last_name,first_name);

  How can you adjust the index to include the employees' birthdays, which is a date type column called DOB?

  A. Use ALTER INDEX ENAME_I ADD COLUMN DOB;.
  B. You can't do this because of the data type mismatch.
  C. You must drop the index and re-create it.
  D. This can only be done if the column DOB is NULL in all existing rows.
  C. You must drop the index and re-create it.

• Question 237:

  The following parameter are set for your Oracle 12c database instance:

  OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE You want to manage the SQL plan evolution task manually. Examine the following steps:

  1.

  Set the evolve task parameters.

  2.

  Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.

  3.

  Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK function.

  4.

  Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.

  5.

  Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function. Identify the correct sequence of steps:

  A. 2, 4, 5
  B. 2, 1, 4, 3, 5
  C. 1, 2, 3, 4, 5
  D. 1, 2, 4, 5
  B. 2, 1, 4, 3, 5

• Question 238:

  What are distinguishing characteristics of a public synonym rather than a private synonym? (Choose two.)

  A. Public synonyms are always visible to all users.
  B. Public synonyms can be accessed by name without a schema name qualifier.
  C. Public synonyms can be selected from without needing any permissions.
  D. Public synonyms can have the same names as tables or views.
  B. Public synonyms can be accessed by name without a schema name qualifier.
  D. Public synonyms can have the same names as tables or views.

  ---

  Public synonyms are not schema objects and so can only be addressed directly. They can have the same names as schema objects.

• Question 239:

  Evaluate the following SQL statement:

  SQL> SELECT cust_id, cust_last_name "Last Name"

  FROM customers

  WHERE country_id = 10

  UNION

  SELECT cust_id CUST_NO, cust_last_name

  FROM customers

  WHERE country_id = 30;

  Which ORDER BY clause are valid for the above query? (Choose all that apply.)

  A. ORDER BY 2, 1
  B. ORDER BY CUST_NO
  C. ORDER BY 2, cust_id
  D. ORDER BY "CUST_NO"
  E. ORDER BY "Last Name"
  A. ORDER BY 2, 1
  C. ORDER BY 2, cust_id

  ---

  Using the ORDER BY Clause in Set Operations

  - The ORDER BY clause can appear only once at the end of the compound query.

  -Component queries cannot have individual ORDER BY clauses.

  -

  The ORDER BY clause recognizes only the columns of the first SELECT query.

  -

  By default, the first column of the first SELECT query is used to sort the output in an ascending order.

• Question 240:

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

  

  In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'. You issue the following query:

  

  What would be the outcome?

  A. 'Oder' and 'Aus'
  B. an error because the TRIM function specified is not valid
  C. an error because the LOWER function specified is not valid
  D. an error because the REPLACE function specified is not valid
  B. an error because the TRIM function specified is not valid