1z1-061 by Oracle Actual Free Exam Questions And Answers

Question 1

Examine the structure of the employees table:

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager' should be displayed in the manager column.
Which SQL query gets the required output?

A. Option B

B. Option A

C. Option D

D. Option C

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 2

Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

A. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, marks.
FROM . students. WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99'. AND course_id = 'INT_SQL'. ORDER BY marks DESC). WHERE . ROWNUM <= 10 ;.

B. SELECT. student id, marks, ROWNUM "Rank". FROM. (SELECT student_id, marks.
FROM students. ORDER BY marks). WHERE. ROWNUM <= 10. AND. finish date
BETWEEN '01-JAN-99' AND '31-DEC-99'. AND. course_id = 'INT_SQL';

C. SELECT . student_id, marks, ROWID "Rank". FROM . students. WHERE . ROWID <
1 0. AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'. AND . course_id
'INT_SQL'. ORDER BY . marks;

D. SELECT . student_ id, marks, ROWNUM "Rank"FROM . studentsWHERE . ROWNUM
< = 10AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-99AND . course_id
'INT_SQL'ORDER BY . marks DESC;

E. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, marks.
FROM . students. WHERE . ROWNUM <= 10. AND . finish_date BETWEEN '01-JAN-99' AND '31-DEC-. 99'. AND . course_id = 'INT_SQL'. ORDER BY . marks DESC);

Discussion 0

Correct Answer: A Vote an answer

Question 3

View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)

A. SELECT promo_id. DECODE(promo_cost. >10000. 'High'. <10000. 'Low')
"Range"FROM promotions;

B. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25,
*N/A') "Catcost"FROM promotions;

C. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100)
"Discount"FROM promotions;

D. SELECT promo id. DECODE(promo_cost. 10000.DECODE(promo_category. 'Gl\ promo_cost * 25. NULL). NULL) "Catcost" FROM promotions;

Discussion 0

Correct Answer: C,D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 4

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?

A. Only the first query executes successfully.

B. Both queries execute successfully and give the same result.

C. Both queries execute successfully but give different results.

D. Only the second query executes successfully.

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

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 B

B. Option F

C. Option A

D. Option D

E. Option C

F. Option E

Discussion 0

Correct Answer: B,C Vote an answer

Question 6

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 B

B. Option A

C. Option D

D. Option C

Discussion 0

Correct Answer: C Vote an answer

Question 7

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.)

A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ''), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ', -
1, -2)<>0;

B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*')
"CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', 1, 2)<>0;

C. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',
1, 2)<>0 ;

D. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*')
"CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', -1, 2)<>0;

Discussion 0

Correct Answer: B,D Vote an answer

Question 8

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? (Choose three.)

A. Disable the primary key in the STORES table.

B. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

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. Use CASCADE keyword with DELETE statement.

Discussion 0

Correct Answer: A,C,D Vote an answer

Question 9

Which object privileges can be granted on a view?

A. ALTER, DELETE, INSERT, SELECT

B. DELETE, INSERT, SELECT

C. none

D. DELETE, INSERT, SELECT, UPDATE

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 10

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:

B. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:

C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:

D. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:

E. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:

Discussion 0

Correct Answer: C,D Vote an answer

Question 11

Examine the structure of the DEPARTMENTS table:

You execute the following command:
SQL> ALTER TABLE departments
SET UNUSED (country);
Which two statements are true? (Choose two.)

A. Synonyms existing om the DEPARTMENTS table would have to be re-created.

B. Unique key constraints defined on the COUNTRY column are removed.

C. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

D. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.

E. Views created on the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.

Discussion 0

Correct Answer: C,E Vote an answer

Question 12

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?

A. MERGE INTO new_employees c USING employees e ON (c.employee_id
e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

B. MERGE INTO new_employees c USING employees e ON (c.employee_id
e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 13

Which two statements are true regarding views? (Choose two.)

A. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

B. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table

C. A view is created with the sub query having the DISTINCT keyword can be updated

D. A sub query that defines a view cannot include the GROUP BY clause

Discussion 0

Correct Answer: A,B Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 14

View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID
2566.
Which UPDATE statement will accomplish the task?

A. UPDATE customersSET (cust_income_level, cust_credit_limit)
(SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id=2560 AND cust_id=2566)WHERE cust_id=2360;

B. UPDATE customersSET cust_income_level = (SELECT cust_income_levelFROM customersWHERE cust_id = 2560),cust_credit_limit = (SELECT cust_credit_limitFROM customersWHERE cust_id = 2566)WHERE cust_id=2360;

C. UPDATE customersSET (cust_income_level, cust_credit_limit)
(SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id IN(2560,
2 566)WHERE cust_id=2360;

D. UPDATE customersSET (cust_income_level, cust_credit_limit)
(SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id=2560 OR cust_id=2566)WHERE cust_id=2360;

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 15

Evaluate the following query:

What would be the outcome of the above query?

A. It produces an error because flower braces have been used.

B. It produces an error because the data types are not matching.

C. It executes successfully and introduces an 's at the end of each PROMO_NAME in the output.

D. It executes successfully and displays the literal "{'s start date was \} * for each row in the output.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 16

Examine the structure of the orders table:

You want to find the total value of all the orders for each year and issue the following command:

Which statement is true regarding the outcome?

A. It executes successfully and gives the correct output.

B. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

C. It gives an error because the TO_CHAR function is not valid.

D. It executes successfully but does not give the correct output.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 17

View the Exhibit; examine the structure of the PROMOTIONS table.

Each promotion has a duration of at least seven days.
Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
Which query would achieve the required result?

A. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;

B. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;

C. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;

D. SELECT promo_name, (promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;

Discussion 0

Correct Answer: C Vote an answer

Question 18

The DBA issues this SQL command:
CREATE USER scott IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

A. only the CONNECT privilege

B. no privileges

C. all the privileges of a default user

D. only the SELECT privilege

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 19

Evaluate the following query:
SELECT INTERVAL '300' MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?

A. +25-00, +00-650, +00 11:12:10.123457

B. +00-300, +54-02, +00 11:12:10.123457

C. +25-00, +54-02, +00 11:12:10.123457

D. +00-300, +00-650, +00 11:12:10.123457

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 20

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:

What would be the outcome?

A. an error because the REPLACE function specified is not valid

B. 'Oder' and 'Aus'

C. an error because the LOWER function specified is not valid

D. an error because the TRIM function specified is not valid

Discussion 0

Correct Answer: D Vote an answer

Oracle Database 12c: SQL Fundamentals - 1z1-061 Exam Practice Test

Contact Us

Useful Links

Latest Updated