CP363 : Test 2 Sample Questions

The DCRIS Database
(click here to show only the diagram)
full name
A member's last name and first name in that order.
Book, Journal Article, Conference Paper
Publication types, where the type is 'B', 'A', or 'P' respectively.
broad expertise
A broad expertise is an expertise that is part of the keyword table. ex: 12 - Military History
narrow expertise
A narrow expertise is an expertise that is part of the supp_key table. ex: 3 - Treaty Organizations
  1. List the full names of Members who have a broad expertise in Military History. Sort the results by full name.
  2. Count the number of Members who have a broad expertise in Military History.
  3. List the full names of Members who have a narrow expertise in Treaty Organizations. Sort the results by full name.
  4. Count the number of Members who have a narrow expertise in Treaty Organizations.
  5. List the full names of Members who are associated with Wilfrid Laurier University. Sort the results by full name.
  6. Count the number of Members who are associated with Wilfrid Laurier University.
  7. List the narrow expertises that belong to the Military History broad expertise. Sort the results by description.
  8. Count the number of narrow expertises that belong to the Military History broad expertise.
  9. List the full names (once) of all Members who have published Books. Sort the results by full name.
  10. Count the number of Members who have published Books. Each Member should be counted only once.
  11. List the titles and publishers of all books published by Members. Sort the results by publisher.
  12. List the titles of all books published by Members with a broad expertise in Military History. Sort the results by publication title.
  13. Count the number of books published by Members with a broad expertise in Military History.
  14. List the full name of Members and titles of their publications where the title refers to 'Canada'. (Does not need to look for alternatives such as 'Canadian'.) Sort the results by publication title within full name.
  15. Count the number of publications where its title refers to Canada. (Does not need to look for alternatives such as 'Canadian'.)
  16. List the title and publication date of all publications published after 1999. Sort the results by title within date.
  17. Count the number of publications published after 1999.
  18. Count the number of narrow expertises that are not held by any Member.
  19. List the descriptions of all narrow expertises that are not held by any Member. Sort the results by description.
  20. List the full name of each Member and number of publications of each Member. Sort the results by full name within number of publications from most to least.
  21. List the full names and number of broad expertises of each Member. Sort the results by full name within the number of broad expertises from most to least.
  22. List the full names and number of narrow expertises of each Member. Sort the results by full name within the number of narrow expertises from most to least.
  23. List each broad expertise and the number of Members who have that expertise. Sort the results by expertise description.
  24. List each narrow expertise and the number of Members who have that expertise. Sort the results by expertise description.
  25. List the full name of each Member and the number of articles written by each Member. Sort the results by full name.
  26. List the full name of the Member and number of books written by each Member who is connected to Wilfrid Laurier University. Sort the results by full name.
  27. Count the number of each type (book, article, or paper) of publication. Sort the results by the full type name.
  28. List the full names of Members who have a broad expertise in Military History and a narrow expertise in Treaty Organizations. Sort the results by full name.
  29. Count the number of publications by each publisher. Sort the results by publisher.
  30. Count the number of publications published since 1999 grouped by publication date. Sort the results by publication date.
  31. List the full names of Members who have a broad expertise in Military History and do not have a narrow expertise in Treaty Organizations. Sort the results by full name.
  32. List the full names of all Members and the number of Books, Articles, and Papers each Member owns. Sort the results by full name.
  33. List the full names of Members and the titles of their Publications where the publication title contains the word "war" but the Member does not have a Military History broad expertise. Sort the results by publication title within name.
  34. List the full names of all Members and the number of broad and narrow expertises each has. Sort the results by full name.
  35. List the full names of Members whose only narrow expertise is in Northeast Asia. Sort the results by full name.
  36. List the full names of all Members who have not published Books. Sort the results by full name.
  37. List the full names of all Members who published only Books. Sort the results by full name.
  38. List the full names of all Members who have more than three publications. Sort the results by full name.
  39. List the full names and number of broad expertises of all Members who have fewer than three broad expertises. Sort the results by full name.