Add New Cateogry and Product - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Add New Cateogry and Product

Author

Topic

lakers34kb
Starting Member

15 Posts

Posted - 2010-02-12 : 00:51:55

Any help is appreciated thanks.

I need to write a script to insert the data:
One new Category
Name = Junk Food, Description = yummy delights
_____________________________________________________

Add a Product for your new Junk Food category.
Product = Twinkies, Supplier = Tokyo Traders, Category = Junk Food
QuantityPerUnit = 10 boxes
UnitsInStock = 0, UnitsOnOrder = 0, ReorderLevel = 10, Disabled = 0
_____________________________________________________________________

Script to set all Products in the Condiments category that have less than 20 units in stock to a price of $5.00 and disable them.
______________________________________________________________________

Need a script to delete the Junk Food Category as well as any Products assigned to that category.

http://i46.tinypic.com/2vkgrwj.jpg
http://i47.tinypic.com/347jwxv.jpg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:00:12

[code]
SELECT p.*
FROM Products p
INNER JOIN Category c
ON c.Name=p.Category
WHERE p.UnitsInStock<20
AND p.UnitPrice =5
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lakers34kb
Starting Member

15 Posts

Posted - 2010-02-12 : 04:15:27

What one is that for?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:25:46

quote:
Originally posted by lakers34kb

What one is that for?


DECLARE @SupplierID int,@CategoryID int

SELECT @SupplierID= SupplierID
FROM Supplier
WHERE Supplier='Tokyo Traders'

 Insert into Category (Name,Description)
   values ('Junk Food','yummy delights')
 select @CategoryID= SCOPE_IDENTITY() 

Insert INTO Product (Product, SupplierID, CategoryID,QuantityPerUnit,
UnitsInStock, UnitsOnOrder, ReorderLevel, Disabled)
Values ('Twinkies',@SupplierID,@CategoryID,'10 boxes',0,0,10,0)

UPDATE p
SET p.unitprice=5.00,
p.Disabled=1
FROM Product p
JOIN Category c
ON c.CategoryID=p.CategoryID
WHERE c.Name='Condiments'
AND p.UnitsInStock<20

DELETE p
FROM Products p
JOIN Category c
ON c.CategoryID=p.CategoryID
WHERE c.Name='Junk Food'

DELETE c FROM Category c WHERE  c.Name='Junk Food'