Author |
Topic |
psk_002
Starting Member
11 Posts |
Posted - 2012-11-27 : 02:02:58
|
Library database schema to record information of books borrowed by its members. member(memb_no, name, dob)books(isbn, title, authors, publisher)borrowed(memb_no, isbn, date)please help me the sql statement for the below question.query : Find the top two most popular authors (i.e., names of two authors whose books have been checked out in highest numbers by library members).BP |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-27 : 02:38:33
|
SELECT TOP 2 WITH TIES COUNT(br.memb_no) cnt, authors FROM books b JOIN borrowed br ON b.isbn = br.isbnWHERE memb_no IN (SELECT memb_no FROM member)GROUP BY authorsORDER BY cnt DESC--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2012-12-04 : 23:15:11
|
Thanks for the replycan you help me with this query:- For each publisher, find the name and membership number of members who have borrowed more than five books of that publisher.BP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 00:02:23
|
quote: Originally posted by psk_002 Thanks for the replycan you help me with this query:- For each publisher, find the name and membership number of members who have borrowed more than five books of that publisher.BP
Suggestion:Have you tried query? First you should work on it...This is simple and assignment query.. right? I am giving hint for this query.. Use GROUP BY, HAVING and JOIN clauses. Show the query if you face any difficulty...--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2012-12-05 : 00:17:49
|
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2 WITH TIES COUNT(br.memb_no) cnt, authors FROM books b JOIN borrowed br ON b.' at line 1BP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 00:30:55
|
quote: Originally posted by psk_002 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2 WITH TIES COUNT(br.memb_no) cnt, authors FROM books b JOIN borrowed br ON b.' at line 1BP
I think you are using MySQL. It is SQL Server forum.. Ok try it onceSELECT COUNT(br.memb_no) cnt, authors FROM books b JOIN borrowed br ON b.isbn = br.isbnWHERE memb_no IN (SELECT memb_no FROM member)GROUP BY authorsORDER BY cnt DESC LIMIT 2Follow this link http://searchoracle.techtarget.com/answer/TOP-n-WITH-TIES-in-MySQL--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2012-12-05 : 00:37:02
|
Thank you very muchBP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 00:38:03
|
quote: Originally posted by psk_002 Thank you very muchBP
Welcome.. Have u tried second query?--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2012-12-05 : 00:42:30
|
No i am trying new query i.e to find average number of books borrowed per person... plz can you help...BP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 01:10:51
|
quote: Originally posted by psk_002 No i am trying new query i.e to find average number of books borrowed per person... plz can you help...BP
this is total number of books borrowed per personSELECT memb_no, COUNT(isbn) TotalNoOfBooksFROM borrowedGROUP BY memb_no--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2012-12-05 : 01:22:06
|
Thank you so much..... By the way are you a database professional or a employee???BP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 01:27:56
|
Am an employee (working as Database Developer)--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 01:35:01
|
quote: Originally posted by psk_002 If you don't mind i have some more issues in databases.... so can you help..... I you want to help me send me an email to xxxxForum is not the place to talk so sorry and please help.....BP
Don't show your personal mailids here. delete above post now....One more thing is:Post your questions here. Many of valuable persons are here for you to solve those queries...--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2012-12-05 : 01:36:04
|
Ok I need help how should I get in contact to you....BP |
|
|
kullyg
Starting Member
6 Posts |
Posted - 2012-12-10 : 07:47:47
|
hi guys i need help with SQL reporting services, a colleuge of mine created subscriptions for a few automated reports to send info to clients at specific dates if months and times as agreed, now its the start of new seasons the last season subscription has expired but each time we recreate/renew the subscription it does successively but just don't give out infokulgs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-10 : 07:51:04
|
quote: Originally posted by kullyg hi guys i need help with SQL reporting services, a colleuge of mine created subscriptions for a few automated reports to send info to clients at specific dates if months and times as agreed, now its the start of new seasons the last season subscription has expired but each time we recreate/renew the subscription it does successively but just don't give out infokulgs
Post as new thread. Then you can get quick response--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2013-01-16 : 00:08:40
|
schema:employee ( person_name ,street , city)works( person_name,company_name,salary)company(company_name,city)(1) Find the name of the company which has employees living in both Lincoln and Omaha (i.e., the same company has some employees living in Lincoln, while some other employees living in Omaha)(2) Find the highest salaried employee from Omaha (i.e., live in Omaha).BP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-16 : 00:18:27
|
Did you try these queries? If u tried, show us solutions and also whats the problem you are facing now?--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2013-01-16 : 00:21:49
|
Hi chandu,, I need to submit the answers by night. i.e 11 20 pm now. so I am tying it but i am not getting the idea.So please help me with answers.BP |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-16 : 00:57:52
|
try these queries(1)SELECT company_name, COUNT(DISTINCT city) cntFROM companyWHERE city IN( 'Lincoln', 'Omaha')GROUP BY company_nameHAVING COUNT(DISTINCT city) = 2(2)SELECT * FROM works WHERE person_name IN (SELECT person_name FROM Employee WHERE city = 'Omaha')ORDER BY SALARY DESC LIMIT 1Note: Limit 1 is in MySql whereas TOP 1 is in SQL Server--Chandu |
|
|
psk_002
Starting Member
11 Posts |
Posted - 2013-01-16 : 23:43:00
|
Thanks a lot chandu you are really helpful.BP |
|
|
Next Page
|