Welcome to download the newest Pass4itsure 1z0-051 dumps:
ATTENTION : Because Oracle 1Z0-051 exam has change recently,Flydumps has updated the Oracle 1Z0-051 exam dumps with all new EMC E20-545 exam questions and answers, visit flydumps.com to get free Oracle 1Z0-051 PDF and VCE dumps.
QUESTION 77
You need to display the date 11-Oct-2007 in words as `Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?
A. SELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth “of” Month, Year’) FROM DUAL;
B. SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’) FROM DUAL;
C. SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp “of” Month, Year’) FROM DUAL;
D. SELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’)) FROM DUAL;
Correct Answer: C Explanation
Explanation/Reference:
Explanation: Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string ’11- Nov-1999′ is ‘DD-Mon-YYYY’. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines · The format model must be enclosed with single quotation marks and is case-sensitive. · The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma. · The names of days and months in the output are automatically padded with blanks. · To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model ——————————————————————— DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week DD Numeric day of the month
MM Two-digit value for the month MON Three-letter abbreviation of the month MONTH Full name of the month YYYY Full year in numbers YEAR Year spelled out (in English)
QUESTION 78
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. The number of columns and data types must be identical for all SELECT statements in the query
C. The names of columns in all SELECT statements must be identical
D. Reversing the order of the intersected tables the result
Correct Answer: B Explanation
Explanation/Reference:
Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing
duplicates.
The columns in the queries that make up a compound query can have different names, but the output
result set will use the names of the columns in the first query.
QUESTION 79
You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.
Evaluate the following SQL statement: Exhibit: Which statement is true regarding the execution of the above UPDATE statement?
A. It would execute and restrict modifications to only the column specified in the SELECT statement
B. It would not execute because two tables cannot be used in a single UPDATE statement
C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
D. It would not execute because the SELECT statement cannot be used in place of the table name
Correct Answer: A Explanation
Explanation/Reference:
QUESTION 80
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(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
Correct Answer: F Explanation
Explanation/Reference:
QUESTION 81
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Evaluate the following query: Exhibit:
The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression
B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
D. The alias NAME should not be enclosed within double quotation marks
Correct Answer: A Explanation
Explanation/Reference:
QUESTION 82
Which statement is true regarding synonyms?
A. Synonyms can be created only for a table
B. Synonyms are used to reference only those tables that are owned by another user
C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
D. A public synonym and a private synonym can exist with the same name for the same table
Correct Answer: D Explanation
Explanation/Reference:
QUESTION 83
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.
Exhibit:
The query generates an error.
What is the reason of generating error?
A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
B. The parenthesis should be added to enclose the entire expression
C. The column alias should be replaced with the expression in the WHERE clause
D. The double quotation marks should be removed from the column alias
Correct Answer: C Explanation
Explanation/Reference:
Note: You cannot use column alias in the WHERE clause.
QUESTION 84
Which one is a system privilege?
A. SELECT
B. DELETE
C. EXECUTE
D. ALTER TABLE
E. CREATE TABLE
Correct Answer: E Explanation
Explanation/Reference:
QUESTION 85
What is true about sequences?
A. The start value of the sequence is always 1.
B. A sequence always increments by 1.
C. The minimum value of an ascending sequence defaults to 1.
D. The maximum value of descending sequence defaults to 1.
Correct Answer: C Explanation
Explanation/Reference:
QUESTION 86
Examine the structure of the INVOICE table: Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’) FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’) FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’) FROM invoice;
Correct Answer: AC Explanation
Explanation/Reference:
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 87
View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?
A. UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
B. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;
C. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;
D. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;
Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Updating Two Columns with a Subquery
You can update multiple columns in the SET clause of an UPDATE statement by writing multiple
subqueries. The syntax is as follows:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,
column = (SELECT column FROM table WHERE condition)] [WHERE condition ] ;
QUESTION 88
A SELECT statement can be used to perform these three functions:
1.
Choose rows from a table.
2.
Choose columns from a table
3.
Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?
A. difference, projection, join
B. selection, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Correct Answer: B Explanation
Explanation/Reference:
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:
A answer should have SELECTION, PROJECTION and JOIN.
C answer should have SELECTION, PROJECTION and JOIN.
D answer should have SELECTION, PROJECTION and JOIN.
E answer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6
QUESTION 89
See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;
C. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;
D. SELECT AVG(inv_date) FROM invoice;
Correct Answer: AB Explanation
Explanation/Reference:
QUESTION 90
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
Exhibit:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?
A. Number of days since the promo started based on the current Chicago data and time
B. Number of days since the promo started based on the current Singapore data and time.
C. An error because the WHERE condition specified is invalid
D. An error because the ROUND function specified is invalid Correct Answer: A
Explanation Explanation/Reference:
QUESTION 91
Which is a valid CREATE TABLE statement?
A. CREATE TABLE EMP9$# AS (empid number(2));
B. CREATE TABLE EMP*123 AS (empid number(2));
C. CREATE TABLE PACKAGE AS (packid number(2));
D. CREATE TABLE 1EMP_TEST AS (empid number(2)); Correct Answer: A
Explanation Explanation/Reference:
Explanation: Table names and column names must begin with a letter and be 1-30 characters long.
Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is discouraged).
Incorrect answer:
B Non alphanumeric character such as “*” is discourage in Oracle table name.
D Table name must begin with a letter.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4
The Oracle 1Z0-051 study materials are followed at some levels to test the skills which are required for the information technology companies. The flydumps Oracle 1Z0-051 test questions are must for you to get the Oracle 1Z0-051 exam and it helps to become an expert database administrator. Flydumps Oracle 1Z0-051 study materials are providing the ways for solving the troubleshooting problems in the operating systems. The Oracle 1Z0-051 certification exam preparation will be helpful for you to solve the complex problems in the information technology companies. Oracle 1Z0-051 study materials guide you to develop the projects or infrastructures. Flydumps Oracle 1Z0-051 test questions provide the ways for planning, configuring and operational technologies that are suitable for the information technology sectors.
Welcome to download the newest Pass4itsure 1z0-051 dumps: https://www.pass4itsure.com/1z0-051.html