1Z1-051 by Oracle Actual Free Exam Q&As

Question 1

Examine the structure and data in the PRICE_LIST table:
Name Null Type
PROD_ID NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE 100 $234.55
101 $6,509.75
102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?

A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST; B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST; C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

Discussion 0

Correct Answer: C Vote an answer

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

Question 2

View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.

Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit
FROM orders o JOIN customers c
USING (customer_id)
WHERE o.order_total > c.credit_limit
FOR UPDATE
ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)

A. The locks are released only when a COMMIT or ROLLBACK is issued. B. It locks all the rows that satisfy the condition in the statement. C. The locks are released after a DML statement is executed on the locked rows. D. It locks only the columns that satisfy the condition in both the tables.

Discussion 0

Correct Answer: A,B Vote an answer

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

Question 3

Which SQL statement returns a numeric value?

A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP; B. SELECT TO_NUMBER(hire_date + 7) FROM EMP; C. SELECT sysdate-hire_date FROM EMP; D. SELECT ROUND(hire_date) FROM EMP;

Discussion 0

Correct Answer: C Vote an answer

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

Question 4

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.
You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:

Which statement is true regarding the above query?

A. It produces an error because the join order of the tables is incorrect. B. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement. C. It executes successfully but does not give the required result. D. It executes successfully and gives the required result.

Discussion 0

Correct Answer: C Vote an answer

Question 5

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

A. A constraint can be disabled even if the constraint column contains data B. All constraints can be defined at the column level as well as the table level C. A constraint is enforced only for the INSERT operation on a table D. A foreign key cannot contain NULL values E. A columns with the UNIQUE constraint can contain NULL values

Discussion 0

Correct Answer: A,E Vote an answer

Question 6

View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.

Evaluate the following SQL statements:

Which statement is true regarding the output of the above query?

A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not B. It gives the details of promos for which there have been no sales C. It gives details of all promos irrespective of whether they have resulted in a sale or not D. It gives the details of promos for which there have been sales

Discussion 0

Correct Answer: C Vote an answer

Question 7

Top N analysis requires _____ and _____. (Choose two.)

A. the use of rowid B. only an inline view C. an inline view and an outer query D. a GROUP BY clause E. an ORDER BY clause

Discussion 0

Correct Answer: C,E Vote an answer

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

Question 8

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?

A. WHERE prod_id LIKE '%_D123%' ESCAPE '_' B. WHERE prod_id LIKE '%_D123%' ESCAPE '%_' C. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_' D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'

Discussion 0

Correct Answer: D Vote an answer

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

Question 9

For which action can you use the TO_DATE function?

A. Convert any date to a character literal B. Convert any numeric literal to a date C. Convert any date literal to a date D. Format '10-JAN-99' to 'January 10 1999' E. Convert any character literal to a date

Discussion 0

Correct Answer: E Vote an answer

Question 10

Which one is a system privilege?

A. CREATE TABLE B. ALTER TABLE C. SELECT D. DELETE E. EXECUTE

Discussion 0

Correct Answer: A Vote an answer

Question 11

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

A. It can be used to access data from tables through equijoins as well as nonequijoins. B. It can be used to join tables that have columns with the same name and compatible data types. C. It can be used to restrict the number of columns used in a NATURAL join. D. It can be used to join a maximum of three tables.

Discussion 0

Correct Answer: B,C Vote an answer

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

Question 12

The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester?

A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end; B. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; C. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; D. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; E. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

Discussion 0

Correct Answer: A Vote an answer

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

Question 13

See the Exhibit and examine the structure of the PROMOSTIONS table:
Exhibit:

Which SQL statements are valid? (Choose all that apply.)

A. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions; B. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions; C. SELECT promo_id, DECODE(promo_cost, >10000, 'High', <10000, 'Low') "Range" FROM promotions; D. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;

Discussion 0

Correct Answer: A,B Vote an answer

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

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