[Free] 2018(Jan) EnsurePass Pass4sure Oracle 1z0-051 Dumps with VCE and PDF 11-20

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 11 – (Topic 1)

Which CREATE TABLE statement is valid?

A.

CREATE TABLE ord_details

(ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY,

ord_date DATE NOT NULL);

B.

CREATE TABLE ord_details

(ord_no NUMBER(2) UNIQUE, NOT NULL,

item_no NUMBER(3),

ord_date DATE DEFAULT SYSDATE NOT NULL);

C.

CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3),

ord_date DATE DEFAULT NOT NULL,

CONSTRAINT ord_uq UNIQUE (ord_no),

CONSTRAINT ord_pk PRIMARY KEY (ord_no));

D.

CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3),

ord_date DATE DEFAULT SYSDATE NOT NULL,

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D Explanation:

PRIMARY KEY Constraint

A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.

Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

Question No: 12 – (Topic 1)

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30)

JOB_ID NUMBER\ SAL NUMBER

MGR_ID NUMBER References EMPLOYEE_ID column

DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of theDEPARTMENTS table

You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.

Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

  1. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.

  2. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.

  3. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.

  4. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

  5. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.

  6. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.

Answer: C,D

Explanation: the EMP_ID_SEQ sequence is not affected by modification to the EMPLOYEES table. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

Incorrect answer:

AEMP_ID_SEQ sequence can be use to populate JOB_ID

BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify. EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES table.

FEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEE_ID column.

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

Question No: 13 – (Topic 1)

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

Ensurepass 2018 PDF and VCE

You want to create a SALE_PROD view by executing the following SQL statements:

Ensurepass 2018 PDF and VCE

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

  1. The view will be created and you can perform DLM operations on the view

  2. The view will not be created because the join statements are not allowed for creating a view

  3. The view will not be created because the GROUP BY clause is not allowed for creating a view

  4. The view will be created but no DML operations will be allowed on the view

Answer: D Explanation:

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

Question No: 14 – (Topic 1)

Ensurepass 2018 PDF and VCE

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

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.

Ensurepass 2018 PDF and VCE

You issue the following SQL statements: Exhibit:

What would be the outcome?

  1. It generates an error because multiple conditions cannot be specified for the WHEN clause

  2. It executes successfully and gives the required result

  3. It generates an error because CASE cannot be used with group functions

  4. It generates an error because NULL cannot be specified as a return value

Answer: B Explanation: CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:

CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn ELSE else_expr]

END

Question No: 15 – (Topic 1)

Examine the description of the EMP_DETAILS table given below: Exhibit:

Ensurepass 2018 PDF and VCE

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

  1. An EMP_IMAGE column can be included in the GROUP BY clause

  2. You cannot add a new column to the table with LONG as the data type

  3. An EMP_IMAGE column cannot be included in the ORDER BY clause

  4. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Answer: B,C 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.

Guidelines

A LONG column is not copied when a table is created using a subquery.

A LONG column cannot be included in a GROUP BY or an ORDER BY clause. Only one LONG column can be used per table.

No constraints can be defined on a LONG column.

You might want to use a CLOB column rather than a LONG column.

Question No: 16 – (Topic 1)

You need to calculate the number of days from 1st Jan 2007 till date: Dates are stored in the default format of dd-mm-rr.

Which two SQL statements would give the required output? (Choose two.)

  1. SELECT SYSDATE – TO_DATE(#39;01/JANUARY/2007#39;) FROM DUAL;

  2. SELECT TO_DATE(SYSDATE,#39;DD/MONTH/YYYY#39;)-#39;01/JANUARY/2007#39; FROM DUAL;

  3. SELECT SYSDATE – TO_DATE(#39;01-JANUARY-2007#39;) FROM DUAL

  4. SELECT SYSDATE – #39;01-JAN-2007#39; FROM DUAL

  5. SELECT TO_CHAR(SYSDATE,#39;DD-MON-YYYY#39;)-#39;01-JAN-2007#39; FROM DUAL;

Answer: A,C

Question No: 17 – (Topic 1)

Evaluate the following SQL statements: Exhibit:

Ensurepass 2018 PDF and VCE

Exhibit:

Ensurepass 2018 PDF and VCE

The above command fails when executed. What could be the reason?

  1. The BETWEEN clause cannot be used for the CHECK constraint

  2. SYSDATE cannot be used with the CHECK constraint

  3. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY

  4. 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 gt; 0),

Question No: 18 – (Topic 1)

Examine the structure of the EMPLOYEES table:

Ensurepass 2018 PDF and VCE

Which INSERT statement is valid?

A.

INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01/01/01′);

B.

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01′);

C.

INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(’01/01/01′));

D.

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);

Answer: D

Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format

Question No: 19 – (Topic 1)

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

Exhibit:

Ensurepass 2018 PDF and VCE

Evaluate the following query: Exhibit:

Ensurepass 2018 PDF and VCE

The above query produces an error on execution. What is the reason for the error?

  1. An alias cannot be used in an expression

  2. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression

  3. The MIDPOINT 100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values

  4. The alias NAME should not be enclosed within double quotation marks

Answer: A

Question No: 20 – (Topic 1)

View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

Ensurepass 2018 PDF and VCE

The above query generates an error on execution.

Which clause in the above SQL statement causes the error?

Ensurepass 2018 PDF and VCE

  1. WHERE

  2. SELECT

  3. GROUP BY

  4. ORDER BY

Answer: C

100% Ensurepass Free Download!
Download Free Demo:1z0-051 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-051 Dumps

EnsurePass 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.