1Z1-051 by Oracle Actual Free Exam Questions And Answers

Question 1

A data manipulation language statement _____.

A. modifies the structure and data in a table

B. modifies the data but not the structure of a table

C. modifies the structure but not the data of a table

D. completes a transaction on a table

Discussion 0

Correct Answer: B Vote an answer

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

Question 2

View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.

In PRODUCT table, PDTNO is the primary key.
In COMPONENT table, COMPNO is the primary key.
In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno,pdtname, compno,compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING(compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

A. JOIN; JOIN

B. RIGHT OUTER JOIN; LEFT OUTER JOIN

C. FULL OUTER JOIN; FULL OUTER JOIN

D. LEFT OUTER JOIN; RIGHT OUTER JOIN

Discussion 0

Correct Answer: B Vote an answer

Question 3

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

A. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

E. ALTER TABLE students ADD PRIMARY KEY student_id;

Discussion 0

Correct Answer: 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 structure of the PROMOTIONS table.

Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?

A. It produces an error because the subquery gives an error.

B. It produces an error because subqueries cannot be used with the CASE expression.

C. It shows COST_REMARK for all the promos in the table.

D. It shows COST_REMARK for all the promos in the promo category 'TV'.

Discussion 0

Correct Answer: C Vote an answer

Question 5

Which statements are true regarding single row functions? (Choose all that apply.)

A. TRUNC : can be used with NUMBER and DATE values

B. MOD : returns the quotient of a division

C. TRIM : can be used to remove all the occurrences of a character from a string

D. CONCAT : can be used to combine any number of values

E. SYSDATE : returns the database server current date and time

F. INSTR : can be used to find only the first occurrence of a character in a string

Discussion 0

Correct Answer: A,E Vote an answer

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

Question 6

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which MERGE statement is valid?

A. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

B. 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 value S(e.employee_id, e.first_name ||', '||e.last_name);

D. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET

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

F. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

G. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);

H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);

Discussion 0

Correct Answer: D Vote an answer

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

Question 7

Which three statements/commands would cause a transaction to end? (Choose three.)

A. CREATE

B. ROLLBACK

C. COMMIT

D. SELECT

E. SAVEPOINT

Discussion 0

Correct Answer: A,B,C Vote an answer

Question 8

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;

A. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

B. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

C. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

D. CREATE SYNONYM ord FOR orders; This command is issued by OE.

Discussion 0

Correct Answer: B Vote an answer

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

Question 9

You need to create a table named ORDERS that contain four columns:
1.an ORDER_ID column of number data type
2.a CUSTOMER_ID column of number data type
3.an ORDER_STATUS column that contains a character data type
4.a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today's date should be used instead.
Which statement accomplishes this?

A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);

B. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);

C. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);

D. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);

E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);

F. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);

Discussion 0

Correct Answer: E Vote an answer

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

Question 10

Examine the structure of the INVOICE table:
Exhibit:

Which two SQL statements would execute successfully? (Choose two.)

A. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice;

B. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;

C. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;

D. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;

Discussion 0

Correct Answer: B,D Vote an answer

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

Question 11

Which two are true about aggregate functions? (Choose two.)

A. You cannot group the rows of a table by more than one column while using aggregate functions.

B. You can use aggregate functions in any clause of a SELECT statement.

C. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.

D. You can use aggregate functions on a table, only by grouping the whole table as one single group.

E. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

F. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

Discussion 0

Correct Answer: B,E Vote an answer

Question 12

Which statement correctly describes SQL and /SQL*Plus?

A. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.

B. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.

C. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.

D. Both SQL and /SQL*plus allow manipulation of values in the database.

Discussion 0

Correct Answer: D Vote an answer

Question 13

View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.
Evaluate the following query: SQL>SELECT p.prod_id, COUNT(s.prod_id)
FROM products p _____________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

A. FULL OUTER JOIN

B. RIGHT OUTER JOIN

C. LEFT OUTER JOIN

D. JOIN

Discussion 0

Correct Answer: A,C Vote an answer

Question 14

Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

A. ORDER_ID

B. SER_NO

C. PROD_ID

D. composite index on ORDER_ID and ORDER_DATE

E. STATUS

F. ORD_TOTAL

Discussion 0

Correct Answer: B,D Vote an answer

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

Oracle Database: SQL Fundamentals I - 1Z1-051 Exam Practice Test

Contact Us

Useful Links

Latest Updated