This is a database containing a list of students (STUDENTS), a list of departments (DEPARTMENT),
a list of faculties (FACULTY) in the university, a list of assignments (ASSIGNMENT) and a table
representing the relationship between which students have completed which assignments and which
departments the assignments are associated with.
A student enrolled in one department can work on an assignment from a different department (e.g.
taking a course offered from a different department).
The relationships in this database are shown in the screenshot below:
Assume the data in the database can change. The data in the above screenshot is just an
example.
Using the database shown above, create a Microsoft Word document containing the SQL statements
(as shown in the lecture materials) to do the following:
a. Find the Student Number, Last Name and Birth Date for all students whose Gender is male.
Sort the result by Birth Date in ascending order.
b. Write a query to list any Students that live on West Ave. List all fields for the student in the
STUDENT table. This query must still work correctly if the address in the STUDENT table
change or new fields are added to the table.
c. List the Student’s First and Last Names as well as the name of Department the student is
enrolled in for all students born after March 2nd, 1996.
d. Modify the student information for Student Number equal to 250012345 to your first and last
name, address, birth date and gender. (only your first/last name must be real, you can make
up values for address, birth date and gender if you wish). Do not alter the value of ClassYear
or EnrolledInDepartmentID in anyway. You must not alter any other student’s data in this
query.
e. Find all Students with an Assignment Grade between 20 (inclusive) and 50 (exclusive). This
means the grade must be 20 or greater and under 50 (a grade of 50 would not be included).
In
clude the Student’s First Name, Last Name, Assignment Name, and Assignment Type in
the results (and no other fields). Students should be listed multiple times if they have multiple
assignments that match these criteria (once for each assignment matching the criteria).
f. Find the number of assignments with a failing grade (less than 50) that belong to a
Department in a Faculty that is a not professional school (does not have a check mark for
the ProfessionalSchool field in the FACULTY table). The resulting field that contains the
number should be named “Fails”. Hint: A check mark in a Yes/No field is equal to True, no
check mark is equal to False.
g. Find a list of Assignments you have completed. Include the Assignment Name, your grade,
the Name of the Department that gave the assignment and the Name of the Faculty of the
Department that gave the assignment (note that this is not necessarily the same as the
Department/Faculty you are enrolled in). You must use your first name and last name in this
query and NOT your student ID.
HINT 1:
it is very easy to tell if a value is stored as a number or as text in an MS Access table.
-if the value is right justified (pushed to the right) then it is stored as a number.
-if the value is left justified (pushed to the left) then it is stored as a text.
-if a checkbox is shown, then it is stored in a Yes/No field.
-BirthDate in the STUDENT table is a Date/Time type.
HINT 2:
remember how field names with spaces are treated differently from field names without spaces.
HINT 3:
remember, round brackets are required to be used with all INNER JOIN statements.
The format (how the SQL statements are written) MUST match the style shown in the notes.
Each SQL reserved word MUST appear on their own line and in capital letters in the document.
Each SQL Statement MUST be indented as shown below. You will lose major marks otherwise.