Hi everyone i need some help to do this excercise and get some idea to do it

melix · March 20, 2016, 3:10am

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)

SELECT customer_id, last_name
FROM dbo.customer
WHERE customer_id IN (
SELECT Customer_ID
FROM order_
GROUP BY Customer_ID

this is my answer for the first question is this correct?

SELECT OrderID
FROM ORDERS
WHERE OrderStatus is NULL;

SELECT OrderID
FROM ORDERS
WHERE ItemID
GROUP BY ItemID
HAVING COUNT(DISTINCT)=4

hi this is my answer in 2 and 3 question in single table query
is this correct?

this is what I did for the questions

List the inventory items Description and Price and the Name and Phone Number of its vendor.

2.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.

3.List the customers that have not placed orders.

this is my answer is this correct

SELECT INVENTORYITEMS.Description,Price,VENDORS.Name,Phone
FROM INVENTORYITEMS INNER JOIN VENDORS
ON INVENTORYITEMS.VendorID = VENDORS.VendorID

SELECT OrderStatus,Dateordered
FROM ORDERS INNER JOIN ORDERITEMS
WHERE Quantity>4
GROUP BY ItemID;

SELECT CustomerID
FROM ORDERS LEFT JOIN ORDERITEMS
ON CustomerID=ORDERS.OrderID
WHERE ORDERS.CustomerID is NULL;

These 3 questions should be answered with subquery queries only

1.List the vendors whose products have been sold in a quantity greater than 2.

2.List the inventory Items ordered between January 1 and March 31.

3.List the customers that have not placed orders.

4.Create a procedure to update an inventory items price given a percentage as an integer

this is my answer to this question is this correct

SELECT VendorID
FROM VENDORS
WHERE VendorID IN (SELECT VendorID
FROM ORDERITEMS
WHERE Quantity > 2)

SELECT ItemID
FROM INVENTORYITEMS
WHERE ItemID IN (SELECT OrderID
FROM ORDERS
WHERE DateOrdered between '1/1/2016' and '3/31/2016')

SELECT CustomerID
FROM ORDERS LEFT JOIN ORDER
ON CustomerID=ORDERS.CustomerID
WHERE ORDERS.CustomerID is NULL;

CREATE PROCEDURE dbo.spgetInventory
@price INT
AS
BEGIN
SET NOCOUNT ON
UPDATE INVENTORYITEMS
WHERE ItemID = Price NOT NULL
END

Hi for q1 for subquery
I use orderitem column cause there a quantity colunm,
So should i use inventoryitem with vendorid column?and inner join then together?

SELECT VendorID
FROM vendors
Join inventoryitems
ON vendors. VendorID=inventoryitems.vendorid;
Is this correct now q1?

khtan · March 20, 2016, 8:51am

what have you tried so far ?

melix · March 20, 2016, 4:06pm

Nothing yet im kindly new in sql im still reading mt textbook and trying to find example and idea to do it

melix · March 20, 2016, 6:48pm

i did the creation already but the selecting the data from it i cannot figure it out
need help thank you

melix · March 21, 2016, 10:17pm

can someone help me pls

melix · March 22, 2016, 6:30am

SELECT customer_id, last_name
FROM dbo.customer
WHERE customer_id IN (
SELECT Customer_ID
FROM order_
GROUP BY Customer_ID

is this correct for the first question?

khtan · March 23, 2016, 11:46am

Yes. It is OK.

You may also use DISTINCT on ORDERS talbe, since the question only asking for displaying of CustomerID only

SELECT DISTINCT CustomerID FROM ORDERS

melix · March 23, 2016, 1:47pm

SELECT OrderID
FROM ORDERS
WHERE OrderStatus is NULL;

SELECT OrderID
FROM ORDERS
WHERE ItemID
GROUP BY ItemID
HAVING COUNT(DISTINCT)=4

hi this is my answer in 2 and 3 question in single table query
is this correct?

melix · March 23, 2016, 6:46pm

SELECT OrderID
FROM ORDERS
WHERE OrderStatus is NULL;

SELECT OrderID
FROM ORDERS
WHERE ItemID
GROUP BY ItemID
HAVING COUNT(DISTINCT)=4

hi this is my answer in 2 and 3 question in single table query
Is this correct? Need advice

khtan · March 24, 2016, 1:22am

Q 2. List the first order from the orders table that has not been shipped.

I am not sure what the OrderStatus value will be like if it is not shipped. But there is a "DateShipped" column. The column will probably be NULL if it is not shipped.

Q 3 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’.

It ask for Quantity, so, should get from the ORDERITEMS table which has the quantity column. And for Item starts with 4 use "LIKE '4%'"

melix · March 25, 2016, 9:32am

thank you for the advice really appreciate helping me

this is what I did for the questions

List the inventory items Description and Price and the Name and Phone Number of its vendor.

2.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.

3.List the customers that have not placed orders.

this is my answer is this correct

SELECT INVENTORYITEMS.Description,Price,VENDORS.Name,Phone
FROM INVENTORYITEMS INNER JOIN VENDORS
ON INVENTORYITEMS.VendorID = VENDORS.VendorID

SELECT OrderStatus,Dateordered
FROM ORDERS INNER JOIN ORDERITEMS
WHERE Quantity>4
GROUP BY ItemID;

SELECT CustomerID
FROM ORDERS LEFT JOIN ORDERITEMS
ON CustomerID=ORDERS.OrderID
WHERE ORDERS.CustomerID is NULL;

khtan · March 25, 2016, 11:56am

Q1 OK

Q2

SELECT     CUSTOMERS.FirstName,
           CUSTOMERS.LastName,
           ORDERS.OrderStatus,
           ORDERS.DateOrdered,
           COUNT(*) AS NoOfItems,
           AVG(Quantity) AS AvgQty
FROM       CUSTOMERS
INNER JOIN ORDERS        ON CUSTOMERS.CustomerID = ORDERS.CustomerID 
INNER JOIN ORDERITEMS    ON ORDERS.OrderID       = ORDERITEMS.OrderID
GROUP BY CUSTOMERS.FirstName,
         CUSTOMERS.LastName,
         ORDERS.OrderStatus,
         ORDERS.DateOrdered
HAVING COUNT(*) > 4

Q3

SELECT CustomerID
FROM ORDERS LEFT JOIN ORDER
ON CustomerID=ORDERS.CustomerID
WHERE ORDERS.CustomerID is NULL;

or you can use NOT EXISTS

ScottPletcher · March 25, 2016, 2:57pm

@khtan:
Doesn't doing someone else's homework violate this site's agreements? Typically that's just not done on these types of sites.

melix · March 26, 2016, 3:13am

These 3 questions should be answered with subquery queries only

1.List the vendors whose products have been sold in a quantity greater than 2.

2.List the inventory Items ordered between January 1 and March 31.

3.List the customers that have not placed orders.

4.Create a procedure to update an inventory items price given a percentage as an integer

this is my answer to this question is this correct

SELECT VendorID
FROM VENDORS
WHERE VendorID IN (SELECT VendorID
FROM ORDERITEMS
WHERE Quantity > 2)

SELECT ItemID
FROM INVENTORYITEMS
WHERE ItemID IN (SELECT OrderID
FROM ORDERS
WHERE DateOrdered between '1/1/2016' and '3/31/2016')

SELECT CustomerID
FROM ORDERS LEFT JOIN ORDER
ON CustomerID=ORDERS.CustomerID
WHERE ORDERS.CustomerID is NULL;

CREATE PROCEDURE dbo.spgetInventory
@price INT
AS
BEGIN
SET NOCOUNT ON
UPDATE INVENTORYITEMS
WHERE ItemID = Price NOT NULL
END

khtan · March 26, 2016, 5:55am

not really. OP does posted his attempted query and i help to correct the wrong syntax or error.

Well, maybe the last post is a bit carried away

khtan · March 27, 2016, 8:50am

table ORDERITEMS does not contain VendorID. You need to join to another table to obain the VendorID

Your WHERE clause is wrong. You are checking ItemID with OrderID.

When you write a query, identify from which table the information is available.

"ordered between January 1 and March 31."
Obviously the information must be comming from ORDERS table

"List the inventory Items ordered"
So this must be comming from ORDERITEMS table.
Now you have ORDERS & ORDERSITEMS table, just INNER JOIN it and you will get the result

OK. Alternative method is to use NOT EXISTS ( )

So what will be the information to pass in to the stored procedure ? Price and ItemID right ?

Your procedure is updating all inventory item . You need to change that

melix · March 27, 2016, 5:42pm

Hi for q1 for subquery
I use orderitem column cause there a quantity colunm,
So should i use inventoryitem with vendorid column?and inner join then together?

melix · March 27, 2016, 6:03pm

And for the q2 what clause i can use to get the date january 1 and march 31 can use having count greater than 2?

melix · March 27, 2016, 6:40pm

Q4 should i update price and itemid
in the inventoryitem table? To create procedure

melix · March 27, 2016, 6:59pm

SELECT VendorID
FROM vendors
Join inventoryitems
ON vendors. VendorID=inventoryitems.vendorid;
Is this correct now q1?