Oracle 1Z0-051 Study Guides 2021

NEW QUESTION 1
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 claus
• B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT lis
• 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 tabl
• 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 tabl

Answer: C

NEW QUESTION 2
You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

• A. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount > 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
• B. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount > 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
• C. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount >= 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
• D. CREATE TABLE ord_details (ord_id NUMBER(2), ord_date DATE NOT NULL DEFAULT SYSDATE, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount >= 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));

Answer: C

NEW QUESTION 3
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

NEW QUESTION 4
Examine the statement:
GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION;
Which two are true? (Choose two.)

• A. MANAGER must be a rol
• B. It allows the MANAGER to pass the specified privileges on to other user
• C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES tabl
• D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES tabl
• E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES tabl
• F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES tabl

Answer: BE

Explanation:
GRANT ROLE to ROLE/USER
Incorrect Answer: ARole can be grant to user CCreate table privilege is not granted DExecute privilege is not granted FDelete privilege is not granted
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

NEW QUESTION 5
EMPLOYEES and DEPARTMENTS data: EMPLOYEES

DEPARTMENTS

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary
FROM employees
WHERE emp_name = 'Smith')
WHERE job_id = 'IT_ADMIN';
What happens when the statement is executed?

• A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
• B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
• C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
• D. The statement fails because there is more than one row matching the employee name Smit
• E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES tabl
• F. The statement fails because there is no 'Administration' department in the DEPARTMENTS tabl

Answer: D

Explanation:
'=' is use in the statement and sub query will return more than one row.
Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A. The Update statement will fail no update was done.
B. The update statement will fail no update was done.
C. The update statement will fail no update was done.
E. The update statement will fail but not due to job_it='IT_ADMIN'
F. The update statement will fail but not due to department_id='Administration'
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12

NEW QUESTION 6
Which three statements/commands would cause a transaction to end? (Choose three.)

• A. COMMIT
• B. SELECT
• C. CREATE
• D. ROLLBACK
• E. SAVEPOINT

Answer: ACD

NEW QUESTION 7
What is true about the WITH GRANT OPTION clause?

• A. It allows a grantee DBA privilege
• B. It is required syntax for object privilege
• C. It allows privileges on specified columns of table
• D. It is used to grant an object privilege on a foreign key colum
• E. It allows the grantee to grant object privileges to other users and role

Answer: AE

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

NEW QUESTION 8
The PRODUCTS table has the following structure:

Evaluate the following two SQL statements:

Which statement is true regarding the outcome?

• A. Both the statements execute and give the same result
• B. Both the statements execute and give different results
• C. Only the second SQL statement executes successfully
• D. Only the first SQL statement executes successfully

Answer: B

Explanation:
Using the NVL2 Function The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null

NEW QUESTION 9
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
• B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
• D. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
• E. SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;

Answer: ACD

NEW QUESTION 10
View the Exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?

• A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET';
• B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');
• C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
• D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

Answer: C

NEW QUESTION 11
Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue the following command:
SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date,'yyyy');
Which statement is true regarding the outcome?

• A. It executes successfully and gives the correct outpu
• B. It gives an error because the TO_CHAR function is not vali
• C. It executes successfully but does not give the correct outpu
• D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY claus

Answer: D

NEW QUESTION 12
Which two statements are true regarding tables? (Choose two.)

• A. A table name can be of any lengt
• B. A table can have any number of column
• C. A column that has a DEFAULT value cannot store null value
• D. A table and a view can have the same name in the same schem
• E. A table and a synonym can have the same name in the same schem
• F. The same table name can be used in different schemas in the same databas

Answer: EF

Explanation:
Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.

NEW QUESTION 13
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

• A. INSERT INTO orders VALUES (l.'10-mar-2007 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
• B. INSERT INTO orders (order_id.order_date.order_mod
• C. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000):
• D. INSERT INTO(SELECT o.order_i
• E. o.order_date.o.order_modex.customer_i
• F. o.ordertotal FROM orders
• G. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
• H. INSERT INTO orders (order_id.order_date.order_mod
• I. (SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600).order_total) VALUES(l.'10-mar-2007 'direct'. &customer_i
• J. 1000):

Answer: A

NEW QUESTION 14
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL;
What is the outcome?

• A. It executes successfully and returns the correct resul
• B. It executes successfully but does not return the correct resul
• C. It generates an error because TO_CHAR should be replaced with TO_DAT
• D. It generates an error because rrrr should be replaced by rr in the format strin
• E. It generates an error because fm and double quotation marks should not be used in the format strin

Answer: A

Explanation:
.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
.
LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

NEW QUESTION 15
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)

• A. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;
• B. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;
• C. SELECT custname, grade FROM customers, grades WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers) AND cust_credit_limit BETWEEN startval AND endval;
• D. SELECT custname, grade FROM customers , grades WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers) AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Answer: BC

NEW QUESTION 16
Examine the description of the CUSTOMERS table:

The CUSTOMER_ID column is the primary key for the table.
Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

• A. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’);
• B. SELECT city_address, COUNT (*) FROMcustomers WHERE city address IN ( ‘Los Angeles’, ‘San Fransisco’) GROUP BY city_address;
• C. SELECT city_address, COUNT(customer_id) FROMcustomers WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’) GROUP BYcity_address, customer_id;
• D. SELECT city_address, COUNT (customer_id) FROM customers GROUP BY city_address IN ( ‘Los Angeles’, ‘San Fransisco’);

Answer: B

Explanation:
Not C: The customer ID in the GROUP BY clause is wrong

NEW QUESTION 17
Which two statements are true regarding single row functions? (Choose two.)

• A. They can be nested only to two levels
• B. They always return a single result row for every row of a queried table
• C. Arguments can only be column values or constant
• D. They can return a data type value different from the one that is referenced
• E. They accept only a single argument

Answer: BD

Explanation:
A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

NEW QUESTION 18
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)

• A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
• B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
• C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
• D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
• E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

Answer: AC

NEW QUESTION 19
Which statement is true regarding the UNION operator?

• A. The number of columns selected in all SELECT statements need to be the same
• B. Names of all columns must be identical across all SELECT statements
• C. By default, the output is not sorted
• D. NULL values are not ignored during duplicate checking

Answer: A

Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

NEW QUESTION 20
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1.
The promo name should not begin with 'T' or 'N'.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?

• A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
• C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')

Answer: C