Day 1 - Exploring SQLite using Translation App's DB

To See All Articles About Technology: Index of Lessons in Technology

How to run SQLite on Windows?

To run SQLite on Windows, follow these steps:

---

1. Download SQLite

1. Go to the official SQLite website:
   🔗 https://www.sqlite.org/download.html
2. Scroll down to the "Precompiled Binaries for Windows" section.
3. Download "SQLite Tools for Windows" (ZIP file).
4. Extract the ZIP file to a folder (e.g., C:\sqlite).

---

2. Add SQLite to System PATH (Optional, for easier access)

1. Copy the extracted folder path (e.g., C:\sqlite).
2. Search for "Environment Variables" in Windows.
3. Under System Variables, find and edit the Path variable.
4. Click New and paste C:\sqlite.
5. Click OK to save changes.

Now, you can run sqlite3 from any command prompt.

---

3. Run SQLite

Option 1: Using Command Prompt

1. Open Command Prompt (cmd).
2. Navigate to the SQLite folder (if you didn't add it to PATH):

   sh
   cd C:\sqlite
   

3. Start SQLite by running:

   sh
   sqlite3
   

4. To open an existing database or create a new one:

   sh
   sqlite3 my_database.db
   

5. To check tables in the database:

   sql
   .tables
   

6. To exit SQLite:

   sh
   .exit
   

---

Option 2: Using SQLite GUI Tools (Optional)

If you prefer a graphical interface, you can download a GUI tool:

• DB Browser for SQLite: https://sqlitebrowser.org/

This tool allows you to easily create and manage SQLite databases without using the command line.

---

Option 3: Using SQLite with Python

If you want to interact with SQLite in Python, use the sqlite3 module:

python
import sqlite3

# Connect to a database (or create one)
conn = sqlite3.connect("my_database.db")

# Create a cursor
cursor = conn.cursor()

# Create a table
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);")

# Insert data
cursor.execute("INSERT INTO users (name) VALUES ('Alice');")
conn.commit()

# Query data
cursor.execute("SELECT * FROM users;")
print(cursor.fetchall())

# Close connection
conn.close()

---

Now you have SQLite running on Windows! 🎉 Let me know if you need more help. 🚀

Show all tables.

To show all tables in an SQLite database, use the following SQL query:

sql
SELECT name FROM sqlite_master WHERE type='table';

Explanation:

• sqlite_master: This is a system table in SQLite that stores information about all database objects (tables, indexes, views, triggers).
• WHERE type='table': Filters the results to show only tables (excluding indexes, views, etc.).

Alternative (Using .tables in SQLite CLI)

If you're using the SQLite command-line interface, you can simply run:

sh
.tables

This will list all the tables in the current database.

Let me know if you need help with something specific! 😊

Get table schema using SQL.

To get the schema of a table in SQLite, you can use the following SQL command:

sql
PRAGMA table_info(your_table_name);

Explanation:

• PRAGMA table_info(your_table_name): Returns details about the table structure, including:
  • Column ID (cid)
  • Column Name (name)
  • Data Type (type)
  • Nullable Constraint (notnull)
  • Default Value (dflt_value)
  • Primary Key Status (pk)

Example Usage:

sql
PRAGMA table_info(HindiToEnglishApp);

Output Example:

pgsql
cid | name       | type      | notnull | dflt_value | pk
----+-----------+----------+--------+------------+----
0   | id        | INTEGER  | 1      | NULL       | 1
1   | word      | TEXT     | 1      | NULL       | 0
2   | meaning   | TEXT     | 0      | NULL       | 0

---

Alternative: Using sqlite_master

To get the CREATE TABLE statement that defines a table's schema, use:

sql
SELECT sql FROM sqlite_master WHERE type='table' AND name='your_table_name';

Example:

sql
SELECT sql FROM sqlite_master WHERE type='table' AND name='HindiToEnglishApp';

Output Example:

sql
CREATE TABLE HindiToEnglishApp (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    word TEXT NOT NULL,
    meaning TEXT
);

This query retrieves the exact SQL statement that was used to create the table.

Let me know if you need more details! 😊

Viewing All Tables

Using sqlite_master table
sqlite> SELECT name FROM sqlite_master WHERE type='table';

attempted_hindi_to_eng_ques
attempt_log
ques_info
hindi_to_english_ques_bank
user_login


Using CLI

sqlite> .tables

attempt_log                  ques_info                  
attempted_hindi_to_eng_ques  user_login                 
hindi_to_english_ques_bank


Viewing the create statement for different tables

sqlite> SELECT sql FROM sqlite_master WHERE type='table' AND name='user_login';

CREATE TABLE user_login (
username text PRIMARY KEY,
password text NOT NULL
)

SELECT sql FROM sqlite_master WHERE type='table' AND name='ques_info';

CREATE TABLE ques_info (
pair_id INTEGER PRIMARY KEY,
chapter INTEGER NOT NULL,
title TEXT NOT NULL,
    subject TEXT not null,
grade INTEGER NOT NULL,
    FOREIGN KEY (pair_id) REFERENCES hindi_to_english_ques_bank (pair_id) 
)

sqlite> SELECT sql FROM sqlite_master WHERE type='table' AND name='hindi_to_english_ques_bank';

CREATE TABLE hindi_to_english_ques_bank (
pair_id INTEGER PRIMARY KEY,
hindi text NOT NULL,
english text NOT NULL UNIQUE
)

sqlite> SELECT sql FROM sqlite_master WHERE type='table' AND name='attempted_hindi_to_eng_ques';

CREATE TABLE attempted_hindi_to_eng_ques (
username text not null,
pair_id integer not null,
flag integer not null DEFAULT 0,
PRIMARY KEY (username, pair_id)
FOREIGN KEY (pair_id) REFERENCES hindi_to_english_ques_bank (pair_id)
)

SELECT sql FROM sqlite_master WHERE type='table' AND name='attempt_log';

CREATE TABLE attempt_log (
    attempt_time DATETIME NOT NULL,
    username TEXT NOT NULL,
pair_id INTEGER NOT NULL,
    result BOOLEAN NOT NULL,
    FOREIGN KEY (pair_id) REFERENCES hindi_to_english_ques_bank (pair_id) 
)

Viewing table data

sqlite> select * from ques_info;

1|1.1|Ram and Mango|English|1
2|1.1|Ram and Mango|English|1
3|1.1|Ram and Mango|English|1
4|1.1|Ram and Mango|English|1
5|1.1|Ram and Mango|English|1
6|1.2|To|English|1
7|1.2|To|English|1
8|1.2|To|English|1
9|1.2|To|English|1
10|1.2|To|English|1
11|1.3|Sense Organs|English|1
12|1.3|Sense Organs|English|1
13|1.3|Sense Organs|English|1
14|1.3|Sense Organs|English|1
15|1.3|Sense Organs|English|1
...

sqlite> select * from hindi_to_english_ques_bank;

1|छत पर मत चढ़|Do Not Climb On The Roof
2|राम आम खाता है|Ram Eats Mango
3|वह इंग्लिश पढ़ता है|He Reads English
4|राम ने आम खाया|Ram Ate Mango
5|राम आम खा रहा है|Ram Is Eating Mango
6|वे आम खाते हैं|They Eat Mango
7|राम द्वारा आम खाया जाता है|Mango Is Eaten By Ram
8|हाथ ताली बजाने के लिए|Hands To Clap
9|पैर चलने के लिए|Legs To Walk
10|नाक सूँघने के लिए|Nose To Smell
11|मुँह बातचीत के लिए|Mouth To Talk
12|सिर सोचने के लिए|Head To Think
13|आँखें देखने के लिए|Eyes To See
14|दो छोटे हाथ बजाएं ताली ताली ताली|Two Little Hands Go Clap Clap Clap
15|दो छोटे पैर बजाएं टैप टैप टैप|Two Little Legs Go Tap Tap Tap

Tags: Database,Technology,