Latest [Nov 27, 2024] 1z1-071 Exam with Accurate Oracle Database SQL PDF Questions
Take a Leap Forward in Your Career by Earning Oracle 323 Questions
NEW QUESTION # 42
Which two statements are true regarding single row functions? (Choose two.)
- A. INSTR : can be used to find only the first occurrence of a character in a string.
- B. CONCAT : can be used to combine any number of values.
- C. TRIM : can be used to remove all the occurrences of a character from a string.
- D. MOD : returns the quotient of a division.
- E. TRUNC : can be used with NUMBER and DATE values.
- F. SYSDATE : returns the database server current date and time.
Answer: E,F
NEW QUESTION # 43
Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)
- A. Substituting a character string in a text expression with a specified string
- B. Combining more than two columns or expressions into a single column in the output
- C. Displaying a date in a nondefault format
- D. Finding the number of characters in an expression
Answer: A,C,D
NEW QUESTION # 44
What is true about non-equijoin statement performance?
- A. The BETWEEN condition always performs less well than using the >= and <= conditions.
- B. Table aliases can improve performance.
- C. The join syntax used makes no difference to performance.
- D. The BETWEEN condition always performs better than using the >= and <= conditions.
- E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
Answer: B,C
NEW QUESTION # 45
View the Exhibit and examine the description of the EMPLOYEES table.
You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?
- A. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, "Total"FROM EMPLOYEES;
- B. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 "Total"FROM EMPLOYEES;
- C. SELECT first_name, salary, salary*12+salary*commission_pct "Total"FROM EMPLOYEES;
- D. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,
salary,salary+commission_pct))"Total"FROM EMPLOYEES;
Answer: D
NEW QUESTION # 46
Which two statements are true regarding subqueries? (Choose two.)
- A. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
- B. A subquery can appear on either side of a comparison operator.
- C. A subquery can be used only in SQL query statements.
- D. A subquery can retrieve zero or more rows.
- E. Only two subqueries can be placed at one level.
Answer: B,D
NEW QUESTION # 47
You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?
- A. All the table's indexes if any exist, are invalidated but retained.
- B. All existing views and synonyms that refer to the table are invalidated but retained.
- C. Any uncommitted transaction in the session is committed.
- D. Table data is deleted but the table structure is retained.
- E. Table data and the table structure are deleted.
Answer: A,C,E
NEW QUESTION # 48
Examine the structure proposed for the TRANSACTIONStable:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
- A. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.
- B. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
- C. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
- D. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
Answer: A,B
NEW QUESTION # 49
Which statement is true about transactions?
- A. Each Data Definition Language (DDL) statement executed forms a single transaction.
- B. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- C. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
- D. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
Answer: A
Explanation:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
NEW QUESTION # 50
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What must be done to fix the statement?
- A. WITH GRANT OPTION should be added to the statement.
- B. PUBLIC should be replaced with specific usernames.
- C. Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.
- D. ALL should be replaced with a list of specific privileges.
Answer: C
Explanation:
Explanation
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
NEW QUESTION # 51
Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24 MI: SS'
ALTER SESSION SET TIME_ ZONE = '-5:00';
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:
If LOCALTIMESTAMP was selected at the same time what would it return?
- A. 11-JUL-2019 11,00,00,000000AM -05:00
- B. 11-JUL-2019 6,00,00,00000000 AM - 05:00
- C. 11-JUL-2019 6,00,00,000000 AM
- D. 11-JUL-2019 11,00,00,00000000 AM
Answer: B
Explanation:
The LOCALTIMESTAMP function in Oracle Database returns the current date and time in the session time zone. Given that the session time zone was set to -5:00 with the ALTER SESSION SET TIME_ZONE command, LOCALTIMESTAMP will return the date and time adjusted to this session time zone.
The SYSDATE function, on the other hand, returns the current date and time from the operating system of the database server, which is not affected by the session time zone setting. In the result shown, SYSDATE returns
11-JUL-2019 11:00:00, which reflects the system time zone, not the session time zone.
The time difference between the DBTIMEZONE (+00:00) and the session time zone (-5:00) is 5 hours. Since SYSDATE shows the time as 11:00:00 in the system time zone, the LOCALTIMESTAMP adjusted to the session time zone would show the time as 06:00:00.
The correct answer is A: 11-JUL-2019 6:00:00.00000000 AM - 05:00. This represents the local timestamp in the session time zone, which is 5 hours behind the DBTIMEZONE.
Please note that the exact format returned by LOCALTIMESTAMP includes the fractional seconds up to 9 decimal places, and the time zone offset is specified as part of the timestamp.
NEW QUESTION # 52
View the Exhibit and examine, the description for the SALES and CHANNELS tables.
(Choose the best answer.)
You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);
Which statement is true regarding the result?
- A. The statement will execute and a new row will be inserted in the SALES table.
- B. The statement will fail because the VALUES clause is not required with the subquery.
- C. The statement will fail because a subquery cannot be used in a VALUES clause.
- D. The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
Answer: A
NEW QUESTION # 53
Which two statements are true about substitution variables? (Choose two.)
- A. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session
- B. A substitution variable prefixed with & always prompts only once for a value in a session
- C. A substitution variable can be used with any clause in a SELECT statement
- D. A substitution variable can be used only in a SELECT statement
- E. A substitution variable used to prompt for a column name must be enclosed in a single quotation marks
- F. A substitution variable used to prompt for a column name must be enclosed in double quotation marks
Answer: B,E
NEW QUESTION # 54
The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
- A. It recovers only the table structure.
- B. It is not possible to recover the table structure, data, or the related indexes.
- C. It recovers the table structure and data but not the related indexes.
- D. It recovers the table structure, data, and the indexes.
Answer: B
NEW QUESTION # 55
View the Exhibit and examine the structure of ORDERS and CUSTOMERStables.
There is only one customer with the cust_last_namecolumn having value Roberts. Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600?
- A. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600 ) 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
VALUES(1, '10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600), 1000); - C. INSERT INTO orders (order_id, order_data, 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); - D. INSERT INTO orders (order_id, order_data, 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).
Answer: B
Explanation:
Explanation
NEW QUESTION # 56
Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?
- A. STUDENT_IDmust be the primary key in the STUDENTSentity and foreign key in the PROJECTSentity.
- B. The ERD must have a M:M relationship between the STUDENTSand PROJECTSentities that must be resolved into 1:M relationships.
- C. PROJECT_IDmust be the primary key in the PROJECTSentity and foreign key in the STUDENTSentity.
- D. The ERD must have a 1:M relationship between the STUDENTSand PROJECTSentities.
- E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
Answer: B,E
Explanation:
Explanation/Reference:
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html
NEW QUESTION # 57
Examine this statement which executes successfully:
Which statement will violate the CHECK constraint?
- A. SELECT *
FROM emp80
WHERE department_id=80; - B. SELECT *
FROM emp80
WHERE department_id=90; - C. UPDATE emp80
SET department_id=90
WHERE department_id=80; - D. DELETE FROM emp80
WHERE department_id=90;
Answer: C
NEW QUESTION # 58
View the Exhibit and examine the structure of CUSTOMERS table.
Evaluate the following query:
Which statement is true regarding the above query?
- A. It executes successfully.
- B. It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
- C. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
- D. It produces an error because the condition on the CUST_CITY column is not valid.
Answer: A
NEW QUESTION # 59
Examine this SQL statement:
DELETE FROM employees e
WHERE EXISTS
(SELECT'dummy'
FROM emp_history
WHERE employee_id = e.employee_id)
Which two are true?
- A. All existing rows in the EMPLOYEE table are deleted.
- B. The subquery is executed for every row in the EMPLOYEES table.
- C. The subquery is executed before the DELETE statement is executed.
- D. The subquery is not a correlated subquery.
- E. The DELETE statement executes successfully even if the subquery selects multiple rows.
Answer: B,E
NEW QUESTION # 60
Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS
- A. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.
- B. SYSDATE can be queried only from the DUAL table.
- C. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
- D. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.
- E. CURRENT_DATE returns the current date and time as per the session time zone
- F. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
Answer: A,E
Explanation:
In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:
* C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.
* D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.
Options A, B, E, and F are incorrect based on Oracle's documentation:
* A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.
* B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.
* E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.
* F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.
NEW QUESTION # 61
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 * NVL
(monthly commission pct,0) ) As annual _ comp
FROM employees; - B. SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS
annual_ comp
FROM employees; - C. SELECT last name,
(monthly salary*12) + (monthly_commission_pct * 12) AS
annual comp
FROM employees
; - D. SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_
FROM employees ;
Answer: A
NEW QUESTION # 62
Examine the structure of the PROGRAMS table:
Which two SQL statements would execute successfully?
- A. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
- B. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;
- C. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs
- D. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
Answer: C,D
NEW QUESTION # 63
Evaluate the following two queries:
Which statement is true regarding the above two queries?
- A. There would be no change in performance.
- B. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
- C. Performance would degrade in query 2.
- D. Performance would improve in query 2.
Answer: A
NEW QUESTION # 64
......
Authentic Best resources for 1z1-071 Online Practice Exam: https://www.braindumpquiz.com/1z1-071-exam-material.html
Practice To 1z1-071 - BraindumpQuiz Remarkable Practice On your Oracle Database SQL Exam: https://drive.google.com/open?id=1mAJzMgmoH0IEuxGbAnYNvWnPly4MmGSm