Question

Consider the following schema:

product (pname, price, category, manufacturer)

Purchase (buyer, seller, store, product)

Company (cname, stock price, country)

Person(per-name, phone number, city)

Write a suitable SQl for the following:

Ex #1: Find people who bought telephony products.

Ex #2: Find names of people who bought American products

Ex #3: Find names of people who bought American products and they live in Seattle.

Ex #4: Find people who have both bought and sold something.

Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.

Answer 1

The SQL queries based on the schema are given as follows:

Ex.1: The names of who buyed telephony products

Query:

Select Person.per-name FROM Person, Purchase, Product WHERE Person.per-name=Purchase.buyer AND Product=Product.pname AND Product.category="telephony".

So, the above query will obtain the desired results.

Ex.2: Names of people who bought American products

Query: Select Person.per-name FROM Person, Purchase, Company WHERE Person.per-name=Purchase.buyer AND Company=Company.cname AND Company.country="USA"

So, the above query will obtain the desired results.

Ex.3: Names of people who bought American products and they live in Seattle

Query: Select Person.per-name FROM Person, Purchase, Company WHERE Person.per-name=Purchase.buyer AND Company=Company.cname AND Company.country="USA" AND Person.city="Seattle"

So, the above query will obtain the desired results.

Ex 4: Name people who have both bought and sold something.

Query:

Select Person.per-name FROM Person, Purchase WHERE Person.per-name=Purchase.buyer AND Person.per-name=Purchase.seller

So, the above query will obtain the desired results.

Ex 5: Name people who bought stuff from Joe or bought products from a company whose stock prices is more than $50

Query:

Select Person.per-name FROM Perseon, Purchase, Company,Product WHERE Product=Product.pname AND Person.per-name=Purchase.seller AND Purchase.seller="Joe" OR Company.stock price>50.

So, the above query will obtain the desired results.

---------------------------------------Please Upvote---------------------------------------------------------------------------------