SQLite Create Table and Insert Data Using SQL Window

SQLite create table in SQLite SQL database

Home > database design > SQL create table

Create a Table in SQLite

our task today is to

  1. create a sqlite database
  2. create a new table – using the sqlite create table statement
  3. enter data into our database – using sqlite
  4. see the data – using sql select statements

Part 1: Learn about the create table SQL in this article

Part 2: Now create the tables for the student database.

Preparation

  1. You need your ER diagram
  2. Consider the data type (integer, text, date, etc.) of each table column
  3. The database data must match your database design

SQLite

  1. Download SQLite from https://sqlitebrowser.org/dl/
  2. Download, instal and open DB Browser for SQLite – Standard installer for 64-bit Windows

SQLite Create Table

Create a Table Statement

  • SQL create table
  • columns
  • data types
  • keys
  • constraints (e.g. check)

SQL create table syntax

CREATE TABLE student (
student_id
name
);

  1. The SQL statement ‘create table’ starts with the words ‘CREATE TABLE’
    • Uppercase letters make it easier to see the SQL but not required
  2. follow the ‘create table’ with the actual table name
  3. Start the statement with an opening bracket ‘(‘
  4. Column names are listed
    • use the order that the data will be entered
  5. Finish with the end of the statement bracket ‘)’
    • Some SQL engines require a semicolon, other will work without it.

SQL create table columns and data types

CREATE TABLE student (
student_id INT,
name VARCHAR(255)
);

  1. Each column must have a column name
  2. Followed by the data type
    • INT or INTEGER is ok
    • VARCHAR is used for text as it can use the number of characters in the text rather than the allotted size.
    • The allotted size of the text is put in brackets after varchar.
  3. Complete each row with a comma, EXCEPT the last column before the end bracket

SQL create table keys (primary key)

CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(255)
);

  1. Add the text ‘PRIMARY KEY’ to the column, which is the primary key.
    • It is normal to put the primary key column first
  2. Foreign keys come from another table therefore they require additional code, which we will see later

SQL create table check (constraints)

CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(255),
mark INT CHECK (mark >= 0 AND mark <= 100)
);
  1. We don’t have any constraints in the table above so here we have added an example column for student marks
  2. The mark is from 0 to 100
    • Therefore any integer between 0 and 100 is possible
    • But, any integer below 0, or above 100, is incorrect
  3. We add a ‘CHECK’ command and enter the constraint rules inside brackets
    • We can use logical operators like ‘OR’, ‘AND’ and ‘NOT’
    • There are other operators in SQL we can use like ‘IN’ or ‘BETWEEN’

create table in SQLite

CREATE TABLE customer (
customerid  float NOT NULL primary key,
firstname  varchar ( 255 ),
lastname  varchar ( 255 ) NOT NULL,
city  varchar ( 255 ),
state  varchar ( 255 )
);
CREATE TABLE myemployee (
ID integer primary key autoincrement,
firstname varchar ( 30 ),
lastname varchar ( 50 ),
title  varchar ( 30 ),
age  number ( 3 ) check (age>=18),
salary  number ( 10 , 2 ) check (salary >= 10000)
);

Continue to the SQL Code for the Student database

second part of lesson 2: SQL code for the Student Database

return to the main page on relational database design

Using SQLite FAQ Section

What is the SQLite create table command?
The create table command is used to create a new table inside a database file.

How do I create a database in SQLite?
You can create a database with sqlite3 by opening a new database file and then using the create table command.

What happens if a table already exists?
If a table already exists, you will get an error unless you use the IF NOT EXISTS option in your statement.

Can I delete a table in SQLite?
Yes, you can delete a table by using the DROP TABLE command.

What is a schema in SQLite?
A schema is the structure of the database. It shows the tables, column definitions, and how data is stored.

What is a default value in SQLite?
A default value is what the database will use if no data is given for a column.

How are null values handled in SQLite?
Null values mean no data is stored in that column. SQLite supports null values unless you use a constraint like text not null.

What is defined as the primary key?
The primary key is one column that uniquely identifies each row in a table.

Does SQLite support multiple tables?
Yes, sqlite supports multiple tables in the same database file.

What type of string is used in SQLite?
SQLite uses the TEXT type for string data.

Useful information

The statement is used to create a table with column definitions.
Column constraint rules control data, such as text not null.
It is important to note that an existing table cannot be created again without using IF NOT EXISTS.
SQLite documentation gives examples of how to specify column types and default values.
A developer can learn how to create tables quickly with practice.
The interface for sqlite3 is simple for students.
The database engine of SQLite is widely used in many sql databases.