Oracle Database SQL Expert - 1Z1-047 Exam Practice Test
Question 1
View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables. You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE
unit_price*quantity > 50000 NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?
You executed the following SQL statement:
SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE
unit_price*quantity > 50000 NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?
Correct Answer: A
Question 2
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
Correct Answer: A
Question 3
Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
Correct Answer: A
Question 4
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?
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?
Correct Answer: B
Question 5
You need to create a table for a banking application with the following considerations:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and
subtracted with 3) date type data without using the conversion functions.
4) The maximum period of the credit provision in the application is 30 days.
5) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and
subtracted with 3) date type data without using the conversion functions.
4) The maximum period of the credit provision in the application is 30 days.
5) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
Correct Answer: C
Question 6
View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve from the table the number of products having LIST_PRICE
as NULL?
Which SQL statement would retrieve from the table the number of products having LIST_PRICE
as NULL?
Correct Answer: A
Question 7
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
Correct Answer: D
Question 8
View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee
whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
You want to generate a hierarchical report for all the employees who report to the employee
whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
Correct Answer: C,D,E
Question 9
Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
Why would the ORD_ITEMS table not get created?
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
Why would the ORD_ITEMS table not get created?
Correct Answer: B
Question 10
View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS
WHERE order_total = 1000 WITH CHECK OPTION)
VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS
WHERE order_total = 1000 WITH CHECK OPTION)
VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
Correct Answer: D
