CS 4250 Project Part 6

Upload to the CS Homework system on or before midnight on Friday, December 3, 2021.

PDF, plain text or MS Word only.

  1. Write five different queries on your database, using the SELECT / FROM / WHERE construct provided in SQL. Your five queries should illustrate several different aspects of database querying, such as:

    You do not have to illustrate all the above aspects, just the ones that occur naturally in your application. Try to infuse some reality into your project and think of some reasonable queries that people would want to make with your application. For example, in a book application, writing a query such as "Find all authors whose ages are one fifth of the number of pages in their novel" sounds silly!

    You will not lose points if you do not cover all the above aspects.

    You will lose points if you don't illustrate a reasonably large fraction of the above aspects.

    Two queries that answer the same user question (return the same results) will "count" as one query. Your five different queries must be genuinely distinct and different from one another.

What to turn in:

A document that details the following:

  1. A list of your defined SQL schemas; these schemas are to remind me about your project.
  2. A list of the SQL queries that you tried out, a description in English of what the queries are supposed to find, and the query results you obtained. If an answer is long (and runs to several pages), please include just enough of the answer in your solution so that we know what is going on. Do not forget to mention that you truncated the final output. Please do not include pages and pages of answers.

    If you can, include an example of a SQL query you tried that did not work out, and an explanation of why it did not work out -- I am looking for evidence of learning, and our failures are frequently the most educational part of any learning experience.

  3. Do not forget to include the names and email addresses of all project partners.
  4. Required but not graded: Include one sentence per group member summarizing each group member's contribution to Project Part 6.


Hints:

  1. Nonsense queries, syntactically incorrect queries, and queries that return no data will earn no points.
  2. "select count(*) from mytablename" will earn no points, since that query was provided in the instructions for Part 5.
  3. What happens if we forget the join conditions in the query? The query is semantically incorrect, so zero points.
  4. Will we lose points for a query no real-world user would ever ask? Yes.
  5. We're convinced real-world users would use this query, but the reason is not obvious; should we explain the reason? Yes.
  6. Can MySQL help me? Yes. By default, only_full_group_by is ON in MySQL 5.7.5 and higher versions. Leave it ON. MySQL will then catch and stop some common GROUP BY semantic errors -- i.e., putting fields into the SELECT clause that are not in the GROUP BY clause, and are not aggregation calculations. (We discussed why those are errors in class.)


Last modified: 4 Nov 2021