These 3 questions should be answered with a single table query only
List all the customerID’s who have placed orders, display each customerID only once. Order by the customerID.
List the first order from the orders table that has not been shipped.
Create a list of the order numbers, the quantity and the item of the order for all ordered items whose itemID starts with a ‘4’.
These 3 questions should be answered with join queries only
List the inventory items Description and Price and the Name and Phone Number of its vendor.
List the orders customer name, order status, date ordered, count of items on the order, and average quantity ordered where the count of items on the order is greater than 4.
List the customers that have not placed orders.
These 3 questions should be answered with subquery queries only
List the vendors whose products have been sold in a quantity greater than 2.
List the inventory Items ordered between January 1 and March 31.
List the customers that have not placed orders.
Create a procedure to update an inventory items price given a percentage as an integer
CREATE TABLE CUSTOMERS (
CustomerID INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(45) NOT NULL,
LastName VARCHAR(45) NOT NULL,
Street VARCHAR(45) NULL,
City VARCHAR(45) NULL,
State CHAR(2) NULL,
ZIP VARCHAR(10) NULL,
Phone VARCHAR(12) NULL)
CREATE TABLE ORDERSTATUSES (
OrderStatus VARCHAR(45) NOT NULL,
PRIMARY KEY (OrderStatus))
CREATE TABLE VENDORS (
VendorID INT NOT NULL PRIMARY KEY IDENTITY,
Name VARCHAR(45) NOT NULL,
Street VARCHAR(45) NULL,
City VARCHAR(45) NULL,
State CHAR(2) NULL,
ZIP VARCHAR(45) NULL,
Phone VARCHAR(12) NULL)
CREATE TABLE ORDERS (
OrderID INT NOT NULL PRIMARY KEY IDENTITY,
CustomerID INT NOT NULL,
OrderStatus VARCHAR(45) NULL,
DateOrdered DATE NULL,
DateShipped DATE NULL,
CONSTRAINT fk_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES CUSTOMERS(CustomerID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_Orders_OrderStautses
FOREIGN KEY (OrderStatus)
REFERENCES ORDERSTATUSES(OrderStatus)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
CREATE TABLE INVENTORYITEMS (
ItemID INT NOT NULL PRIMARY KEY IDENTITY,
Description VARCHAR(200) NOT NULL,
Price DECIMAL(6,2) NOT NULL,
QuantityInStock INT NULL,
VendorID INT NULL,
CONSTRAINT fk_InventoryItems_Vendor
FOREIGN KEY (VendorID)
REFERENCES VENDORS(VendorID)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
CREATE TABLE ORDERITEMS (
OrderID INT NOT NULL,
SequenceNumber INT NOT NULL,
ItemID INT NOT NULL,
Quantity INT NOT NULL,
PRIMARY KEY (OrderID, SequenceNumber),
CONSTRAINT fk_OrderItems_Orders
FOREIGN KEY (OrderID)
REFERENCES ORDERS(OrderID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_OrderItems_InventoryItems
FOREIGN KEY (ItemID)
REFERENCES INVENTORYITEMS(ItemID)
ON DELETE NO ACTION
ON UPDATE NO ACTION)