CP363: Assignment 03 - Fall 2024

Due 10:30 AM, Saturday, November 2, 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 dealt with joined tables and aggregation. This assignment deals with subqueries. You may use the DCRIS views.

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 (";").
• These SQL statements must use subqueries. The use of (NOT) IN or (NOT) EXISTS are up to you.
• 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.
• If using a join, 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 surname. 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 expertise 'categories'. 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, or examine the contents of the vBroadNarrow view.)

Write a single SQL statement that:

1. selects the full member name and the numbers of publications for each member of each publication type. Name publication type fields books, articles, and papers. Sort the results by the full member name. Save the statement as .

   This is an example of a subset of the results:

   memberSurname	memberForename	books	articles	papers
   Bain	William	4	2	0

2. selects the full member name and the the number of broad and narrow expertises for each member. Name these expertise count fields broadCount and narrowCount. Sort the results by the full member name. Save the statement as .

   This is an example of a subset of the results:

   memberSurname	memberForename	broadCount	narrowCount
   Bedeski	Robert	6	5

   ---

3. selects the full member name and narrow expertise descriptions for members who have a narrow expertise in the Environmental Security broad category, but who have not declared that they have a broad expertise in the Environmental Security. Sort the results by the full member name. Save the statement as .

   There are members who have a certain narrow expertise but who have not declared that they have the matching broad expertise. For example, Alistair D. Edgar has a narrow expertise in the Second World War, but does not claim to have a broad expertise in Military History, which is the category that Second World War belongs to.

   ---

4. selects the full member name and broad expertise descriptions for members who do not have publications. Sort the results by the full member name and then broad expertise description. Save the statement as .

   ---

5. selects the full member name and publication count for all members with four or more publications. Name the publication count pubCount. Sort the results by the full member name. Save the statement as .

   You must do this with a subquery and not with the HAVING clause.

   ---

6. selects the narrow expertise descriptions of all narrow expertises that are not held by any member. Sort the results by the narrow expertise description. Save the statement as .

   ---

7. selects the broad expertise descriptions of all broad expertises that are not held by any member. Sort the results by the broad expertise description. Save the statement as .

   ---

8. selects the full member name and broad expertise count for all members with eight or more broad expertises. Name the broad expertise count broadCount. Sort the results by the full member name. Save the statement as .

   You must do this with a subquery and not with the HAVING clause.

   ---

9. selects the full member name and narrow expertise count for all members with thirty or more narrow expertises. Name the narrow expertise count narrowCount. Sort the results by the full member name. Save the statement as .

   You must do this with a subquery and not with the HAVING clause.

   ---

10. selects the broad expertise description for all broad expertises that do not have any associated narrow expertises. Sort the results by the broad expertise description. Save the statement as .

    ---