PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE
QUESTION 141
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’MON’), ‘dd “is the first Monday for” fmmonth rrrr’) FROM DUAL;
What is the outcome?
A. It executes successfully and returns the correct result.
B. It executes successfully but does not return the correct result.
C. It generates an error because TO_CHAR should be replaced with TO_DATE.
D. It generates an error because rrrr should be replaced by rr in the format string.
E. It generates an error because fm and double quotation marks should not be used in the format string.
Answer: A
Explanation:
– NEXT_DAY(date, ‘char’): Finds the date of the next specified day of the week (‘char’) following date. The value of char may be a number representing a day or a character string.
– LAST_DAY(date): Finds the date of the last day of the month that contains date
The second innermost function is evaluated next. TO_CHAR(’28-OCT-2009′, ‘fmMonth’) converts the given date based on the Month format mask and returns the character string October.
The fm modifier trims trailing blank spaces from the name of the month.
QUESTION 142
You need to calculate the number of days from 1st January 2007 till date. Dates are stored in the default format of dd-mon-rr. Which SQL statements would give the required output? (Choose two .)
A. SELECT SYSDATE – ’01-JAN-2007′ FROM DUAL;
B. SELECT SYSDATE – TO_DATE(’01/JANUARY/2007′) FROM DUAL;
C. SELECT SYSDATE – TO_DATE(’01-JANUARY-2007′) FROM DUAL;
D. SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2007′ FROM DUAL;
E. SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ’01/JANUARY/2007′ FROM DUAL;
Answer: BC
QUESTION 143
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;
Answer: C
QUESTION 144
The SQL statements executed in a user session are as follows:
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
A. The rollback generates an error.
B. No SQL statements are rolled back.
C. Only the DELETE statements are rolled back.
D. Only the second DELETE statement is rolled back.
E. Both the DELETE statements and the UPDATE statement are rolled back.
Answer: AB
QUESTION 145
When does a transaction complete? (Choose all that apply.)
A. when a DELETE statement is executed
B. when a ROLLBACK command is executed
C. when a PL/SQL anonymous block is executed
D. when a data definition language ( DDL) statement is executed
E. when a TRUNCATE statement is executed after the pending transaction
Answer: BDE
QUESTION 146
Which statement is true regarding transactions? (Choose all that apply.)
A. A transaction can consist only of a set of DML and DDL statements.
B. A part or an entire transaction can be undone by using ROLLBACK command .
C. A transaction consists of a set of DML or DCL statements.
D. A part or an entire transaction can be made permanent with a COMMIT.
E. A transaction can consist of only a set of queries or DML or DDL statements.
Answer: BC
QUESTION 147
View the Exhibit and examine the structure of the PROMOTIONS table. Each promotion has a duration of at least seven days. Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date. Which query would achieve the required result?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
Answer: D
QUESTION 148
Which two statements are true regarding the starting of the database instance using the following command? (Choose two.)
SQL>STARTUP UPGRADE
A. It enables all system triggers.
B. It allows only SYSDBA connections.
C. It ensures that all job queues remain active during the upgrade process.
D. It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be run.
Answer: BD
QUESTION 149
View the Exhibit for the structure of the STUDENT and FACULTY tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:
Which statement is true regarding the outcome?
A. Only s tatement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: D
QUESTION 150
View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables. You need to generate a report that gives details of the customer’s last name, name of the product, and the quantity sold for all customers in ‘ Tokyo’. Which two queries give the required result? (Choose two.)
A. SELECT c.cust_last_name,p.prod_name, s.quantity_sold
FROM sales s JOIN products p
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city=’Tokyo’;
B. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city=’Tokyo’;
C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
AND c.cust_city=’Tokyo’;
D. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales s
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city=’Tokyo’;
Answer: AC
QUESTION 151
View the Exhibit and examine the data in the table. The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them. The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks. You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on. Which query would give the required result?
A. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p JOIN proj_task_details d
ON (p.based_on = d.task_id);
B. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
C. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
D. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p JOIN proj_task_details d
ON (p.task_id = d.task_id);
Answer: B
QUESTION 152
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Answer: AD
QUESTION 153
View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables. The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively. Evaluate the following CREATE TABLE command:
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
Answer: B
QUESTION 154
View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE – 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE – 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE – 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;
Answer: AB
QUESTION 155
You need to extract details of those products in the SALES table where the PROD_ID column contains the string ‘_D123’. Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘_’
B. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\’
C. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘%_’
D. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\_’
Answer: B
Explanation:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement:
select job_id from jobs
where job_id like ‘SA\_%’ escape ‘\’;
QUESTION 156
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.
Answer: BD
Explanation:
A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.
QUESTION 157
Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,’$0G000D00′)
FROM DUAL;
B. SELECT TO_CHAR(1890.55,’$9,999V99′)
FROM DUAL;
C. SELECT TO_CHAR(1890.55,’$99,999D99′)
FROM DUAL;
D. SELECT TO_CHAR(1890.55,’$99G999D00′)
FROM DUAL;
E. SELECT TO_CHAR(1890.55,’$99G999D99′)
FROM DUAL;
Answer: ADE
QUESTION 158
Examine the structure of the SHIPMENTS table:
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. Evaluate the following two queries:
Which statement is true regarding the above commands?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.
Answer: C
QUESTION 159
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
Answer: CD
QUESTION 160
View the Exhibit and examine the structure of the CUSTOMERS table. Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is ‘married’
C. finding the average credit limit of male customers residing in ‘Tokyo’ or ‘Sydney’
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city ‘Tokyo’
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: DE
PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE