Updated Nov-2024 Test Engine to Practice Test for 1z1-071 Exam Questions and Answers!
Oracle Database SQL Certification Sample Questions and Practice Exam
NEW QUESTION # 136
You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
- A. All indexes on the table will remain but they are invalidated
- B. All data along with the table structure is deleted
- C. All viewsand synonyms will remain but they are invalidated
- D. The pending transaction in the session is committed
- E. All data in the table are deleted but the table structure will remain
Answer: B,C,D
NEW QUESTION # 137
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
- A. AIl conditions evaluated using DECODE can also be evaluated using CASE.
- B. Both CASE and DECODE are functions.
- C. CASE is a function and DECODE is not.
- D. DECODE is a function and CASE is not.
- E. Neither CASE nor DECODE is a function.
- F. All conditions evaluated using CASE can also be evaluated using DECODE.
Answer: D,F
NEW QUESTION # 138
Examine the data in the CUST_NAME column of the CUSTOMERS table:
CUST_NAME
---------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
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 SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%' OR 'MC%'
- B. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) IN ('MC%','Mc%')
- C. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE 'Mc%'
- D. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%'
- E. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE UPPER('MC%')
Answer: C,E
NEW QUESTION # 139
Examine the description of the ENPLYEES table:
Which two queries return all rows for employees whose salary is greater than the average salary in their department?
- A. SELECT.
FROM
SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal
FROM employees e
WHERE salary > avg_ sal; - B. SELECT"
FROM employees e1
WHERE salary >!
SELECT AVG (salary)
FROM employees e2
WHERE e1. Department _id = e2, department_ id - C. SELECT
FROM employees
WHERE salary > AVG (salary) OVER (PARTITION BY department _ id); - D. SELECT"
FROM employees
WHERE salary >
( SELECT AVG
(salary) FROM
employees
GROUP BY department _ id - E. SELECT "
FROM employees
WHERE salary > ANY
SELECT AVG (salary)
EROM employees
GROUP BY department_ id);
Answer: A,B
NEW QUESTION # 140
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.
ORDERS_MASTER
ORDER_TOTAL
ORDER_ID
1 1000
2 2000
3 3000
4
MONTHLY_ORDERS
ORDER_TOTAL
ORDER_ID
2 2500
3
Evaluate the following MERGEstatement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?
- A. The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.
- B. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
- C. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.
- D. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.
Answer: C
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION # 141
View the Exhibit and examine the structure of the PRODUCTStable
Which two tasks would require subqueries? (Choose two.)
- A. display the total number of products supplied by supplier 102and have a product status as 'OBSOLETE'
- B. display all products whose PROD_MIN_PRICEis more than the average PROD_LIST_PRICEof all products, and whose status is orderable
- C. display the number of products whose PROD_LIST_PRICEis more than the average PROD_LIST_PRICE
- D. display the minimum PROD_LIST_PRICEfor each product status
- E. display suppliers whose PROD_LIST_PRICEis less than 1000
Answer: B,C
NEW QUESTION # 142
Examine the description of the EMP_DETAILS table given below:
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?
- A. An EMP_IMAGE column cannot be included in the ORDER BY clause.
- B. An EMP_IMAGE column can be included in the GROUP BY clause.
- C. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
- D. You cannot add a new column to the table with LONG as the data type.
Answer: A,D
NEW QUESTION # 143
Examine the description of the PRODUCT_STATUS table:
The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row.
Which two queries will execute successfully?
- A. Option B
- B. Option C
- C. Option D
- D. Option F
- E. Option A
- F. Option E
Answer: A,F
NEW QUESTION # 144
View the exhibit for the structure of the STUDENTand FACULTYtables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?
- A. Only statement 1 executes successfully and gives the required result.
- B. Both statements 1 and 2 execute successfully and give different results.
- C. Both statements 1 and 2 execute successfully and give the same required result.
- D. Only statement 2 executes successfully and gives the required result.
Answer: A
NEW QUESTION # 145
Which three are true about the MERGE statement?
- A. It can update the same row of the target table multiple times.
- B. It can combine rows from multiple tables conditionally to insert into a single table.
- C. It can use subqueries to produce source rows.
- D. It can update, insert, or delete rows conditionally in multiple tables.
- E. It can merge rows only from tables.
- F. It can use views to produce source rows.
Answer: B,C,F
NEW QUESTION # 146
Examine the data in the ENPLOYEES table:
Which statement will compute the total annual compensation tor each employee
- A. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees
- B. SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees
- C. SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp FROM employees;
- D. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL (monthly_commission_pct, 0)) AS annual_comp FROM employees
Answer: D
Explanation:
The correct statement for computing the total annual compensation for each employee is option C. This is because the monthly commission is a percentage of the monthly salary (indicated by the column name MONTHLY_COMMISSION_PCT). To calculate the annual compensation, we need to calculate the annual salary (which is monthly_salary * 12) and add the total annual commission to it.
Here's the breakdown of the correct statement, option C:
* (monthly_salary * 12) computes the total salary for the year.
* NVL(monthly_commission_pct, 0) replaces NULL values in the monthly_commission_pct column with 0, ensuring that the commission is only added if it exists.
* (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) computes the annual commission by first determining the monthly commission (which is a percentage of the monthly salary), and then multiplying it by 12 to get the annual commission.
* Finally, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) adds the annual salary and the annual commission to get the total annual compensation.
The other options are incorrect:
* Option A is incorrect because it adds the monthly_commission_pct directly to the monthly_salary, which does not consider that monthly_commission_pct is a percentage.
* Option B is incorrect because it adds the commission percentage directly without first applying it to the monthly salary.
* Option D is incorrect because it does not handle the NULL values in the commission column, which would result in a NULL total annual compensation whenever the monthly_comission_pct is NULL.
References:
* Oracle Documentation on NVL function: NVL
* Oracle Documentation on Numeric Literals: Numeric Literals
NEW QUESTION # 147
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 displays all cities in descending order, within which the last names are further sorted in descending order.
- D. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
Answer: B
NEW QUESTION # 148
Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)
- A. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
- B. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
- C. DELETE <sequencename> would remove a sequence from the database.
- D. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
- E. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.
Answer: B,D
Explanation:
Explanation
References:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm
NEW QUESTION # 149
View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.) Which two statements are true regarding data type conversion in query expressions?
- A. inv_date = '15-february-2008' :uses implicit conversion
- B. inv_date > '01-02-2008' : uses implicit conversion
- C. inv_amt = '0255982' : requires explicit conversion
- D. inv_no BETWEEN '101' AND '110' : uses implicit conversion
- E. CONCAT(inv_amt, inv_date) : requires explicit conversion
Answer: A,D
NEW QUESTION # 150
Examine the description of the PROMTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used?
- A. SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;
- B. SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;
- C. SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:
- D. SELECT DISTINCT promo_category ||'has'|| promo_cost AS COSTS FROM promotions ORDER BY 1;
- E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D,E
NEW QUESTION # 151
Examine this statement:
Which two things must be changed for it to execute successfully?
- A. One of the LONG columns must be changed to a VARCHAR2 or CLOB.
- B. The primary key constraint on BMP_ID must have a name.
- C. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.
- D. The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.
- E. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.
Answer: A,C
Explanation:
The statement is trying to create a table with columns of different data types and constraints. Here's what needs to be corrected:
* C: In Oracle, the LONG data type is used for character data of variable length up to 2 Gigabytes, but it is deprecated, and you should use CLOB or VARCHAR2 instead. Furthermore, a table cannot have more than one LONG column.
* D: The NOT NULL constraint should be specified at the column level, not at the table level. The correct syntax for creating a table with a NOT NULL constraint is to define it inline with the column definition, like this:
ename VARCHAR2(15) CONSTRAINT ename_nn NOT NULL,
The other options are incorrect:
* A: The foreign key constraint syntax is correct; the word CONSTRAINT is followed by the constraint name and then the REFERENCES clause.
* B: The foreign key constraint can be defined at the column level.
* E: While it's a good practice to name constraints, it is not mandatory for the primary key constraint to have a name; Oracle will generate one if it's not provided.
References:
* Oracle Documentation on CREATE TABLE: SQL Language Reference - CREATE TABLE
* Oracle Documentation on Data Types: SQL Language Reference - Data Types
NEW QUESTION # 152
Examine the description of the CUSTOMERS table:
Which two statements will do an implicit conversion?
- A. SELECT FROM + customers WHERE (customer_id) = 0001;
- B. SELECT FROM + customers WHERE TO-CHAR (customer_id) = '0001';
- C. SELECT FROM + customers WHERE insert_date = '01-JAN-19';
- D. SELECT FROM + customers WHERE insert_date = DATE '2019-01-01';
- E. SELECT FROM + customers WHERE (customer_id) = '0001';
Answer: A,C
NEW QUESTION # 153
You need to display the date 11-oct-2007 in words as 'Eleventh of October, Two Thousand Seven'.
Which SQL statement would give the required result?
- A. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year')FROM DUAL;
- B. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year')FROM DUAL;
- C. SELECT TO_DATE(TO_CHAR('11-oct-2007'), 'fmDdspth "of" Month, Year'))FROM DUAL;
- D. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year')FROM DUAL;
Answer: A
NEW QUESTION # 154
View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
- A. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
- B. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') )
- C. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
- D. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
Answer: A,D
NEW QUESTION # 155
......
Certification dumps Oracle PL/SQL Developer Certified Associate 1z1-071 guides - 100% valid: https://exampdf.dumpsactual.com/1z1-071-actualtests-dumps.html
