INFO210_HW5_Solution

INFO210_HW5_Solution

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: that were written either by a Nobel Prize winner or by an Argentinian, or for which publication year is unknown. Solution select B.title, A.last from Authors A, Books B, Nobel_Winners N where A.last = B.author and A.last = N.author UNION select B.title, A.last from Authors A, Books B where A.last = B.author and A.country = 'Argentina ' UNION select B.title, B.author from Books B where B.year is null !tle author Captain Pantoja and the Special Service Vargas Llosa The City and the Dogs Vargas Llosa The Bridge on the Drina Andric The Damned Yard Andric Axolotl Cortazar The Library of Babel Borges Labyrinths Borges (b) Compute the number of Nobel Prize winner by country, for counties in which we have information about Nobel Prize winners. Sort the results by the number of winners, from higher to lower. Solution select A.country, count(*) as num_winners from Authors A, Nobel_Winners N where A.last = N.author group by A.country order by num_winners desc country num_winners Japan 2 Colombia 1 Peru 1 Yugoslavia 1 (c) List names of countries from which at least 2 authors won the Nobel Prize. Order results by the number of Nobel Prize winners, higher to lower, breaking ties by country name, (alphabetically) lower to higher. Solution select A.country, count(*) as num_winners from Authors A, Nobel_Winners N where A.last = N.author group by A.country having count(*) >= 2 order by num_winners desc, A.country country num_winners Japan 2 (d) List book titles by an author whose first name is Mario. Solution 1 select B.title from Authors A, Books B where A.last = B.author and A.first = ‘Mario’ Solution 2 select title from Books where author in ( select last from Authors where first = ‘Mario’) !tle Captain Pantoja and the Special Service The City and the Dogs...
View Full Document

This note was uploaded on 01/21/2014 for the course INFO 210 taught by Professor Stoy during the Winter '13 term at Drexel.

Ask a homework question - tutors are online