DB Fundamentals SQL

Assignment: DB Fundamentals SQL (100 points)

Read the brief case below. Using Oracle Application Express, complete the questions.

Atlas Bikes

Atlas Bikes has been selling their bikes since 1975 in the United States. They expanded to Australia and Germany in the year 2015. The bikes are sold via a trusted network of sales outlets, which are not owned by Atlas Bikes. As a measure to improve their footprint in the market, Atlas sales wants to open new stores which will be owned by them. To do this, the Senior Management wants to review the numbers and come up with their expansion strategy.

Use the ER Diagram below to understand the Data Model

DB Fundamentals SQL 1

5 points each

List the name and population for each country.

How many Products are sold by Atlas Bikes?

List all the Sales Outlets that are in the United States.[Hint: The COUNTRY_ID of United States is 1003]

List the names all the Product_Categories.

How many Distribution Channels are there? Name the column as “Number of DC”.

What was the Total Revenue generated from Germany in the years 2015 and 2016? [Hint: The Country_ID of Germany is 1002. Use the SUM function. Also use to_char to change the format of the revenue]. Call the header “SALES_TOT_GERMANY” and display the result in US Dollars.

List the PRODUCT_NAME and PRODUCT_CATEGORY_NAME for each of the Products. Sort the list in the Descending order of the Product_Name[Hint: Use an INNER JOIN and table name aliases to write your query].

What was the Total Revenue for Atlas Bikes for the year 2015 and 2016. Format the revenue to show the unit of measure as US Dollars. [ Hint: Use GROUP BY expression to aggregate the revenue for 2015 and 2016. Use ‘to_char’ to change the format of the revenue]

For the year 2015, list the Quantity of Bikes sold by each Sales Outlet. The header for the Sales Outlet name would be “Outlet Name” and the Sales Quantity should be marked as “Quantity Sold”. [Hint: Use INNER JOIN on Sales and Sales_Outlet tables. Also use the GROUP BY expression]

List the Sales Outlet Names and their Total Revenue for 2015 and 2016. Show the revenue in US Dollars. The header for the total revenue should be REVENUE.