All About Top Quality 1Z0-061 Free Practice Test

NEW QUESTION 1

You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
Exhibit:

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 time.
• C. An error because the WHERE condition specified is invalid
• D. An error because the ROUND function specified is invalid

Answer: A

NEW QUESTION 2

What is true regarding sub queries?

• A. The inner query always sorts the results of the outer query
• B. The outer query always sorts the results of the inner query
• C. The outer query must return a value to the outer query
• D. The inner query returns a value to the outer query
• E. The inner query must always return a value or the outer query will give an error

Answer: D

Explanation:
The inner query returns a value to the outer query. If the inner query does not return a
value, the outer query does not return a result

NEW QUESTION 3

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?

• A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
• B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
• C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);
• D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);

Answer: C

NEW QUESTION 4

View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Exhibit 1.

Exhibit 2.

Evaluate the following SQL statement:

What would be the outcome of the above SQL statement?

• A. It displays prod IDs in the promo with the lowest cost.
• B. It displays prod IDs in the promos with the lowest cost in the same time interval.
• C. It displays prod IDs in the promos with the highest cost in the same time interval.
• D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

Answer: D

NEW QUESTION 5

Which two statements are true regarding single row functions?

• A. MOD: returns the quotient of a division
• B. TRUNC: can be used with number and date values
• C. CONCAT: can be used to combine any number of values
• D. SYSDATE: returns the database server current date and time
• E. INSTR: can be used to find only the first occurrence of a character in a string
• F. TRIM: can be used to remove all the occurrences of a character from a string

Answer: BD

Explanation:
ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division
SYSDATE is a date function that returns the current database server date and time.
Date-Manipulation Functions
Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.
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 above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below:
ROUND(date[, 'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function
The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values.
The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com', '.', 1, 2) = 18
The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:

NEW QUESTION 6

Examine the structure of the PROMOTIONS table:

The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?

• A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
• B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
• C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
• D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Answer: D

NEW QUESTION 7

Which two statements are true about WHERE and HAVING clauses? (Choose two)

• A. A WHERE clause can be used to restrict both rows and groups.
• B. A WHERE clause can be used to restrict rows only.
• C. A HAVING clause can be used to restrict both rows and groups.
• D. A HAVING clause can be used to restrict groups only.
• E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
• F. A HAVING clause CANNOT be used in sub queries.

Answer: BD

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 8

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 sorted.
• B. The results are sorted numerically.
• C. The results are sorted alphabetically.
• D. The results are sorted numerically and then alphabetically.

Answer: D

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

NEW QUESTION 9

The CUSTOMERS table has these columns:
The CUSTOMER_ID column is the primary key for the table. You need to determine how dispersed your customer base is.
Which expression finds the number of different countries represented in the CUSTOMERS table?

• A. COUNT(UPPER(country_address))
• B. COUNT(DIFF(UPPER(country_address)))
• C. COUNT(UNIQUE(UPPER(country_address)))
• D. COUNT DISTINCT UPPER(country_address)
• E. COUNT(DISTINCT (UPPER(country_address)))

Answer: E

NEW QUESTION 10

On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command?

• A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
• B. It fails because no SQL *Loader data file location is specified.
• C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
• D. It fails because no SQL *Loader control file location is specified.

Answer: AC

Explanation:
*SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics.

NEW QUESTION 11

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
• B. It produces an error because positional notation cannot be used in the order by clause with set operators.
• C. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.
• D. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.

Answer: D

NEW QUESTION 12

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:
CUST_ID. NUMBER(4). NOT NULL CUST_NAME. VARCHAR2(100). NOT NULL CUST_ADDRESS. VARCHAR2(150)
CUST_PHONE. VARCHAR2(20)
Which SELECT statement accomplishes this task?

• A. SELECT * FROM customers
• B. SELECT name, address FROM customers;
• C. SELECT id, name, address, phone FROM customers;
• D. SELECT cust_name, cust_address FROM customers;
• E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

Answer: D

Explanation:
This answer provides correct list of columns for the output. Incorrect Answers
A:. This answer does not provide correct list of columns for the output. It is not required to show all columns of the table. Symbol “*” is used in the SELECT command to substitute a list of all columns of the table.
B:. This answer does not provide correct list of columns for the output. There are not NAME and ADDRESS columns in the CUSTOMERS table.
C:. This answer does not provide correct list of columns for the output. There are not ID, NAME, ADDRESS or PHONE columns in the CUSTOMERS table.
E:. This answer does not provide correct list of columns for the output. It is not required to show all columns of the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24 Chapter 1: Overview of Oracle Databases

NEW QUESTION 13

Evaluate the following query:

What would be the outcome of the above query?

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

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 multi byte, 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 14

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

Evaluate the following query: Exhibit:

The above query produces an error on execution. What is the reason for the error?

• A. An alias cannot be used in an expression
• B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
• C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
• D. The alias NAME should not be enclosed within double quotation marks

Answer: A

NEW QUESTION 15

Examine the structure of the EMPLOYEES table:

You want to generate a report that contains the department IDs and last names of employees ordered by hire date in their respective departments. Employees from one department must appear in a single row.
Which query will provide the required output?

• A. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP (ORDER BY hire_date)” Employees”FROM employeesGROUP BY department_idORDER BY department_id;
• B. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP (ORDER BY hire_date)” Employees”FROM employeesGROUP BY department_idORDER BY hire_date;
• C. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP (ORDER BY hire_date)” Employees”)FROM employeesGROUP BY department_idORDER BY hire_date;
• D. SELECT department_id “Dept.”,LISTAGG (last_name, ‘;’) WITHIN GROUP ORDER BY (hire_date)” Employees”)FROM employeesGROUP BY department_idORDER BY department_id;

Answer: C

NEW QUESTION 16

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. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

Explanation:
The '11-oct-2007' is a string not a date. We need to user the TO_DATE function and convert it to a date.

NEW QUESTION 17

View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: D

Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number. Data types must match:
– NVL(commission_pct, 0)
– NVL(hire_date, '01-JAN-97')
– NVL(job_id, 'No Job Yet')

NEW QUESTION 18

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 '\';

NEW QUESTION 19

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.
You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:

Which statement is true regarding the above query?

• A. It executes successfully and gives the required result.
• B. It executes successfully but does not give the required result.
• C. It produces an error because the join order of the tables is incorrect.
• D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.

Answer: B

NEW QUESTION 20

View the Exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

• A. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endval;
• B. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endvalAND cust_credit_limit BETWEEN startval AND endval;
• C. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)FROM customers)AND cust_credit_limit BETWEEN startval AND endval;
• D. SELECT custname, gradeFROM customers , gradesWHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)FROM customers)AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Answer: BC

NEW QUESTION 21

Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL;
What would be the outcome?

• A. 16
• B. 100
• C. 160
• D. 200
• E. 150

Answer: C

Explanation:
Function Purpose
ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)
TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

NEW QUESTION 22

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
B- Wrong statement with the keyword EXISTS
C- Wrong statement with the keyword EXISTS
D- Wrong statement on the MERGE new_employees
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

NEW QUESTION 23

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results the results from the SALES1 table, which are not present in the SALES2 table.

Which set operator generates the required output?

• A. UNION
• B. INTERSECT
• C. PLUS
• D. MINUS
• E. SUBSTRACT

Answer: D

NEW QUESTION 24

View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where
CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

• A. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL;
• B. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;
• C. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);
• D. UPDATE customersSET cust_credit_limit = TO_NUMBER(' ', 9999)WHERE cust_income_level IS NULL;

Answer: B

NEW QUESTION 25

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 26

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 result.
• B. It produces an error because an expression cannot be used in the order by clause.
• C. It produces an error because the DESC option cannot be used with an expression in the order by clause.
• D. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.

Answer: A

NEW QUESTION 27
......