SQLite DB directions.rtf

{\rtf1\ansi\deff0\nouicompat{\fonttbl{\f0\fnil\fcharset0 Calibri;}}
{\colortbl ;\red0\green0\blue255;}
{\*\generator Riched20 10.0.16299}\viewkind4\uc1 
\pard\sl240\slmult1\f0\fs22\lang9\par
+++++++++++++++++++++++++++++++++++++++++++++++++++++\par
{{\field{\*\fldinst{HYPERLINK https://www.tutorialspoint.com/sqlite/sqlite_create_table.htm }}{\fldrslt{https://www.tutorialspoint.com/sqlite/sqlite_create_table.htm\ul0\cf0}}}}\f0\fs22\par
\par
SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column's data type.\par
\par
Syntax\par
Following is the basic syntax of CREATE TABLE statement:\par

\pard\li720\sl240\slmult1 CREATE TABLE database_name.table_name(\par
   column1 datatype PRIMARY KEY(one or more columns),\par
   column2 datatype,\par
   column3 datatype,\par
   .....\par
   columnN datatype);\par

\pard\sl240\slmult1\par
CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally, you can specify database_name along with table_name.\par
Following is an example which creates a COMPANY table with ID as the primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.\par

\pard\li720\sl240\slmult1 sqlite> CREATE TABLE COMPANY(\par
   ID INT PRIMARY KEY     NOT NULL,\par
   NAME           TEXT    NOT NULL,\par
   AGE            INT     NOT NULL,\par
   ADDRESS        CHAR(50),\par
   SALARY         REAL);\par

\pard\sl240\slmult1\par
Let us create one more table, which we will use in our exercises in subsequent chapters.\par

\pard\li720\sl240\slmult1 sqlite> CREATE TABLE DEPARTMENT(\par
   ID INT PRIMARY KEY      NOT NULL,\par
   DEPT           CHAR(50) NOT NULL,\par
   EMP_ID         INT      NOT NULL);\par

\pard\sl240\slmult1\par
You can verify if your table has been created successfully using SQLite command .tables command, which will be used to list down all the tables in an attached database.\par

\pard\li720\sl240\slmult1 sqlite>.tables\par
COMPANY     DEPARTMENT\par

\pard\sl240\slmult1\par
Here, you can see the COMPANY table twice because its showing COMPANY table for main database and test.COMPANY table for 'test' alias created for your testDB.db. You can get complete information about a table using the following SQLite .schema command.\par

\pard\li720\sl240\slmult1 sqlite>.schema COMPANY\par
CREATE TABLE COMPANY(\par
   ID INT PRIMARY KEY     NOT NULL,\par
   NAME           TEXT    NOT NULL,\par
   AGE            INT     NOT NULL,\par
   ADDRESS        CHAR(50),\par
   SALARY         REAL);\par

\pard\sl240\slmult1\par
\par
=====================================================\par
\par
Importing a CSV file into a table using sqlite3 tool\par
        {{\field{\*\fldinst{HYPERLINK http://www.sqlitetutorial.net/sqlite-import-csv/ }}{\fldrslt{http://www.sqlitetutorial.net/sqlite-import-csv/\ul0\cf0}}}}\f0\fs22\par
\par
In the first scenario, you want to import data from CSV file into a table that does not exist in the SQLite database.\par
\par
FIRST SCENARIO, \par
   the sqlite3 tool creates the table. The sqlite3 tool uses the first row of the \par
    CSV file as the names of the columns of the table.\par
SECOND SCENARIO, \par
   the sqlite3 tool import data from the second row of the CSV file into the table.\par
   We will import a CSV file named city.csv with two columns: name and population. \par
\par
FIRST SCENARIO\par

\pard\li720\sl240\slmult1 First, set the mode to CSV to instruct the command-line shell program to interpret the input file as a CSV file. To do this, you use the .mode command as follows:\par

\pard\li1440\sl240\slmult1 sqlite> .mode csv\par

\pard\li720\sl240\slmult1 Second, use the .import FILE TABLE command to import the data from the city.csv file into the cities table.\par

\pard\li1440\sl240\slmult1 sqlite>.import c:/sqlite/city.csv cities\par

\pard\li720\sl240\slmult1 To verify the import, you use the .schema command to display the structure of the cities table.\par

\pard\li1440\sl240\slmult1 sqlite> .schema cities\par
CREATE TABLE cities(\par
  "name" TEXT,\par
  "population" TEXT);\par

\pard\li720\sl240\slmult1 To view the data of the cities table, you use the following SELECT statement.\par

\pard\li1440\sl240\slmult1 SELECT name, \par
       population\par
FROM cities;\par

\pard\sl240\slmult1\par
SECOND SCENARIO\par

\pard\li720\sl240\slmult1 In the second scenario, the table is already available in the database and you just need to import the data.\par
First, remove the cities table that you have created.\par

\pard\li1440\sl240\slmult1 DROP TABLE IF EXISTS cities;\par

\pard\li720\sl240\slmult1 Second, use the following CREATE TABLE statement to create the cities table.\par

\pard\li1440\sl240\slmult1 CREATE TABLE cities(\par
  name TEXT NOT NULL,\par
  population INTEGER NOT NULL);\par

\pard\li720\sl240\slmult1 If the table already exists, the sqlite3 tool uses all the rows, including the first row, in the CSV file as the actual data to import. Therefore, you should delete the first row of the CSV file.\par
The following commands import the city_without_header.csv file into the cities table.\par

\pard\li1440\sl240\slmult1 sqlite> .mode csv\par
sqlite> .import c:/sqlite/city_no_header.csv cities\par

\pard\sl240\slmult1\par
=====================================================\par
\par
To add the primary key to the cities table, you need to perform the script above:\par
\par
PRAGMA foreign_keys=off;\par
BEGIN TRANSACTION;\par
ALTER TABLE cities RENAME TO old_cities;\par
CREATE TABLE cities (\par
\tab id INTEGER NOT NULL PRIMARY KEY,\par
\tab name text NOT NULL);\par
\par
INSERT INTO cities SELECT * FROM old_cities;\par
DROP TABLE old_cities;\par
COMMIT;\par
PRAGMA foreign_keys=on;\par
\par
=====================================================\par
\par
\par
}