Exam Code: 1z0 061 practice test (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 12c SQL Fundamentals
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass oracle database 12c sql fundamentals 1z0 061 pdf free download Exam.
Q31. 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.
Q32. Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. Null values are not ignored during duplicate checking.
C. Names of all columns must be identical across all select statements.
D. The number of columns selected in all select statements need not be the same.
Answer: D
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q33. In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:
What would be the outcome?
A. Abigail PA
B. Abigail Pa
C. Abigail IS
D. An error message
Answer: B
Q34. 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
Answer: A
Reference: http://www.comp.nus.edu.sg/~ooibc/courses/sql/ddl_table.htm (see changing table structures)
Q35. Which create table statement is valid?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
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.
Q36. Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?
A. Disable the primary key in the STORES table.
B. Use CASCADE keyword with DELETE statement.
C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
D. Disable the FOREIGN KEY in SALES table and then delete the rows.
E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
Answer: A,C,D
Q37. View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:
Which statement is true regarding the execution of the above query?
A. It produces an error because the AMT_SPENT column contains a null value.
B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
Answer: C
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.
Q38. 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.
Answer: C
Q39. You issue the following command to drop the products table:
SQL> DROP TABLE products;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. A pending transaction in the session is committed.
C. All indexes on the table remain but they are invalidated.
D. All views and synonyms remain but they are invalidated.
E. All data in the table is deleted but the table structure remains.
Answer: A,B,D
Q40. CORRECT TEXT
View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which where clause would give the required result?
Answer: WHERE promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’ AND promo_cost > 20000 AND promo_end_date > ‘1-JAN-01'