Display all customers. If a customer has placed any orders, also display the highest.....
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)
);
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;
Display all customers. If a customer has placed any orders, also display the highest..... Tables: CREATE TABLE Sales.Cu...
Hello, Does anyone have any input on how to fragment this table in visual studio? CREATE TABLE [dbo].[Stock] ( [itemNo] INT NOT NULL , [store] VARCHAR(50) NOT NULL, [qtyOnHand] INT NULL, [qtyHand] INT NULL, [qtyOnOrder] INT NULL, [reorderPoint] INT NULL, CONSTRAINT [FK_Stock_Item] FOREIGN KEY ([itemNo]) REFERENCES [Item]([itemNo]), CONSTRAINT [FK_Stock_Store] FOREIGN KEY ([store]) REFERENCES [Store]([storeName]), CONSTRAINT [PK_Stock] PRIMARY KEY ([itemNo], [store]), The other tables are: CREATE TABLE [dbo].[Item] ( [itemNo] INT NOT NULL PRIMARY KEY, [itemName] VARCHAR(50) NULL, [supplier] VARCHAR(50) NULL,...
Based on the CREATE TABLE statements, make an ER model of the database. Give suitable names to the relationships. (Remember cardinality and participation constraints.) The diagram must use either the notation used in the textbook (and the lectures) or the crow’s foot notation. To save you some time: There are a few tables that include the following address fields: Address, City, State, Country and PostalCode (and the same fields with the prefix Billing-). You are allowed to replace these attributes...
-- Schema definition create table Customer ( cid smallint not null, name varchar(20), city varchar(15), constraint customer_pk primary key (cid) ); create table Club ( club varchar(15) not null, desc varchar(50), constraint club_pk primary key (club) ); create table Member ( club varchar(15) not null, cid smallint not null, constraint member_pk primary key (club, cid), constraint mem_fk_club foreign key (club) references Club, constraint mem_fk_cust...
The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Here is the schema of the database: Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued); Suppliers (SupplierID, CompanyName, ContactName , ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage); Categories (CategoryID, CategoryName, Description, Picture); Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry); Order_details (ID, OrderID,...
An online company sells hundreds of office supply products on its e-Commerce store. It has asked you to design and implement a sales order processing system which will be used by users in our Operations department to process sales orders. Here is an overview of the sales order process. Customers make purchases by placing orders. Each customer has a customer number and profile (such as name, shipping address). To simplify the matter, each time an order is placed, only one...
-- drop tables DROP TABLE REQUEST CASCADE CONSTRAINTS; DROP TABLE FULLORDER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; -- create and link tables CREATE TABLE CUSTOMER ( CustomerID INTEGER, Name VARCHAR2(40), City VARCHAR2(20), State CHAR(2), Zip CHAR(5), CONSTRAINT PK_CUSTOMER PRIMARY KEY (CustomerID) ); CREATE TABLE PRODUCT ( ProductID INTEGER, Description VARCHAR2(30), Material VARCHAR2(20), Price NUMBER(5,2), CONSTRAINT PK_PRODUCT...
For each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order. Use equal join for this query. CREATE TABLE customer ( cust_id number(11,0) not null, cust_name varchar2(25) not null, street varchar2(30), city varchar2(20), state varchar2(2), zipcode varchar2(5), CONSTRAINT customer_pk PRIMARY KEY (cust_id) ); CREATE TABLE ordertable ( order_id number(11,0) not null, order_date date, cust_id number(11,0), CONSTRAINT order_pk PRIMARY KEY (order_id), CONSTRAINT order_fk FOREIGN KEY (cust_id) REFERENCES customer (cust_id)); CREATE TABLE...
-- drop tables DROP TABLE REQUEST CASCADE CONSTRAINTS; DROP TABLE FULLORDER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; -- create and link tables CREATE TABLE CUSTOMER ( CustomerID INTEGER, Name VARCHAR2(40), City VARCHAR2(20), State CHAR(2), Zip CHAR(5), CONSTRAINT PK_CUSTOMER PRIMARY KEY (CustomerID) ); CREATE TABLE PRODUCT ( ProductID INTEGER, Description VARCHAR2(30), Material VARCHAR2(20), Price NUMBER(5,2), CONSTRAINT PK_PRODUCT...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
Customer (CustomerId, CustomerName) Employee (EmployeeId, EmployeeName, Salary, SupervisorId) Product(ProductId, ProductName, ListPrice) Orders (OrderId, OrderDate, CustomerId, EmployeeId, Total) OrderedProduct (OrderId, ProductId, Quantity, Price) Write the code to complete the methods in OrderJDBC.java (look for TODO items). <---**IN BOLD** throughout code. /* OrderJDBC.java - A JDBC program for accessing and updating an order database on MySQL. */ import java.io.File; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; /** * An application for...