Posted By

mShady on 10/19/10


Tagged


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

sharathreddyp
Tyster


Install Northwind


 / Published in: SQL
 

  1. /*
  2. ** Copyright Microsoft, Inc. 1994 - 2000
  3. ** All Rights Reserved.
  4. */
  5.  
  6. SET NOCOUNT ON
  7. GO
  8.  
  9. USE master
  10. GO
  11. IF EXISTS (SELECT * FROM sysdatabases WHERE name='Northwind')
  12. DROP DATABASE Northwind
  13. go
  14.  
  15. DECLARE @device_directory NVARCHAR(520)
  16. SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
  17. FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1
  18.  
  19. EXECUTE (N'CREATE DATABASE Northwind
  20. ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')
  21. LOG ON (NAME = N''Northwind_log'', FILENAME = N''' + @device_directory + N'northwnd.ldf'')')
  22. go
  23.  
  24. exec sp_dboption 'Northwind','trunc. log on chkpt.','true'
  25. exec sp_dboption 'Northwind','select into/bulkcopy','true'
  26. GO
  27.  
  28. SET quoted_identifier ON
  29. GO
  30.  
  31. /* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
  32.   the default DATEFORMAT on the server.
  33. */
  34. SET DATEFORMAT mdy
  35. GO
  36. USE "Northwind"
  37. go
  38. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Employee Sales by Country') AND sysstat & 0xf = 4)
  39. DROP procedure "dbo"."Employee Sales by Country"
  40. GO
  41. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Sales by Year') AND sysstat & 0xf = 4)
  42. DROP procedure "dbo"."Sales by Year"
  43. GO
  44. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Ten Most Expensive Products') AND sysstat & 0xf = 4)
  45. DROP procedure "dbo"."Ten Most Expensive Products"
  46. GO
  47. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Category Sales for 1997') AND sysstat & 0xf = 2)
  48. DROP VIEW "dbo"."Category Sales for 1997"
  49. GO
  50. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Sales by Category') AND sysstat & 0xf = 2)
  51. DROP VIEW "dbo"."Sales by Category"
  52. GO
  53. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Sales Totals by Amount') AND sysstat & 0xf = 2)
  54. DROP VIEW "dbo"."Sales Totals by Amount"
  55. GO
  56. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Summary of Sales by Quarter') AND sysstat & 0xf = 2)
  57. DROP VIEW "dbo"."Summary of Sales by Quarter"
  58. GO
  59. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Summary of Sales by Year') AND sysstat & 0xf = 2)
  60. DROP VIEW "dbo"."Summary of Sales by Year"
  61. GO
  62. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Invoices') AND sysstat & 0xf = 2)
  63. DROP VIEW "dbo"."Invoices"
  64. GO
  65. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Order Details Extended') AND sysstat & 0xf = 2)
  66. DROP VIEW "dbo"."Order Details Extended"
  67. GO
  68. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Order Subtotals') AND sysstat & 0xf = 2)
  69. DROP VIEW "dbo"."Order Subtotals"
  70. GO
  71. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Product Sales for 1997') AND sysstat & 0xf = 2)
  72. DROP VIEW "dbo"."Product Sales for 1997"
  73. GO
  74. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Alphabetical list of products') AND sysstat & 0xf = 2)
  75. DROP VIEW "dbo"."Alphabetical list of products"
  76. GO
  77. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Current Product List') AND sysstat & 0xf = 2)
  78. DROP VIEW "dbo"."Current Product List"
  79. GO
  80. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Orders Qry') AND sysstat & 0xf = 2)
  81. DROP VIEW "dbo"."Orders Qry"
  82. GO
  83. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Products Above Average Price') AND sysstat & 0xf = 2)
  84. DROP VIEW "dbo"."Products Above Average Price"
  85. GO
  86. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Products by Category') AND sysstat & 0xf = 2)
  87. DROP VIEW "dbo"."Products by Category"
  88. GO
  89. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Quarterly Orders') AND sysstat & 0xf = 2)
  90. DROP VIEW "dbo"."Quarterly Orders"
  91. GO
  92. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Customer and Suppliers by City') AND sysstat & 0xf = 2)
  93. DROP VIEW "dbo"."Customer and Suppliers by City"
  94. GO
  95. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Order Details') AND sysstat & 0xf = 3)
  96. DROP TABLE "dbo"."Order Details"
  97. GO
  98. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Orders') AND sysstat & 0xf = 3)
  99. DROP TABLE "dbo"."Orders"
  100. GO
  101. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Products') AND sysstat & 0xf = 3)
  102. DROP TABLE "dbo"."Products"
  103. GO
  104. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Categories') AND sysstat & 0xf = 3)
  105. DROP TABLE "dbo"."Categories"
  106. GO
  107. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Customers') AND sysstat & 0xf = 3)
  108. DROP TABLE "dbo"."Customers"
  109. GO
  110. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Shippers') AND sysstat & 0xf = 3)
  111. DROP TABLE "dbo"."Shippers"
  112. GO
  113. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Suppliers') AND sysstat & 0xf = 3)
  114. DROP TABLE "dbo"."Suppliers"
  115. GO
  116. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.Employees') AND sysstat & 0xf = 3)
  117. DROP TABLE "dbo"."Employees"
  118. GO
  119. CREATE TABLE "Employees" (
  120. "EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
  121. "LastName" nvarchar (20) NOT NULL ,
  122. "FirstName" nvarchar (10) NOT NULL ,
  123. "Title" nvarchar (30) NULL ,
  124. "TitleOfCourtesy" nvarchar (25) NULL ,
  125. "BirthDate" "datetime" NULL ,
  126. "HireDate" "datetime" NULL ,
  127. "Address" nvarchar (60) NULL ,
  128. "City" nvarchar (15) NULL ,
  129. "Region" nvarchar (15) NULL ,
  130. "PostalCode" nvarchar (10) NULL ,
  131. "Country" nvarchar (15) NULL ,
  132. "HomePhone" nvarchar (24) NULL ,
  133. "Extension" nvarchar (4) NULL ,
  134. "Photo" "image" NULL ,
  135. "Notes" "ntext" NULL ,
  136. "ReportsTo" "int" NULL ,
  137. "PhotoPath" nvarchar (255) NULL ,
  138. CONSTRAINT "PK_Employees" PRIMARY KEY CLUSTERED
  139. (
  140. "EmployeeID"
  141. ),
  142. CONSTRAINT "FK_Employees_Employees" FOREIGN KEY
  143. (
  144. "ReportsTo"
  145. ) REFERENCES "dbo"."Employees" (
  146. "EmployeeID"
  147. ),
  148. CONSTRAINT "CK_Birthdate" CHECK (BirthDate < getdate())
  149. )
  150. GO
  151. CREATE INDEX "LastName" ON "dbo"."Employees"("LastName")
  152. GO
  153. CREATE INDEX "PostalCode" ON "dbo"."Employees"("PostalCode")
  154. GO
  155.  
  156. CREATE TABLE "Categories" (
  157. "CategoryID" "int" IDENTITY (1, 1) NOT NULL ,
  158. "CategoryName" nvarchar (15) NOT NULL ,
  159. "Description" "ntext" NULL ,
  160. "Picture" "image" NULL ,
  161. CONSTRAINT "PK_Categories" PRIMARY KEY CLUSTERED
  162. (
  163. "CategoryID"
  164. )
  165. )
  166. GO
  167. CREATE INDEX "CategoryName" ON "dbo"."Categories"("CategoryName")
  168. GO
  169.  
  170. CREATE TABLE "Customers" (
  171. "CustomerID" nchar (5) NOT NULL ,
  172. "CompanyName" nvarchar (40) NOT NULL ,
  173. "ContactName" nvarchar (30) NULL ,
  174. "ContactTitle" nvarchar (30) NULL ,
  175. "Address" nvarchar (60) NULL ,
  176. "City" nvarchar (15) NULL ,
  177. "Region" nvarchar (15) NULL ,
  178. "PostalCode" nvarchar (10) NULL ,
  179. "Country" nvarchar (15) NULL ,
  180. "Phone" nvarchar (24) NULL ,
  181. "Fax" nvarchar (24) NULL ,
  182. CONSTRAINT "PK_Customers" PRIMARY KEY CLUSTERED
  183. (
  184. "CustomerID"
  185. )
  186. )
  187. GO
  188. CREATE INDEX "City" ON "dbo"."Customers"("City")
  189. GO
  190. CREATE INDEX "CompanyName" ON "dbo"."Customers"("CompanyName")
  191. GO
  192. CREATE INDEX "PostalCode" ON "dbo"."Customers"("PostalCode")
  193. GO
  194. CREATE INDEX "Region" ON "dbo"."Customers"("Region")
  195. GO
  196.  
  197. CREATE TABLE "Shippers" (
  198. "ShipperID" "int" IDENTITY (1, 1) NOT NULL ,
  199. "CompanyName" nvarchar (40) NOT NULL ,
  200. "Phone" nvarchar (24) NULL ,
  201. CONSTRAINT "PK_Shippers" PRIMARY KEY CLUSTERED
  202. (
  203. "ShipperID"
  204. )
  205. )
  206. GO
  207. CREATE TABLE "Suppliers" (
  208. "SupplierID" "int" IDENTITY (1, 1) NOT NULL ,
  209. "CompanyName" nvarchar (40) NOT NULL ,
  210. "ContactName" nvarchar (30) NULL ,
  211. "ContactTitle" nvarchar (30) NULL ,
  212. "Address" nvarchar (60) NULL ,
  213. "City" nvarchar (15) NULL ,
  214. "Region" nvarchar (15) NULL ,
  215. "PostalCode" nvarchar (10) NULL ,
  216. "Country" nvarchar (15) NULL ,
  217. "Phone" nvarchar (24) NULL ,
  218. "Fax" nvarchar (24) NULL ,
  219. "HomePage" "ntext" NULL ,
  220. CONSTRAINT "PK_Suppliers" PRIMARY KEY CLUSTERED
  221. (
  222. "SupplierID"
  223. )
  224. )
  225. GO
  226. CREATE INDEX "CompanyName" ON "dbo"."Suppliers"("CompanyName")
  227. GO
  228. CREATE INDEX "PostalCode" ON "dbo"."Suppliers"("PostalCode")
  229. GO
  230.  
  231. CREATE TABLE "Orders" (
  232. "OrderID" "int" IDENTITY (1, 1) NOT NULL ,
  233. "CustomerID" nchar (5) NULL ,
  234. "EmployeeID" "int" NULL ,
  235. "OrderDate" "datetime" NULL ,
  236. "RequiredDate" "datetime" NULL ,
  237. "ShippedDate" "datetime" NULL ,
  238. "ShipVia" "int" NULL ,
  239. "Freight" "money" NULL CONSTRAINT "DF_Orders_Freight" DEFAULT (0),
  240. "ShipName" nvarchar (40) NULL ,
  241. "ShipAddress" nvarchar (60) NULL ,
  242. "ShipCity" nvarchar (15) NULL ,
  243. "ShipRegion" nvarchar (15) NULL ,
  244. "ShipPostalCode" nvarchar (10) NULL ,
  245. "ShipCountry" nvarchar (15) NULL ,
  246. CONSTRAINT "PK_Orders" PRIMARY KEY CLUSTERED
  247. (
  248. "OrderID"
  249. ),
  250. CONSTRAINT "FK_Orders_Customers" FOREIGN KEY
  251. (
  252. "CustomerID"
  253. ) REFERENCES "dbo"."Customers" (
  254. "CustomerID"
  255. ),
  256. CONSTRAINT "FK_Orders_Employees" FOREIGN KEY
  257. (
  258. "EmployeeID"
  259. ) REFERENCES "dbo"."Employees" (
  260. "EmployeeID"
  261. ),
  262. CONSTRAINT "FK_Orders_Shippers" FOREIGN KEY
  263. (
  264. "ShipVia"
  265. ) REFERENCES "dbo"."Shippers" (
  266. "ShipperID"
  267. )
  268. )
  269. GO
  270. CREATE INDEX "CustomerID" ON "dbo"."Orders"("CustomerID")
  271. GO
  272. CREATE INDEX "CustomersOrders" ON "dbo"."Orders"("CustomerID")
  273. GO
  274. CREATE INDEX "EmployeeID" ON "dbo"."Orders"("EmployeeID")
  275. GO
  276. CREATE INDEX "EmployeesOrders" ON "dbo"."Orders"("EmployeeID")
  277. GO
  278. CREATE INDEX "OrderDate" ON "dbo"."Orders"("OrderDate")
  279. GO
  280. CREATE INDEX "ShippedDate" ON "dbo"."Orders"("ShippedDate")
  281. GO
  282. CREATE INDEX "ShippersOrders" ON "dbo"."Orders"("ShipVia")
  283. GO
  284. CREATE INDEX "ShipPostalCode" ON "dbo"."Orders"("ShipPostalCode")
  285. GO
  286.  
  287. CREATE TABLE "Products" (
  288. "ProductID" "int" IDENTITY (1, 1) NOT NULL ,
  289. "ProductName" nvarchar (40) NOT NULL ,
  290. "SupplierID" "int" NULL ,
  291. "CategoryID" "int" NULL ,
  292. "QuantityPerUnit" nvarchar (20) NULL ,
  293. "UnitPrice" "money" NULL CONSTRAINT "DF_Products_UnitPrice" DEFAULT (0),
  294. "UnitsInStock" "smallint" NULL CONSTRAINT "DF_Products_UnitsInStock" DEFAULT (0),
  295. "UnitsOnOrder" "smallint" NULL CONSTRAINT "DF_Products_UnitsOnOrder" DEFAULT (0),
  296. "ReorderLevel" "smallint" NULL CONSTRAINT "DF_Products_ReorderLevel" DEFAULT (0),
  297. "Discontinued" "bit" NOT NULL CONSTRAINT "DF_Products_Discontinued" DEFAULT (0),
  298. CONSTRAINT "PK_Products" PRIMARY KEY CLUSTERED
  299. (
  300. "ProductID"
  301. ),
  302. CONSTRAINT "FK_Products_Categories" FOREIGN KEY
  303. (
  304. "CategoryID"
  305. ) REFERENCES "dbo"."Categories" (
  306. "CategoryID"
  307. ),
  308. CONSTRAINT "FK_Products_Suppliers" FOREIGN KEY
  309. (
  310. "SupplierID"
  311. ) REFERENCES "dbo"."Suppliers" (
  312. "SupplierID"
  313. ),
  314. CONSTRAINT "CK_Products_UnitPrice" CHECK (UnitPrice >= 0),
  315. CONSTRAINT "CK_ReorderLevel" CHECK (ReorderLevel >= 0),
  316. CONSTRAINT "CK_UnitsInStock" CHECK (UnitsInStock >= 0),
  317. CONSTRAINT "CK_UnitsOnOrder" CHECK (UnitsOnOrder >= 0)
  318. )
  319. GO
  320. CREATE INDEX "CategoriesProducts" ON "dbo"."Products"("CategoryID")
  321. GO
  322. CREATE INDEX "CategoryID" ON "dbo"."Products"("CategoryID")
  323. GO
  324. CREATE INDEX "ProductName" ON "dbo"."Products"("ProductName")
  325. GO
  326. CREATE INDEX "SupplierID" ON "dbo"."Products"("SupplierID")
  327. GO
  328. CREATE INDEX "SuppliersProducts" ON "dbo"."Products"("SupplierID")
  329. GO
  330.  
  331. CREATE TABLE "Order Details" (
  332. "OrderID" "int" NOT NULL ,
  333. "ProductID" "int" NOT NULL ,
  334. "UnitPrice" "money" NOT NULL CONSTRAINT "DF_Order_Details_UnitPrice" DEFAULT (0),
  335. "Quantity" "smallint" NOT NULL CONSTRAINT "DF_Order_Details_Quantity" DEFAULT (1),
  336. "Discount" "real" NOT NULL CONSTRAINT "DF_Order_Details_Discount" DEFAULT (0),
  337. CONSTRAINT "PK_Order_Details" PRIMARY KEY CLUSTERED
  338. (
  339. "OrderID",
  340. "ProductID"
  341. ),
  342. CONSTRAINT "FK_Order_Details_Orders" FOREIGN KEY
  343. (
  344. "OrderID"
  345. ) REFERENCES "dbo"."Orders" (
  346. "OrderID"
  347. ),
  348. CONSTRAINT "FK_Order_Details_Products" FOREIGN KEY
  349. (
  350. "ProductID"
  351. ) REFERENCES "dbo"."Products" (
  352. "ProductID"
  353. ),
  354. CONSTRAINT "CK_Discount" CHECK (Discount >= 0 AND (Discount <= 1)),
  355. CONSTRAINT "CK_Quantity" CHECK (Quantity > 0),
  356. CONSTRAINT "CK_UnitPrice" CHECK (UnitPrice >= 0)
  357. )
  358. GO
  359. CREATE INDEX "OrderID" ON "dbo"."Order Details"("OrderID")
  360. GO
  361. CREATE INDEX "OrdersOrder_Details" ON "dbo"."Order Details"("OrderID")
  362. GO
  363. CREATE INDEX "ProductID" ON "dbo"."Order Details"("ProductID")
  364. GO
  365. CREATE INDEX "ProductsOrder_Details" ON "dbo"."Order Details"("ProductID")
  366. GO
  367.  
  368. CREATE VIEW "Customer and Suppliers by City" AS
  369. SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
  370. FROM Customers
  371. UNION SELECT City, CompanyName, ContactName, 'Suppliers'
  372. FROM Suppliers
  373. --ORDER BY City, CompanyName
  374. GO
  375.  
  376. CREATE VIEW "Alphabetical list of products" AS
  377. SELECT Products.*, Categories.CategoryName
  378. FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
  379. WHERE (((Products.Discontinued)=0))
  380. GO
  381.  
  382. CREATE VIEW "Current Product List" AS
  383. SELECT Product_List.ProductID, Product_List.ProductName
  384. FROM Products AS Product_List
  385. WHERE (((Product_List.Discontinued)=0))
  386. --ORDER BY Product_List.ProductName
  387. GO
  388.  
  389. CREATE VIEW "Orders Qry" AS
  390. SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
  391. Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
  392. Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
  393. Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
  394. FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  395. GO
  396.  
  397. CREATE VIEW "Products Above Average Price" AS
  398. SELECT Products.ProductName, Products.UnitPrice
  399. FROM Products
  400. WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
  401. --ORDER BY Products.UnitPrice DESC
  402. GO
  403.  
  404. CREATE VIEW "Products by Category" AS
  405. SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
  406. FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
  407. WHERE Products.Discontinued <> 1
  408. --ORDER BY Categories.CategoryName, Products.ProductName
  409. GO
  410.  
  411. CREATE VIEW "Quarterly Orders" AS
  412. SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
  413. FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  414. WHERE Orders.OrderDate BETWEEN '19970101' AND '19971231'
  415. GO
  416.  
  417. CREATE VIEW Invoices AS
  418. SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
  419. Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
  420. Customers.Region, Customers.PostalCode, Customers.Country,
  421. (FirstName + ' ' + LastName) AS Salesperson,
  422. Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName AS ShipperName,
  423. "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
  424. "Order Details".Discount,
  425. (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
  426. FROM Shippers INNER JOIN
  427. (Products INNER JOIN
  428. (
  429. (Employees INNER JOIN
  430. (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
  431. ON Employees.EmployeeID = Orders.EmployeeID)
  432. INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
  433. ON Products.ProductID = "Order Details".ProductID)
  434. ON Shippers.ShipperID = Orders.ShipVia
  435. GO
  436.  
  437. CREATE VIEW "Order Details Extended" AS
  438. SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName,
  439. "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount,
  440. (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
  441. FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID
  442. --ORDER BY "Order Details".OrderID
  443. GO
  444.  
  445. CREATE VIEW "Order Subtotals" AS
  446. SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
  447. FROM "Order Details"
  448. GROUP BY "Order Details".OrderID
  449. GO
  450.  
  451. CREATE VIEW "Product Sales for 1997" AS
  452. SELECT Categories.CategoryName, Products.ProductName,
  453. Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
  454. FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
  455. INNER JOIN (Orders
  456. INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
  457. ON Products.ProductID = "Order Details".ProductID
  458. WHERE (((Orders.ShippedDate) BETWEEN '19970101' AND '19971231'))
  459. GROUP BY Categories.CategoryName, Products.ProductName
  460. GO
  461.  
  462. CREATE VIEW "Category Sales for 1997" AS
  463. SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales
  464. FROM "Product Sales for 1997"
  465. GROUP BY "Product Sales for 1997".CategoryName
  466. GO
  467.  
  468. CREATE VIEW "Sales by Category" AS
  469. SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName,
  470. Sum("Order Details Extended".ExtendedPrice) AS ProductSales
  471. FROM Categories INNER JOIN
  472. (Products INNER JOIN
  473. (Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID)
  474. ON Products.ProductID = "Order Details Extended".ProductID)
  475. ON Categories.CategoryID = Products.CategoryID
  476. WHERE Orders.OrderDate BETWEEN '19970101' AND '19971231'
  477. GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
  478. --ORDER BY Products.ProductName
  479. GO
  480.  
  481. CREATE VIEW "Sales Totals by Amount" AS
  482. SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
  483. FROM Customers INNER JOIN
  484. (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
  485. ON Customers.CustomerID = Orders.CustomerID
  486. WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' AND '19971231')
  487. GO
  488.  
  489. CREATE VIEW "Summary of Sales by Quarter" AS
  490. SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
  491. FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
  492. WHERE Orders.ShippedDate IS NOT NULL
  493. --ORDER BY Orders.ShippedDate
  494. GO
  495.  
  496. CREATE VIEW "Summary of Sales by Year" AS
  497. SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
  498. FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
  499. WHERE Orders.ShippedDate IS NOT NULL
  500. --ORDER BY Orders.ShippedDate
  501. GO
  502.  
  503. CREATE procedure "Ten Most Expensive Products" AS
  504. SET ROWCOUNT 10
  505. SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
  506. FROM Products
  507. ORDER BY Products.UnitPrice DESC
  508. GO
  509.  
  510. CREATE procedure "Employee Sales by Country"
  511. @Beginning_Date DateTime, @Ending_Date DateTime AS
  512. SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
  513. FROM Employees INNER JOIN
  514. (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
  515. ON Employees.EmployeeID = Orders.EmployeeID
  516. WHERE Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date
  517. GO
  518.  
  519. CREATE procedure "Sales by Year"
  520. @Beginning_Date DateTime, @Ending_Date DateTime AS
  521. SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
  522. FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
  523. WHERE Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date
  524. GO
  525.  
  526. SET quoted_identifier ON
  527. go
  528. SET identity_insert "Categories" ON
  529. go
  530. ALTER TABLE "Categories" NOCHECK CONSTRAINT ALL
  531. go
  532. INSERT "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(1,'Beverages','Soft drinks, coffees, teas, beers, and ales',0x151C2F00020000000D000E0014002100FFFFFFFF4269746D617020496D616765005061696E742E5069637475726500010500000200000007000000504272757368000000000000000000A0290000424D98290000000000005600000028000000AC00000078000000010004000000000000000000880B0000880B00000800000008000000FFFFFF0000FFFF00FF00FF000000FF00FFFF000000FF0000FF00000000000000000010000000001000000001001000000000000001010100010000100100000001010001000000100100000100000010101000100000100010000000000000000000001000000001000000000000001000000000000000000000001001001000000000000000000001001001000012507070100000001000000001000100100000010000001000000000100100100000001001010101010000000000000000000001001000100000101010101000000000000000000000000000000000000101020525363777777777777753530100101000100000000001010001001000100100100000000000100000000000000000100010001001010001000000010010000000000000100000000000000000000000000000001014343577777777777777777777777777770100120001010100102000000000000000000000000100100010010000000000100010000000000000000010010000001001001010100000000000000000000000000000001021617777777775757535353525777777777777770150120000100010101000000000000000001000000000000001001001000000000010010000010010010000101001001000000100000001000000000000000000000001417777737617376377775767771707752777777776340161210300000000010000000000010000000000000000000000000000000000000000100000000000100000000000010000100000000000000000000000000100503677577571705014000000101070717775777777775334101400010101010001010010101000000000000000000000000000001000000000000000000000000000000001010001000001000000000000000000000000010357353000000000000000000000000000171777777777470612101000000001000001000000010000000000000000000000000000000100010703010101210000000000000000000000000000000000000000000000101034350000000010653435777570477474700000107757777171000000101001000101000101010000100000000000000000001041477110131035750020040477301000100000000000000000000000000000000000000000010004014176577707000067777777776560404007371734361701400001241000001000000001000020400000200000101747767777000101073777577777775372100000000100000000000000100100000000010000010000037676476700004000577470416717477421405000434140343000565120510100301014002107171370701437777777775777777112103177777777777705757771010000000100000000000000010000100000101000470440440671007477776777777614060600742524001777777747646776752060250003021716737777777777777774747727574777001016777777777767173317703000101010000000010000000001000000000000420460200077427477767777777775677777746474656606767777665746776757151253507166737777733746777577777777572776771100517777777767776015713141030020001000000001001000000000010100067440044047761477767776706767777674765673470657505767375664746757777271252167717733771737776777677567476577577121103775777777776717027753121010101000010000000000100001010000010767060004776747776776777756776777777777042477676067777467474747676777565256531311771177376477777576757777747710110701777777767777401717340000000000100001000000000001000000101004776540666050777677657777677470774777776747664607777376747476777777677717173735377717737747777777777774774770011271077777767777763171735210121010100000000000000000000010000000300406767757676775077006767477774774777774747770476777656706746777657777777777777777777737667777476574774777771001031777777776767741371771000000000010000000000000000000000101005707442567656176006767004770476707700767770000477747734656446745677676777777777777777777375667777777777777777773100010777777777777771753521010101010000000000100010000000010007777712152503004670077774767427047247776577564700076737747670474277777777777777777367777777765777777777777434777750757775377767676770172773100000000001000000000000001000101007170701614204046007746040676167470774167743656777740077776067407465677677777777777757717777737476775716161243410303077777777577775210000011350001001001000000101000100000100002100171525675070074340670005004076700706570757777767770077744746466567777677777777777777777773776777610000000137775350317777773777737750701000101021001000100000000000010100010010300067777761650604065047604760746404776406705656776770077764750474747677777777777777777773733747777773011735777777777777777757777777777767412041001001000001000001000000010001000577744140000607406706767676776777776477756767777447700774076646764777567777777777777737373737764677747753527777776777777777776365735353513010300120301010000000000000000001000107000210006147767674646040404040066667767677775476777046644644044456776767777777777733737373776777776774244777377717712777165357577534242040010010010000010001000000100010000100300050000146664000000101030734352101100065677767077770047604774377676777767777777777373737333756477657075377100770770177776525271673001012101210301001030000000100100000001000005000060046160004000125343510110101000000000007740000047744733737377757677777777777373737377737656757777777373101676770777717775671773001010300000021021010000000000000100000000100077400000414021414000000000000000000000000000300000777777773737377677677777777777373737333735677677777377710177777717774705271767340300000010101000100000100000100100000001014005660000000737560600000000000000000000000000004730777773733373737777747677777777737337353761666777777737737017771677077353777574735310012101000000010010100000100000000010004300065400000000400141254140404000000000000000000037737776773777373733777677777777777677646746565756747777773773017017710765654352735770017010303031010010000000100001001010030704000660000000000000040000000000000000000000000007777514673373373737777777476777777777474644764666776777777772711031076117307374357477373010341050043030012100100010100100012500000047000000000046742000000000000000000000000077776677777377377373733737767777777777767645676507574777657610057121101731611574777637735105270125213010050210100001070210301650000000640000000006776406776464000040641434177777767667614737337337373777777767677777776564767474664667477761775271112116101002331211101052721016120140161034106010173075617770000570047400047400446000000467770504777767173573756767776767737737773737377776777777777776564746765477576777176700774656474731010011000001250165214716170121012011070777173777400063770040000760467600000000740760600777067777777676767676767337333373737377747677777777776767747424676747677157701677677676131331213131301371317310312161525053073077777777700047577700000006006760000640400006474046740777777777676767676737737777373777777767777777777674746767467477777743670175305325352527135335353170143414371617130131211777177777777001737770006760476677047064466400047640077747777777767676767673773373333737373776746777777765467674704747674765375610731773573752534737417017035303130101010030001427777777737770047777770047460704644064400004640067004767077777777767676767673377377773737777776777777777766565665670767767775077007563153347370731013213617034343434307031417121177773777777740257737700027447000064000000000640064006760777777776767767767773373333373737777476777777777746765674464747767763477027172753717175777757757357171717171717433616163777777735737400737773400460660046000000000004000600676747777777776766766767377377777373737777747677777776756567467746765777117100537153353773777777777777777777777777737757737573777773773772047777350000474044600440000000000040047774007777700667677677633333333333737777766767777777777667476564657476760600007353375373177777777777777777777777777777737777377733753777740007177770000664024640640000000000004646700477777007767667666777777777773737777777777777776777446467565676777535373525317137177177777777777777777777777777777775377773753771737700076737350000000474664665646644400400464000077700067677677773333333333373737776676777777777767777766767765677771713175217037173777777777777777777777777775375377173753777377773700057777004007477667764766767667467600000004770000767667666677777777777337777775677777771777772604000404067761171613131535353717777777777777777777737753777777777753773717735374700000500670446677777776777776777776561004661000006767767777333131101100777777666567777567704040505140777716536353147173135371777777777777777777577577777777777777777353753777371700000001776040404040404606076767776170000470000071101100100000000000110157177776777776470124100002530004777111301313017535371777777777771771737377773777377753773717353252165376164464265700400000000000004040040076774000440000777500750000000000000000017347766777746564100000000400300652513530753303170737777775777777777777737777777773777753757035353134317137313533000046440000004400000000053770000000000077343100000000000000000004135777775676176000700000004044213052153115353371357777377737737775777777573757777777353213503161617163521657257000006700060042400000005273710000000000007577000000000000000000531117777665447405244000000040031501313030721353537737775777577753737717737777777777777035343343131303103171317337130100000567000200000031756000000000000000077771012100101101131117133375466747465707047000071502161011531534353517753773737353777737777777777737537713503353170717173561343105307030525370047014161717433700000000000000000000101011770000006402737373767456467777777773065773510137343531317073737773775777773777577375735737577777343375377373673071316352731717173137000007737352713574000000000000000000000000464000000046733737373446647777777777740007373737110310343537171773565373537577177375737777777777773353737717175357727753717163737357770000071735371677700000000000000000000000000460004004676173737374745777777777777004631713112031213131317337177737777777377777777777777777777775377737777377371717353773571747737377617771677773570000000000000000000000000400400000000406337333464673777777777774007733373311001013135317177737775377377777777777777777777777737777573777377777736771773773716717535343373525773700000000000000000000000000000000000000037337374433373777777777700007740010313133173137337357753777777777777777777777777777777737737775375737373717367171653735727367374753737174000000000000000000004600000000000000000373733643773373777777777404073000000000012137331737377777777777777777777777777777777777577773737773757575735317273353531757535737377576300000000000000000000424400000004000040007373375733337333377777770000700000000000000000070477777777777777777777777777777777777737773757753757373737777775357273673373773535737357000000000000000000004406000000000404004037337333773737737377777700400000000000004006404043777777773757777777777777777777777777773773737773777777717371737357171752573473721777340000000000000000000006446400000000004004337337333373337337337777100004705340100016503777747717717757777777777777777777777777773757757773577173577775777577377773777373757777177700000000000660000640047674000000004000003737337373377337373737774040077760004000000044004737777777777777777777777777777777777773773773577377777377377377377537177535757373537710000000000004040004640604600000000000400073733737337333737373777700000047477420000000000435777777777777777777777777777777777777757777777777777777777777777777777737737377577777000000000004600000460064600000004000000000373373337337373737373777600000000000550043617777777777777777777777777777777777777777773777777773777777777777777777777777777777737737777000000000000000000000406400000004040000003373373737337373737373770040000000002777357777777777777777777777777777777777777775777777777773777573717775777777577377777777777777757340400000000000000040004064000000000000000073373373337333373737377750000000000057777777777777777777777777777777177775737577737777777735777773777773773775377377735735735375737737000000000000000044600406060000000000000046337337337373777373733777007460000000377777777777777777777777777777777737737777377777377777737371775353753753777777777777777777737717750000000000000000000000444404400400000000063733737337333337373377774067400000000777777777777777777777777375773777757777177177377735777777777377777777777777777777777777777777777704000000000000000000006000666066000000004433733337337377333377777700676004004407777777777777777777777777777757357375377777775777737777777777777777777777777777777777777777777772010000000000000000000040004404440000000000373737337337337377777777704600674660077777777777777777777777777737777777777773773773777777777777777377377777737777753777777777777777750040000000000000000000000460460000000000463733733733733737777777770047464067000777777777777777777777777777777777777777777777777777771737177777757377377753777777777737757773737000000000000000000000644640000460000000000073373733733733777777773750660760400017777777777777777777777777777777777777777777777777777777777373773777357173775377735737777377757777240000000000000000000606400000000000000000373373773733777777777737604746400406057777777777777777777777777777777777777777775775771733735377757177175737753737537777757777777777750100000000000000000046540000000000000000007337333333777777777771771066067674767677777777777777777777777777777777377777777377737777775737573737736373717375773777373737377777371200400000000000000000046000000000000000000073737373777777777777737700656476464617777777777777777777777777775757777777575757735773735371737357737575357635733577377577777773777775000040000000000000440646000040000000000000733733377777777777777137106606476400077777777777777775777757357777777757577377375777775737777577735737377371735773757073737175777777370000000000000000046764656546400000000000007733737777537777777777774474407467005077777777777775777757377735737717737377777737777371773737373773577535373437073737757577737353777700500000000000004676474266640000000000000047333777074747777777777776567642766027777757537775777371735777777577777777577777775377777777577577777737777577737757757373737777775777000000400000000067407604040000000000000000077777103716173777777737676665646470577757377775777375777777177377777777777357357777773737777777371735737773735753737377777773577377370004000000000000666424604040000000000000000777777007677477777777767676767474003577777777773777777777777777773773573777377773777777577773777777777771775773777757353753577357777770010000000000040406404000244000000000000000777370141477567777777762476767660067777777773777777737773777753777777777777777777777777773777777777375367377375357367767767737673477140240000000000000446400004660000040000000007737520077772757777770040047667767177777757777777777777577737777757777717753717717777777757753535357777775775777777535753735757177357005004000000000000000040400476440464000000007773401616575777777006440004764256777377375775375735737777777737737737773777777777773777777777777771771777777777777773775777377577773000000040000000000400000000000067400000000077771425777367777700400060006765377777377777377737777777735735777777777777777777777757777777777777777777777777777777777777377377353770070040000000000000400000404000040000000000077770525765777777004004040440065773775717377777777377777777777777777777777777777777777777777777777777773737371775377773775657527777500004000000000000000000442424400064000000000777724077576777700400600007000373757373775775375375737777777777777777777777777777777777777737777377373577575777777573575373733771737300700004000000000000004646440000672440000000777507567657775000444040644047777377777773777777777757777777777777777777777777777777757377771777375773737373737373773377753575377577400004000000000000000000400000040440640000004777407757777700404246044604375777757737777777777777777777773777777777777777777777777377775773575737175717175717571757253372734372773007000040000000000000000000004600464000000007772525677777004704064240124373777377577777777777777773773777777573577777777777757377737373777373777737367363727373735356171737177175000400000400000000000000004600000400000000047710477777700676006564640577777777777777777777737773777777577177777777777777777377735775775377757173717535357174352537737373717717730070040000000000000000000040046000000000000077777711357047600446500072777777777737777777377777777573573777777777777777777777737777377377177377757773777377737777343574356773737710060040400400000000000000000400000000000000771571715356770446002470757775773777777377757735735773777777777777777777777777735777377777777777777737573577177535357773777371747527710160000000040000000000000006000000000000007771353777767600056440042735373775377375773777777777777777777777777777777777777777777777777777757377773777377737777735777537577373717700104004000000000000000000440000000000000077171357777674006064214357577775737757777777777777777777777777777777777777777777377777777777777777777777777777777737777373777737577777300424000400000000000000000000000000000000777174777756765404051425373735737777777777777777777777777777777777777777777777777777777777777377777577777777777777777375777737777353777100100400040400000000000000000000000000007717137577764767404061777777777777737737777777777777777377777777737537777777777777777577777777773773777737775377177577737353753737770737100400400000000000000000000000000000000077717177777467760030065377577777777777777777777777377777777777777777777777777777777373735371777775777177753777777737717757775375753573536100050040404040000000000000000000000000771717177720767000043737737737737757737773773777777777777777777777777777777777777577777777777737773777777777777777777773773737737377357753000004200000004040000000000000000000047773537777504004104375777573757777371777777777773777777777777777777777777777777373777777777777777777777777777777777777777757777777377373777200504040404000000400000000000000000077153577770000016075375373777737177777717717777777777777777777777777777777777777777777777777777777777777777777777777777777375373577177573535300100040104004000040400040040004000177353577770070007277377777537777753757777777777777777777777777777777777777777777777777777777757777777773777577777775377537727576377717252734120050040400404040000040000000400007735353777005006535357777737771773777377777777777777777777777777777777777777777777775737777377777717377777777773777777777753753735752771775173500007000040000004004000400400000477717177775004353777737377773777777777777777777777777777777777777777777777777777773737757377173717777773577737777773777773773777773771773136343700000561040405004000400400040400775317777700367771737577537757777777777777777777777777377777777777777777777777775757717777777777737177577377777775777773777353717773771776535353716000047000404004000500050010001735717777761717777573777777777777777777777777777757375777777777777777777777773737737773753777177577737777537537737777757777777771757372537737271717100005252004004040604004040077531717777177777777777777737777777775777777777777777777777777777777777777777757717753757775377737737773777777777777777777177173777737753770775363774320000416524100000400400004773717777777777737777777777777777377377777777777777777777777777777777777777777737773777773777777777577757377377777777377377777753737753771775375757377577600000106141410143405007757537777777777777377737777773777777777777777777777777777777777777777777777777753777737777777777777737777777777777777777777377777573777777377373775373735373000000000400010000077377717777737777757757571777777777777777777777777777777777777777777777777737773777777777777777777777777777777777777777777777777777777737775777777377775777777777161612161637777777101777777771771773777777777777777777777777777777777777777777177577377577757777777777777777777777777777777777737777777777777737737775773737717717771737737537777777777777777775717177777771777777777377773777777777777777777777777777777777777777777777777777777777777777777377377777377777777777377577177537777777373757737737735377735737737377737775773777377717177777777737777777777777777777377777777777777777777777777777777777777777777777357537537777577773775753573577577537377737753757357757357571753777171735735775357537737571777771717577777777777375777375735377377775377777777777777777777777777777777777777777777737777771773753757377377777737777777777773777377737737737377375377777737573537737753773777777777177777777775775737757737777777757377777777777777777777777777777777777777777357777777777777777777777777777777773777777777777777777777777777777777537717773777777777777577777717711737777173737377777377777777177377777777777777777777777777777777777777777777737377777777777777777777777773777777777737775777777777757777775373737777773777377377537737777777710101417777757757377777771735377777777777777777777777777777777777777777777777777777777377777777377377777777777775775775775737777717717371735377575735373757175365737777773737777777773617377373775737773777777777777777777777777777777777777777777777777777777377757177573737777577773575373573737737777773773737777777777777737373777175337637173573537777577717777753775777775377777777777777777777777777777777777777777777777777777777777777773737773777573573753777737777777777773773777577577737353717353577175217437753577377377771737373773777375377375377777777777777777777777777777777777777777777777777777777777777757153471773737373773771737771737377777777777773777737577777777777377737733717373717177737777777577777375377777777777777777777777777777777777777777777777777777777777777777777773737773771757577573577377717777575717377777777777377773717353717357175717577717753777175773773537777777777777777777777777777777777777777777777777777777777777777777777777777777753473535377373717353717171735373737777777777777777737777777777777737737737353735371737737777377777777777777777777777777777777777777777777777777777777777777777777777777777777777773777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777773535000000000000000000000105000000000000C7AD05FE)
  533. INSERT "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings',0x151C2F00020000000D000E0014002100FFFFFFFF4269746D617020496D616765005061696E742E5069637475726500010500000200000007000000504272757368000000000000000000A0290000424D98290000000000005600000028000000AC00000078000000010004000000000000000000880B0000880B00000800000008000000FFFFFF0000FFFF00FF00FF000000FF00FFFF000000FF0000FF00000000000000777777777737125353313731773543110105302502105313321714317343717135371373147317317171717121135301610131217777777770146765074747776567616774776565774040371737031611737710110100007777777777717435357353531713343030301103112161705353317353343717135371370317717737371734125031131352171777777714066544724767776774747657700577764774340735757100371507530210777777777777777731737317353731704311151303112110431731305317314171731717171354731713535373107131703011317177777770664576076567476404776147676777674174074740573312411002173611137777777777777353167171735337173531163125351615307173171737171707373173733537023177373737351611010113521737777775245006047474747407777777767657775747477416560075141200115351103077777777777777377143161735717353463107113131303343353171317373107177317173171477135353717370737312503173577777344760547061604760777777777777764677776007470774033001010035212100777777777777777173563535335371731053130707071351165343171701773417357717177130177173717717134101713353173777747640076047447000777777777777777775667570467760774040301010101053107777777777777773712531335337171735301531313134334135353361371350735331737137707137353731737433731717377777776040000407647604777777777777777777460547743565054776011001031213010077777777777777353561737534717337161352171712717103737335137137061373573171711073531737171710351171735373777704740460464746777777777777777777777040667746776007751300530101301300777777777777777373071713713717135241311030711317605117533517171075353357373734173173537373735373773777777770460464740406757777777777777777771777640577740457777000131035310701007777173777777775353431613717357131630731713735311637317173737171235353353535725377777777777777777773737777567404706425046777777777777777757777775246577777767711350131030311300177777357777777737350771731171337510531071351613735534317131717305737357377373077777777777777777777777777776540060405646777777777777374777377777774767777747771076035031110121000173735777777773535307131717373513243357317073171163353712571735073171733535735777777777777777777777777777704600564064077577777777777737777777777424577756771147741121161037100017357733577777773731603521725153251071335213317077071335373371732177373573737777777777777777737737777777776460464604046473777777777777777777737777567776657167647421121121103001035775737377777571711613531337353371435135351713131471731171735716171773753777777777737777777773777777777774740405674747777777777777777777377737176567757370470067070121121100010733531717577777371734173535353353107127713631735370371737173537107377373777777777773777377377171377777777444006640464677777777777777777777777777756777774747047741137112116100305737161373777737173107313531735352471713171173537017173717353731637535777777777777777737737737337177777770760650406047777777777777777077777373437777770567674776012101611210010131717135357777713253425343525353131031717373537171617171371717750537737777777777773737737733713537777777744404656440467777777777777777777377771577774764044774470717131071301210161335253077777757131035313137377534721717173537371637171733737343537777777777777777737737753713137377777764604646560457777777777777773777373001777574777764477611301121010001017135131314377773131716317353135313001717353353717165317371713573173777777777777777737573533373353717777777465404006400767777777777777377735000137776067664707640341216131300300035253521707577135271653531161773716173371375335373531717173757316773777777777777773773737377171313773777770460000440066757777777777777773700010577756764100674031311310100010103131313521073777731131052773171371310715377135737171773353353337717777777777777777773773737333131353353777776764007640456006777777777737371000013576644566565671341210131300010103521703170073735371730311173171371352735377335373725357353757177177777777777777777773737377173317173773777344564046466404444056477777537301000373405606746764011331352171001201013523152107177735303504373171357017005335217135307107317371337377777777777777777773377373137317133353353777706400004400676000640677471001000171464767444564031301052117100301001703117211617173531713035316127331710737171717731734071737171777737777777777777777777737377735333531737777717746654047046440044700465700016113000564440676653130171311303001010303152311340217173613530435313513531210717313613535312131713771777577777777777777777373735333337113713131377777344660240404740064000007003012446064000065641301430121217100303010117214341305030713521770035312153431340315251703537140713531737773573777777777777737373737171337311317171771777714540440064600464074764547407644764474661061711131171213001100121311331330433171353713107121713013170071631331353113013073173537752777777777777777773737337373131371731317707377776646600000000400464006460040000476461100121212163011710430103104341170510350307131714035353017317034353153417125240735317537717377373777777777737173537713137113133135371377737771404047400000000440040000046564612110016111211111303013012110331333130343135134352334031251210717107353213717131300131733737777577777777777777737173713737133713717131774353737777776646444600006000046442564670513430031611030301700012112131170552530043032531351307171335313137007153513035211071631535737716173777777777777737373733733531313133713707375737737757474604640746406546442411030301104111210110303104012112533130313134315113171371407135031707110712313253121520031173733537777777777777777737737373717373313533531177165373577777737777574746445652413513125110130012121121210110013152113152531725005303616343160335303521310243535161134112143537371777777173777777777777731737737313171731353137350737173717352573717737353737171343070110212100210130101013020210311612313171134121711311353134135311353531061303116113010013535373537370777777777777773777371737373331371335117340537153717352573737517340707317351130211011201712103103011001312531711725371124301253717135035215271212170171703130313030703535373777757377777777777773735733717171311371333173163537353707142570532717161352513307111211211401113502101211041713030371135363105331301212530431731135353107031100110411000713737177377377777777777773773733771733317131335353170143417217317073173535317071353250303071021120120301311303124330171711371133150435053537171703713107031316053160317031301071371717717734777777777777777377377337371331351733137124331335351700717053530700714351131501103112107111131030105001153012125363757312131303113121051707131716110210110101100300317137373737713777777777777777777777777377373331537174101170535321705713725353507331216121312110710003070125103130061213110133151317052521716161370213134310313514310303121310140307171717735653777777777777777777775341307071331313130060130305313003411310303014105310101012101214311130121103130131412130757377735213171213135105350311251212021030110101030035317337735731777777777777777777757171310101373535317100112535321610613161035110031310130103010131003030013112105007031301011317731730717031711612012135035335310503110212130104713535713737167377777777777777777737310010135153313530003011010511001212117121243001030012101103010051013100301130011030130077737771750731731631350717133031035302110211010121303533733753773177777777777777777777510100000017335331711043030312121041153010101001121031010102103010303100311012100121010010731737773731731711531300316153171307116111035031101433537533771774377777777777777753012110111000015617137200103110311121203103031210021110010030101010000103110121013000130131017771777771471352373053525313317037130612102103121312573713753777377773777777777735035355371731510001717701100314311430100101311011021102031211011010130010100312112100030012003037777737377335375317330131351631713150110311301535017353777377377177777777777773513513130111053351101771130070131303131053170161307050311101030102121000121211010010101013101101777777777716537131731570716331531352352311210713013343773777375377577777777777713171310135371315373103520010113161311032072113131110311212121012110110031101121213030003100100307737777773717137171731310315331707353014301311253353573573717377737377377777773177125353131735335357103131202521135271510113412163105211111113110121210003100111011100101301010177777377775637717331737071735213317317431734121314317373777777777757777777777317313113107173777531737150101013173031133043713353110631777377777373111001310312121030012121000210777777773733171316171611073135351731703101013171733525777777652104277777377731713535341717353537357571310100010351353250310351317377577525010505357730301031010112100210100101037377777777757335735331734353717371371707131343121753177050001040014077777737713713011331357777775337175000100010370351314771377775713400100000000000417531013130313051130100010077777777777345331735353125353310375313430521353531377770000400140014057777777351717351071353771775357331001111111353353211377777434001000000000000000003531051014110030100100210777777737735335731735217103341737137353413110313535377104700106756207747773537371710325175375777317735110110001107317351677771611013400100000000000000000703121313003012100010017777777777773433173131710735333113710305303431073737770777406456065570014777753103535113137773711771101110010100171771737777171607000010001000000000000000130110300611101010011077737777773717717353731730537516371737125313173171777575646747676566756704773757110717757777773773130000111001110771377777516101105010000000000000000000000532131101721000012000777777773777717317353431343133317171717035307173773777775747400456556756701773737711010343571513571110010010001001777777777357343034341010000050000000000000150121001121100010107777777777771635371353735343535353371335431713535377777770006047606677674073777777771711113173753000001000010100177777777535305141000000401070000000000000000313500310100100010077777777777373537037331530173537317137523173773737777777747650460447465677777777777777777777777300001100000010110777777573530530374175353107057310000000000001710071030010101010777777777377753713713573716137131733533507171353777777777774640540761465477777777777777777777575000000000000000007777777753577575031035257053007700000000000003100121121000300007777375777737343711713131716137171753533437173773777777576700766704465625777777777777777777777737000000000000000077777777773537077577561763571001000000000000053010121001000110077777737777773531733536173253717373373711717353777777777755046564476767477777777777777777777777111300000110101000777777753577753712707100142070070000000010010300713110100010000375771777373534371353317315171731717171707353777377777774766474677644747777757777777777777777717370100013000000007777777777777350574100005251007100000000000000611210030001000017737371777773716135271711732533537373737307377177777777777752424464765677777777777777777777777777141010011100000077777777777770712170710301701617000000000130010531031010101001071771735717777136131173731716173531717135353737377777777747654476744644777777777777777777777777713001011010100100777777777777570570110414161600071000000004170003071011000100000371737137773733417373171371217137373737737771777777777777777467444604677777777777777777777777771700000001010010017777777577757353052431201001015340000000012100171121200100101011735717773375353735317137131613717171731717377377777777777600742076565677777777777777777777777737100010110100000077777777777305705251525034000702100000000005037103101100010000037133710775737352135237317350713737371773777377777777777765046546046467777777675777777777777773710113110110110001777777777775730701006125010100050000000010030013103000010001010717717373737735357135117717334353537373773577377777777777567746644650477577775777777777777777750131100000100100077777777771775711753010530400001010000000010010703113100010000003710735357353737037333713317137373757377173737377777773776564745204646757774777777777777777773313010010101110007777777777577771650341252051012104200000000007001352100101000101017373171737373531617171371713435317337537357777777377377756470064404657777777776567777777777751111101000110011377777777777577161035214105200040101000000000101031013010001010000735377335773773535373173173353737737737737373737377375377777447476704677777775777777777777771371301001001011017777777777537577134104034001001000000000000000700033101000100001003171357331771737160152173171351733717373717177775371737776767460446044777777467774777777777771311111001101017777777777777577756134311012161401000100000000010101012121010000000077337335737377373137335353737363573735373737735337173177756104700046567777777757477777777777117131000100113777777777777776717351410401450101000000000000000016017110100010000010357371773177735371613533371353537373737717353173713717775654065400004677777774707757777777735311010112113777777777777777535757161252161210000010001000000010010310210001001010007335377377353773530713535337337173537373735377311713737765670000004004777765656577777777777531311010111777777777777777777536352141010014340100000000000000010000311010100000001071775335377373737170733735717537373717373735317373717775725650000474046777577777677777777773711311313777777777777777777777575757161050000100000000000000000001001210000100000000377335737737777737313571733733717373735353737731353733770567000007400077777677777577777777735311177777777777777777777777573537010116310100000000000000000000000131010010000010105335173353777371353053331353171735373737373713177737777770016140740004777757777747777777777531377777777777777777777777757347753777717400000000000000000000000010103001000001000035736317357357377317271737373735337171737353777333737377716140141003473777776757465777777773537777777777777777777777777775771757761601000000000000000000000000003100000001001010173317717377377373711373535353734737371717371377777777777502112007047377777756777777777777777777777777777777777777777777771777771501000100000000000000000000000003103010001021016317431635377377173727173373371313531373737377737737777733714005001737377777777777777777777777777777777777777777777777775777776142140100000000000000000000000000110001000101012017713173537377737353117317137137343777373737737373773737373737137773777777777777777777777777777777777777777777777777777777775011210010000000000000000000000000001200100010301211431617353717737353353613733537335373337777733737373373737373737737373777777474240567777777777777737173302137777777777777753435341410001010000000000000000000000010010003010101003173617313737573753353435373135337773333777733737373373373373733737773777756101000507777777777777776140500001377777777777753525210250000000000000000000000000000210010010210303117351314771737373371321733173737733337377333373333373373373733773773777714000404070747777777777400000000000400257777777757170714141001000000000000000000000000001003001011010100617335733135377717137152357333773337333333373337373737377373777377377777435777707477175777777700000000000000005377577777716171430300100100000000000000000000000000010021201210311314121353737173737313253333733337333373373337333373377337373337737777710777775077574707777700000000000000011007377757753717071050140000000000000000000000000000001000101310310035737171253537177317353057733737333333333333337373373337373777737777777775475725777770477770000000000000000003005777677757717070102101000000000000000000000000000121010100310311121312135353343737733337373373333373333333737333337373737373373777777777773470052574177777700000000000000000010077575777771751016010000100000000000000000000000000100030310130307171353433035353773731717373333333337337333333373733373737377777777777777747125352757657770000000000000000001250577777753571252501410100000000000000000000000000110001011013010112130313117312777733323323332337333333333737373333737373737737777777777777140016050257407700050000000000000041003777777777357103000000000000000000000000000000002030003071301213353413437017717737373333333333333333333333333373733737373737737777777777777375017257400747100000000000000001000075777577575307505101010000000000000000000000000010101211035351010313703113733337337333333323333333733733733373333373737373737777777777777777477405670067777000000000000000000007743477777737530302500000100000000000000000000000130300313121213013431353673377373323333333333323333333333373333737373737377777777777777776747640424000474775200000000000000007575707705753553141410010100000000000010000000000013011035217131301703137331373333233333323333333333333333373373733737373737737777777777777756777004774770576705700000000000002177677057777777347130012000000000000003500000000000013125035217050131353137337333313333323333233333332337333333333733737373737737777777777464644640004047406700677505000107161756505777000575357316153050101000000000017100000000000707125131213130137333273313332333233333333332333733333373737373733733737737777777777656740000074067640000575767700416500416777777775777777717535214010000001000005370000000000424133530351302130137333323233333333333333333333233333333333333333773377373737777777757474000004656504704756524057470770071257777777777777571771341431001010000010117430000000007406753071034111013273331333323332333333233233333333733733733737373377337377777777774246740047000064704706760077077574774774577777777777777775347131020500010000035210100000000675740243103130303033323233331333333333233333333333333333333733337373337373737377776564404004064000474404004104747724740776776777777777777774735317435102100010015035700000000004642440043101010101331333323333323333233333333233332333333733373733373737373777777706400000670400000000070470477777577074757757777777777775675775701520510521001431500010000000700040056103121312103233333332333333213333323333323333333733337333737373737373777744470000004041640560046747477757556777417677707777777776567467171353413001006143043401000000074000004640210101001033323033333231333333233333333333333733373737373737337373773774676740460000640646406756777477776775774675447407776774052467747257253143525012107100000000000464270047040121303121333333323333323333333333332333333333373333333333337337373377640444004000004004000046777770707756767775677777657574256477567057357057177171410507110100000000054640676740101001003033333132333332332333033333323337333337373737373737373777774040000600004640000470047677434475034774434774750676705657740400645717377753430001214730000000000600004404042101301333323233333333333333333312333333333733733333733373373737376420000004006040420006406767767477042457707407047765774067764740064163717575251010000573500014425604450000046500210130333131323313233333333333333333373333733373733737337373777745400044004040000405447747747577774050604077447747465765044747604776445777775200010101350102467406470640000046041030113233733312333323323323323333233337333373333733737373373774664400000004000000460467767676776770675424770747725046565677654004476064065351777777777770005470474004000600470001012031323333333333333333333333333333333737337373373337377777000400000000000040000006767477676777765702576004765406770464004604700440000577777777777777750076000000000007407646001211733330332332313333333333323333333733333733377373737373744040000000000040647400477676765657656564047645076567656440756425674004704047777777777777777710400007647600540044650030123333333331333233233231233333337333373733737333373737777000040000000000040004000445740400676472470041674004740400042447560470424747677777777777777777760004047044064600000640171130337303233333333333333333333333733333733737373737737765400400000000400046004600064000400400540470047040076000470047646404004740004377777777777777777040077047707400740000740121333331373323333333333333233333333373733373337373733777046400040000000640040074006004367400407601647400764045607404650470576474040654777777777777777770404400746440044674046002117303137133133233231233233337373337333337337737373777704640000040004040000004400440674400046764064740040410065247000006746645647704427777777777777777700600047004704670400674013031377032323333333333333333233337333373733733373773737400000000000004040074567202400460000007400564706776656065646406004007247044046577777777777777777040460057706000400005674001137117313330333333333333333333333373333733737373377740400004400004000004464044047004747440046564006004454045640474654004744064760006777777777777777777400400674147700707604060307032313733333233233213233333373373337373373737377737740004000040000040400070004406640460707656475004006020064047441600474007476500077777777777777777740000000047464064074004400117313073333333333333333333373333337333337333737373770600460004604000007006464640045061046404650640560056440540064674070465647400406477777777777777774000420000760000434007060003313753533723331333333333323333337337373737373737777704700040640004000044050065000460460074004604006544640046700470640470744006647040047777777777777000400404007704000467444044013073312713330323323323323333373333333333337373737377466400404400654060006460460447474050060000460046064740004474400564464024045240640004777777656744000640074047777047446056700053531713733333333333333333333337373737373733737377774400000000047676404746540000746447465440047406704504004467404046746540470564004740046567765656424064040060777744040610674003312731353333333333333333333333333333333333737377777400004440000464640004044604464647676766746560404046000476776767677776004646400404656676646464644400400640404777600004400460011713000000000000000000000105000000000000E0AD05FE)
  534. INSERT "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(3,'Confections','Desserts, candies, and sweet breads',0x151C2F00020000000D000E0014002100FFFFFFFF4269746D617020496D616765005061696E742E5069637475726500010500000200000007000000504272757368000000000000000000A0290000424D98290000000000005600000028000000AC00000078000000010004000000000000000000880B0000880B00000800000008000000FFFFFF0000FFFF00FF00FF000000FF00FFFF000000FF0000FF000000000000001133307357377777040000000000000060600002520000141313153117160370700212401610753716176375063571725121713571701735371600000252140020700000124361677777771733337371717737373777000011111313301313577377000000000000100000500400063311313133107050734300400000030707616174335143565375717731717717161671706040767767756770642534371777777373575317373737535373371133131111111131312357770000437120067677736777777111315313117773770770431253617071711777735637373735637771773713747357167717077177356377000167774767777375313337335373713737175311113131301313161311121630353713500077534771773111371331713317777777707347725161777777177773525757773573777175375331737771602777677777770436356737177375333371713537357373537737131112711311303170131111111311316700736777767717331131131351377777777714707776167777777771753773753773571777735737570735777756777776777703656357776775337535373373717373533773773131111231301313131031313131317117704776707773535331335313311777777777637771777177777777777353573777577777377773533535537377377777777777617716375357335333331353173737533713173712711311113135213133101121311713370037173777713131171131711317777777771777167777777777777777777353537737777777777757733657777777777777671606777476731737171173373353737771777753111231031211131303711313171133131430477657671717153313131331377777777776777777343777777777774375777773577777777777373575307357777777777760717777373173717337335317371773137337371311133131133035111301533113113173430777777731313311713531531377777777353777777777777777777775377371777777777777777777377777677777777777170616177173313373131733717377373735737731031113131031321301330713113113130006172771731717313313131111377777777777571753777777777777365275771737577757777752575371717777353617772535256167173735353331713717377171737537133131210113131131131531311313713100006041671131311711317131311777777753537377365357771737737537527677573777377377777367577771777777761652063537353313173371333353737537373737731113111313113013121303313031131317010701270133531331317131131131717737377735717537735276577652743717177377377777737737536371777777071777165352735331737317137171337173773171773731211311111211303131310313133135316000765077113171531313131131177777777171637637777

Report this snippet  

You need to login to post a comment.