Unit Outcome:

· Use advanced SQL statements to manage and interact with data from more than one table.

Course Outcome:

IT234-4: Discover more advanced SQL such as security commands and logins.

Purpose

You created a relational database so that you could maximize the ability to retrieve data in a variety of ways. By relating the tables through the use of foreign keys, you ensured that you could pull meaningful data from more than one table. This enhances an organization’s data analytics ability. In this unit, you will use joins, subqueries, and union statements to retrieve data from more than one table.

Assignment Instructions

Before completing the Assignment, please watch the Unit 8 videos covering facets associated with advanced querying.

Navigate to the Academic Tools area of this course and select Library then Required Readings to access your texts and videos.

Generate SQL statements to address the problems below using the SQL Server Management Studio query window. You will be creating advanced SQL statements to extract data from more than one table.

You will use the Northwind database for this assignment. Leverage the following database design diagram as a guide in forming your statements:

·

Your assignment submittal needs to show both the generated SQL statements and confirmatory screenshots verifying task completion.

Problem 1: Create a report of “seafood” and “produce” products, showing ProductID, ProductName, and CategoryName. Incorporate an inner join condition for this query.

Expected Output

Problem 2: List the last name, first name, title, and salary of company employees with salaries above the company average. Use a non-correlated subquery in the SQL statement.

Expected Output

Problem 3: List the average salaries for employees in Seattle and London. The averages need to be calculated on a per city basis. Use a union operation to generate the results. (Hint: Use one SQL statement to calculate the average salary for one city and another almost identical SQL statement to calculate the average salary for the other city.)

Expected Output

Problem 4: Show the product names for products that have been ordered in quantities equal to or exceeding 120. Use a non-correlated subquery in the SQL statement.

Expected Output

Problem 5: List the supplier names and cities for suppliers that reside in the same cities as Northwind employees. Use a non-correlated subquery in the SQL statement.

Expected Output

Problem 6: Display the names of Northwind employees that manage territories located in the Western region. Use inner joins in the SQL statement linking the Employees, EmployeeTerritories, Territories, and Region tables. Do not show duplicate employee names in the result set.

Expected Output

Problem 7: Display customer names, cities, and order IDs for customers residing in Madrid or Paris. Show all customers regardless of whether they have placed orders or not. Use an outer join in the SQL statement.

Expected Output

Problem 8: Display a combined list of supplier and shipper names along with their phone numbers. Use a union operation in the SQL statement. Present the results in alphabetical order based on CompanyName.

Expected Output

Problem 9: Show the employee names, salaries, and countries for employees that have salaries above the average salary within their respective countries. Use a correlated subquery in the SQL statement.

Expected Output

Problem 10: Display the names of products supplied by vendors in the USA and Norway. Show the product country in the result set. Present the results in alphabetical order by product name. Use an inner join in the SQL statement.

Expected Output

Assignment Requirements

Microsoft SQL Server Express and SQL Server Management Studio (SSMS) MUST be installed to complete this Assignment.

Compose your Assignment in a Word document and be sure to identify yourself, your class, and unit Assignment at the top of your paper. Embed the screenshots of your SQL statements and confirmatory output (e.g., table structure definitions) into the Word document.