Which version of SQL Server I would need to purchase in order to be able to legally include in a web app I am going to package? In other words, I need to be able to build it into the package along with the app so when it is loaded on the users machine, they have a local db available.
To date I've only worked with Sql db's on a server. Now I want to package stand alone db's with application. I want to avoid using Access.
You don't really say a lot about what your requirements are or what environment/language this web app is written in. I'm guessing this is Microsoft SQL Server you're asking about, so I would guess that SQL Express would be enough for most cases. If this is asp.net, you could even put the data files in the App_Data folder.
If you're just looking for a free sql database there are many: SQLite as Valentin suggested, PostgreSQL, MySQL, Firebird, etc.
You'll want your users to install SQL Server Express - it's what MS provides as the solution for this exact problem.
If your app requires more than Express can offer, your clients will need to purchase SQL Server. If this is an Access-scale application then that isn't likely.
Are you doing custom software for individual clients? If so, then consider becoming a reseller of MS licenses. That way you can profit on the sale of the licenses to your customers, in addition to the consulting fees for software development. (For instance, if you develop an intranet app, you can probably sell a Windows Server 2008, SQL Server and maybe Sharepoint license..)
The only version you can embed in your own application distribution is SQL Server Express, which is free. For all other versions your clients have to purchase a license. You cannot transfer your license to your clients. You can however become a reseller and sell them the required license along with the application, as Andrew suggests. For more details see SQL Server 2008 Pricing.