Question

Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause)

Need to Create a Query to display products purchased by customers in Germany that were not purchased by customers in the US.

Customers PK Customer D CompanyName Contact Name Contact Title Address City State OrRegion PostalCode Country Phone char(5) varchar(40) varchar(30) varchar(30) varchar(60) varchar(15) varchar(15) varchar(10) varchar(15) varchar(24) varchar(24) Order Details Orders PK OrderID int FK CustomerlD char(5) FK Employeeld int Order Date datetime Required Date datetime ShippedDate datetime FK ShipVia int Freight money ShipName varchar(40) ShipAddress varchar(60) Ship City varchar(15) ShipStateOrRegion varchar(15) ShipPostalCode varchar(10) ShipCountry varchar(15) int Fax PK,FK OrderID PK,FK ProductID UnitPrice Quantity Discount int money smallint decimal(3.2) Products Employees PK Employeeld int LastName varchar(20) FirstName varchar(10) Job Title varchar(30) Birth Date date Hire Date date Termination Date date Address varchar(60) City varchar(15) StateOrRegion varchar(15) PostalCode varchar(10) Country varchar(15) Home Phone varchar(24) Extension varchar(4) EmailAddress varchar(100) Notes varchar(max) FK Reports To int Shippers PK ShipperID int CompanyName varchar(40) Phone varchar(24) Suppliers PK Supplier D int CompanyName varchar(40) ContactName varchar(30) Contact Title varchar(30) Address varchar(60) City varchar(15) State OrRegion varchar(15) PostalCode varchar(10) Country varchar(15) Phone varchar(24) Fax varchar(24) PK ProductID int ProductName varchar(40) FK SupplierID int FK CategoryID int QuantityPerUnit varchar(20) UnitPrice money Wholesale Price money UnitsInStock smallint Units OnOrder smallint ReorderLevel smallint Discontinued bit Employee Territories PK,FK Employeeld int PK,FK TerritoryID int Territories PK TerritoryID int Territory Description varchar(50) FK RegionID int Categories PK CategoryID int CategoryName varchar(15) Description varchar(max) Region PK RegionID RegionDescription varchar(50) int

Answer 1

SELECT P.ProductID,P.ProductName FROM

Products P INNER JOIN OrderDetails OD ON OD.ProductID=P.ProductID

INNER JOIN Orders O ON O.OrderID = OD.OrderID

INNER JOIN Customers C ON C.CustomerID =O.CustomerID

WHERE C.Country ='Germany' AND

P.ProductID NOT IN (

SELECT P.ProductID FROM

Products P INNER JOIN OrderDetails OD ON OD.ProductID=P.ProductID

INNER JOIN Orders O ON O.OrderID = OD.OrderID

INNER JOIN Customers C ON C.CustomerID =O.CustomerID

WHERE C.Country ='US'

);

Explanation:-

Here, we are using SELECT to fetch data from the table.For product we are fetching only ProductId and ProductName.

We have used INNER JOIN to combine the tables.

We joined products with orderdetails ,then to orders and then customers.

For filtering data we are using WHERE clause in which we are checking for country to be Germany of customers and then we use AND operator to check for productid not in the list of products which is purchased by customer in US using NOT IN operator.

For getting that list of customers we used subquery which is similar to the main query and we fetched only productId.