Setting Up a Database

Three Ways to Get a Database, and Which to Pick

You cannot learn SQL by reading it. You need somewhere to type a query and watch rows come back, including the queries that fail — the error messages teach as much as the successes. There are three practical routes, and the right one depends on how much setup pain you are willing to accept today.

The fastest is a browser sandbox. Sites such as DB Fiddle and SQLite Online give you a query box and a results grid with nothing to install. Use one if you are reading this on a college machine where you cannot install software, or if you just want to try one statement from a lesson. The limitation is that your tables usually disappear when you close the tab, so it is no good for building a project.

The middle route is SQLite. It is a real SQL database that lives entirely in one file, with no server to start or stop. If you have Python installed you already have it. It is excellent for practising queries and perfectly good for a small project, but its type system is deliberately loose and it is missing a few things this course uses, so it is not a complete substitute.

The route worth taking if you have a machine of your own is a real server: MySQL or PostgreSQL installed locally. This is the setup companies use, and getting it running teaches you things — users, ports, permissions — that a browser sandbox hides from you. Everything in this course is written for MySQL, so that is the smoothest path.

• Browser sandbox — zero setup, nothing persists, good for trying a single query
• SQLite — one file, no server, built into Python; good for practice and small projects
• MySQL Community Server — free, matches this course exactly, most common in Indian college labs
• PostgreSQL — free, stricter and more standards-faithful; a very good second database to know
• XAMPP — bundles MySQL with Apache and PHP in one installer; convenient if you are also learning PHP

Installing and Connecting

Download MySQL Community Server — the free edition — from the official MySQL site, or PostgreSQL from postgresql.org. Both installers walk you through the process. The one screen that matters is where MySQL asks you to set a root password. Write it somewhere you will find it again; recovering a forgotten MySQL root password is a genuinely annoying afternoon.

Once installed, the server runs quietly in the background as a service and listens on a network port — 3306 for MySQL, 5432 for PostgreSQL. You then connect to it with a client. This is the distinction that trips up almost everybody at the start: the server stores your data, and the client is the separate program you type queries into. The mysql command in your terminal is a client. MySQL Workbench is a client. Your Python or PHP application is a client. None of them holds any data.

Understanding that split turns confusing errors into obvious ones. "Can't connect to MySQL server on 127.0.0.1" means the client worked fine but nothing was listening on that port — the service is not running. "Access denied for user 'root'" is the opposite: the server is running and answered you, it just did not accept the password.

# Connect from a terminal (MySQL). You will be prompted for the password.
mysql -u root -p

# PostgreSQL uses a different client
psql -U postgres

# SQLite needs no server at all - this makes or opens a file
sqlite3 practice.db

-- Once you are in the MySQL client, check what you are talking to
SELECT VERSION();

-- List the databases that already exist on this server
SHOW DATABASES;

-- MySQL creates these four itself; leave them alone
--   information_schema, mysql, performance_schema, sys

Creating a Database and Switching Into It

A database here means a named container that holds tables, views and indexes that belong together. One server can hold many of them, and they are kept apart deliberately: your college project database, a tutorial database and a company application can sit on the same server without ever seeing each other's tables.

Create one with CREATE DATABASE. Then — and this is the step beginners forget — you must switch into it with USE. Until you do, the client has no current database, and any CREATE TABLE you run fails with "No database selected". If you open a new terminal tomorrow, you have to run USE again; the selection lasts only as long as the connection.

Name the database for what it holds, in lowercase with underscores: college_portal, not MyDB1 or test2_final_FINAL. On Linux servers, MySQL database names are case-sensitive, so a habit of all-lowercase names saves you a deployment surprise later.

-- Create a database
CREATE DATABASE college_portal;

-- Safe version: does nothing instead of erroring if it already exists (MySQL)
CREATE DATABASE IF NOT EXISTS college_portal;

-- Switch into it. Everything after this runs inside college_portal.
USE college_portal;

-- Confirm which database you are actually in
SELECT DATABASE();

-- What tables does it contain right now?
SHOW TABLES;

-- Removing a database: this deletes every table and every row inside it,
-- immediately, with no confirmation prompt and no undo.
-- DROP DATABASE college_portal;

Character Sets: Why Indian Names Sometimes Turn Into Question Marks

This section looks like a detail and is not. A character set decides which characters a text column can physically store, and a collation decides how text is compared and sorted. Get them wrong and the symptom is unforgettable: a student named "अनन्या" is saved and comes back as ???????, or an emoji in a review breaks the insert entirely.

MySQL carries a historical trap here. Its character set named utf8 is not real UTF-8 — it stores a maximum of three bytes per character, which is enough for most Indian scripts but not for emoji or some less common characters. The complete one is called utf8mb4. MySQL 8.0 finally made utf8mb4 the default, but plenty of tutorials, older servers and shared-hosting setups still hand you utf8, so it is worth stating explicitly when you create the database.

Collation also decides case sensitivity in comparisons. With a collation ending in _ci (case-insensitive), WHERE branch = 'cse' matches a stored value of 'CSE'. With a case-sensitive collation it does not. That single letter in the collation name explains a surprising number of "my WHERE clause returns nothing" problems.

-- Create a database that can store any script and any emoji (MySQL)
CREATE DATABASE college_portal
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Check what an existing database was created with
SHOW CREATE DATABASE college_portal;

-- Check the server defaults
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

Saving Your Work: Script Files and Backups

Typing statements into a client one at a time is fine for exploring, but the statements that build your database should live in a .sql file that you keep with the rest of your project. Then rebuilding from nothing is one command, and a teammate — or a marker of your project — can create the exact same database in seconds.

The second habit is backups. A backup in SQL is usually a dump: a plain text file full of CREATE TABLE and INSERT statements that recreate everything. mysqldump produces one for MySQL, pg_dump for PostgreSQL. Take a dump before doing anything you are unsure of. It takes ten seconds and it is the difference between a mistake and a disaster.

That advice is not abstract. Later in this course you will meet DELETE and UPDATE without a WHERE clause, which rewrite every row in a table instantly. There is no recycle bin. A dump file taken five minutes earlier is the entire recovery plan.

# Run a whole file of SQL (from your normal terminal, not inside the client)
mysql -u root -p college_portal < setup.sql

# Or, from inside the MySQL client
SOURCE C:/projects/college_portal/setup.sql;

# Back up one database to a text file
mysqldump -u root -p college_portal > backup_2026_08_02.sql

# Restore it into an empty database
mysql -u root -p college_portal < backup_2026_08_02.sql

# PostgreSQL equivalents
# pg_dump -U postgres college_portal > backup.sql
# psql -U postgres college_portal < backup.sql