Opening SQLite
C:\Users\adi >sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
Opening Database and Tables
sqlite> .databases
main: "" r/w
sqlite> .open mydb.db
sqlite> .tables
STORE bank_account
Crea ng Tables
sqlite> CREATE TABLE Students_detail (
(x1...> student_id INTEGER PRIMARY KEY AUTOINCREMENT,
(x1...> name TEXT NOT NULL,
(x1...> age INTEGER CHECK (age > 0),
(x1...> gender TEXT CHECK (gender IN ('M', 'F', 'O')),
(x1...> email TEXT UNIQUE NOT NULL,
(x1...> course TEXT NOT NULL,
(x1...> gpa REAL
(x1...> );
Crea ng Triggers
sqlite> CREATE TRIGGER validate_students_details
...> BEFORE INSERT ON Students_detail
...> FOR EACH ROW
...> BEGIN
...> SELECT CASE
...> WHEN NEW.gpa<0.0 AND NEW.gpa>10.0
...> THEN RAISE (ABORT, 'GPA must lie between 0 to 10')
...> END;
...> SELECT CASE
...> WHEN NEW.email NOT LIKE '%_@_%'
...> THEN RAISE (ABORT, 'Invalid email format')
...> END;
...> END;
Inser ng Values into the Table
sqlite> INSERT INTO Students_detail (student_id, name, age, gender, email, course, gpa)
...> VALUES (1, 'Adi ', 20, 'F', 'adi @gmail.com', 'DSA', 8.3);
sqlite> INSERT INTO Students_detail (name, age, gender, email, course, gpa)
...> VALUES ('Diksha', 21, 'F', 'diksha@gmail.com', 'ML', 7);
sqlite> INSERT INTO Students_detail(name, age, gender, email, course, gpa)
...> VALUES ('Purvi', 20, 'F', 'purvi@gmail.com', 'ML', 7);
Output Table
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM Students_detail;
student_id name age gender email course gpa
-------------- -------- ------ --------- ---------------------------- -------- -----
1 Adi 20 F adi @gmail.com DSA 8.3
2 Diksha 21 F diksha@gmail.com ML 7.0
3 Purvi 20 F purvi@gmail.com ML 7.0
Inpu ng wrong values to check the results of trigger
sqlite> INSERT INTO Students_detail(name, age, gender, email, course, gpa)
...> VALUES ('Diksha', 21, 'F', 'dikshagail.com', 'ML', 7);
Run me error: Invalid email format (19)
sqlite> INSERT INTO Students_detail(name, age, gender, email, course, gpa)
...> VALUES ('Diksha', 0, 'F', 'diksha@gmail.com', 'ML', 7);
Run me error: CHECK constraint failed: age > 0 (19)
Upda ng Table
sqlite> UPDATE Students_detail SET gpa=9 WHERE name='Purvi';
sqlite> UPDATE Students_detail SET course='Web3' where name like 'P%';
Selec ng specific features
sqlite> SELECT MAX(gpa) FROM Students_detail;
9.0
sqlite> SELECT MIN(gpa) FROM Students_detail;
7.0
sqlite> SELECT SUM(gpa) FROM Students_detail;
24.3
sqlite> SELECT AVG(gpa) FROM Students_detail;
8.1
sqlite> SELECT UPPER(name) FROM Students_detail;
ADITI
DIKSHA
PURVI
Ordering
sqlite> SELECT * FROM Students_detail ORDER BY gpa;
student_id name age gender email course gpa
---------- ------ --- ------ ---------------- ------ ---
2 Diksha 21 F diksha@gmail.com ML 7.0
1 Adi 20 F adi @gmail.com DSA 8.3
3 Purvi 20 F purvi@gmail.com Web3 9.0
sqlite> SELECT gpa FROM Students_detail ORDER BY gpa desc;
9.0
8.3
7.0
Impor ng dataset in CSV format
sqlite> .mode csv
sqlite> .import C:\Users\adi \Downloads\data.csv db1
sqlite> .schema db1
CREATE TABLE IF NOT EXISTS "db1"(
"Car" TEXT, "Model" TEXT, "Volume" TEXT, "Weight" TEXT,
"CO2" TEXT);
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM db1;
Car Model Volume Weight CO2
---------- ---------- ------ ------ ---
Toyoty Aygo 1000 790 99
Mitsubishi Space Star 1200 1160 95
Skoda Ci go 1000 929 95
Fiat 500 900 865 90
Mini Cooper 1500 1140 105
