Proper study guides for Far out Oracle Oracle Database: SQL Fundamentals I certified begins with Oracle 1z0 051 pdf preparation products which designed to deliver the Guaranteed 1z0 051 latest dumps free download pdf questions by making you pass the oracle 1z0 051 test at your first time. Try the free 1z0 051 dumps demo right now.

Q21. - (Topic 1) 

Which two statements are true about sequences created in a single instance database? (Choose two.) 

A. CURRVAL is used to refer to the last sequence number that has been generated 

B. DELETE <sequencename> would remove a sequence from the database 

C. The numbers generated by a sequence can be used only for one table 

D. When the MAXVALUE limit for a 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: A,D 

Explanation: 

Gaps in the Sequence 

Although sequence generators issue sequential numbers without gaps, this action occurs 

independent of a commit or rollback. Therefore, if you roll back a statement containing a 

sequence, the number is lost. 

Another event that can cause gaps in the sequence is a system crash. If the sequence 

caches values in memory, those values are lost if the system crashes. 

Because sequences are not tied directly to tables, the same sequence can be used for 

multiple tables. 

However, if you do so, each table can contain gaps in the sequential numbers. 

Modifying a Sequence 

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement: 

DROP SEQUENCE dept_deptid_seq; 


Q22. - (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:

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


Q23. - (Topic 1) 

Which two statements are true regarding the COUNT function? (Choose two.) 

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns 

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column 

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column 

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause 

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types 

Answer: A,C 

Explanation: 

Using the COUNT Function 

The COUNT function has three formats: 

COUNT(*) 

COUNT(expr) 

COUNT(DISTINCT expr) 

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT 

statement, including duplicate rows and rows containing null values in any of the columns. 

If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of 

rows that satisfy the condition in the WHERE clause. 

In contrast, 

COUNT(expr) returns the number of non-null values that are in the column identified by 

expr. 

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the 

column identified by expr. 


Q24. - (Topic 2) 

View the Exhibit and examine the data in the EMPLOYEES table: 

You want to display all the employee names and their corresponding manager names. 

Evaluate the following query: 

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" 

FROM employees e ______________ employees m 

ON e.manager_id = m.employee_id; 

Which JOIN option can be used in the blank in the above query to get the required output? 

Exhibit: 

A. only inner JOIN 

B. only FULL OUTER JOIN 

C. only LEFT OUTER JOIN 

D. only RIGHT OUTER JOIN 

Answer:


Q25. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

Which is the correct output of the above query? 

A. +00-300, +54-02,+00 11:12:10.123457 

B. +00-300,+00-650,+00 11:12:10.123457 

C. +25-00, +54-02, +00 11:12:10.123457 

D. +25-00,+00-650,+00 11:12:10.123457 

Answer:


Q26. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. 

Exhibit: 

and examine the structure of CUSTOMRS AND SALES tables: 

Evaluate the following SQL statement: 

Exhibit: 

Which statement is true regarding the execution of the above UPDATE statement? 

A. It would not execute because the SELECT statement cannot be used in place of the table name 

B. It would execute and restrict modifications to only the column specified in the SELECT statement 

C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement 

D. It would not execute because two tables cannot be used in a single UPDATE statement 

Answer:


Q27. - (Topic 1) 

Here is the structure and data of the CUST_TRANS table: Exhibit: 

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. 

Which three SQL statements would execute successfully? (Choose three.) 

A. SELECT transdate + '10' FROM cust_trans; 

B. SELECT * FROM cust_trans WHERE transdate = '01-01-07' 

C. SELECT transamt FROM cust_trans WHERE custno > '11' 

D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07' 

E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000; 

Answer: A,C,D 


Q28. - (Topic 2) 

Examine the structure of the EMP_DEPT_VU view: 

Which SQL statement produces an error? 

A. SELECT * 

FROM emp_dept_vu; 

B. SELECT department_id, SUM(salary) 

FROM emp_dept_vu 

GROUP BY department_id; 

C. SELECT department_id, job_id, AVG(salary) 

FROM emp_dept_vu 

GROUP BY department_id, job_id; 

D. SELECT job_id, SUM(salary) 

FROM emp_dept_vu 

WHERE department_id IN (10,20) 

GROUP BY job_id 

HAVING SUM(salary) > 20000; 

E. None of the statements produce an error; all are valid. 

Answer:

Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error 


Q29. - (Topic 1) 

What is true regarding sub queries? 

A. The inner query always sorts the results of the outer query 

B. The outer query always sorts the results of the inner query 

C. The outer query must return a value to the outer query 

D. The inner query returns a value to the outer query 

E. The inner query must always return a value or the outer query will give an error 

Answer:

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result 


Q30. - (Topic 1) 

You need to perform these tasks: 

. Create and assign a MANAGER role to Blake and Clark . Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark 

Which set of SQL statements achieves the desired results? 

A. CREATE ROLE manager; 

GRANT create table, create view 

TO manager; 

GRANT manager TO BLAKE,CLARK; 

B. CREATE ROLE manager; 

GRANT create table, create voew 

TO manager; 

GRANT manager ROLE TO BLAKE,CLARK; 

C. GRANT manager ROLE TO BLAKE,CLARK; 

GRANT create table, create voew 

TO BLAKE CLARK; 

***MISSING*** 

Answer:

Explanation: Result of commands: