Exam Code: 1Z0-061 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 12c SQL Fundamentals
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-061 Exam.
2021 Mar 1Z0-061 practice test
Q21. Examine the structure of the products table:
You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q22. You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'active1 if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
CLOB Character data (up to 4 GB)
NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from –84 to 127.)
Q23. In which three situations does a transaction complete?
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language (DDL) statement is executed
E. When a TRUNCATE statement is executed after the pending transaction
Answer: B,D,E
Q24. You issue the following command to alter the country column in the departments table: Which statement is true?
A. It produces an error because column definitions cannot be altered to add default values.
B. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'.
C. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table.
D. It produces an error because the data type for the column is not specified.
Answer: B
Q25. 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 result.
B. It produces an error because count(*) should be specified in the SELECT clause also.
C. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.
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.
Renovate 1Z0-061 free practice test:
Q26. 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
Answer: D
Q27. 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.
Answer: B,D
Explanation:
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.
Q28. 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
Answer: A
Q29. Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
A. First normal form
B. Second normal form
C. Third normal form
D. Fourth normal form
Answer: B
Q30. Evaluate the following SQL commands:
The command to create a table fails. Identify the two reasons for the SQL statement failure?
A. You cannot use SYSDATE in the condition of a check constraint.
B. You cannot use the BETWEEN clause in the condition of a check constraint.
C. You cannot use the NEXTVAL sequence value as a default value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.
Answer: A,C
Explanation:
CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... Salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary > 0),