SQL on your own computer

Part of the free LogicWiz SQL & Databases course, Module 1: What a database is. The queries and practice run in the app on real Postgres.

Meher's first interview round is a take-home task: a file called orders.sql and one line, "Load this into Postgres and answer the five questions." Her laptop has no Run button like this page. Where does she even type the SQL?

You'll learn to

Choose between Postgres, MySQL and SQLite for your own machine
Explain what a database server is and how a client connects to it
Create a database, run a `.sql` file and look around with psql or MySQL commands
Fix the four problems almost everyone hits on day one

Learn

A server and its clients

On your computer, Postgres runs as a server: a program that starts with the computer, runs quietly in the background, holds the data and waits for questions.

You never open its files yourself. You use a client: a program that connects to the server, sends your SQL and shows the result. psql, pgAdmin, DBeaver and the app you build are all clients. Think of one kitchen (the server) and many waiters (the clients) carrying orders in.

To connect, a client needs:

host: which computer (localhost means this one)
port: which door on it (Postgres uses 5432, MySQL 3306)
user and password
database: which database on that server

Which one to install

Database	Needs a server?	Where you meet it	Pick it when
PostgreSQL	Yes	Startups, data teams, this course	You are following this course: the SQL is exactly what you run here
MySQL	Yes	Very common in Indian company stacks, PHP and Java web apps	Your company, lab or project already uses it
SQLite	No: the whole database is one file	Inside phone apps, browsers and small tools	You want zero setup for a small project

All three are free and speak SQL. Learn on Postgres; moving to MySQL later is a few small differences, which this course points out in MySQL notes.

Install Postgres and connect

Download the installer for your system from postgresql.org/download. On Windows and Mac it also installs pgAdmin, a point-and-click client.
Setup asks you to choose a password for the main user, postgres. Write it down. Keep the port at 5432.
Connect. On Windows or Mac, open SQL Shell (psql), which came with the install. Press Enter to accept each default (server, database, port, user), then type your password. On Linux, run sudo apt install postgresql, then sudo -u postgres psql.

Prefer clicking? pgAdmin and DBeaver (a free client that works with every database in this lesson) ask for the same host, port, user and password in a form.

Your first session: psql and MySQL side by side

Step	psql (Postgres)	mysql (MySQL)
Connect	psql -U postgres	mysql -u root -p
Make a database	CREATE DATABASE college;	CREATE DATABASE college;
List databases	\l	SHOW DATABASES;
Switch to it	\c college	USE college;
Run a .sql file	\i college.sql	SOURCE college.sql;
List tables	\dt	SHOW TABLES;
Describe a table	\d students	DESCRIBE students;
Ask a question	SELECT FROM students;	the same
Quit	\q	exit

Read it top to bottom: that is a whole first session. A .sql file is just SQL saved in a text file, and \i or SOURCE runs it as if you had typed it. If the file is in another folder, give the full path with forward slashes, even on Windows: \i C:/Users/meher/Downloads/college.sql. Commands that start with a backslash belong to psql itself, not to SQL: they need no semicolon, and they do not work in pgAdmin, in your app or on this page.

The first query to run anywhere

SELECT version();

SELECT current_database(), current_user;

Run these the moment you connect. If they answer, the connection works. The first tells you which Postgres you have; the second tells you which database you are in, and as whom, which settles half of all "where did my table go?" moments. Here they describe the Postgres that runs inside this page, with no server at all. MySQL has SELECT VERSION(); and SELECT DATABASE();.

\dt and \d in plain SQL

-- the tables in this database (like \dt)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

-- the columns of one table (like \d students)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'students'
ORDER BY ordinal_position;

information_schema is a set of read-only tables that describe the database itself: its tables, columns and keys. It is standard SQL, so it works here, in pgAdmin, in your app and in MySQL. public is the default schema, the folder your own tables go in. ORDER BY ordinal_position lists the columns in the order the table has them (ORDER BY comes in Module 2).

This page rebuilds the database before every run, so nothing you do here lasts and nothing can break. On your own computer everything lasts. A row you insert is still there tomorrow, and so is a DELETE FROM students; you ran without a WHERE. There is no undo button. Three habits:

Keep the .sql file. To start over, drop the database, create it again and re-run the file.
Try risky changes inside BEGIN; … ROLLBACK; first, as in the last lesson.
Back up before big changes: pg_dump -U postgres college > college_backup.sql (MySQL has mysqldump).

First-day problems and their fixes

You see	It means	Do this
Connection refused or could not connect to server	The server is not running, or you used the wrong port	Start it. Restarting the computer usually does. Windows: open Services, find the one named postgresql-…, click Start. Linux: sudo systemctl start postgresql. Mac with Homebrew: brew services start postgresql@17.
password authentication failed for user "postgres"	Wrong password, or the wrong user	Use the password you chose during setup. The main user is postgres (in MySQL, root).
relation "students" does not exist	You are in the wrong database, or the file was never run	Run \c college, then \dt to see what is really there.
database "college" does not exist	It was never created, or the name is spelt differently	Check with \l, then CREATE DATABASE college;.
The prompt changes from college= to college- and nothing happens	psql is waiting for the ; that ends your statement	Type ; and press Enter. To throw away a half-typed query, type \r.

Read the error slowly. It nearly always names the problem.

MySQL on your machine. Install MySQL Server and MySQL Workbench (its point-and-click client) from dev.mysql.com. The main user is root, the port is 3306, and you connect with mysql -u root -p (-p makes it ask for the password). In MySQL a database and a schema are the same thing, so in information_schema you filter on table_schema = 'college', not 'public'. Instead of psql's \dt and \d students, type SHOW TABLES; and DESCRIBE students;.

In one line

Postgres and MySQL run as servers that clients (psql, pgAdmin, DBeaver, Workbench) reach with host, port, user and password; psql's \l \c \dt \d \i are MySQL's SHOW DATABASES, USE, SHOW TABLES, DESCRIBE, SOURCE.