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

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

You issue the following command to drop the PRODUCTS table: SQLgt;DROP TABLE products;

What is the implication of this command? (Choose all that apply.)

  1. All data in the table are deleted but the table structure will remain

  2. All data along with the table structure is deleted

  3. All views and synonyms will remain but they are invalidated

  4. The pending transaction in the session is committed

  5. All indexes on the table will remain but they are invalidated

Answer: B,C,D

Question No: 32 – (Topic 1)

View the Exhibit and examine the structure of the PROMOTIONS table.

Ensurepass 2018 PDF and VCE

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the #39;INTERNET#39; category.

Which query would give you the required output?

A.

SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date gt; ALL (SELECT MAX(promo_begin_date) FROM promotions

)AND

promo_category = #39;INTERNET#39;;

B.

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions

WHERE promo_category=#39;INTERNET#39;);

C.

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date gt; ALL (SELECT promo_begin_date FROM promotions

WHERE promo_category = #39;INTERNET#39;);

D.

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date gt; ANY (SELECT promo_begin_date FROM promotions

WHERE promo_category = #39;INTERNET#39;);

Answer: C

Question No: 33 – (Topic 1)

User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.

Which SQL statement can Mary use to accomplish that task?

  1. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;

  2. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);

  3. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;

  4. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu

    FOR EACH USER;

  5. CREATE SYNONYM EDL_VU FOR EACH USER

    ON emp_dept_loc_vu;

  6. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu

FOR ALL USERS;

Answer: C Explanation:

The general syntax to create a synonym is:

CREATE [PUBLIC] SYNONYM synonym FOR object;

Question No: 34 – (Topic 1)

Which SQL statement displays the date March 19, 2001 in a format that appears as “Nineteenth of March 2001 12:00:00 AM”?

  1. SELECT

    TO_CHAR(TO_DATE(#39;19-Mar-2001′, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYY fmHH:MI:SS AM’) NEW_DATE

    FROM dual;

  2. SELECT

    TO_CHAR(TO_DATE(’19-Mar-2001′, ‘DD-Mon-YYYY’), ‘Ddspth “of” Month YYYY fmHH:MI:SS AM’) NEW_DATE

    FROM dual;

  3. SELECT

    TO_CHAR(TO_DATE(’19-Mar-2001′, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYY HH:MI:SS AM’) NEW_DATE FROM dual;

  4. SELECT

TO_CHAR(TO_DATE(’19-Mar-2001′, ‘DD-Mon-YYYY), ‘fmDdspth “of” Month YYYYfmtHH:HI:SS AM#39;) NEW_DATE FROM dual;

Answer: A

Question No: 35 – (Topic 1)

Which is the valid CREATE [TABLE statement?

  1. CREATE TABLE emp9$# (emp_no NUMBER(4));

  2. CREATE TABLE 9emp$# (emp_no NUMBER(4));

  3. CREATE TABLE emp*123 (emp_no NUMBER(4));

  4. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);

Answer: A Explanation:

Schema Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

A quoted identifier begins and ends with double quotation marks (quot;). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

A nonquoted identifier is not surrounded by any punctuation.

The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:

Names must be from 1 to 30 bytes long with these exceptions: Names of databases are limited to 8 bytes.

Names of database links can be as long as 128 bytes.

If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long.

Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:

quot;schemaquot;.quot;tablequot;.quot;columnquot;

Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended.

Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.

The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results.

In particular, do not use words beginning with SYS_ as schema object names, and do not

use the names of SQL built-in functions for the names of schema objects or user-defined functions.

You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.

Nonquoted identifiers must begin with an alphabetic character (ANSWER B – begins with 9) from your database character set. Quoted identifiers can begin with any character.

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and quot;atquot; signs (@). Oracle strongly discourages you from using

$ and # in nonquoted identifiers.

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

Within a namespace, no two objects can have the same name.

Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

employees quot;employeesquot; quot;Employeesquot; quot;EMPLOYEESquot;

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

employees EMPLOYEES quot;EMPLOYEESquot;

Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

Question No: 36 – (Topic 1)

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

A.

GRANT select ON dept TO ALL_USERS;

B.

GRANT select ON dept TO ALL;

C.

GRANT QUERY ON dept TO ALL_USERS

D.

GRANT select ON dept TO PUBLIC;

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Incorrect answer:

Atable to view all constraints definition and names Bshow all object name belong to user

Cdoes not display column associated Eno such view

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

Question No: 37 – (Topic 1)

Evaluate the following SQL statement:

SQLgt; SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN

(select cust_credit_limit FROM customers

WHERE cust_city=#39;Singapore#39;):

Which statement is true regarding the above query if one of the values generated by the sub query is NULL?

  1. It produces an error.

  2. It executes but returns no rows.

  3. It generates output for NULL as well as the other values produced by the sub query.

  4. It ignores the NULL value and generates output for the other values produced by the sub query.

Answer: C

Question No: 38 – (Topic 1)

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:

Ensurepass 2018 PDF and VCE

Ensurepass 2018 PDF and VCE

Ensurepass 2018 PDF and VCE

You issue the following query:

Ensurepass 2018 PDF and VCE

Which statement is true regarding the outcome of this query?

  1. It produces an error because the NATURAL join can be used only with two tables

  2. It produces an error because a column used in the NATURAL join cannot have a qualifier

  3. It produces an error because all columns used in the NATURAL join should have a qualifier

  4. It executes successfully

Answer: B Explanation:

Creating Joins with the USING Clause

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.

The Natural JOIN USING Clause

The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column

FROM table1

JOIN table2 USING (join_column1, join_column2…);

While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING

syntax does not.

An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

Question No: 39 – (Topic 1)

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

Ensurepass 2018 PDF and VCE

There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

A.

INSERT INTO orders

VALUES (1,#39;10-mar-2007#39;, #39;direct#39;,

(SELECT customer_id

FROM customers

WHERE cust_last_name=#39;Roberts#39; AND credit_limit=600), 1000);

B.

INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id

FROM customers

WHERE cust_last_name=#39;Roberts#39; AND credit_limit=600),order_total)

VALUES(1,#39;10-mar-2007#39;, #39;direct#39;, amp;amp;customer_id, 1000);

C.

INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total

FROM orders o, customers c

WHERE o.customer_id = c.customer_id

AND c.cust_last_name=#39;Roberts#39; ANDc.credit_limit=600 ) VALUES (1,#39;10-mar-2007#39;, #39;direct#39;,(SELECT customer_id FROM customers

WHERE cust_last_name=#39;Roberts#39; AND credit_limit=600), 1000);

D.

INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id

FROM customers

WHERE cust_last_name=#39;Roberts#39; AND credit_limit=600),order_total)

VALUES(1,#39;10-mar-2007#39;, #39;direct#39;, amp;customer_id, 1000);

Answer: A

Question No: 40 – (Topic 1)

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

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

  2. DELETE lt;sequencenamegt; would remove a sequence from the database

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

  4. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

  5. 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;

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.