According to Microsoft "SQL Server Express is a version of SQL Server 2005 designed to helps developers build robust and reliable applications by providing a powerful database that is also free and easy to use."
I know nothing about SQL Server, except that there is a free cut-down version called the Microsoft database engine (MSDE), used by many products where a full-blown SQL Server installation would be overkill.
With SQL Server 2005 Express Edition (the replacement for MSDE), Microsoft have made some changes in the packaging and promotion of the product - it's better in some ways, but more limited in others. It also has a partner product - Microsoft SQL Server 2005 Express Manager, which can be used for database administration (including existing SQL Server 2000 and MSDE installations).
You can read more about SQL Server 2005 Express Edition, including a comparison with MSDE and links for download on Mat Stephen's weblog (Mat is one of the Microsoft UK IT Evangelists).