Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 21:

  Evaluate the SQL statement:

  SELECT ROUND(45.953, -1), TRUNC(45.936, 2)

  FROM dual;

  Which values are displayed?

  A. 46 and 45
  B. 46 and 45.93
  C. 50 and 45.93
  D. 50 and 45.9
  E. 45 and 45.93
  F. 45.95 and 45.93
  C. 50 and 45.93

• Question 22:

  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.
  B. The result remains unchanged.

• Question 23:

  You need to create a table with the following column specifications:

  1.

  Employee ID (numeric data type) for each employee

  2.

  Employee Name (character data type) that stores the employee name

  3.

  Hire date, which stores the date of joining the organization for each employee

  4.

  Status (character data type), that contains the value 'ACTIVE' if no data is entered

  5.

  Resume (character large object [CLOB] data type), which contains the resume submitted by the employee

  Which is the correct syntax to create this table?

  A. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, e_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB(200));
  B. CREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);
  C. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT "ACTIVE", resume CLOB);
  D. CREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);
  D. CREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);

• Question 24:

  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;
  B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
  D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice;

• Question 25:

  In which four clauses can a sub query be used? (Choose four.)

  A. in the INTO clause of an INSERT statement
  B. in the FROM clause of a SELECT statement
  C. in the GROUP BY clause of a SELECT statement
  D. in the WHERE clause of a SELECT statement
  E. in the SET clause of an UPDATE statement
  F. in the VALUES clause of an INSERT statement
  A. in the INTO clause of an INSERT statement
  B. in the FROM clause of a SELECT statement
  D. in the WHERE clause of a SELECT statement
  E. in the SET clause of an UPDATE statement

• Question 26:

  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.
  B. A table can have only one primary key but multiple foreign keys.
  E. Both primary key and foreign key constraints can be defined at both column and table levels.

• Question 27:

  View the exhibit and examine the description for the SALES and CHANNELS tables.

  

  You issued the following SQL statement to insert a row in the SALES table:

  INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);

  Which statement is true regarding the execution of the above statement?

  A. The statement will execute and the new row will be inserted in the SALES table.
  B. The statement will fail because subquery cannot be used in the VALUES clause.
  C. The statement will fail because the VALUES clause is not required with subquery.
  D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.
  A. The statement will execute and the new row will be inserted in the SALES table.

• Question 28:

  View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

  

  You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)

  A. SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';
  B. SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id) ON(s.cust_id=c.cust_id) WHERE c.cust_city='Tokyo';
  C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id) AND c.cust_city='Tokyo';
  D. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales s USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';
  A. SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';
  C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id) AND c.cust_city='Tokyo';

• Question 29:

  Which statement is true regarding synonyms?

  A. Synonyms can be created only for a table
  B. Synonyms are used to reference only those tables that are owned by another user
  C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
  D. A public synonym and a private synonym can exist with the same name for the same table
  D. A public synonym and a private synonym can exist with the same name for the same table

• Question 30:

  Examine the structure of the INVOICE table: Exhibit:

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;
  B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice;
  C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
  D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;
  A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;
  C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;