NEW QUESTION 1
You issued this command:
CHOOSE THREE
SQL > DROP TABLE employees; Which three statements are true?

• A. Sequences used in the EMPLOYEES table become invalid.
• B. If there is an uncommitted transaction in the session, it is committed.
• C. All indexes and constraints defined on the table being dropped are also dropped.
• D. The space used by the EMPLOYEES table is always reclaimed immediately.
• E. The EMPLOYEES table can be recovered using the ROLLBACK command.
• F. The EMPLOYEES table may be moved to the recycle bin.

Answer: BCF

NEW QUESTION 2
Which two statements are true regarding working with dates? (Choose two.)

• A. The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
• B. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
• C. The default internal storage of dates is in character format.
• D. The default internal storage of dates is in numeric format.

Answer: AD

NEW QUESTION 3
View the exhibit and examine the structure of ORDERS and CUSTOMERS tables. ORDERS
Name Null? Type
ORDER_ID NOT NULL NUMBER(4) ORDER_DATE NOT NULL DATE ORDER_MODE VARCHAR2(8) CUSTOMER_ID NOT NULL NUMBER(6) ORDER_TOTAL NUMBER(8, 2) CUSTOMERS
Name Null? Type
CUSTOMER_ID NOT NULL
NUMBER(6) CUST_FIRST_NAME NOT NULL VARCHAR2(20) CUST_LAST_NAME NOT NULL VARCHAR2(20) CREDIT_LIMIT NUMBER(9,2) CUST_ADDRESS VARCHAR2(40)
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

• A. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
• C. INSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

Answer: C

NEW QUESTION 4
Examine the types and examples of relationship that follows: (Choose the best answer.)
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?

• A. 1-d, 2-b, 3-a, and 4-c
• B. 1-c, 2-d, 3-a, and 4-b
• C. 1-a, 2-b, 3-c, and 4-d
• D. 1-c, 2-a, 3-b, and 4-d

Answer: C

NEW QUESTION 5
You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.) Which query generates the required output?

• A. SELECT * FROM users Where user_name LIKE '%ch_';
• B. SELECT * FROM usersWhere user_name LIKE '%ch_%'ESCAPE'%';
• C. SELECT * FROM users Where user_name LIKE 'ch_%' ESCAPE '_';
• D. SELECT * FROM users Where user_name LIKE '%ch_%' ESCAPE '';

Answer: B

NEW QUESTION 6
Evaluate this ALTER TABLE statement: (Choose the best answer.) ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

• A. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
• B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
• C. ROLLBACK can be used to restore the ORDER_DATE column.
• D. The DESCRIBE command would still display the ORDER_DATE column.

Answer: A

NEW QUESTION 7
Which three statements are true regarding group functions? (Choose three.)

• A. They can be used on columns or expressions.
• B. They can be passed as an argument to another group function.
• C. They can be used only with a SQL statement that has the GROUP BY clause.
• D. They can be used on only one column in the SELECT clause of a SQL statement.
• E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer: ABE

Explanation:
References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

NEW QUESTION 8
Examine the structure of the MEMBERS table: NameNull?Type
------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?

• A. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;
• B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
• C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
• D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Answer: B

NEW QUESTION 9
View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)

You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ' ';
Which statement is true?

• A. It executes successfully but returns no result.
• B. It executes successfully and returns the required result.
• C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
• D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Answer: A

NEW QUESTION 10
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?

• A. PUBLIC should be replaced with specific usernames.
• B. ALL should be replaced with a list of specific privileges.
• C. WITH GRANT OPTION should be added to the statement.
• D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Answer: D

Explanation:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

NEW QUESTION 11
View the Exhibit and examine the structure of the PORDUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key. You create an index using this command: SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table. Which query would use the UPPER_NAME_IDX index?

• A. SELECT product_id, UPPER(product_name)FROM product_informationWHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
• B. SELECT UPPER(product_name)FROM product_information;
• C. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
• D. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Answer: D

NEW QUESTION 12
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. (Choose two.)
Which two SQL statements would create the required table?

• A. CREATE TABLE employees(employee_id NUMBER,Login_id NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));
• B. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
• C. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, Login_id NUMBER UNIQUE, Employee_name VARCHAR2(25),Hire_date DATE);
• D. CREATE TABLE employees(employee_id NUMBER,Login_id NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));
• E. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL, Login_id NUMBER CONSTRAINT login_id_nn NOT NULL,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));

Answer: BE

NEW QUESTION 13
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

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

• A. It produces an error because subqueries cannot be used with the CASE expression.
• B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
• C. It shows COST_REMARK for all the promos in the table.
• D. It produces an error because the subquery gives an error.

Answer: C

NEW QUESTION 14
View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

• A. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);
• B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• C. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
• D. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);

Answer: C

NEW QUESTION 15
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS
SELECT order_id.order_date,customer_id FROM orders;
Which statement is true regarding the above command?

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

Answer: B

NEW QUESTION 16
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true?

• A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
• B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• 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 NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Answer: D

NEW QUESTION 17
View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:
SELECT TO_CHAR (list_price, '$9,999') From product_information;
Which two statements are true regarding the output?

• A. A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
• B. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
• C. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
• D. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

Answer: AC

NEW QUESTION 18
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. (Choose all that apply.)

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN'
------------------------------------- SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.

• A. UNION
• B. MINUS
• C. INTERSECT
• D. UNION ALL

Answer: AD

NEW QUESTION 19
Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output?

• A. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
• B. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
• C. In the second column, 'c' indicates a check constraint.
• D. The STATUS column indicates whether the table is currently in use.

Answer: AC

NEW QUESTION 20
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

• A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
• B. During executing, the oracle server may read data from storage if the required data is not already in memory.
• C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Answer: B

NEW QUESTION 21
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. The outer query stops evaluating the result set of the inner query when the first value is found.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: AC

Explanation:
References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION 22
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

• A. UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
• B. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
• C. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
• D. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Answer: D

NEW QUESTION 23
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?

• A. DELETE orders o, order_items IWHERE o.order_id = i.order_id;
• B. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
• C. DELETE ordersWHERE order_total < 1000;
• D. DELETE order_idFROM ordersWHERE order_total < 1000;

Answer: B

NEW QUESTION 24
......