SQLite Create Table and Insert Data Using SQL Window
Create a Table in SQLite
our task today is to
- create a sqlite database
- create a new table – using the sqlite create table statement
- enter data into our database – using sqlite
- 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
- You need your ER diagram
- Consider the data type (integer, text, date, etc.) of each table column
- The database data must match your database design
SQLite
- Download SQLite from https://sqlitebrowser.org/dl/
- 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
);
- The SQL statement ‘create table’ starts with the words ‘CREATE TABLE’
- Uppercase letters make it easier to see the SQL but not required
- follow the ‘create table’ with the actual table name
- Start the statement with an opening bracket ‘(‘
- Column names are listed
- use the order that the data will be entered
- 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)
);
- Each column must have a column name
- 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.
- 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)
);
- Add the text ‘PRIMARY KEY’ to the column, which is the primary key.
- It is normal to put the primary key column first
- 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)
);
student_id INT PRIMARY KEY,
name VARCHAR(255),
mark INT CHECK (mark >= 0 AND mark <= 100)
);
- We don’t have any constraints in the table above so here we have added an example column for student marks
- 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
- 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 )
);
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)
);
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