1

INFT 1020 -Database Fundamentals - Assignment 2
Instructions:
Referring to the Adventure Works database, attempt the following questions for Assignment
2 (individual submission). This database represents all the business transactions for a
fictitious bicycle manufacturer called Adventure Works Cycles and includes everything from
Manufacturing, Sales, Purchasing, to customers, Contact Management and Human
Resources.
This is the database you need to use for assignment 2.
Feel free to browse the database and create diagrams to see how the tables relate to one
another. You can also research the "AdventureWorks" database on-line for more information.
Remember: If you want to generate diagrams in Management Studio you will need to issue
the following command to create the diagrams since all security objects (users/logins etc)
have been removed:
ALTER AUTHORIZATION ON DATABASE::AdventureWorks2012 TO sa
Submission Requirements:
Submit all files including SQL scripts and a backup of your working database version.
Include also in response to the questions, screenshots of the results screen for each
question and the overall conceptual database model used.

Questions:
Write SQL queries to answer the following questions. Include any
assumptions made, screenshots of the results set for each question and the
overall conceptual database diagram you designed.
Basic Joins
1. Find the names of all single unmarried male employees currently
working for the company
2. Find the First and Last name of all customers who have purchased items
in the database
3. Find the names of all employees who currently work in the Production
Department
4. Find the names of all Business Contacts in the Database
2

Hard Joins
1. Find the names of all current employees and their departments who are
salaried employees (exempt from collective bargaining).
2. Find the names of all current employees that work in departments other
than the sales department.
3. Find the names of bicycle stores in Victoria Australia
Aggregates
1. Calculate the number of employees currently working in each different
department
2. Find the names of all stores that have more than one address on record.
3. Count the number of customers without an address on record.
Hard Aggregates
1. Find the state in Australia with the most bicycle stores for this company.
2. Find the names and sales quota of the stores that have the highest sales
quota of all stores and whose store name starts with the letter ‘C’. MAX
should be SUM as this is finding the store with the person with the
highest sales quota.
3. Find the number of Job Candidates with known departments including
those whose department cannot be determined. Use the words
“Unknown” for candidates where the department cannot be
determined.
Sub Queries
1. Find the names of all customers without an address on record
Set Operators
2. Give a list of First and Last Names of all people that have the same First
and Last Name of someone who lives in Australia.