[2021-New] Oracle 1Z0-051 Dumps With Update Exam Questions (101-110)

1Z0-051 Dumps

1Z0-051 Exam Questions - Online Test

1Z0-051 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. - (Topic 1)

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;

B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;

C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;

D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;

Answer: A,B

Explanation:

Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C

Q2. - (Topic 2)

View the Exhibit and examine the structure of the CUSTOMERS table.

Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

A. listing of customers who do not have a credit limit and were born before 1980

B. finding the number of customers, in each city, whose marital status is 'married'

C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'

E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

Answer: D,E

Explanation:

Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another. Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements: select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ;

Alternatively, this example uses one statement with a subquery:

select last_name from employees where salary < (select avg(salary)from employees);

In this example, the subquery is used to substitute a value into the WHERE clause of the

parent query: it is returning a single value, used for comparison with the rows retrieved by

the parent query.

The subquery could return a set of rows. For example, you could use the following to find

all departments that do actually have one or more employees assigned to them:

select department_name from departments where department_id in

(select distinct(department_id) from employees);

Q3. - (Topic 1)

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),

Q4. - (Topic 1)

Evaluate this SQL statement:

SELECT e.emp_name, d.dept_name

FROM employees e

JOIN departments d

USING (department_id)

WHERE d.department_id NOT IN (10,40)

ORDER BY dept_name;

The statement fails when executed. Which change fixes the error?

A. remove the ORDER BY clause

B. remove the table alias prefix from the WHERE clause

C. remove the table alias from the SELECT clause

D. prefix the column in the USING clause with the table alias

E. prefix the column in the ORDER BY clause with the table alias

F. replace the condition

”d.department_id NOT IN (10,40)”

in the WHERE clause with

”d.department_id <> 10 AND d.department_id <> 40”

Answer: B

Q5. - (Topic 2)

In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the

CUST_FIRST_NAME 'ABIGAIL'.

Evaluate the following query:

What would be the outcome?

A. Abigail PA B. Abigail Pa

C. Abigail IS

D. an error message

Answer: B

Q6. - (Topic 1)

View the Exhibit for the structure of the STUDENT and FACULTY tables.

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:

Which statement is true regarding the outcome?

A. Only statement 1 executes successfully and gives the required result.

B. Only statement 2 executes successfully and gives the required result.

C. Both statements 1 and 2 execute successfully and give different results.

D. Both statements 1 and 2 execute successfully and give the same required result.

Answer: D

Q7. - (Topic 1)

See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following the CREATE TABLE command:

Exhibit:

Which statement is true regarding the above command?

A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match

B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table

C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition

D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

Answer: B

Explanation:

Creating a Table Using a Subquery

Create a table and insert rows by combining the CREATE

TABLE statement and the AS subquery option.

CREATE TABLE table

[(column, column...)]

AS subquery;

Match the number of specified columns to the number of subquery columns.

Define columns with column names and default values.

Guidelines

The table is created with the specified column names, and the rows retrieved by the

SELECT statement are inserted into the table.

The column definition can contain only the column name and default value.

If column specifications are given, the number of columns must equal the number of

columns in the subquery SELECT list.

If no column specifications are given, the column names of the table are the same as the

column names in the subquery.

The column data type definitions and the NOT NULL constraint are passed to the new

table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY

column will not pass the NOT NULL feature to the new column. Any other constraint rules

are not passed to the new table. However, you can add constraints in the column definition.

Q8. - (Topic 1)

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

A. GRANT select ON dept TO ALL_USERS;

B. GRANT select ON dept TO ALL;

C. GRANT QUERY ON dept TO ALL_USERS

D. GRANT select ON dept TO PUBLIC;

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

Q9. - (Topic 1)

Examine the statement:

Create synonym emp for hr.employees;

What happens when you issue the statement?

A. An error is generated.

B. You will have two identical tables in the HR schema with different names.

C. You create a table called employees in the HR schema based on you EMP table.

D. You create an alternative name for the employees table in the HR schema in your own schema.

Answer: D

Q10. - (Topic 2)

Which statement describes the ROWID data type?

A. Binary data up to 4 gigabytes.

B. Character data up to 4 gigabytes.

C. Raw binary data of variable length up to 2 gigabytes.

D. Binary data stored in an external file, up to 4 gigabytes.

E. A hexadecimal string representing the unique address of a row in its table.

Answer: E

Explanation:

The ROWID datatype stores information related to the disk location of table rows. They

also uniquely identify the rows in your table. The ROWID datatype is stored as a

hexadecimal string.

Incorrect Answers

A:It is not a binary data. The ROWID datatype is a hexadecimal string.

B:It is not a character data. The ROWID datatype is a hexadecimal string.

C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.

D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal

string.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216

Chapter 5: Creating Oracle Database Objects