| help with sql for college student [message #373362] |
Thu, 12 April 2001 11:48  |
Raistlin
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
I am a college student who is learning sql.
I have an assignmentthat I am having a problem with.
I am wondering if someone would give me a hand.
Here are the two questions that I am having difficulty with.
1) Find the product that sold for the lowest minimum price and the one that has the highest actualprice. Print out the product description.
Info: actualprice is in the demo.item table with a fk = prodid
minprice is in the demo.price table with a fk = prodid
descrip is in the demo.product table with a pk = prodid
also, I know that I product has the lowest min price and another different product has the highest actuall price. So I should just get two rows of data.
2) Find Clark's boss and list out all people who also work for Clark's boss. List out the employee's names and the bosses name. Entitle the columns Employee and Manager.
Info: This is a unary relationship. The table is demo.emp with a pk = empno and a fk = mgr. Now the mgr is linked back to the empno.
Can anyone give me a hand?
|
|
|
|
| Re: help with sql for college student [message #373371 is a reply to message #373362] |
Fri, 13 April 2001 06:57  |
aylin
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
To find Clark's boss name ,
try this,make a join of emp table with itself.
where the emp table is (empno,mgr,name)
select e2.name from emp e1,emp e2
where e1.name = 'Clark' and e1.mgr = e2.empno
To find all the names of the people who work for Clark's boss
select name from emp where mgr = (select mgr from emp where name = 'Clark')
but this won't work if there are more than one named Clark
|
|
|
|
| Re: help with sql for college student [message #373408 is a reply to message #373362] |
Mon, 16 April 2001 06:43 |
lp
Messages: 8
Registered: April 2001
|
Junior Member |
|
|
Hi,
Here is the query to find CLARK's boss and his colleagues.
You could try this and hope it gives you a solution.
select 'CLARK''s Boss is ' || ename from emp
where empno = (select mgr from emp where ename like 'CLARK%')
UNION
select 'CLARK''s Colleagues are ' || ename from emp
where mgr in (select mgr from emp where ename like 'CLARK%')
and ename <> 'CLARK'
Here is the o/p of the above query
CLARK's Boss is KING
CLARK's Colleagues are BLAKE
CLARK's Colleagues are JONES
Regards
lp
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
