Unit 6 Assignment_ Developing SQL Statements to Query Existing Data
docx
keyboard_arrow_up
School
Purdue Global University *
*We aren’t endorsed by this school
Course
234
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
5
Uploaded by Savannah_98
Unit 6 Assignment: Developing SQL Statements to Query Existing Data
Outcomes addressed in this activity:
Unit Outcomes:
●
Use DML commands to query existing tables.
Course Outcome:
IT234-3: Explore Data Manipulation Language (DML) statements to manage data within
schema objects.
Purpose
You are asked to develop SQL DML statements to address the questions presented below.
You must use the Northwind database for this assignment.
Assignment Instructions
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
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
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.
Also review the university policy on plagiarism. If you have any questions, please contact
your professor.
The assignment is due by the final day of the Unit 6 week.
Directions for Submitting Your Assignment
Name your assignment document according to this convention:
IT234_<YourName>_Unit6.docx. (replace <YourName> with your full name). Submit your
completed assignment to the Unit 6 Assignment Dropbox by the final day of the Unit 6
week.
Review the Unit 6 Assignment Rubric before beginning this activity.