I'm working on this problem in DB Browser for SQLite. Here are the questions:
-----
3. Write the queries for the following request on your database, take the screenshots of the results and submit.
a. What are the name and phone number of a customer with ID = 100?
b. What are the name and price of a product with ID = 1000?
c. What are the total sales (money) of a product with ID = 1000?
d. What are the products and total units (sum of quantity) and total sales (money) corresponding with each product bought by a customer with ID = 100?
e. What is the product with highest total sales?
------
tables that I have:
Customer (fields for name, address, phone number, customer id)
Products (fields for name, price of item, number of item in stock, product id)
Purchases (fields for quantity purchased, customer id, and product id)
3.a)
Select name, phoneNumber from Customer where CustomerId = 100;
b)
Select name, price from Products where productId = 1000;
c)
Select sum(price) from Products where productId = 1000;
d)
Select Products.name, Purhcases.sum(quantity_purchased), Products.sum(price) from Products, Purchases, Customer where Customer.customerId = 100 and Customer.name = Products.name and Products.productId=Purchases.productId;
e.
Select name from Products where price = (select max(price) from products);
Note: If you have any doubts or queries, feel free to ask by commenting down below.
And if my answer suffice to the requirements, then kindly upvote as an appreciation
Happy Learning :)
Get Answers For Free
Most questions answered within 1 hours.