1Z0-051 by Oracle Actual Free Exam Q&As

Question 1

You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)

A. All views and synonyms will remain but they are invalidated B. All data in the table are deleted but the table structure will remain C. All indexes on the table will remain but they are invalidated D. All data along with the table structure is deleted E. The pending transaction in the session is committed

Discussion 0

Correct Answer: A,D,E Vote an answer

Question 2

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

and examine the structure of CUSTOMRS AND SALES tables: Evaluate the following SQL statement:
Exhibit:

Which statement is true regarding the execution of the above UPDATE statement?

A. It would not execute because two tables cannot be used in a single UPDATE statement B. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement C. It would execute and restrict modifications to only the column specified in the SELECT statement D. It would not execute because the SELECT statement cannot be used in place of the table name

Discussion 0

Correct Answer: C Vote an answer

Question 3

Which statement is true regarding synonyms?

A. Synonyms can be created only for a table B. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid C. Synonyms are used to reference only those tables that are owned by another user D. A public synonym and a private synonym can exist with the same name for the same table

Discussion 0

Correct Answer: D Vote an answer

Question 4

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:

What would be the outcome of the above statement?

A. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses. B. It executes successfully. C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values. D. It produces an error because the condition on CUST_LAST_NAME is invalid.

Discussion 0

Correct Answer: B Vote an answer

Question 5

View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format: CATEGORIES 5MP Digital Photo Camera's category is Photo Y Box's category is Electronics Envoy Ambassador's category is Hardware Which two queries would give the required output? (Choose two.)

A. SELECT prod_name || q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products; B. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products; C. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products; D. SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;

Discussion 0

Correct Answer: A,D Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 6

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. 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?

A. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id.
o.ordertotal
FROM orders o. customers c
WHERE o.customer_id = c.customerid
AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600)
VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_id
FROM customers
WHERE cust_last_iiame='Roberts' AND
credit_limit=600). 1000); B. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); C. INSERT INTO orders (order_id.order_date.order_mode.
(SELECT customer_id
FROM customers
WHERE cust_last_iiame='Roberts' AND
credit_limit=600).order_total)
VALUES(l.'10-mar-2007\ 'direct'. &customer_id. 1000): D. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total)
VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000):

Discussion 0

Correct Answer: B Vote an answer

Question 7

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

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

A. It executes successfully. B. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. C. It returns an error because the BETWEEN operator cannot be used in the HAVING clause. D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

Discussion 0

Correct Answer: A Vote an answer

Question 8

Examine the structure of the PROMOS table:

You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)

A. SELECT
promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',
promo_end_date - promo_start_date)
FROM promos; B. SELECT promo_name, DECODE(promo_end_date
-promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM
promos; C. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date promo_start_date),'ONGOING') FROM promos; D. SELECT promo_name, NVL(TO_CHAR(promo_end_date -
promo_start_date),'ONGOING') FROM promos; E. SELECT promo_name, TO_CHAR(NVL(promo_end_date promo_start_date,'ONGOING')) FROM promos;

Discussion 0

Correct Answer: B,C,D Vote an answer

Question 9

Which CREATE TABLE statement is valid?

A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL); B. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no)); C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no)); D. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 10

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;

A. CREATE SYNONYM ord FOR orders; This command is issued by OE. B. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator. C. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator. D. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Oracle Database: SQL Fundamentals I - 1Z0-051 Exam Practice Test