Exam Code: 1Z0-051 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-051 Exam.
2021 Jun 1Z0-051 Study Guide Questions:
Q51. - (Topic 2)
Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVEPOINT C;
D. GRANT SELECT ON employees TO SCOTT;
E. ALTER TABLE employeesSET UNUSED COLUMN sal;
F. Select MAX(sal)FROM employeesWHERE department_id = 20;
Answer: D,E
Explanation:
D: GRANT is a DML operation which will cause an implicit commit
E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object.
Incorrect Answers A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction. B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.
C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction F:SELECT command is used to retrieve data. It does not complete a transaction.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle
Q52. - (Topic 2)
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?
A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00'
B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000'
C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00'
D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00'
Answer: D
Q53. - (Topic 1)
Examine the structure of the STUDENTS table:
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?
A. SELECT student_ id, marks, ROWNUM "Rank"
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99
AND course_id = 'INT_SQL'
ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID "Rank"
FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks;
C. SELECT student_id, marks, ROWNUM "Rank"
FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-
99'
AND course_id = 'INT_SQL'
ORDER BY marks DESC);
D. SELECT student_id, marks, ROWNUM "Rank”
FROM (SELECT student_id, marks
FROM students
WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’
AND course_id = ‘INT_SQL’
ORDER BY marks DESC)
WHERE ROWNUM <= 10 ;
E. SELECTstudent id, marks, ROWNUM “Rank”
FROM(SELECT student_id, marks
FROM students
ORDER BY marks)
WHEREROWNUM <= 10
ANDfinish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’
ANDcourse_id = ‘INT_SQL’;
Answer: D
Renovate oca exam 1z0-051:
Q54. - (Topic 1)
The STUDENT_GRADES table has these columns:
STUDENT_IDNUMBER(12)
SEMESTER_ENDDATE
GPANUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.
Which statement accomplish this?
A.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
B.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC
C.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
D.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
E.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
F.
SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC
Answer: F
Q55. - (Topic 2)
Which four are attributes of single row functions? (Choose four.)
A. cannot be nested
B. manipulate data items
C. act on each row returned
D. return one result per row
E. accept only one argument and return only one value
F. accept arguments which can be a column or an expression
Answer: B,C,D,F
Explanation:
manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression.
Incorrect Answer: Ais not single row attributes Efunctions can accept more than one argument, e.g NVL2
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-5
Q56. - (Topic 2)
Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?
A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.
B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
Answer: C
High quality oracle database 11g sql fundamentals 1 1z0-051:
Q57. - (Topic 1)
See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Exhibit:
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
Answer: B
Explanation:
Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE
TABLE statement and the AS subquery option.
CREATE TABLE table
[(column, column...)]
AS subquery;
Match the number of specified columns to the number of subquery columns.
Define columns with column names and default values.
Guidelines
The table is created with the specified column names, and the rows retrieved by the
SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of
columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the
column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new
table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY
column will not pass the NOT NULL feature to the new column. Any other constraint rules
are not passed to the new table. However, you can add constraints in the column definition.
Q58. - (Topic 2)
Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
A. DELETE can be used to remove only rows from only one table at a time.
B. DELETE can be used to remove only rows from multiple tables at a time.
C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
D. DELETE can be used to remove data from specific columns as well as complete rows.
E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.
Answer: A,E
Explanation:
Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed. The Transaction Control Statements A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables: a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT Explicit Transaction Control Statements You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements. Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.
untitled
Q59. - (Topic 2)
You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?
A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
Answer: B
Explanation: function MAX(column_name)
Incorrect Answer:
Ainvalid statement
Cinner query return more than one line
Dcolumn maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
Q60. - (Topic 2)
Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:
Which two expressions give the same output? (Choose two.)
A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price, min_price), 0)
C. NVL2(COALESCE(list_price, min_price), min_price, 0)
D. COALESCE(NVL2(list_price, list_price, min_price), 0)
Answer: B,D
Explanation:
Using the COALESCE Function
.
The advantage of the COALESCE function over the NVL function is that the COALESCE
function can take multiple alternate values.
.
If the first expression is not null, the COALESCE function returns that expression;
otherwise, it does a COALESCE of the remaining expressions.
Using the COALESCE Function
The COALESCE function returns the first non-null expression in the list.
Syntax
COALESCE (expr1, expr2, ... exprn) In the syntax:
.
expr1 returns this expression if it is not null
.
expr2 returns this expression if the first expression is null and this expression is not null
.
exprn returns this expression if the preceding expressions are null Note that all expressions must be of the same data type.