1Z0-047 Exam Questions

View the Exhibit and examine the structure of the ORDERS table. You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order...

You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the de...

View the Exhibit and examine the data in the EMPLOYEES tables. Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER...

Evaluate the following command: CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMB...

View the Exhibit and examine the data in EMPLOYEES and DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPART...

View the Exhibit and examine the description of the ORDERS table. Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales represe...

View the Exhibit and examine the description of the PRODUCT_INFORMATION table. You want to display the expiration date of the warranty for a product. Which SQL statement would you...

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE. E...

Evaluate the following SQL statement: SELECT 2 col1,ycol2 FROM dual UNION SELECT 1 ,'x' FROM dual UNION SELECT 3 .NULL FROM dual ORDER BY 2; Which statement is true regarding the o...

View the Exhibit and examine the structure of the PRODUCT INFORMATION table. Which two queries would work? (Choose two.)

View the Exhibit and examine the data in the PRODUCTS table. Which statement would add a column called PRICE, which cannot contain NULL?

Which statement correctly differentiates a system privilege from an object privilege?

Evaluate the following SQL statement: CREATE INDEX upper_name_idx ON product_information(UPPER(product_name)); Which query would use the UPPER_NAME_IDX index?

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement: SELECT category_name ,category_description FROM categories_t...

Evaluate the following CREATE TABLE command: CREATE TABLE order_item (order_id NUMBER(3), item_id NUMBER(2), qty NUMBER(4), CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id,item_id)...

View the Exhibit and examine the description of the EMPLOYEES table. Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the c...

View the Exhibit and examine the description of the ORDERS table. Evaluate the following SQL statement: SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001';...

View the Exhibit and examine the structure of the EMPLOYEES table. You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let...

View the Exhibit and examine the description of the ORDERS table. The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SA...

View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECT employee_id, last_name, job_id, manager_id, LEVEL FROM employees S...

View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Re...

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO...

View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables. You want to display all the cities that have no departments and the departments that have not...

View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables. Evaluate the following SOL statement: SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location...

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: SELECT...

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD colum...

Given below is a list of functions and the tasks performed by using these functions, in random order. Function Usage --------------- -----------------------------------------------...

Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. Which SET operator should be used in the blank space in the following SQL statement to display t...

The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the...

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST...

View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively. Which two INSERT stateme...

View the Exhibit and examine the details of the EMPLOYEES table. You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 10...

A subquery is called a single-row subquery when

View the Exhibit and examine the structure of the EMPLOYEES table. You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee wi...

View the Exhibit and examine the data in EMP and DEPT tables. In the DEPT table, DEPTNO is the PRIMARY KEY. In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY...

Evaluate the following CREATE TABLE command: CREATE TABLE order_item (order_id NUMBER(3), Item_id NUMBER(2), qty NUMBER(4), CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id jtem_id)...

View the Exhibit and examine the description of the CUSTOMERS table. You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in...

View the Exhibit and examine the structure of the CUST table. Evaluate the following SQL statements executed in the given order: ALTER TABLE cust ADD CONSTRAINT cust_id_pk PRIMARY...

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) which stores the e...

Which two statements are true regarding the types of table joins available in Oracle Database 10g? (Choose two.)

Which two statements are true about sequences created in a single instance database? (Choose two.)

Evaluate the following SQL statements in the given order: DROP TABLE dept; CREATE TABLE dept (deptno NUMBER(3) PRIMARY KEY, deptname VARCHAR2(10)); DROP TABLE dept; FLASHBACK TABLE...

View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. Which is the best method to load data from the MARKS DETAILStable to the MARKStable?

Which two statements are true about the GROUPING function? (Choose two.)

View the Exhibit and examine the structure of the EMPLOYEES table. Evaluate the following SQL statement: SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH...

View the Exhibit and examine the structure of the CUST table. Evaluate the following SQL statements executed in the given order: ALTER TABLE cust ADD CONSTRAINT cust_id_pk PRIMARY...

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. The query should display the employee IDs of all the employees who have held the job SA_MAN at a...