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 May 1Z0-051 Study Guide Questions:
Q171. - (Topic 2)
View the Exhibits and examine PRODUCTS and SALES tables.
You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?
A. The statement executes successfully and produces the required output.
B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
Answer: A
Q172. - (Topic 1)
View the Exhibit and examine the data in the PROMOTIONS table.
You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)
A.
SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount'
B.
SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount'
C.
SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount'
D.
SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount'
Answer: A,D
Q173. - (Topic 2)
What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges.
B. It is required syntax for object privileges.
C. It allows privileges on specified columns of tables.
D. It is used to grant an object privilege on a foreign key column.
E. It allows the grantee to grant object privileges to other users and roles.
Answer: E Explanation:
The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle
Avant-garde 1z0-051 oracle database 11g sql fundamentals i pdf:
Q174. - (Topic 2)
Which two statements are true regarding constraints? (Choose two.)
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table
levels.
Answer: B,E
Q175. - (Topic 1)
View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.
What would be the outcome if all the parentheses are removed from the above statement?
A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Answer: B
Q176. - (Topic 1)
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result.
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
Answer: C
Explanation:
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further
restricting the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for
which the specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
1.
Rows are grouped.
2.
The group function is applied to the group.
3.
The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you
place the GROUP BY clause first because it is more logical. Groups are formed and group
functions are calculated before the HAVING clause is applied to the groups in the SELECT
list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.
Realistic 1z0-051 oracle database 11g sql fundamentals i:
Q177. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?
A.
SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
B.
SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
C.
SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
D.
SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);
Answer: A
Explanation:
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value
returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.
Q178. - (Topic 1)
Which two statements are true regarding the COUNT function? (Choose two.)
A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types
Answer: A,C
Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT
statement, including duplicate rows and rows containing null values in any of the columns.
If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of
rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by
expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the
column identified by expr.
Q179. - (Topic 1)
The SQL statements executed in a user session as follows: Exhibit:
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
A. Both the DELETE statements and the UPDATE statement are rolled back
B. The rollback generates an error
C. Only the DELETE statements are rolled back
D. Only the seconds DELETE statement is rolled back
E. No SQL statements are rolled back
Answer: B,E
Q180. - (Topic 2)
View the Exhibit and examine the structure and data in the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT AVG(inv_date ) FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice;
Answer: B,D
Explanation:
Using the AVG and SUM Functions You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives Using the MIN and MAX Functions You can use the MAX and MIN functions for numeric, character, and date data types. The example in the slide displays the most junior and most senior employees.