CIS336 Study guide Exam PDF

Preview

Summary

Download CIS336 Study guide Exam PDF

Description

Study guide for IT478 Exam #2 – on 3/23 There will be 2 parts – Part 1 (60 points) will have 60 T/F and MC questions. I have included several examples below. Part 2 (40 points) will be take-home. It will have 8 questions. I will hand-out part two on the day of the exam. You will submit part 2 via TeggieNet From chapter 4 1. In a join, column names need to be qualified only a. in inner joins b. in outer joins

c. when the code is confusing d. when the same names are used in both tables

ANS: D 2. If you assign an alias to one table in a join, you have to a. b. c. d.

assign them to all of the tables use that name for the table qualify every column name in the query qualify all of the column names for that table

ANS: B 3. When you need to code multiple conditions in a join, it’s best to a. b. c. d.

code all conditions in the ON clause code only join conditions in the ON clause code all conditions in the WHERE clause code only join conditions in the WHERE clause

ANS: B 4. You don’t ever need to code a right outer join because a. left outer joins are just as efficient b. left outer joins are easier to code

c. right outer joins are less efficient d. right outer joins can be converted to left outer joins

ANS: D 5. Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax a. is easier to read and understand b. lets you separate the join and search conditions

c. can be used for more than two tables d. lets you combine inner and outer joins

ANS: C 6. In a cross join, all of the rows in the first table are joined with all of the

a. distinct rows in the second table b. matched rows in the second table

c. unmatched rows in the second table d. rows in the second table

ANS: D 7. When you code a union that combines two result sets, which of the following is not true? a. b. c. d.

Each result set must have the same number of columns. The result sets may or may not have any duplicate rows. The corresponding columns in the result sets must have compatible data types. The result sets must be derived from different tables.

ANS: D 8. When you code a union with the INTERSECT keyword to combine two result sets, the union a. b. c. d.

includes only rows that occur in both result sets excludes rows from the first result set if they also occur in the second result set includes all rows that occur in both result sets if the primary keys are the same excludes rows from the second result set if they also occur in the first result set

ANS: A 9. A full outer join includes rows that satisfy the join condition, plus a. b. c. d.

rows in the left table that don’t satisfy the join condition rows in both tables that don’t satisfy the join condition rows in the right table that don’t satisfy the join condition the Cartesian product of the two tables

ANS: B Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id

10. (Refer to code example 4-2.) The total number of rows returned by this query must equal a. b. c. d.

the number of rows in the Invoices table the number of rows in the Vendors table the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above

ANS: A 11. (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal a. b. c. d.

the number of rows in the Invoices table the number of rows in the Vendors table the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above

ANS: D 12. (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal a. b. c. d.

the number of rows in the Invoices table the number of rows in the Vendors table the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above

ANS: D 13. When you use the USING keyword instead of the ON keyword for a join, a. b. c. d.

the join can’t be an outer join the join can’t be done on more than one column the join must be based on a column or columns that have the same name in both tables the statement can only join two tables

ANS: C

From Chapter 5 1.The six clauses of the SELECT statement must be coded in the following order: a. b. c. d.

SELECT, SELECT, SELECT, SELECT,

FROM, FROM, FROM, FROM,

GROUP BY, HAVING, WHERE, ORDER BY WHERE, ORDER BY, GROUP BY, HAVING WHERE, GROUP BY, HAVING, ORDER BY ORDER BY, WHERE, GROUP BY, HAVING

ANS: C 2. Expressions coded in the HAVING clause a. b. c. d.

can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can’t use non-aggregate search conditions can use non-aggregate search conditions but can’t use aggregate search conditions can refer to any column in the base tables

ANS: A 3. Expressions coded in the WHERE clause a. b. c. d.

can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can’t use non-aggregate search conditions can use non-aggregate search conditions but can’t use aggregate search conditions must refer to columns in the SELECT clause

ANS: C

4. Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_id, SUM(invoice_total - payment_total - credit_total) AS column-2 FROM invoices WHERE invoice_total - payment_total - credit_total > 0 GROUP BY vendor_id

a. b. c. d.

The unpaid balance for each invoice The total unpaid balance due for each vendor_id The total amount invoiced by each vendor_id The total of paid invoices for each vendor_id

ANS: B 5. Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1

a. b. c. d.

The names of the vendors in each state The duplicate vendors from each state The number of vendors in each state The number of vendors in each state that has more than one vendor

ANS: D Code example 5-1 SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name

6. (Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for a. each city with invoice totals over $500 c. each city with invoice average over $500 b. each vendor with invoice totals over $500 d. each vendor with invoice average over $500 ANS: B 7. (Please refer to code example 5-1.) Although this query runs as coded, it contains this logical error: a. The condition in the WHERE clause should be coded in the HAVING clause. b. The column name for the fifth column in the result set doesn’t match the data. c. The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. d. The condition in the HAVING clause should be coded in the WHERE clause. ANS: B

From chapter 6

1.If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery)

a. b. c. d.

a single value a column of one or more rows a table a subquery can’t be introduced in this way

ANS: B 2. If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)

a. b. c. d.

a single value a column of one or more rows a table a subquery can’t be introduced in this way

ANS: D 3. If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)

a. b. c. d.

a single value a column of one or more rows a table a subquery can’t be introduced in this way

ANS: A 4. If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)

a. b. c. d.

a single value a column of one or more rows a table a subquery can’t be introduced in this way

ANS: B 5. If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)

a. a single value

b. a column of one or more rows c. a table d. a subquery can’t be introduced in this way ANS: A 6. If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)

a. b. c. d.

a single value a column of one or more rows a table a subquery can’t be introduced in this way

ANS: C Code example 6-1 SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC

7. (Please refer to code example 6-1.) When this query is executed, the result set will contain a. one row for each invoice that has a larger balance due than the average balance due for all invoices b. one row for the invoice with the largest balance due for each vendor c. one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices d. one row for each vendor that shows the largest balance due for any of the vendor’s invoices, but only if that balance due is larger than the average balance due for all invoices ANS: D 8. (Please refer to code example 6-1.) When this query is executed, the number_of_invoices for each row will show the number a. b. c. d.

1 of invoices in the Invoices table of invoices for each vendor of invoices for each vendor that have a larger balance due than the average balance due for all invoices

ANS: D 9. (Please refer to code example 6-1.) When this query is executed, the rows will be sorted by

a. b. c. d.

invoice_id vendor_id balance_due in descending sequence vendor_id and then by balance_due in descending sequence

ANS: C Code example 6-2 WITH invoice_averages AS (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100 ORDER BY average_invoice DESC) SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN invoice_averages ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC

10. (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for a. each vendor c. each invoice b. each vendor in the invoice_averages table d. each invoice in the invoice_averages table ANS: B 11. (Please refer to code example 6-2.) When this query is executed, each row in the result table will show a. the largest invoice total related to that row b. the average invoice total related to that row c. the largest invoice total related to that row, but only if it’s larger than the average for all invoices d. the average invoice total related to that row, but only if it’s greater than 100 ANS: A 12. (Please refer to code example 6-2.) When this query is executed, there will be one row a. b. c. d.

for each vendor for each vendor with an average invoice total that’s greater than 100 for each vendor with a maximum invoice total that’s greater than 100 for each invoice with an invoice total that’s greater than the average invoice total for the vendor and also greater than 100

ANS: B

From Chapter 7 1.Which of the following statements is not true when you use Oracle SQL Developer to run INSERT, UPDATE, and DELETE statements?

a. b. c. d.

By default, the changes are automatically commited to the database. You can rollback the changes by clicking on the Rollback button. You can commit the changes by clicking on the Commit button. By default, the changes are rolled back when you exit SQL Developer unless you commit them.

ANS: A 2. Assuming that all of the table and column names are spelled correctly, what’s wrong with the INSERT statement that follows? INSERT INTO invoices (vendor_id, invoice_number, invoice_total, payment_total, credit_total, terms_id, invoice_date, invoice_due_date) VALUES (97, '456789', 8344.50, 0, 0, 1, '31-AUG-08')

a. b. c. d.

The column names in the column list are in the wrong sequence. There are too few items in the column list. There are too many items in the VALUES list. The number of items in the column list doesn’t match the number in the VALUES list.

ANS: D 3. Which of the following statements best describes what this INSERT statement does? INSERT INTO invoices_copy SELECT * FROM invoices WHERE terms_id = 1

a. Adds one row from the Invoices table to the Invoices_Copy table. b. Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table. c. Adds all of the rows in the Invoices table to the Invoices_Copy table and sets the terms_id column to 1 in each row. d. Updates all of the rows in the Invoices_Copy table that have 1 in the TermsID column to the rows in the Invoices table. ANS: B From chapter 8 1.All of the following values can be stored in a column that’s defined as NUMBER(6,2), except a. -246.29 b. 0

c. 2479.95 d. 32492.05

ANS: D 2. Which uses the least amount of storage? a. 'example' stored in a column of type CHAR(20) b. 'exam' stored in a column of type CHAR(20) c. 'ex' stored in a column of type CHAR(20)

d. they all use the same amount of storage ANS: D 3. Which uses the least amount of storage? a. b. c. d.

'example' stored in a column of type VARCHAR2(20) 'exam' stored in a column of type VARCHAR2(20) 'ex' stored in a column of type VARCHAR2(20) they all use the same amount of storage

ANS: C 4. The DATE data type can store a. dates only b. dates and times

c. times only d. all of the above

ANS: B 5. What does the function that follows return? TO_CHAR(1234, '$99,999.99')

a. $12,340.00 12.34 b. $

c. $ 1,234.00 d. $1,234.00

ANS: D 6. If first_name contains Edward and last_name contains Williams, what will the Solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution

a. EWilliams b. EWilliam

c. ewilliams d. ewilliam

ANS: D 7. Which of the following is the best way to sort a VARCHAR2 column that contains numbers in numeric sequence? In the ORDER BY clause a. b. c. d.

use the TO_NUMBER function to convert the column values to numbers use the RPAD function to add zeros to the column values use the TO_CHAR function to format the all the numbers in the column the same way use the TRIM function to trim off any leading zeros in the column so all the numbers will be formatted the same way

ANS: A 8. If expiration_date contains a value that’s equivalent to November 2, 2008 and the SYSDATE function returns a value that’s equivalent to December 17, 2008, what will the exp_days column contain when this code is executed? expiration_date - SYSDATE AS exp_days

a. 1.5

c. 45

b. -1.5

d. -45

ANS: D 9. Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that’s defined with the DATE data type? a. use the TRUNC function to truncate the time values from the date value in the column that you’re searching b. search for date values that fall between the day and the day after the date you want c. use the TO_DATE function to remove the time values from the dates in each column ANS: C 10. Which of the following statements best describes what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN (SYSDATE - invoice_date) >= 30 AND (SYSDATE - invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN (SYSDATE - invoice_date) >= 60 AND (SYSDATE - invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN (SYSDATE - invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices

a. Displays three columns for each invoice with a value in the third column that indicates how many days have elapsed between the invoice date and the current date. b. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date. c. Displays three columns for each invoice with one of these messages in the third column: 30-60, 60-90, or Over 90. d. Displays five columns for each invoice with a message in one of the last three columns based on how many days have elapsed between the invoice date and the current date. ANS: B...