A session cookie is a cookie that is set for the duration of the current browser session. The browser session lasts as long as the browser is not closed by the user. Once the browser is closed, the session ends and the cookie is deleted. The next time the user wants to log into the website, they'll need to enter their information again to begin a new session.
A common use of a session cookie is for a shopping cart. As long as the user keeps the browser running, the shopping cart will be saved. The user can browse that site further or even go to other sites and come back, and the shopping cart will not lose any of the shopping cart items.