To create a table with data in SQL you first build a frame for the data to go into. To do this you list each desired column name along with the data type for that column.
The datatype options are as follows:
Numeric-
- INTEGER: A whole number.
- NUMERIC(p,s): A fixed number, where p is the total number of digits and s is the total number of digits after a decimal point.
- FLOAT: Typically stores up to 7 digits.
- DOUBLE: Typically stores up to 16 digits.
Character-
- VARCHAR(n): A variable-length string with a length specified by n.
- CHAR(n): A fixed-length string padded or stripped to the length of n.
Date-
- DATE: Stores a date.
- TIME: Stores a time.
- DATETIME: Stores a data and time.
Now we know which data types are allowed, we can look at the syntax for building our table: Here, NEW_TABLE is the table name and Column1 and 2 are the columns being inputted.
The example below will make a table called customers, with three columns (customer_id, first_name, last_name):
We can specify primary keys by simply writing PRIMARY KEY next to the column of our choice.
Now we have our frame we can populate this with data. To do this we can use the insert function: Here, you specify the columns you want to insert data into, and then specify the values you would like to insert:
