Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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.
Q2. View the Exhibit and examine the structure of the promotions table.
Evaluate the following SQL statement:
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.
Answer: A
Q3. View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the promotions table, and the required output format.
Which two queries give the correct result? A. Option A
B. Option B
C. Option C
D. Option D
Answer: C,D
Q4. Which three SQL statements would display the value 1890.55 as $1, 890.55?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Answer: A,D,E
Q5. Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1)
FROM DUAL;
What would be the outcome?
A. 16
B. 100
C. 160
D. 200
E. 150
Answer: C
Explanation:
Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero
Q6. Examine the structure and data of the CUST_TRANS table:
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three SQL statements would execute successfully?
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07'
C. SELECT transamt FROM cust_trans WHERE custno > "11";
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07'
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
Answer: A,C,D
Q7. 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 $100 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.
Answer: B
Q8. View the Exhibits and examine the structures of the products and sales tables.
Which two SQL statements would give the same output? A. Option A
B. Option B
C. Option C
D. Option D
Answer: A,C
Q9. You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Explanation:
The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position:
substr('http://www.domain.com', 12, 6) = domain
The position at which the first character of the returned string begins.
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the first character.
When position is negative, then the function counts backward from the end of string. substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses
Unicode complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
When you do not specify a value for this argument, then the function
The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins:
instr('http://www.domain.com', '.', 1, 2) = 18
Q10. View the Exhibit and examine the data in the promotions table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post category that were launched before January 1, 2000.
Which SQL statement would you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q11. Examine the structure of the employees table:
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager1 should be displayed in the manager column.
Which SQL query gets the required output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Reference: http://ivrainbow65.blogspot.com/
Q12. Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. Null values are not ignored during duplicate checking.
C. Names of all columns must be identical across all select statements.
D. The number of columns selected in all select statements need not be the same.
Answer: D
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q13. 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: C
Q14. 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
Q15. Examine the structure of the sales table:
Evaluate the following create table statement:
Which two statements are true about the creation of the SALES1 table?
A. The SALES1 table is created with no rows but only a structure.
B. The SALES1 table would have primary key and unique constraints on the specified columns.
C. The SALES1 table would not be created because of the invalid where clause.
D. The SALES1 table would have not null and unique constraints on the specified columns.
E. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,
Answer: A