Top Quality of 1Z0-051 rapidshare materials and questions for Oracle certification for IT examinee, Real Success Guaranteed with Updated 1Z0-051 pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!
2021 Jun 1Z0-051 Study Guide Questions:
Q141. - (Topic 2)
The DBA issues this SQL command:
CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. No privileges.
B. Only the SELECT privilege.
C. Only the CONNECT privilege.
D. All the privileges of a default user.
Answer: A
Explanation:
There are no privileges for the user Scott at this point. They are not added themselves to
the user immediately after creation. The DBA needs to grant all privileges explicitly.
Incorrect Answers
B:There are no privileges for the user Scott at this point. SELECT privilege needs to be
added to the user Scott.
C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be
added to the user Scott.
D:There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351
Chapter 8: User Access in Oracle
Q142. - (Topic 1)
Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
Answer: A,D,F
Explanation:
A: The TRUNCATE TABLE Statement releases storage space used by the table,
D: Can not rollback the deletion of rows after the statement executes,
F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.
Incorrect Answer: Cis not true Dis not true Eis not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18
Q143. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A.
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000'
B.
SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'
C.
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
D.
SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
Answer: A,B
Avant-garde oca 1z0-051 dumps:
Q144. - (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
Q145. - (Topic 2)
Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?
A.
SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",
SUM(DECODE(SUBSTR
(promo_begin_date,8),'99',1,0)) "1999"
FROM promotions;
B.
SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0
END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE
0 END) "2000"
FROM promotions;
C.
SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
D.
SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1,
0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,
0)) "2000"
FROM promotions;
Answer: A
Q146. - (Topic 2)
Top N analysis requires _____ and _____. (Choose two.)
A. the use of rowid
B. a GROUP BY clause
C. an ORDER BY clause
D. only an inline view
E. an inline view and an outer query
Answer: C,E
Explanation:
The correct statement for Top-N Analysis SELECT [coloumn_list], ROWNUM FROM (SELECT [coloumn_list]
FROM table
ORDER BY Top-N_coloumn)
WHERE ROWNUM <= N;
Incorrect Answer:
AROWID is not require
BGROUP BY clause is not require
DMust have inline view and outer query.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-23
Approved 1z0-051 books:
Q147. - (Topic 2)
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
A. DROP emp_dept_vu;
B. DELETE emp_dept_vu;
C. REMOVE emp_dept_vu;
D. DROP VIEW emp_dept_vu;
E. DELETE VIEW emp_dept_vu;
F. REMOVE VIEW emp_dept_vu;
Answer: D
Explanation:
DROP VIEW viewname;
Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20
Q148. - (Topic 2)
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C. 50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Answer: C
Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13
Q149. - (Topic 1)
View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.
You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)
A.
SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo'
B.
SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo'
C.
SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
AND c.cust_city='Tokyo'
D.
SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM
products p JOIN sales s
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo'
Answer: A,C
Q150. - (Topic 1)
Examine the structure of the EMPLOYEES table:
Which INSERT statement is valid?
A.
INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’);
B.
INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’);
C.
INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’));
D.
INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);
Answer: D
Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format