Precise 1Z0-051 Braindumps 2021

NEW QUESTION 1
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit

Using the PROMOTIONS table, you need to display the names of all promos done after
January 1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)

• A. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1 DESC;
• B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
• C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;
• D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC;

Answer: BC

NEW QUESTION 2
Examine the structure and data in the PRICE_LIST table:
Name Null Type
PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE
100 $234.55 101 $6,509.75 102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?

• A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
• B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
• C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
• D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

Answer: B

Explanation: Use TO_NUMBER on the prod_price column to convert from char to number
to be able to multiply it with 0.25. Then use the TO_CHAR function (with
formatting'$99,999.00') to convert the number back to char.
Incorrect:
Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the
TO_NUMBER function.
Note:
*
Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single
*
Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

NEW QUESTION 3
View the Exhibit and examine the structure of the PRODUCTS table.

Which two tasks would require subqueries? (Choose two.)

• A. Display the minimum list price for each product statu
• B. Display all suppliers whose list price is less than 1000.
• C. Display the number of products whose list price is more than the average list pric
• D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
• E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

Answer: CE

NEW QUESTION 4
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

• A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
• B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
• C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
• D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
• E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
• F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

Answer: F

Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

NEW QUESTION 5
Which best describes an inline view?

• A. a schema object
• B. a sub query that can contain an ORDER BY clause
• C. another name for a view that contains group functions
• D. a sub query that is part of the FROM clause of another query

Answer: D

Explanation:
a sub query that is part of the FROM clause of another query
Incorrect Answer:
Ais not a schema object
Bsub query can contain GROUP BY clause as well.
Cdoes not necessary contains group functions
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21

NEW QUESTION 6
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO
columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)

• A. CREATE INDEX ord_idx1 ON ord(ord_no);
• B. CREATE INDEX ord_idx2 ON ord_items(ord_no);
• C. CREATE INDEX ord_idx3 ON ord_items(item_no);
• D. CREATE INDEX ord_idx4 ON ord,ord_items(ord_no, ord_date,qty);

Answer: BC

Explanation: How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a
column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index
is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create
the FOREIGN KEY column index for a join in a query to improve the speed of retrieval.
Note: You can manually create a unique index, but it is recommended that you create a
unique constraint, which implicitly creates a unique index.

NEW QUESTION 7
What does the FORCE option for creating a view do?

• A. creates a view with constraints
• B. creates a view even if the underlying parent table has constraints
• C. creates a view in another schema even if you don't have privileges
• D. creates a view regardless of whether or not the base tables exist

Answer: D

Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3

NEW QUESTION 8
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.
You issue the following SQL statements:
Exhibit:

What would be the outcome?

• A. It generates an error because multiple conditions cannot be specified for the WHEN clause
• B. It executes successfully and gives the required result
• C. It generates an error because CASE cannot be used with group functions
• D. 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

NEW QUESTION 9
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clause are valid for the above query? (Choose all that apply.)

• A. ORDER BY 2,1
• B. ORDER BY CUST_NO
• C. ORDER BY 2,cust_id
• D. ORDER BY "CUST_NO"
• E. ORDER BY "Last Name"

Answer: ACE

Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.

NEW QUESTION 10
A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table
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

Answer: B

Explanation: 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:
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6

NEW QUESTION 11
The CUSTOMERS table has these columns:

A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France?

• A. WHERE lower(country_address) = "france"
• B. WHERE lower(country_address) = 'france'
• C. WHERE lower(country_address) IS 'france'
• D. WHERE lower(country_address) = '%france%'
• E. WHERE lower(country_address) LIKE %france%

Answer: B

Explanation:
WHERE lower(country_address)=’france’
Incorrect Answer: Ainvalid use of symbol “” Cinvalid use of IS keyword Dinvalid use of % in condition Einvalid use of condition Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-12

NEW QUESTION 12
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 resul
• B. It executes successfully but does not return the correct resul
• C. It generates an error because TO_CHAR should be replaced with TO_DAT
• D. It generates an error because rrrr should be replaced by rr in the format strin
• E. It generates an error because fm and double quotation marks should not be used in the format strin

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.

NEW QUESTION 13
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?

• A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
• B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
• C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
• D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

Answer: B

NEW QUESTION 14
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 two SQL statements would give the required output? (Choose two.)

• A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
• B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
• C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
• D. SELECT TO_CHAR(SYSDAT
• E. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
• F. SELECT TO_DATE(SYSDAT
• G. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:

Answer: BC

NEW QUESTION 15
Which two statements are true regarding views? (Choose two.)

• A. A simple view in which column aliases have been used cannot be update
• B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keywor
• C. Rows added through a view are deleted from the table automatically when the view is droppe
• D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating i
• E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the vie

Answer: BD

NEW QUESTION 16
The STUDENT_GRADES table has these columns:

Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?

• A. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ OR gpa > 3.;
• B. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ AND gpa gt 3.0;
• C. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ AND gpa > 3.0;
• D. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ OR gpa > 3.0;
• E. SELECT student_id, gpa FROM student_grades WHERE semester_end > ’01-JAN-2001’ OR semester_end < ’31-DEC-2001’ AND gpa >= 3.0;

Answer: C