1. Evaluate this SQL statement:

SELECT manufacturer_id, COUNT(*), order_date

FROM inventory

WHERE price > 5.00

GROUP BY order_date, manufactured_id

HAVING COUNT(*) > 10

ORDER BY order_date DESC;

Which clause specifies which rows will be returned from the inventory table (choose only one answer below)?

WHERE price > 5.00

HAVING COUNT(*) > 10

ORDER BY order_date DESC;

GROUP BY order_date, manufactured_id

Answer:

2. For which result would you use a group function (choose only one answer below)?

To display the order date of orders in DD MON YYYY format

To convert the character string January 28, 2000 to a date format

To produce a total of all the values in the COST column in the PRODUCT table

To display all the values in the DESCRIPTION column in the PRODUCT table in lowercase

Answer:

3. For which task can you use the TO_CHAR function (choose only one answer below)?

to accept 'October 20, 1965' and store it as a date value

to accept 'ten' and store it as a number 10

to accept the number 10 and store it as 'ten'

to display a date value in 'MM/DD/YY' format

Answer:

4. Which character function can you use to return a specified portion of a character string (choose only one answer below)?

INITCAP

CONCAT

SUBSTR

LENGTH

Answer:

5. Benefits are based on the number of years an employee has been employed. You need to create a report to display each employees name, id number, date hired, and the number of years employed, rounded to a whole number, based on the number of months from the date hired until today. Which statement produces the required results (choose only one answer below)?

Select first_name, last_name, emp_id, date_hired, ROUND(SYSDATE) ROUND(date_hired)

FROM employee;

Select first_name, last_name, emp_id, date_hired,

(ROUND(SYSDATE) ROUND(date_hired))/12

FROM employee;

Select first_name, last_name, emp_id, date_hired,

TRUNC(SYSDATE, YY) TRUNC(date_hired, YY)

FROM employee;

Select first_name, last_name, emp_id, date_hired,

ROUND(MONTHS_BETWEEN(SYSDATE, date_hired)/12) FROM employee;

Answer:

6. The primary advantage of using JOIN ON is (choose only one answer below):

The join happens automatically based on matching column names and data types

It will display rows that do not meet the join condition

It easily produces a Cartesian product between the tables in the statement

It permits columns with different names to be joined

Answer:

7. Based on the INVENTORY instance chart in the following:

You attempt to query the database with this SQL statement:

SELECT NVL(100/quantity, none)

FROM inventory;

Why does this statement cause an error when quantity values are null (choose only one answer below)?

The expression attempts to divide by a null value.

The data types in the conversion function are incompatible.

The character string none should be enclosed in single quotes ().

A null value used in an expression cannot be converted to an actual value.

Answer:

8. On which side of the outer join condition would you place the outer join symbol (choose only one answer below)?

the side with matching rows

the side without matching rows

both sides of the join condition

neither side of the join condition

Answer:

9. Evaluate this SQL statement:

SELECT i.id_number, m.manufacturer_id

FROM inventory i, inventory m

WHERE i.manufacturer_id = m.id_number;

What type of join is used in this statement (choose only one answer below)?

self

outer

equijoin

non-equijoin

Answer:

10. A NATURAL JOIN is based on (choose only one answer below):

Columns with the same data type and width

Columns with the same name

Columns with the same name and data type

Tables with the same structure

Answer:

11. What happens when you create a Cartesian product (choose only one answer below)?

All rows from one table are joined to all rows of another table

No rows are returned as you entered wrong join-criteria

The table is joined to itself, one column to the next column, exhausting all possibilities

All rows that do not match in the WHERE clause are displayed

Answer:

12. Assuming an EMPLOYEE table has columns of Emp_Id, L_Name, F_Name, M_Name and Salary. Please write a statement to display those employees who have no middle names (i.e. M_NAME).

Answer:

13. Which Select statement could you use if you wanted to display unique combinations of the ID_NUMBER and MANUFACTURER_ID values from the INVENTORY table (choose only one answer below)?

SELECT DISTINCT manufacturer_id

FROM inventory;

SELECT id_number, manufacturer_id

FROM inventory;

SELECT DISTINCT id_number, manufacturer_id

FROM inventory;

SELECT id_number, manufacturer_id DISTINCT

FROM inventory;

SELECT id_number, DISTINCT manufacturer_id

FROM inventory;

Answer:

14. The ITEM table contains these columns:

COST NUBMER(7,2) RETAIL NUMBER(7,2)

The RETAIL and COST columns must have a value that is greater than zero. Evaluate these two SQL

Statements:

1. SELECT

retail cost * 1.25 * .10

FROM

item;

2. SELECT

(retail (cost *1.25 )*.10)

FROM

item;

What will be the results (choose only one answer below)?

Statement 1 will return a low value than statement 2.

Statement 1 and statement 2 will return the same values.

Statement 1 will return a higher value than statement 2.

Only one of the statements will execute.

Answer:

15. Based on the INVENTORY table displayed as follows:

Evaluate this SQL statement (NOTE: This is a tricky question):

SELECT id_number

FROM inventory

WHERE description = 'cable'

Which value would be displayed (choose only one answer below)?

25023

25025

25027

25028

No value will be displayed.

Answer:

16. For which task would you use the WHERE clauses in a SELECT statement (choose only one answer below)?

to designate the INVENTORY table location

to compare the MANUFACTURED_ID values to 897898

to display only the unique MANUFACTURE_ID values

to restrict the output of the SUM(price) function Answer:

17. Which operator would it be most appropriate to use when searching on a list of values (choose only one answer below)?

=

IN

LIKE

BETWEEN AND

Answer:

18. Based on the INVENTORY table in question 15, evaluate this SQL statement:

SELECT MIN(description) FROM inventory;

Which value is displayed (choose only one answer below)?

Cable

Ribbon

Copper Wire

Vinyl Sheets

Answer:

19. Based on the INVENTORY table in question 15, evaluate this SQL statement:

SELECT manufacturer_id Manufacturer Identification Number, SUM(price)

FROM inventory

WHERE price > 6.00

GROUP BY Manufacturer Identification Number

ORDER BY 2;

Which clause will cause an error (choose only one answer below)?

ORDER BY 2;

FROM inventory

WHERE price > 6.00

GROUP BY Manufacturer Identification Number

SELECT manufacturer_id Manufacturer Identification Number, SUM(price) Answer:

20. Which clause could you use to restrict values returned by a group function (choose only one answer below)?

WHERE

HAVING

ORDER

A group function cannot be restricted

Answer:

21. Which operator would it be most appropriate to use with a single row subquery (choose only one answer below)?

=

IN

LIKE

BETWEEN AND

Answer:

22. If you need to display data from two tables that have common values existing in corresponding columns, which type of join condition would you use (choose only one answer below)?

self

outer

equijoin

non-equijoin

Answer:

23. Based on the instance chart in Question 7. You created a report to display the prices of products from your warehouse inventory. Which script will you use to display the prices in this format:

$0.25 (choose only one answer below)?

SELECTFROM TO_CHAR(price,inventory; $9.99)

SELECT TO_CHAR(price, $999990)

FROM inventory;

SELECT TO_NUM(price, $999990.99)

FROMSELECT inventory;TO_NUM(price, $999990.99)

FROM inventory;

SELECT TO_CHAR(price, $999990.99)

FROM inventory;

Answer:

24. You attempt to query the database with this SQL statement:

SELECT inventory.id_number, manufactuer.id_number FROM inventory i, manufacturer m

WHERE i.manufacturer_id = m.id_number

ORDER BY 1;

Which line will cause an error (choose only one answer below)?

ORDER BY 1;

FROM inventory I, manufacturer m

WHERE i.manufacturer_id = m.id_number

SELECT inventory.id_number, manufactuer.id_number

Answer:

25. Based on the Question 15. You query the database and return the value 0804. Which script did

you use (choose only one answer below)?

SELECT SUB(manufacturer_id, 4)

FROM inventory WHERE id_number = 32081;

SELECT SUBSTR(manufacturer_id, 4)

FROM inventory

WHERE id_number = 32081;

SELECT SUBSTR(manufacturer_id, -4, 4)

FROM inventory

WHERE id_number = 32081;

SELECT SUBSTR(manufacturer_id, 4, -4)

FROM inventory

WHERE id_number = 32081;

None of the scripts returns a value of 0804.