Need some help on an intro sql lab

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

SQL Server 2000 Forums

SQL Server Development (2000)

Need some help on an intro sql lab

Author

Topic

highcontrast
Starting Member

6 Posts

Posted - 2006-02-14 : 22:22:57

Hi everyone!

I'm currently taking an SQL intro course and we're doing simple select queries and I'm stuck on a few questions. The database essentially is a library system where there are tables such as member, book, booksub, bookcopy, reservation, etc.

member has these attributes: memberno, name, etc.
book has: isbn, author, title
booksub has: isbn, subcode
reservation has: memberno, isbn, etc.

If anyone can shed some light on how to generally code the following questions it would be greatly appreciated because I've spent hours on these few questions already but to no results:

1)How many books are there for each subject code
2)List in alphabetic order sequence the book titles for books reserved by Gopun.

Also is "name" a function in SQL because when I try to query to find member names that have outstanding library fines the "name" is in blue, symbolizing a function?

Thank you in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-14 : 22:39:50

>>I've spent hours on these few questions already but to no results:

We kind of have an unspoken policy not to help on homework. But why don't you post whatever you came up with so far

We'll nudge you in the right direction...

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 22:40:13

1) use COUNT(*) and GROUP BY <column name>
2) use ORDER BY <column> to sort the records in the order you want.

----------------------------------
'KH'
everything that has a beginning has an end

highcontrast
Starting Member

6 Posts

Posted - 2006-02-14 : 23:11:48

I totally understand the no spoon feeding policy.

For the first query I had something like:

select count(distinct isbn) from booksub inner join book
on booksub.isbn = book.isbn
where isbn = (select count(isbn) from booksub where subcode = 'hist')

I realize this obviously doesn't work but I can't seem to get the count column and subject code and somehow I have to integrate it with the three other subject codes

For the second query I had something like:

select book.title from book inner join bookcopy
on book.isbn = bookcopy.isbn inner join member
on bookcopy.memberno = member.memberno inner join reservation
on member.memberno = reservation.memberno
where memberno = '6'
order by title

and that one doesn't work.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-14 : 23:45:06

Hi,
see BOL for Group By and Order by Clause

1st
select subcode, count(isbn)
from booksub
group by subcode

for 2nd

select a.title
from book a
inner join reservation b
on a.isbn = b.isbn
inner join member c
on b.memberno = c.memberno
where
c.name = 'Gopun'
order by a.title

highcontrast
Starting Member

6 Posts

Posted - 2006-02-15 : 01:01:46

shallu1 I can't thank you enough. I was totally overcomplicating things. TG and khtan, thank you as well.

About my question from before, is "name" a function in SQL because when I try to select name from a column, it won't work.

ie. List the name of the member with the highest outstanding fees.

I put:

select member.name from member
where (select max(osfines) from member)

but I get an error.

As well, List the name of any member that hasnt any books currently on loan. I have

select member.name, member.memberno from member inner join bookcopy
on member.memberno = bookcopy.memberno
where memberno is null

doesn't work out too.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-15 : 01:09:05

quote:
select member.name from member
where (select max(osfines) from member)

but I get an error.

The error is not because of name but the where statement.
it should be something like

where somecolumn = (select max(osfines) from member)

----------------------------------
'KH'
everything that has a beginning has an end

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-15 : 01:12:54

I think you need to learn SQL first.
However name is a keyword that's why shown in Blue.
A function is normally in Pink.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-15 : 01:18:38

>> I'm currently taking an SQL intro course
He / She is.

Maybe he / she is doing something ahead of the course

Some SQL for beginners
http://www.w3schools.com/sql
http://www.sqlcourse.com/

----------------------------------
'KH'
everything that has a beginning has an end

highcontrast
Starting Member

6 Posts

Posted - 2006-02-15 : 01:41:13

Sorry I'm a beginner to SQL and I just listed some questions that I had trouble on. Still haven't figured out the one about the member who has no books on loan. And if I have to find out the books that have more than 2 copies on loan, should I count memberno to copyno with status on loan? Sorry for the silly questions.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-15 : 01:49:46

Hi,

quote:

select member.name from member
where (select max(osfines) from member)

but I get an error.

As well, List the name of any member that hasnt any books currently on loan. I have

select member.name, member.memberno from member inner join bookcopy
on member.memberno = bookcopy.memberno
where memberno is null

doesn't work out too.

Post your Complete Table structure.

highcontrast
Starting Member

6 Posts

Posted - 2006-02-15 : 14:08:19

For Member:

MemberNo - Name - Address - Phone - OSFines

For Book:

ISBN - Title - Author

For BookCopy:

ISBN - CopyNo - MemberNo - LibraryName - PurchaseDate - DueDate - Status(Loan/Open)

For Reservation:

MemberNo - ISBN - PickupLibrary - HoldDate - Notified Date

trying to find members that no books on loan and books that have more than 2 copies on loan. The other tables are library, booksub and subject which I don't think are relevant to the 2 queries.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-15 : 17:57:06

>> find members that no books on loan

select MemberNo, [Name], Address, Phone
from   Member m
where  not exists (select * from BookCopy c where c.MemberNo = m.MemberNo)

>>books that have more than 2 copies on loan

select ISBN, count(*)
from   BookCopy
group by ISBN
having count(*) > 1

----------------------------------
'KH'
everything that has a beginning has an end

highcontrast
Starting Member

6 Posts

Posted - 2006-02-15 : 20:51:36

quote:

>>books that have more than 2 copies on loan
select ISBN, count(*)
from BookCopy
group by ISBN
having count(*) > 2

That gives me the books that have more than 2 copies in all libraries. The query is asking for books that have more than 2 copies ON LOAN.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-15 : 21:21:22

Add a where clause and check for the status equal to ON LOAN

----------------------------------
'KH'
everything that has a beginning has an end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources