Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 2)
Which are iSQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation:
The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.
Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
Q2. - (Topic 1)
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.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, -2, 1)='a'
B.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>'' AND SUBSTR(cust_first_name, -2, 1)='a'
C.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)='a'
D.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a'
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
Q3. - (Topic 1)
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.
Q4. - (Topic 1)
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. The ON clause can be used to join tables on columns that have different names but compatible data types
B. A maximum of one pair of columns can be joined between two tables using the ON clause
C. Both USING and ON clause can be used for equijoins and nonequijoins
D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause
Answer: A,D
Explanation:
Creating Joins with the USING Clause If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin. Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive Using Table Aliases with the USING clause When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause. The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement. Creating Joins with the ON Clause The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join. – ANSWER C The join condition is separated from other search conditions. ANSWER D
The ON clause makes code easy to understand.
Q5. - (Topic 1)
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value
Answer: B
Explanation:
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
Q6. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
Exhibit:
The above command fails when executed. What could be the reason?
A. The BETWEEN clause cannot be used for the CHECK constraint
B. SYSDATE cannot be used with the CHECK constraint
C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
D. The CHECK constraint cannot be placed on columns having the DATE data type
Answer: B
Explanation:
CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),
Q7. - (Topic 1)
You are currently located in Singapore and have connected to a remote database in
Chicago.
You issue the following command:
Exhibit:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. Number of days since the promo started based on the current Singapore data and time.
B. An error because the ROUND function specified is invalid
C. An error because the WHERE condition specified is invalid
D. Number of days since the promo started based on the current Chicago data and time
Answer: D
Q8. - (Topic 2)
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
A. Both commands execute successfully.
B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
Answer: B
Explanation:
Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create
constraints at either the column level or table level. Constraints defined at the column level
are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint
pertains in a set of parentheses. It is mainly the syntax that differentiates the two;
otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.
Q9. - (Topic 1)
A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table
Bring together data that is stored in different tables by creating a link between
them.
Which set of keywords describes these capabilities?
A. difference, projection, join
B. selection, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Answer: B
Explanation: Explanation: choose rows from a table is SELECTION,
Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN.
Incorrect Answer:
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6
Q10. - (Topic 1)
Examine the structure of the EMPLOYEES table:
You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:
Which INSERT statement meets the above requirements?
A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50));
C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
Answer: D
Q11. - (Topic 1)
See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;
C. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;
D. SELECT AVG(inv_date) FROM invoice;
Answer: A,B
Q12. - (Topic 2)
View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: D,E
Explanation:
Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another. Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements: select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ;
Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the
parent query: it is returning a single value, used for comparison with the rows retrieved by
the parent query.
The subquery could return a set of rows. For example, you could use the following to find
all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in
(select distinct(department_id) from employees);
Q13. - (Topic 1)
Examine the structure of the EMPLOYEES table:
Which UPDATE statement is valid?
A.
UPDATE employees
SET first_name = ‘John’
SET last_name = ‘Smith’
WHERE employee_id = 180;
B.
UPDATE employees
SET first_name = ‘John’,
SET last_name = ‘Smoth’
WHERE employee_id = 180;
C.
UPDATE employee
SET first_name = ‘John’
AND last_name = ‘Smith’
WHERE employee_id = 180;
D.
UPDATE employee
SET first_name = ‘John’, last_name = ‘Smith’
WHERE employee_id = 180;
Answer: D
Q14. - (Topic 2)
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:
What would be the outcome?
A. Abigail PA B. Abigail Pa
C. Abigail IS
D. an error message
Answer: B
Q15. - (Topic 2)
View the Exhibit and examine the data in the EMPLOYEES table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
What is the outcome?
A. It generates an error because the alias is not valid.
B. It executes successfully and gives the correct output.
C. It executes successfully but does not give the correct output.
D. It generates an error because the usage of the ROUND function in the expression is not valid.
E. It generates an error because the concatenation operator can be used to combine only two items.
Answer: C
Explanation:
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.)