CODEWITHSUNDEEP
oracle-database
user3103832
user3103832

Reputation: 15

Date difference between first and last book per each author

I would create this table. 

create table Books 
  (
  book_id number PRIMARY KEY,
  Title number,
  yearpub DATE,
  author_id number
  )

create table Authors 
  (
  author_id number,
  book_id number
  )  

create table Author_name 
  (
  author_id number,
  first_name varchar(10),
  last_name varchar(10)
  )

I have no Oracle installed now but I wanna select Date difference (number of years) between first and last book per each author.
I think there should be some aggregate function.

Upvotes: 0

Views: 77

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

SELECT authors.author_id,
       max(name.first_name||' '||name.last_name) as full_name,
       max(to_char(yearpub,'yyyy')) - min(to_char(yearpub,'yyyy')) as  years
from author_name name, books,authors
where
      authors.author_id = name.author_id and
      authors.author_id = books.author_id and
      authors.book_id = books.book_id
group by authors.author_id

Upvotes: 1

Related Questions