Attention please! Here is the shortcut to pass your Hotest 1z0-061 pdf dumps exam! Get yourself well prepared for the Oracle Database Jun 27,2022 Latest 1z0-061 exam questions Oracle Database 12c: SQL Fundamentals exam is really a hard job. But don’t worry! We We, provides the most update 1z0-061 exam questions. With We latest 1z0-061 vce, you’ll pass the Oracle Database Latest 1z0-061 pdf dumps Oracle Database 12c: SQL Fundamentals exam in an easy way
We Geekcert has our own expert team. They selected and published the latest 1z0-061 preparation materials from Official Exam-Center.
The following are the 1z0-061 free dumps. Go through and check the validity and accuracy of our 1z0-061 dumps.Do you what to see some samples before 1z0-061 exam? Check the following 1z0-061 free dumps or download 1z0-061 dumps here.
Question 1:
View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: A
Question 2:
You have created an index with this statement:
create index ename_i on employees(last_name,first_name);
How can you adjust the index to include the employees\’ birthdays, which is a date type column called DOB?
A. Use ALTER INDEX ENAME_I ADD COLUMN DOB;.
B. You can\’t do this because of the data type mismatch.
C. You must drop the index and re-create it.
D. This can only be done if the column DOB is NULL in all existing rows.
Correct Answer: C
Question 3:
Examine the structure of the transactions table:
You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Question 4:
Which two statements are true regarding the count function?
A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
Correct Answer: BD
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement,
COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.
Question 5:
You want to create a table employees in which the values of columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required table?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
Correct Answer: AF
Question 6:
View the Exhibit and examine the structure of the EMPLOYEES table.
Examine the data in the ename and hiredate columns of the employees table:
You want to generate a list of user IDs as follows:
You issue the following query:
What is the outcome?
A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the REPLACE function is not valid.
D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
Correct Answer: A
REPLACE (text, search_string, replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3, \’9\’, \’85\’) from dual Query 2: select replace(sysdate, \’DEC\’, \’NOV\’) from dual
Question 7:
The customers table has the following structure:
You need to write a query that does the following tasks:
1.
Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2.
Only those customers whose income level has a value should be considered.
3.
Customers whose tax amount is null should not be considered. Which statement accomplishes all the required tasks?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Question 8:
You want to create a sales table with the following column specifications and data types: SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
To specify the default value of payment field you must use DEFAULT \’CASH\’.
References:
Question 9:
View the Exhibit and examine the structure of the customers table.
Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK. Which SQL statement would produce the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: C
Question 10:
Which SQL statements would display the value 1890.55 as $1, 890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55, \’$0G000D00\’)FROM DUAL;
B. SELECT TO_CHAR(1890.55, \’$9, 999V99\’)FROM DUAL;
C. SELECT TO_CHAR(1890.55, \’$99, 999D99\’)FROM DUAL;
D. SELECT TO_CHAR(1890.55, \’$99G999D00\’)FROM DUAL;
E. SELECT TO_CHAR(1890.55, \’$99G999D99\’)FROM DUAL;
Correct Answer: ADE
Question 11:
Which create table statement is valid?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: D
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint
enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.
Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.
Incorrect:
Not A: Two primary keys are not allowed.
Not B: You cannot specific a column to be both UNIQUE and NOT NULL.
Not C: The default value cannot be NOT NULL.
Question 12:
Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: C
Question 13:
Which three tasks can be performed using SQL functions built into Oracle Database?
A. Displaying a date in a nondefault format
B. Finding the number of characters in an expression
C. Substituting a character string in a text expression with a specified string
D. Combining more than two columns or expressions into a single column in the output
Correct Answer: ABC
Question 14:
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 $100 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 error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Correct Answer: B
Question 15:
Evaluate the following SQL statement:
Which statement is true regarding the above query if one of the values generated by the subquery is null?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for null as well as the other values produced by the subquery.
D. It ignores the null value and generates output for the other values produced by the subquery.
Correct Answer: D
