IT認証試験問題集
毎月、ITshikenは1500人以上の受験者が試験準備を助けて、試験に合格するために受験者にご協力します
 ホームページ / 1Z0-061 問題集  / 1Z0-061 問題練習

Oracle 1Z0-061 問題練習

Oracle Database 12c: SQL Fundamentals 試験

最新更新時間: 2019/07/10,合計352問。

平成から令和へ:1Z0-061 最新真題を買う時、日本語版と英語版両方を同時に獲得できます。

実際の問題集を練習し、試験のポイントを了解し、テストに申し込むするかどうかを決めることができます。

さらに試験準備時間の35%を節約するには、1Z0-061 問題集を使用してください。

 / 24

Question No : 1
Examine the structure of the PROMOS table:



You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed.
Which queries give the correct output? (Choose all that apply.)

正解:

Question No : 2
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE DATE
TRANS_AMT NUMBER(10, 2)
You want to display the transaction date and specify whether it is a weekday or weekend.
Evaluate the following two queries:



Which statement is true regarding the above queries?

正解:
Explanation:
Range Conditions Using the BETWEEN
Operator Use the BETWEEN operator to display rows based on a range of values:
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
Range Conditions Using the BETWEEN Operator
You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit.
The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2, 500 and $3, 500.
Values that are specified with the BETWEEN operator are inclusive.
However, you must specify the lower limit first.
You can also use the BETWEEN operator on character values:
SELECT last_name
FROM employees
WHERE last_name BETWEEN 'King' AND 'Smith';

Question No : 3
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?

正解:
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

Question No : 4
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?

正解:
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.

Question No : 5
View the Exhibit and examine the structure and data in the INVOICE table.



Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)

正解:
Explanation:
In some cases, the Oracle server receives data of one data type where it expects data of a different data type.
When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.
Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output.
Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.

Question No : 6
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
----------------------
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)

正解:

Question No : 7
View the Exhibit and examine the structure of the CUSTOMERS table.
In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'. You issue the following query:
SQL> SELECT LOWER(REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O'))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE 'a%n';
What would be the outcome?

正解:
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
The TRIM Function
The TRIM function removes characters from the beginning or end of character literals, columns or expressions to yield one potentially shorter character item. Numeric and date literals are automatically cast as characters when they occur as parameters to the TRIM function. Numeric or date expressions are evaluated first before being converted to strings ready to be trimmed.
The TRIM function takes a parameter made up of an optional and a mandatory component. Its syntax is TRIM
([trailing|leading|both] trimstring from s).
The string to be trimmed (s) is mandatory.
The following points list the rules governing the use of this function:
TRIM(s) removes spaces from both sides of the input string. TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of the string s if it is present. TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of the string s if it is present.
TRIM(both trimstring from s) removes all occurrences of trimstring from the beginning and end of the string s if it is present. The following queries illustrate the usage of this function:
Query 1: select trim(trailing 'e' from 1+2.14||' is pie') from dual
Query 2: select trim(both '*' from '*******Hidden*******') from dual
Query 3: select trim(1 from sysdate) from dual
ORA-30001: trim set should have only one character

Question No : 8
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)

正解:

Question No : 9
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.



Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"
FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost "code"
FROM promotions
ORDER BY 1;
Which statement is true regarding the execution of the above queries?

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

Question No : 10
View the Exhibit and examine the structure of the PRODUCTS table.



You want to display only those product names with their list prices where the list price is at least double the minimum price.
The report should start with the product name having the maximum list price satisfying this condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name, prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output? (Choose all that apply.)

正解:
Explanation:
Using the ORDER BY Clause
The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows.
However, if you use the ORDER BY clause, it must be the last clause of the
SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition.
Syntax
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, numeric_position} [ASC|DESC]];
In the syntax:
ORDER BY specifies the order in which the retrieved rows are displayed
ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.
Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

Question No : 11
View the Exhibit and examine the structure of the CUSTOMERS table.



You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:



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

正解:

Question No : 12
View the Exhibit and examine the structure of CUSTOMERS table.



Evaluate the following query:



Which statement is true regarding the above query?

正解:

Question No : 13
View the Exhibit and examine the data in the PROMOTIONS table. PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

正解:

Question No : 14
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?

正解:
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.

Question No : 15
Which two statements are true regarding indexes? (Choose two.)

正解:

 / 24