Verified of 1z0-047 actual exam materials and braindump for Oracle certification for candidates, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!
2021 May 1z0-047 Study Guide Questions:
Q1. Which two statements are true regarding subqueries? (Choose three.)
A. The ORDER BY clause can be used in the subquery.
B. A subquery can be used in the FROM clause of a SELECT statement.
C. If the subquery returns NULL, the main query may still return result rows.
D. A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause.
E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
Answer: ABC
Q2. Evaluate the SQL statements:
CREATE TABLE new_order
(orderno NUMBER(4),
booking_date TIMESTAMP WITH LOCAL TIME ZONE);
The database is located in San Francisco where the time zone is -8:00.
The user is located in New York where the time zone is -5:00.
A New York user inserts the following record:
INSERT INTO new_order
VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?);
Which statement is true?
A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.
C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000'
D. When the San Francisco user selectstherow, booking_dateis displayed
as 007-05-103.00.00.000000-8:00'
Answer: C
Q3. View the Exhibit and examine the data in the CUST_DET table.
You executed the following multitable INSERT statement:
INSERT FIRST
WHEN credit_limit >= 5000 THEN
INTO cust_1 VALUES(cust_id, credit_limit, grade, gender)
WHEN grade = THE
INTO cust_2 VALUES(cust_id, credit_limit, grade, gender)
WHEN gender = THE
INTO cust_3 VALUES(cust_id, credit_limit, grade, gender)
INTO cust_4 VALUES(cust_id, credit_limit, grade, gender)
ELSE
INTO cust_5 VALUES(cust_id, credit_limit, grade, gender)
SELECT * FROM cust_det;
The row will be inserted in________.
A. CUST_1 table only because CREDIT_LIMIT condition is satisfied
B. CUST_1and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied
C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied
D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it
Answer: A
Improved 1z0-047 oracle database sql expert exam dumps:
Q4. Which two statements are true about the GROUPING function? (Choose two.)
A. Itis used to find the groups forming the subtotal in a row.
B. It is used to identify the NULL value in the aggregate functions.
C. It is used to form the group sets involved in generating the totals and subtotals.
D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.
Answer: AD
Q5. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
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 orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total)
VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);
C. INSERT INTO orders (order_id.order_date.order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit _limit=600) .order_total)
VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);
D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id,
o.order_totalFROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.cust_last_name='Roberts'ANDc. Credit_limit=600)
VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
Credit_limit=600), 1000);
Answer: A
Q6. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. Which is the best method to load data from the MARKS_DETAILStable to the MARKS table?
A. PivotingINSERT
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: A
Validated 1z0-047 study guide:
Q7. View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement:
SELECT category_name ,category_description FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)
A. WHEREREGEXPJJKE(category_description, 'hard+.s’);
B. WHERE REGEXPJJKE(category_description,‘^H|hard+.s’);
C. WHERE REGEXPJJKE (category_description, '^H|hard+.s$');
D. WHEREREGEXPJJKE (category_description, '[^Hlhard+.s]');
Answer: AB
Q8. 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 SOL statement would you execute to accomplish the task?
A. UPDATE deptd
SET city = ANY (SELECT city FROM locations I);
B. UPDATE deptd
SET city = (SELECT city
FROM locations I)
WHERE d.location_id = l.location_id;
C. UPDATE deptd
SET city = (SELECT city
FROM locations I
WHERE d.location_id = l.location_id);
D. UPDATE deptd
SET city = ALL (SELECT city
FROM locations I
WHERE d.location_id = l.location_id);
Answer: C
Q9. View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables.
In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key.
Which view can have all the DML operations performed on it?
A. CREATE VIEWV1
ASSELECTorder_id, product_id
FROMorder_items;
B. CREATE VIEW V4(or_no, or_date, cust_id)
ASSELECTorder_id, order_date, customer_id
FROM orders
WHEREorder_date < 30-mar-2007'
WITH CHECK OPTION;
C. CREATEVIEW V3
ASSELECT o.order_id,o.customer_id, i.product_id
FROM orders o, order_items i
WHERE o.order_id=i.order_id;
D. CREATE VIEWV2
AS SELECT order_id, line_item_id, unit_price*quantity total
FROM order items;
Answer: B
Q10. Given below is a list of datetime data types and examples of values stored in them in a random order:
Datatype Example
1) INTERVAL YEAR TO MONTH a) 2003-04-15 8:00:00 -8:00'
2) TIMESTAMP WITH LOCAL TIME ZONE b) '-K)6 03:30:16.000000'
3) TIMESTAMP WITH TIME ZONE c) '17-JUN-03 12.00.00.000000 AM'4)INTERVAL DAY TO
SECOND d) '402-00'
Identify the option that correctly matches the data types with the values.
A. 1-d.2-c.3-a.4-b
B. 1-b.2-a.3-c.4-d
C. 1-b.2-a,3-d,4-c
D. 1-d.2-c.3-b.4-a
Answer: A