Latest Oracle 1z1-071 First Attempt, Exam real Dumps Updated [Jan-2022]
Get the superior quality 1z1-071 Dumps Questions from ITPassLeader. Nobody can stop you from getting to your dreams now. Your bright future is just a click away!
NEW QUESTION 34
You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
- A. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name, -2, 1)='a'; - B. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a'; - C. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, -2, 1)='a'; - D. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name, 1, -2)='a';
Answer: C
NEW QUESTION 35
View the Exhibits and examine PRODUCTS and SALES tables.
Exhibit 1
Exhibit 2
You issue the following query to display product name the number of times the product has been sold:
What happens when the above statement is executed?
- A. The statement executes successfully and produces the required output.
- B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
- C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
- D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
Answer: A
NEW QUESTION 36
Examine the structure of the MEMBERS table: (Choose the best answer.)
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution?
- A. It fails because a column alias cannot be used in the ORDER BY clause.
- B. It displays all cities in ascending order, within which the last names are further sorted in descending order.
- C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- D. It displays all cities in descending order, within which the last names are further sorted in descending order.
Answer: B
NEW QUESTION 37
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 view successfully?
- A. 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;
- B. 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; - 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;
Answer: D
NEW QUESTION 38
View the exhibit and examine the description of the PRODUCT_INFORMATIONtable.
Which SQL statement would retrieve from the table the number of products having LIST_PRICEas NULL?
SELECT COUNT (DISTINCT list_price)
- A. FROM product_information
WHERE list_price i= NULL
SELECT COUNT (list_price) - B. FROM product_information
WHERE list_price is NULL
SELECT COUNT (list_price) - C. FROM product_information
WHERE list_price is NULL
SELECT COUNT (NVL(list_price, 0)) - D. FROM product_information
WHERE list_price is NULL
Answer: B
NEW QUESTION 39
View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
- A. Statement 1 would not return give the same output.
- B. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.
- C. Statement 1 would return only one row of output.
- D. Statement 2 would return multiple rows of output.
- E. Both the statements would give the same output.
Answer: B,C,D
NEW QUESTION 40
View the Exhibits and examine PRODUCTSand SALES tables.
Exhibit 1
Exhibit 2
You issue the following query to display product name the number of times the product has been sold:
What happens when the above statement is executed?
- A. The statement produces an error because a subquery in the FROMclause and outer-joins cannot be used together.
- B. The statement executes successfully and produces the required output.
- C. The statement produces an error because ITEM_CNTcannot be displayed in the outer query.
- D. The statement produces an error because the GROUP BYclause cannot be used in a subquery in the FROMclause.
Answer: B
NEW QUESTION 41
View the exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which three DELETE statements are valid? (Choose three.)
- A. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale')OR promo_name = 'everyday low price');
- B. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale')OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price')
- C. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promo_name = 'blowout sale')AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price')FROM promotionsWHERE promo_name = 'everyday low price');
- D. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN = 'blowout sale','everyday low price'));
Answer: A,B,D
NEW QUESTION 42
Examine this description of the PRODUCTS table:
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?
- A. ALTER TABLE products DROP UNUSED COLUMNS;
- B. CREATE INDEX price_idx ON products (price);
- C. ALTER TABLE products DROP COLUMN expiry_date;
- D. DROP TABLE products;
- E. ALTER TABLE products SET UNUSED (expiry_date);
- F. TRUNCATE TABLE products;
Answer: B,D,F
NEW QUESTION 43
Table EMPLOYEEScontains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEE_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
Which two statements are true? (Choose two.)
- A. Employee 200will have SALARYset to the same value as the SALARYof employee 100
- B. Employees 100and 200 will have the same SALARYas before the update command
- C. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
- D. Employee 100will have SALARYset to the same value as the SALARYof employee 200
- E. Employees 100will have JOB_ID set to the same value as the JOB_ID of employee 200
- F. Employees 100 and 200will have the same JOB_ID as before the update command
Answer: E,F
NEW QUESTION 44
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 not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- B. 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.
- 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 45
Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?
- A. It displays 5 percent of the products with the lowest amount sold.
- B. It displays 5 percent of the products with the highest amount sold.
- C. It displays the first 5 percent of the rows from the SALES table.
- D. It results in an error because the ORDER BY clause should be the last clause.
Answer: A
Explanation:
Explanation
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION 46
View the exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
- A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
- B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= 'INTERNET';
- C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= 'INTERNET');
- D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= 'INTERNET');
Answer: A
NEW QUESTION 47
The user SCOTTwho is the owner of ORDERSand ORDER_ITEMStables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
- A. WITH GRANT OPTIONshould be added to the statement.
- B. PUBLICshould be replaced with specific usernames.
- C. ALLshould be replaced with a list of specific privileges.
- D. Separate GRANTstatements are required for ORDERSand ORDER_ITEMS tables.
Answer: D
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
NEW QUESTION 48
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. DELETE <sequencename> would remove a sequence from the database
- C. 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
- D. CURRVAL is used to refer to the last sequence number that has been generated
- E. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
Answer: D,E
Explanation:
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs
independent of a commit or rollback. Therefore, if you roll back a statement containing a
sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence
caches values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for
multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;
NEW QUESTION 49
......
Guaranteed Success with Valid Oracle 1z1-071 Dumps: https://certlibrary.itpassleader.com/Oracle/1z1-071-dumps-pass-exam.html