Question

Display all customers. If a customer has placed any orders, also display the highest.....

Exercise 6.9 Display all customers. If a customer has placed any orders, also display the highest unit price in any order of , ever ordeted that customer. Otherwise, display null. We want to know each customer's highest price item Show customer ID, customer name, and the highest unit price in any order of that customer Highest unit price Customerld CustomerName 1599.99 Just Electronics Beyond Blectronics 1449.99 Beyond Electronics 1499.99 99.99 2 3 3 E Fun 4 999.99 Overstock E 5 5 NULL E Fun 6 6 79.99 Electronics4U 7 7 NULL Cheap Electronics LC

Tables:

CREATE TABLE Sales.Customers

(

CustomerId          INT          NOT NULL IDENTITY,

CustomerName          NVARCHAR(50) NOT NULL,

StreetAddress NVARCHAR(50) NULL,

City          NVARCHAR(20) NULL,

[State]          NVARCHAR(20) NULL,

PostalCode         NVARCHAR(10) NULL,

Country          NVARCHAR(20) NULL,

Contact          NVARCHAR(50) NULL,

Email         NVARCHAR(50) NULL,

CONSTRAINT PK_Customers PRIMARY KEY(CustomerId)

);

CREATE TABLE HR.Employees

(

EmployeeId          INT          NOT NULL IDENTITY,

FirstName         NVARCHAR(50) NOT NULL,

LastName          NVARCHAR(50) NOT NULL,

BirthDate         DATE         NOT NULL,

HireDate          DATE         NOT NULL,

HomeAddress NVARCHAR(50) NULL,

City          NVARCHAR(20) NULL,

[State]         NVARCHAR(20) NULL,

PostalCode        NVARCHAR(10) NULL,

Phone         NVARCHAR(20) NULL,

ManagerId         INT           NULL,

CONSTRAINT PK_Employees PRIMARY KEY(EmployeeId),

CONSTRAINT FK_Employees_Employees FOREIGN KEY(ManagerId)

        REFERENCES HR.Employees(EmployeeId),

CONSTRAINT CHK_BirthDate CHECK(BirthDate <= CAST(SYSDATETIME() AS DATE)),

CONSTRAINT CHK_HireDate CHECK(BirthDate < HireDate)

);

CREATE TABLE Purchasing.Suppliers

(

SupplierId         INT          NOT NULL IDENTITY,

SupplierName NVARCHAR(50) NOT NULL,

StreetAddress NVARCHAR(50) NULL,

City          NVARCHAR(20) NULL,

[State]          NVARCHAR(20) NULL,

PostalCode         NVARCHAR(10) NULL,

Country                  NVARCHAR(20) NULL,

CONSTRAINT PK_Supplier PRIMARY KEY(SupplierId)

);

CREATE TABLE Purchasing.Products

(

ProductId          INT          NOT NULL IDENTITY,

ProductName NVARCHAR(40) NOT NULL,

supplierid          INT          NOT NULL,

CONSTRAINT PK_Products PRIMARY KEY(ProductId),

CONSTRAINT FK_Products_Suppliers FOREIGN KEY(SupplierId)

        REFERENCES Purchasing.Suppliers(SupplierId)

);

CREATE TABLE Purchasing.Deliveries

(

        DeliveryId         INT         NOT NULL IDENTITY,

        ProductId          INT         NOT NULL,

        Quantity          INT         NOT NULL,

        Price          MONEY          NOT NULL

        CONSTRAINT DF_Deliveries_price DEFAULT(0),

        DeliveryDate          DateTime2          NOT NULL

        CONSTRAINT DF_Deliveries_DeliveryDate DEFAULT(SYSDATETIME()),

        CONSTRAINT PK_Deliveries PRIMARY KEY (DeliveryId),

        CONSTRAINT FK_Deliveries_Products FOREIGN KEY(ProductId)

        REFERENCES Purchasing.Products(ProductId),

        CONSTRAINT CHK_Deliveries_Price CHECK (Price >= 0)

);

CREATE TABLE Sales.Orders

(

OrderId           INT NOT NULL IDENTITY,

CustomerId         INT          NOT NULL,

EmployeeId         INT          NOT NULL,

OrderDate          DATE         NOT NULL,

CONSTRAINT PK_Orders PRIMARY KEY(OrderId),

CONSTRAINT FK_Orders_Customers FOREIGN KEY(CustomerId)

        REFERENCES Sales.Customers(CustomerId),

CONSTRAINT FK_Orders_Employees FOREIGN KEY(EmployeeId)

        REFERENCES HR.Employees(EmployeeId)

);

CREATE TABLE Sales.OrderDetails

(

OrderId INT NOT NULL,

ProductId INT         NOT NULL,

Price          MONEY NOT NULL

        CONSTRAINT DF_OrderDetails_price DEFAULT(0),

quantity SMALLINT NOT NULL

        CONSTRAINT DF_OrderDetails_qty DEFAULT(1),

CONSTRAINT PK_OrderDetails PRIMARY KEY(Orderid, Productid),

CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(OrderId)

        REFERENCES Sales.Orders(OrderId),

CONSTRAINT FK_OrderDetails_Products FOREIGN KEY(ProductId)

        REFERENCES Purchasing.Products(ProductId),

CONSTRAINT CHK_quantity CHECK (quantity > 0),

CONSTRAINT CHK_price CHECK (price >= 0)

);

Answer 1

Please find the SQL query below.

SELECT c.CustomerId, c.CustomerName, max(od.Price) FROM Customer c LEFT JOIN Orders o ON c.CustomerId = o.CustomerId JOIN OrderDetails od ON o.OrderId = od.OrderId GROUP BY o.CustomerId;