How to Create and Manage a Database in SQL, MySQL and PostgreSQL

To create a database in SQL, run CREATE DATABASE your_db_name; in your SQL client. MySQL, PostgreSQL and SQL Server all support this command. Charset, collation and management syntax differ across engines. This guide gives you the exact commands for each, side by side, so you stop switching between tabs.

• Key Takeaway 1: The core CREATE DATABASE command works across MySQL, PostgreSQL and SQL Server, but charset and collation options vary by engine.
• Key Takeaway 2: Listing databases uses SHOW DATABASES in MySQL, \l in PostgreSQL and sys.databases in SQL Server.
• Key Takeaway 3: MySQL has no native RENAME DATABASE command. You need a dump-and-restore workaround.
• Key Takeaway 4: DROP DATABASE is irreversible. Always run a mysqldump or equivalent backup first.
• Key Takeaway 5: A well-typed CREATE TABLE statement with correct data types is your single biggest quality control lever.

How to Create a Database in SQL and Build Your First Table

The fastest way to understand how to create a database in SQL is to run the command in a live environment. MySQL Workbench, psql and SQL Server Management Studio all give you an interactive query window. Open it and start there.

CREATE DATABASE: MySQL, PostgreSQL and SQL Server Side by Side

In MySQL, the full production-safe command to create a database in SQL is:

CREATE DATABASE school_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The utf8mb4 charset handles emojis and multilingual content, which matters if you are building anything for Indian regional languages such as Hindi, Tamil or Bengali. The collation controls sort order and comparison rules.

In PostgreSQL, the equivalent command to create a database in SQL is:

CREATE DATABASE school_db ENCODING 'UTF8' LC_COLLATE 'en_IN.UTF-8' LC_CTYPE 'en_IN.UTF-8';

PostgreSQL inherits locale settings from the OS, so specifying them explicitly avoids surprises when you deploy on a different server.

In SQL Server, you write:

CREATE DATABASE school_db COLLATE Latin1_General_CI_AS;

SQL Server attaches the database to a default filegroup unless you specify otherwise. For learning purposes, the one-liner above is fine.

How to Create a Table in a Database

Once your database exists, select it and define your tables. In MySQL, run USE school_db; before any table commands. In PostgreSQL, connect directly with \c school_db.

Here is a typed CREATE TABLE example that works across all three engines with minor syntax tweaks:

CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE,
  enrollment_date DATE,
  gpa DECIMAL(3,2)
);

Use AUTO_INCREMENT in MySQL, SERIAL or GENERATED ALWAYS AS IDENTITY in PostgreSQL, and IDENTITY(1,1) in SQL Server for auto-generated primary keys. Getting data types right at this stage saves painful schema migrations later. Indian edtech platforms like BYJU’s and Unacademy, for example, rely on correctly typed schemas to handle millions of student records without performance degradation.

If you want to go deeper on how structured data feeds into analytics pipelines, the Big Data Analytics notes on 3.0 University cover exactly that progression from raw tables to analytical models.

Feature	MySQL 8.x	PostgreSQL 16	SQL Server 2022
Default charset	utf8mb4	UTF8	Latin1_General
Auto-increment syntax	AUTO_INCREMENT	SERIAL / IDENTITY	IDENTITY(1,1)
List databases command	SHOW DATABASES;	\l	SELECT name FROM sys.databases;
Select/switch database	USE db_name;	\c db_name	USE db_name;
Rename database	No native command	ALTER DATABASE old RENAME TO new;	ALTER DATABASE old MODIFY NAME = new;
GUI tool	MySQL Workbench	pgAdmin 4	SSMS

According to the Stack Overflow Developer Survey 2024, MySQL is the most used database among professional developers globally at 40.3% usage share. PostgreSQL leads in developer satisfaction, with 48.7% of its users saying they love working with it. SQL Server remains dominant in enterprise India, particularly in BFSI and government sectors.

Ready to build on these fundamentals with structured mentorship? Explore online certification courses at 3.0 University covering Data Analytics, AI, Blockchain and Web3, all built around practical labs that employers in India and globally recognize.

How to List, Select and Rename Databases in SQL

Knowing how to show databases in MySQL is one of those basics that trips people up. When you are managing twenty databases on a shared host, you will use these commands constantly.

Listing All Databases

In MySQL, type SHOW DATABASES; in the query window. MySQL Workbench also shows them in the left-hand schema panel for a quick visual overview without writing a query.

In PostgreSQL, the psql meta-command \l lists every database with its owner, encoding and collation. You can also query the catalog directly:

SELECT datname FROM pg_database;

In SQL Server, the system view does the work:

SELECT name, database_id, create_date FROM sys.databases;

This is particularly useful for auditing because it shows creation timestamps alongside names.

How to Select a Database in SQL

To select a database in SQL and make it the active context for subsequent queries, MySQL and SQL Server both use USE db_name;. PostgreSQL users connect at the session level, so you either specify the database when launching psql or switch with \c db_name mid-session.

How to Rename a Database in MySQL

MySQL removed its RENAME DATABASE command in version 5.1.23 and never brought it back. The safe workaround is a three-step process: export with mysqldump, create the new database, then import.

-- Step 1: Export
mysqldump -u root -p old_db_name > old_db_backup.sql

-- Step 2: Create new database
CREATE DATABASE new_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Step 3: Import
mysql -u root -p new_db_name < old_db_backup.sql

Once you have verified the new database is intact, drop the old one. Do not skip the verification step. A lot of data loss stories in Indian startups and college projects start with a confident DROP DATABASE run too early.

In PostgreSQL, renaming is straightforward:

ALTER DATABASE old_name RENAME TO new_name;

In SQL Server:

ALTER DATABASE old_name MODIFY NAME = new_name;

Both require that no active connections exist to the target database at the time of renaming.

Database administration skills like these sit right at the entry point of a data career. If you are weighing whether to stay in data science or branch out, this guide on how to shift from data science to AI and ML walks through what skills transfer and what you would need to add.

How to Back Up, Restore and Drop a Database Safely

This is where careless commands cost real money. According to IBM’s Cost of a Data Breach Report 2024, the average cost of a data breach in India reached Rs 19.5 crore, up 39% since 2020. Most breaches are not sophisticated hacks. Many are accidental deletions or misconfigured permissions.

Backing Up with mysqldump

The mysqldump utility is MySQL’s built-in export tool. A full database backup runs as:

mysqldump -u root -p --databases school_db > school_db_backup_2025.sql

Add --single-transaction for InnoDB tables to avoid locking issues during the dump. For PostgreSQL, the equivalent is:

pg_dump school_db > school_db_backup.sql

SQL Server users typically use the BACKUP DATABASE T-SQL command or SQL Server Management Studio’s backup wizard.

Restoring a Database

Restoring from a MySQL dump is one command:

mysql -u root -p school_db < school_db_backup_2025.sql

For PostgreSQL:

psql -U postgres -d school_db -f school_db_backup.sql

Test your restores regularly. A backup you have never restored is a backup you cannot trust.

How to Drop a Database in SQL

Warning: this is permanent. Running DROP DATABASE school_db; removes the database, all its tables, all its data and all associated objects in one shot. There is no recycle bin. There is no undo.

The command is the same across MySQL, PostgreSQL and SQL Server. Always run your backup first. In MySQL Workbench, you can right-click a schema and choose “Drop Schema,” but the GUI does not add any extra safety net. It still executes immediately.

To avoid accidents in a shared environment, revoke DROP privileges from non-admin users:

REVOKE DROP ON *.* FROM 'dev_user'@'localhost';

That single line prevents a lot of 2 a.m. panic calls.

According to the DB-Engines Ranking (January 2025), MySQL, PostgreSQL and SQL Server hold the top three spots among relational database engines by popularity score, a position they have held consistently for over a decade. Learning to create a database in SQL across all three engines gives you coverage across almost every employer in India’s tech sector.

The demand for SQL-proficient data professionals in India is growing fast. According to the NASSCOM Technology Sector Report 2023, over 60% of data-related job descriptions in India explicitly list SQL as a required skill, ahead of Python and Excel. Getting comfortable with CREATE DATABASE, table design, backups and safe deletion puts you ahead of a large portion of applicants who only know SELECT queries.

The AI, blockchain and data science careers in India overview on 3.0 University breaks down which roles are hiring, what salaries look like and which skills get you past the first screening round.

If you want structured practice with mentorship, the bootcamp training programs at 3.0 University include hands-on database labs where you build, populate, query and manage real databases across multiple engines. You can also connect with peers working through the same material in the REACH learner community.

Frequently Asked Questions

How do I create a database in SQL?

Run CREATE DATABASE your_db_name; in your SQL client. For MySQL, add CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci for full Unicode support. For PostgreSQL, use ENCODING 'UTF8'. For SQL Server, specify a collation after the database name. All three engines accept the base CREATE DATABASE syntax.

How do I list all databases in MySQL?

Type SHOW DATABASES; in the MySQL query window or in MySQL Workbench’s SQL editor. This lists every database your current user has permission to see. In PostgreSQL, use \l in psql. In SQL Server, query SELECT name FROM sys.databases; to get the same result.

How do I drop a database in SQL?

Run DROP DATABASE db_name; but understand this is completely irreversible. The database, all its tables and all its data are gone immediately. Always take a full backup using mysqldump in MySQL or pg_dump in PostgreSQL before running this command. Revoke DROP privileges from non-admin users in shared environments.

How do I create a table in a database?

First select your database with USE db_name; in MySQL or connect to it in PostgreSQL. Then run CREATE TABLE table_name (column1 datatype constraints, column2 datatype constraints);. Always define a primary key, use appropriate data types like VARCHAR, INT and DATE, and add NOT NULL constraints on columns that must always have a value.

How do I rename a database in MySQL?

MySQL has no native rename command. The workaround is: export the old database with mysqldump, create the new database with CREATE DATABASE new_name;, import the dump into the new database, verify the data, then drop the old database. PostgreSQL makes it simpler: ALTER DATABASE old_name RENAME TO new_name; works directly.

Last updated: June 2025. Reviewed by the 3University editorial team.