[Dec-2024] 1z1-071 Exam Questions and Valid 1z1-071 Dumps PDF
1z1-071 Brain Dump: A Study Guide with Tips & Tricks for passing Exam
NEW QUESTION # 32
Examine the description of the BRICKS table;
Examine the description of the BRICKS_STAGE table;
Which two queries execute successfully?
- A. SELECT shape,color,weight from bricks
MINUS
SELECT * FROM bricks_stage; - B. select * from bricks
MINUS
select * from bricks_stage; - C. SELECT brick_id,shape FROM bricks
MINUS
SELECT WEIGHT,COLOR from bricks_stage; - D. SELECT shape,color FROM bricks
MINUS
SELECT WEIGHT,color FROM bricks_stage; - E. SELECT shape,color FROM bricks
MINUS
SELECT color,shape FROM bricks_stage;
Answer: C,E
NEW QUESTION # 33
The sales table has columns prod_id and quantity_sold of data type number. Which two queries execute successfully?
- A. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;
- B. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;
- C. SELECT prod Id FROH sales NHERE quantity sold > 55000 3RODI BY prod_id HAVING COUNT(*) > 10;
- D. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND
COUNT(*) > 10 GROUP BY COUNT(*) > 10; - E. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;
Answer: B,C
NEW QUESTION # 34
Which two statements are true about single row functions?
- A. CONCAT : can be used to combine any number of values
- B. mod : returns the quotient of a division operation
- C. ceil : can be used for positive and negative numbers
- D. FLOOR : returns the smallest integer greater than or equal to a specified number
- E. TRUNC : can be used with number and date values
Answer: A,C
NEW QUESTION # 35
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
- A. select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees
- B. SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;
- C. SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp
- D. SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees;
Answer: C
NEW QUESTION # 36
Which three statements are true about views in an Oracle Database?
- A. Views have no segment.
- B. Rows inserted into a table using a view are retained in the table if the view is dropped.
- C. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
- D. Views can join tables only if they belong to the same schema.
- E. Views have no object number.
- F. A view can be created that refers to a non-existent table in its defining query.
Answer: D,E,F
NEW QUESTION # 37
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 PROD ID in a pro motion time interval.
Examine this SQL statement:
SELECT prod id
FROM costs
WHERE promo id IN
(SELECT promo id
FROM promotions
WHERE promo_cost < ALL
(SELECT MAX (promo cost)
FROM promotions
GROUP BY (promo_end date-promo_begin_date)) );
What will be the result?
- A. It gives an error because the GROUP BY clause is not valid
- B. It gives an error because the ALL keyword is not valid.
- C. It executes successfully and gives the required result.
- D. It executes successfully but does not give the required result.
Answer: D
NEW QUESTION # 38
Which statements is true about using functions in WHERE and HAVING?
- A. using aggregate functions in the WHERE clause requires a subquery
- B. using aggregate functions in the HAVING clause requires a subquery
- C. using single-row functions in the HAVING clause requires a subquery
- D. using single-row functions in the WHERE clause requires a subquery
Answer: D
NEW QUESTION # 39
Which two statements are true regarding the USING and ON clauses in table joins?
- A. The ON clause can be used to join tables on columns that have different names but compatible data types.
- B. A maximum of one pair of columns can be joined between two tables using the ON clause.
- C. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
- D. Both USING and ON clauses can be used for equijoins and nonequijoins.
Answer: A,C
NEW QUESTION # 40
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_DATE column would allow storage of dates only in the dd-mon-yyyy format.
- B. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
- C. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
- D. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
Answer: B,D
NEW QUESTION # 41
Which two statements are true regarding multiple-row subqueries? (Choose two.)
- A. They use the < ALL operator to imply less than the maximum.
- B. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
- C. They always contain a subquery within a subquery.
- D. They can contain group functions.
- E. They can be used to retrieve multiple rows from a single table only.
Answer: B,D
NEW QUESTION # 42
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
- A. select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees
- B. SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:
- C. SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;
- D. SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission_pct, 0)) AS annual _comp
Answer: D
Explanation:
The correct way to compute the total annual compensation, which includes the monthly salary and the monthly commission (if any), is:
* Option C: SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;
* This statement takes the monthly salary and multiplies it by 12 to get the annual salary, and then adds the annual commission which is the monthly salary multiplied by the commission percentage (if any, else 0) and then by 12.
Options A, B, and D are incorrect because:
* Option A: Does not handle the case where the commission percentage is NULL which would result in NULL for the entire expression when added to the monthly salary.
* Option B: Does not consider that the commission percentage might be NULL which could lead to incorrect calculations (or NULL values if commission is NULL).
* Option D: Incorrectly adds the monthly commission percentage directly to the annual salary without considering that the percentage needs to be applied to the salary.
NEW QUESTION # 43
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?
- A. Each SELECT statement in the query can have an ORDER BY clause.
- B. There must be an equal number of columns in each SELECT list.
- C. None of the set operators can be used when selecting CLOB columns.
- D. The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
- E. The FOR UPDATE clause cannot be specified.
Answer: B,E
NEW QUESTION # 44
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)
- A. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
- B. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
- C. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
- D. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
- E. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
- F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.
Answer: B,D
Explanation:
Explanation
http://www.techonthenet.com/sql/and_or.php
NEW QUESTION # 45
Which two statements are true about the SET VERIFY ON command? (Choose two.)
- A. It can be used only in SQL*Plus
- B. It displays values for variables used only in the WHERE clause of a query
- C. It displays values for variables created by the DEFINE command
- D. It displays values for variables prefixed with &&
- E. It can be used in SQL Developer and SQL*Plus
Answer: A,C
NEW QUESTION # 46
Which three statements are true about GLOBAL TEMPORARY TABLES?
- A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
- B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
- C. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
- D. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
- E. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.
- F. GLOBAL TEMPORARY TABLE space allocation occurs at session start.
Answer: A,D,F
NEW QUESTION # 47
Examine the data in the EMPLOYEEStable.
Which statement will compute the total annual compensation for each employee?
SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 *
- A. annual_comp
FROM employees;
SELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS - B. annual_comp
FROM employees;
SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL - C. (monthly_commission_pct, 0)) AS annual_comp
FROM employees; - D. monthly_commission_pct) AS annual_comp
FROM employees;
SELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS
Answer: D
NEW QUESTION # 48
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.
You want to update EMPLOYEEStable as follows:
* Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
* Set department_idfor these employees to the department_idcorresponding to London (location_id
2100).
* Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.
* Set the employees' commission in location_id2100 to 1.5 times the average commission of their department.
You issue the following command:
What is outcome?
- A. It generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.
- B. It executes successfully and gives the desired update
- C. It generates an error because a subquery cannot have a join condition in a UPDATEstatement.
- D. It executes successfully but does not give the desired update
Answer: D
NEW QUESTION # 49
View the exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement?
- A. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
- B. listing of customers who do not have a credit limit and were born before 1980
- C. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
- D. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
- E. finding the number of customers, in each city, who's marital status is 'married'.
Answer: A,C
NEW QUESTION # 50
Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?
- A. The first VARCHAR2 column in the first SELECT of the compound query
- B. The first column in the last SELECT of the compound query
- C. The first NUMBER or VARCHAR2 column in the last SELECTof the compound query
- D. The first NUMBER column in the first SELECT of the compound query
- E. The first column in the first SELECT of the compound query
Answer: E
Explanation:
For the sorting of output in compound queries (INTERSECT, UNION, etc.):
* D. The first column in the first SELECT of the compound query: By default, Oracle does not automatically sort the results of SET operations unless an ORDER BY clause is explicitly stated.
However, if an ORDER BY is implied or specified without explicit columns, the default sorting would logically involve the first column specified in the first SELECT statement of the compound query.
NEW QUESTION # 51
......
1z1-071 Exam Questions: Free PDF Download Recently Updated Questions: https://vcecollection.trainingdumps.com/1z1-071-valid-vce-dumps.html
