-3.1 Northwinds Calculated Fields. Madison Dewitt 1. List all customer ID's followed by a second column...

Transcribed Image Text:

-3.1 Northwinds Calculated Fields. Madison Dewitt 1. List all customer ID's followed by a second column combining customers' company names with their country, in parentheses. Results should look like this: "Alfreds Futterkiste (Germany)" The second column name should be: "Company (Country)" Sort by CustomerID 2. List all orders, with these fields: Order10, Orderbate, CustomerID, then in a single column like above, with "Company Name (Country)" HINT: This requires a 30IN. Sort by OrderDate then Order ID 3. List all product ID's, followed by a second column combining product names with their supplier company name in parantheses. Results should look like this: "Chai (Exotic Liquids)" The second column name should read: "Product (Supplier)" Sort by Productio 4. List all Orders with Products (Suppliers) OrderID, Orderpate, Product ID, then in a single column like above, with "Product Name (Supplier)" HINT: This requires multiple 30INS. Be sure to include all orders. Additional joins can be inner joins. Sort by OrderDate then Product ID -- 5. List Employees, including EmployeeID and a second column combining LastName, FirstName into a single column called Employee In this format: "LastName, FirstName" --Sort by EmployeeID. --6. We want to create new email addresses for each employee - List the EmployeeID for each employee, LastName, FirstName, followed by - a single field named NewEmail in this format: "FirstName.LastNane@Northwinds.com" - Sort by Employee ID. -7. List all Orders, including these fields: OrderID, OrderDate, Employee with "LastName, FirstName" in a single column, and NewEmail as constructed above Sort by OrderID 8. Calculate order subtotals per record by calculating Quantity times UnitPrice -Include these fields: OrderID, OrderDate, Product ID, UnitPrice, Discount, plus a new Discount Price column, calculated as UnitPrice (UnitPrice Discount) -9. Add two columns to the above, including Quantity and a new TotalPrice column, calculated as Discount Price times Quantity (NOTE: You will have to re-calculate Discount Price as part of the calculation for the TotalPrice column) -3.1 Northwinds Calculated Fields. Madison Dewitt 1. List all customer ID's followed by a second column combining customers' company names with their country, in parentheses. Results should look like this: "Alfreds Futterkiste (Germany)" The second column name should be: "Company (Country)" Sort by CustomerID 2. List all orders, with these fields: Order10, Orderbate, CustomerID, then in a single column like above, with "Company Name (Country)" HINT: This requires a 30IN. Sort by OrderDate then Order ID 3. List all product ID's, followed by a second column combining product names with their supplier company name in parantheses. Results should look like this: "Chai (Exotic Liquids)" The second column name should read: "Product (Supplier)" Sort by Productio 4. List all Orders with Products (Suppliers) OrderID, Orderpate, Product ID, then in a single column like above, with "Product Name (Supplier)" HINT: This requires multiple 30INS. Be sure to include all orders. Additional joins can be inner joins. Sort by OrderDate then Product ID -3.1 Northwinds Calculated Fields. Madison Dewitt 1. List all customer ID's followed by a second column combining customers' company names with their country, in parentheses. Results should look like this: "Alfreds Futterkiste (Germany)" The second column name should be: "Company (Country)" Sort by CustomerID 2. List all orders, with these fields: Order10, Orderbate, CustomerID, then in a single column like above, with "Company Name (Country)" HINT: This requires a 30IN. Sort by OrderDate then Order ID 3. List all product ID's, followed by a second column combining product names with their supplier company name in parantheses. Results should look like this: "Chai (Exotic Liquids)" The second column name should read: "Product (Supplier)" Sort by Productio 4. List all Orders with Products (Suppliers) OrderID, Orderpate, Product ID, then in a single column like above, with "Product Name (Supplier)" HINT: This requires multiple 30INS. Be sure to include all orders. Additional joins can be inner joins. Sort by OrderDate then Product ID -- 5. List Employees, including EmployeeID and a second column combining LastName, FirstName into a single column called Employee In this format: "LastName, FirstName" --Sort by EmployeeID. --6. We want to create new email addresses for each employee - List the EmployeeID for each employee, LastName, FirstName, followed by - a single field named NewEmail in this format: "FirstName.LastNane@Northwinds.com" - Sort by Employee ID. -7. List all Orders, including these fields: OrderID, OrderDate, Employee with "LastName, FirstName" in a single column, and NewEmail as constructed above Sort by OrderID 8. Calculate order subtotals per record by calculating Quantity times UnitPrice -Include these fields: OrderID, OrderDate, Product ID, UnitPrice, Discount, plus a new Discount Price column, calculated as UnitPrice (UnitPrice Discount) -9. Add two columns to the above, including Quantity and a new TotalPrice column, calculated as Discount Price times Quantity (NOTE: You will have to re-calculate Discount Price as part of the calculation for the TotalPrice column) -- 5. List Employees, including EmployeeID and a second column combining LastName, FirstName into a single column called Employee In this format: "LastName, FirstName" --Sort by EmployeeID. --6. We want to create new email addresses for each employee - List the EmployeeID for each employee, LastName, FirstName, followed by - a single field named NewEmail in this format: "FirstName.LastNane@Northwinds.com" - Sort by Employee ID. -7. List all Orders, including these fields: OrderID, OrderDate, Employee with "LastName, FirstName" in a single column, and NewEmail as constructed above Sort by OrderID 8. Calculate order subtotals per record by calculating Quantity times UnitPrice -Include these fields: OrderID, OrderDate, Product ID, UnitPrice, Discount, plus a new Discount Price column, calculated as UnitPrice (UnitPrice Discount) -9. Add two columns to the above, including Quantity and a new TotalPrice column, calculated as Discount Price times Quantity (NOTE: You will have to re-calculate Discount Price as part of the calculation for the TotalPrice column)

Answer rating: 100% (QA)

To create the calculated fields in Northwind you can use the following SQL queries Query 1 SELECT CustomerID CONCATCompanyName Country AS Company Country FROM Customers ORDER BY CustomerID A single st... View the full answer