[Free] 2018(Jan) Latesttests Dumpsleader Oracle 1z0-051 Dumps with VCE and PDF Download 1-10

Latesttests 2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.Latesttests.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 1 – (Topic 1)

The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Latesttests 2018 PDF and VCE

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

  1. listing the employees who earn the same amount of commission as employee 3

  2. finding the total commission earned by the employees in department 10

  3. finding the number of employees who earn a commission that is higher than the average commission of the company

  4. listing the departments whose average commission is more that 600

  5. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID

  6. listing the employees whose annual commission is more than 6000

Answer: A,C

Question No: 2 – (Topic 1)

Examine the structure of the INVOICE table: Exhibit:

Latesttests 2018 PDF and VCE

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

  1. SELECT inv_no,NVL2(inv_date,#39;Pending#39;,#39;Incomplete#39;) FROM invoice;

  2. SELECT inv_no,NVL2(inv_amt,inv_date,#39;Not Available#39;) FROM invoice;

  3. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;

  4. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,#39;Not Available#39;) FROM invoice;

Answer: A,C Explanation:

The NVL2 Function

The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not.

5-6 The NVL function\

If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG.

They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

Question No: 3 – (Topic 1)

Which statement is true regarding the default behavior of the ORDER BY clause?

  1. In a character sort, the values are case-sensitive

  2. NULL values are not considered at all by the sort operation

  3. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause

  4. Numeric values are displayed from the maximum to the minimum value if they have decimal positions

Answer: A Explanation:

Character Strings and Dates

Character strings and date values are enclosed with single quotation marks. Character values are case-sensitive and date values are format-sensitive.

The default date display format is DD-MON-RR.

Question No: 4 – (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#39;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?

  1. 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));

  2. 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));

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

  4. 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

Question No: 5 – (Topic 1)

Evaluate the following SQL statements: DELETE FROM sales;

There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?

  1. It removes all the rows as well as the structure of the table

  2. It removes all the rows in the table and deleted rows cannot be rolled back

  3. It removes all the rows in the table and deleted rows can be rolled back

  4. It would not remove the rows if the table has a primary key

Answer: C

Question No: 6 – (Topic 1)

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

  1. INSERT

  2. UPDATE

  3. SELECT

  4. DESCRIBE

  5. DELETE

  6. RENAME

Answer: D Explanation:

Describe is a valid iSQL*Plus/ SQL*Plus command.

INSERT, UPDATE amp; DELETE are SQL DML Statements. A SELECT is an ANSI Standard SQL Statement not an iSQL*Plus Statement.

RENAME is a DDL Statement.

Question No: 7 – (Topic 1)

Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

  1. Combining more than two columns or expressions into a single column in the output

  2. Displaying a date in a nondefault format

  3. Substituting a character string in a text expression with a specified string

  4. Finding the number of characters in an expression

Answer: B,C,D

Question No: 8 – (Topic 1)

Which three statements are true regarding sub queries? (Choose three.)

  1. Multiple columns or expressions can be compared between the main query and sub query

  2. Main query and sub query can get data from different tables

  3. Sub queries can contain GROUP BY and ORDER BY clauses

  4. Main query and sub query must get data from the same tables

  5. Sub queries can contain ORDER BY but not the GROUP BY clause

  6. Only one column or expression can be compared between the main query and subqeury

Answer: A,B,C

Question No: 9 – (Topic 1)

Which statement correctly describes SQL and /SQL*Plus?

  1. Both SQL and /SQL*plus allow manipulation of values in the database.

  2. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.

  3. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.

  4. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.

Answer: A

Question No: 10 – (Topic 1)

Which two statements are true regarding the USING clause in table joins?(Choose two.)

  1. It can be used to join a maximum of three tables.

  2. It can be used to restrict the number of columns used in a NATURAL join.

  3. It can be used to access data from tables through equijoins as well as nonequijoins.

  4. It can be used to join tables that have columns with the same name and compatible data types.

Answer: B,D Explanation:

NATURAL JOIN operation

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

All the common columns

Every column in the first (left) table that is not a common column Every column in the second (right) table that is not a common column

An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column.

If a common column is referenced without being qualified by a table name, the column

reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

Syntax

TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN {

TableViewOrFunctionExpression | ( TableExpression ) }

Examples

If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES SELECT * FROM COUNTRIES JOIN CITIES

USING (COUNTRY, COUNTRY_ISO_CODE)

100% Free Download!
Download Free Demo:1z0-051 Demo PDF
100% Pass Guaranteed!
Download 2018 Latesttests 1z0-051 Full Exam PDF and VCE

Latesttests ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2018 Latesttests IT Certification PDF and VCE