SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL QUERY HELP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

psk_002
Starting Member

USA
11 Posts

Posted - 11/27/2012 :  02:02:58  Show Profile  Reply with Quote
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
2168 Posts

Posted - 11/27/2012 :  02:38:33  Show Profile  Reply with Quote

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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 12/04/2012 :  23:15:11  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  00:02:23  Show Profile  Reply with Quote
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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 12/05/2012 :  00:17:49  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  00:30:55  Show Profile  Reply with Quote
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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 12/05/2012 :  00:37:02  Show Profile  Reply with Quote
Thank you very much

BP
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  00:38:03  Show Profile  Reply with Quote
quote:
Originally posted by psk_002

Thank you very much

BP


Welcome..
Have u tried second query?

--
Chandu
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 12/05/2012 :  00:42:30  Show Profile  Reply with Quote
No i am trying new query i.e

to find average number of books borrowed per person...

plz can you help...

BP
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  01:10:51  Show Profile  Reply with Quote
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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 12/05/2012 :  01:22:06  Show Profile  Reply with Quote
Thank you so much..... By the way are you a database professional or a employee???

BP
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  01:27:56  Show Profile  Reply with Quote
Am an employee (working as Database Developer)

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  01:35:01  Show Profile  Reply with Quote
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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 12/05/2012 :  01:36:04  Show Profile  Reply with Quote
Ok I need help how should I get in contact to you....

BP
Go to Top of Page

kullyg
Starting Member

South Africa
6 Posts

Posted - 12/10/2012 :  07:47:47  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/10/2012 :  07:51:04  Show Profile  Reply with Quote
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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 01/16/2013 :  00:08:40  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 01/16/2013 :  00:18:27  Show Profile  Reply with Quote
Did you try these queries? If u tried, show us solutions and also whats the problem you are facing now?

--
Chandu
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 01/16/2013 :  00:21:49  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 01/16/2013 :  00:57:52  Show Profile  Reply with Quote
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
Go to Top of Page

psk_002
Starting Member

USA
11 Posts

Posted - 01/16/2013 :  23:43:00  Show Profile  Reply with Quote
Thanks a lot chandu you are really helpful.

BP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000