Attention please! Here is the shortcut to pass your Latest 1Z0-051 vce exam! Get yourself well prepared for the Oracle Database Application Development Newest 1Z0-051 pdf dumps Oracle Database 11g : SQL Fundamentals I exam is really a hard job. But don’t worry! We We, provides the most update 1Z0-051 new questions. With We latest latest 1Z0-051 dumps, you’ll pass the Oracle Database Application Development May 15,2022 Hotest 1Z0-051 vce Oracle Database 11g : SQL Fundamentals I exam in an easy way
We Geekcert has our own expert team. They selected and published the latest 1Z0-051 preparation materials from Official Exam-Center.
The following are the 1Z0-051 free dumps. Go through and check the validity and accuracy of our 1Z0-051 dumps.Free sample questions of 1Z0-051 free dumps are provided here. All the following questions are from the latest real 1Z0-051 dumps.
Question 1:
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which MERGE statement is valid?
A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
B. name = e.first_name ||\’,\’|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||\’, \’||e.last_name);
C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
D. name = e.first_name ||\’,\’|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||\’, \’||e.last_name);
E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
F. name = e.first_name ||\’,\’|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||\’, \’||e.last_name);
G. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
H. name = e.first_name ||\’,\’|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||\’, \’||e.last_name);
Correct Answer: A
Explanation: this is the correct MERGE statement syntax Incorrect answer: B it should MERGE INTO table_name C it should be WHEN MATCHED THEN D it should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
Question 2:
Which two statements are true regarding constraints? (Choose two.)
A. A constraint can be disabled even if the constraint column contains data
B. A constraint is enforced only for the INSERT operation on a table
C. A foreign key cannot contain NULL values
D. All constraints can be defined at the column level as well as the table level
E. A columns with the UNIQUE constraint can contain NULL values
Correct Answer: AE
Question 3:
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value
Correct Answer: B
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
Question 4:
View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format: CATEGORIES 5MP Digital Photo Camera\’s category is Photo Y Box\’s category is Electronics Envoy Ambassador\’s category is Hardware Which two queries would give the required output? (Choose two.)
A. SELECT prod_name || q\’\’\’s category is \’ || prod_category CATEGORIES FROM products;
B. SELECT prod_name || q\'[\’s ]\’category is \’ || prod_category CATEGORIES FROM products;
C. SELECT prod_name || q\’\\’s\\’ || \’ category is \’ || prod_category CATEGORIES FROM products;
D. SELECT prod_name || q\’\’ || \’category is \’ || prod_category CATEGORIES FROM products;
Correct Answer: CD
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark
in the character string Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols
that enclose or wrap any other character literal. These character- enclosing symbols could have been anything other than single quotation marks. Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to
choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces},
[squarebrackets], or . Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character The syntax of the alternative quote operator is as follows:
q\’delimiter\’character literal which may include the single quotes delimiter\’ where delimiter can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q\'[ Department\’s Manager Id: ]\’
|| manager_id
AS “Department and Manager”
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter. You can choose any convenient
delimiter, single-byte or multibyte, or any of the following character pairs:
[ ], { }, ( ), or .
In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between
the brackets delimiters is interpreted as a literal character string.
Question 5:
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on \’TV\’ and \’internet\’ that ended in the time interval 15th March \’00 to 15th October \’00. Which two queries would give the required result? (Choose two.)
A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN (\’TV\’, \’internet\’) AND promo_end_date BETWEEN \’15-MAR-00\’ AND \’15-OCT-00\’;
B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = \’TV\’ OR promo_category =\’internet\’ AND promo_end_date >=\’15- MAR-00\’ OR promo_end_date <=\'15-OCT-00\';
C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN \’TV\’ AND \’internet\’) AND (promo_end_date IN (\’15-MAR-00\’,\’15-OCT-00\’));
D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = \’TV\’ OR promo_category =\’internet\’) AND (promo_end_date >=\’15-MAR- 00\’ AND promo_end_date <=\'15-OCT-00\');
Correct Answer: AD
Question 6:
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?
A. SELECT COUNT(*) FROM employees WHERE last_name=\’Smith\’;
B. SELECT COUNT (dept_id) FROM employees WHERE last_name=\’Smith\’;
C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name=\’Smith\’;
D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name=\’Smith\’;
E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name=\’Smith\’;
Correct Answer: D
Question 7:
Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN (\’CREDIT\’, \’CASH\’)), PROD_ID NUMBER REFERENCES PRODUCTS (PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)
A. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE
Correct Answer: AF
Explanation: Index exist for UNIQUE and PRIMARY KEY constraints
Incorrect answer: B ORDER_ID is neither UNIQUE nor PRIMARY KEY C STATUS is neither UNIQUE nor PRIMARY KEY D PROD_ID is neither UNIQUE nor PRIMARY KEY E ORD_TOTAL is neither UNIQUE nor PRIMARY KEY
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-15
Question 8:
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;
Correct Answer: C
For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed
Incorrect answer: A per semester condition is not included B result would not display the highest gpa value D invalid syntax error E invalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
Question 9:
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
B. dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
C. 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;
D. 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);
E. 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;
Correct Answer: B
Explanation: function MAX(column_name)
Incorrect answer:
A invalid statement
C inner query return more than one line
D column maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
Question 10:
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
Correct Answer: A
when a user is created, by default no privilege is granted
Incorrect answer: B SELECT is not grant C CONNECT is not grant D default profile is grant by default not privilege. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-6
Question 11:
View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is \’married\’
C. finding the average credit limit of male customers residing in \’Tokyo\’ or \’Sydney\’
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city \’Tokyo\’
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Correct Answer: DE
Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another.
Use of a Subquery Result Set for Comparison Purposes
Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements:
select avg(salary) from employees;
select last_name from employees where salary < result_of_previous_query ; Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees); In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison
with the rows retrieved by the parent query. The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in (select distinct(department_id) from employees);
Question 12:
You need to produce a report where each customer\’s credit limit has been incremented by $1000. In the output, the customer\’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit.
The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit 1000 “New Credit Limit” FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit 1000 AS New Credit Limit FROM customers;
C. SELECT cust_last_name AS “Name”, cust_credit_limit 1000 AS “New Credit Limit” FROM customers;
D. SELECT INITCAP(cust_last_name) “Name”, cust_credit_limit 1000 INITCAP(“NEW CREDIT LIMIT”) FROM customers;
Correct Answer: C
A column alias:
-Renames a column heading
-Is useful with calculations
-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.
Question 13:
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.
Correct Answer: BE
Question 14:
View the Exhibit and examine the structure of the SALES and PRODUCTS tables.
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.
Evaluate the following query:
SQL>SELECT p.prod_id, COUNT(s.prod_id)
FROM products p _____________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)
A. JOIN
B. FULL OUTER JOIN
C. LEFT OUTER JOIN
D. RIGHT OUTER JOIN
Correct Answer: BC
Question 15:
View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_ begin_date))); What would be the outcome of the above SQL statement?
A. It displays prod IDs in the promo with the lowest cost.
B. It displays prod IDs in the promos with the lowest cost in the same time interval.
C. It displays prod IDs in the promos with the highest cost in the same time interval.
D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.
Correct Answer: D
