OraFAQ Forum: SQL & PL/SQL » sql queries(urgent help needed)

Home » SQL & PL/SQL » SQL & PL/SQL » sql queries(urgent help needed)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql queries(urgent help needed) [message #8553]

Fri, 29 August 2003 04:07

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

Report message to a moderator

Re: sql queries(urgent help needed) [message #8560 is a reply to message #8553]

Sat, 30 August 2003 00:53

Mahesh
Messages: 90
Registered: January 2001

Member

1query answer:
select c.firstname, c.surname from customer c, orders o, items i where c.cust_id = o.custid and o.cust_id=i.cust_id and o.order_id = i.order_id
group by o.order_id,c.firstname, c.surname having count(i.item_id)>5

check this out and for other queries please send the sample data so that we can doit in a better manner

Report message to a moderator

Re: sql queries(urgent help needed) [message #8572 is a reply to message #8553]

Mon, 01 September 2003 02:13

Rajesh Lakshkar
Messages: 3
Registered: September 2003

Junior Member

Dear

for your first query
SQL>
1 select a.firstname,a.surname
2 from Customer a,Orders b,Items c,Wine d
3 where a.cust_id=b.cust_id
4 and b.order_id = c.order_id
5 and c.wine_id=d.wine_id
6 group by c.item_id
7 having count(c.item_id)>5;

I am working on other queries...

Rajesh

Report message to a moderator

Previous Topic:	important..
Next Topic:	COOKIES & PL/SQL SERVER PAGES - URGENT HELP REQUIRED

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:21:06 CDT 2024