SQL CREATE DATABASE and DROP DATABASE: Dialect Guide

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

CREATE DATABASE chooses a name and requests a new database container. It does not automatically define customer tables or insert records. Character encoding, collation, ownership and default storage settings may be chosen at creation time or inherited from a template. These choices affect later text comparison and object management. In PostgreSQL, a database is a connection boundary; after creating it you connect to it separately before creating its application tables.

MySQL normally selects a database with USE database_name. SQL Server also supports USE, while PostgreSQL clients establish a new connection or use a client command such as psql’s backslash-c. A client command is not portable SQL and should not be sent through an ordinary SQL driver as if it were a server statement. Check the current database before running schema creation, especially in a tool that keeps several connections open.

DROP DATABASE removes the entire named database, not just its rows or one table. There is no WHERE clause that limits it to selected records. A backup or exported script is not useful unless it can actually restore the required objects and data. Server-specific rules may prohibit dropping the current database or a database with other active connections. Permission to read a table does not imply permission to create or remove a database.

IF EXISTS and IF NOT EXISTS support is not uniform. MySQL offers both forms for the relevant database commands. PostgreSQL supports DROP DATABASE IF EXISTS but not CREATE DATABASE IF NOT EXISTS. PostgreSQL CREATE DATABASE and DROP DATABASE cannot run inside a transaction block. MySQL database DDL commonly causes implicit commits. SQL Server has its own context and permission rules. SQLite has no CREATE DATABASE or DROP DATABASE statement: opening a database file creates or accesses it, and file lifecycle is handled outside this SQL command family.

This CREATE statement is an administration example for PostgreSQL, MySQL or SQL Server when the caller has the required permissions. Additional options and transaction restrictions differ. SQLite uses a database file or in-memory connection instead, so do not execute CREATE DATABASE there. To remove only the disposable server database later, use the documented DROP DATABASE command for that engine.

Check the engine-specific syntax and version requirements in PostgreSQL CREATE DATABASE, PostgreSQL DROP DATABASE.

In PostgreSQL, verify whether a database called training_reference exists. This read-only check should be run after the creation example in a server connection.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT datname FROM pg_database
WHERE datname='training_reference';

datname
training_reference

pg_database is PostgreSQL’s database catalog. The filter checks the explicit practice name and does not remove or modify anything. An absent result means that name is not present on the server visible to this connection; it is not proof that a SQLite file or another server has no such database.

On a disposable server, create the named practice database once and verify its catalog entry. Attempting the same creation again should be handled according to the dialect’s duplicate-name behaviour, not assumed idempotent. Reconnect to it and inspect its initially empty application schema. In SQLite practise opening a separate disposable database instead of executing unsupported database-creation SQL.