CP363: Assignment 02 - Fall 2024

Due 10:30 AM, Saturday, October 12, 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.

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.

Assignment 1 dealt with single tables and views, Assignment 2 deals with joined tables and aggregation. You may not work with views - you must provide the appropriate joins.

Requirements

• Put nothing but SQL code into each task file. Do not use EXPLAIN.
• Use single quotes ('...') around text. Do not use double quote ("..." at all.
• Do not use semicolons (";") at all.
• 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.
• Do not use any views. To select data from multiple tables you must use JOINs.
• Use only the JOIN … ON syntax, not alternatives such as USING.

The Tasks

Some explanations:

• full member name - means the member surname followed by the member forename. Ex: 'Brown' 'David'
• full publication type - means the publication type description rather than the single letter code. Ex: 'book' rather than 'b'
• Narrow expertises are subsets of broad expertises. Thus 'Cold War' is a subset of 'Military History', and 'Canada' is a subset of 'Geographic Focus'. (See the second selection box in: DCRIS Search by Expertise for a list of these relationships.)

Write a single SQL statement that:

1. selects the full member name, the full publication type, and the publication title for all members, sorted by the full member name, then the full publication type, then the publication title. Save the statement as .

2. selects the full member name, the full publication type, and the publication title for members associated with Laurier, sorted by the full member name. Save the statement as .

3. selects the full member name and the publication title for conference papers with the word 'Nuclear' in the title, sorted by the publication title. Save the statement as .

4. selects the publication title and the full publication type for William Bain, sorted by the publication title. Save the statement as .

5. selects the full member name and the number of publications for each member, sorted by the full member name. (Hint: some members have no publications, but we still want the count for them). Save the statement as .

6. selects the full member name and the number of books for each member, sorted by the full member name. (Hint: some members have no books, but we still want the count for them). Save the statement as .

7. selects the full publication type and the number of publications for each type, sorted by the full publication type. Save the statement as .

8. selects the full member name and the number of broad expertises for each member, sorted by the full member name. Save the statement as .

9. selects the broad expertise descriptions and the number of members who have that expertise, sorted by the expertise description. Save the statement as .

10. selects the full member name and the number of narrow expertises for each member, sorted by the full member name. Save the statement as .

11. selects the narrow expertise descriptions and the number of members who have that expertise, sorted by the expertise description. Save the statement as .

12. selects the member full name, the member's narrow expertises, and the broad expertise that the narrow expertises belong to, sorted by the full member name then the narrow expertise description. Save the statement as .