Unit 6 Assignment: Developing SQL Statements To Query Existi

Unit 6 Assignment Developing Sql Statements To Query Existing Dataout

Develop SQL DML statements to query data within the Northwind database based on specific problem prompts, including retrieving product records with certain prices, summarizing unit values, listing suppliers and employees based on criteria, and analyzing order and stock information. The assignment involves deploying the Northwind database in SQL Server, writing SQL queries to solve each problem, and documenting query results with screenshots. All work must be submitted in a Word document with proper identification, including embedded SQL scripts, output images, and relevant schema definitions.

Paper For Above instruction

The Northwind database is a classic sample database that contains data about a fictitious company, including products, suppliers, employees, customers, and orders. It is widely used for learning and practicing SQL querying skills. In this assignment, the goal is to develop SQL queries to extract specific information, thereby demonstrating proficiency in Data Manipulation Language (DML) commands within SQL Server Management Studio (SSMS). The assignment steps include deploying the Northwind database, writing targeted SQL statements for various scenarios, and documenting the results with appropriate visual evidence.

Problem 1: Display products with unit prices equal to or greater than $100

This query targets high-priced products by filtering on the 'UnitPrice' field. The relevant table is the 'Products' table, which contains product details including unit prices. The SQL query selects product IDs, names, and unit prices where the 'UnitPrice' is at least $100, facilitating analysis of premium products.

SELECT ProductID, ProductName, UnitPrice

FROM Products


WHERE UnitPrice >= 100;

This statement helps identify product offerings that are priced at a premium, which could inform inventory management and pricing strategies.

Problem 2: Present minimum, maximum, average, and standard deviation of unit values grouped by ‘Discontinued’ status

This task involves aggregating data from the 'Products' table to understand the distribution of product prices, differentiating between active and discontinued products. The aggregate functions include MIN, MAX, AVG, and STDEV, grouped by the 'Discontinued' attribute which indicates whether a product is no longer available.

SELECT Discontinued,

MIN(UnitPrice) AS MinPrice,


MAX(UnitPrice) AS MaxPrice,


AVG(UnitPrice) AS AvgPrice,


STDEV(UnitPrice) AS StdDevPrice


FROM Products


GROUP BY Discontinued;

The results assist in analyzing pricing trends and the impact of discontinuation on product value.

Problem 3: List suppliers with names starting with “ G ” in alphabetical order

This query filters suppliers based on the 'CompanyName' field. Using a LIKE operator with a wildcard, it filters for supplier names beginning with 'G', and sorts the list alphabetically for easy reference.

SELECT SupplierID, CompanyName

FROM Suppliers


WHERE CompanyName LIKE 'G%'


ORDER BY CompanyName;

This helps identify suppliers whose names start with 'G', which could be useful for supplier management or outreach initiatives.

Problem 4: Display full names of all Employees in "FirstName, LastName" format

Constructing full employee names involves concatenating the 'FirstName' and 'LastName' fields, separated by a comma. This provides a clear, readable listing of employee identities.

SELECT CONCAT(FirstName, ', ', LastName) AS FullName

FROM Employees;

This representation simplifies employee identification in reports and communications.

Problem 5: List suppliers with null HomePage values, ordered alphabetically

This query identifies suppliers who do not have a homepage URL specified, aiding in data completeness checks. The results are ordered alphabetically by the supplier's name for clarity.

SELECT CompanyName, HomePage

FROM Suppliers


WHERE HomePage IS NULL


ORDER BY CompanyName;

This helps clean data records and prioritize suppliers lacking online presence.

Problem 6: Count customers per country where there are at least five customers, ordered by country

This involves grouping customer data by 'Country' in the 'Customers' table, counting the number of customers in each country, and filtering for countries with five or more customers. The results are ordered alphabetically by country for easy review.

SELECT Country, COUNT(*) AS CustomerCount

FROM Customers


GROUP BY Country


HAVING COUNT(*) >= 5


ORDER BY Country;

This analysis highlights key markets with significant customer bases.

Problem 7: Calculate total product costs for ProductID = 20, with associated order IDs, ordered ascending by OrderID

This query joins the 'OrderDetails' table to access order-specific quantities and unit prices for ProductID 20. It computes total cost per order by multiplying unit price and quantity, listing results ordered by the 'OrderID' field.

SELECT OrderID, (UnitPrice * Quantity) AS TotalCost

FROM OrderDetails


WHERE ProductID = 20


ORDER BY OrderID ASC;

This provides insight into the sales performance of product 20 across different orders.

Problem 8: Find distinct cities where employees are located

This involves selecting unique city names from the 'Employees' table and ordering them alphabetically. It helps assess geographic distribution of employees.

SELECT DISTINCT City

FROM Employees


ORDER BY City;

This list assists in regional staffing and operational planning.

Problem 9: List employees residing in London, Seattle, or Redmond, including their city

Filtering employees based on their city of residence, this query retrieves first and last names along with city, using the IN clause to specify multiple locations.

SELECT FirstName, LastName, City

FROM Employees


WHERE City IN ('London', 'Seattle', 'Redmond');

This focuses on specific geographical locations relevant for regional analysis or contact purposes.

Problem 10: Display product IDs and names for out-of-stock products priced between $20 and $25, including units-in-stock

This query filters products with zero units in stock and a unit price within the specified range. It displays ProductID, ProductName, and UnitsInStock for inventory management and sales analysis.

SELECT ProductID, ProductName, UnitsInStock

FROM Products


WHERE UnitsInStock = 0 AND UnitPrice BETWEEN 20 AND 25;

This information aids in stock replenishment decisions for moderately priced out-of-stock items.

Conclusion

Executing these SQL queries on the Northwind database demonstrates an understanding of core DML operations—SELECT, WHERE, GROUP BY, HAVING, ORDER BY, and JOIN. These queries support various data retrieval needs, from inventory and supplier management to customer analysis and sales reporting. Proper documentation with screenshots of query outputs reinforces the accuracy of results and the effectiveness of query construction. The use of the Northwind database as a practice tool enhances understanding of real-world database querying scenarios, preparation for advanced SQL tasks, and data-driven decision-making.

References

  • Fu, P. (2016). SQL For Beginners: Learn SQL the right way! SQL Tutorial for Beginners. CreateSpace Independent Publishing Platform.
  • Hipp, D. R. (2020). Beginning SQL, MySQL, and Database Design. Apress.
  • Valentine, J. (2020). Data Management and SQL for Dummies. Wiley.

Related Assignments