Questions for the 1Z0-071 were updated on : Sep 01 ,2024
Page 1 out of 26. Viewing questions 1-15 out of 318
Question 1
Choose the best answer. Examine the description of the EMPLOYEES table: Which query is valid?
A. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
A. SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;
B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
B. SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:
C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
C. SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;
D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
D. SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;
Answer:
A
User Votes:
A
50%
A 1 votes
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
Discussions
0/ 1000
Question 2
Which three are true about the CREATE TABLE command?
A. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
A. It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
B. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
B. The owner of the table should have space quota available on the tablespace where the table is defined.
C. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
C. It implicitly executes a commit.
D. It executes successfully.
D. It implicitly rolls back any pending transactions.
E. A user must have the CREATE ANY TABLE privilege to create tables.
F. The owner of the table must have the UNLIMITED TABLESPACE system privilege.
Answer:
A, B, C
User Votes:
A
50%
A
50%
B
50%
B 1 votes
50%
C
50%
C
50%
D 1 votes
50%
D
50%
E
50%
F
50%
Discussions
0/ 1000
Question 3
The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number. Which two queries execute successfully?
A. SELECT TO_DATE(‘2019-01-01’) FROM DUAL;
A. SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;
B. SELECT DATE ‘2019-01-01’ FROM DUAL;
B. SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;
C. SELECT ‘2019-01-01’ FROM DUAL;
C. SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;
D. SELECT TO_CHAR(‘2019-01-01’) FROM DUAL;
D. SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;
E. SELECT TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’) FROM DUAL;
E. SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;
Answer:
D, E
User Votes:
A
50%
A 1 votes
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
0/ 1000
Question 4
Choose two Examine the description of the PRODUCT DETALS table:
A. It produces an error because subqueries cannot be used with the CASE expression.
A. PRODUCT_ID can be assigned the PEIMARY KEY constraint.
B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
B. EXPIRY_DATE cannot be used in arithmetic expressions.
C. It shows COST_REMARK for all the promos in the table.
C. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it
D. It produces an error because the subquery gives an error.
D. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
F. PRODUCT_NAME cannot contain duplicate values.
Answer:
A, D
User Votes:
A
50%
A 1 votes
50%
B
50%
B
50%
C
50%
C
50%
D 1 votes
50%
D 1 votes
50%
E
50%
F
50%
Discussions
0/ 1000
Question 5
The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose COST_LAST_MANE values are Anderson and Ausson. Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?
A. Data selected by a view's defining query is deleted from its underlying tables when the view is dropped.
A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
B. Read only views cannot be dropped.
B. SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;
C. The creator of a view to be dropped must have the DROP ANY VIEW privilege.
C. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
D. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.
D. SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;
E. Views referencing a dropped view become invalid.
Answer:
A
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
Discussions
0/ 1000
Question 6
Which three statements are true about indexes and their administration in an Oracle database?
A. CEIL: can be used for positive and negative numbers
A. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
B. FLOOR: returns the smallest integer greater than or equal to a specified number
B. An index can be created as part of a CREATE TABLE statement.
C. TRUNC: can be used with NUMBER and DATE values
C. A DROP INDEX statement always prevents updates to the table during the drop operation
D. CONCAT: can be used to combine any number of values
D. A UNIQUE and non-unique index can be created on the same table column
E. MOD: returns the quotient of a division operation
E. A descending index is a type of function-based index
F. If a query filters on an indexed column then it will always be used during execution of the query
Answer:
B, C, E
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
Discussions
0/ 1000
Question 7
Which three are true about granting object privileges on tables, views, and sequences?
A. SELECT TO_DATE(SYSDATE, ‘FMDAY, DD MONTH, YYYY’) FROM DUAL;
A. UPDATE can be granted only on tables and views.
B. SELECT TO_CHAR(SYSDATE, ‘FMDD, DAY MONTH, YYYY’) FROM DUAL;
B. DELETE can be granted on tables, views, and sequences.
C. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDTH MONTH, YYYY’) FROM DUAL;
C. REFERENCES can be granted only on tables and views.
D. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DD MONTH, YYYY’) FROM DUAL;
D. INSERT can be granted on tables, views, and sequences.
E. SELECT can be granted only on tables and views.
F. ALTER can be granted only on tables and sequences.
Answer:
A, C, F
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
0/ 1000
Question 8
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?
A. The ORDER BY Clause can be used in a subquery.
A. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
B. A subquery can be used in the FROM clause of a SELECT statement.
B. SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
C. If a subquery returns NULL, the main query may still return rows.
C. SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
D. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
Answer:
A
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
Discussions
0/ 1000
Question 9
Which three statements are true about views in an Oracle database?
A. There is only one subquery in the outer query and the inner query returns one or more values
A. A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query
B. The inner query returns a single value to the outer query.
B. Rows inserted into a table using a view are retained in the table if the view is dropped
C. The inner query uses an aggregating function and returns one or more values.
C. Views can join tables only if they belong to the same schema.
D. The inner query returns one or more values and the outer query returns a single value.
D. Views have no segment.
E. Views have no object number.
F. A view can be created that refers to a non-existent table in its defining query.
Answer:
B, D, F
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
0/ 1000
Question 10
Which two statements are true about Oracle synonyms?
A. Option A
A. A synonym can have a synonym.
B. Option B
B. All private synonym names must be unique in the database.
C. Option C
C. Any user can create a PUBLIC synonym.
D. Option D
D. A synonym can be created on an object in a package.
E. Option E
E. A synonym has an object number.
Answer:
A, E
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
0/ 1000
Question 11
Which two statements are true about a self join?
A. The NOT IN operator is equivalent to IS NULL.
A. The join key column must have an index.
B. The
B. It can be a left outer join.
C. =ANY and =ALL operators have the same functionality.
C. It must be a full outer join.
D. The IN operator cannot be used in single-row subqueries.
D. It can be an inner join.
E. The NOT operator can be used with IN, ANY and ALL operators.
E. It must be an equijoin.
Answer:
B, D
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
0/ 1000
Question 12
Which three statements are true about dropping and unused columns in an Oracle database?
A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
B. A DROP COLUMN command can be rolled back.
C. Rows are displayed in the order that they are stored in the table only for the first three rows.
C. An UNUSED column's space is remained automatically when the block containing that column is next queried.
D. Rows are displayed sorted in ascending order of the values in the third column in the table.
D. An UNUSED column's space is remained automatically when the row containing that column is next queried.
E. Partition key columns cannot be dropped.
F. A column that is set to NNUSED still counts towards the limit of 1000 columns per table.
Answer:
A, E, F
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
0/ 1000
Question 13
Examine this query: SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?
A. The statement would execute successfully to produce the required output.
A. 16
B. The statement would not execute because inline views and outer joins cannot be used together.
B. 160
C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
C. 150
D. The statement would not execute because the GROUP BY clause cannot be used in the inline.
D. 200
E. 100
Answer:
D
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
Discussions
0/ 1000
Question 14
Examine this SQL statement: Which two are true?
A. No updates can be made to the data in the DEPARTMENT_ID column.
A. The subquery is executed before the UPDATE statement is executed.
B. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
B. All existing rows in the ORDERS table are updated.
C. A query can be display data from the DEPARTMENT_ID column.
C. The subquery is executed for every updated row in the ORDERS table.
D. The DEPARTMENT_ID column is set to null for all rows in the table.
D. The UPDATE statement executes successfully even if the subquery selects multiple rows.
E. The DEPARTMENT_ID column can be recovered from the recycle bin.
E. The subquery is not a correlated subquery.
F. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.
Answer:
B, C
User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
Discussions
0/ 1000
Question 15
Examine the description of the PRODUCTS table: Which three queries use valid expressions?
A. It generates an error because = is not valid and should be replaced by the IN operator.
A. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
B. It executes successfully but does not give the correct output.
B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
C. It executes successfully and gives the correct output.
C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;
D. It generate an error because the subquery does not have a GROUP BY clause.
D. SPLECT product_id, expiry_date * 2 FROM products;
E. SELEGT product_id, unit_price, unit_price + surcharge FROM products;
F. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;