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 1z0 061 pdf Exam.
Q9. Which two statements are true regarding constraints?
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.
Answer: B,E
Q10. Examine the structure proposed for the transactions table:
Which two statements are true regarding the creation and storage of data in the above table structure?
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
E. The TRANS_VALIDITY column would have a maximum size of one character.
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds
Answer: B,C
Explanation:
VARCHAR2(size)Variable-length character data (A maximum size must be specified:
minimum size is 1; maximum size is 4, 000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size
is 1; maximum size is 2, 000.)
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.)
DATE Date and time values to the nearest second between January 1, 4712 B.C., and
December 31, 9999 A.D.
Q11. 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
Q12. You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Explanation:
The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position:
substr('http://www.domain.com', 12, 6) = domain
The position at which the first character of the returned string begins.
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the first character.
When position is negative, then the function counts backward from the end of string. substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses
Unicode complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
When you do not specify a value for this argument, then the function
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
Q13. You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table.
Which query will generate the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q14. 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?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q15. 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
Q16. 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