- A Database Management System (DBMS) is specialized software crafted to handle extensive datasets efficiently and systematically.
- SQL, which stands for Structured Query Language, serves as a standardized language primarily employed for interacting with Database Management Systems (DBMS).
- SQLite is a C library that offers a compact, disk-based database solution, eliminating the need for a separate server process.
- A database consists of one or more tables, each of which holds a collection of related data. These tables organize data into rows and columns.
- A primary key is a column or set of columns that uniquely identifies each row in a table. The primary key constraint ensures that the values in this column (or columns) are unique and not null.
- SQLite database follows a standard procedure as follows:
- Establish a connection with the database.
- Retrieve a cursor for the database.
- Execute operations on the database.
- Apply changes to the database.
- Terminate the connection to the database.
- To create a table in an SQLite database, utilize the SQL command CREATE TABLE.
- To delete a table in an SQLite database, utilize the SQL command DROP TABLE.
- The SELECT statement is employed to fetch specific rows from a table.
- The WHERE clause, when utilized with the SELECT statement, enables the specification of search criteria.
- within the WHERE clause, relational operators such as >, <, >=, <=, ==, =, !=, and <> are employed, alongside logical operators like AND, OR, and NOT.
- The LIKE operator facilitates searching for substrings. The % symbol functions as a wildcard for multiple characters.
- This SELECT statement with ORDER BY clause will fetch all rows from the table. The sorting will be in ascending order based on the column.
- 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.
- The UPDATE statement modifies the contents of an existing row within a table.
- The DELETE statement removes one or more rows from a table.
- Combining two or more columns to form a composite key is possible.
- In relational databases, columns in one table often relate to columns in other tables through associations. Relational database design aims to minimize redundant data.
- A foreign key (FK) is a column within one table that refers to the primary key in another table.