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

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: 91 – (Topic 1)

View the Exhibit and examine the description for the PRODUCTS and SALES table.

Latesttests 2018 PDF and VCE

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?

A.

DELETE

FROM products

WHERE prod_id = (SELECT prod_id FROM sales

WHERE time_id – 3*365 = SYSDATE );

B.

DELETE

FROM products

WHERE prod_id = (SELECT prod_id FROM sales

WHERE SYSDATE gt;= time_id – 3*365 );

C.

DELETE

FROM products

WHERE prod_id IN (SELECT prod_id FROM sales

WHERE SYSDATE – 3*365 gt;= time_id);

D.

DELETE

FROM products

WHERE prod_id IN (SELECT prod_id FROM sales

WHERE time_id gt;= SYSDATE – 3*365 );

Answer: C

Question No: 92 – (Topic 1)

A SELECT statement can be used to perform these three functions:

->Choose rows from a table.

->Choose columns from a table

->Bring together data that is stored in different tables by creating a link between them.

Which set of keywords describes these capabilities?

  1. difference, projection, join

  2. selection, projection, join

  3. selection, intersection, join

  4. intersection, projection, join

  5. difference, projection, product

Answer: B

Explanation: Explanation: choose rows from a table is SELECTION, Choose column from a table is PROJECTION

Bring together data in different table by creating a link between them is JOIN.

Incorrect answer:

Aanswer should have SELECTION, PROJECTION and JOIN. Canswer should have SELECTION, PROJECTION and JOIN. Danswer should have SELECTION, PROJECTION and JOIN. Eanswer should have SELECTION, PROJECTION and JOIN.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6

Question No: 93 – (Topic 1)

Latesttests 2018 PDF and VCE

Evaluate the following SQL statements: Exhibit:

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.

Exhibit:

Latesttests 2018 PDF and VCE

Which two statements are true regarding the above view? (Choose two.)

  1. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table

  2. It allows you to delete details of the existing sales staff from the EMPLOYEES table

  3. It allows you to insert rows into the EMPLOYEES table

  4. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

Answer: B,D

Question No: 94 – (Topic 1)

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

  1. The BLOB data type column is used to store binary data in an operating system file

  2. The minimum column width that can be specified for a VARCHAR2 data type column is one

  3. A TIMESTAMP data type column stores only time values with fractional seconds

  4. The value for a CHAR data type column is blank-padded to the maximum defined column width

  5. Only One LONG column can be used per table

Answer: B,D,E Explanation:

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table.

DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column.

If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size)

Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,000.)

BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.

BFILE A locator pointing to a file stored on the operating system of the database server. The size of the files is limited to 4GB.

TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified.

Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.

Question No: 95 – (Topic 1)

Latesttests 2018 PDF and VCE

See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit:

Which SQL statements are valid? (Choose all that apply.)

  1. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) quot;Discountquot;

    FROM promotions;

  2. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, #39;G1#39;, promo_cost *.25, NULL),

    NULL) quot;Catcostquot; FROM promotions;

  3. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, #39;N/A#39;) quot;Catcostquot;

    FROM promotions;

  4. SELECT promo_id, DECODE(promo_cost, gt;10000, #39;High#39;,

lt;10000, #39;Low#39;) quot;Rangequot; FROM promotions;

Answer: A,B Explanation:

The DECODE Function

Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1,

[comp2,iftrue2…[ compN,iftrueN]], [iffalse]).

Question No: 96 – (Topic 1)

The STUDENT_GRADES table has these columns:

STUDENT_IDNUMBER(12) SEMESTER_ENDDATE GPANUMBER(4,3)

The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.

Which statement accomplish this?

A.

SELECT student_id, semester_end, gpa FROM student_grades

ORDER BY semester_end DESC, gpa DESC;

B.

SELECT student_id, semester_end, gpa FROM student_grades

ORDER BY semester_end, gpa ASC

C.

SELECT student_id, semester_end, gpa FROM student_grades

ORDER BY gpa DESC, semester_end ASC;

D.

SELECT student_id, semester_end, gpa FROM student_grades

ORDER BY gpa DESC, semester_end DESC;

E.

SELECT student_id, semester_end, gpa FROM student_grades

ORDER BY gpa DESC, semester_end ASC;

F.

SELECT student_id,semester_end,gpa FROM student_grades

ORDER BY semester_end,gpa DESC

Answer: F

Question No: 97 – (Topic 1)

Which two statements are true about constraints? (Choose two.)

  1. The UNIQUE constraint does not permit a null value for the column.

  2. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  3. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.

  4. The NOT NULL constraint ensures that null values are not permitted for the column.

Answer: B,D Explanation:

B: A unique constraint can contain null values because null values cannot be compared to anything.

D: The NOT NULL constraint ensure that null value are not permitted for the column

Incorrect answer: Astatement is not true Cstatement is not true

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

Question No: 98 – (Topic 1)

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:

Latesttests 2018 PDF and VCE

What would be the outcome of the above statement?

Latesttests 2018 PDF and VCE

  1. It executes successfully.

  2. It produces an error because the condition on CUST_LAST_NAME is invalid.

  3. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.

  4. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.

Answer: A

Question No: 99 – (Topic 1)

View the Exhibit and examine the description for the CUSTOMERS table.

Latesttests 2018 PDF and VCE

You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where

CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

A.

UPDATE customers

SET cust_credit_limit = NULL

WHERE CUST_INCOME_LEVEL = NULL;

B.

UPDATE customers

SET cust_credit_limit = NULL WHERE cust_income_level IS NULL; C.

UPDATE customers

SET cust_credit_limit = TO_NUMBER(NULL) WHERE cust_income_level = TO_NUMBER(NULL); D.

UPDATE customers

SET cust_credit_limit = TO_NUMBER(#39; #39;,9999) WHERE cust_income_level IS NULL;

Answer: B

Question No: 100 – (Topic 1)

Which two statements about sub queries are true? (Choose two.)

  1. A sub query should retrieve only one row.

  2. A sub query can retrieve zero or more rows.

  3. A sub query can be used only in SQL query statements.

  4. Sub queries CANNOT be nested by more than two levels.

  5. A sub query CANNOT be used in an SQL query statement that uses group functions.

  6. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Answer: B,F

Explanation: Explanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.

Incorrect answer:

Asub query can retrieve zero or more rows Csub query is not SQL query statement Dsub query can be nested

Egroup function can be use with sub query

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