ISM 2200C Broward College Business

Scenario 7

You are the rock star of the office yet again. You proved that you could retrieve data from orders grouped by customer, customers by state, profit by publisher, or any other number of attributes grouped by another attribute. In fact, you developed an executive summary report with descriptive statistical findings for your boss to review at the end of last week. He was extremely impressed with your work. In fact, your report was so impressive it led to revisiting the conversation about your future.

Welcome to your first week of full-time work at JustLee Books as a data analyst! Your boss has planned an after-work social at the pub next door to welcome you to the team officially. When you arrive at the office, your boss asks to see you. He shares that subqueries are very helpful, and now he is interested in how you can write more dynamic queries that don’t have to be changed every time the underlying data changes. (ERD – Figure 1.5 is attached).

Image credits: Photo of People Holding Each Others’ Hands, Canva.

More specifically, your boss wants you to learn a different approach from separate queries that involves using a subquery to achieve the same output using a single SQL statement. He wants you to be thinking about when it is appropriate to use subquery and the type of subquery that can be used in a SELECT clause. He will be out of the office again this week, but he walks you through your next small project. He wants you to work through the following exercises to help you practice with more dynamic queries:

1. Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books.
2. List the title of all books in the same category as books previously purchased by customer 1007. Don’t include books this customer has already purchased.
3. List the shipping city and state for the order that had the longest shipping delay.
4. Determine which customers placed orders for the least expensive book (in terms of the regular retail price) carried by JustLee Books.
5. Determine the number of different customers who have placed an order for books written or co-written by James Austin.
6. Determine which books were published by the publisher of The Wok Way to Cook.
7. List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books.
8. Determine which books cost less than the average cost of other books in the same category.
9. Determine which orders were shipped to the same state as order 1014.
10. Determine which orders had a higher total amount due than order 1008. Scenario 8 You are a regular part of the team, and your colleagues know to come to you for all things database and talking through data reports that require more formatting results and greater detail. You are able to work independently in the database, and you have begun to make suggestions for how things can get done faster and more efficiently. In fact, some of your ideas have sparked new ideas for your boss as well. Now your boss is excited about two new things. First, limiting views onto all of the data by creating views, and second, creating a way to enter data using the MERGE command. (ERD – Figure 1.5). Image credits: Photo of People Looking at Laptop, Canva and Internet Safety Concept with Padlocked on Computer Keyboard, Canva.
    Your boss wants to simplify the queries that have to be written as well as secure data, preventing it from being compromised by prying eyes that might troll and lurk online. Views help achieve both of these things! You smile and think to yourself. I guess this is my next small project. You grab your tablet to take some notes related to each item he wants you to create for view, keeping in mind that some views are not updateable….he offers that this means you won’t be able to change data through view, although you will be able to query through view. You know you have your work cut out for you on this final small project. You already know you will be coming in early and working later most of this week.
    1. Select one of the books in the REORDERINFO view and try to change its ISBN. Was an error message displayed when performing this step? If so, what was the cause of the error message?
    2. Delete the record in the REORDERINFO view containing your name. (If you weren’t able to perform #6 successfully, delete one of the contacts already listed in the table.) Was an error message displayed when performing this step? If so, what was the cause of the error message?
    3. Issue a rollback command to undo any changes made with the preceding DML operations.
    4. Delete the REORDERINFO view.
    5. Create a view that lists the name and phone number of the contact person at each publisher. Don’t include the publisher’s ID in the view. Name the view CONTACT.
    6. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
    7. Create a view called HOMEWORK13 that includes the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make sure the view is created even if the FIRSTATTEMPT table doesn’t exist.
    8. Attempt to view the structure of the HOMEWORK13 view.
    9. Create a view that lists the ISBN and title for each book in inventory, along with the name and phone number of the person to contact if the book needs to be reordered. Name the view REORDERINFO.
    10. Try to change the name of a contact person in the REORDERINFO view to your name.
    11. Was an error message displayed when performing this step? If so, what was the cause of the error message?