Home » SQL & PL/SQL » SQL & PL/SQL » Easy Query
Easy Query [message #656539] Mon, 10 October 2016 17:04 Go to next message
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
Re: Easy Query [message #656540 is a reply to message #656539] Mon, 10 October 2016 17:56 Go to previous messageGo to next message
BlackSwan
Messages: 25856
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.

Re: Easy Query [message #656541 is a reply to message #656540] Mon, 10 October 2016 18:03 Go to previous messageGo to next message
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
Re: Easy Query [message #656542 is a reply to message #656541] Mon, 10 October 2016 18:06 Go to previous messageGo to next message
BlackSwan
Messages: 25856
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?
Re: Easy Query [message #656543 is a reply to message #656542] Mon, 10 October 2016 18:44 Go to previous messageGo to next message
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.
Re: Easy Query [message #656544 is a reply to message #656543] Mon, 10 October 2016 18:49 Go to previous messageGo to next message
BlackSwan
Messages: 25856
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.
Re: Easy Query [message #656545 is a reply to message #656544] Mon, 10 October 2016 19:20 Go to previous messageGo to next message
calamardo114
Messages: 4
Registered: October 2016
Junior Member
Thank you! I´ll add that.

Do you think the rest of the query is ok?
Re: Easy Query [message #656548 is a reply to message #656545] Tue, 11 October 2016 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
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?
Re: Easy Query [message #656555 is a reply to message #656543] Tue, 11 October 2016 03:41 Go to previous message
cookiemonster
Messages: 13014
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.
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:
  


Current Time: Wed Feb 21 09:09:34 CST 2018

Total time taken to generate the page: 0.07712 seconds