CP363: Assignment 01 - Fall 2024

Due 10:30 AM, Saturday, October 5, 2024

General Assignment Notes

When writing and submitting your assignments follow these requirements:

• When asked to write a SQL statement you may use any editor you like so long as it stores the statements as plain text. This means you should not use a word processing program such as Microsoft Word. Simple editors such as Notepad or TextEdit, or a Eclipse code editor are fine.

• There should be no errors or warnings in your code. SQL code that doesn't run doesn't get graded.

• Put all your programs, testing, and discussion files into one zip file.

• Use the Validate Assignment link to make sure that your .zip file is named correctly have have the proper contents. Improper assignment submissions are given a grade of zero.

Recorded Instructions for Assignment 1

WARNING

Follow the assignment instructions to the letter (e.g. name the various files t01.txt, t02.txt, etc.), as this assignment is auto-graded. If your SQL code files are not named correctly the auto-grading fails, and your assignment will be given a mark of 0.

These tasks use the DCRIS database. You may test your SQL code either through your own DBMS connection, or with the SQL Practice page.

These tasks are written for the most part in English and must be translated into SQL. You may have to look at the DCRIS data to determine which ID numbers to use, if appropriate, and which columns to sort on. Requests to have rows "sorted by x then y", means to write the ORDER BY as ORDER BY X, Y.

Requirements

• Put nothing but SQL code into each task file. Do not use EXPLAIN.
• These queries must refer to a single table or view. Do not use any joins, subqueries, grouping, or relational operations. Use only the table or view specifically asked for.
• Do not use UNION, INTERSECT, or EXCEPT.
• When asked for a particular projection (i.e. set of columns), ask for the columns in the order given in the task description.
• When not asked for a particular projection, select all (*) columns.

The Tasks

Write a SQL statement that:

1. selects all rows from the broad table sorted by the broad category description. Save the statement as .

2. selects rows from the broad table that start with the word Military sorted by the broad category description. Save the statement as .

3. selects rows from the broad table that contain the word Military sorted by the broad category description. (This is not the same query as the previous task.) Save the statement as .

4. selects the publication title and authors for all rows in the pub table sorted by the publication title. Save the statement as .

5. selects the publication title for rows in the pub table for member William Bain sorted by the publication title. Save the statement as .

6. selects the publication title of books in the pub table sorted by the publication title. Save the statement as .

7. selects the publication title of books in the pub table written by Allan English sorted by the publication title. Save the statement as .

   Note that "written by" in this context means that Allan English owns the books listed in the sense that it is his member ID given in the publication tuple, not that his name appears in the pubAuthors attribute.

8. selects the broad category description for rows in the vMemberBroad view for Terry Copp sorted by the broad category description. Save the statement as .

9. selects the member surname and forename for rows in the vMemberBroad table for the broad category Military History sorted by the member surname then forename. Save the statement as .

10. selects all rows from the narrow table sorted by the narrow category description then the broad category ID. Save the statement as .

11. selects the broad and narrow descriptions for all rows from the vBroadNarrow view sorted by broad category description then the narrow category description. Save the statement as .

12. selects the narrow category descriptions for rows in the vBroadNarrow view for the broad category Military History sorted by the narrow category description. Save the statement as .