a) Create a table
To create a table in an SQLite database, utilize the SQL command CREATE TABLE.
| CREATE TABLE TableName ( ColumnName1 DataType1, ColumnName2 DataType2, ... ) |
| CREATE TABLE Students (Name TEXT, Email TEXT, GPA REAL) |
To specify a column as the primary key, apply the PRIMARY KEY constraint following the column's data type.
| CREATE TABLE Students (ID INTEGER PRIMARY KEY, Name TEXT, Email TEXT, GPA REAL) |
The NOT NULL constraint ensures that a column must contain a value and cannot be null.
| CREATE TABLE Students (ID INTEGER PRIMARY KEY NOT NULL, Name TEXT, Email TEXT, GPA REAL) |
For example,
b) Delete a table
| DROP TABLE STUDENTS DROP TABLE IF EXISTS Students |
c) Add data to a table
To insert data into a table, utilize the INSERT statement.
| INSERT INTO TableName (ColumnName1, ColumnName2, ...) VALUES (Value1, Value2, etc...) |
| INSERT INTO Students (ID, Name, Email, GPA) VALUES (1, "Tony Stark", "Tony.Stark@mga.edu", 3.88) |
For example,
You can incorporate the value of a variable into an SQL statement by employing question mark placeholders.
| Name = "Black Widow" Email = "Black.Widow@mga.edu" cur.execute('''INSERT INTO Inventory (Name, Email, GPA) VALUES (?, ?, ?)''', (Name, Email, GPA)) |
- Search data with SELECT statement
The SELECT statement is employed to fetch specific rows from a table.
| SELECT Columns FROM Table |
There are three ways to retrieve data from a table.
- Retrieves the Name column from each row in the Students table.
- Multiple column names are separated by commas. This query retrieves the Name and Email columns from each row in the Students table.
- The asterisk (*) symbol can be employed to fetch all columns in the table.
After executing SELECT statement, fetch the results from a table using fetchall method or fetchone method.
(Output)
| Tony Stark Steve Rogers Black Widow |
(Output)
| Tony Stark Tony.Stark@mga.edu Steve Rogers Steve.Rogers@mga.edu Black Widow Black.Widow@mga.edu |
(Output)
| 1 Tony Stark Tony.Stark@mga.edu 3.88 2 Steve Rogers Steve.Rogers@mga.edu 3.95 3 Black Widow Black.Widow@mga.edu 3.52 |
e) SELECT statement with WHERE clause
The WHERE clause, when utilized with the SELECT statement, enables the specification of search criteria. Consequently, only the rows that satisfy the search criteria will be included in the result set.
| SELECT Columns FROM Table WHERE Criteria |
The first segment of the statement, SELECT * FROM Students, indicates the retrieval of all columns.
The WHERE clause dictates that only rows where the ID column is less than or equal to 2 should be included.
(Output)
| 1 Tony Stark Tony.Stark@mga.edu 3.88 2 Steve Rogers Steve.Rogerse@mga.edu 3.95 |
f) SELECT statement with WHERE clause using relational operators and logical operators
Additionally, within the WHERE clause, relational operators such as >, <, >=, <=, ==, =, !=, and <> are employed, alongside logical operators like AND, OR, and NOT.
g) SELECT statement with WHERE clause using LIKE operator
The LIKE operator facilitates searching for substrings. The % symbol functions as a wildcard for multiple characters. The following statement retrieves all rows where the Email column contains the string "Tony" preceded by any characters and followed by any characters.
(Output)
| 2 Steve Rogers Steve.Rogers@mga.edu 3.95 |
h) SELECT statement with ORDER BY clause
This statement will fetch all rows from the Students table, sorted by the Email column. The sorting will be in ascending order based on the Email column.
(Output)
| 3 Black Widow Black.Widow@mga.edu 3.52 2 Steve Rogers Steve.Rogers@mga.edu 3.95 1 Tony Stark Tony.Stark@mga.edu 3.88 |
- Aggregate Functions
- The AVG function computes the average value within a specified column.
- The SUM function calculates the total sum of values within a column.
- The MIN and MAX functions ascertain the minimum and maximum values present in a column.
- The COUNT function is utilized to determine the total number of rows in a table.
(Output)
| Average GPA 3.783333333333333 SUM GPA 11.35 MIN GPA 3.52 MAX GPA 3.95 COUNT 3 |
i) Update rows
The UPDATE statement modifies the contents of an existing row within a table.
| UPDATE Table SET Column = Value WHERE Criteria |
This example updated all rows in the Students table where the Name is "Black Widow". For each of these rows, it set the GPA column from 3.52 to 3.75.
(Output)
| 1 Tony Stark Tony.Stark@mga.edu 3.88 2 Steve Rogers Steve.Rogers@mga.edu 3.95 3 Black Widow Black.Widow@mga.edu 3.75 |
(Be careful!)
Always include the WHERE clause unless you intend to update every row in the table! For instance, this SQL statement sets the GPA column to 0 for every row in the Students table:
| UPDATE Students SET GPA = 0 |
k) Delete rows
The DELETE statement removes one or more rows from a table.
| DELETE FROM Table WHERE Criteria |
This statement removed all rows from the Products table where the Name is "Steve Rogers".
(Output)
| 1 Tony Stark Tony.Stark@mga.edu 3.88 3 Black Widow Black.Widow@mga.edu 3.75 |
(Be careful!)
Always include the WHERE clause unless you intend to delete every row in the table! For instance, this SQL statement deletes every row in the Students table:
| DELETE FROM Students |
l) Composite Keys
Combining two or more columns to form a composite key is possible. This approach is sometimes necessary to ensure unique values in the primary key.
| CREATE TABLE Students (MGA_ID INTEGER NOT NULL, Name TEXT NOT NULL, Email TEXT, PRIMARY KEY(MGA_ID, Name)) |
m) Handing Exceptions
This code attempts to establish a connection to the SQLite database specified by DatabaseName. Inside the try block, you can perform various database operations. If there's a SQLite-specific error, it's caught in the first except block, and if there's a general error, it's caught in the second except block. Finally, the connection is closed in the finally block to ensure it's properly closed regardless of whether an exception occurred or not.