Question: SQLITE3 QUERY QUESTION For each user who has had some bids, list the user email, the total number of distinct sales the user has had

SQLITE3 QUERY QUESTION

For each user who has had some bids, list the user email, the total number of distinct sales the user has had bids on, the number of winning bids, and the total dollar amount of the wining bids. The user has a winning bid if the sale is over (i.e., the end date is in the past), the user has the highest bid and the bid amount is not less than the reserved price. The result should include users who have no winning bids. Hint: subqueries can be used in the from clause.

users(email, name, pwd, city, gender)
products(pid, descr)
sales(sid, lister, pid, edate, descr, cond, rprice)
bids(bid, bidder, sid, bdate, amount)
items(sid, no, pid, descr)
reviews(reviewer, reviewee, rating, rtext, rdate)
previews(rid, pid, reviewer, rating, rtext, rdate)

Clarifications: The lister is represented by an email, bidder is represented by an email.

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

In sqlite create table users ( email char(20), name char(16), pwd char(4), city char(15), gender char(1), primary key (email) ); create table products ( pid char(4), "This is the product id" descr...

in sqlite these are the tables: create table users ( email char(20), name char(16), pwd char(4), city char(15), gender char(1), primary key (email) ); create table products ( pid char(4), descr...

In sqlite please users(email, name, pwd, city, gender) products(pid, descr) sales(sid, lister, pid, edate, descr, cond, rprice) bids(bid, bidder, sid, bdate, amount) items(sid, no, pid, descr)...

question? Create a view called product_info with columns pid, descr, revcnt, rating, rating6, and salecnt. The view includes for each product the product id, the description, the number of reviews,...

The Answers have to be written in SQlite 3. You have been provided with the following relational schema. users(email, name, pwd, city, gender) products(pid, descr) sales(sid, lister, pid, edate,...

In SQL users(email, name, pwd, city, gender) products(pid, descr) sales(sid, lister, pid, edate, descr, cond, rprice) bids(bid, bidder, sid, bdate, amount) items(sid, no, pid, descr)...

There are two problems due this week (each worth 35 points) as follows. Case 5-1David L. Miller: Portrait of a White-Collar Criminal (page 144). In comprehensive paragraphs, answerrequirements 1?6....

I have problems understanding this case study for database. can someone help me solve this. Deliverables: 1. Create a list with the main business rules 2. Identify the main Entity types and their...

# import the sqlite3 database module import sqlite3 print("SQLite Practice - Example User Table ") # create a connection to the database file conn = sqlite3.connect("myDatabase.db") # create a cursor...

A small pollution particle settles toward the earth in still air with a terminal speed of 0.3 mm/s. The particle has a mass of 10 10 g and a retarding force of the form bv. What is the value of b?

Assume that the production of cement also produces a substantial amount of air pollution and that a technology is available that can lower the pollution but with somewhat higher production costs for...

Which of the following is false about residual income? It creates the underinvestment problem. It is calculated by subtracting the cost of capital ( in $ ) from accounting - based profit. It can be...

CT Corp Comprehensive Question Canadian Tire Corporation, Limited (Canadian Tire) is a family of companies that includes a retail segment and a financial services division, among others. The retail...

1. How have the origins of the study of intercultural communication in the United States affected its present focus?

7. Identify six intercultural communication dialectics.

3. How have the worldviews of researchers influenced how they studied intercultural communication?