Oracle 1z0-071 Daily Practice Exam New 2022 Updated 305 Questions
Use Valid 1z0-071 Exam - Actual Exam Question & Answer
NEW QUESTION 43
Which task can be performed by using a single Data Manipulation Language (OML) statement?
- A. adding a column constraint when inserting a row into a table
- B. removing all data only from one single column on which a primary key constraint is defined
- C. adding a column with a default value when inserting a row into a table
- D. removing all data only from one single column on which a unique constraint is defined
Answer: B
NEW QUESTION 44
Which three statements are true regarding subqueries? (Choose three.)
- A. Only one column or expression can be compared between the main query and subquery.
- B. Subqueries can contain ORDER BY but not the GROUP BY clause.
- C. Subqueries can contain GROUP BY and ORDER BY clauses.
- D. Main query and subquery must get data from the same tables.
- E. Multiple columns or expressions can be compared between the main query and subquery.
- F. Main query and subquery can get data from different tables.
Answer: C,E,F
Explanation:
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html
NEW QUESTION 45
View the Exhibit and examine the structure in the EMPLOYEES tables.
Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?
- A. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
- B. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
- C. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
- D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
Answer: D
NEW QUESTION 46
Examine the structure of the DEPARTMENTS table.
You execute the following command:
Which two statements are true?
- A. Indexes created on the COUNTRYcolumn exist until the DROP UNUSED COLUMNScommand is executed.
- B. Views created in the DEPARTMENTStable that include the COUNTRYcolumn are automatically modified and remain valid.
- C. Unique key constraints defined on the COUNTRYcolumn are removed.
- D. Synonyms existing of the DEPARTMENTStable would have to be re-created.
- E. A new column, COUNTRY, can be added to the DEPARTMENTStable after executing the command.
Answer: C,E
NEW QUESTION 47
Which two are true about granting privilege on objects?
- A. The owner of an object acquires all object privilege on that object by default
- B. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table
- C. The WITH GRANT OPTION clause can be used only by DBA users
- D. An object privilege can be granted to other users only by the owner of that object
- E. An object privilege can be granted to a role only by the owner of that object
Answer: A,B
NEW QUESTION 48
You want to display the date for the first Monday of the next month and issue the following command:
What is the outcome?
- A. In generates an error because TO_CHARshould be replaced with TO_DATE.
- B. It executes successfully and returns the correct result.
- C. In generates an error because rrrrshould be replaced by rrin the format string.
- D. In generates an error because fmand double quotation marks should not be used in the format string.
- E. It executes successfully but does not return the correct result.
Answer: B
NEW QUESTION 49
Which two statements are true regarding non equijoins?
- A. The USING clause can be used.
- B. The ON clause can be used.
- C. The Oracle join syntax can be used.
- D. Table aliases must be used.
- E. The SQL:1999 compliant ANSI join syntax must be used.
Answer: B,C
NEW QUESTION 50
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTSand TIMEStables.
The PROD_IDcolumn is the foreign key in the SALEStable referencing the PRODUCTStable.
The CUST_IDand TIME_IDcolumns are also foreign keys in the SALEStable referencing the CUSTOMERS and TIMEStables, 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_SALEStable would not get created because the column names in the CREATETABLE command and the SELECTclause do not match.
- B. The NEW_SALEStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
- C. The NEW_SALEStable would get created and all the FOREIGNKEYconstraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- D. The NEW_SALEStable would get created and all the NOTNULLconstraints 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 51
View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best answer.)
You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?
- A. It executes successfully and gives the correct output.
- B. It generates an error because = is not valid and should be replaced by the IN operator.
- C. It executes successfully but does not give the correct output.
- D. It generate an error because the subquery does not have a GROUP BY clause.
Answer: D
NEW QUESTION 52
You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?
- A. Table data is deleted but the table structure is retained.
- B. All existing views and synonyms that refer to the table are invalidated but retained.
- C. All the table's indexes if any exist, are invalidated but retained.
- D. Any uncommitted transaction in the session is committed.
- E. Table data and the table structure are deleted.
Answer: B,C,E
NEW QUESTION 53
Examine the structure of the BOOKS_TRANSACTIONStable:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM
- A. FROM BOOKS_TRANSACTIONS;
SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM - B. BOOKS_TRANSACTIONS;
SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM - C. BOOKS_TRANSACTIONS;
SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" - D. BOOKS_TRANSACTIONS;
Answer: A
NEW QUESTION 54
Examine the structure of the MEMBERStable:
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?
- A. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
- B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
- C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
- D. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
Answer: B
NEW QUESTION 55
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the cust_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 orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES (1,
'10-mar-2007', 'direct', &customer_id, 1000). - B. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' AND c.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
- C. INSERT INTO ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
- D. INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1,
'10-mar-2007', 'direct', &&customer_id, 1000);
Answer: C
NEW QUESTION 56
Examine the structure of the PROMOTIONS table: (Choose the best answer.)
Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?
- A. SELECT promo_category, DISTINCT promo_cost FROM promotions
- B. SELECT DISTINCT promo_cost, promo_category FROM promotions
- C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
- D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
Answer: D
NEW QUESTION 57
View the Exhibit and examine the structure of the ORDERStable.
Which UPDATEstatement is valid?
UPDATE orders
- A. SET order_date = '12-mar-2007',
order_total = NULL
WHERE order_id = 2455;
UPDATE orders - B. SET order_date = '12-mar-2007',
order_total IS NULL
WHERE order_id = 2455;
UPDATE orders - C. SET order_date = '12-mar-2007',
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
UPDATE orders - D. SET order_date = TO_DATE('12-mar-2007','dd-mon-yyyy'),
SET order_total = TO_NUMBER (NULL)
WHERE order_id = 2455;
Answer: A
NEW QUESTION 58
Examine the description of the CUSTOMERStable:
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:
Which two WHEREconditions give the required result? (Choose two.)
- A.
- B.
- C.
- D.
- E.
Answer: A,B
NEW QUESTION 59
View the Exhibits and examine the structure of the costs and promotions tables.
You want to display prod_ids whose promotion cost is less than the highest cost PR0D_ID in a promotion time interval.
Examine this SQL statement:
What will be the result?
- A. It executes successfully and gives the required result.
- B. It gives an error because the ROUP BY clause is not valid.
- C. It gives an error because the all keyword is not valid.
- D. It executes successfully but does not give the required result.
Answer: A
NEW QUESTION 60
In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?
- A. third normal form
- B. first normal form
- C. fourth normal form
- D. second normal form
Answer: D
Explanation:
Explanation
https://blog.udemy.com/database-normal-forms/
NEW QUESTION 61
Which three statements indicate the end of a transaction? (Choose three.)
- A. after a ROLLBACKis issued
- B. after a COMMITis issued
- C. after a CREATEstatement is issued
- D. after a SAVEPOINTis issued
- E. after a SELECTstatement is issued
Answer: A,B,C
NEW QUESTION 62
Which two statements are true about outer Joins?
- A. The outer join operator (+) is used next to the column of the table without the matching rows.
- B. A condition representing an outer join cannot be linked to another condition using the or logical operator.
- C. The IN operator cannot be used in a condition that Involves an outer join.
- D. The outer join operator (+) can be used on both sides of the join condition in an outer join.
- E. An outer join is used to retrieve only the rows that do not meet the join condition.
Answer: B,C
NEW QUESTION 63
Examine the description of the EMPLOYEES table:
Which query is valid?
- A. SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id;
- B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
- C. SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id;
- D. SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date;
Answer: D
NEW QUESTION 64
Examine this statement:
What is returned upon execution?
- A. 0 rows
- B. 2 rows
- C. an error
- D. 1 row
Answer: D
NEW QUESTION 65
......
Test Engine to Practice 1z0-071 Test Questions: https://troytec.dumpstorrent.com/1z0-071-exam-prep.html