Mega Code Archive

 

Categories / MSSQL Tutorial / Query

 

Do our sorting using numeric fields

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