Page: 1
/ 5
Total 82 questions
View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.
Which query would accomplish this task?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : A
Examine the data in the ename and hiredate columns of the employees table:
You want to generate a list of user IDs as follows:
You issue the following query:
What is the outcome?
- A. It executes successfully and gives the correct output.
- B. It executes successfully but does not give the correct output.
- C. It generates an error because the REPLACE function is not valid.
- D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
Answer : A
Explanation:
REPLACE (text, search_string, replacement_string)
Searches a text expression for a character string and, if found, replaces it with a specified replacement string
The REPLACE Function -
The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function.
The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]).
If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. .
The following queries illustrate the REPLACE function with numeric and date expressions:
Query 1: select replace(10000-3, '9', '85') from dual
Query 2: select replace(sysdate, 'DEC', 'NOV') from dual
Examine the data in the PROMO_BEGIN_DATE column of the promotions table:
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : A
The customers table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : B
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
- B. It produces an error because positional notation cannot be used in the order by clause with set operators.
- C. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.
- D. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.
Answer : D
YOU need to display the date ll-oct-2007 in words as Eleventh of October, Two Thousand
Seven'.
Which SQL statement would give the required result?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : A
Examine the structure of the products table:
You want to display the names of the products that have the highest total value for
UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : A
You issue the following command to alter the country column in the departments table:
Which statement is true?
- A. It produces an error because column definitions cannot be altered to add default values.
- B. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'.
- C. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table.
- D. It produces an error because the data type for the column is not specified.
Answer : B
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
Which two statements are true regarding the count function?
- A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
- B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
- C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
- D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
- E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
Answer : B,D
Explanation:
Using the COUNT Function -
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns.
If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.
Which statement is true regarding the default behavior of the order by clause?
- A. In a character sort, the values are case-sensitive.
- B. NULL values are not considered at all by the sort operation.
- C. Only those columns that are specified in the select list can be used in the order by clause.
- D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.
Answer : A
Explanation:
Character Strings and Dates -
Character strings and date values are enclosed with single quotation marks.
Character values are case-sensitive and date values are format-sensitive.
The default date display format is DD-MON-RR.
View the Exhibit and examine the structure of the customers table.
Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK.
Which SQL statement would produce the required result?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : C
Which two statements are true regarding subqueries?
- A. A subquery can retrieve zero or more rows.
- B. Only two subqueries can be placed at one level.
- C. A subquery can be used only in SQL query statements.
- D. A subquery can appear on either side of a comparison operator.
- E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Answer : A,D
Explanation:
Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abels salary.
To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query
(or main query).
Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.
Subquery Syntax -
A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries.
They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including the following:
WHERE clause -
HAVING clause -
FROM clause -
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.
Guidelines for Using Subqueries -
Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple- row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to only 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and
HAVING clauses of a query.
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 : A
Examine the structure of the transactions table:
You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered.
Which query gives the required result?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : B
30 days access 