Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 51:

  View the Exhibit and examine the structure of the CUSTOMERS table. In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'. You issue the following query:

  SQL> SELECT LOWER(REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM CUSTOMERS WHERE LOWER(cust_last_name) LIKE 'a%n';

  What would be the outcome?

  A. 'Oder' and 'Aus'
  B. an error because the TRIM function specified is not valid
  C. an error because the LOWER function specified is not valid
  D. an error because the REPLACE function specified is not valid
  B. an error because the TRIM function specified is not valid

• Question 52:

  Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

  Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer

  You need to display customers' second names where the second name starts with "Mc" or "MC." Which query gives the required output?

  A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
  B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
  C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
  D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
  B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';

• Question 53:

  The EMPLOYEES table has these columns:

  LAST NAME VARCHAR2(35)

  SALARY NUMBER(8,2)

  HIRE_DATE DATE

  Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:

  ALTER TABLE EMPLOYEES

  MODIFY (SALARY DEFAULT 5000);

  What is true about your ALTER statement?

  A. Column definitions cannot be altered to add DEFAULT values.
  B. A change to the DEFAULT value affects only subsequent insertions to the table.
  C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type.
  D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
  B. A change to the DEFAULT value affects only subsequent insertions to the table.

• Question 54:

  Examine the structure of the EMPLOYEES table:

  EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER\ SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table

  You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.

  Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

  A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
  B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
  C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
  D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
  E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
  F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
  C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
  D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

• Question 55:

  View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

  You want to update the EMPLOYEES table as follows:4 ? 4;

  -Update only those employees who work in Boston or Seattle (locations 2900 and 2700). -Set department_id for these employees to the department_id corresponding to London (location_id 2100). -Set the employees' salary in location_id

  2100 to 1.1 times the average salary of their department. -Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

  You issue the following command:

  SQL>UPDATE employees

  SET department_id =

  (SELECT department_id

  FROM departments

  WHERE location_id = 2100),

  (salary, commission) =

  (SELECT 1.1*AVG(salary), 1.5*AVG(commission)

  FROM employees, departments

  WHERE departments.location_id IN(2900,2700,2100))

  WHERE department_id IN

  (SELECT department_id

  FROM departments

  WHERE location_id = 2900

  OR location_id = 2700)

  What is the outcome?

  A. It executes successfully and gives the correct result.
  B. It executes successfully but does not give the correct result.
  C. It generates an error because a subquery cannot have a join condition in an UPDATE statement.
  D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.
  B. It executes successfully but does not give the correct result.

• Question 56:

  View the Exhibit and examine the structure of the PROMOTIONS table.

  

  Evaluate the following SQL statement:

  SQL>SELECT promo_name,CASE

  WHEN promo_cost >=(SELECT AVG(promo_cost)

  FROM promotions

  WHERE promo_category='TV')

  then 'HIGH'

  else 'LOW'

  END COST_REMARK

  FROM promotions;

  Which statement is true regarding the outcome of the above query?

  A. It shows COST_REMARK for all the promos in the table.
  B. It produces an error because the subquery gives an error.
  C. It shows COST_REMARK for all the promos in the promo category 'TV'.
  D. It produces an error because subqueries cannot be used with the CASE expression.
  A. It shows COST_REMARK for all the promos in the table.

• Question 57:

  Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

  A. addition
  B. subtraction
  C. raising to a power
  D. finding the quotient
  E. finding the lowest value
  A. addition
  C. raising to a power
  E. finding the lowest value

• Question 58:

  What is true regarding sub queries?

  A. The inner query always sorts the results of the outer query
  B. The outer query always sorts the results of the inner query
  C. The outer query must return a value to the outer query
  D. The inner query returns a value to the outer query
  E. The inner query must always return a value or the outer query will give an error
  D. The inner query returns a value to the outer query

• Question 59:

  View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

  

  A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;
  B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id
  C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id
  D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id
  A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;
  B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id

• Question 60:

  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.
  A. No privileges.