Q1. 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: 

Which statement is true regarding the outcome? 

A. It executes successfully and gives the correct output. 

B. It gives an error because the TO_CHAR function is not valid. 

C. It executes successfully but does not give the correct output. 

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 clause. 

Answer:


Q2. View the Exhibit and examine the structure of the product, component, and PDT_COMP tables. 

In product table, PDTNO is the primary key. 

In component table, COMPNO is the primary key. 

In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table. 

You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist. 

Evaluate the following query: 

SQL>SELECT pdtno, pdtname, compno, compname 

FROM product _____________ pdt_comp 

USING (pdtno) ____________ component USING (compno) 

WHERE compname IS NOT NULL; 

Which combination of joins used in the blanks in the above query gives the correct output? 

A. JOIN; JOIN 

B. FULL OUTER JOIN; FULL OUTER JOIN 

C. RIGHT OUTER JOIN; LEFT OUTER JOIN 

D. LEFT OUTER JOIN; RIGHT OUTER JOIN 

Answer:


Q3. Examine the types and examples of relationships that follow: 

1. One-to-one a) Teacher to students 

2. One-to-many b) Employees to Manager 

3. Many-to-one c) Person to SSN 

4. Many-to-many d) Customers to products 

Which option indicates the correctly matched relationships? 

A. 1-a, 2-b, 3-c, and 4-d 

B. 1-c, 2-d, 3-a, and 4-b 

C. 1-c, 2-a, 3-b, and 4-d 

D. 1-d, 2-b, 3-a, and 4-c 

Answer:


Q4. Evaluate the following query: 

What would be the outcome of the above query? 

A. It produces an error because flower braces have been used. 

B. It produces an error because the data types are not matching. 

C. It executes successfully and introduces an 's at the end of each PROMO_NAME in the output. 

D. It executes successfully and displays the literal "{'s start date was \} * for each row in the output. 

Answer: C Explanation: 

So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string 

Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. 

These character-enclosing symbols could have been anything other than single quotation marks. 

Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character 

The syntax of the alternative quote operator is as follows: 

q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket. 

Alternative Quote (q) Operator 

Specify your own quotation mark delimiter. 

Select any delimiter. 

Increase readability and usability. 

SELECT department_name || q'[ Department's Manager Id: ]' 

|| manager_id 

AS "Department and Manager" 

FROM departments; 

Alternative Quote (q) Operator 

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter. 

You can choose any convenient delimiter, single-byte or multi byte, or any of the following character pairs: [ ], { }, ( ), or < >. 

In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets 

[] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string. 


Q5. Examine the create table statements for the stores and sales tables. 

SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); 

SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id)); 

You executed the following statement: 

SQL> DELETE from stores 

WHERE store_id=900; 

The statement fails due to the integrity constraint error: 

ORA-02292: integrity constraint (HR.STORE_ID_FK) violated 

Which three options ensure that the statement will execute successfully? 

A. Disable the primary key in the STORES table. 

B. Use CASCADE keyword with DELETE statement. 

C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table. 

D. Disable the FOREIGN KEY in SALES table and then delete the rows. 

E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option. 

Answer: A,C,D 


Q6. View the Exhibit and examine the structure of the customers table. 

Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. 

Which SQL statement would produce the required result? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

NVL Function 

Converts a null value to an actual value: 

Data types that can be used are date, character, and number. 

Data types must match: 

– NVL(commission_pct, 0) 

– NVL(hire_date, '01-JAN-97') 

– NVL(job_id, 'No Job Yet') 


Q7. View the Exhibit and examine the structure of the products table. 

Evaluate the following query: 

What would be the outcome of executing the above SQL statement? 

A. It produces an error. 

B. It shows the names of all products in the table. 

C. It shows the names of products whose list price is the second highest in the table. 

D. It shows the names of all products whose list price is less than the maximum list price. 

Answer:


Q8. View the Exhibit and examine the structures of the employees and departments tables. 

You want to update the employees table as follows: 

-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 iocation_id 2100 to 1.1 times the average salary of their department. 

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

You issue the following command: 

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, COMMISSION) cannot be specified together in an update statement. 

Answer:


Q9. Which three tasks can be performed using SQL functions built into Oracle Database? 

A. Displaying a date in a nondefault format 

B. Finding the number of characters in an expression 

C. Substituting a character string in a text expression with a specified string 

D. Combining more than two columns or expressions into a single column in the output 

Answer: A,B,C 


Q10. 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: 

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. 

Answer: