SQLite Database Tool Introduction and SQL Commands

Downloading SQLite

• Go to sqlite.org/download.
• Choose the appropriate download based on your operating system.
  • For Windows, click the third link under "Precompiled Binaries for Windows" to get a bundle of command-line tools and SQLite database files.
  • For macOS, click the zip file link.
  • For Linux, click the zip file link.
• Extract the downloaded file.
• The sqlite3.exe file is essential; the others are optional.

Understanding SQLite

• SQLite is a self-contained, zero-configuration, and serverless software package.
• Databases can be saved locally.

Setting Up SQLite

• Copy the SQLite files (including sqlite3.exe) to a dedicated folder (e.g., C:\sqlite).
• Add the SQLite folder to the system environment variables.
  • Go to "Edit the system environment variables" in Windows.
  • Under "Advanced," click "Environment Variables."
  • Edit the "Path" variable under "System variables" (preferred) or create a new "Path" variable under "User variables."
  • Add the path to the SQLite folder (e.g., C:\sqlite).
• Open Command Prompt and type sqlite3 to verify the installation.
• If SQLite is recognized, the version information will be displayed, indicating a connection to a transient in-memory database.
• Type .help to see available commands.
• Type .q or .quit to exit.

Creating a Permanent Database

• Navigate to the desired directory in Command Prompt using the cd command (e.g., cd C:\sqlite).
• Create a database using the command: sqlite3 myDB.db (replace myDB.db with your desired database name).
• The database file will be created in the specified directory.

Basic SQL Operations

• .tables: Shows existing tables in the current database.
• CREATE TABLE: Creates a new table.
  • Example: CREATE TABLE store (store_id INT, item_id INT, item_name TEXT);
• SQL commands are not case-sensitive but it is better to use uppercase for SQL commands to follow standards otherwise is case sensitive.
• It's good practice to end SQL commands with a semicolon.
• Data Types (5 major data types):
  • NULL: Represents a missing or undefined value.
  • INTEGER: Signed integer values of varying sizes (e.g., INT, SMALLINT, SIGNED INTEGER).
  • REAL: Floating-point values.
  • TEXT: Stores text strings.
  • BLOB: Stores binary data, such as images.

Inserting Data

• INSERT INTO: Inserts records into a table.
  • Example: INSERT INTO store (store_id, item_id, item_name) VALUES (1, 11, 'pen');
  • If inserting values for all fields, the field names can be omitted: INSERT INTO store VALUES (2, 22, 'pencil');

Selecting and Viewing Data

• SELECT * FROM: Retrieves all records from a table.
  • Example: SELECT * FROM store;
  • SELECT: Select specific columns from a tableSELECT item_name FROM Store;

Saving the Database

• .save: Saves the current database to a file.
  • Example: .save myDB.db

Customizing the Command Prompt

• Right-click the Command Prompt title bar and select "Properties" to change colors, fonts, and terminal settings.

Exiting SQLite

• .quit or .q: Exits the SQLite command-line tool.

Opening an Existing Database

• Using sqlite3 myDB.db or using dot open after connecting:
  First Method
  * sqlite3 myDB.db

  Second mehtod
  * sqlite3
  * .open myDB.db

Using the WHERE Clause for Conditional Selection

• SELECT * FROM table_name WHERE condition;
  • Example: SELECT * FROM store WHERE store_id = 1; (retrieves records where store_id is 1).

Displaying Headers

• .header on: Displays column headers in the output.
• .header off: Hides column headers.

Combining Conditions with AND and OR

• AND: Both conditions must be true.
  • Example: SELECT * FROM store WHERE item_name = 'pencil' AND item_id = 22;
• OR: At least one condition must be true.
  • Example: SELECT * FROM store WHERE item_name = 'pencil' OR store_id = 1;

The GROUP BY Clause

• Used to group rows with the same values in a column into summary rows, like counting occurrences.
• SELECT columnA, COUNT(columnB) FROM table_name GROUP BY columnA;

The ORDER BY Clause

• Sorts the result set based on one or more columns.
• SELECT * FROM table_name ORDER BY column_name; (sorts in ascending order by default).
• SELECT * FROM table_name ORDER BY column_name DESC; (sorts in descending order).

The BETWEEN Operator

• Selects values within a given range.
• SELECT * FROM invoice WHERE invoice_amount BETWEEN 1700 AND 6500;

The IN Operator

• Specifies multiple values in a WHERE clause.
• SELECT invoice_id FROM invoice WHERE invoice_id IN (10, 50); (equivalent to invoice_id = 10 OR invoice_id = 50).

The UPDATE Command

• Modifies existing records in a table.
• UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  • Example: UPDATE invoice SET invoice_name = 'test' WHERE invoice_id = 10 AND invoice_amount = 100;
• If the WHERE clause is omitted, all records in the table will be updated.

Viewing Table Schema

• .schema: Displays the SQL schema of all tables.
• .schema table_name: Displays the schema of a specific table.

Date Functions

• Refer to sqlite.org/lang_datefunc.html for a complete list of date and time functions.
• SELECT date('now'); (returns the current date in GMT).
• SELECT time('now'); (returns the current time in GMT).
• SELECT datetime('now'); (returns the current date and time in GMT).

Constraints

• Rules enforced on data columns to ensure data integrity.
  • Primary Key: A column or set of columns with unique and non-null values that identify each row in a table. Only one primary key is allowed per table.
  • NOT NULL: Ensures that a column cannot contain NULL values.
  • Unique: Forces a column to contain unique values.
  • Default: Specifies a default value for a column when no value is provided during insertion.
  • Check: Defines a condition that each row must satisfy.

Primary Key Constraint

• Example: CREATE TABLE student (sid INTEGER PRIMARY KEY, sname TEXT, phone TEXT, email TEXT);.
• Primary keys must be unique and cannot be NULL.

NOT NULL Constraint

• Example: CREATE TABLE student_not_null1 (store_id INT PRIMARY KEY, store_name TEXT NOT NULL);.
• Distinction between no value and NULL values is important.

Unique Constraint

• Forces a column to contain only unique values.
• Example: CREATE TABLE t1 (tid INTEGER UNIQUE, tname TEXT);

Default Constraint

• Assigns a default value to a column if no value is specified during insertion.
• Example: CREATE TABLE t2 (tid INTEGER UNIQUE NOT NULL, tname TEXT DEFAULT 'unknown');

Check Constraint

• Defines a condition that data must satisfy.
• Example: CREATE TABLE t3 (t_amount REAL CHECK(t_amount > 900));

Altering Tables

• ALTER TABLE: Modifies the structure of an existing table.
  • ALTER TABLE table_name ADD column_name data_type;. Adds a new column.
  • ALTER TABLE table_name RENAME COLUMN old_name TO new_name; Renames a column.
  • ALTER TABLE table_name RENAME TO new_table_name;. Renames a table.

Dropping Tables

• DROP TABLE table_name;. Permanently deletes a table and its data.

Deleting Records

• DELETE FROM table_name WHERE condition;- Used to delete rows from the table.
• DELETE FROM table_name;. Deletes all records from a table. The table structure remains.

Backing Up a Database

• .backup filename.db. Creates a backup of the current database.

Restoring a Database

• .restore backup_filename.db. Restores a database from a backup file.

Changing Output Modes

• .mode: Controls how query results are displayed.
• Refer to The SQLite website (sqlite.org/cli.html) for different CLI commands.
• .mode csv: Sets output to CSV format (comma-separated values).
• .mode insert: Displays output as INSERT statements.
• .mode tabs: Uses tab characters as separators.
• .mode column: Formats output in columns.
• .mode list: Sets the default output mode (pipe-separated values).

Saving the Database Explicitly

• It's recommended to explicitly save the database using .save myDB.db
  After saving the backup you can check the result using dir

Database Tools

• DBeaver (dbeaver.io): A database tool that provides a GUI for managing databases.
• DB Browser for SQLite (sqlitebrowser.org): Another GUI tool for managing SQLite databases.
• Visual Studio Code: Can be used to run SQLite with appropriate extensions.