|
7> CREATE TABLE Products (
8> ProductID int NOT NULL ,
9> ProductName nvarchar (40) NOT NULL ,
10> SupplierID int NULL ,
11> CategoryID int NULL ,
12> QuantityPerUnit nvarchar (20) NULL ,
13> UnitPrice money NULL,
14> UnitsInStock smallint NULL,
15> UnitsOnOrder smallint NULL,
16> ReorderLevel smallint NULL,
17> Discontinued bit NOT NULL
18> )
19> GO
1> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(71,'F',15,4,'10 - 999 g pkgs.',61.5,66,6,6,6)
2> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(72,'M',14,4,'24 - 888 g pkgs.',34.8,74,7,7,7)
3> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(73,'R',17,8,'24 - 777 g jars',17,171,0,5,0)
4> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(74,'L',4,7,'5 kg pkg.',10,4,20,5,0)
5> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(75,'R',12,1,'24 - 0.5 l bottles',1.23,445,0,25,0)
6> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(76,'L',23,1,'500 ml',18,57,1,20,0)
7> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(77,'O',12,2,'12 boxes',13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder
3> FROM Products
4> WHERE UnitsOnOrder > 0
5> AND UnitsInStock < 10
6> ORDER BY UnitsOnOrder DESC
7> GO
ProductID ProductName UnitsInStock UnitsOnOrder
----------- ---------------------------------------- ------------ ------------
74 L 4 20
(1 rows affected)
1>
2> drop table products;
3> GO
|