Section 1
(Answer all questions in this
section)
1.
Which
comparison operator retrieves a list of values?
IN (*)
LIKE
BETWEEN ... IN ...
IS NULL
2.
You issue this
SQL statement:
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?
1200
1282
1282.25
1300 (*)
3.
Which two
functions can be used to manipulate number or date column values, but NOT
character column values?
(Choose two)
RPAD
TRUNC (*)
ROUND (*)
INSTR
CONCAT
4.
The STYLES
table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979 12.00
You query the database and return the value 79.
Which script did you use?
SELECT INSTR(category, 2,2)
FROM styles
WHERE style_id = 895840;
SELECT INSTR(category, -2,2)
FROM styles
WHERE style_id = 895840;
SELECT SUBSTR(category, 2,2)
FROM styles
WHERE style_id = 895840;
SELECT SUBSTR(category, -2,2)
FROM styles
WHERE style_id = 758960;
(*)
5.
Evaluate this
SELECT statement:
SELECT LENGTH(email)
FROM
employee;
What will this SELECT statement display?
The longest e-mail address in the
EMPLOYEE table
The email address of each employee
in the EMPLOYEE table
The number of characters for each
value in the EMAIL column in the employees table (*)
The maximum number of characters
allowed in the EMAIL column
6.
You need to
display the number of characters in each customer's last name.
Which function should you use?
LENGTH (*)
LPAD
COUNT
SUBSTR
7.
The PRICE table
contains this data:
PRODUCT_ID MANUFACTURER_ID
86950 59604
You query the database and return the value 95.
Which script did you use?
SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
(*)
SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;
SELECT TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;
8.
You need to
display each employee's name in all uppercase letters.
Which function should you use?
CASE
UCASE
UPPER (*)
TOUPPER
9.
Which functions
can be used to manipulate character, number, and date column values?
CONCAT, RPAD, and TRIM (*)
UPPER, LOWER, and INITCAP
ROUND, TRUNC, and MOD
ROUND, TRUNC, and ADD_MONTHS
10. Which SQL function is used to return the position where a specific
character string begins within a larger character string?
CONCAT
INSTR (*)
LENGTH
SUBSTR
11. You need to return a portion of each employee's last name,
beginning with the first character up to the fifth character.
Which character function should you use?
INSTR
TRUNC
SUBSTR (*)
CONCAT
12. Which function would you use to return the current database server
date and time?
DATE
SYSDATE (*)
DATETIME
CURRENTDATE
13. You need to display the number of months between today's date and
each employee's hiredate.
Which function should you use?
ROUND
BETWEEN
ADD_MONTHS
MONTHS_BETWEEN (*)
14. Which of the following SQL statements will correctly display the
last name and the number of weeks employed for all employees in department 90?
SELECT last_name, (SYSDATE-hire_date)/7
AS WEEKS
FROM employees
WHERE department_id = 90;
(*)
SELECT last name,
(SYSDATE-hire_date)/7 DISPLAY WEEKS
FROM employees
WHERE department id = 90;
SELECT last_name, # of WEEKS
FROM employees
WHERE department_id = 90;
SELECT last_name,
(SYSDATE-hire_date)AS WEEK
FROM employees
WHERE department_id = 90;
15. You want to create a report that displays all orders and their
amounts that were placed during the month of January. You want the orders with
the highest amounts to appear first.
Which query should you issue?
SELECT orderid, total
FROM orders
WHERE order_date LIKE '01/jan/2002' AND '31/jan/2002'
ORDER BY total DESC;
SELECT orderid, total
FROM orders
WHERE order_date IN ( 01/jan/2002 , 31/jan/2002 )
ORDER BY total;
SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '01/jan/2002' AND '31/jan/2002'
ORDER BY total DESC;
(*)
SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '31/jan/2002' AND '01/jan/2002'
ORDER BY total DESC;
16. The EMPLOYEES table contains these columns:
LAST_NAME
VARCHAR2(20)
FIRST_NAME
VARCHAR2(20)
HIRE_DATE
DATE
EVAL_MONTHS
NUMBER(3)
Evaluate this SELECT statement:
SELECT hire_date + eval_months
FROM employees;
The values returned by this SELECT statement will be of which data
type?
DATE (*)
NUMBER
DATETIME
INTEGER
Section 2
(Answer all questions in this
section)
17. Which three statements concerning explicit data type conversions
are true?
(Choose three) (Choose all correct answers)
Use the TO_NUMBER function to
convert a number to a character string.
Use the TO_DATE function to convert
a character string to a date value. (*)
Use the TO_NUMBER function to
convert a character string of digits to a number. (*)
Use the TO_DATE function to convert
a date value to a character string or number.
Use the TO_CHAR function to convert
a number or date value to a character string. (*)
18. You have been asked to create a report that lists all customers who
have placed orders of at least $2,500. The report's date should be displayed
using this format:
Day,
Date Month, Year (For example, Tuesday, 13 April, 2004).
Which statement should you issue?
SELECT companyname, TO_CHAR
(sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (date,
'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE
(sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_CHAR
(sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)
19. If you use the RR format when writing a query using the date 27-OCT-17
and the year is 2001, what year would be the result?
2001
1901
2017 (*)
1917
20. Which statement concerning single row functions is true?
Single row functions can accept only
one argument, but can return multiple values.
Single row functions cannot modify a
data type.
Single row functions can be nested.
(*)
Single row functions return one or
more results per row.
21. Which two statements concerning SQL functions are true?
(Choose two) (Choose all correct answers)
Character functions can accept
numeric input.
Not all date functions return date
values. (*)
Number functions can return number
or character values.
Conversion functions convert a value
from one data type to another data type. (*)
Single-row functions manipulate groups
of rows to return one result per group of rows.
22. Which arithmetic operation will return a numeric value?
TO_DATE('01-JUN-2004') -
TO_DATE('01-OCT-2004') (*)
NEXT_DAY(hire_date) + 5
SYSDATE - 6
SYSDATE + 30 / 24
23. The PRODUCT table contains this column: PRICE NUMBER(7,2)
Evaluate this statement:
SELECT
NVL(10 / price, '0')
FROM
PRODUCT;
What would happen if the PRICE column contains null values?
The statement would fail because
values cannot be divided by 0.
A value of 0 would be displayed. (*)
A value of 10 would be displayed.
The statement would fail because
values cannot be divided by null.
24. Which of the following General Functions will return the first
non-null expression in the expression list?
NVL
NVL2
NULLIF
COALESCE (*)
25. Which statement about group functions is true?
NVL and NVL2, but not COALESCE, can
be used with group functions to replace null values.
NVL and COALESCE, but not NVL2, can
be used with group functions to replace null values.
NVL, NVL2, and COALESCE can be used
with group functions to replace null values. (*)
COALESCE, but not NVL and NVL2, can
be used with group functions to replace null values.
26. When executed, which statement displays a zero if the
TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
SELECT NVL (tuition_balance + housing_balance, 0) "Balance
Due"
FROM student_accounts;
(*)
SELECT
NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance +
housing_balance "Balance Due"
FROM student_accounts;
SELECT tuition_balance +
housing_balance
FROM student_accounts;
SELECT
TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance
+ housing_balance "Balance Due"
FROM student_accounts;
Section 3
(Answer all questions in this
section)
27. Which query represents the correct syntax for a left outer join?
SELECT companyname, orderdate, total
FROM
customers c
LEFT
JOIN orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM
customers c
OUTER
JOIN orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM customers c
LEFT OUTER JOIN orders o
ON c.cust_id = o.cust_id;
(*)
SELECT companyname, orderdate, total
FROM
customers c
LEFT
OUTER orders o
ON
c.cust_id = o.cust_id;
28. Which two sets of join keywords create a join that will include
unmatched rows from the first table specified in the SELECT statement?
LEFT OUTER JOIN and FULL OUTER JOIN
(*)
RIGHT OUTER JOIN and LEFT OUTER JOIN
USING and HAVING
OUTER JOIN and USING
29. What types of joins will return the unmatched values from both
tables in the join?
Natural joins
Left outer joins
Full outer joins (*)
Right outer joins
30. Evaluate this SELECT statement:
SELECT
*
FROM
employee e, employee m
WHERE
e.mgr_id = m.emp_id;
Which type of join is created by this SELECT statement?
a self join (*)
a cross join
a left outer join
a full outer join
31. Which SELECT statement implements a self join?
SELECT p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id;
(*)
SELECT
p.part_id, t.product_id
FROM
part p, product t
WHERE
p.part_id = t.product_id;
SELECT
p.part_id, t.product_id
FROM
part p, product t
WHERE
p.part_id = t.product_id (+);
SELECT
p.part_id, t.product_id
FROM
part p, product t
WHERE
p.part_id =! t.product_id;
32. Which statement about a self join is true?
The NATURAL JOIN clause must be
used.
Table aliases must be used to
qualify table names. (*)
Table aliases cannot be used to
qualify table names.
A self join must be implemented by
defining a view.
33. Evaluate this SELECT statement:
SELECT
a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname
as "Physician", c.admission
FROM
patient a
JOIN
physician b
ON
(b.physician_id = c.physician_id)
JOIN
admission c
ON
(a.patient_id = c.patient_id);
Which clause generates an error?
JOIN physician b
ON (b.physician_id =
c.physician_id); (*)
JOIN admission c
ON (a.patient_id = c.patient_id)
34. You created the CUSTOMERS and ORDERS tables by issuing these CREATE
TABLE statements in sequence:
CREATE
TABLE customers
(custid
varchar2(5),
companyname
varchar2(30),
contactname
varchar2(30),
address
varchar2(30),
city
varchar2(20),
state
varchar2(30),
phone
varchar2(20),
constraint
pk_customers_01 primary key (custid));
CREATE
TABLE orders
(orderid
varchar2(5) constraint pk_orders_01 primary key,
orderdate
date,
total
number(15),
custid
varchar2(5) references customers (custid));
You have been instructed to compile a report to present the
information about orders placed by customers who reside in Nashville.
Which query should you issue to achieve the desired results?
SELECT
custid, companyname
FROM
customers
WHERE
city = 'Nashville';
SELECT
orderid, orderdate, total
FROM
orders o
NATURAL
JOIN customers c ON o.custid = c.custid
WHERE
city = 'Nashville';
SELECT orderid, orderdate, total
FROM orders o
JOIN customers c ON o.custid = c.custid
WHERE city = 'Nashville';
(*)
SELECT
orderid, orderdate, total
FROM
orders
WHERE
city = 'Nashville';
35. The primary advantages of using JOIN ON is:
(Select two) (Choose all correct answers)
The join happens automatically based
on matching column names and data types.
It will display rows that do not
meet the join condition.
It permits columns with different
names to be joined. (*)
It permits columns that don?t have
matching data types to be joined. (*)
36. Which keyword in a SELECT statement creates an equijoin by specifying
a column name common to both tables?
A HAVING clause
The FROM clause
The SELECT clause
A USING clause (*)
37. Which of the following statements is the simplest description of a
nonequijoin?
A join condition containing
something other than an equality operator (*)
A join condition that is not equal
to other joins
A join condition that includes the
(+) on the left hand side
A join that joins a table to itself
38. Below find the structures of the PRODUCTS and VENDORS tables:
PRODUCTS
PRODUCT_ID
NUMBER
PRODUCT_NAME
VARCHAR2 (25)
VENDOR_ID
NUMBER
CATEGORY_ID
NUMBER
VENDORS
VENDOR_ID
NUMBER
VENDOR_NAME
VARCHAR2 (25)
ADDRESS
VARCHAR2 (30)
CITY
VARCHAR2 (25)
REGION
VARCHAR2 (10)
POSTAL_CODE
VARCHAR2 (11)
You want to create a query that will return an alphabetical list of
products, including the product name and associated vendor name, for all products
that have a vendor assigned.
Which two queries could you use?
(Choose all correct answers)
SELECT
p.product_name, v.vendor_name
FROM
products p
LEFT
OUTER JOIN vendors v
ON
p.vendor_id = v.vendor_id
ORDER
BY p.product_name;
SELECT
p.product_name, v.vendor_name
FROM
products p
JOIN
vendors v
ON
(vendor_id)
ORDER
BY p.product_name;
SELECT p.product_name, v.vendor_name
FROM products p
NATURAL JOIN vendors v
ORDER BY p.product_name;
(*)
SELECT
p.product_name, v.vendor_name
FROM
products p
JOIN
vendors v
USING
(p.vendor_id)
ORDER
BY p.product_name;
SELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (vendor_id)
ORDER BY p.product_name;
(*)
39. For which condition would you use an equijoin query with the USING
keyword?
You need to perform a join of the CUSTOMER and ORDER tables but
limit the number of columns in the join condition. (*)
The
ORDER table contains a column that has a referential constraint to a column in
the PRODUCT table.
The
CUSTOMER and ORDER tables have no columns with identical names.
The
CUSTOMER and ORDER tables have a corresponding column, CUST_ID. The CUST_ID
column in the ORDER table contains null values that need to be displayed.
Section 4
(Answer all questions in this
section)
40. Which group function would you use to display the highest salary
value in the EMPLOYEES table?
AVG
COUNT
MAX (*)
MIN
41. You need to compute the total salary amount for all employees in
department 10.
Which group function will you use?
MAX
SUM (*)
VARIANCE
COUNT
42. The CUSTOMER table contains these columns:
CUSTOMER_ID
NUMBER(9)
FIRST_NAME
VARCHAR2(25)
LAST_NAME
VARCHAR2(30)
CREDIT_LIMIT
NUMBER (7,2)
CATEGORY
VARCHAR2(20)
You need to calculate the average credit limit for all the
customers in each category. The average should be calculated based on all the
rows in the table excluding any customers who have not yet been assigned a
credit limit value.
Which group function should you use to calculate this value?
AVG (*)
SUM
COUNT
STDDEV
43. Examine the data in the PAYMENT table:
PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT
86590586 8908090 10-JUN-2003 BASIC 859.00
89453485 8549038 15-FEB-2003 INTEREST 596.00
85490345 5489304 20-MAR-2003 BASIC 568.00
You need to determine the average payment amount made by each
customer in January, February, and March of 2003.
Which SELECT statement should you use?
SELECT AVG(payment_amount)
FROM payment
WHERE payment_date
BETWEEN '01-JAN-2003' AND '31-MAR-2003';
(*)
SELECT
AVG(payment_amount)
FROM
payment;
SELECT
SUM(payment_amount)
FROM
payment
WHERE
payment_date BETWEEN '01-JAN-2003' and '31-MAR-2003';
SELECT
AVG(payment_amount)
FROM
payment
WHERE
TO_CHAR(payment_date) IN (JAN, FEB, MAR);
44. Which aggregate function can be used on a column of the DATE data
type?
AVG
MAX (*)
STDDEV
SUM
45. The TRUCKS table contains these columns:
TRUCKS:
TYPE
VARCHAR2(30)
YEAR
DATE
MODEL
VARCHAR2(20)
PRICE
NUMBER(10)
Which SELECT statement will return the average price for the 4x4
model?
SELECT AVG(price)
FROM trucks
WHERE model = '4x4';
(*)
SELECT
AVG(price)
FROM
trucks
WHERE
model IS '4x4';
SELECT
AVG(price)
FROM
trucks
WHERE
model IS 4x4;
SELECT
AVG(price), model
FROM
trucks
WHERE
model IS '4x4';
46. Group functions return a value for ________________ and ________________
null values in their computations.
a row set, ignore (*)
each row, ignore
a row set, include
each row, include
47. The EMPLOYEES table contains these columns:
EMPLOYEE_ID
NUMBER(9)
LAST_NAME
VARCHAR2(20)
FIRST_NAME
VARCHAR2(20)
SALARY
NUMBER(7,2)
DEPARTMENT_ID
NUMBER(9)
You need to display the number of employees whose salary is greater
than $50,000?
Which SELECT would you use?
SELECT
* FROM employees
WHERE
salary > 50000;
SELECT
* FROM employees
WHERE
salary < 50000;
SELECT
COUNT(*)
FROM
employees
WHERE
salary < 50000;
SELECT COUNT(*)
FROM employees
WHERE salary > 50000;
(*)
SELECT
COUNT(*)
FROM
employees
WHERE
salary > 50000
GROUP
BY employee_id, last_name, first_name, salary, department_id;
48. Examine the data from the LINE_ITEM table:
LINE_ITEM_ID ORDER_ID PRODUCT_ID PRICE DISCOUNT
890898 847589 848399 8.99 0.10
768385 862459 849869 5.60 0.05
867950 985490 945809 5.60
954039 439203 438925 5.25 0.15
543949 349302 453235 4.50
You query the LINE_ITEM table and a value of 5 is returned.
Which SQL statement did you execute?
SELECT
COUNT(discount)
FROM
line_item;
SELECT COUNT(*)
FROM line_item;
(*)
SELECT
SUM(discount)
FROM
line_item;
SELECT
AVG(discount)
FROM
line_item;
49. Which SELECT statement will calculate the number of rows in the
PRODUCTS table?
SELECT
COUNT(products);
SELECT
COUNT FROM products;
SELECT COUNT (*) FROM products; (*)
SELECT
ROWCOUNT FROM products;
50. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
You issue this SELECT statement:
SELECT
COUNT(category)
FROM
styles;
Which value is displayed?
0
6
7 (*)
The statement will NOT execute
successfully.
