Click here to Skip to main content
15,917,455 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
CSS
Tables:
Book: BID,BNAME,BPRICE,SID
Author: AID,ANAME,BID

Question:
Write query which give list of Books, having more than one Author.



This question was asked to me in interview.
Posted

1 solution

If you didn't know, then you didn't deserve the job.

Can you see a column that both tables have in column ? It's BID. So the sql is

SQL
select b.bname, a.aname from book b inner join author a on b.bid = a.bid


to get a list of all authors and books. However, what you want, is books having more than one author. So the answer is:

SQL
select a.aname, count(b.bname) from book b inner join author a on b.bid = a.bid group by a.aname having count(b.bname) > 1
 
Share this answer
 
v2
Comments
Yogi ,Pune 24-Dec-13 1:44am    
it is not that i don't know, I was already tried below mentioned query
Select * From Student_Master Inner Join Book_Master On Student_Master.SID=Book_Master.SID Inner Join Author_Master On Book_Master.BID=Author_Master.BID Where Book_Master.BPRICE>1000 Group By Author_Master.BID Having COUNT(Author_Master.BID)>1

for above query system throws an error:"Column 'Student_Master.SID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


Main Question:
Write query which give list of student who take books who's price is >1000 & Book having more than one Author.

Tables:
Student: SID,SNMAE
Book: BID,BNAME,BPRICE,SID
Author: AID,ANAME,BID

I have also tried query given by you but system showing blank result but there is records in database , having more than one author to books
Christian Graus 24-Dec-13 1:47am    
The error means what it says. Every non aggregate column you select, needs to be in the group by list. Just copy all your column names, comma separated, and it will work.
Yogi ,Pune 24-Dec-13 1:50am    
I used Select * in my query.
Christian Graus 24-Dec-13 1:52am    
Well, that was dumb. Then you need to list ALL the columns, and if more are added, your query will break.
Yogi ,Pune 24-Dec-13 1:58am    
I have also tried it,i.e instead of * putting column names still same error persist.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900