Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 2)
Examine the description of the CUSTOMERS table:
The CUSTOMER_ID column is the primary key for the table.
Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?
A. SELECT city_address, COUNT(*) FROM customers
WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’);
B. SELECT city_address, COUNT (*)
FROMcustomers
WHERE city address IN ( ‘Los Angeles’, ‘San Fransisco’)
GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id)
FROMcustomers
WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’)
GROUP BYcity_address, customer_id;
D. SELECT city_address, COUNT (customer_id)
FROM customers
GROUP BY city_address IN ( ‘Los Angeles’, ‘San Fransisco’);
Answer: B
Explanation:
Not C: The customer ID in the GROUP BY clause is wrong
Q2. - (Topic 2)
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc'
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%'
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
Answer: B
Q3. - (Topic 1)
See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Exhibit:
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
Answer: B
Explanation:
Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE
TABLE statement and the AS subquery option.
CREATE TABLE table
[(column, column...)]
AS subquery;
Match the number of specified columns to the number of subquery columns.
Define columns with column names and default values.
Guidelines
The table is created with the specified column names, and the rows retrieved by the
SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of
columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the
column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new
table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY
column will not pass the NOT NULL feature to the new column. Any other constraint rules
are not passed to the new table. However, you can add constraints in the column definition.
Q4. - (Topic 2)
You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?
A. It executes successfully and gives the correct output.
B. It gives an error because the HAVING clause is not valid.
C. It executes successfully but does not give the correct output.
D. It gives an error because the GROUP BY expression is not valid.
Answer: B
Explanation:
The general form of the SELECT statement is further enhanced by the addition of the
HAVING clause and becomes:
SELECT column|expression|group_function(column|expression [alias]),…}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
An important difference between the HAVING clause and the other SELECT statement
clauses is that it may only be specified if a GROUP BY clause is present. This dependency
is sensible since group-level rows must exist before they can be restricted. The HAVING
clause can occur before the GROUP BY clause in the SELECT statement. However, it is
more common to place the HAVING clause after the GROUP BY clause. All grouping is
performed and group functions are executed prior to evaluating the HAVING clause.
Q5. - (Topic 2)
Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.
Answer: B,D,F
Explanation:
Creating a Sequence (continued)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after
reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and
keeps in memory (By default, the Oracle server caches 20 values.)
Q6. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
A. Display the minimum list price for each product status.
B. Display all suppliers whose list price is less than 1000.
C. Display the number of products whose list price is more than the average list price.
D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.
Answer: C,E
Q7. - (Topic 1)
You need to perform these tasks:
. Create and assign a MANAGER role to Blake and Clark . Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark
Which set of SQL statements achieves the desired results?
A. CREATE ROLE manager;
GRANT create table, create view
TO manager;
GRANT manager TO BLAKE,CLARK;
B. CREATE ROLE manager;
GRANT create table, create voew
TO manager;
GRANT manager ROLE TO BLAKE,CLARK;
C. GRANT manager ROLE TO BLAKE,CLARK;
GRANT create table, create voew
TO BLAKE CLARK;
***MISSING***
Answer: A
Explanation: Result of commands:
Q8. - (Topic 1)
View the Exhibit and examine the data in the PROMOTIONS table.
You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)
A.
SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount'
B.
SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount'
C.
SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount'
D.
SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount'
Answer: A,D
Q9. - (Topic 1)
Examine the description of the EMP_DETAILS table given below: Exhibit:
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause
B. You cannot add a new column to the table with LONG as the data type
C. An EMP_IMAGE column cannot be included in the ORDER BY clause
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column
Answer: B,C
Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.
There can only be one LONG column in a table.
Guidelines
A LONG column is not copied when a table is created using a subquery.
A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
Only one LONG column can be used per table.
No constraints can be defined on a LONG column.
You might want to use a CLOB column rather than a LONG column.
Q10. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q11. - (Topic 2)
Which four are attributes of single row functions? (Choose four.)
A. cannot be nested
B. manipulate data items
C. act on each row returned
D. return one result per row
E. accept only one argument and return only one value
F. accept arguments which can be a column or an expression
Answer: B,C,D,F
Explanation:
manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression.
Incorrect Answer: Ais not single row attributes Efunctions can accept more than one argument, e.g NVL2
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-5
Q12. - (Topic 2)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which DELETE statement is valid?
A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey');
D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey');
Answer: C
Explanation:
The correct syntax for DELETE statement
DELETE [ FROM ] table
[ WHERE condition ];
Incorrect Answers :
A. '=' 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.
B. Incorrect DELETE statement
D. Incorrect DELETE statement
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data,
p. 8-19
Q13. - (Topic 1)
Which one is a system privilege?
A. SELECT
B. DELETE
C. EXECUTE
D. ALTER TABLE
E. CREATE TABLE
Answer: E
Q14. - (Topic 2)
Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?
A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;
Answer: C
Q15. - (Topic 1)
Examine the structure of the MARKS table:
Exhibit:
Which two statements would execute successfully? (Choose two.)
A. SELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE 'R%'
C. SELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;
D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2;
Answer: C,D