September 17, 2011 at 11:59 am
Question # 11: Create a loop to go through the products table ordered by productname . for each product give me the total quantity sold for each product and return a print statement that reads ///(as an example) a total of 7 monitor(s) were sold //// you can use any looping construct.
CREATE TABLE Customers
(
CustomerID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
City VARCHAR(50),
State CHAR(2),
Zip VARCHAR(10)
)
GO
CREATE TABLE Products
(
ProductID TINYINT IDENTITY PRIMARY KEY,
ProductName VARCHAR(20),
RecommendedPrice MONEY,
Category VARCHAR(10)
)
GO
CREATE TABLE Sales
(
SaleID INT IDENTITY PRIMARY KEY,
ProductID TINYINT NOT NULL REFERENCES Products(ProductID),
CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
SalePrice MONEY NOT NULL,
SaleDate SMALLDATETIME NOT NULL
)
GO
INSERT INTO Products(ProductName, RecommendedPrice, Category)
VALUES('DVD',105,'LivingRoom')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Microwave',98,'Kitchen')
INSERT INTO Products(ProductName, RecommendedPrice, Category)
VALUES('Monitor',200,'Office')
INSERT INTO Products(ProductName, RecommendedPrice, Category)
VALUES('Speakers',85,'Office')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Refrigerator',900,'Kitchen')
INSERT INTO Products(ProductName, RecommendedPrice, Category)
VALUES('VCR',165,'LivingRoom')INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('CoffeePot',35,'Kitchen')
GO
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('John','Miller','Asbury','NY','23433')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Fred','Hammill','Basham','AK','85675')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Stan','Mellish','Callahan','WY','38556')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Adrian','Caparzo','Denver','CO','12377')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Mike','Horvath','Easton','IN','47130')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Irwin','Wade','Frankfurt','KY','45902')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('George','Marshall','Gallipoli','ND','34908')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Frank','Costello','Honolulu','HI','23905')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Billy','Costigan','Immice','SC','75389')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Shelly','Sipes','Lights','AZ','35263')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Chirsty','Melton','Spade','CA','97505')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Amanda','Owens','Flask','CN','50386')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Brittany','Smits','Bourbon','KY','24207')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kristy','Bryant','Tarp','FL','58960')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kelly','Street','TableTop','ID','57732')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Tricia','Hill','Camera','ME','46738')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Holly','Raines','Compact','MS','35735')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Natalie','Woods','Woods','IN','87219')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Wendy','Hilton','Action','KY','47093')
GO
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,'2/6/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,97,'1/7/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,3,200,'8/8/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,4,80,'4/9/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,899,'10/10/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,6,150,'10/11/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,7,209,'12/12/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,8,90,'5/13/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,9,130,'6/14/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,14,85,'6/19/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,15,240,'9/20/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,16,99,'7/21/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,17,87,'3/22/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,18,99,'1/23/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,19,150,'3/24/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,900,'3/10/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,6,86,'8/11/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,88,'8/12/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,198,'12/13/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,9,150,'5/14/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,14,99,'7/19/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,15,104,'9/20/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,16,270,'2/21/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,17,90,'7/22/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,'3/6/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,102,'4/7/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,3,114,'11/8/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,4,1000,'5/9/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,1100,'10/10/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,6,285,'6/11/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,87,'10/12/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,300,'7/13/2005')
GO
September 17, 2011 at 12:23 pm
We don't do people's homework. You learn nothing that way.
Show what you have so far, explain where you are stuck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2011 at 1:54 pm
how can i get the total quantity.
September 17, 2011 at 2:02 pm
suggestion...read up on GROUP BY / SUM et al
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2011 at 3:10 pm
Chapter 4: Summing and grouping page 36 topic A.2
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 18, 2011 at 12:12 am
Hi,
Can you explain your desired output?
Shatrughna
September 18, 2011 at 8:43 am
The output should be the total quantity sold for each product and return a print statement that reads (as an example) a total of 7 monitor(s) were sold you can use any looping construct.
September 18, 2011 at 8:58 am
pathuripr (9/18/2011)
The output should be the total quantity sold for each product and return a print statement that reads (as an example) a total of 7 monitor(s) were sold you can use any looping construct.
which part of the question are you struggling with...the "total quantity" or the "print" statement.
did you research previous suggestion about GROUP BY / SUM ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2011 at 10:06 am
Total quantity
SELECT sum(SalePrice)
FROM Products p
INNER JOIN Sales s ON s.ProductID = p.ProductID
group by ProductName
September 18, 2011 at 10:18 am
Ok, so what exactly is it that you're stuck on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2011 at 11:10 am
pathuripr (9/17/2011)
Question # 11: Create a loop to go through the products table ordered by productname . for each product give me the total quantity sold for each product and return a print statement that reads ///(as an example) a total of 7 monitor(s) were sold //// you can use any looping construct.
Create a cursor on a query that lists all available product names and ids.
Fetch a row at a time from the cursor into variables.
Use the product id to query for the number of sales for the current product.
Construct and print the required message.
Repeat until the cursor runs out of rows.
The question does not say what you should do about Coffee Pots - the product exists, but none were sold. You need to decide whether to print a message that says zero were sold, or to omit that product.
Books Online (help) entries that will assist you:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 18, 2011 at 11:44 am
Having said all that, writing cursor code is a pretty poor way to learn to use SQL Server. Once you have written the required solution using a loop or cursor, show your tutor this set-based solution:
DECLARE @Message VARCHAR(8000) =
(
SELECT
'A total of ' +
CONVERT(VARCHAR(12), COUNT_BIG(s.SaleID)) + SPACE(1) +
p.ProductName + '(s) were sold' +
CHAR(13) + CHAR(10)
FROM dbo.Products AS p
LEFT JOIN dbo.Sales AS s ON
s.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY p.ProductName
FOR XML
PATH (''), TYPE
).value('(./text())[1]', 'VARCHAR(8000)')
PRINT @Message
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 18, 2011 at 1:05 pm
pathuripr (9/17/2011)
Question # 11: Create a loop to...
This is just so wrong... What is the name of the school where they are teaching you to do such a thing in SQL Server?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2011 at 1:35 pm
Jeff Moden (9/18/2011)
This is just so wrong... What is the name of the school where they are teaching you to do such a thing in SQL Server?
RBARs R Us 😀
BTW, congratulations on your recent accolade Mr Moden 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2011 at 2:38 pm
probably about the only thing missing to get a meaningful result is the product indication:
SELECT ProductName, sum(SalePrice)
FROM Products p
INNER JOIN Sales s ON s.ProductID = p.ProductID
group by ProductName
order by ProductName
Of course this will return a set and will not use the requested loop.
I just wanted to add this to "loop" :sick::sick::sick::sick::sick::sick::sick:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply