Home » SQL & PL/SQL » SQL & PL/SQL » Beginner SQL Query Question
Beginner SQL Query Question [message #415936] Wed, 29 July 2009 18:37 Go to next message
peace2009
Messages: 11
Registered: July 2009
Junior Member
Colored field is pk:

CARS (cid, mid, cyear)
BUYERS (bid, bname, bcity, age)
MANUFACTURERS (maname, location)
MODELS (mid, maname, model)
SALESPEOPLE (sid, sname, years_employed)
TRANSACTIONS (bid, cid, sid, amount, month, day, year)

The design structure is given, i'm not allowed to change it...

Questions:
1. Print the sid of all salespeople who have sold both a Ford and a Toyota in 1997.

My Attempt:
select s.sid
from salespeople s join transactions t
on s.sid = t.sid
join cars c
on c.cid = t.cid
join models m
on m.mid = c.mid
group by s.sid
having count(distinct t.cid) = (select count(distinct maname) from manufacturers)
and (m.maname in (select distinct maname from manufacturers));


2. Print the sname and total sales amount of the salesperson who had the highest total sales (in dollars) for 1997.

My Attempt:

select s.sname, to_char(sum(t.amount), 'fm$999999.00')
from transactions t, salespeople s
where t.sid = s.sid
group by s.sname, t.year
having (t.year = 1997) and max(sum(t.amount));


I did my best, i need your help...
Re: Beginner SQL Query Question [message #415939 is a reply to message #415936] Wed, 29 July 2009 19:44 Go to previous messageGo to next message
arshadshabbir
Messages: 6
Registered: February 2009
Junior Member
Please explain your first q again

only those salesamn who sold both ford & toyota or one of them
in 1997 and toyota & ford are model name or maname
any how i am replying both questions......



Ans Q#1
==================
selct distinct t.sid,s.sname
from transactions t,salespeople s,cars c,models m,MANUFACTURERS ma
where t.sid = s.sid and
t.cid = c.cid and
c.mid = m.mid
m.maname = ma.maname
and t.year = 1997
and mn.mname in ('Toyota','Ford')


Result
------------------

SID SNAME CID CYEAR MID MODEL MANAME
-----------------------------------------------------------------------







Ans of Q#2
------------------------------
select t.sid,s.sname,to_char(sum(t.amount),'fm$999999.00') tot_amount
from transactions t, salespeople s
where t.sid = s.sid and
year = 1997
group by sid,year
having sum(t.amount)
in
(
select max(t_amount) max_amount from
(
select sum(amount) t_amount from transactions
where year = 1997
group by sid,year
)
)

result query 2
========================

sid sname tot_amounnt
--------------------------------
1234 Alexandar $98745

Arshad Shabbir, Paksitan







Re: Beginner SQL Query Question [message #415941 is a reply to message #415936] Wed, 29 July 2009 19:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
arshadshabbir,

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

It says in part, which you did not read or follow:

"Responding to Posts

When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation. "
Re: Beginner SQL Query Question [message #415945 is a reply to message #415936] Wed, 29 July 2009 20:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
amateur/flawed design

>BUYERS (bid, bname, bcity, age)
store birthday (DATE datatype) so age can be computed; since AGE can change as record sits in table
>SALESPEOPLE (sid, sname, years_employed)
only store hire_date (DATE datatype) so years_employed can be calculated
>TRANSACTIONS (bid, cid, sid, amount, month, day, year)
Store buy_date as DATE datatype; do not store separate month, day, year fields
Re: Beginner SQL Query Question [message #415955 is a reply to message #415936] Wed, 29 July 2009 23:11 Go to previous message
peace2009
Messages: 11
Registered: July 2009
Junior Member
Thanks. Your response helped....
Previous Topic: Convert to ANSI from non-standard SQL (merged 7) 10g
Next Topic: How to find table based on data in a column
Goto Forum:
  


Current Time: Fri Dec 02 20:34:03 CST 2016

Total time taken to generate the page: 0.09077 seconds