SQL server runs as a service and will normally be running all the time that the PC is switched on. When set up right it will serve data on ports 1233/1433 and another machine on the network can access and interact with that data as long as Windows Authentication or SQL Authentication is operating properly.

If you don't have an app to interact with it you can use MS Access and set the SQL database as a data source, it is relatively easy to set up. However you still need a network connection.