Free Access Oracle.1z0-071.v2022-02-20.q161 Practice Test (Page 11)

Question 46

The SYSDATEfunction displays the current Oracle Server date as:
21-MAY-19
You wish to display the date as:
MONDAY, 21 MAY, 2019
Which statement will do this?
SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

A.SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;

B.SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;

C.SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

Question 47

Examine the structure of the PROMOTIONS table:

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

A.SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

B.SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

C.SELECT DISTINCT promo_cost, promo_category FROM promotions;

D.SELECT promo_category, DISTINCT promo_cost FROM promotions;

Question 48

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?

A.CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

B.CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

C.CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
||"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;

D.CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

Question 49

View the exhibit and examine the structure of the STORES table.
STORES table
NameNull?Type
---------------------- -------------
STORE_IDNUMBER
NAMEVARCHAR2(100)
ADDRESSVARCHAR2(200)
CITYVARCHAR2(100)
COUNTRYVARCHAR2(100)
START_DATEDATE
END_DATEDATE
PROPERTY_PRICENUMBER
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01- Jan-2000 and above.
Which SQL statement would get the desired output?

A.SELECT name, concat (address| | ','| |city| |', ', country) AS
full_address,start_date,property_price, property_price*115/100FROM storesWHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

B.SELECT name, concat (address||','| |city| |', ', country) AS
full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

C.SELECT name, concat (address| | ','| |city| |', ', country) AS
full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;

D.SELECT name, address||','||city||','||country AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Question 50

Examine the description of the CUSTOMERS table:

You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000.
Examine this partial query: