Oracle 1Z0-051 Exam Questions 2021

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

You want to create a SALE_PROD view by executing the following SQL statements:

Which statement is true regarding the execution of the above statement?

• A. The view will be created and you can perform DLM operations on the view
• B. The view will not be created because the join statements are not allowed for creating a view
• C. The view will not be created because the GROUP BY clause is not allowed for creating a view
• D. The view will be created but no DML operations will be allowed on the view

Answer: D

Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION 2
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

Answer: ABDE

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
1.
rows are grouped
2.
the group function is applied to the group
3.
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 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');

Answer: C

NEW QUESTION 4
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

Answer: B

NEW QUESTION 5
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
• B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
• E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• G. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
• H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);

Answer: A

Explanation: Explanation: this is the correct MERGE statement syntax
Incorrect Answer: Bit should MERGE INTO table_name Cit should be WHEN MATCHED THEN Dit should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

NEW QUESTION 6
Which three are true? (Choose three.)

• A. A MERGE statement is used to merge the data of one table with data from anothe
• B. A MERGE statement replaces the data of one table with that of anothe
• C. A MERGE statement can be used to insert new rows into a tabl
• D. A MERGE statement can be used to update existing rows in a tabl

Answer: ACD

Explanation: The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table

NEW QUESTION 7
Where can sub queries be used? (Choose all that apply)

• A. field names in the SELECT statement
• B. the FROM clause in the SELECT statement
• C. the HAVING clause in the SELECT statement
• D. the GROUP BY clause in the SELECT statement
• E. the WHERE clause in only the SELECT statement
• F. the WHERE clause in SELECT as well as all DML statements

Answer: ABCF

Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following
are required clauses:
A SELECT list
A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

NEW QUESTION 8
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

Explanation:
Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C

NEW QUESTION 9
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

• A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy forma
• B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integer
• C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and second
• D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 character

Answer: BD

Explanation:
B: The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually
any magnitude can be stored and are guaranteed portable among different systems
operating Oracle, up to 38 digits of precision.
The following numbers can be stored in a NUMBER column:
Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits Zero Positive and negative infinity (generated only by importing from an Oracle Version 5 database)
D: The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes, and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid.
A DAY TO MINUTE interval considers an interval of days to the nearest minute. Reference: Oracle Database Concepts 10g, Native Datatypes

NEW QUESTION 10
Which two statements are true regarding savepoints? (Choose two.)

• A. Savepoints are effective only for COMMI
• B. Savepoints may be used to ROLLBAC
• C. Savepoints can be used for only DML statement
• D. Savepoints are effective for both COMMIT and ROLLBAC
• E. Savepoints can be used for both DML and DDL statement

Answer: BC

NEW QUESTION 11
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

Answer: C

Explanation: Note: You cannot use column alias in the WHERE clause.

NEW QUESTION 12
Evaluate the following query:

What would be the outcome of the above query?

• A. It produces an error because flower braces have been use
• B. It produces an error because the data types are not matchin
• C. It executes successfully and introduces an 's at the end of each promo_name in the outpu
• D. It executes successfully and displays the literal" {'s start date was} " for each row in the outpu

Answer: C

Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially
two mechanisms available. The most popular of these is to add an additional single
quotation mark next to each naturally occurring single quotation mark in the character
string
Oracle offers a neat way to deal with this type of character literal in the form of the
alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote
characters as the special pair of symbols that enclose or wrap any other character literal.
These character-enclosing symbols could have been anything other than single quotation
marks.
Bearing this in mind, consider the alternative quote (q) operator. The q operator enables
you to choose from a set of possible pairs of wrapping symbols for character literals as
alternatives to the single quote symbols. The options are any single-byte or multibyte
character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle
brackets>. Using the q operator, the character delimiter can effectively be changed from a
single quotation mark to any other character
The syntax of the alternative quote operator is as follows:
q'delimiter'character literal which may include the single quotes delimiter' where delimiter
can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter. You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

NEW QUESTION 13
You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
• B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;

Answer: B

Explanation: function MAX(column_name)
Incorrect Answer:
Ainvalid statement
Cinner query return more than one line
Dcolumn maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

NEW QUESTION 14
Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four)

• A. You cannot roll back this statemen
• B. All pending transactions are committe
• C. All views based on the DEPT table are delete
• D. All indexes based on the DEPT table are droppe
• E. All data in the table is deleted, and the table structure is also delete
• F. All data in the table is deleted, but the structure of the table is retaine
• G. All synonyms based on the DEPT table are delete

Answer: ABDE

Explanation:
You cannot roll back DROP TABLE statement. All pending transactions related on this
table are committed. If the table is dropped, Oracle automatically drops any index, trigger
and constraint associated with the table as well. All data in the table is deleted, and the
table structure is also deleted.
Incorrect Answers
C:All views based on the DEPT table become invalid, but they are not deleted.
F:All data in the table is deleted, and the table structure is also deleted. Command
TRUNCATE deletes all data in the table, but does not delete the structure of the table.
G:All synonyms based on the DEPT table are not deleted after dropping the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 15
Examine the description of the CUSTOMERS table:

The CUSTOMER_ID column is the primary key for the table.
Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

• A. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’);
• B. SELECT city_address, COUNT (*) FROMcustomers WHERE city address IN ( ‘Los Angeles’, ‘San Fransisco’) GROUP BY city_address;
• C. SELECT city_address, COUNT(customer_id) FROMcustomers WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’) GROUP BYcity_address, customer_id;
• D. SELECT city_address, COUNT (customer_id) FROM customers GROUP BY city_address IN ( ‘Los Angeles’, ‘San Fransisco’);

Answer: B

Explanation:
Not C: The customer ID in the GROUP BY clause is wrong

NEW QUESTION 16
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

Answer: AE