Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

NorthWind traders wants to know the following information about their orders for products supplied by Supplier number 12 and fulfilled by employee number 2 a.

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedNorthWind traders wants to know the following information about their orders for products supplied by Supplier number 12 and fulfilled by employee number 2

a. The First and Last name of the employee who took the order

b. The customer name (CompanyName) of the customer who placed the order

c. The OrderId and the ShippedDate.

d. The ProductId and ProductName of the item(s) ordered

e. The SupplierID and CompanyName of the supplier

f. The ShipperId and CompanyName of the Shipper.

Sort the output by OrderDate then by CustomerID. Since several of the fields use the same name use column aliases where needed to differentiate between the columns with the same names

I'm having a difficult time figuring out how to join these all together to receive the correct results. To my understanding it's joining 4 tables? However, I'm not entirely sure on the syntax. Could someone help me? Thank you so much. This is for sql and the database is NorthWinds

Allow Nulls * Column Name CustomerID Company Name ContactName ContactTitle Address Data Type nchar(5) nvarchar(40) nvarchar(30) nvarchar(30) nvarchar(60) nvarchar(15) nvarchar(15) nvarchar(10) nvarchar(15) nvarchar(24) nvarchar(24) City DODO Region PostalCode Country Phone Fax Connect Y the System Tables # File Tables # External Tables + Graph Tables + 8 dbo.Categories + dbo.Contacts + 2 dbo.CustomerCustomerDemo + ! dbo.Customer Demographics + 9 dbo.Customers dbo.Employees # dbo.Employee Territories + dbo.Jobs # dbo.Order Details + dbo.Orders + dbo.Products + dbo.Region # dbo.Shippers dbo.Suppliers # dbo.Territories # Views # External Resources # Synonyms # Programmability # Service Broker # Storage Security Security Server Objects Replication PolyBase Management XEvent Profiler Column Properties (General) (Name) Allow Nulls Data Type Default Value or Binding Length CustomerlD No nchar (General) Connect T o th Allow Nulls Column Name Employeeld LastName FirstName Title TitleOfCourtesy Birth Date HireDate Address City Region PostalCode Country Home Phone Graph Tables # 3 dbo.Categories # 3 dbo.Contacts + 2 dbo.CustomerCustomerDemo + 2 dbo.CustomerDemographics + 3 dbo.Customers 2 dbo.Employees Columns Employeeld (PK, int, not null) | LastName (nvarchar(20), not null) FirstName (nvarchar(10), not null) Title (nvarchar(30), null) TitleOfCourtesy (nvarchar(25), null) BirthDate (datetime, null) HireDate (datetime, null) Address (nvarchar(60), null) City (nvarchar(15), null) Region (nvarchar(15), null) PostalCode (nvarchar(10), null) Country (nvarchar(15), null) B HomePhone (nvarchar(24), null) Extension (nvarchar(4), null) Photo (image, null) Notes (ntext, null) ow ReportsTo (FK, int, null) PhotoPath (nvarchar(255), null) w Jobid (FK, int, null) + Keys # Constraints # Triggers + Indexes # Statistics Data Type int nvarchar(20) nvarchar(10) nvarchar(30) nvarchar(25) datetime datetime nvarchar(60) nvarchar(15) nvarchar(15) nvarchar(10) nvarchar(15) nvarchar(24) nvarchar(4) image ntext DINNI Extension Photo Notes Reports To PhotoPath Jobid int nvarchar(255) int Column Properties Allow Nulls Data Type int nchar(5) int date date date Connect - * Yo the + System Tables # FileTables # External Tables Graph Tables + dbo.Categories # 3 dbo.Contacts dbo.CustomerCustomerDemo + 2 dbo.CustomerDemographics + 3 dbo.Customers # dbo.Employees + dbo.Employee Territories + 3 dbo.Jobs + dbo.Order Details E dbo.Orders + 3 dbo.Products + 8 dbo.Region - dbo.Shippers # Columns + Keys # Constraints + Triggers # Indexes Statistics 5 dbo.Suppliers # Columns + Keys Constraints Triggers # Indexes # Statistics dbo.Territories # Views Column Name OrderID CustomerID Employeeld Order Date Required Date Shipped Date ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry int money nvarchar(40) nvarchar(60) nvarchar(15) nvarchar(15) nvarchar(10) nvarchar(15) Column Properties OrderID No (General) (Name) Allow Nulls Data Type Default Value or Binding Table Designer int (General) Data Type Allow Nulls Column Name ShipperlD Company Name Phone nvarchar(40) nvarchar(24) DOO Connect - * * the System Tables # File Tables # External Tables + Graph Tables # dbo.Categories # ! dbo.Contacts + 2 dbo.CustomerCustomerDemo + 2 dbo.CustomerDemographics # dbo.Customers # dbo.Employees + dbo.Employee Territories # dboJobs # dbo.Order Details # ! dbo.Orders + 8 dbo.Products # I dbo.Region - dbo.Shippers # Columns # Keys # Constraints # Triggers Indexes # Statistics + dbo.Suppliers dbo.Territories Views # External Resources # Synonyms Programmability # Service Broker # Storage # Security Column Properties (General) (Name) Allow Nulls Data Type Default Value or Binding Table Designer ShipperlD No int (General) L Data Type Allow Nulls int Column Name SupplierlD CompanyName ContactName ContactTitle Address City Region PostalCode nvarchar(40) nvarchar(30) nvarchar(30) nvarchar(60) nvarchar(15) nvarchar(15) nvarchar(10) nvarchar(15) nvarchar(24) nvarchar(24) Country Phone III Fax HomePage Connect - i ** the System Tables # FileTables # External Tables # Graph Tables + 9 dbo.Categories + dbo.Contacts # D dbo.CustomerCustomer Demo + 2 dbo.CustomerDemographics dbo.Customers # 3 dbo.Employees # B dbo.Employee Territories 3 dbo.Jobs # 3 dbo.Order Details + dbo.Orders # 3 dbo.Products # dbo.Region - dbo.Shippers # Columns + Keys # Constraints + Triggers # Indexes + Statistics 6 dbo.Suppliers # Columns + Keys # Constraints # Triggers Indexes # Statistics + dbo.Territories # Views ntext Column Properties (General) (Name) Allow Nulls Data Type Default Value or Binding Table Designer SupplierlD No int (General) O Ready

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Data Management Databases And Organizations

Authors: Richard T. Watson

2nd Edition

0471180742, 978-0471180746

More Books

Students also viewed these Databases questions