Question

This is extra information about the shopping database given to answer this question:

For many query questions we refer to the following database schema for a website that keeps track of what people like to buy or actually buy in different on-line supermarkets. (Think of something like a cross-store loyalty card system.)

customer(cID, cName, street, city)

Customers with unique cID and other attributes

store(sID, sName, street, city)

Stores with a unique ID and other attributes. Stores with the same name but different sID (and address) form what we call a store chain.

product(pID, pName, suffix)

Products with a unique ID

shoppinglist(cID, pID, quantity, date)

What a customer wants to buy on a certain date. (Every customer creates at most one shoppinglist per day.)

purchase(tID, cID, sID, pID, date, quantity, price)

A transaction: what a customer bought in one go;
every transaction (tID) corresponds to one customer (cID) buying from one store (sID) on one date (date); at some point during the course we see that this actually means tID → cID,sID,date

inventory(sID, pID, date, quantity, unitPrice)

Inventory of a store as published at the start of a date.

Primary key attributes are underlined. (Foreign keys are not indicated but can be inferred from the attribute names that have been used.)

• The interpretation of the schema is as follows:
  Customers and stores should be pretty obvious.
• Products have a unique id and name but also some textual suffix that can indicate a content (weight in gram or content in liter, or size such as large, medium, small, etc. we do not "interpret" this suffix).
• A customer may wish to buy products on a certain date. The shoppinglist contains each product only once, with a quantity (how many "units" of the product to buy.)
• A customer then goes to an on-line store to buy some products. (The address of the store is only important for goods pick-up.) Such a purchase constitutes a transaction (hence tID). In a purchase a customer is at one store on one date and buys a number of products in a certain quantity and pays a price for that product (and quantity). It's like the "receipt" from the purchase. Each product is listed only once on that receipt.
• Each supermarket has inventory. For each product it has a number of units (quantity) in stock at the start of a certain date, and each product has a unit price. (We do not keep track of discounts that may lower the total price when buying several items in a single purchase.)

The database is "complete" (closed world assumption): we do not consider anything that may exist outside the database (like people who are not a customer or products that nobody wants and no store sells).

All customers appear in customer, all products in product and all stores in store. A store name that appears only once does not constitute a store chain. A store chain requires the existence of at least two stores with the same name.

When a store carries (sells) a product it may temporarily have an inventory quantity of 0 (meaning the product is out of stock). The product is then not removed from inventory. It is possible that there are several products (different pID) with the same pName and suffix, due to different identification schemes.

Although the true inventory changes constantly during the day the published inventory is only updated once, at the start of the day. (At some point during the course we start dealing with this issue.)

The question is:
Consider the shopping database schema.
Write a SQL query for the following natural language query.

Find all the different store ids that only sold products to people that live in same city as the location of the store.

Answer 1

Please use the below query.

Initially I have created customer table,store table and enter some sample value.

Query(customer table):

create table customer(cid integer, cname varchar(100),street varchar(100),city varchar(100));

insert into customer values(1, "Anirudh","South Street", "Chennai");

insert into customer values(2, "Anil","New Street", "Mumbai");

insert into customer values(3, "Rokhit","Gandhiji Road", "New Jersy");

select * from customer;

Customer table:

cid	cname	Street	city
1	Anirudh	South Street	Chennai
2	Anil	New Street	Mumbai
3	Rokhit	Gandhiji Road	New Jersy

Query(store table):

create table store(sid integer, sname varchar(100),street varchar(100),city varchar(100));

insert into store values(1, "New Trendy","Gandhiji Street", "New Jersy");

insert into store values(2, "Fashion","West Street", "Chennai");

insert into store values(3, "Trendy","New Street", "Hyderabad");

select * from store;

Store table:

sid	sname	street	city
1	New Trendy	Gandhiji Street	New Jersy
2	Fashion	West Street	Chennai
3	Trendy	New Street	Hyderabad

Question:

Find all the different store ids that only sold products to people that live in same city as the location of the store.

I have stored the below data in the purchase table.

tid	cid	sid	pid	date	quantity	price
1	3	1	2	12/4/2019	2	90
2	1	3	2	12/5/2019	5	990
3	1	2	6	12/6/2019	12	890
21	2	3	6	12/7/2019	112	8454990

use the below query to pull the information from the purchase table.

Query:

select distinct a.sid from purchase a join store b

on a.sid=b.sid

join customer c

on a.cid=c.cid

and b.city=c.city;

Query Explanation:

In the above query i have selected only the sid that is store id by joining the purchase and store table so that we can get the whole purchase table at the first joining a.sid=b.sid.

Then in the second join we have joined with customer table and purchase table based on the customer id. so that we can get the whole purchase table. Finally we have applied the condition b.city=c.city that is nothing but we have compared the location of customer and store table. so it returned only two rows.

purchase
tid	cid	sid	pid	date	quantity	price
1	3	1	2	12/4/2019	2	90
3	1	2	6	12/6/2019	12	890

The first row has the customer id 3. From the customer table the location is New Jersy and sid 1 from the store table the location is also New Jersy. In the same way customer id 1 and store id 2 has the same location Chennai. Hence it returned only the store and customer has same location.

Output:

sid

2

1