Find authors who ONLY wrote history books
one hit wonder authors
who wrote famous books
who wrote what book
most novels written by one author
which human authors wrote the most books in the bible
New to SQL, although catching on - stuck on this query. This works, although Paddy O'Furniture should not be showing up as this author hasn't written any book and his au_id does not appear in the title_authors table. Please help.
Find authors who ONLY wrote history books
select a.au_id, a.au_lname, a.au_fname from authors a where not exists( select ta.au_id from title_authors ta join titles t on t.title_id = ta.title_id where t.type != 'history' and a.au_id = ta.au_id )
A01 Buchman Sarah
A07 O'Furniture Paddy
While your approach can work if you use another
exists to make sure the author wrote at least a history book, here's another approach using
select a.au_id, a.au_lname, a.au_fname from authors a join title_authors ta on a.au_id = ta.au_id join titles t on ta.title_id = t.title_id group by a.au_id, a.au_lname, a.au_fname having sum(case when t.type != 'history' then 1 else 0 end) = 0
Famous authors who only wrote one book, Sewell died only five months after its publication, but she lived to see its initial success. In honor of her legacy, here are eight authors — Sewell New to SQL, although catching on - stuck on this query. This works, although Paddy O'Furniture should not be showing up as this author hasn't written any book and his au_id does not appear in the title_authors table. Please help. Find authors who ONLY wrote history books. select a.au_id, a.au_lname, a.au_fname from authors a where not exists( select ta.au_id from title_authors ta join titles t on t.title_id = ta.title_id where t.type != 'history' and a.au_id = ta.au_id ) Output:
Paddy O'Furniture is in your result because no matching rows were discovered in the correlated subquery. i.e. no rows
exist for that author and hence
where not exists is true.
select a.au_id, a.au_lname, a.au_fname from authors a inner join title_authors ta ON a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id group by a.au_id, a.au_lname, a.au_fname having count(case when t.type <> 'history' then 1 end) = 0
The approach above uses a case expression within a count() function so that if any books have a non history type this count will be greater than zero. The
having clause enables using aggregated values to filter the final result (having is used is AFTER the
group by clause, and is not a substitute for a
Authors With Only One Novel (88 books), 88 books based on 237 votes: Wuthering Heights by Emily Brontë, The Picture of Fiction · Graphic Novels · Historical Fiction · History · Horror · Memoir · Music This list is for authors of books who have written only one novel (books of as the author wrote more than one book (see also message above). William Shakespeare (1564-1616) Nationality: English. Known for: Romeo and Juliet, Hamlet, Othello, King Lear. Shakespeare, known as the “Bard of Avon,” is one of the best known English writers in history. He is credited with writing nearly 40 plays, more than 150 sonnets, and several poems.
You are close. Adding
title_authors on the outer query will filter out authors who have not written a book.
select a.au_id, a.au_lname, a.au_fname from authors a join title_authors ta1 on ta1.au_id = a.au_id where not exists( select 1 from title_authors ta join titles t on t.title_id = ta.title_id where t.type != 'history' and ta1.id = ta.id )
title_authors in the inner query can actually be removed.
select a.au_id, a.au_lname, a.au_fname from authors a join title_authors ta on ta.au_id = a.au_id where not exists( select 1 from titles where t.type != 'history' and title_id = ta.title_id )
List of prolific writers, Some writers have had prolific careers with hundreds of their works being published. While some best-selling authors have written a small number of books that novelist in history, is estimated to have sold 4 billion books, having written 69 of the books is not detailed on the sales page and the books are printed only Welcome to OrderofBooks.com - the most comprehensive website on the internet for listing books in order. We provide detailed listings of all authors and characters/series in both publication order and chronological order. We currently have 3495 authors listed on the site and 2723 characters/series with more added each day.
14 Writers Who Only Wrote One Novel, And if those writers happen to have passed on, well, you're just SOL,… into a good old chapter book, but, hey, we've all got to take what we can get. follows a cast of characters through landmark moments in U.S. history. Amazon's Most Sold charts rank books according to the number of copies sold and pre-ordered through Amazon.com, Audible.com, Amazon Books stores, and books read through digital subscription programs (once a customer has read a certain percentage – roughly the length of a free reading sample).
50 Of The Best Historical Fiction Authors Writing Today, The best historical fiction authors write unputdownable books that help Are the author's historical novels award-winning and/or critically Her notable works include The Other Einstein, Carnegie's Maid, and The Only Woman in the Room. Lisa See is an American author whose historical novels include 4,038 books — 4,897 voters Best First Book by New Author. 1,368 books — 3,706 voters
The great writers forgotten by history - BBC Culture, They're just some of the overlooked authors we need to rediscover, writes Hephzibah Anderson. OK; Find out what's changed The creator of Billy Bunter, Charles Hamilton, reputedly wrote 100 million words (Credit: Alamy) Dennis was also the first writer in history to have three books on the New York Read-Alikes at BookBrowse. Here you can find handpicked read-alike recommendations for more than 4000 contemporary books and 3000 authors. Use the alphabetical lists below to look up a favorite book or author and then click "See Read-Alikes" to discover similar books.
- Thank you for your help.
- Thank you. This explanation helps get me started. I have a lot to learn.
- I like this one, however I do not understand the 'select 1' - maybe I'm not far enough in my course to understand this.