Question

CREATE TABLE customer (

   id INTEGER PRIMARY KEY,

   customer_name TEXT,

   contact_number TEXT

);

CREATE TABLE supplier (

   id INTEGER PRIMARY KEY,

   supplier_name TEXT UNIQUE,

   contact_number TEXT

);

CREATE TABLE product (

   id INTEGER PRIMARY KEY,

   supplier_id INTEGER REFERENCES supplier(id),

   product_name TEXT,

   product_price INTEGER,

   UNIQUE(supplier_id, product_name)

);

CREATE TABLE purchase (

   id INTEGER PRIMARY KEY,

   customer_id INTEGER REFERENCES customer(id),

   purchase_date REAL,

   store_id INTEGER REFERENCES store(id)

);

CREATE TABLE store (

   id INTEGER PRIMARY KEY,

   store_name TEXT,

   store_address TEXT

);

CREATE TABLE purchase_product (

   purchase_id INTEGER REFERENCES purchase(id),

   product_id INTEGER REFERENCES product(id),

   quantity INTEGER

);

1. List all ladders with multiple suppliers, along with the supplier that offers the lowest price for the given product.

2. List the names of all customers who have purchased a product that could have been purchased from a supplier with a lower price by at least 5 dollars

   (500 cents), ordered by customer_name

3. List all customers who have never made a purchase at the Diamond Drive

4. List the names of all customers who purchased Tool 1717 and then later purchased O-Ring 1736, ordered by customer name.

5. List the name and number of purchases made at each store, sorted by store name.

6. List the name and total number of (non-distinct) items purchased at each store, sorted by store name.

7. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, after or on 2018-11-30.

8. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, for the months of October (before 2018-11-01), November (between 2018-11-01 and 2018-12-01), and December (after 2018-12-01). Your output should look like this, with ??? replaced with correct values:

   Los Alamos DD|October|???
   Los Alamos DD|November|???
   Los Alamos DD|December|???
   Los Alamos TD|October|???
   Los Alamos TD|November|???
   Los Alamos TD|December|???
   White Rock|October|???
   White Rock|November|???
   White Rock|December|???
   

Answer 1

List all ladders with multiple suppliers, along with the supplier that offers the lowest price for the given product.
Ans: Select supplier_name from supplier s join product p ON s.id = p.supplier_id where p.product_name ="xyz" order By p.product_price ASC

  
List all customers who have never made a purchase at the Diamond Drive

Ans: select c.customer_name from customer c JOIN purchase p on p.customer_id = c.id JOIN store s on s.id = p.store_id where s.store_name !="Diamond Drive"

    List the name and number of purchases made at each store, sorted by store name.
  
   ANS: Select s.store_name,count(p.id) from store s LEFT OUTER JOIN purchase p ON s.id = p.store_id GROUP BY s.id ORDER BY s.store_name ASC
  
  
    List the name and total number of (non-distinct) items purchased at each store, sorted by store name.
   ANS: please provide more info

    List the name and total number of (non-distinct) items purchased at each store, sorted by store name, after or on 2018-11-30.
   ANS: please provide more info

    List the name and total number of (non-distinct) items purchased at each store, sorted by store name, for the months of October (before 2018-11-01), November (between 2018-11-01 and 2018-12-01), and December (after 2018-12-01). Your output should look like this, with ??? replaced with correct values:
  
   ANS: please provide more info