The Oracle professionals potentially have being a specialist inside it area should also get to the top regarding ability and also achievement, so they must take part in diverse Oracle qualification exams. Oracle 1Z0-051 named Oracle Database: SQL Fundamentals I is stepping-stone to examine the candidates information and also capability inside a relevant area of work. Actualtests Oracle 1Z0-051 on the web practice assessments can guarantee that you can to handle these very skilled and also qualified functions. Our own 1Z0-051 practice examination contains the genuine answers and questions, ensuring an individual complete the actual 1Z0-051 examination together with best levels.
2021 Jun 1Z0-051 testing engine
Q31. - (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.
Q32. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.
In PRODUCT table, PDTNO is the primary key.
In COMPONENT table, COMPNO is the primary key.
In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno,pdtname, compno,compname FROM product _____________ pdt_comp USING (pdtno) ____________ component USING(compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?
A. JOIN; JOIN
B. FULL OUTER JOIN; FULL OUTER JOIN
C. RIGHT OUTER JOIN; LEFT OUTER JOIN
D. LEFT OUTER JOIN; RIGHT OUTER JOIN
Answer: C
Q33. - (Topic 1)
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.
Evaluate the following SQL statements:
Which statement is true regarding the output of the above query?
A. It gives details of product IDs that have been sold irrespective of whether they had a
promo or not
B. It gives the details of promos for which there have been no sales
C. It gives the details of promos for which there have been sales
D. It gives details of all promos irrespective of whether they have resulted in a sale or not
Answer: D
Q34. - (Topic 2)
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.
Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = 'Roberts' AND
credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because two tables cannot be used in a single UPDATE statement.
B. It would not execute because the SELECT statement cannot be used in place of the table name.
C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
Answer: C
Explanation:
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.
Q35. - (Topic 1)
Examine the structure of the EMPLOYEES table:
Which UPDATE statement is valid?
A.
UPDATE employees
SET first_name = ‘John’
SET last_name = ‘Smith’
WHERE employee_id = 180;
B.
UPDATE employees
SET first_name = ‘John’,
SET last_name = ‘Smoth’
WHERE employee_id = 180;
C.
UPDATE employee
SET first_name = ‘John’
AND last_name = ‘Smith’
WHERE employee_id = 180;
D.
UPDATE employee
SET first_name = ‘John’, last_name = ‘Smith’
WHERE employee_id = 180;
Answer: D
Up to date oracle database 11g sql fundamentals 1 1z0-051 pdf:
Q36. - (Topic 2)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
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 c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.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);
Answer: A
Explanation:
The correct statement for MERGE is MERGE INTO table_name Incorrect Answer: BWrong statement with the keyword EXISTS CWrong statement with the keyword EXISTS DWrong statement on the MERGE new_employees
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
Q37. - (Topic 1)
See the Exhibit and examine the structure of the CUSTOMERS table:
Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
A.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit, cust_city;
B.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;
C.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D.
SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');
Answer: C
Explanation:
Creating Groups of Data: GROUP BY Clause Syntax You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression Specifies the columns whose values determine the basis for grouping rows Guidelines
.
If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
.
Using a WHERE clause, you can exclude rows before dividing them into groups.
.
You must include the columns in the GROUP BY clause.
.
You cannot use a column alias in the GROUP BY clause.
Q38. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display the category with the maximum number of items. You issue the following query:
SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?
A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the subquery does not have a GROUP BY clause.
D. It generates an error because = is not valid and should be replaced by the IN operator.
Answer: C
Q39. - (Topic 2)
You need to create a table named ORDERS that contain four columns:
1.
an ORDER_ID column of number data type
2.
a CUSTOMER_ID column of number data type
3.
an ORDER_STATUS column that contains a character data type
4.
a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead.
Which statement accomplishes this?
A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
B. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);
F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);
Answer: B
Explanation: Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.
Q40. - (Topic 2)
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?
A. SELECT last_name, department_name, location_id FROM employees , departments ;
B. SELECT employees.last_name, departments.department_name,
departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
Answer: D
Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8