Oracle Database Application Development

1Z0-051 Real Questions, Free PDF Tests Online and Free Study Guides

Tens of thousands of competitors, pages of hard questions and unsatisfied exam preparation situations… Do not worried about all those annoying things! We, help you with your Oracle Database Application Development 1Z0-051 Oracle Database 11g : SQL Fundamentals I exam. We will assist you clear the 1Z0-051 exam with Oracle Database Application Development 1Z0-051 dumps. We 1Z0-051 exam dumps are the most comprehensive ones.

Visit our site to get more 1Z0-051 Q and As:https://www.pass1day.com/1Z0-051.html (292 QAs Dumps)
Question 1:

Evaluate the SQL statement: TRUNCATE TABLE DEPT;

Which three are true about the SQL statement? (Choose three.)

A. It releases the storage space used by the table.

B. It does not release the storage space used by the table.

C. You can roll back the deletion of rows after the statement executes.

D. You can NOT roll back the deletion of rows after the statement executes.

E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Correct Answer: ADF

A: The TRUNCATE TABLE Statement releases storage space used by the table,

D: Can not rollback the deletion of rows after the statement executes,

F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.

Incorrect answer: C is not true D is not true E is not true

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18


Question 2:

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.

Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT * FROM ord;

A. CREATE SYNONYM ord FOR orders; This command is issued by OE.

B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

Correct Answer: D

Creating a Synonym for an Object

To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and

provides you with an alternative name for a table, view, sequence, procedure, or other objects.

This method can be especially useful with lengthy object names, such as views.

In the syntax:

PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package.

A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):


Question 3:

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

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 \’INTERNET\’ category.

Which query would give you the required output?

A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions ) AND promo_category = \’INTERNET\’;

B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category=\’INTERNET\’);

C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = \’INTERNET\’);

D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = \’INTERNET\’);

Correct Answer: C


Question 4:

Which two are true about aggregate functions? (Choose two.)

A. You can use aggregate functions in any clause of a SELECT statement.

B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

E. You can use aggregate functions on a table, only by grouping the whole table as one single group.

F. You cannot group the rows of a table by more than one column while using aggregate functions.

Correct Answer: AD


Question 5:

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

A. SELECT TO_CHAR(TO_DATE(\’19-Mar-2001\’, `DD-Mon-YYYY\’), `fmDdspth “of” Month YYYY fmHH:MI:SS AM\’) NEW_DATE FROM dual;

B. SELECT TO_CHAR(TO_DATE(\’19-Mar-2001\’, `DD-Mon-YYYY\’), `Ddspth “of” Month YYYY fmHH:MI:SS AM\’) NEW_DATE FROM dual;

C. SELECT TO_CHAR(TO_DATE(\’19-Mar-2001\’, `DD-Mon-YYYY\’), `fmDdspth “of” Month YYYY HH:MI:SS AM\’) NEW_DATE FROM dual;

D. SELECT TO_CHAR(TO_DATE(\’19-Mar-2001\’, `DD-Mon-YYYY), `fmDdspth “of” Month YYYYfmtHH:HI:SS AM\’) NEW_DATE FROM dual;

Correct Answer: A


Question 6:

In which four clauses can a sub query be used? (Choose four.)

A. in the INTO clause of an INSERT statement

B. in the FROM clause of a SELECT statement

C. in the GROUP BY clause of a SELECT statement

D. in the WHERE clause of a SELECT statement

E. in the SET clause of an UPDATE statement

F. in the VALUES clause of an INSERT statement

Correct Answer: ABDE

A: a sub query is valid on the INTO clause of an ISERT Statement

B: a sub query can be used in the FROM clause of a SELECT statement

D: a sub query can be used in the WHERE clause of a SELECT statement,

E: a sub query can be used in the SET clauses of an UPDATE statement,

Incorrect answer:

C sub query cannot be used

F: is incorrect.

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


Question 7:

The PRODUCTS table has these columns:

PRODUCT_ID NUMBER(4)

PRODUCT_NAME VARCHAR2(45)

PRICE NUMBER(8,2)

Evaluate this SQL statement:

SELECT *

FROM PRODUCTS

ORDER BY price, product_name;

What is true about the SQL statement?

A. The results are not sorted.

B. The results are sorted numerically.

C. The results are sorted alphabetically.

D. The results are sorted numerically and then alphabetically.

Correct Answer: D

the result is sort by price which is numeric and follow by product_name which is alphabetically.

Incorrect answer: A the results are sorted B the results are sorted with alphabetically as well C the results are sorted with numerically as well

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


Question 8:

Which two statements are true regarding tables? (Choose two.)

A. A table name can be of any length.

B. A table can have any number of columns.

C. A column that has a DEFAULT value cannot store null values.

D. A table and a view can have the same name in the same schema.

E. A table and a synonym can have the same name in the same schema.

F. The same table name can be used in different schemas in the same database.

Correct Answer: EF

Synonyms

Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.


Question 9:

Examine the structure of the TRANSACTIONS table:

Name Null Type

TRANS_ID NOT NULL NUMBER(3)

CUST_NAME VARCHAR2(30)

TRANS_DATE DATE

TRANS_AMT NUMBER(10,2)

You want to display the transaction date and specify whether it is a weekday or weekend.

Evaluate the following two queries:

Which statement is true regarding the above queries?

A. Both give wrong results.

B. Both give the correct result.

C. Only the first query gives the correct result.

D. Only the second query gives the correct result.

Correct Answer: C

Range Conditions Using the BETWEEN Operator

Use the BETWEEN operator to display rows based on a range of values:

SELECT last_name, salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500;

Range Conditions Using the BETWEEN Operator

You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit.

The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500.

Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first.

You can also use the BETWEEN operator on character values:

SELECT last_name

FROM employees

WHERE last_name BETWEEN \’King\’ AND \’Smith\’;


Visit our site to get more 1Z0-051 Q and As:https://www.pass1day.com/1Z0-051.html (292 QAs Dumps)