QuestionQuestion

Instructions
Consider the multi-table third normal form (3NF) database specified in the file "Database.txt” attached. The database is used by an e-commerce website that sells a variety of products. Customers go to the web site, select products based on product categories and sets of product features, and fill a shopping cart with products that they want to buy. Later on, they place an order for one or more products that exist in the shopping cart. Once an order is placed, all the items that were bought are removed from the shopping cart and added to the ordered items table. The shopping cart has also the ability to hold products as part of a wish list, which the customer is interested in but hasn’t decided yet to buy.

Use the file “Database.txt” to study and understand the structure of this database. Use it also to create and populate the database on your personal Oracle account. Then generate SQL code to answer the following questions:

Show the shopping cart content for the customers with word ‘David’ in their full name, sorted with the wish list at the end
List of ALL customers and the total price of their shopping carts, excluding the wish list
List ALL shopping carts in descending order of the number of items that they hold, excluding the wish list
List ALL products and the number of shopping carts they are in (if any)
List the products with a 'Brand' feature in ‘Outdoors’ and ‘Electronics’ categories
Create a view of unshipped goods, with individual quantities and prices, for each customer, and select all the rows from it. An order item is unshipped if the DateShipped attribute in set to NULL
Using the view created before, display all customers who have unshipped orders, together with the total value of the unshipped orders per customer
List the first three most sold products of category 'Books' (don't count unshipped orders)
Display the number of ‘HP’ brand products sold during the last month (from current date)
Use a correlated query to list the names of the customers who have more than 2 copies of the same item in their shopping cart
List of shopping carts, together with the cheapest and the most expensive product in each.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

--1. Shopping cart content for the customer with word David in their full name, sorted with the wish list at the end

SELECT c.NAME as CUSTOMER, p.NAME as "PRODUCT NAME",
p.PRICE as "UNIT PRICE ", si.QUANTITY ,
si.WISHLIST as "IN WISH LIST"
FROM CUSTOMERS c ,SHOPPING_CART_ITEMS si, PRODUCTS p
where c.ID = si.CUSTID and c.NAME like '%David%'
and si.PRODID = p.ID
order by c.NAME asc , si.WISHLIST desc
;

-- 2. List of ALL customers and the total price of their shopping carts, excluding the wish list

select s1.NAME,
sum(s1.QUANTITY * p.PRICE) as "CART VALUE [$]"
from
(
SELECT c.NAME, si.PRODID, si.QUANTITY, si.WISHLIST
FROM
CUSTOMERS c left join SHOPPING_CART_ITEMS si
on c.ID = si.CUSTID
where si.WISHLIST <> 'Y' or si.WISHLIST is null
) s1 left join PRODUCTS p
on s1.PRODID = p.ID
group by s1.NAME;...
$83.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats