OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Easy Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Easy Query [message #656539]

Mon, 10 October 2016 17:04

calamardo114
Messages: 4
Registered: October 2016

Junior Member

Anyone would like to help me with this query?
It will be very much appreciated!!!

Assume you have two tables in an Oracle database, one named BOOK and one named AUTHOR. There is a column in BOOK named author_id that references the AUTHOR table. The idea here is that a single author may have written multiple books. Write an SQL query that produces a result similar to this:

Author Name Total number of books written

Charles Dickens 23
Dr. Seuss 51
Norman Mailer 20
Harper Lee 1
Jane Doe 0

Report message to a moderator

Re: Easy Query [message #656540 is a reply to message #656539]

Mon, 10 October 2016 17:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We don't do homework.
Show us what you've tried.

Report message to a moderator

Re: Easy Query [message #656541 is a reply to message #656540]

Mon, 10 October 2016 18:03

calamardo114
Messages: 4
Registered: October 2016

Junior Member

Thank you Blackswan,

I have this so far, but nut sure if Null answers will be a problem :S

select author, count(books.author_id) as books_written
from authors
left outer
join books
on authors.id = books.author_id
group
by author

Report message to a moderator

Re: Easy Query [message #656542 is a reply to message #656541]

Mon, 10 October 2016 18:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

calamardo114 wrote on Mon, 10 October 2016 16:03

Thank you Blackswan,

I have this so far, but nut sure if Null answers will be a problem :S

select author, count(books.author_id) as books_written
from authors
left outer
join books
on authors.id = books.author_id
group
by author

Which of the columns in the SQL above would/could contain NULL?

Report message to a moderator

Re: Easy Query [message #656543 is a reply to message #656542]

Mon, 10 October 2016 18:44

calamardo114
Messages: 4
Registered: October 2016

Junior Member

If there aren't any book entries by an author in the books table, then a NULL result would come up.

Report message to a moderator

Re: Easy Query [message #656544 is a reply to message #656543]

Mon, 10 October 2016 18:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

calamardo114 wrote on Mon, 10 October 2016 16:44

If there aren't any book entries by an author in the books table, then a NULL result would come up.

then use NLV function to return 0 where NULL exists.

Report message to a moderator

Re: Easy Query [message #656545 is a reply to message #656544]

Mon, 10 October 2016 19:20

calamardo114
Messages: 4
Registered: October 2016

Junior Member

Thank you! I´ll add that.

Do you think the rest of the query is ok?

Report message to a moderator

Re: Easy Query [message #656548 is a reply to message #656545]

Tue, 11 October 2016 00:32

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

SQL> select d.dname, count(e.empno)
  2  from dept d left outer join emp e on e.deptno = d.deptno
  3  group by d.dname
  4  /
DNAME          COUNT(E.EMPNO)
-------------- --------------
ACCOUNTING                  3
OPERATIONS                  0
RESEARCH                    5
SALES                       6

Isn't this easier to read?

Report message to a moderator

Re: Easy Query [message #656555 is a reply to message #656543]

Tue, 11 October 2016 03:41

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

calamardo114 wrote on Tue, 11 October 2016 00:44

If there aren't any book entries by an author in the books table, then a NULL result would come up.

No it wont, count can never return null. There is an obvious value to return if count can't find anything to count after all.

Report message to a moderator

Previous Topic:	What is Corrected By RowID? I wish to know Missing Numbers in a column
Next Topic:	Unable to Recreate the Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 06:56:33 CDT 2024