View the step-by-step solution to:

Final Exam CIT113 Spring 2010 1. Provide the SQL statement to produce the report displaying the changes in the following request. NOTE: this SQL you...

How do I do this attached document?

Final Exam CIT113 Spring 2010 1. Provide the SQL statement to produce the report displaying the changes in the following request. NOTE: this SQL you are to create only affects the report. The actual changes to the price are not part of this exercise . We are going to increase the price of our books in the course database at a later date. The amount of the increase will be based upon the books category and is listed below. 10p computer books: 10% fitness books: 15% self-help books: 20% all other categories: 7% The report to be created will list: Book title, category, current retail price, and the revised retail price. The original and revised price should have two decimal places, and create the column headers as follows: Title, Category, Current Price, New Price. Sort on the book's category and within that sort a secondary sort on the title should occur. 2. Provide the SQL statement necessary to determine which books have less than a 55% profit, and how many copies of those books have been sold. 10p 3. Provide the SQL statement necessary to list the five most frequently purchased books, and their associated percentage of profit. Use the formula ((retail-cost)/cost*100) as you have seen in your text book. Provide the isbn, the total quantity, and the percentage of profit in your output. Important! Only show the FIVE most frequently purchased. 10p For questions 4 through 9, answer based upon successful execution of the following SQL statement. Create view changeaddress As select c.customer#, c.lastname, c.firstname, o.order#, o.shipstreet, o.shipcity, o.shipstate, o.shipzip From customers c, orders o Where c.customer# = o.customer# And shipdate is null With check option; 4. Assuming that there is only a primary key and that foreign key constraints exist on the underlying tables, which of the following commands will return an error message? 5p a. update changeaddress set shipstreet = ‘123 Elm Street’ where customer# = 1020; b. insert into changeaddress values(9999, ‘LAST’, ‘FIRST’, 9999, ‘123 My Avenue’, ‘Your Town’, ‘MA’, 99999); c. delete from changeaddress where customer# = 1020; d. all of the above e. only a and b. f. only b and c. g. none of the above 5. Which of the following columns can serve as the primary key for the changeaddress view? 5p a. lastname b. firstname c. order# d. shipstreet e. None of the above 6. Which of the following is the key-preserved table for the changeaddress view? 5p 1
Background image of page 1
a. customers table b. orders table c. both tables together serve as a composite key-preserved table d. none of the above 7. If a record for a customer is deleted from the changeaddress view, the customer information is then deleted from which underlying table? 5p a. customers b. orders c. customers and orders d. neither, the delete command cannot be used on the changeaddress view 8. Which of the following is correct? 5p a. rownum cannot be used with the view because it is not included in the results returned from the subquery b. the view is a simple view because it does not include a group function or a group by clause c. the data in view cannot be presented in descending order by customer number because an order by clause is not allowed when working with views. d. All of the above e. None of the above 9. Assuming one of the orders has been shipped, which of the following is true? 5p a. the changeaddress view cannot be used to update the shipping date of the order due to the with check option constraint. b. the changeaddress view cannot be used to update the shipping date of the order because the ship date column is not included in the view. c. the changeaddress view cannot be used to update the shipping date of the order because the orders table is not the key preserved table. d. the changeaddress view cannot be used to update the shipping date of the order because the update command cannot be used on the data in the view. 10.A complex view can contain which of the following? 5p a. Data from one of more tables. b. An expression. c. A GROUP BY clause for data retrieved fro one table. d. Five columns from one table. e. All of the above. f. None of the above. 11. Which of the following commands can be used to instruct Oracle to use a particular index when a SELECT statement is being executed? 5p a. ALTER SESSION b. ALTER INDEX c. SELECT . .. FOR UPDATE . .. USING d. None of the above. Using the script, “final_exam_db.sql” run the script to answer questions 12 & 13. 12. Provide the SQL statements used to execute the following exercises: 5p Create a new table called “non_appliance” using the columns from the existing tables: Part_num Description On_hand Class 2
Background image of page 2
Show entire document

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question