Accurate 1Z0-061 Exam Questions and Answers 2021

NEW QUESTION 1
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

• A. The ON clause can be used to join tables on columns that have different names but compatible data types
• B. A maximum of one pair of columns can be joined between two tables using the ON clause
• C. Both USING and ON clause can be used for equijoins and nonequijoins
• D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

Answer: AD

Explanation: Creating Joins with the USING Clause
If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.
Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive
Using Table Aliases with the USING clause
When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias
the location_id column in the WHERE clause because the column is used in the USING clause.
The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement.
Creating Joins with the ON Clause
The join condition for the natural join is basically an equijoin of all columns with the same name.
Use the ON clause to specify arbitrary conditions or specify columns to join. – ANSWER C The join condition is separated from other search conditions. ANSWER D
The ON clause makes code easy to understand.

NEW QUESTION 2
Evaluate the following SQL statements: Exhibit:
Exhibit:

The above command fails when executed. What could be the reason?

• A. The BETWEEN clause cannot be used for the CHECK constraint
• B. SYSDATE cannot be used with the CHECK constraint
• C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
• D. The CHECK constraint cannot be placed on columns having the DATE data type

Answer: B

Explanation: CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees (...
salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary > 0),

NEW QUESTION 3
Which statement is true regarding synonyms?

• A. Synonyms can be created only for a table
• B. Synonyms are used to reference only those tables that are owned by another user
• C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
• D. A public synonym and a private synonym can exist with the same name for the same table

Answer: D

NEW QUESTION 4
Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

• A. SELECT . student_ id, marks, ROWNUM "Rank"FROM . studentsWHERE . ROWNUM<= 10AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-99AND . course_id = 'INT_SQL'ORDER BY . marks DESC;
• B. SELECT . student_id, marks, ROWID "Rank". FROM . student
• C. WHERE . ROWID <=10. AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'. AND . course_id = 'INT_SQL'. ORDER BY . marks;
• D. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, mark
• E. FROM . student
• F. WHERE . ROWNUM <= 10. AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-. 99'. AND . course_id = 'INT_SQL'. ORDER BY . marks DESC);
• G. SELECT . student_id, marks, ROWNUM "Rank”. FROM . (SELECT student_id, mark
• H. FROM . student
• I. WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’. AND course_id = ‘INT_SQL’. ORDER BY marks DESC). WHERE . ROWNUM <= 10 ;.
• J. SELEC
• K. student id, marks, ROWNUM “Rank”. FRO
• L. (SELECT student_id, mark
• M. FROM student
• N. ORDER BY marks). WHER
• O. ROWNUM <= 10. AN
• P. finish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’. AN
• Q. course_id = ‘INT_SQL’;

Answer: D

NEW QUESTION 5
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

• A. ALTER TABLE students ADD PRIMARY KEY student_id;
• B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
• C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
• D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
• E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Answer: D

Explanation: ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn);
Incorrect
A- wrong syntax
B- wrong syntax
C- wrong syntax
E- no such MODIFY keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17

NEW QUESTION 6
You want to create a table employees in which the values of columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required table?

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F

Answer: AF

NEW QUESTION 7
Which are /SQL*Plus commands? (Choose all that apply.)

• A. INSERT
• B. UPDATE
• C. SELECT
• D. DESCRIBE
• E. DELETE
• F. RENAME

Answer: D

Explanation: Describe is a valid iSQL*Plus/ SQL*Plus command.
INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard SQL Statement not an iSQL*Plus Statement.
RENAME is a DDL Statement.

NEW QUESTION 8
Evaluate the following SQL commands:

The command to create a table fails. Identify the two reasons for the SQL statement failure?

• A. You cannot use SYSDATE in the condition of a check constraint.
• B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
• C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
• D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: AC

Explanation: CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees (...
Salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary > 0),
Topic 2, Practice Questions Set 1

NEW QUESTION 9
SLS is a private synonym for the SH.SALES table. The user SH issues the following command: DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?

• A. Only the synonym would be dropped.
• B. The synonym would be dropped and the corresponding table would become invalid.
• C. The synonym would be dropped and the packages referring to the synonym would be dropped.
• D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

Answer: A

Explanation: A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

NEW QUESTION 10
Which statement correctly describes SQL and /SQL*Plus?

• A. Both SQL and /SQL*plus allow manipulation of values in the database.
• B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
• C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
• D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.

Answer: A

NEW QUESTION 11
Examine the structure of the EMPLOYEES table:

Which UPDATE statement is valid?

• A. UPDATE employeesSET first_name = ‘John’SET last_name = ‘Smith’WHERE employee_id = 180;
• B. UPDATE employeesSET first_name = ‘John’,SET last_name = ‘Smoth’WHERE employee_id = 180;
• C. UPDATE employeeSET first_name = ‘John’AND last_name = ‘Smith’WHERE employee_id = 180;
• D. UPDATE employeeSET first_name = ‘John’, last_name = ‘Smith’WHERE employee_id= 180;

Answer: D

NEW QUESTION 12
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

Explanation: So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string
Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks.
Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character
The syntax of the alternative quote operator is as follows:
q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter. Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager" FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multi byte, or any of the following character pairs: [ ], { }, ( ), or < >.
In the example shown, the string contains a single quotation mark, which is normally
interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

NEW QUESTION 13
Study this view creation statement:
create view dept30 as select department_id,employee_id,last_name from employees where department_id=30 with check option;
What might make the following statement fail? (Choose the best answer.) update dept30 set department_id=10 where employee_id=114;

• A. Unless specified otherwise, views will be created as WITH READ ONLY.
• B. The view is too complex to allow DML operations.
• C. The WITH CHECK OPTION will reject any statement that changes the DEPARTMENT_ID.
• D. The statement will succeed.

Answer: C

Explanation: INCORRECT: A, B, and D are incorrect. A is incorrect because views are, by default, created read-write. B is incorrect because the view is a simple view. D is incorrect because the statement cannot succeed because the CHECK option will reject it.

NEW QUESTION 14
You need to produce a report where each customer's credit limit has been incremented by
$1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

Explanation: A column alias:
- Renames a column heading
- Is useful with calculations
- Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
- Requires double quotation marks if it contains spaces or special characters, or if it is case
sensitive.

NEW QUESTION 15
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit

Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)

• A. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 1 DESC;
• B. SELECT promo_name, promo_begin_date "START DATE"FROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY "START DATE" DESC;
• C. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 2 DESC;
• D. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY promo_name DESC;

Answer: BC

NEW QUESTION 16
Examine the structure of the EMPLOYEES table:

Which INSERT statement is valid?

• A. INSERT INTO employees (employee_id, first_name, last_name, hire_date)VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’);
• B. INSERT INTO employees(employee_id, first_name, last_name, hire_date)VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’);
• C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date)VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’));
• D. INSERT INTO employees(employee_id, first_name, last_name, hire_date)VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);

Answer: D

Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format