1Z0-051 | Oracle 1Z0-051 Exam Questions 2021
It is more faster and easier to pass the 1z0 051 pdf by using 1z0 051 practice test. Immediate access to the 1z0 051 latest dumps free download pdf and find the same core area 1z0 051 pdf with professionally verified answers, then PASS your exam with a high score now.
Check 1Z0-051 free dumps before getting the full version:
NEW QUESTION 1
View the Exhibit and examine the data in the EMPLOYEES table:
You want to display all the employee names and their corresponding manager names.
Evaluate the following query:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
- A. only inner JOIN
- B. only FULL OUTER JOIN
- C. only LEFT OUTER JOIN
- D. only RIGHT OUTER JOIN
NEW QUESTION 2
The user Sue issues this SQL statement:
GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;
The user Alice issues this SQL statement:
GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement:
GRANT SELECT ON sue.EMP TO timber;
The user Sue issues this SQL statement:
REVOKE select on sue.EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?
- A. Alice only
- B. Alice and Reena
- C. Alice, Reena, and Timber
- D. Sue, Alice, Reena, and Timber
Explanation: use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked. Alice, Reena and Timber will be revoke.
Incorrect Answer: Athe correct answer should be Alice, Reena and Timber Bthe correct answer should be Alice, Reena and Timber Dthe correct answer should be Alice, Reena and Timber
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-17
NEW QUESTION 3
Which two statements are true regarding single row functions? (Choose two.)
- A. They can be nested only to two levels
- B. They always return a single result row for every row of a queried table
- C. Arguments can only be column values or constant
- D. They can return a data type value different from the one that is referenced
- E. They accept only a single argument
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.
NEW QUESTION 4
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
- A. It executes and produces the required resul
- B. It produces an error because an expression cannot be used in the ORDER BY claus
- C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY claus
- D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT claus
NEW QUESTION 5
Which two statements are true regarding constraints? (Choose two.)
- A. A table can have only one primary key and one foreign ke
- B. A table can have only one primary key but multiple foreign key
- C. Only the primary key can be defined at the column and table level
- D. The foreign key and parent table primary key must have the same nam
- E. Both primary key and foreign key constraints can be defined at both column and table level
NEW QUESTION 6
The STUDENT_GRADES table has these columns:
Which statement finds the highest grade point average (GPA) per semester?
- A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
- B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
- C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
- D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
- E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Explanation: Explanation: For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed
Incorrect Answer: Aper semester condition is not included Bresult would not display the highest gpa value Dinvalid syntax error Einvalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
NEW QUESTION 7
Examine the data in the CUSTOMERS table:
You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:
SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
- A. JOIN
- B. NATURAL JOIN
- C. LEFT OUTER JOIN
- D. FULL OUTER JOIN
- E. RIGHT OUTER JOIN
NEW QUESTION 8
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)
- A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
- B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
- C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
- D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
- E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
NEW QUESTION 9
View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.
What would be the outcome if all the parentheses are removed from the above statement?
- A. It produces a syntax erro
- B. The result remains unchange
- C. The total price value would be lower than the correct valu
- D. The total price value would be higher than the correct valu
NEW QUESTION 10
Which object privileges can be granted on a view?
- A. none
- B. DELETE, INSERT,SELECT
- C. ALTER, DELETE, INSERT, SELECT
- D. DELETE, INSERT, SELECT, UPDATE
Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.
Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12
NEW QUESTION 11
Which two statements are true about WHERE and HAVING clauses? (Choose two)
- A. A WHERE clause can be used to restrict both rows and group
- B. A WHERE clause can be used to restrict rows onl
- C. A HAVING clause can be used to restrict both rows and group
- D. A HAVING clause can be used to restrict groups onl
- E. A WHERE clause CANNOT be used in a query of the query uses a HAVING claus
- F. A HAVING clause CANNOT be used in sub querie
Explanation: B: WHERE clause cannot be use to restrict groups
WHERE clause cannot be use when there is group functions.
D: A HAVING clause can only e used to restrict GROUPS.
Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause
rows are grouped
the group function is applied to the group
the group that match the criteria in the Having clause are displayed.
Incorrect Answers :
A. Where clause cannot be use to restrict groups
C. A HAVING clause can only e used to restrict GROUPS.
E. WHERE clause cannot be use when there is group function, instead HAVING is to be use.
F. There is no constraint to use HAVING clause in a sub queries.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20
NEW QUESTION 12
You need to produce a report where each customer's credit limit has been incremented by $1000.
In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit.
The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?
- A. SELECT cust_last_name Name, cust_credit_limit + 1000 "New Credit Limit" FROM customers;
- B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers;
- C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;
- D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW CREDIT LIMIT") FROM customers;
A column alias:
-Renames a column heading
-Is useful with calculations
-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.
NEW QUESTION 13
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
a sub query that is part of the FROM clause of another query
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 14
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
- A. It executes successfully and generates the required resul
- B. It produces an error because COUNT(*) should be specified in the SELECT clause als
- C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE claus
- D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further
restricting the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for
which the specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
Rows are grouped.
The group function is applied to the group.
The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you
place the GROUP BY clause first because it is more logical. Groups are formed and group
functions are calculated before the HAVING clause is applied to the groups in the SELECT
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.
NEW QUESTION 15
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
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 16
You work as a database administrator at ABC.com. You study the exhibit carefully.
You issue the following SQL statement:
Which statement is true regarding the execution of the above query?
- A. It produces an error because the AMT_SPENT column contains a null valu
- B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMI
- C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nul
- D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functio
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.
NEW QUESTION 17
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
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 tim
- C. An error because the WHERE condition specified is invalid
- D. An error because the ROUND function specified is invalid
Thanks for reading the newest 1Z0-051 exam dumps! We recommend you to try the PREMIUM Certleader 1Z0-051 dumps in VCE and PDF here: https://www.certleader.com/1Z0-051-dumps.html (292 Q&As Dumps)