Oracle 1Z0-061 Certification Sample Questions and Answers

To make you familiar with Oracle Oracle Database 12c: SQL Fundamentals(1Z0-061) certification exam structure, we have prepared this sample question set. We suggest you to try our Demo Oracle OCA 1Z0-061 Certification Practice Exam to test your understanding of Oracle 1Z0-061 process with real Oracle certification exam environment.

If you are planning to write the Oracle OCA 1Z0-061 certification exam. We strongly recommend you to use our Premium Oracle 1Z0-061 Certification Practice Exam to clear your certification exam with good score.

1Z0-061 Sample Questions:

01) You query the database with this SQL statement:
SELECT AVG(LENGTH(name)) COLUMN1, SUM(INSTR(ssn,'52',2,2)) COLUMN2 FROM emp2 WHERE name = INITCAP (name);
 
Review the structure and data of the emp2 table.
 
Structure:
1z0-061 dumps, 1z0-061 questions
Data: 
1z0-061 sample questions
What will be displayed for the output of COLUMN1 and COLUMN2?
a) The value in COLUMN1 will be 3.5 and the value in COLUMN2 will be 11.
b) The value in COLUMN1 will be 3.6 and the value in COLUMN2 will be 19.
c) The value in COLUMN1 will be 3.5 and the value in COLUMN2 will be 26.
d) The value in COLUMN1 will be 3.6 and the value in COLUMN2 will be 26.
e) None of the above will be displayed for the output.
 
02) You query the database with this SQL statement:
SELECT id_number, NVL(100 / quantity, 0)
FROM product;
 
Which SQL SELECT statement capabilities are performed by this query?
a) selection only
b) projection only
c) selection and projection only
d) projection, selection, and joining
 
03) Which arithmetic expression will return a numeric value?
a) '14-FEB-2002' + 25
b) '03-JAN-2000' - 30
c) '17-JUN-1999' * (480/24)
d) TO_DATE('01-JAN-2001') - TO_DATE('01-DEC-2000')
 
04) Click the Exhibit(s) button to examine the data from the po_header and po_detail tables.
 
Examine the structures of the po_header and po_detail tables:
PO_HEADER
--------------------
PO_NUM      NUMBER  NOT NULL
PO_DATE     DATE  DEFAULT SYSDATE
PO_TOTAL    NUMBER(9,2)
SUPPLIER_ID NUMBER(9)
PO_TERMS    VARCHAR2(25)
 
PO_DETATIL
------------------
PO_NUM      NUMBER  NOT NULL
PO_LINE_ID  NUMBER  NOT NULL
PRODUCT_ID  NUMBER  NOT NULL,
QUANTITY    NUMBER(3)  NOT NULL,
UNIT_PRICE  NUMBER (5,2)  DEFAULT 0,
 
The primary key of the po_header table is po_num. The primary key of the po_detail table is the combination of po_num and po_line_id. A foreign key constraint is defined on the po_num column of the po_detail table that references the po_header table.
 
You want to update the purchase order total amount for a given purchase order. The po_total column in the po_header table should equal the sum of the extended amounts of the corresponding po_detail records. You want the user to be prompted for the purchase order number when the query is executed. When a purchase order is updated, the po_date column should be reset to the current date.
 
Which UPDATE statement should you execute?
a)
UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail
WHERE po_num = &&ponum)),
SET po_date = sysdate
WHERE po_num = &&ponum;
b)
UPDATE po_header
SET po_total = (SELECT SUM(quantity * unit_price)
FROM (SELECT po_num)
FROM po_detail
WHERE po_num = &&ponum)),
po_date = DEFAULT
WHERE po_num = &&ponum;
c)
UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail
WHERE po_num = &&ponum)),
UPDATE po_header
SET po_date = sysdate
WHERE po_num = &&ponum;
d)
UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail
WHERE po_num = &&ponum)),
po_date = DEFAULT
WHERE po_num = &&ponum;
e)
UPDATE po_header
SET po_total = (SELECT po_num, SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail
WHERE po_num = &&ponum)),
po_date = DEFAULT
WHERE po_num = &&ponum;
f)
UPDATE po_header
SET po_total = (SELECT SUM(ext)
FROM (SELECT po_num, quantity * unit_price ext
FROM po_detail
WHERE po_num = &&ponum)),
po_date = NULL
WHERE po_num = &&ponum;
 
05) The account table contains these columns:
ACCOUNT_ID NUMBER(12)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
 
You need to create a single SELECT statement to accomplish these requirements:
- Display accounts that have a new balance that is less than the previous balance.
- Display accounts that have a finance charge that is less than $25.00.
- Display accounts that have no finance charge. 
 
Evaluate this statement:
SELECT account_id
FROM account
WHERE new_balance < prev_balance
AND NVL(finance_charge, 0) < 25;
 
How many of the three requirements will this SELECT statement accomplish?
a) all of the requirements
b) one of the requirements
c) two of the requirements
d) none of the requirements
 
06) Examine the data in the product table.
1z0-061 questions and answers
Evaluate this SELECT statement:
SELECT description, cost
FROM product
ORDER BY cost, quantity;
 
Which statements are true? (Choose all that apply.)
a) The product_id value for the first record displayed is 220.
b) The product_id values for the last two rows displayed are 140 and 126.
c) The description value for the first two rows displayed is C 2pk-battery.
d) The description value for the first two rows displayed is AA 2pk-battery.
e) No row with a product_id of 220is displayed.
 
07) Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.)
a) The Oracle Server will evaluate a HAVING clause before a WHERE clause.
b) The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.
c) The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.
d) The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.
e) The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.
 
08) The employee table contains these columns:
EMPLOYEE_ID NUMBER NOT NULL
EMP_LNAME VARCHAR2(20) NOT NULL
EMP_FNAME VARCHAR2(10) NOT NULL
DEPT_ID NUMBER
SALARY NUMBER(9,2)
 
A user needs to retrieve information on employees who have the same department ID and salary as an employee ID that the user will enter. You want the query results to include employees who do not have a salary, but not the employee that the user entered.
 
Which statement will return the desired result?
a)
SELECT *
FROM employee
WHERE (department, salary) NOT IN
(SELECT department, salary)
FROM employee
WHERE employee_id = &1);
b)
SELECT *
FROM employee
WHERE (dept_id, salary) IN
(SELECT dept_id, NVL(salary, 0)
FROM employee
WHERE employee_id = &1);
c)
SELECT *
FROM employee
WHERE (dept_id, NVL(salary, 0)) IN
(SELECT dept_id, NVL(salary, 0)
FROM employee
WHERE employee_id = &&1)
AND employee_id <> &&1;
d)
SELECT *
FROM employee
WHERE (dept_id, salary) IN
(SELECT dept_id, salary)
FROM employee
WHERE employee_id = &1
AND salary IS NULL);
 
09) Click the Exhibit(s) button to examine the structures of the EMPLOYEE and TASK tables.
You need to produce a report containing all employees and all tasks. An employee must be included on the report even if he has no tasks assigned. All tasks, whether assigned to an employee or not, must also be included on the report.
 
Which SELECT statement should you use?
a)
SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date
FROM employee e, task t
WHERE e.employee_id = t.employee_id;
b)
SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date
FROM employee e, task t
WHERE e.employee_id (+) = t.employee_id;
c)
SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date
FROM employee e, task t
WHERE e.employee_id = t.employee_id (+);
d)
SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date
FROM employee e, task t
WHERE e.employee_id (+) = t.employee_id (+);
e) None of the options will produce the desired result.
 
10) Examine the structure of the employee table.
 
1z0-061 practice exams
Which CREATE TABLE statement should you use to create the employee table?
a)
CREATE TABLE employee (
employee_id NUMBER,
emp_lname VARCHAR2(25) NOT NULL,
emp_fname VARCHAR2(25),
dept_id NUMBER,
job_id NUMBER,
mgr_id NUMBER,
salary NUMBER(9,2),
hire_date DATE,
CONSTRAINT employee_id_pk PRIMARY KEY(employee_id));
b)
CREATE TABLE employee (
employee_id NUMBER,
emp_lname VARCHAR2(25) NOT NULL,
emp_fname VARCHAR2(25),
dept_id NUMBER,
job_id NUMBER,
mgr_id NUMBER,
salary NUMBER(9,2),
hire_date DATE,
CONSTRAINT employee_id_pk PRIMARY KEY(employee_id),
CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id));
c)
CREATE TABLE employee (
employee_id NUMBER,
emp_lname VARCHAR2(25) NOT NULL,
emp_fname VARCHAR2(25),
dept_id NUMBER,
job_id NUMBER,
mgr_id NUMBER,
salary NUMBER(9,2),
hire_date DATE,
CONSTRAINT employee_id_pk PRIMARY KEY(employee_id),
CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id),
CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id));
d)
CREATE TABLE employee (
employee_id NUMBER,
emp_lname VARCHAR2(25) NOT NULL,
emp_fname VARCHAR2(25),
dept_id NUMBER,
job_id NUMBER,
mgr_id NUMBER,
salary NUMBER(9,2),
hire_date DATE,
CONSTRAINT employee_id_pk PRIMARY KEY(employee_id),
CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id),
CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id),
CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id));

Solutions:

QUESTION: 01

Answer: a

QUESTION: 02

Answer: b

QUESTION: 03

Answer: d

QUESTION: 04

Answer: d

QUESTION: 05

Answer: a

QUESTION: 06

Answer: b, c

QUESTION: 07

Answer: b, c

QUESTION: 08

Answer: c

QUESTION: 09

Answer: e

QUESTION: 10

Answer: d

Note: If you find any typo or data entry error in these sample questions, we request you to update us by commenting on this page or write an email on feedback@oraclestudy.com

Rating: 3.5 / 5 (10 votes)