Free Oracle 1Z1-051 Practice Test & Real Exam Questions, Page 2

Question #11

The PRODUCTS table has these columns:
PRODUCT_ID NUMBER(4)
PRODUCT_NAME VARCHAR2(45)
PRICE NUMBER(8,2)
Evaluate this SQL statement:
SELECT *
FROM PRODUCTS
ORDER BY price, product_name;
What is true about the SQL statement?

A. The results are sorted alphabetically.

B. The results are sorted numerically and then alphabetically.

C. The results are sorted numerically.

D. The results are not sorted.

Discussion 0

Correct Answer: B Vote an answer

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

Question #12

Which two statements are true about sequences created in a single instance database? (Choose two.)

A. The numbers generated by a sequence can be used only for one table

B. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted

C. When the MAXVALUE limit for a sequence isreached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

D. DELETE would remove a sequence from the database

E. CURRVAL is used to refer to the last sequence number that has been generated

Discussion 0

Correct Answer: C,E Vote an answer

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

Question #13

Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

A. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.

B. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

C. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.

D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.

Discussion 0

Correct Answer: A,C Vote an answer

Question #14

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

You want to display the category with the maximum number of items. You issue the following query:
SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?

A. It generates an error because the subquery does not have a GROUP BY clause.

B. It generates an error because = is not valid and should be replaced by the IN operator.

C. It executes successfully and gives the correct output.

D. It executes successfully but does not give the correct output.

Discussion 0

Correct Answer: A Vote an answer

Question #15

Evaluate the following SQL statements:
Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?

A. The CHECK constraint cannot be placed on columns having the DATE data type

B. The BETWEEN clause cannot be used for the CHECK constraint

C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY

D. SYSDATE cannot be used with the CHECK constraint

Discussion 0

Correct Answer: D Vote an answer

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

Question #16

See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit: Which two SQL statements would executes successfully? (Choose two.)

A. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

B. SELECT (AVG(inv_date) FROM invoice;

C. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

D. SELECT AVG(inv_date - SYSDATE),AVG(inv)amt) FROM invoice;

Discussion 0

Correct Answer: C,D Vote an answer

Question #17

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)

A. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo';

B. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p
JOIN saless
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo';

C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
AND c.cust_city='Tokyo';

D. SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo';

Discussion 0

Correct Answer: C,D Vote an answer

Question #18

View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)

A. SELECT promo_name. promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:

B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC; B. SELECT promo_name. promo_begiii_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:

C. SELECT promo_name, promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;

Discussion 0

Correct Answer: B,C Vote an answer

Question #19

See the structure of the PROGRAMS table:

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

A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;

B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;

C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;

D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;

Discussion 0

Correct Answer: A,D Vote an answer

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

Question #20

Which two statements are true about WHERE and HAVING clauses? (Choose two)

A. A HAVING clause can be used to restrict groups only.

B. A HAVING clause CANNOT be used in sub queries.

C. A WHERE clause can be used to restrict both rows and groups.

D. A HAVING clause can be used to restrict both rows and groups.

E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.

F. A WHERE clause can be used to restrict rows only.

Discussion 0

Correct Answer: A,F Vote an answer

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

Free Oracle 1Z1-051 Practice Test & Real Exam Questions

Download Free Oracle 1Z1-051 Demo