Creating tables in SQL

Another SQL tutorial with basic methods

Published in

The Startup

4 min readJun 21, 2020

There are many ways to insert data into a database. Most are automated and exists through the creation of an app’s user data. I can’t speak to these processes. However, I am very interested in learning about these!

In this blog, I want to show two manual ways of creating a SQL table and adding data to that table. So, there are two goals of this post:

Goal: learn how to insert data into a table for SQL toy examples, and
Goal: learn how to create a SQL table from a csv file.

Again, I am going to use data from astronomical objects as examples. Let’s begin.

First, let’s cover creating a table of data for a toy example. Suppose you want to recreate the black_holes table I have used in many of my SQL posts, one of which is Classifying black holes with SQL. Let’s use the same data, which I have provided again, here.

Now create a table with the data columns specified. Here, the columns are:

name — the name associated with the black hole (a record); this is recorded as TEXT data type.
base_mass and power — the mass of the record, each recorded as anINT (integer). To understand this, use scientific notation: base_mass x 10^power. The unit of mass for these black holes is in solar masses, or “the mass of the Sun”. For example, the Sombrero galaxy supermassive black hole is 1x10⁹ times the mass of the Sun.

And, we will include one more column:

num — the unique number of the record, which is recorded as an INT.

To create a table in the database, we need to create a table object with the CREATE TABLE statement. There is a simple syntax for creating a SQL Server table —

CREATE TABLE table_name (
  column_name data_type [NULL|NOT NULL]
);

where data_type is what kind of data that is in the column (for example, INT, TEXT, CHAR, etc.). The [NULL|NOT NULL] option specifies if the column needs a value when a record is inserted.

If a column is defined to be NOT NULL then we cannot create a record without an actual value in the column.
If a column is defined to be NULL then we can create a row without an actual value in the column.

Here is an example.

CREATE TABLE black_holes (
  num INT NOT NULL,
  name TEXT NOT NULL,
  base_mass INT NOT NULL,
  power INT NOT NULL
);

Finally, we can insert the data for the four records into the table with the INSERT INTO statement.

INSERT INTO black_holes(num, name, base_mass, power)
  VALUES (1, 'M104 - Sombrero Galaxy', 1, 9),
  (2, 'M31 - Andromeda Galaxy', 1, 8),
  (3, 'NGC 4889', 1, 10),
  (4, 'Cygnus X-1', 15, 0);

Now, let’s cover creating a data table from a csv file. For this example, I will use statistics for near-Earth asteroids and comets discovered by NASA’s WISE mission. A csv file for this data can be downloaded here. I will also be using a flavor of SQL — PostgreSQL — for the statements.

When creating a SQL table from a csv file, we need to know the order of columns and type of data in the columns. This can be found in a data dictionary, but sometimes you need to contact the subject matter experts of the data within the company to discover this information. Luckily, we have all the information we need listed on the link referenced above for the data download. Again, we use the CREATE TABLE statement with column names, data types, and the [NULL|NOT NULL] features. The code follows.

CREATE TABLE comets (
  designation TEXT NOT NULL,
  discovery_date DATE NOT NULL,
  h_mag FLOAT NOT NULL,
  moid_au FLOAT NOT NULL,
  q_au_1 FLOAT NOT NULL,
  q_au_2 FLOAT NOT NULL,
  period_yr FLOAT NOT NULL,
  i_deg FLOAT NOT NULL,
  pha TEXT NOT NULL,
  orbit_class TEXT NOT NULL
);

Once you have the table created, inserting the data is easy using the COPY statement. COPY moves data between tables and standard files; where COPY FROM copies data from a file to a table.

COPY comets
FROM '../data/WISE_NEA_COMET_DISCOVERY_STATISTICS.csv'
DELIMITER ',' CSV HEADER;

Note that DELIMITER specifies the character that separates columns within each row of the file and HEADER specifies that the file contains a header line with the names of each column in the file.

There you have it, folks! Two more tools for your SQL toolbox. Have questions? Comments? Let’s discuss this in the chat!

For your reference, I have a series of tutorials that cover basic and intermediate SQL topics. Take a look!

Creating tables in SQL

Another SQL tutorial with basic methods

Written by Karen Warmbein