Many RDBMSs will have a graphical diagramming tool which can be used to create a database; this is often helpful, particularly if you find it easier to visualize the database that way. In other circumstances, it is possible to use an Object-Relational Model (ORM) tool to create databases directly from an object model built in an object-oriented language, or there may be executables which will create a database from a configuration file. However, there are times—such as when creating a database with code or a script—when it is useful to write SQL to create the database (the graphical and object-relational tools are actually generating this SQL for you; SQL is how databases are created).

The code to create an empty database itself is fairly simple:

Your RDBMS will likely have several other possible parameters such as collation options, but a command of this sort typically uses default values for those which are fine for most purposes. If you need to delete the database, use the DROP command but beware; you should assume that this operation is irreversible.

With the database created, it is time to create the tables. An aside: a fully-qualified object name, such as a column in a table in a database, will look something like this (but check your RDBMS documentation):

It is often a good idea to fully-qualify object names if there is a possibility of your SQL being run in the wrong context or scope.

Tables are created using the CREATE TABLE command. Whether columns default to creating columns do or do not allow NULL values by default will depend on your RDBMS, your configuration and potentially also the tool you use to connect. If unsure, it is best to specify.

Some notes on this command. Firstly, we specify whether columns accept NULL values or not—we allow NULLs for measurement_unit in case the measurement doesn't have units (say, it is a ratio)—and specify each column's datatypes. We also specify the primary key and should give the primary key a name as it is a SQL object; in this case we called it "PK_Instrument_models" and picked the single column "instrument_model" as the primary key. In addition to specifying the primary key, if we are using a surrogate key for that field and want an auto-incrementing integer field, we can specify it here but the syntax will depend on the RDBMS in use. For the Measurements table we could give a comma-separated list of "(weatherstation_name, instrument_id, datetime)" instead (see below). We can also create foreign keys:

Here, we made a choice that instrument_id would be a surrogate key; in practice we'd probably make it an auto-incrementing value, but that is done with RDBMS-specific SQL so is not shown here. In this case we would most likely also include a column for serial numbers, something usefully real-world which also allows identification.

Indexes are created independently, after the table has been created (if a table is to be loaded with an initial burst of data, sometimes it will be better to first create the index after the loading is complete). Here we add an index to Instruments.instrument_model as that is a foreign key column for the foreign key onto Instrument_models.instrument_model.

It is also possible to alter tables once they have been created using the ALTER TABLE command, with the general syntax:

For example:

or