SQLTeam.com | Weblogs | Forums

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


#1

These 3 questions should be answered with a single table query only

  1. List all the customerID’s who have placed orders, display each customerID only once. Order by the customerID.

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

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

These 3 questions should be answered with join queries only

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

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

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?


#2

what have you tried so far ?


#3

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


#4

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


#5

can someone help me pls


#6

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?


#7

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


#9

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?


#10

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


#11

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%'"


#12

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;


#13

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


#14

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


#15

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


#16

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 :slight_smile:


#17

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


#18

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?


#19

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?


#20

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


#21

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