Database Fundamentals Assignment 2 2019 SP2
Instructions:
• Download the Adventure Works database from the Assignments tab of the course website and attach it to your SQL Server (as per practicals)
• Use the provided UML/Database diagram of the Adventure works database or create your own database diagrams to help you answer the following questions. Note that the diagram is from 2008 and has some minor differences. To find out information about specific table attributes/columns either right click on them in the database diagram to view the properties window, or open the table up in the design view and select the column to view the properties. Each column (and FK relationship) has a description that tells you the purpose of the data if you need assistance. You can also consult the schema library.
• Write your queries to the questions below and paste your final SQL query into the corresponding .SQL file along with your username. Once you have completed all your files, zip them into a single folder and name using your university network/username (e.g. wazza001).
• DUE DATE: 2nd June @11:55 pm
–Q1001-Write a query to display the First and Last name of all employees that currently work in the production department as part of the manufacturing group who started work before the end of 2002
–Q1002-Write a query to display the name of stores, cities and countries of all stores worldwide except stores located in the United States, United Kindom or Canada – these countries are blacklisted
–Q1003- Products come in various colours. List the colours of the various products and the number of products for each of those colours if there are at least 30 items that came in that colour. Where the colour is not specified, display N/A
–Q1004-Find the First and Last Name of employees who have the same First name but different Last Name to that of customers (no duplicates)
–Q1005 – List the ProductModel name and Name for all Customers who ordered a product which was the most recent to start selling. No duplicate records!
–Q1006 – Find the First and Last name as well as the bonus amount of all sales people who have a bonus less than the average bonus of salespersons in the German territory.
–Q1007-Find the most common job title(s) and the number of employees with that title
–Q1008 – Find the name of products that have illustrations (no duplicates)
–Q1009 Find the second highest pay rate of all employees and the number of employees on this pay rate
–Q1010- Find the month, year and number of employees hired for the month and year in which the most employees were hired in the second half of the year.