| Author |
 Topic  |
|
|
psk_002
Starting Member
USA
11 Posts |
 Posted - 11/27/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/27/2012 : 02:38:33
|
SELECT TOP 2 WITH TIES COUNT(br.memb_no) cnt, authors
FROM books b
JOIN borrowed br ON b.isbn = br.isbn
WHERE memb_no IN (SELECT memb_no FROM member)
GROUP BY authors
ORDER BY cnt DESC
--
Chandu |
 |
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 12/04/2012 : 23:15:11
|
Thanks for the reply
can 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/05/2012 : 00:02:23
|
quote:
Originally posted by psk_002
Thanks for the reply
can 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
USA
11 Posts |
Posted - 12/05/2012 : 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 1
BP |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/05/2012 : 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 1
BP
I think you are using MySQL. It is SQL Server forum.. Ok try it once
SELECT COUNT(br.memb_no) cnt, authors
FROM books b
JOIN borrowed br ON b.isbn = br.isbn
WHERE memb_no IN (SELECT memb_no FROM member)
GROUP BY authors
ORDER BY cnt DESC LIMIT 2
Follow this link
http://searchoracle.techtarget.com/answer/TOP-n-WITH-TIES-in-MySQL
--
Chandu |
|
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 12/05/2012 : 00:37:02
|
Thank you very much
BP |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/05/2012 : 00:38:03
|
quote:
Originally posted by psk_002
Thank you very much
BP
Welcome..
Have u tried second query?
--
Chandu |
|
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 12/05/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/05/2012 : 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 person
SELECT memb_no, COUNT(isbn) TotalNoOfBooks
FROM borrowed
GROUP BY memb_no
--
Chandu |
|
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 12/05/2012 : 01:22:06
|
Thank you so much..... By the way are you a database professional or a employee???
BP |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/05/2012 : 01:27:56
|
Am an employee (working as Database Developer)
--
Chandu |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/05/2012 : 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 xxxx
Forum 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 |
Edited by - bandi on 12/05/2012 01:38:29 |
|
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 12/05/2012 : 01:36:04
|
Ok I need help how should I get in contact to you....
BP |
|
|
|
kullyg
Starting Member
South Africa
6 Posts |
Posted - 12/10/2012 : 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 info
kulgs |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/10/2012 : 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 info
kulgs
Post as new thread. Then you can get quick response
--
Chandu |
|
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 01/16/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/16/2013 : 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
USA
11 Posts |
Posted - 01/16/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/16/2013 : 00:57:52
|
try these queries
(1)
SELECT company_name, COUNT(DISTINCT city) cnt
FROM company
WHERE city IN( 'Lincoln', 'Omaha')
GROUP BY company_name
HAVING 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 1
Note: Limit 1 is in MySql whereas TOP 1 is in SQL Server
--
Chandu |
Edited by - bandi on 01/16/2013 00:59:46 |
|
|
|
psk_002
Starting Member
USA
11 Posts |
Posted - 01/16/2013 : 23:43:00
|
Thanks a lot chandu you are really helpful.
BP |
|
|
| |
Topic |
|
SQL QUERY HELP
