SQL代写|数据库代写|Assignment代写 - Microsoft Access Queries and SQL Statements
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
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
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).
Include 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.
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.
remember how field names with spaces are treated differently from field names without spaces.
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.
somewhere else ON some condition )
some condition is true;
This is non-optional. You MUST use this standard.
You will be graded on adhering to this standard.
You MUST write the SQL without the use or aid of any electronic method.
For example: You can NOT use MS Access Query Builder to create the SQL graphically and then
copy or type in the result to your Word document. You will be graded on adhering to this standard.
You must identify yourself on the document. You will lose marks if this is missing.
Somewhere visible on the beginning (top) of the Word file you must include:
- your first and last name
- your Western ID (see below for a description of your Western ID)
- your student number
Save your SQL statements in the Word file named "youraccountname_sql.docx” and attach the file to
Project 2: Queries using Query Designer in Microsoft Access
Create a brand new, blank database and name it “youraccountname_customers.accdb”.
(substitute youraccountname with your actual account name.)
Using the XML Import utility in MS Access import the data in the file Assign5.xml. You will need to
have the Assign5_F20.xsd file in the same directory as Assign5.xml.
This will import five (5) tables with data into your database.
Update the customer table for customer_number=1 by replacing the Customer First and Last Name
with your own (you may do this by hand).
Create the following relationships in your database AND enforce referential integrity for each:
1. customer_number in the Customer table related to the custID in the Orders table.
2. isbn in the Books table related to the BookISBN in the Writes table
3. isbn in the Books table related to the BookISBN in the Orders table.
4. authID in the Authors table related to the AuthorID in the Writes table.
Everything else is set by this XML and the schema. No further action or intervention is required by the
students on the MS Access file.
Create a query using the Graphical Query Design Tool in the Create tab in MS Access for each of
the following. (You MUST name each query object using the names:
Query A, Query B, Query C, Query D and Query E respectively)
A. Find the isbn, title, publication year, and retail price for all of the books from 180 series of the
ISBN ordered by publication date. Books from the 180 series all have an isbn number that
begins with a “0-180”.
B. Find the ISBN, title and order Date for all of the books order by the customer whose ID is 11.
C. Find the customer number, first name and last name for all customers who ordered a book
which was published before 1970. In addition to showing the customer number, customer
first name and customer last name, show the book title and order quantity as part of the
resulting dataset. Order this by book title in ascending order. If a customer has ordered more
than one book published before 1970 that customer will appear more than once in the
D. Find the total number of orders of books which were published before 1970. Customers who
have ordered more than one book published before 1970 will be included in the answer for
each book that they have ordered.
E. Find the title, author’s name, Order Date and quantity for all of the books that you have
ordered (use your last name and first name in the query not the Customer ID). List the books
in descending order by retail price.
Save your database in the file named "youraccountname_customers.accdb” and submit the file
Project 3: Information Systems Questions regarding your Company
Create a one page MS Word document and complete the following questions pertaining to the
business you described in Assignment One (1).
Each answer must be comprehensive (more than one sentence). Each answer requires at least four
sentences. The entire Project 3 should be at least approximately 400 words. It is expected that some
thought and explanation is included in this section.
1.) Does your company have a Web Presence:
- why or why not?
- suggest a domain name (i.e. www.workingbytes.com) for your company
2.) Is your company’s primary source of advertising the web?
- If yes, why, if no, then what is your company’s main way of advertising..
3.) How important is it for the head of the company (president or owner or CEO – whichever title) to
be ‘computer literate’?
- What is the minimal level of understanding of software and hardware would you need to run a
company of 200 to 1,000 employees?
The format of these document should be similar to format you used in Assignment One (1).
Place your name, followed by the company name at the top.
At the top of the document, include your name, Student number and Western ID (the first part of your
Western email (i.e. if your email was - email@example.com your ID would be - derntwis)
Fill in the required information after.
Formatting is not important as long as the document is easy to follow:
This documents must be a Word file saved and submitted as a .docx file
The youraccountname must be your Western Account Name.
You must upload, attach and submit, via the CS1032 course website in WebCT, the following 3 files:
• youraccountname_ customers.accdb
NOTE: This description of the assignment contains instructions that tell you to create files with names
that have a specific format. In these file names; the “youraccountname” is your UWO username.
It is your responsibility to ensure the correct files have been submitted in OWL.
Please check and make sure you have received the confirming email and then check that the three
(3) files (you must submit three (3) files for this assignment) have been uploaded correctly.
If you submit incorrect or incomplete files, there will be no re-sends or re-grading.
It is your responsibility to check. No exceptions, no excuses.
You must do ALL THREE (3) Projects in this assignment. This is Assignment 5, comprised of three
(3) parts, Project 1, Project 2, and Project 3. All three projects are to be completed and submitted.
It is your responsibility to regularly backup your files. No extensions will be given for lost or corrupt
REMEMBER: It must be your own work. You can NOT work in groups or share work.
You cannot use a template or “starting point” from another person (even if they are
We will be checking for any academic dishonesty and the penalties as outlined
in the course outline will be applied.