Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 301:

  Which two statements are true regarding the USING clause in table joins? (Choose two.)

  A. It can be used to join a maximum of three tables.
  B. It can be used to restrict the number of columns used in a NATURAL join.
  C. It can be used to access data from tables through equijoins as well as nonequijoins.
  D. It can be used to join tables that have columns with the same name and compatible data types.
  B. It can be used to restrict the number of columns used in a NATURAL join.
  D. It can be used to join tables that have columns with the same name and compatible data types.

  ---

  NATURAL JOIN operation

  A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

  If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

  All the common columns

  Every column in the first (left) table that is not a common column Every column in the second (right) table that is not a common column An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of

  that table that is not a common column. If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a

  RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

  Syntax

  TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression |

  ( TableExpression ) }

  Examples

  If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

  SELECT * FROM COUNTRIES NATURAL JOIN CITIES

  SELECT * FROM COUNTRIES JOIN CITIES

  USING (COUNTRY, COUNTRY_ISO_CODE)

• Question 302:

  You need to create a table named ORDERS that contain four columns:

  1.

  AN ORDER_ID column of number data type

  2.

  A CUSTOMER_ID column of number data type

  3.

  AN ORDER_STATUS column that contains a character data type

  4.

  A DATE_ORDERED column to contain the date the order was placed.

  When a row is inserted into the table, if no value is provided when the order was placed, today's date should be used instead.

  Which statement accomplishes this?

  A. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
  B. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
  C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
  D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
  E. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE);
  F. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE DEFAULT SYSDATE);
  B. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);

  ---

  Requirement that Order_Status should be a character data type

  Not E: Order_status must be a character data type. There is also a syntax error.

• Question 303:

  Examine the structure of the DEPARTMENTS table:

  

  You execute the following command:

  SQL> ALTER TABLE departments

  SET UNUSED (country);

  Which two statements are true? (Choose two.)

  A. Synonyms existing om the DEPARTMENTS table would have to be re-created.
  B. Unique key constraints defined on the COUNTRY column are removed.
  C. Views created on the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.
  D. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.
  E. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.
  C. Views created on the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.
  E. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

• Question 304:

  A SELECT statement can be used to perform these three functions:

  Which set of keywords describes these capabilities?

  A. difference, projection, join
  B. selection, projection, join
  C. selection, intersection, join
  D. intersection, projection, join
  E. difference, projection, product
  B. selection, projection, join

  ---

  choose rows from a table is SELECTION,

  Choose column from a table is PROJECTION

  Bring together data in different table by creating a link between them is JOIN.

  Incorrect answer:

  A. answer should have SELECTION, PROJECTION and JOIN. C. answer should have SELECTION, PROJECTION and JOIN. D. answer should have SELECTION, PROJECTION and JOIN. E. answer should have SELECTION, PROJECTION and JOIN.

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6

• Question 305:

  You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:

  1) The order ID will be unique and cannot have null values.

  2) The order date cannot have null values and the default should be the current date.

  3) The order amount should not be less than 50.

  4) The order status will have values either shipped or not shipped.

  5) The order payment mode should be cheque, credit card, or cash on delivery (COD).

  Which is the valid DDL statement for creating the ORD_DETAIL table?

  A. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount > 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN ('Shipped', 'Not Shipped')),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN ('Cheque', 'Credit Card','Cash On Delivery')));
  B. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount > 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN ('Shipped', 'Not Shipped')),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN ('Cheque', 'Credit Card','Cash On Delivery')));
  C. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount >= 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN ('Shipped', 'Not Shipped')),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN ('Cheque', 'Credit Card','Cash On Delivery')));
  D. CREATE TABLE ord_details(ord_id NUMBER(2),ord_date DATE NOT NULL DEFAULT SYSDATE,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount >= 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN ('Shipped', 'Not Shipped')),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN ('Cheque', 'Credit Card','Cash On Delivery')));
  C. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount >= 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN ('Shipped', 'Not Shipped')),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN ('Cheque', 'Credit Card','Cash On Delivery')));

• Question 306:

  View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.

  SALE_VW is a view created using the following command:

  SQL>CREATE VIEW sale_vw AS

  SELECT prod_id, SUM(quantity_sold) QTY_SOLD

  FROM sales GROUP BY prod_id;

  You issue the following command to add a row to the SALE_SUMMARY table:

  SQL>INSERT INTO sale_summary

  SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products

  USING (prod_id) WHERE prod_id = 16;

  What is the outcome?

  A. It executes successfully.
  B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
  C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
  D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
  D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.

• Question 307:

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

  Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00.

  Which two queries would give the required result? (Choose two.)

  

  A. SELECT promo_name, promo_costFROM promotionsWHERE promo_category IN ('TV', 'internet') ANDpromo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
  B. SELECT promo_name, promo_costFROM promotionsWHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date
  C. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category BETWEEN 'TV' AND 'internet') AND(promo_end_date IN ('15-MAR-00', '15-OCT-00'));
  D. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date
  A. SELECT promo_name, promo_costFROM promotionsWHERE promo_category IN ('TV', 'internet') ANDpromo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
  D. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date

• Question 308:

  You need to design a student registration database that contains several tables storing academic information.

  The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.

  You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

  A. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
  B. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
  C. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
  D. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
  D. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

  ---

  CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);

  Incorrect answer:

  A. invalid syntax

  B. invalid syntax

  C. invalid syntax

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

• Question 309:

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

  

  Examine the data in the ename and hiredate columns of the employees table:

  

  You want to generate a list of user IDs as follows:

  

  You issue the following query:

  

  What is the outcome?

  A. It executes successfully and gives the correct output.
  B. It executes successfully but does not give the correct output.
  C. It generates an error because the REPLACE function is not valid.
  D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
  A. It executes successfully and gives the correct output.

  ---

  REPLACE (text, search_string, replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3, '9', '85') from dual Query 2: select replace(sysdate, 'DEC', 'NOV') from dual

• Question 310:

  Which create table statement is valid?

  

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

  ---

  PRIMARY KEY Constraint

  A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint

  enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.

  Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

  Incorrect:

  Not A: Two primary keys are not allowed.

  Not B: You cannot specific a column to be both UNIQUE and NOT NULL.

  Not C: The default value cannot be NOT NULL.