Pass 1z0-071 Exam in First Attempt Guaranteed 100% Cover Real Exam Questions [Nov-2021]
Valid 1z0-071 test answers & Oracle 1z0-071 exam pdf
Topics of Oracle 1Z0-071: Oracle Database SQL Exam
The Oracle 1Z0-071 dumps tests the abilities and knowledge of the candidates by checking the following objectives:
Relational Database concepts
- Explaining the relationship between a database and SQL
- Relating clauses in SQL Select Statement to Components of an ERD
- Explaining the theoretical and physical aspects of a relational database
Retrieving Data using the SQL SELECT Statement
- Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
- Using Arithmetic expressions and NULL values in the SELECT statement
- Using The SQL SELECT statement
- Using Column aliases
Restricting and Sorting Data
- Limiting Rows Returned in a SQL Statement
- Using Substitution Variables
- Using the DEFINE and VERIFY commands
- Applying Rules of precedence for operators in an expression
- Sorting Data
Using Single-Row Functions to Customize Output
- Manipulating numbers with the ROUND, TRUNC and MOD functions
- Manipulating dates with the date function
- Manipulating strings with character functions in SQL SELECT and WHERE clauses
- Performing arithmetic with date data
Using Conversion Functions and Conditional Expressions
- Nesting multiple functions
- Understanding implicit and explicit data type conversion
- Applying the NVL, NULLIF, and COALESCE functions to data
- Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Reporting Aggregated Data Using Group Functions
- Restricting Group Results
- Creating Groups of Data
- Using Group Functions
Displaying Data from Multiple Tables
- Understanding and Using Cartesian Products
- Using Self-joins
- Use of non-equijoins
- Using OUTER joins
- Using Various Types of Joins
Using Subqueries to Solve Queries
- Update and delete rows using correlated subqueries
- Using Single Row Subqueries
- Using Multiple Row Subqueries
Using SET Operators
- Using The UNION and UNION ALL operators
- Matching the SELECT statements
- Using the ORDER BY clause in set operations
- Using The INTERSECT operator
- Using The MINUS operator
Managing Tables using DML statements
- Managing Database Transactions
- Perform Insert, Update and Delete operations
- Performing Merge statements
- Performing multi table Inserts
- Controlling transactions
Managing Indexes Synonyms and Sequences
- Managing Synonyms
- Managing Indexes
- Managing Sequences
Use DDL to manage tables and their relationships
- Managing Constraints
- Truncating tables
- Describing and Working with Columns and Data Types
- Describing and Working with Tables
- Creating and using external tables
- Creating and using Temporary Tables
- Dropping columns and setting columns UNUSED
- Creating tables
Managing Views
- Distinguishing between granting privileges and roles
- Differentiating system privileges from object privileges
- Controlling User Access
- Managing Views
- Granting privileges on tables
Managing Objects with Data Dictionary Views
- Managing Data in Different Time Zones
- Working with INTERVAL data types
- Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
- Using data dictionary views
NEW QUESTION 90
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. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
- B. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.
- C. CURRENT_DATE returns the current date and time as per the session time zone
- D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
- E. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.
- F. SYSDATE can be queried only from the DUAL table.
Answer: B,C
NEW QUESTION 91
The PRODUCTS table has the following structure.
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ' ') FROM products; SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products; Which statement is true regarding the outcome?
- A. Only the second SQL statement executes successfully
- B. Both the statements execute and give different results
- C. Only the first SQL statement executes successfully
- D. Both the statements execute and give the same result
Answer: B
Explanation:
Explanation
Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax
NVL2(expr1, expr2, expr3)
In the syntax:
Expr1 is the source value or expression that may contain a null
Expr2 is the value that is returned if expr1 is not null
Expr3 is the value that is returned if expr1 is null
NEW QUESTION 92
Evaluate the following query:
What would be the outcome of the above query?
- A. It produces an error because flower braces have been used.
- B. It produces an error because the data types are not matching.
- C. It executes successfully and introduces an 's at the end of each promo_name in the output.
- D. It executes successfully and displays the literal " {'s start date was \> " for each row in the output.
Answer: C
NEW QUESTION 93
View the Exhibit and examine the structure of the PRODUCT table.
Which two tasks would require subqueries? (Choose two.)
- A. display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
- B. display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
- C. display suppliers whose PROD_LIST_PRICE is less than 1000
- D. display the minimum PROD_LIST_PRICE for each product status
- E. display the total number of products supplied by supplier 102 and have product status as 'OBSOLETE'
Answer: A,B
NEW QUESTION 94
You executed the following CREATE TABLE statement that resulted in an error:
SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename));
Identify two reasons for the error.
- A. The NOT NULL constraint on the ENAME column must be defined as the column level
- B. Only one LONG column can be used per table
- C. FOREIGN KEY defined on the DEPT_ID column must be at the table level only
- D. The FOREIGN KEY keyword is missing in the constraint definition
- E. The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only
Answer: A,B
NEW QUESTION 95
The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.
Which two queries execute successfully?
- A. SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;
- B. SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;
- C. SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;
- D. SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;
- E. SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;
Answer: A,D
NEW QUESTION 96
Examine this statement:
SELECT 1 AS id, ' John' AS first name
FROM DUAL
UNION
SELECT 1 , ' John' AS name
FROM DUAL
ORDER BY 1;
What is returned upon execution?
- A. an error
- B. 2 rows
- C. 0 rows
- D. 1 row
Answer: D
NEW QUESTION 97
Examine the description of the EMPLOYEES table:
Which two queries return the highest salary in the table?
- A. SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id; - B. SELECT MAX (salary)
FROM employees
GROUP BY department_id; - C. SELECT MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX (salary) = MAX (MAX (salary)); - D. SELECT MAX (MAX (salary))
FROM employees
GROUP BY department_id; - E. SELECT MAX (salary)
FROM employees;
Answer: D,E
NEW QUESTION 98
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTSand TIMEStables.
The PROD_IDcolumn is the foreign key in the SALEStable, which references the PRODUCTStable.
Similarly, the CUST_IDand TIME_IDcolumns are also foreign keys in the SALEStable referencing the CUSTOMERSand TIMEStables, respectively.
Evaluate the following CREATE TABLEcommand:
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 regarding the above command?
- 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 get created and all the FOREIGNKEYconstraints defined on the specified columns would be passed to the new table.
- C. The NEW_SALEStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
- D. The NEW_SALEStable would get created and all the NOTNULLconstraints defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 99
Examine the BRICKS table:
You write this query:
SELECT
FROM bricks b1 CROSS JOIN bricks b2
WHERE b1. Weight < b2. Weight:
How many rows will the query return?
- A. 0
- B. 1
- C. 2
- D. 3
- E. 4
- F. 5
Answer: C
NEW QUESTION 100
which is true about the round,truncate and mod functions>?
- A. ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE DIFFERENT RESULTS.
- B. TRUNC(MOD(25,3),-1) IS INVALID.
- C. ROUND(MOD(25,3),-1) IS INVALID
- D. ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE SAME RESULT.
Answer: D
NEW QUESTION 101
Which two statements are true about dropping views?
- A. CASCADE constraints must be specified when referential integrity constraints on other objects refer to primary or unique keys In the view to be dropped.
- B. Data selected by a view's defining query is deleted from Its underlying tables when the view is dropped.
- C. Read only views cannot be dropped.
- D. Views referencing a dropped view become Invalid.
- E. The creator of a view to be dropped must have the drop ANY_ VIEW privilege.
Answer: A,D
NEW QUESTION 102
Examine the description of the ENPLOYES table:
Which query requires explicit data type conversion?
- A. SELECT join _ date FROM employees WHERE join date > *10-02-2018';
- B. SELECT SUBSTR(join date, 1, 2) - 10 FROM employees;
- C. SELECT join_ date + '20' EROM employees;
- D. SELECT salary + '120.50' FROM employees;
- E. SELECT join_ date||''|| salary FROM employees;
Answer: A
NEW QUESTION 103
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
- A. PUBLIC should be replaced with specific usernames.
- B. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.
- C. ALL should be replaced with a list of specific privileges.
- D. WITH GRANT OPTION should be added to the statement.
Answer: B
Explanation:
Explanation
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
NEW QUESTION 104
Examine the data in the CUST_NAMEcolumn of the CUSTOMERStable.
CUST_NAME
-------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?
- A. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
LIKE 'Mc%'; - B. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
LIKE INITCAP ('MC%'); - C. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
'Mc'; - D. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
INITCAP 'MC%';
Answer: A
NEW QUESTION 105
The following are the steps for a correlated subquery, listed in random order:
1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
- A. 4,1,2,3
- B. 2,1,4,3
- C. 2,4,1,3
- D. 4,2,1,3
Answer: C
NEW QUESTION 106
Examine the data in the CUST_NAME column of the CUSTOMERS table:
You want to display the CUST_NAME values where the last name starts with Mc or MC.
Which two WHERE clauses give the required result?
- A. WHERE INITCAP (SUBSTR (cust_name, INSTR(cust_name, '')+1)) LIKE 'Mc%'
- B. WHERE UPPER (SUBSTR (cust_name, INSTR(cust_name, '')+1)) LIKE UPPER ('MC%')
- C. WHERE SUBSTR (cust_name, INSTR (cust_name, '') +1) LIKE 'Mc%'
- D. WHERE INITCAP (SUBSTR (cust_name, INSTR(cust_name, '')+1)) IN ('MC%', 'Mc%)
- E. WHERE SUBSTR (cust_name, INSTR (cust_name, '') +1) LIKE 'Mc%' OR 'MC%'
Answer: C
NEW QUESTION 107
You must write a query that prompts users for column names and conditions every time it is executed.
The user must be prompted only once for the table name.
Which statement achieves those objectives?
- A. SELECT &col1, &col2FROM &&tableWHERE &condition = &cond;
- B. SELECT &col1, &col2FROM &&tableWHERE &condition = &&cond
- C. SELECT &col1, &col2FROM "&table"WHERE &condition = &cond;
- D. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
Answer: A
NEW QUESTION 108
Examine the structure of the EMPLOYEES table. (Choose the best answer.)
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
- A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
- B. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;
- C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
- D. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
Answer: D
NEW QUESTION 109
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
- B. Only one SAVEPOINT may be issued in a transaction
- C. Rolling back to a SAVEPOINT can undo a DELETE statement
- D. A SAVEPOINT does not issue a COMMIT
- E. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
Answer: C,D
NEW QUESTION 110
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 the table structure, data, and the indexes.
- B. It recovers the table structure and data but not the related indexes.
- C. It is not possible to recover the table structure, data, or the related indexes.
- D. It recovers only the table structure.
Answer: C
Explanation:
Explanation
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm
NEW QUESTION 111
......
1z0-071 Exam Questions – Valid 1z0-071 Dumps Pdf: https://www.dumpsvalid.com/1z0-071-still-valid-exam.html
Verified 1z0-071 dumps Q&As - Pass Guarantee: https://drive.google.com/open?id=1BegvevMGW6r4IK27v__mdIVqJwvvo8eD