BC JUSTLEE Books Activity Worksheet

There are two different things

Scenario Overview

Your boss, Michael, is back in the office. The office ordered pizza, and a working lunch ensued. You are congratulated for successfully completing adding join conditions that combined data from more than one table. Last week, you were not feeling very confident because you had only worked with one table, but now you have experience with queries that combine data from more than one table. You were really pleased with yourself because you figured it out on your own without needing to contact Michael at his conference. Michael is all fired up from his conference and full of new ideas and innovative ways to run data, among other things, for his small staff. He is particularly excited to have you play around in the database with the single-row and multiple-row SQL functions. He also wants to introduce you to character and date functions and other functions and regular expressions.

Image credits: People Eating Pizza. Canva.

Work Project Details

Michael explains that he is interested in more fine-grained reports that include formatting results different from that shown in the database. He knows that you are up for the task and has given you the week to work through this small project. At the end of the week, he also wants to chat with you about your long-term plans for coming aboard full-time with JustLee Books. To perform the following assignment, refer to the tables in the JustLee Books database.

1. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).
2. Produce a list of all customer names in which the first letter of the first and last names is in uppercase, and the rest are in lowercase.
3. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text “NOT REFERRED” if the customer wasn’t referred to JustLee Books by another customer or “REFERRED” if the customer was referred.
4. Determine the amount of total profit generated by the book purchased on order 1002.
5. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.
6. Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amounts as a percent of the cost.)
7. Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you’re using.
8. Create a list of all book titles and costs. Precede each book’s cost with asterisks so that the width of the displayed Cost field is 12.
9. Using today’s date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age.
10. Determine the calendar date of the next occurrence of Wednesday, based on today’s date.
11. Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number if it exists Second assignment Scenario Overview You have been getting up to speed with grouping functions after that challenging project last week. You continue to learn about character and date functions, and other functions and regular expressions. Now that you understand and have a good grasp of grouping functions, your boss is excited to have you learn more about groups and how they compare to one another. Michael shared that groups can be created by any of the attributes of your database tables. He is ready for you to start thinking about how orders are grouped by customer, customers by state, profit by publisher, or any other number of attributes grouped by another attribute. (ERD – Figure 1.5 is attached).Image credits: Fresh Coffee, Canva and Laptop, Coffee Cup, Graph Chart on a Wooden Surface, Canva.

12. Work Project Details

    First, you better get a tall and hot cup of coffee because, in this next small project, your boss has a renewed interest in orders. He is apparently still excited about the rich information he discovered at that conference a couple of weeks ago. Your boss wants to know where orders are coming from and which books, categories, and publishers are most profitable. He knows that you are up for the task and has given you a week to work through this small project. Last week was pretty busy, and the two of you did not get a chance to chat about your long-term plans. To perform the following assignment, refer to the tables in the JustLee Books database.

    1. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn’t recently placed an order with JustLee Books.
    2. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
    3. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.
    4. What’s the retail price of the most expensive book written by Lisa White?
    5. Determine how many books are in the Cooking category.
    6. Display the number of books with a retail price of more than $30.00.
    7. Display the most recent publication date of all books sold by JustLee Books.
    8. Determine the total profit generated by sales to customer 1017. Note: The total profit calculation should reflect the quantity.
    9. List the retail price of the least expensive book in the Computer category.
    10. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.

    Scenario Overview

    You have been getting up to speed with grouping functions after that challenging project last week. You continue to learn about character and date functions, and other functions and regular expressions. Now that you understand and have a good grasp of grouping functions, your boss is excited to have you learn more about groups and how they compare to one another. Michael shared that groups can be created by any of the attributes of your database tables. He is ready for you to start thinking about how orders are grouped by customer, customers by state, profit by publisher, or any other number of attributes grouped by another attribute. (ERD – Figure 1.5 is attached).Image credits: Fresh Coffee, Canva and Laptop, Coffee Cup, Graph Chart on a Wooden Surface, Canva.

    Work Project Details

    First, you better get a tall and hot cup of coffee because, in this next small project, your boss has a renewed interest in orders. He is apparently still excited about the rich information he discovered at that conference a couple of weeks ago. Your boss wants to know where orders are coming from and which books, categories, and publishers are most profitable. He knows that you are up for the task and has given you a week to work through this small project. Last week was pretty busy, and the two of you did not get a chance to chat about your long-term plans. To perform the following assignment, refer to the tables in the JustLee Books database.

    1. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn’t recently placed an order with JustLee Books.
    2. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
    3. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.
    4. What’s the retail price of the most expensive book written by Lisa White?
    5. Determine how many books are in the Cooking category.
    6. Display the number of books with a retail price of more than $30.00.
    7. Display the most recent publication date of all books sold by JustLee Books.
    8. Determine the total profit generated by sales to customer 1017. Note: The total profit calculation should reflect the quantity.
    9. List the retail price of the least expensive book in the Computer category.
    10. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.
        

        Scenario Overview

        You have been getting up to speed with grouping functions after that challenging project last week. You continue to learn about character and date functions, and other functions and regular expressions. Now that you understand and have a good grasp of grouping functions, your boss is excited to have you learn more about groups and how they compare to one another. Michael shared that groups can be created by any of the attributes of your database tables. He is ready for you to start thinking about how orders are grouped by customer, customers by state, profit by publisher, or any other number of attributes grouped by another attribute. (ERD – Figure 1.5 is attached).Image credits: Fresh Coffee, Canva and Laptop, Coffee Cup, Graph Chart on a Wooden Surface, Canva.

        Work Project Details

        First, you better get a tall and hot cup of coffee because, in this next small project, your boss has a renewed interest in orders. He is apparently still excited about the rich information he discovered at that conference a couple of weeks ago. Your boss wants to know where orders are coming from and which books, categories, and publishers are most profitable. He knows that you are up for the task and has given you a week to work through this small project. Last week was pretty busy, and the two of you did not get a chance to chat about your long-term plans. To perform the following assignment, refer to the tables in the JustLee Books database.

        1. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn’t recently placed an order with JustLee Books.
        2. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
        3. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.
        4. What’s the retail price of the most expensive book written by Lisa White?
        5. Determine how many books are in the Cooking category.
        6. Display the number of books with a retail price of more than $30.00.
        7. Display the most recent publication date of all books sold by JustLee Books.
        8. Determine the total profit generated by sales to customer 1017. Note: The total profit calculation should reflect the quantity.
        9. List the retail price of the least expensive book in the Computer category.
        10. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.