Question

I'm working on this problem in DB Browser for SQLite. Here are the questions: ----- 3....

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)

Homework Answers

Answer #1
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 :)

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Create a program which uses each struct declared in problem 1 and accesses each member of...
Create a program which uses each struct declared in problem 1 and accesses each member of each struct. (a) Create a variable using each struct and initialize it with some mock data of your choosing. (b) Print the members of each struct separated by a newline. (c) Modify each member of each struct to something different. (d) Print all members again. Save your code as prob2.c. This is Problem 1: struct ProductData {     int ID;     char Name[50];     float Price;     int...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment 4’s schema (Customer-Invoice-Line-Product-Vendor). Make sure that your SQL script runs without any errors. Submit your answers in a .SQL file. 1 (2 Points) - Find the count of distinctvendors thatsupplied products that are priced lowerthan 185? 2 (2 Points) - For each vendor, find their product that has the lowest product quantity. Your output should include vendor code, vendor name, product description and product...
Hello, can you teach me how to draw a Use Case diagram for this given scenario?...
Hello, can you teach me how to draw a Use Case diagram for this given scenario? I also need to include all the extends and includes relationships. Case Description Acme Drug Stores (ADS) which consists of a chain of retail stores across Canada wants to be able to provide a new online customer service system. Each store has an identifying store no, address, phone, and email. The company dispenses a wide range of pharmaceutical (drug) products. Each product has a...
**[70 pts]** You will be writing a (rather primitive) online store simulator. It will have these...
**[70 pts]** You will be writing a (rather primitive) online store simulator. It will have these classes: Product, Customer, and Store. All data members of each class should be marked as **private** (a leading underscore in the name). Since they're private, if you need to access them from outside the class, you should do so via get or set methods. Any get or set methods should be named per the usual convention ("get_" or "set_" followed by the name of...
What Dimensions and Measures would be required by the management of a "big box" retail chain...
What Dimensions and Measures would be required by the management of a "big box" retail chain store (e.g., Costco or Sam's Club)? What various Levels would exist within the Dimensions? What Technical Metadata and Business Metadata should be included in the Dimensional Modeling for such an organization's data? This is what i have so far, i would just need help with the second part of the question involving metadata: Some of the dimensions needed would be a customer table, product...
Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you...
Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you can submit a zipped file containing the text file. Word, PDF, or Image format are not accepted. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle 11g. Assumptions: Each tool belongs to a category. Each category may have a parent category but the parent category should not have parent category (so at most two levels)....
Use the following table to answer the next questions (11-14). (T1 to T5 is the type...
Use the following table to answer the next questions (11-14). (T1 to T5 is the type of the consumer and from each type there is one consumer, to be clear there is a total of 5 consumers in the market ) Customer Product A Reservation Price Product B Reservation Price T1 180 170 T2 120 140 T3 100 80 T4 90 120 T5 90 80 Marginal Cost 80 70 11.  If the firm does not bundle the products, what single price...
Problem 1 (Defining products and Creating a Menu) Write a program called a2_p1.py that asks a...
Problem 1 (Defining products and Creating a Menu) Write a program called a2_p1.py that asks a shop owner for product information and creates a menu system that allows customers to purchase multiple products of varying quantities. The program should start by asking the shop owner’s name (string) and company name (string). Next it should ask for three (3) products (strings), their prices (int – not ideal, but for now we will keep it this way), and the number of products...
Using the information for Headley Company from the previous problem, now assume there are no market...
Using the information for Headley Company from the previous problem, now assume there are no market constraints for either product, leaving the 250,000 labor hours as the only constraint and that Headley will make optimal use of its labor.  In early November, Headley was invited to bid on a contract to provide 2,000 units of Product B. What is the minimum bid Headley would have to submit in order to breakeven on the contract? Product A                    Product B Per Unit Information: Selling...
Instructions: Step 1: Create UNF Relation from a User View. The goal here is to create...
Instructions: Step 1: Create UNF Relation from a User View. The goal here is to create a single relation for the data found in the user view. The method used is: •   Examine the user view •   Identify all attributes •   Describe the user view using DBDL Here is an example of a user view. This reports lists the customers of the Premiere Corporation. Premiere Corporation Customer List CustNo   Name   Street   City   State   ZipCode   SalesRepNo 124    Sally Adams   482...