Explore Data Manipulation Language (DML) statements to manage data within schema objects.
You are asked to develop SQL DML statements to address the questions presented below. You must use the Northwind database for this assignment.
Before completing the Assignment, please watch the Unit 6 videos covering facets associated with database querying.
Navigate to the Academic Tools area of this course and select Library then Required Readings to access your texts and videos.
You will use the Northwind database for this assignment. Deploy the Northwind database into your Microsoft SQL Server instance by executing the following script in a Microsoft SQL Server Management Studio (SSMS) query window:
• Northwind Database Deployment Script
Once the Northwind database is successfully deployed, generate SQL statements to address the problems below. Use the following database design diagram as a guide in forming your statements:
• Northwind Database Design Diagram
Problem 1: Display the product records with unit prices equal to or greater than $100.
Expected Output
Problem 2: Present the unit value minimum, maximum, average, and standard deviation for current and discontinued products. Group the results based on the “Discontinued” attribute.
Expected Output
Problem 3: List suppliers with names starting with “G” in alphabetical order.
Expected Output
Problem 4: Display the full names of all Employees in FirstName, “ ” , LastName format.
Expected Output
Problem 5: Display all of the supplier names and HomePage values where the HomePage value is null. Present the results in alphabetical order by supplier name.
Expected Output
Problem 6: Get the number of Northwind customers in each country that has five or more customers. Present the results in alphabetical order by country name.
Expected Output
Problem 7: Get the total product costs (i.e., multiplying the unit price by quantity) for ProductID = 20 along with the associated orders IDs from the OrderDetails table. Provide the results in ascending order based on the order ID.
Expected Output
Problem 8: Select all of the cities where there are employees with no duplicates values displayed. Show the cities in alphabetical order.
Expected Output
Problem 9: List first and last names for employees who live in London, Seattle, or Redmond. Show the employee’s city in the output.
Expected Output
Problem 10: Display all product IDs and names for out-of-stock products having a unit price in the range of $20 to $25. Show the units-in-stock value in the results.
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.
