NYT Bestsellers and Goodreads Ratings Database
- All the universities at which authors have been educated:
SELECT last_name , first_name , name FROM EDUCATEDAT LEFT JOIN AUTHOR ON Person = AUTHOR_ID LEFT JOIN UNIVERSITY ON University = U_ID
All the awards received by the various authors in the database
SELECT last_name , first_name , name FROM AWARDRECEIVED LEFT JOIN AUTHOR ON Person = AUTHOR_ID LEFT JOIN AWARD ON Award = A_ID ORDER BY last_name ;
To produce a bar chart, write a query whith a SELECT list which mentions two columns. The values in the first column specify the labels to be used for the bars. The second column needs to provide the numbers which can determine the length of the bars.
Type and number of degrees earned by authors of non-fiction:
SELECT Description , COUNT(*) FROM EDUCATION LEFT JOIN AUTHOR ON Person = AUTHOR_ID LEFT JOIN DEGREE ON Degree = D_ID LEFT JOIN BOOK ON Author = AUTHOR_ID WHERE genre = 'nonfiction' GROUP BY Description
To create a line chart, write an SQL query with a SELECT list containing two columns. The values of the first column will be shown on the X-axis and the values of the second column on the Y-axis.
The total number of reviews per year
SELECT YEAR, SUM(reviewCount) AS 'Reviews'
GROUP BY YEAR
The text box below can be used to generate a scatter plot. The SELECT list of the SQL query that you type in needs to refer to two columns. The values in the first column will be show on the X-axis, and the values in the second columns will be shown on the Y-axis. Both columns need to contain numeric values.
The relation between ratings in GoodReads and the position of the NYT Bestsellers list
SELECT avgRating , highestPosition