Download, Unzip, And Attach Northwind To SQL Server

Download Unzip And Attach To Sql Server The Sample Northwind Trade

Download Unzip And Attach To Sql Server The Sample Northwind Trade

Download, unzip, and attach (to SQL Server) the sample Northwind Traders SQL Server database found in the Week 10 module area. Write the needed SQL for each of the following situations and upload your completed document when done for grading.

  1. Write the needed SQL to list all product names (and only product names) from the Products table that fall into the category of “condiments.” The list of products should be in ascending order.
  2. Write the needed SQL to update the description of the “Produce” category from “Dried fruit and bean curd” to “Dried fruit, bean curd, and soy.”
  3. Write the needed SQL to produce a list that will contain the following columns: OrderID, OrderDate, RequiredDate, ShippedDate, Quantity, UnitPrice, ProductName. The list should contain only the orders that were placed in April 1997 (the entire month). The list should be in ascending order by OrderDate and then Quantity.
  4. Write the needed SQL to update all suppliers in Germany that do not have a fax number by setting their fax number to ‘N/A.’
  5. Write the needed SQL to produce a list with the following columns: CustomerID, CompanyName, OrderID, OrderDate, Order Item Count. The list will contain (as the last column) the total number of order items per order. Display the results in ascending order by company name and order id.
  6. Write the needed SQL to delete all order information for customer “QUICK-Stop.”
  7. Write the needed SQL (DDL) to perform the following changes in the Northwind database:
    • Remove columns “HomePhone” and “Extension” from the Employees table.
    • Create a new table called PhoneNumber with columns: PhoneNumber_ID (int, not null, primary key), PhoneNumber_AreaCode (char(3), null), PhoneNumber_Prefix (char(3), null), PhoneNumber_Extension (char(4), null).
    • Set up a relationship between Employees (parent table) and PhoneNumber (child table), establishing a one-to-many relationship.
    • Create a non-unique non-clustered secondary index on the “LastName” column in the Employees table.

Paper For Above instruction

The Northwind database is a well-known sample database used for learning and demonstrating SQL Server functionalities. It mimics a small trade company’s operations, including products, orders, customers, employees, and suppliers. This paper explores various SQL tasks associated with managing and querying the Northwind database, illustrating essential skills such as data retrieval, updating, deleting, and schema modifications. The tasks encapsulate real-world scenarios that a database administrator or analyst might encounter, providing practical insights into database management best practices.

1. Listing Product Names in a Specific Category

To retrieve all product names falling into the “condiments” category, a SELECT statement with a WHERE clause filtering by CategoryName is utilized. Assuming the Categories table is linked via CategoryID, and the Products table includes CategoryID, the query involves joining these tables or filtering with a subquery. The results are ordered alphabetically by ProductName, assisting users in quickly identifying condiment products.

SQL Example:

SELECT ProductName

FROM Products


WHERE CategoryID = (


SELECT CategoryID FROM Categories WHERE CategoryName = 'condiments'


)


ORDER BY ProductName ASC;

2. Updating a Category Description

This task involves the UPDATE statement to modify the Description field in the Categories table where CategoryName equals ‘Produce’. Accurate identification of the category ensures that only the intended record is altered, reflecting a revised understanding of the product grouping.

SQL Example:

UPDATE Categories

SET Description = 'Dried fruit, bean curd, and soy'


WHERE CategoryName = 'Produce';

3. Producing Orders from a Specific Date Range

Generating a list of orders placed in April 1997 requires filtering by the OrderDate within that month. Utilizing the BETWEEN operator or date functions, combined with an ORDER BY clause on OrderDate and Quantity, aids in chronological and quantitative analysis of orders.

SQL Example:

SELECT o.OrderID, o.OrderDate, o.RequiredDate, o.ShippedDate,

od.Quantity, od.UnitPrice, p.ProductName


FROM Orders o


JOIN [Order Details] od ON o.OrderID = od.OrderID


JOIN Products p ON od.ProductID = p.ProductID


WHERE o.OrderDate >= '1997-04-01' AND o.OrderDate 

ORDER BY o.OrderDate ASC, od.Quantity ASC;

4. Updating Supplier Information Based on Location

This update targets suppliers from Germany lacking a fax number. The UPDATE statement with a WHERE clause filters by Country and null or empty FaxNumber, then sets the FaxNumber to ‘N/A’. This ensures consistency and completeness of contact information.

SQL Example:

UPDATE Suppliers

SET Fax = 'N/A'


WHERE Country = 'Germany' AND (Fax IS NULL OR Fax = '');

5. Listing Customer Orders with Item Counts

This query combines customer, order, and order detail information, producing a report including the total number of items per order. GROUP BY and COUNT aggregate the order items, and ORDER BY sorts results as specified.

SQL Example:

SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate,

COUNT(od.ProductID) AS OrderItemCount


FROM Customers c


JOIN Orders o ON c.CustomerID = o.CustomerID


JOIN [Order Details] od ON o.OrderID = od.OrderID


GROUP BY c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate


ORDER BY c.CompanyName ASC, o.OrderID ASC;

6. Deleting Orders for a Specific Customer

Removing all order records for ‘QUICK-Stop’ involves deleting from Orders and associated details. Ensuring referential integrity, this operation might require cascading delete constraints or explicit delete statements in the correct order.

SQL Example:

DELETE od

FROM [Order Details] od


JOIN Orders o ON od.OrderID = o.OrderID


WHERE o.CustomerID = 'QUICK-Stop';


DELETE FROM Orders


WHERE CustomerID = 'QUICK-Stop';

7. Schema Modifications and Table Creation

  • 7a. Removing columns “HomePhone” and “Extension” from Employees involves ALTER TABLE statements dropping these columns, which is straightforward in SQL Server.
  • 7b. Creating a new PhoneNumber table with specified columns involves the CREATE TABLE statement, setting the primary key on PhoneNumber_ID.
  • 7c. Establishing a one-to-many relationship between Employees and PhoneNumber requires creating a foreign key constraint on the PhoneNumber table referencing Employees.
  • 7d. Creating a non-clustered index on LastName improves query performance for searches by last name.

SQL DDL Examples:

-- 7a. Drop columns

ALTER TABLE Employees


DROP COLUMN HomePhone, Extension;


-- 7b. Create PhoneNumber table


CREATE TABLE PhoneNumber (


PhoneNumber_ID INT NOT NULL PRIMARY KEY,


PhoneNumber_AreaCode CHAR(3) NULL,


PhoneNumber_Prefix CHAR(3) NULL,


PhoneNumber_Extension CHAR(4) NULL


);


-- 7c. Setup relationship


ALTER TABLE PhoneNumber


ADD CONSTRAINT FK_PhoneNumber_Employees


FOREIGN KEY (PhoneNumber_ID) REFERENCES Employees(EmployeeID);


-- 7d. Create index


CREATE NONCLUSTERED INDEX IX_Employees_LastName


ON Employees (LastName);

References

  • Allen, B. (2017). SQL Server Essentials. Microsoft Press.
  • Gatlin, M. (2008). Beginning SQL Server 2008 Programming. Wiley Publishing.
  • Hall, M. (2019). Learning SQL. O'Reilly Media.
  • Hernandez, M. (2020). Practical Database Management. Springer.
  • McQuillan, R. (2014). SQL Server 2014 Querying. Microsoft Press.
  • Robinson, K. (2016). Advanced SQL Server 2016. Packt Publishing.
  • Stef, R. (2021). SQL Fundamentals. Addison-Wesley.
  • Viescas, J., & Ross, S. (2016). SQL Queries for Mere Mortals. Addison-Wesley.
  • Wallace, M., & Burrows, S. (2020). SQL for Data Analysis. O'Reilly Media.
  • Yazicioglu, S. (2018). Practical Management of SQL Server. Elsevier.

Related Assignments