Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 221:

  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')));
  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')));

• Question 222:

  Examine the structure of the STUDENTS table:

  

  You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.

  Which SQL statement accomplishes this task?

  A. SELECT student_ id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM
  B. SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID
  C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM
  D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99' AND course_id = `INT_SQL' ORDER BY marks DESC) WHERE ROWNUM
  E. SELECT student id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM
  D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99' AND course_id = `INT_SQL' ORDER BY marks DESC) WHERE ROWNUM

• Question 223:

  You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

  

  You issue the following SQL statement:

  

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

  A. It produces an error because the AMT_SPENT column contains a null value.
  B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
  C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
  D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
  C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.

• Question 224:

  View the Exhibit and examine the structure of the CUSTOMERS table. You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000. Evaluate the following two queries:

  

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

  

  A. Only the first query gives the correct result.
  B. Only the second query gives the correct result.
  C. Both execute successfully and give the same result.
  D. Both execute successfully but do not give the required result.
  A. Only the first query gives the correct result.

• Question 225:

  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;
  C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;

• Question 226:

  View the Exhibit and examine the structure of the PRODUCTS tables. You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. Which query would give the correct output?

  A. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
  B. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
  C. SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
  D. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);
  A. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);

• Question 227:

  Evaluate the following SQL statements: DELETE FROM sales;

  There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?

  A. It removes all the rows as well as the structure of the table
  B. It removes all the rows in the table and deleted rows cannot be rolled back
  C. It removes all the rows in the table and deleted rows can be rolled back
  D. It would not remove the rows if the table has a primary key
  C. It removes all the rows in the table and deleted rows can be rolled back

• Question 228:

  View the Exhibits and examine PRODUCTS and SALES tables.

  

  You issue the following query to display product name and the number of times the product has been sold:

  SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p ON i.prod_id = p.prod_id;

  What happens when the above statement is executed?

  A. The statement executes successfully and produces the required output.
  B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
  C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
  D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
  A. The statement executes successfully and produces the required output.

• Question 229:

  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

• Question 230:

  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
  B. It executes successfully and gives the required result