Oracle PL/SQL Developer Certified Associate Certification 1z0-071 Sample Questions Reliable
Prepare for the Actual Oracle PL/SQL Developer Certified Associate 1z0-071 Exam Practice Materials Collection
Oracle 1z1-071 certification exam is designed for individuals who want to validate their skills and knowledge in SQL database administration with the Oracle database. 1z0-071 exam is a great way to demonstrate your proficiency in writing SQL queries, creating database objects, and retrieving data from the Oracle database. Passing 1z0-071 exam will earn you the Oracle Database SQL Certified Associate credential, which is a valuable certification in the IT industry.
NEW QUESTION # 54
Which three actions can you perform on an existing table containing data? (Choose three.)
- A. Change a DATE column containing data to a NUMBER data type
- B. Change the default value of a column
- C. Add a new NOT NULL column with a DEFAULT value
- D. Define a default value that is automatically inserted into a column containing nulls
- E. Add a new column as the table's first column
- F. Increase the width of a numeric column
Answer: B,E
NEW QUESTION # 55
You issue the following command to drop the PRODUCTStable:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command? (Choose three.)
- A. A pending transaction in the session is committed.
- B. All data along with the table structure is deleted.
- C. All data in the table is deleted but the table structure remains.
- D. All indexes on the table remain but they are invalidated.
- E. All views and synonyms on the table remain but they are invalidated.
Answer: A,B,E
Explanation:
Explanation
NEW QUESTION # 56
Which three actions can you perform by using the ALTER TABLE command?
- A. Restrict all DML statements on a table.
- B. Enable or disable constraints on a table.
- C. Rename a table
- D. Drop pseudo columns from a table.
- E. Drop all columns simultaneously from a table.
- F. Lock a set of rows in a table CE Rename a table.
Answer: B,C,F
NEW QUESTION # 57
You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true?
- A. The employees table can be recovered using the rollback command.
- B. All uncommitted transactions are committed.
- C. The space used by the employees table is reclaimed immediately.
- D. The employees table is moved to the recycle bin
- E. Sequences used in the employees table become invalid.
- F. All indexes and constraints defined on the table being dropped are also dropped.
Answer: B,D,F
NEW QUESTION # 58
View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
- A. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
- B. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
- C. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;
- D. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
Answer: B
NEW QUESTION # 59
Which three statements are true about single-row functions? (Choose three.)
- A. They can accept only one argument
- B. They can be nested to any level
- C. The data type returned can be different from the data type of the argument
- D. The argument can be a column name, variable, literal or an expression
- E. They return a single result row per table
- F. They can be used only in the WHERE clause of a SELECT statement
Answer: C,D
NEW QUESTION # 60
Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS tables:
You want to generate a list of all department IDs that do not exist in the COURSE_DETAILStable.
You execute the SQL statement:
What is the outcome?
- A. It executes successfully and displays the required list.
- B. It executes successfully but displays an incorrect list.
- C. It fails because the ON clause condition is not valid.
- D. It fails because the join type used is incorrect.
Answer: A
Explanation:
Explanation
NEW QUESTION # 61
No-user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)?
- A. ROLLBACKwithout the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.
- B. ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.
- C. ROLLBACKwithout the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
- D. COMMITerases all the transaction's savepoints and releases its locks.
- E. ROLLBACKwithout the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
- F. COMMITends the transaction and makes all its changes permanent.
Answer: A,D,F
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/A58617_01/server.804/a58233/trans.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9021.htm
NEW QUESTION # 62
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? (Choose two.)
- A. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
- B. 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.
- C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
- D. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
- E. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
Answer: A,B
Explanation:
Explanation
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html
NEW QUESTION # 63
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.) SQL > CREATE TABLE transaction (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?
- A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
- B. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')
- C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
- D. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
Answer: C
NEW QUESTION # 64
View the Exhibit and examine the structure of CUSTOMERStable.
Evaluate the following query:
Which statement is true regarding the above query?
- A. It produces an error because conditions on the CUST_CREDIT_LIMITcolumn are not valid.
- B. It produces an error because the condition on the CUST_FIRST_NAMEcolumn is not valid.
- C. It executes successfully.
- D. It produces an error because the condition on the CUST_CITYcolumn is not valid.
Answer: C
NEW QUESTION # 65
Examine this SQL statement:
Which two are true?
- A. The UPDATE statement executes successfully even if the subquery selects multiple rows.
- B. The subquery is executed for every updated row in the ORDERS table.
- C. All existing rows in the ORDERS table are updated.
- D. The subquery is not a correlated subquery.
- E. The subquery is executed before the UPDATE statement is executed.
Answer: B,C
NEW QUESTION # 66
The user SCOTTwho is the owner of ORDERSand ORDER_ITEMStables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
- A. Separate GRANTstatements are required for ORDERSand ORDER_ITEMS tables.
- B. PUBLICshould be replaced with specific usernames.
- C. WITH GRANT OPTIONshould be added to the statement.
- D. ALLshould be replaced with a list of specific privileges.
Answer: A
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
NEW QUESTION # 67
Which two statements are true regarding the DELETEand TRUNCATEcommands? (Choose two.)
- A. DELETEand TRUNCATEcan be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETErule.
- B. DELETEcan be used to remove rows from only one table in one statement.
- C. DELETEcan be used to remove data from specific columns as well as complete rows.
- D. DELETEcan be used to remove rows from multiple tables in one statement.
- E. DELETEcan be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.
Answer: A,B
NEW QUESTION # 68
Which three statements are true about the DESCRIBE command? (Choose three.)
- A. It displays the NOT NULL constraint for any columns that have that constraint
- B. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
- C. It displays all constraints that are defined for each column
- D. It can be used from SQL Developer
- E. It can be used to display the structure of an existing view
- F. It can be used only from SQL*Plus
Answer: A,E,F
NEW QUESTION # 69
Examine the structure of the SALES table. (Choose two.)
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- B. It is created with no rows.
- C. It will not be created because of the invalid WHERE clause.
- D. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- E. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
Answer: B,E
NEW QUESTION # 70
Which two statements are true about sequences created in a single instance database? (Choose two.)
- A. DELETE <sequencename> would remove a sequence from the database.
- B. The numbers generated by a sequence can be used only for one table.
- C. CURRVAL is used to refer to the last sequence number that has been generated.
- D. When the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
- E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
Answer: C,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 # 71
Examine the structure of the PROMOTIONS table: (Choose the best answer.) Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?
- A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
- B. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
- C. SELECT DISTINCT promo_cost, promo_category FROM promotions
- D. SELECT promo_category, DISTINCT promo_cost FROM promotions
Answer: A
NEW QUESTION # 72
Evaluate the following ALTER TABLEstatement:
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?
- A. ROLLBACKcan be used to get back the ORDER_DATEcolumn in the ORDERStable.
- B. After executing the ALTER TABLEcommand, you can add a new column called ORDER_DATEto the ORDERStable.
- C. The ORDER_DATEcolumn must be empty for the ALTER TABLEcommand to execute successfully.
- D. The DESCRIBEcommand would still display the ORDER_DATEcolumn.
Answer: B
NEW QUESTION # 73
View the Exhibit and examine the description for the SALES and CHANNELS tables.
You issued this SQL statement:
Which statement is true regarding the result? (Choose the best answer.)
- A. The statement will fail because the VALUES clause is not required with the subquery.
- B. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
- C. The statement will execute and a new row will be inserted in the SALES table.
- D. The statement will fail because a subquery cannot be used in a VALUES clause.
Answer: C
NEW QUESTION # 74
Examine the description of the PROMOTIONS TABLE:
You want to display the unique is promotion costs in each promotion category.
Which two queries can be used?
- A. select DISTINCT promo_categoryIl 'has'||promol_cost as COSTS FROM promotions ORDER BY 1:
- B. SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:
- C. SELECT promo_cost, promo_category FROM promotions ORDER BY 1
- D. SELECT DISTINCT promo_cost ||'in'IIDISTINCT promo_category promotions ORDER BY1;
- E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: A,E
NEW QUESTION # 75
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:
What would be the outcome?
- A. It generates an error because NULL cannot be specified as a return value
- B. It generates an error because CASE cannot be used with group functions
- C. It executes successfully and gives the required result
- D. It generates an error because multiple conditions cannot be specified for the WHEN clause
Answer: C
Explanation:
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
NEW QUESTION # 76
View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME
CATEGORY_ID
SUPPLIER_ID
Inkjet C/8/HQ
12
102094
Inkjet C/4
12
102090
LaserPro 600/6/BW
12
102087
LaserPro 1200/8/BW
12
102099
Inkjet B/6
12
102096
Industrial 700/ID
12
102086
Industrial 600/DQ
12
102088
Compact 400/LQ
12
102087
Compact 400/DQ
12
102088
HD 12GB /R
13
102090
HD 10GB /I
13
102071
HD 12GB @7200 /SE
13
102057
HD 18.2GB @10000 /E
13
102078
HD 18.2GB @10000 /I
13
102050
HD 18GB /SE
13
102083
HD 6GB /I
13
102072
HD 8.2GB@5400
13
102093
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?
- A. It would execute and the output would display the desired result.
- B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
- C. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
- D. It would execute but the output would return no rows.
Answer: D
NEW QUESTION # 77
Evaluate the following SQL statements that are issued in the given order: CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
- A. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
- B. It would be automatically enabled and immediate.
- C. It would be automatically enabled and deferred.
- D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
Answer: B,C,D
NEW QUESTION # 78
......
Ace Oracle 1z0-071 Certification with Actual Questions Apr 06, 2024 Updated: https://www.updatedumps.com/Oracle/1z0-071-updated-exam-dumps.html
Oracle PL/SQL Developer Certified Associate Certified Official Practice Test 1z0-071: https://drive.google.com/open?id=1HZg1Di7sz_qnGFrNT9wdD7rxmQLD29B0