Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL QUERY HELP

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.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

11 Posts

Posted - 2012-12-04 : 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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 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
Go to Top of Page

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 1

BP
Go to Top of Page

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 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

11 Posts

Posted - 2012-12-05 : 00:37:02
Thank you very much

BP
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 00:38:03
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

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

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 person
SELECT memb_no, COUNT(isbn) TotalNoOfBooks
FROM borrowed
GROUP BY memb_no



--
Chandu
Go to Top of Page

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 01:27:56
Am an employee (working as Database Developer)

--
Chandu
Go to Top of Page

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

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

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 info

kulgs
Go to Top of Page

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 info

kulgs


Post as new thread. Then you can get quick response

--
Chandu
Go to Top of Page

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

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

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

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

psk_002
Starting Member

11 Posts

Posted - 2013-01-16 : 23:43:00
Thanks a lot chandu you are really helpful.

BP
Go to Top of Page
    Next Page

- Advertisement -