Database Management · Beginner level

Beginner level · Module 1: Relational Databases and First Queries · Lesson 2 of 3

Setting Up a Database and Tool

Install a database and a graphical tool, create a database and run SQL statements.

In this lesson you will

  • Install SQLite with DB Browser (or PostgreSQL with a client)
  • Create a table and run statements in the SQL editor

Key concepts

SQLite file databasePostgreSQL servergraphical clientCREATE TABLE

The lesson

SQLite stores a whole database in one file and needs no server, which makes it ideal for learning and for small offline applications. PostgreSQL runs as a server and suits multi-user systems. The SQL you learn works in both, with small differences.

A graphical tool such as DB Browser for SQLite, pgAdmin or DBeaver lets you browse tables and run SQL in an editor. Save your SQL in files so you can run it again and keep a record of your work.

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT, year INTEGER); creates a table. NOT NULL makes a column required, and the primary key identifies each row.

Step by step

  1. Install SQLite and DB Browser for SQLite.
  2. Create a new database file.
  3. Create a books table with CREATE TABLE.
  4. Save the SQL in a file and run it again on a fresh database.

Practice activity

Create the library database with a books table defined in a saved SQL script.

Expected result: Running the script on an empty database creates the table correctly every time.

Check your understanding

  1. What is special about SQLite?

    Show answer

    The whole database is one file and no server is needed.

  2. What does NOT NULL do?

    Show answer

    Makes a column required.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.