Oracle 1Z0-051 Free Practice Questions 2021

NEW QUESTION 1
View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables.
You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.
Which FROM clause gives the required result?

• A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
• B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);

Answer: C

NEW QUESTION 2
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 3
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
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 c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
• D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.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:
The correct statement for MERGE is MERGE INTO table_name Incorrect Answer: BWrong statement with the keyword EXISTS CWrong statement with the keyword EXISTS DWrong statement on the MERGE new_employees
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

NEW QUESTION 4
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(*).

Answer: C

Explanation:
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:
1.
Rows are grouped.
2.
The group function is applied to the group.
3.
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
list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

NEW QUESTION 5
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 6
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:

What would be the outcome of the above statement?

• A. It executes successfull
• B. It produces an error because the condition on CUST_LAST_NAME is invali
• C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null value
• D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clause

Answer: A

NEW QUESTION 7
Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

• A. No change is required to achieve the desired result
• B. SELECT ename, sal, 12*(sal+100) FROM emp;
• C. SELECT ename, sal, (12*sal)+100 FROM emp;
• D. SELECT ename, sal+100,*12 FROM emp;

Answer: B

Explanation:
to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP;
Incorrect Answer: AMultiplication and division has priority over addition and subtraction in Operator precedence. CGive wrong results DWrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11

NEW QUESTION 8
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 9
Examine the structure of the PROMOS table:

You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day.
Which query gives the correct output?

• A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') FROM promos;
• B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') FROM promos;
• C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'Same Day') FROM promos;
• D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') FROM promos;

Answer: D

Explanation:
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 ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component

NEW QUESTION 10
You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

• A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Mont
• B. Year') FROM DUAL:
• C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
• D. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Mont
• E. Year') FROM DUAL;
• F. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Mont
• G. Year')) FROM DUAL:

Answer: A

NEW QUESTION 11
View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:

Which statement is true regarding the above two SQL statements?

• A. statement 1 gives an error, statement 2 executes successfully
• B. statement 2 gives an error, statement 1 executes successfully
• C. statement 1 and statement 2 execute successfully and give the same output
• D. statement 1 and statement 2 execute successfully and give a different output

Answer: D

NEW QUESTION 12
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?

• A. 46 and 45
• B. 46 and 45.93
• C. 50 and 45.93
• D. 50 and 45.9
• E. 45 and 45.93
• F. 45.95 and 45.93

Answer: C

Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13

NEW QUESTION 13
The PRODUCTS table has these columns:
PRODUCT_ID NUMBER(4)
PRODUCT_NAME VARCHAR2(45)
PRICE NUMBER(8,2)
Evaluate this SQL statement:
SELECT *
FROM PRODUCTS
ORDER BY price, product_name;
What is true about the SQL statement?

• A. The results are not sorte
• B. The results are sorted numericall
• C. The results are sorted alphabeticall
• D. The results are sorted numerically and then alphabeticall

Answer: D

Explanation:
the result is sort by price which is numeric and follow by product_name which is alphabetically.
Incorrect Answer: Athe results are sorted Bthe results are sorted with alphabetically as well Cthe results are sorted with numerically as well
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-2

NEW QUESTION 14
Evaluate the following SQL statements: Exhibit:

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Exhibit:

Which two statements are true regarding the above view? (Choose two.)

• A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
• B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
• C. It allows you to insert rows into the EMPLOYEES table
• D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

Answer: BD

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

• A. Views can be created only from table
• B. Views can be created from tables or other view
• C. Only simple views can use indexes existing on the underlying table
• D. Both simple and complex views can use indexes existing on the underlying table
• E. Complex views can be created only on multiple tables that exist in the same schem
• F. Complex views can be created on multiple tables that exist in the same or different schema

Answer: BDF

Explanation:
Creating a Sequence (continued)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after
reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and
keeps in memory (By default, the Oracle server caches 20 values.)

NEW QUESTION 16
Examine the structure and data in the PRIC E_LIST table: Name Null? Type
PROD_D NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD PRICE
100 $234.55
101 $6,509.75
102 $1,234
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 PRICEJLIST:
• B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
• C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.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: C

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

NEW QUESTION 18
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?

• A. It removes all the rows as well as the structure of the table
• B. It removes all the rows in the table and deleted rows cannot be rolled back
• C. It removes all the rows in the table and deleted rows can be rolled back
• D. It would not remove the rows if the table has a primary key

Answer: C

NEW QUESTION 19
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?

• A. Only the synonym would be droppe
• B. The synonym would be dropped and the corresponding table would become invali
• C. The synonym would be dropped and the packages referring to the synonym would be droppe
• D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invali

Answer: A

Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

NEW QUESTION 20
View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00.
Which two queries would give the required result? (Choose two.)

• A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
• B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00';
• C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00'));
• D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00');

Answer: AD