Questions Ask for 1z0 051 dumps

Exam Code: 1z0 051 practice test (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0 051 dumps pdf Exam.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-051-exam-dumps.html

Q161. - (Topic 2)

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES

EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2(25)

LAST_NAME VARCHAR2(25)

HIRE_DATE DATE

NEW_EMPLOYEES

EMPLOYEE_ID NUMBER Primary Key

NAME VARCHAR2(60)

Which MERGE statement is valid?

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id,

e.first_name ||', '||e.last_name);

Answer: A

Explanation:

The correct statement for MERGE is MERGE INTO table_name Incorrect Answer: BWrong statement with the keyword EXISTS CWrong statement with the keyword EXISTS DWrong statement on the MERGE new_employees

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

Q162. - (Topic 1)

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)

SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p

USING(prod_id)

JOIN customers c

USING(cust_id)

WHERE c.cust_city='Tokyo'

SELECT c.cust_last_name, p.prod_name, s.quantity_sold

FROM products p JOIN sales s JOIN customers c

ON(p.prod_id=s.prod_id)

ON(s.cust_id=c.cust_id)

WHERE c.cust_city='Tokyo'

SELECT c.cust_last_name, p.prod_name, s.quantity_sold

FROM products p JOIN sales s

ON(p.prod_id=s.prod_id)

JOIN customers c

ON(s.cust_id=c.cust_id)

AND c.cust_city='Tokyo'

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM

products p JOIN sales s

USING(prod_id)

JOIN customers c

USING(cust_id)

WHERE c.cust_city='Tokyo'

Answer: A,C

Q163. - (Topic 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: A,D

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.

Q164. - (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

Q165. - (Topic 1)

You need to design a student registration database that contains several tables storing academic information.

The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.

You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));

B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));

D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Answer: D

Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);

Incorrect Answer: Ainvalid syntax Binvalid syntax Cinvalid syntax

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

Q166. - (Topic 2)

A data manipulation language statement _____.

A. completes a transaction on a table

B. modifies the structure and data in a table

C. modifies the data but not the structure of a table

D. modifies the structure but not the data of a table

Answer: C

Explanation:

modifies the data but not the structure of a table

Incorrect Answer:

ADML does not complete a transaction

BDDL modifies the structure and data in the table

DDML does not modified table structure.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-3

Q167. - (Topic 2)

What is true about sequences?

A. Once created, a sequence belongs to a specific schema.

B. Once created, a sequence is linked to a specific table.

C. Once created, a sequence is automatically available to all users.

D. Only the DBA can control which sequence is used by a certain table.

E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.

Answer: A

Q168. - (Topic 2)

What is true of using group functions on columns that contain NULL values?

A. Group functions on columns ignore NULL values.

B. Group functions on columns returning dates include NULL values.

C. Group functions on columns returning numbers include NULL values.

D. Group functions on columns cannot be accurately used on columns that contain NULL values.

E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.

Answer: A

Explanation: group functions on column ignore NULL values

Incorrect Answer: Bgroup functions on column ignore NULL values Cgroup functions on column ignore NULL values DNVL function can be use for column with NULL values Eno such INC_NULLS keyword

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-12

Q169. - (Topic 2)

View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.

Evaluate the following SQL command:

SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;

Which two statements are true regarding the outcome of the above query? (Choose two.)

A. It locks all the rows that satisfy the condition in the statement.

B. It locks only the columns that satisfy the condition in both the tables.

C. The locks are released only when a COMMIT or ROLLBACK is issued.

D. The locks are released after a DML statement is executed on the locked rows.

Answer: A,C

Explanation:

FOR UPDATE Clause in a SELECT Statement

Locks the rows in the EMPLOYEES table where job_id is SA_REP.

Lock is released only when you issue a ROLLBACK or a COMMIT.

If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;

Q170. - (Topic 2)

Which two statements are true about WHERE and HAVING clauses? (Choose two)

A. A WHERE clause can be used to restrict both rows and groups.

B. A WHERE clause can be used to restrict rows only.

C. A HAVING clause can be used to restrict both rows and groups.

D. A HAVING clause can be used to restrict groups only.

E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.

F. A HAVING clause CANNOT be used in sub queries.

Answer: B,D Explanation:

B: WHERE clause cannot be use to restrict groups

WHERE clause cannot be use when there is group functions.

D: A HAVING clause can only e used to restrict GROUPS.

Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause

rows are grouped

the group function is applied to the group

the group that match the criteria in the Having clause are displayed.

Incorrect Answers :

A. Where clause cannot be use to restrict groups

C. A HAVING clause can only e used to restrict GROUPS.

E. WHERE clause cannot be use when there is group function, instead HAVING is to be use.

F. There is no constraint to use HAVING clause in a sub queries.

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20

To see all sample questions go to: 1Z0-051 test prep

Related 1Z0-051 posts: