1Z0-051 | All About 100% Correct 1Z0-051 braindumps
Q141. - (Topic 2)
The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE ''
B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'
C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "";
D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_'
Answer: A
Explanation: ESCAPE identifier to search for the _ symbol
Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13
Q142. - (Topic 2)
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.
Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"
FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost "code"
FROM promotions
ORDER BY 1;
Which statement is true regarding the execution of the above queries?
A. Only the first query executes successfully.
B. Only the second query executes successfully.
C. Both queries execute successfully but give different results.
D. Both queries execute successfully and give the same result.
Answer: B
Explanation:
Note: You cannot use column alias in the WHERE clause.
Q143. - (Topic 1)
The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE
123456 152525.99
You issue the following query:
SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*')
FROM products
WHERE prod_id = 123456;
What would be the outcome?
A. 152526****
B. **152525.99
C. 152525**
D. an error message
Answer: A
Explanation:
The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.
Q144. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.
Evaluate the following SQL statement: Exhibit:
Which statement is true regarding the execution of the above UPDATE statement?
A. It would execute and restrict modifications to only the column specified in the SELECT statement
B. It would not execute because two tables cannot be used in a single UPDATE statement
C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
D. It would not execute because the SELECT statement cannot be used in place of the table name
Answer: A
Q145. - (Topic 2)
The PRODUCTS table has these columns:
PRODUCT_ID NUMBER(4)
PRODUCT_NAME VARCHAR2(45)
PRICE NUMBER(8,2)
Evaluate this SQL statement:
SELECT *
FROM PRODUCTS
ORDER BY price, product_name;
What is true about the SQL statement?
A. The results are not sorted.
B. The results are sorted numerically.
C. The results are sorted alphabetically.
D. The results are sorted numerically and then alphabetically.
Answer: D
Explanation:
the result is sort by price which is numeric and follow by product_name which is alphabetically.
Incorrect Answer: Athe results are sorted Bthe results are sorted with alphabetically as well Cthe results are sorted with numerically as well
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-2
Q146. - (Topic 1)
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city='Singapore'):
Which statement is true regarding the above query if one of the values generated by the sub query is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the sub query.
D. It ignores the NULL value and generates output for the other values produced by the sub query.
Answer: C
Q147. - (Topic 2)
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;
B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Answer: C
Explanation: Explanation: For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed
Incorrect Answer: Aper semester condition is not included Bresult would not display the highest gpa value Dinvalid syntax error Einvalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
Q148. - (Topic 1)
You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?
A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Month. Year') FROM DUAL:
B. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Month. Year') FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Month. Year')) FROM DUAL:
Answer: A
Q149. - (Topic 1)
User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.
Which SQL statement can Mary use to accomplish that task?
A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;
B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);
C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;
D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER;
E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu;
F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS;
Answer: C
Explanation:
The general syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM synonym FOR object;
Q150. - (Topic 2)
Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses.
B. Main query and subquery can get data from different tables.
C. Main query and subquery must get data from the same tables.
D. Subqueries can contain ORDER BY but not the GROUP BY clause.
E. Only one column or expression can be compared between the main query and subquery.
F. Multiple columns or expressions can be compared between the main query and subquery.
Answer: A,B,F
Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following
are required clauses:
A SELECT list
A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.