Please, I am using APEX ORACLE as the platform to write queries $.$ ESPECIALLY QUESTION $9$ IS VERY DIFFICULT.

Write SQL queries to answer the following questions:

$1.$ List the total number of customers.

$2.$ For each order, list the order ID$,$ customer ID$,$ customer name, and store ID$.$

$3.$ For the products that are ordered by both Fred Fontain and Judy Evans, list the product

ID and product name.

Select c$1.$customer$\_$id$,$ c$1.$full$\_$name, c$2.$customer$\_$id$,$ c$2.$full$\_$name, i$1.$line$\_$item$\_$id

From customers c$1,$ orders o$1,$ order$\_$items i$1,$ customers c$2,$ orders o$2,$ order$\_$items i$2$

Where c$1.$customer$\_$id$=$o$1.$customer$\_$id and o$1.$order$\_$id$=$i$1.$order$\_$id and

C$2.$customer$\_$id$=$o$2.$customer$\_$id and o$2.$order$\_$id$=$i$2.$order$\_$id and

C$1.$customer$\_$id c$2.$customer$\_$id and i$1.$line$\_$item$\_$id$=$i$2.$line$\_$item$\_$id;

$4.$ Retrieve the names of the products that are ordered in store $$London$$;

IFSC $7320$ Database System$/$Information Architecture

$4$

$5.$ For each store, list the store name, and total number of orders it received.

$6.$ List the name of the store that received the greatest number of orders.

$7.$ List the names of the customers who bought the Boy$$s Shirt $($White$).$

$8.$ For the customers who bought Girl$$s Jeans, list their names and the names of stores that they

bought those jeans.

$9.($extra credit $10$ points$)$ List the name of the customer who spent the highest amount of money

in all orders.

$4.$ view Create a view ORDERINFO, whose schema is as follows:

ORDERINFO$($ORDER$\_$ID$,$ LINE$\_$ITEM$\_$ID$,$ PRODUCT$\_$ID$,$ UNIT$\_$PRICE, QUANTITY, subtotal$)$

Here, subtotal is the total cost of a specific product of an order, and is calculated as

unit$\_$price $*$ quantity

$5.$Data analysis

Assume you are a business analyst, and you need to analyze sales from different angles so that you

can answer the following questions.

Note that if it is hard to write a single SQL query to answer the question, then you may try to use

subquery, views, anonymous block, stored functions$/$procedure$,$ cursor, etc. You only need to

submit the SQL and$/$or PL$/$SQL codes for each question.

a$)$ Which products are sold below the listed unit price $($in PRODUCT table$)?$ What is the total

loss of revenue incurred by the sales of the products?

b$)$ Which product has the lowest sales number$($ i$.$e$.,$ the total number of items sold$)$

c$)$ For each store, list the name of the customer who spent the highest amount of money.

d$)$ For each store, list the products that generated the highest sales in dollar amount.

e$)$ What are the sales $($in total number of products $)$ of each store by month? List the store id$,$

store name, month, and total sales.

Extra credit: $(5$ points each, $10$ points in total$)$: pick any two questions.

a$)$ What is the total sales in dollar amount by country

b$)$ What is the total sales in dollar amount by different type of products $($you may infer the type

of products by product names and have your definition of product types$)(5$ points$)$

c$)$ For each type of products, which store generates the highest sales in dollar amount?

d$)$ For each type of products, which month has the highest sales in dollar amount?

e$)$ Which product has the highest total sales in dollar amount?

f$)$ What is the total sales in dollar amount from the US stores?

$6.(10$ points$)$ Relative support of itemset.

IFSC $7320$ Database System$/$Information Architecture

$5$

For each product, list the product ID and its relative support. Note the relative support of a product

equals

The number of orders contain the product$/$total number of orders

For this question, you can either

$$ write a SQL query for this question, or

$$ write a stored function, which takes a product ID as an input, and returns the relative

support of the product. Then write a SQL query to list product IDs along with their relative

support measures.

Order$\_$item Table with COLUMN TITLES

Order$\_$Table with COLUMN TITLES

Production$\_$Table with COLUMN TITLES

Store$\_$Table with COLUMN TITLES

Customer$\_$Table with COLUMN TITLES