Home » SQL & PL/SQL » SQL & PL/SQL » sql queries
sql queries [message #8552] Fri, 29 August 2003 01:11 Go to next message
Sheikh
Messages: 2
Registered: August 2003
Junior Member
Hi all,
I need to write a Query based on below tables and questions.
BlottoBottle is a new online retail wine merchant. Their database has been set up as follows:-

Customer(cust_id , surname, firstname, m, title, addressline1, addressline2, addressline3,
city_state, zipco, country, phone, fax, email, salary)

Grape_variety(variety_id, variety)

Inventory(wine_id, inventory_id, on_hand, cost, case_cost, date_added)

Items(cust_id, order_id, item_id, wine_id, qty, price)

Orders(cust_id, order_id, order_date, discount, delivery, note)

Region(region_id, region_name, description, map)

Wine(wine_id, wine_name, type, year, winery_id, description)

Winery(winery_id, winery_name, region_id, description, phone, fax)

Wine_variety(wine_id, variety_id)

Questions are:-

1)BlottoBottle(wineshop) wishes to know the firstname and surname of customers who have at least one order containing more than 5 items. Do not include duplicates and do not use subqueries.

2)What is the most popular (total quantity ordered) variety (as in attribute grape_variety.variety) of wine ordered by customers who have a salary of 60000 or more?

3)BlottoBottle is concerned about the reliability of a particular winery (winery_id 59) and wishes to consider alternative suppliers in future. Are there any wineries that have produced all the same types (as in attribute wine.type) of wine as winery 59, and are also in the same region as winery 59? If so, give their winery_id.

Thanx in advance.

Sheikh
Re: sql queries [message #8617 is a reply to message #8552] Thu, 04 September 2003 07:07 Go to previous message
ramana
Messages: 51
Registered: December 2000
Member
query 1
select distinct c.firstname, c.surname
from
customer c,
items i
where
c.cust_id = i.cust_id
group by i.cust_id, i.order_id, c.firstname, c.surname
having count(i.cust_id) > 5

query 2
select g.variety, wv.variety_id, i.wine_id, sum(i.qty) qty
from
items i,
grape_variety g,
wine_variety wv
where
i.wine_id = wv.wine_id and
g.variety_id = wv.variety_id and
cust_id in (select cust_id from customer where salary >= 60000)
group by variety, variety_id, wine_id
having sum(i.qty) =
(
select max(sum(i.qty)) qty
from
items i,
grape_variety g,
wine_variety wv
where
i.wine_id = wv.wine_id and
g.variety_id = wv.variety_id and
cust_id in (select cust_id from customer where salary >= 60000)
group by variety, variety_id, wine_id
)

3) question is not clear.
Previous Topic: Insert Statement...
Next Topic: Delete 5 th row from a table
Goto Forum:
  


Current Time: Thu Mar 28 15:00:36 CDT 2024