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

Question 1

View the Exhibit and examine the description for the PRODUCTS and SALES table.
PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?

A. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );

B. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);

C. DELETE FROM products WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id - 3*365 = SYSDATE );

D. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );

Correct Answer: B

Question 2

See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)

A. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;

B. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;

C. SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;

D. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;

Correct Answer: B,D

Question 3

Which three statements are true regarding sub queries? (Choose three.)

A. Only one column or expression can be compared between the main query and subqeury

B. Sub queries can contain GROUP BY and ORDER BY clauses

C. Sub queries can contain ORDER BY but not the GROUP BY clause

D. Main query and sub query must get data from the same tables

E. Main query and sub query can get data from different tables

F. Multiple columns or expressions can be compared between the main query and sub query

Correct Answer: B,E,F

Question 4

Which four are attributes of single row functions? (Choose four.)

A. cannot be nested

B. manipulate data items

C. accept only one argument and return only one value

D. accept arguments which can be a column or an expression

E. act on each row returned

F. return one result per row

Correct Answer: B,D,E,F

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

Question 5

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

A. Both primary key and foreign key constraints can be defined at both column and table levels.

B. Only the primary key can be defined at the column and table levels.

C. The foreign key and parent table primary key must have the same name.

D. A table can have only one primary key but multiple foreign keys.

E. A table can have only one primary key and one foreign key.

Correct Answer: A,D

Question 6

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 OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);

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

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

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

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

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

Correct Answer: C

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

Question 7

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

A. A table name can be of any length.

B. The same table name can be used in different schemas in the same database.

C. A table can have any number of columns.

D. A column that has a DEFAULT value cannot store null values.

E. A table and a synonym can have the same name in the same schema.

F. A table and a view can have the same name in the same schema.

Correct Answer: B,E

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

Question 8

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

A. Complex views can be created only on multiple tables that exist in the same schema.

B. Complex views can be created on multiple tables that exist in the same or different schemas.

C. Views can be created only from tables.

D. Only simple views can use indexes existing on the underlying tables.

E. Views can be created from tables or other views.

F. Both simple and complex views can use indexes existing on the underlying tables.

Correct Answer: B,E,F

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

Question 9

Which statement is true regarding the UNION operator?

A. Names of all columns must be identical across all SELECT statements

B. By default, the output is not sorted

C. NULL values are not ignored during duplicate checking

D. The number of columns selected in all SELECT statements need to be the same

Correct Answer: C

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

Question 10

Examine the structure proposed for the TRANSACTIONS table:

Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)

A. The TRANS_VALIDITY column would have a maximum size of one character.

B. The TRANS_VALIDITY column would give an error.

C. The CUST_STATUS column would give an error.

D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.

E. The CUST_STATUS column would store exactly one character.

F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, andfractions of seconds.

Correct Answer: B,E

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

Question 11

Which two statements are true regarding the USING clause in table joins?(Choose two.)

A. It can be used to restrict the number of columns used in a NATURAL join.

B. It can be used to access data from tables through equijoins as well as nonequijoins.

C. It can be used to join a maximum of three tables.

D. It can be used to join tables that have columns with the same name and compatible data types.

Correct Answer: A,D

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

Question 12

Which is the valid CREATE [TABLE statement?

A. CREATE TABLE emp*123 (emp_no NUMBER(4));

B. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);

C. CREATE TABLE 9emp$# (emp_no NUMBER(4));

D. CREATE TABLE emp9$# (emp_no NUMBER(4));

Correct Answer: D

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

Question 13

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 promo category 'TV'.

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

Correct Answer: D

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

Latest Update

Useful Links

Contact Us