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

Solution : Required SQL queries have been given below. Syntax has been checked.

Notes: in the given schema there are column names having "spaces", and special characters like "per-name".

In most of the the DBMS while querying such columns, the column name should be enclosed properly. For example in mySQL it should be enclosed in ` ` . For SQL server it should be enclosed in square brackets "[ ]". Following queries have been given using mySQL.


Ex #1: Find people who bought telephony products.

Select
  p.*
from
  Person p
  inner join Purchase pp on p.`per-name` = pp.buyer
  inner join Product pr on pp.product = pr.pname
where
  pr.category = 'Telephony'

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

Select
  p.*
from
  Person p
  inner join Purchase pp on p.`per-name` = pp.buyer
  inner join Product pr on pp.product = pr.pname
  inner join Company c on pr.manufacturer = c.cname
where
  c.country = 'America'

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

Select
  p.*
from
  Person p
  inner join Purchase pp on p.`per-name` = pp.buyer
  inner join Product pr on pp.product = pr.pname
  inner join Company c on pr.manufacturer = c.cname
where
  where c.country = 'America' and p.city = 'Seattle'

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

Select
  *
from
  Person p
where
  p.`per-name` in (
    Select
      buyer
    from
      Purchase
    where
      buyer in (
        Select
          seller
        from
          Purchase
      )
  )

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

Select
  p.*
from
  Person p
  inner join Purchase pp on p.`per-name` = pp.buyer
  inner join Product pr on pp.product = pr.pname
  inner join Company c on pr.manufacturer = c.cname
where
  c.`stock price` > 50
  or pp.seller = "Joe"