Question
please help w/ queries 4&5 thx.
PROJECT TASK 7: DML AND REPORTING Overview of Project The class project will require students to apply the knowledge they learn in class in a practical manner. Each student will develop an E-Commerce database used to maintain customers, products and sales information. You are required to 1) gather and analyze requirements 2) design logical structure of the database 3) create stored procedures to develop the tables and insert the data 4) write SQL statements for data extraction and reporting. All work for this project MUST be done on your own. This is an individual assignment, not group assignment. Anybody caught copying the work of others will receive a zero for the project Project Task 7: Background Throughout the course of this semester you have analyzed the requirements for an eCommerce database, designed and developed your database. As a class we have gone through the process of writing the DDL scripts to create our database and the DML scripts to load the data. Now that we have a functional database loaded with data, we can start working with our database and performing business functions. Maintaining the data and reporting are two tasks that are performed daily on all databases. Data is constantly being inserted, updated and deleted. Managers need reports and users execute queries to look up inform ation. We will first take a look at some queries to get an understanding ho uced Then ou will write your own queries for reporting
Explanation / Answer
There are two tables, based on which I am using SQL query to generate the Reports for 4, 5.
Purchase_Summary containing orderid, orderdate, userid
Purchase_Details containing itemid, orderid, category [this field contains the name of the category like “Books”,”Music”, to which itemid belongs] ItemPayment [this field contains the payment made for that particular itemid]
Report 1:- As a Report we need a list of all userid and the total payment received from them in the year 2013.
Report 2:- We need a report consisting list of all userids which have bought for more than 10,000 Rs. of value in 2012 but not bought at all in 2013.
Please let me know in case of any clarifications required. Thanks!
