Homework 3
This week's exercise is to write a variety of SQL queries. These queries are intended to answer plausible questions about the data while demanding more than just SELECT *.
Submit 10 text files named q0.sql, q1.sql, and so on, up to q9.sql in a directory hw3. Each file should contain the text of the query in question. You may also include comments; sqlite comments go from -- (two hyphens; fond Haskell memories, anyone?) to the end of the line.
Download these two databases to run your queries on them. We will test them on the exact same schemas as those given, but not necessarily the same data. The databases contain data about the Art Institute of Chicago and publications at the ICFP (International Conference on Functional Programming) 2011, respectively.
[art-inst-chicago.db]
[icfp-2011.db]
Queries on the art database
q0. Retrieve the title of each painting, the artist's last name, its height in inches and its width in inches in descending order of surface area.
q1. Retrieve the artistID, first name, last name, and age at death of the artist(s) who was/were youngest at the time of death. (Note that since the birth and death dates are given as years only, any age computation is approximate, accurate plus or minus one.)
q2. Retrieve the titles of all paintings and sculptures, with the artists' last names, in the database and the age the artist was at the time the work was produced, in order of youngest to oldest such age.
q3. Retrieve the artist's last name and the title of all the undisplayed works.
q4. Write a query to display the artistID, last name and all (distinct) media used by all artists in all paintings. Order the query result by last name, ascending. Your results should include not just the medium ("oil") but the surface the medium is on ("oil on canvas"). Use GROUP_CONCAT to denormalize the data and produce comma-delimited results like this:
artistID lname media ---------------- ---------------- -------------------------------- 444 Cassatt oil on canvas 555 Degas oil on canvas 222 Picasso lithograph on ivory wove paper, oil on canvas, oil on panel 111 Seurat conte crayon on ivory laid paper, oil on canvas, oil on panel 333 van Gogh oil on canvas
Queries on the publication database
q5. Retrieve the titles of all papers with a single author.
q6. Retrieve the titles and number of affiliated institutions of all papers with authors from more than one institution.
q7. Retrieve the title(s) and number of authors of the paper(s) with the most authors in the database instance. For example, if there is one paper with three authors in the whole batch, and every other paper has one or two authors (that's the case in the database you are given), identify that paper.
q8. Retrieve the authors' authID, first and last names, in alphabetical order, and the number of papers on which they have authorship.
q9. Retrieve the authID, first and last name of the author(s) and their number of collaborators (shared authorships), in alphabetical order.
Each query must be self-contained and not depend on temporary tables or views.
You are explicitly allowed to share the results of these queries with one another as you work on this homework, but not the SQL code you wrote to compute them.
This work is due by Friday, May 1 at 11:59pm. Please submit ten text files as specified above. No need to submit the databases; we will run your queries against our own databases.