Blog

Consider the multi-table third normal form (3NF) database specified in the file “Project 1 DDL.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 “Project 1 DDL.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 endList of ALL customers and the total price of their shopping carts, excluding the wish listList ALL shopping carts in descending order of the number of items that they hold, excluding the wish listList ALL products and the number of shopping carts they are in

(if any)List the products with a ‘Brand’ feature in ‘Outdoors’ and ‘Electronics’ categoriesCreate 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 theDateShipped attribute in set to NULLUsing the view created before, display all customers who have unshipped orders, together with the total value of the unshipped orders per customerList 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 cartList of shopping carts, together with the cheapest and the most expensive product in each.The second file attached (“Project 1 Results.txt”) contains the expected results for each of the queries. Try to make your queries match the content provided in this second file.Use your Oracle account to run these SQL statements and verify correctness. Submit the SQL statements as a text file following the document naming convention FirstLastP1.txt. Grading: This project is awarded 100 points, as follows: 9 points each of the SQL queries between #1 and #10, and 10 points for SQL query #11.