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
• Find the names of all single unmarried male employees currently working for the company
• Find the First and Last name of all customers who have purchased items in the database
• Find the names of all employees who currently work in the Production Department
• Find the names of all Business Contacts in the Database
Hard Joins
• Find the names of all current employees and their departments who are salaried employees (exempt from collective bargaining).
• Find the names of all current employees that work in departments other than the sales department.
• Find the names of bicycle stores in Victoria Australia
Aggregates
• Calculate the number of employees currently working in each different department
• Find the names of all stores that have more than one address on record.
• Count the number of customers without an address on record.
Hard Aggregates
• Find the state in Australia with the most bicycle stores for this company.
• 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.
• 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
Find the names of all customers without an address on record
Set Operators
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.