Data manipulation through SQL - Formula Sheet

SQL Syntax and Query Structures

In database manipulation with SQL, syntax templates act as formulas. Below are the key statements and functions used with SQLite in Python:

1. Database Connection and Cursor Initialization

connection = sqlite3.connect(database_name)

• database_name: The file path of the database on disk or the keyword ':memory:' for RAM-based storage.

cursor = connection.cursor()

• cursor: The control structure used to execute statements and fetch results.

2. Database Schema Creation

CREATE TABLE table_name (column_1 data_type constraints, column_2 data_type, ...);

• table_name: Unique name of the relation.
• column_1, column_2: Names of individual fields.
• data_type: Data types such as INTEGER, VARCHAR, DECIMAL, DATE, float, etc.
• constraints: Restrictions such as PRIMARY KEY, NOT NULL, UNIQUE, CHECK, or DEFAULT.

3. Data Insertion Formula

INSERT INTO table_name (column_list) VALUES (value_list);

• column_list: Optional explicit list of destination columns.
• value_list: Commas-separated literals mapped to the respective columns.

4. Aggregate Functions