Oracle Database: SQL Fundamentals I
Question No: 181 – (Topic 2)
The CUSTOMERS table has these columns:
The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is.
Which expression finds the number of different countries represented in the CUSTOMERS table?
COUNT DISTINCT UPPER(country_address)
Question No: 182 – (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
Display the minimum list price for each product status.
Display all suppliers whose list price is less than 1000.
Display the number of products whose list price is more than the average list price.
Display the total number of products supplied by supplier 102 and have product status as #39;obsolete#39;.
Display all products whose minimum list price is more than the average list price of products and have the status #39;orderable#39;.
Question No: 183 – (Topic 2)
Evaluate the following query:
SELECT INTERVAL #39;300#39; MONTH, INTERVAL #39;54-2#39; YEAR TO MONTH,
INTERVAL #39;11:12:10.1234567#39; HOUR TO SECOND
What is the correct output of the above query?
A. 25-00 , 54-02, 00 11:12:10.123457
B. 00-300, 54-02, 00 11:12:10.123457
C. 25-00 , 00-650, 00 11:12:10.123457
D. 00-300 , 00-650, 00 11:12:10.123457
Answer: A Explanation: Datetime Data Types
You can use several datetime data types: INTERVAL YEAR TO MONTH
Stored as an interval of years and months INTERVAL DAY TO SECOND
Stored as an interval of days, hours, minutes, and seconds
Question No: 184 – (Topic 2)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which DELETE statement is valid?
DELETE FROM employees
WHERE employee_id = (SELECT employee_id FROM employees);
DELETE * FROM employees
WHERE employee_id = (SELECT employee_id FROM new_employees);
DELETE FROM employees
WHERE employee_id IN(SELECT employee_id FROM new_employees
WHERE name = #39;Carrey#39;);
DELETE * FROM employees
WHERE employee_id IN (SELECT employee_id FROM new_employees
WHERE last_name = #39;Carrey#39;);
Answer: C Explanation: Explanation:
The correct syntax for DELETE statement
DELETE [ FROM ] table
[ WHERE condition ]; Incorrect Answers :
#39;=#39; is use in the statement and sub query will return more than one row. Error Ora-01427: single-row sub query returns more than one row.
Incorrect DELETE statement
D. Incorrect DELETE statement
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data, p. 8-19
Question No: 185 – (Topic 2)
What are two reasons to create synonyms? (Choose two.)
You have too many tables.
Your tables names are too long.
Your tables have difficult names.
You want to work on your own tables.
You want to use another schema#39;s tables.
You have too many columns in your tables.
Create a synonyms when the names of the tables are too long or the table names are difficult.
Question No: 186 – (Topic 2)
Which statements are true regarding single row functions? (Choose all that apply.)
MOD : returns the quotient of a division
TRUNC : can be used with NUMBER and DATE values
CONCAT : can be used to combine any number of values
SYSDATE : returns the database server current date and time
INSTR : can be used to find only the first occurrence of a character in a string
TRIM : can be used to remove all the occurrences of a character from a string
Answer: B,D Explanation:
ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division
SYSDATE is a date function that returns the current database server date and time.
Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value.
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.
NEXT_DAY(date, #39;char#39;): Finds the date of the next specified day of the week (#39;char#39;) following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date
The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,#39;fmt#39;]): Returns date rounded to the unit that is specified by the format model
fmt. If the format model fmt is omitted, date is rounded to the nearest day.
TRUNC(date[, #39;fmt#39;]): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function
The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values.
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(#39;http://www.domain.com#39;,#39;.#39;,1,2) = 18
The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:
Question No: 187 – (Topic 2)
View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update the EMPLOYEES table as follows:4 ? 4;
-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#39; salary in location_id 2100 to 1.1 times the average salary of their department.
-Set the employees#39; commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
SQLgt;UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments
WHERE departments.location_id IN(2900,2700,2100))
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700)
What is the outcome?
It executes successfully and gives the correct result.
It executes successfully but does not give the correct result.
It generates an error because a subquery cannot have a join condition in an UPDATE statement.
It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an
Question No: 188 – (Topic 2)
The DBA issues this SQL command: CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?
Only the SELECT privilege.
Only the CONNECT privilege.
All the privileges of a default user.
Answer: A Explanation:
There are no privileges for the user Scott at this point. They are not added themselves to the user immediately after creation. The DBA needs to grant all privileges explicitly.
B:There are no privileges for the user Scott at this point. SELECT privilege needs to be added to the user Scott.
C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be added to the user Scott.
D:There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351 Chapter 8: User Access in Oracle
Question No: 189 – (Topic 2)
What is true of using group functions on columns that contain NULL values?
Group functions on columns ignore NULL values.
Group functions on columns returning dates include NULL values.
Group functions on columns returning numbers include NULL values.
Group functions on columns cannot be accurately used on columns that contain NULL values.
Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
Explanation: group functions on column ignore NULL values
Bgroup functions on column ignore NULL values Cgroup functions on column ignore NULL values
DNVL function can be use for column with NULL values Eno such INC_NULLS keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-12
Question No: 190 – (Topic 2)
View the Exhibit; e xamine the structure of the PROMOTIONS table.
Each promotion has a duration of at least seven days.
Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
Which query would achieve the required result?
SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM
SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|