Listado de la etiqueta: SQLite

Constraints are said to be limitations or restrictions that are applied to specific columns of a table. Sometimes, it refers to the extra privileges that are assigned to the particular columns. One of that constraints is NOT NULL constraints in SQL database. Whichever column has been specified with the NOT NULL constraint, that column cannot be left without a value. Thus, we have decided to cover the use of the NOT NULL constraint within the SQLite database while implementing this article on Ubuntu 20.04. Before going to the illustration of using the NOT NULL constraint in the SQLite database, we have to open the Ubuntu terminal via the Ctrl+Alt+T instruction and update and upgrade our system using the shown-below instruction.

Make sure to have SQLite C-library of SQL already installed on your Linux system. After that, you need to launch it within the shell terminal with the use of the keyword “sqlite3”. The static shell will be opened within the shell of Ubuntu 20.04 for the SQLite database.

Let’s list all the tables that are found in the SQLite database. Thus, we will be trying the “.tables” instruction to do so. The Sqlite3 database doesn’t contain any database yet (i.e. according to the “.tables” instruction.)

The constraints can only be applied to the columns of a table. If we don’t have any table, then we don’t have any columns. Thus no constraints. Therefore, we have to create a table in the database on which we can apply the NOT NULL constraint. So, the database allows us to use the CREATE TABLE instruction to create one table with the name “Test”. This table will contain a total of 2 columns “ID” and “Name”. The column ID will be of integer type and will be used as a primary key for the table. The “Name” column will be of text type and must not be Null as per the use of the NOT NULL constraint specified at the time of creating a table. Now, we have a new table “Test” in the database as per the “.tables” instruction.

The use of SELECT instruction to fetch the records of a Test table is showing that the table is empty right now. So, we need to add some records to it first.

We will be using the SQL’s INSERT INTO instruction followed by the name of a table and its column to insert the data records within its columns. You need to add records after the “VALUES” keyword followed by the simple brackets holding a total of 10 records. No record has been specified NULL for the column “Name” so far as presented below.

INSERT INTO Test(ID, Name) VALUES (1, «George»), (2, «Bella»), (3, «Paul»), (4, «Johny»),

(5, «Ketty»), (6, «Angelina»), (7, «Nina»), (8, «Dilraba»), (9, «Tom»), (10, «Tyalor»);

INSERT INTO Test(ID, Name) VALUES (11, «»), (12, «»);

INSERT INTO Test(ID, Name) VALUES (11), (12);

INSERT INTO Test(ID, Name) VALUES (13, NULL), (14, NULL);

After inserting the records into the Test table, we have tried the SELECT instruction to display all the data on our SQLite shell. It displayed 10 records for the ID and Name column.

Let’s see how the NOT NULL constraint reacts to the spaces and NULL keyword while inserting data into column “Name” of the Test table. So, we have used the empty value “” the place of the “Name” column within the VALUES part of the INSERT INTO instruction. The record has been successfully added to the table Test. After using the SELECT instruction on the shell, we have found that it has been displaying nothing for column “Name” at records 11 and 12 and taking space as a NOT NULL value.

If you try the INSERT INTO instruction with the use of column names that contain a constraint NOT Null and doesn’t add the value for a particular column, then it will throw an error: “1 values for 2 columns” as presented below. To remove this error, you need to put a value for the “Name” column and do not leave it empty.

Let’s put the NULL Keyword within the VALUES part of the INSERT INTO instruction to add the null records for the column “Name” that contains the NOT NULL constraint. Execution of this instruction with the NULL keyword is throwing an error “NOT NULL constraint failed: test.Name”. This means we cannot put NULL as a value to the column “Name” due to its NOT NULL constraint restriction.

Let’s take a look at another example. So, we have created a new table Actor with the three columns ID, Name, and Age via the CREATE TABLE instruction. None of the columns contains a NOT NULL constraint on it.


Right now the table Actor is empty as per the SELECT “*” instruction below.

We have to put some records in the table “Actor” first with the INSERT INTO instruction. So, we have added 5 records for all three columns: ID, Name, and Age.

>> INSERT INTO Actor(ID, Name, Age) VALUES (1, «Julia», 49), (2, «Angelina», 49),

(3, «Leonardo», 50), (4, «Tom», 55);

We have tried the SELECT instruction to fetch all the newly added records of an “Actor” table. A total of 5 records have been displayed on our screen with no NULL values.

Let’s add a new record within the Actor table that contains a NULL value using the INSERT INTO instruction. So, we have tried the instruction for all the 3 columns and added a NULL value for the column “Age”. The record has been successfully inserted in the table and didn’t throw any error because we haven’t set any NOT NULL constraint for any of the columns of a table “Actor”. The use of SELECT instruction for the table Actor has been displaying all the first 5 added records and the 6th record with a NULL value at its column “Age”.

>> INSERT INTO Actor(ID, Name, Age) VALUES (1, «Ema Watson», NULL)
<blockquote>> SELECT * FROM Actor;

So, this was about the use of NOT NULL constraints for specific columns of SQLite tables. We have demonstrated how the database reacts to not putting the values within the columns with NOT NULL constraints and how we can use the NULL value with the column values.

Source link

“Transactions are logically organized blocks or sequences of work that can be done manually or mechanically by a DBMS. If you create, amend, or remove data from the table, you are performing transactions on the table. Controlling transactions is critical for maintaining data integrity and dealing with database issues.

In SQLite, a transaction is a group of T-SQL commands that execute as a unique T-SQL command. If an error occurs while performing these SQLite commands, the transaction will be rolled back in its entirety. In universal, SQLite is in auto-commit mode, which means it automatically creates a transaction with each command, processes it, and commits the changes to the database.

In this article, we will demonstrate how to implement the SQLite transaction to assure the data’s integrity and reliability.”

Properties of SQLite Transaction

SQLite is a transactional database, which means that all updates and searches are atomic, consistent, isolated, and long-lasting (ACID). The four standard features stated below, which are commonly abbreviated as ACID, are present in transactions.

Atomicity: A single step of the process is required to be completed by an atomic transaction. It denotes that a modification cannot be split down into smaller components. Either the complete transaction is executed or not when a transaction is in COMMIT mode.

Consistency: Transactions must guarantee that the database is changed from one legitimate state to the next. The database gets inconsistent when a transaction initiates and executes a command to update data. However, the database must remain consistent when COMMIT and ROLLBACK are performed on the transactions.

Isolation: The session’s pending transaction must be segregated from other sessions. When a session initiates a transaction and uses the INSERT or UPDATE command to modify data, the modifications are only accessible to the current operation and not to others. The changes made by other users after the transaction started, on the other hand, should never be apparent to the current user.

Durability: If a transaction is properly committed, the changes in the database must be permanent in the event of a power outage or a program crash. The update should not persist if the program terminates first before the transaction is committed.

How to Perform Transactions in SQLite?

Suppose we wish to regulate such transactions to retain data consistency and handle database failures. We can stop auto-commit mode and initiate the transactions explicitly based on our needs by using the following instructions.

  • BEGIN: This is the point where the transaction is started.
  • COMMIT: With this terminology in SQLite, we will commit the transaction, which means it will save all the modifications to the database.
  • ROLLBACK: The transaction as a whole will be reversed.

Note that only the DML operations INSERT, UPDATE, and DELETE use transactional control instructions. They can’t be used for extracting tables since the database promptly commits these operations.

Creating SQLite Tables for Performing Transactions

To perform transactions, first, we have to create a table. In the figure, you can notice that we have created a table with the name “person_accounts.” The table is specified with the four columns as person_id, person_name, account_number, and the account_balance with their data types.

CREATE TABLE person_accounts(
      person_id INT PRIMARY KEY    ,
      person_name           CHAR(10)   ,
      account_number          INT  ,
      account_balance         FLOAT  

Here, we have a record inserted with the SQLite insert command.

INSERT INTO person_accounts VALUES (1, ‘Ayat’, 171636460, 5000);
INSERT INTO person_accounts VALUES (2, ‘Muneeb’, 673201984, 8000 );
INSERT INTO person_accounts VALUES (3, ‘Wahaj’,112603502, 4000);
INSERT INTO person_accounts VALUES (4, ‘Maya’, 501738449, 7500);

The table is viewable in the table format as follows:

Example 1: Perform Transaction With BEGIN Command in SQLite

BEGIN TRANSACTION, or the BEGIN command, can be used to start transactions. However, if the database is terminated or an error occurs, a transaction will ROLLBACK.

Initially, we have included a BEGIN terminology with the TRANSACTION term. Then, we have the UPDATE command, which is operating on the account_balance column where person_id is equal to “2” along with the person_id equal to “4”. As we have given a COMMIT instruction, the transaction terminates here.


UPDATE person_accounts
   SET account_balance = account_balance 1000
 WHERE person_id = 2;

UPDATE person_accounts
   SET account_balance = account_balance + 500
 WHERE person_id = 4;

The translation is performed successfully on the table “person_accounts,” and the “account_balance” field is updated on the specified person_id.

Example 2: Perform Transaction on COMMIT Command in SQLite

The COMMIT command is a transactional command that saves changes to the database triggered by a transaction. The COMMIT command preserves all database transactions since the previous COMMIT or ROLLBACK instruction.

In the following example, we have the first start of the transaction with the term BEGIN TRANSACTION. After this, we have the insert command, which inserts a new record in the table “person_accounts.” We have given a COMMIT command in the end, which terminates the transaction here and saves the change in the given table.

INSERT INTO person_accounts(person_id,person_name,account_number,account_balance)
VALUES(3, ‘Wahaj’,112603502, 4000);


The view of the table shows the new record in the following table.

Example 3: Perform Transaction on ROLLBACK Command in SQLite

ROLLBACK is a transactional command that is used to unwind transactions that haven’t been committed to the database yet.

Here, we have performed the DELETE operation on the table “persons_accounts,” where it matched the condition on the given account_number field. After this, we have given ROLLBACK instructions that will also terminate the transaction here but not save the changes we have performed on the table.

DELETE FROM person_accounts WHERE account_number= 112603502;
DELETE FROM person_accounts WHERE account_number= 171636460;

After performing the ROLLBACK operation, the table has the same values and fields existing in the table as before.


After reading this article, we hope you have a clear grasp of SQLite Transactions. We talked about SQLite’s transaction properties and transaction control. Also implemented several SQLite examples describing transaction behavior with the COMMIT and ROLLBACK functionalities. That’s everything there is to know about SQLite transactions, including COMMIT and ROLLBACK transaction information.

Source link

“SQLite provides the user with various tools like the describe command, which allows us to inspect the table’s precise structure. In the SQLite database, the .schema query is used to define the tables; it is a control program, and we must follow specific procedures if we need an effect of the table at that moment. Essentially, describing the table implies printing information about each column, such as the column name, data type, and size. There are numerous methods in SQLite for retrieving data from a table.”

How to Display the Structure of a Table

Let’s have a glance at the description table command in action. In SQLite, the schema command operates as follows. In most cases, the table can be described in four ways. We have shown the syntax of each method through which we can view the structure of the table.


# PRAGMA table_info(table name)
This command returns a single row from each column in the defined table, similar to the SQL DESCRIBE command.

# PRAGMA table_xinfo(table name)
This method is similar to the previous statement. The only distinction is that it provides the imaginario table’s hidden column.

 # .schema table name
This operation represents the selected table; however, we must first build the table before using it.

# Sqlite_master Table
Another method of showing the table’s structure is by using the SQLite master table is this one. Each SQLite database’s schema is kept in a unique “schema table.” The schema is a visual representation of all the database’s records, collections, triggers, and views.

SQLite Table Creation for Performing the Method That Shows the Structure of the Table

Let’s peek at several alternative ways of describing tables. To begin, we’ll use the create table command to construct a table.

We have used below the create command for creating a table and defined a table name as “pet.” We have specified some fields with the data types inside the pet table. The pet table fields include pet_id, pet_name, pet_price, pet_gender, and pet_stock with the different data types as per their requirement.

Then, we have added some values against each field of the table pet by using the SQLite insert command. You can add the values with the insert command at merienda, but we have inserted them one by one.

Example 1: Using PRAGMA table_info() Method to Show the Table’s Structure

The PRAGMA table info() function returns a single entry on every field in the table given. It’s similar to MySQL’s DESCRIBE statement in SQLite. We have used a PRAGMA table_info() command here and passed a table name “pet” to it.

>> PRAGMA table_info(pet);

When we execute the above-mentioned command, the SQLite shell represents the pet table structure like in the figure. Here, it shows the field name and the data type which we have assigned to them explicitly. Against each field, we can see two more columns. The first column shows the not null values, and the second column displays the primary key value as the pet_id is the primary key of the table pet, so there is a 1 in the primary key column.

Example 2: Using PRAGMA table_xinfo() Method to Show the Table’s Structure

The PRAGMA table xinfo() statement is identical to the PRAGMA table info() function, except that it provides hidden fields on imaginario tables as well. This is the option to utilize if you wish to inspect a imaginario table’s structure, including any concealed columns. We have passed a table name pet to the following command.

>> PRAGMA table_xinfo(pet);

We can see the results of the PRAGMA table_info(pet) command, as the fourth column is of the hidden table, but we haven’t hidden any column in the table pet, so all the values are zeros, and it’s not a imaginario table.

If we want to see the table structure vertically, then we have to give the .mode line command in the MongoDB shell. Then, specify the command “PRAGMA table_xinfo()” in the shell.

>> .mode line
>> PRAGMA table_xinfo(pet);

Results of using .mode line showing the structure of table pet vertically.

Example 3: Using the .schema Command Method to Show the Table’s Structure

The schema command is another approach for getting the structure of a table. This is one of the several techniques to get the SQL that was used to construct the table. Here’s another example that uses the same table as the last one.

We have given a command .schema with the table name pet, which shows the structure of the table we have created above in the following cases.

As you can see, the schema of the table pet is the same as the time of creation and setting of the table pet field. As seen in the following diagram, the .schema command produces the result.

Example 4: Using the sqlite_master Table Method to Show the Table’s Structure

All SQLite databases have a table called SQLite master. This table’s content explains the schema of the database. As in the previous example, we can perform the same approach with the SQLite master table. Well, here’s an example, each time utilizing the same table.

We have included a select command from the sqlite_master table as shown. Then we have a WHERE clause to which we have specified a table name pet to an object tbl_name.

    FROM sqlite_master
    WHERE tbl_name= ‘pet’;

The output of the overhead query is demonstrated in the next snapshot.


We hope that after understanding this artifact, you have a better understanding of how SQLite represents table structures. We learned the different syntax of SQLite for viewing the table and saw several examples of SQLite describing the table structure in the recent article. We also learned how to describe tables using SQLite rules. We learned how to utilize the SQLite, describe the table in this post, and when to use it.

Source link

Memory classes are a set of five rudimentary sorts of data provided by SQLite. BLOB lets you supply files, photos, and additional media assets in a central database using a web browser. BLOB stands for “Binary Large Object”. It is a type of database with a capacity for storing an unlimited amount of data and has been used to store all types of records or information. This article will look at the use of Blob type in our table records.

Let’s get started with this article by logging in and then opening the terminal of Ubuntu 20.04. After opening it with Ctrl+Alt+T, we need to use the “update” instruction via the “apt” package to make our system up-to-date with new functionalities. To continue this process, it may require the password of a logged-in user. Add your password and continue.

After a successful update, we need to upgrade our system using the upgrade instruction with the apt package followed by the password for the currently logged-in user.

Meanwhile, the process may require your permission to continue upgrading the system as it requires 508 MB of space on the system.

After a successful update and upgrade, we need to launch the sqlite3 shell, i.e., C-module for SQL database on our terminal shell using the “sqlite3” instruction. The SQLite database shell will be generated, and we can now add commands to it.

Before looking at the BLOB type of SQLite database, we will first look at the most common and most used types of SQLite databases. For this, we will utilize the typeof() function within the SELECT instruction and the value inserted in the function parameters. So, we have been starting our first illustration with the use of SELECT instruction for the NULL value in the typeof() function parameters. The execution of this instruction has been returning the type null for a value “Null”.

After, we used the value “50” within the parenthesis of a typeof() function to find the type of a value using the SELECT instruction. It returns the type “integer” for the value 50. After that, we tried the value “10.9” within the typeof() function. This SELECT instruction displayed that the value 10.9 is a vivo number. The character “A” has been used with the single and double inverted commas within the next two SELECT queries inside the typeof() function. These two SELECT instructions return that the value “A” is text.

After this, we used the number 100 within the single inverted commas to determine its type using the typeof() function. It turns out that the value 100 within the inverted commas is taken as a text type. Let’s use the BLOB type value in the typeof() function within the SELECT instruction of the SQLite database. The BLOB value must contain the character “x” before starting a single quote, followed by the four character values. We have been using three commands to use the BLOB type values within the typeof() function. We have tried the first two values with the hacienda “X” character and the last value with the small “x” character in the instruction. The output for the previous three commands shows the type ‘blob’ for the three values.

sqlite> SELECT typeof(NULL);

sqlite> SELECT typeof(50);

sqlite> SELECT typeof(10.9);

sqlite> SELECT typeof(«A»);

sqlite> SELECT typeof(‘A’);

sqlite> SELECT typeof(‘100’);

sqlite> SELECT typeof(X‘3100’);

sqlite> SELECT typeof(X‘1000);

sqlite> SELECT typeof(x’1000‘);

Now, let’s see how we can add BLOB-type data within the SQLite table of Ubuntu 20.04. First, we just need a table in our database. So, we have been creating the table “Test” with the CREATE TABLE command. This table will contain two columns, i.e., ID and Types. The ID column will contain Integer type data and will be a primary key as specified.

After creating a table, we inserted a total of six records within it using the INSERT INTO instruction, followed by the table’s name. The first record contains the integer, the second contains the vivo value, the third and 4th contain the text type value, the 5th record contains the NULL, and the last record includes the BLOB-type value. After adding these records, we have tried to fetch all columns of a table “Test” along with the use of the “typeof()” function on the “Types” function to show the types. It turns out that the records have been displayed in the previously explained format.


sqlite> INSERT INTO TEST(Types) VALUES (10);

sqlite> INSERT INTO TEST(Types) VALUES (10.99);

sqlite> INSERT INTO TEST(Types) VALUES (‘678’);

sqlite> INSERT INTO TEST(Types) VALUES (99.99);


sqlite> INSERT INTO TEST(Types) VALUES (X‘9999’);

sqlite> SELECT ID, Types, typeof(Types) FROM Test;

You can also organize the table according to your choice using the ORDER BY clause and the name of a column that will maintain equality using the “Types” function.

sqlite> SELECT ID, Types, typeof(Types) FROM Test ORDER BY Types;

Within the previous example, we have displayed the BLOB-type value in the last part. Use the INSERT INTO instruction again to insert all the four BLOB-type values in the table “Test” using the same instruction.

sqlite> INSERT INTO TEST(Types) VALUES (X‘5678’);

sqlite> INSERT INTO TEST(Types) VALUES (X‘2414’);

sqlite> INSERT INTO TEST(Types) VALUES (X‘0000’);

sqlite> INSERT INTO TEST(Types) VALUES (X‘0234’);

After inserting four new records, we have been using the ID and Types column within the Test column to display all the records that contain the type ‘blob’ within them using the typeof() function within the query. It returns five records for the type BLOB, as shown below:

sqlite> SELECT ID, Types FROM Test WHERE typeof(Types) = ‘blob’;

Using the SELECT instruction with the simple “*” character followed by the FROM clause containing the name of a table “Test”, we have listed all the 10 column values in the table.

sqlite> SELECT * FROM Test;

Let’s type the values from the table where the type is ‘blob’ as per the DELETE instruction and display the rest. It shows the five records have been left.

sqlite> DELETE FROM Test WHERE typeof(Types) = ‘blob’;


This article was about using the BLOB type in the tables of SQLite. We inserted the BLOB records, displayed them using the typeof() function, and deleted them. You can use all these queries in any operating system and get the same result we presented in this article.

Source link

Files and folders are the necessary parts of any operating system. But, when you need to work on databases, the CSV files come in handy. The CSV files are also another name for Microsoft Excel documents used to store data in the form of rows and columns. The Excel or CSV files can be used to import data within the tables of a database. Therefore, we have decided to cover the method of importing a CSV file containing data in columns to the SQLite database table. Make sure to have SQLite C-library of SQL installed in your Ubuntu 20.04.

Let’s get started with today’s article by launching the console application within Ubuntu 20.04 with the Ctrl+Alt+T shortcut. After the successful launch of the console application, you need to update your Linux system with the help of an apt package preceded by the “sudo” keyword. It will ask for your currently working user account password to resume the updating process through this query. We have added the password, pressed the Enter key, and continued.

After a while, your system will be up to date. After this, you need to upgrade your system and its packages, i.e., modules and libraries. Try out the same instruction with the keyword “upgrade” instead of the word “update”.

Make sure to have some CSV files within your Linux’s home folder to use within the SQLite database, i.e., import its data to a table. Thus, we have created a new CSV file named “data.csv” within the home folder to be used in the table by importing. Let’s start the SQLite database on the shell terminal of the Ubuntu 20.04 system using the keyword “sqlite3”. The query area of an SQLite will be launched as shown below:

We have been starting this guide using the “.tables” instruction in the database to display the list of tables. Right now, we don’t have any table in this database.


To import CSV files, we need a table in the database. Thus, we have to create a table with the same column names and types as we have in the CSV file. So, we have been creating a new table named DATA with the CREATE TABLE instruction containing two columns, i.e., “Country” of TEXT type and column “People” of integer type. This newly created table has no records yet.

CREATE TABLE DATA(«Country» TEXT, «People» INT);


Let’s import the CSV file named “data.csv” within the DATA table using the “.import” instruction followed by the path to the file and the table’s name, as presented below:

.import /home/linux/DATA.csv DATA

After the successful query execution, we should confirm whether the import was proper and successful. For this, you need to try out the SELECT instruction for the table DATA merienda again on your SQLite shell. The output of this command shows that the import is successful. All the records from the data.csv file have been imported into the DATA table properly.

Let’s try out the condition within the SELECT query. We have added the WHERE clause to display all the records from the imported data of a table “DATA”, where the column “People” contains the values less than or equal to 550000. It returned a total of eight records.

SELECT * FROM DATA WHERE People >= 550000;

Let’s try a SELECT query with the WHERE clause in the SQLite database to display all the records of a table “DATA” where the column “People” have a value between 550000 and 750000. For this, we have been using the BETWEEN keyword and have only three results.


Let’s look at another example of importing the same file with a different set of records. This time, the data set contains 15 rows with the three columns, i.e., Name, Age, and Salary. The column Name and Salary are Integer types, while the column “Name” is text type.

For this data, we have been creating a new table named “Detail” containing the same name in three columns as the data.csv file, i.e., Name, Age, and Salary. Before importing the data.csv file to the Detail table, we haven’t found any record within it per the SELECT instruction executed at the SQLite shell screen, i.e., empty table.

CREATE TABLE Detail(Name TEXT, Age INT, Salary INT);


To import the data of the data.csv file within the Detail table, we need to try out the “.import” instruction followed by the path to the “data.csv” file, i.e., /home/Linux/, and the name of a table “Detail” at the end of this query. This query will return nothing showing that the import has been successful. So, we will be using the SELECT instruction merienda again to confirm that the import was made perfectly and efficiently. The SELECT instruction with the “*” character followed by the name of a table “Detail” displayed all the records of the data.csv file just imported to the table. This means the import was successful.

.import /home/linux/DATA.csv Detail

Let’s apply the WHERE clause on the imported CSV column “Salary” of the table Detail to display only the records where the value of the Salary column is less than 35000. This query with the WHERE condition returns two records as a result.

SELECT * FROM Detail WHERE Age < 20;

Let’s use the WHERE clause within the SELECT query to display the records of a table “Detail” where the imported data has values between 35 and 47 in the “Age” column. This time, we have five records, as presented below.



This article used CSV files to import data records to the SQLite database table using the “import” instruction at the shell. You need to ensure that the CSV column names and the table column names must be the same, i.e., uppercase or lowercase. We also discussed the ways to display the imported data in tables using some conditions.

Source link

A variant of ALTER TABLE is supported by SQLite. A preexisting table can be modified, have a field retitled, have a field inserted into it, or have a field eliminated from it using SQLite’s ALTER TABLE statement. The table’s column name is renamed a new name with the RENAME COLUMN command. We can only modify a table inside the same dataset using this operation. Whereas if the table changes to include triggers or indices, these are kept just after the change. Therefore, we have decided to cover the topic of renaming columns of an SQLite table in this guide.

This article begins with the launch of the shell application first in Ubuntu 20.04 system. Using the Ctrl+Alt+T, we have opened it and started to update our system with the apt update instruction. After adding the password for the currently logged-in user, the updating process has started and been completed.

After updating the system, we have to ensure that our system and its internal packages are upgraded to the newest version. So, we have been upgrading its packages using the apt upgrade instruction, as displayed below:

After successfully updating and upgrading our system, we will be moving toward launching the SQLite database within the terminal shell. You have to utilize the single keyword “sqlite3” to start it. The SQLite shell will be launched on our screen, and we can use it for querying data.

After opening it, we have listed the tables of a database with the “.tables” instruction and found there are no tables so far in the database.

To rename a column, we must have a table in the database. Therefore, we have been making a table titled “Test” within our current SQLite database with the CREATE TABLE instruction. Within this table, we will have two columns ID and Name. The ID column will contain an integer type value representing the primary key of a table that must not be NULL. The Name column will be of Text type and must not be NULL as well. Now, we have a “test” table within the list of tables as per the “.tables” instruction. Selecting the records of a Test table, we have found that it is empty and needs some records to be inserted within it.

Therefore, we have inserted five records within the ID and Name column of a table “Test” using the INSERT INTO instruction using the VALUES keyword followed by the records to be inserted. The five records are unique and have no duplicate values. After inserting the records, we have been checking the table records with the help of a SELECT instruction followed by the asterisk “*” character and the table name “Test”. This query returns all the five records of this table for the ID and Name column separated by the “|” character.

Our column names are “ID” and “Name” for the Test table. Let’s start renaming the column names using the RENAME COLUMN instruction. We will be renaming the column “Name” to “Fname” using the ALTER TABLE instruction followed by the table name “Test” and the “RENAME COLUMN” using the “TO” keyword. The query was successful, as shown below:


After altering the column’s name for the table “Test”, we will use the SELECT instruction to display all the table’s records. A total of five records have been displayed, as presented below:

sqlite> SELECT * FROM Test;

Let’s see how updating a new name works or not. Let’s insert the records within the Test table using the same llamativo names of columns for the table Test. Thus, we have tried the INSERT INTO instruction with the llamativo names of the “ID” and “Name” column followed by the VALUES keyword and the 6th record, i.e., (6, “Barak”). Execution of this instruction returns an error “table test has no column named “Name”. This error has occurred due to the usage of the llamativo column’s name “Name” instead of the new column name “Fname”.

Let’s insert the same record with the new column name “Fname” instead of the llamativo column name “Name” via the INSERT INTO instruction usage in the terminal. This time, we didn’t have any errors after executing this insertion command. We have displayed all the records of the Test table using the SELECT instruction followed by the asterisk “*” character. A total of six records have been displayed, i.e., the last record is the newest inserted record with the new column name “Fname”.

Just like the INSERT instruction, we can also use the SELECT instruction to fetch the records of a table and use the column name within it to display that the new name has been successfully added to the table column. So, we have been utilizing the SELECT instruction to display the Test table records while adding a WHERE clause condition specified. For this, we have been using the llamativo column’s name, “Name”, to display only the records from the table where the value in the Name column is “Ana”. Execution of this query displayed an error, “no such column: Name”. The reason for this error is the newly updated column’s name to “Fname”. Let’s run the same query with the new column name “Fname” to fetch all the records where the “Fname” column contains the value “Ana”. It displayed a single record from the table and removed the error.

sqlite> SELECT * FROM Actor WHERE Name = «Ana»;

sqlite> SELECT * FROM Actor WHERE FName = «Ana»;


This article discussed using the RENAME COLUMN clause within the ALTER TABLE instruction to update or modify the name of a specific column from the table. The example can be amended as well. We have done it so far in the simplest way possible and hope you like it.

Source link

SQLite has a case statement which allows us to include conditional logic in our SQL statements. SQLite case expressions execute a list of conditions and return an expression based on the results. SQLite case statements behave identically to IF – THEN – ELSE statements like the other scripting languages. If the phrase is valid, we can use the SQLite case statement inside any of the clauses or SQL statements. In genérico, the SQLite Case statement starts with an optional expression and one or more WHEN… THEN clauses, an optional ELSE clause, and a necessary END keyword.

Syntax of the CASE statement in SQLite:

In SQLite, the case statement usually has one of two forms.

SQLite simple Case statement:

CASE test_statement
WHEN [condition1] THEN [statement1]
WHEN [condition2] THEN [statement2]
WHEN [condition(n)] THEN [statement(n)]
ELSE [statement]

We use the syntax above to establish many conditions to reach the desired outcome. When and then clauses are used in the above syntax. They function in a logical order. To return the final output, this method compares each statement to the list of statements. This is a fairly easy approach for executing the case statement according to the user’s requirements. Each condition and statement are interdependent, meaning, when the first condition is true, the statement is only executed after that. This way, all conditions, and statements are executed. If a condition is false, control is transferred to the else part as seen in the above syntax.

To choose the outcome, the query case statement evaluates a list of statements. It’s worth noting that the simple case expression just looks for equity. Whereas, the looked-through case statement can use any form of inspection.

SQLite Search case statement:

CASE test_statement
WHEN [BOOLEAN statement1] THEN [statement1]
WHEN[BOOLEAN statement2] THEN[statement2]
 ELSE [ statement] END

If the Boolean statement in the specified grouping is valid, the Search case statement evaluates it and returns the corresponding result. When no valid statement is found, the query case statement returns the statement in the ELSE condition. If the ELSE clause is neglected, the looked-through case statement returns NULL. When the stated criteria are met, the search case statement terminates the assessment and execution.

Creating table for CASE statement in SQLite:

First, we have created a table and gave the name “Student” with the CREATE query. The table is set with the different columns such as ID with the data type integer and create ID as a primary key, NAME, and EMAIL is set with the data type TEXT. The last column CGPA is assigned a TEXT data type. The table Student and its attributes are shown in the SQLite shell as follows:

   …>       ID INT PRIMARY KEY    ,
   …>       NAME           TEXT   ,
   …>       EMAIL          TEXT   ,
   …>       CGPA           FLOAT
   …>     );

Now, we have to insert the values against each column of the table Student. With the SQLite INSERT query, we have inserted five rows in each of the columns specified in the table Student. The screenshot below is showing the way of inserting records into the table columns.

sqlite> INSERT INTO Student VALUES (2, ‘Ibrahim’,[email protected], 3.20 );
sqlite> INSERT INTO Student VALUES (3, ‘Maha’,[email protected], 3.9);
sqlite> INSERT INTO Student VALUES (4, ‘Jennifer’, [email protected], 2.5);
sqlite> INSERT INTO Student VALUES (5, ‘Rehan’, [email protected], 3.10 );

Let’s view whether the data is recorded in the table Student. By using the SELECT statement, we have retrieved the entire data in the table Student in a table format. The aforementioned statement is represented in the screenshot below:

sqlite> SELECT * FROM Student;

Example 1: Program of using SQLite simple CASE statement:

We have used a simple CASE statement with the SELECT statement to show the workings of the CASE statement in SQLite.

Here, we have used a SELECT statement and selected a column ID, NAME, and CGPA from the table Student. After that, we used a CASE statement that compares the CGPA of the Student. It matches the CGPA with the condition whether the CGPA is greater than or equal to 4.00 or not. If so, then it gives that CGPA an A+. But if the condition is false, then CASE will switch to the next statement and check whether the CGPA is greater than 3.5. If the condition is true, then it assign this CGPA a grade “A”.

Like this, the control is passed to each case statement until the CGPA does not meet the given condition. If all the cases are false, then the else clause will be executed and will print a statement FAIL.

   …> CASE
   …> WHEN CGPA >= 4.00 THEN «A+»
   …> WHEN CGPA >= 3.5 THEN «A»
   …> WHEN CGPA >= 3.0 THEN «B»
   …> WHEN CGPA >= 2.5 THEN «C»
   …> ELSE «FAIL»
   …> END AS «GRADE»
   …> FROM Student;

Now, we will perform the query and look at the outcome. It should look like this: The GRADE column is included in the table Student along with the values from the CASE statement.

Example 2: Program of using SQLite search CASE statement:

To determine the outcome, the searched CASE statement analyses a set of expressions. The simple CASE expression simply compares for equality. Whereas, the searching CASE expression can compare in any way.

A Boolean case statement is used in the example given. For this, we have a SELECT query through which we have selected three attributes ID, NAME, and CGPA from the table Student. Only these attributes and their corresponding values will appear as the output. Then, we have a CASE keyword for the SQLite CASE statement. The condition is applied with the WHEN keyword. First, case statement checks if the CGPA is greater than 3.5 and then assigns the grade A. If the condition is not satisfied here, then we will move to our second case statement which checks the condition whether is satisfied here or not. If both our cases are false, then the else part will execute and print the grade C.

   …> CASE
   …> WHEN CGPA > 3.5 THEN «A»
   …> WHEN CGPA >  3.0 AND CGPA < 2.5 THEN «B»
   …> ELSE
   …> «C»
   …> END «GRADE»
   …> FROM Student;

When executing the above CASE query, the results are obtained like this:


We studied the basic syntax of case statements in this article. We also saw a variety of case statement instances. The rules for case statements were also taught. We learned using the SQLite case statement in this post and when to do so.

Source link

When returning from a SELECT query in SQLite, the Except clause is essential for excluding some rows. If we use the Except clause with consecutive SQLite Select statements, it returns all data with the first select expression except those from the second. With examples in this SQLite article, we will learn about the SQLite Except operator. Then, we will learn how to use it to retrieve all the records from this first select statement beside the data produced by a second select statement.

In most cases, we utilize the SQLite Except clause with Select statements. All of the Select statements, then, that include the Except clause should have the same number of records. The Except clause in SQLite is summarized in the figure below:

If you look at the above SQLite Except clause pictorial representation, it will return the records that are in the color shaded area. This indicates that the Except clause will provide all of the records from the first Select statement except those that are in the second Select statement.

Syntax of the Except clause in SQLite:

The SQLite Except clause is used with a select statement in the following pattern:

SELECT column_Name1, column_Name2,…, columnn_Name_n
[WHERE condition]

SELECT column_Name1, column_Name2,…, columnn_Name_n
[WHERE condition];

We recognized two Select queries with the Except operator in SQLite, as you can see above. In this case, the SQLite Except operator produces entries of the first SELECT query that do not occur in the second Select query. We specified some of the attributes that are used in the Except operator syntax above:

Column: You want to compare the columns or operations between both the SELECT statements. For each of the Queries, the fields do not have to be the same, but the related columns must be of similar data types.

Table_Name: The Table_Name out of which you would like to retrieve records. The FROM clause must mention at least one table.

WHERE condition: The Where condition is Optional here. We used the Where condition for the criteria that should be required for records to be chosen.

Create Tables for the Except clause:

We should first construct tables in the database before we can use the Except clause. We have created a table “Teacher” and set its fields as mentioned in the figure.

The insert query is used in the following figure to insert the record in the table “Teacher”.

From the SELECT query, we can fetch the table record as follows:

As we have created the table “Teacher”. Now, we are required to make another table for the except clause. For this, we have created the second table in the existing database as “Student”. The fields are set for the table “Student” as mentioned below.

Then, we have inserted the entries for the table “Student” by utilizing the insert query as follows:

You can see the record in the ‘’Student” table in the following prompt shell.

Example 1: Program of using SQLite Except clause with the single expression:

For that basic SQLite query, we will examine how to use the SQLite Except clause in Select statements with a single column. Within our SQLite shell, we provided our first SELECT expression that included the field ID from the table “Student” and then we used the Except clause. After that, the second SELECT expression is utilized to select the same field ID but from the table “Teacher”. This example of the EXCEPT clause retrieves all ID entries from the table Student but not from the table Teacher. This means that if an ID value was present in both the Student and Teacher tables, the ID value would not appear in the EXCEPT query data.

As the ID “6” is a unique ID found in the table Student so it is displayed in the output here:

FROM Student
FROM Teacher;

Example 2: Program of using SQLite Except clause with the multiple expression:

Next, let’s examine a case of an SQLite EXCEPT query that returns multiple columns. Here, we have two SELECT expressions with the WHERE condition. We have taken the fields ID, St_NAME, SUBJECT, and CGPA from the table student. Then, we applied a WHERE condition that only displays the record whose CGPA is greater than and equal to “3.0”. We have a second SELECT expression which selects the column ID, T_NAME, T_AGE, and SUBJECT from the table Teacher. The second SELECT expression uses the WHERE condition that fetched the record from the table Teacher where the SUBJECT is equal to Computer. In between these two SELECT expressions, we have an EXCEPT clause that returns only the record of the table Student with the specified field values but not the field values matched with the Teacher table.

FROM Student
FROM Teacher
WHERE SUBJECT = ‘Computer’;

The output from the Except query is as follows:

Example 3: Program of using SQLite Except for clause with ORDER BY clause:

Here, we have used the Except clause along with the order by descending order. First, we have defined two SELECT expressions with the different fields from the table Student and Teacher. These SELECT expressions are called the except clause in between them. As the field names in the two SELECT expressions differ, it’s preferable to refer to the fields in the ORDER BY clause by their result set position.

FROM Student
FROM Teacher

We have successfully retrieved the record from Except query in descending order as follows:


This is how we may utilize the SQLite Except clause to retrieve data from Select statements that match our conditions. We have provided a quick introduction with a pictorial representation in this post. Also, described the syntax of except clause and used this syntax in the example to show the results. We can use the except clause for the single column and multiple columns. The order by clause is also used with the except clause for the sorted results.

Source link

SQLite gives the user a new set of clauses, one of which is a GROUP BY. In essence, the GROUP BY statement is an additional component of the select query that works in conjunction with other select commands to organize content differently. The GROUP BY statement is being used to create a set of rows based on the user’s query. It also provides single or multiple rows for every group. We may employ accrual accounting methods like SUM and COUNT as parameters in the GROUP BY command to provide more information about the given table. The FROM component of the SELECT query is followed by the GROUP BY statement. If a query includes a WHERE command, the GROUP BY command has to be placed after it.

If the SELECT query is an accumulated statement with a GROUP BY command, then each of the provided variables defined as an element of the GROUP BY command is evaluated for every column of the data. Each entry is then assigned to a “collection” based on the results. Entries with the same GROUP BY command results are assigned to a related group. Empty values are considered identical for aggregating rows. When evaluating a given argument in a GROUP BY command, the usual criteria for selecting a grouping configuration for analyzing textual values are applicable. The GROUP BY command’s provided argument will not have to be a parameter that appears in the output. In a GROUP BY command, the given argument cannot be an accumulated defined expression. We are going to explore how to utilize the GROUP BY command to develop a set of summarized rows from a series of values in this article.

Create Table:

First, we have to create a table named ‘Doctor’. There are different attributes of this table. The columns include Doc_id, Doc_fname, Doc_lname, Salary, and City. The attributes have different data types. The column ‘Doc_id’ has an integer data type, ‘Doc_fname’, ‘Doc_lname’, and ‘City’ has a TEXT data type. Whereas, the attribute ‘Salary’ contains the NUMERIC data type.

Doc_fname TEXT,
Doc_lname TEXT,

The output displays the effective execution of the above program.

Insert table:

In the next step, we have been using the INSERT query to insert the data of different doctors in the columns (Doc_id, Doc_fname, Doc_lname, Salary, and City) of the table ‘Doctor’.

INSERT INTO Doctor (Doc_id, Doc_fname, Doc_lname, Salary, City) VALUES (764, ‘Asma’, ‘Muneeb’, ‘40000’, ‘Islamabad’),
(381, ‘Laiba’, ‘Ajmal’, ‘90000’, ‘Swat’),
(904, ‘Muhammad’, ‘Ahmed’, ‘50000’, ‘Multan’),
(349, ‘Noor’, ‘Zain’, ‘120000’, ‘Karachi’),
(557, ‘Kashaan’, ‘Khalid’, ‘70000’, ‘Islamabad’);

We successfully run the query of INSERT as shown in the figure below.

SELECT query:

We can get the entire data of the table or acquire data from just a few columns. Whenever we want to obtain the information of some columns, we have to specify the names of that column in the SELECT query.

After executing the SELECT query, we get the entire data of the table ‘Doctor’. The table contains the ids, first names, last names, salaries, and cities of doctors.

Use GROUP BY clause:

In SQLite queries, the GROUP BY clause has been utilized in association with the SELECT command to organize similar data. In a SELECT query, the GROUP BY clause comes after the WHERE statement and just before the ORDER BY statement. In this instance, we have applied the GROUP BY clause on City.

>> SELECT * FROM Doctor GROUP BY City;

The data in the output is aggregated by the column of ‘City’.

Here, we just want to acquire the data of ids, first names, and salaries of doctors. The GROUP BY and ORDER BY clauses are employed on the column ‘Doc_id’.

>> SELECT Doc_id, Doc_fname, Salary FROM Doctor GROUP BY Doc_id ORDER BY Doc_id DESC;

In the resultant table, only data of the column ‘Doc_id’, ‘Doc_fname’, and salary will be obtained. The data in the table is sorted by using the ids of doctors in descending order.

Use GROUP BY command with SUM:

For the table ‘Doctor’, we are going to demonstrate using Group By statement along with the SUM function. Whether we want to consider the exact amount of income paid to every doctor, we will utilize the GROUP BY command as seen below. Here, the GROUP BY clause is employed on the column ‘Doc_fname’.

>> SELECT Doc_fname, SUM(salary) FROM Doctor GROUP BY Doc_fname;

The table of the outcome will be grouped by column ‘Doc_fname’.

Use GROUP BY command with COUNT:

We will modify the above query slightly by just adding the COUNT(Doc_lname) in the SELECT statement like the following screenshot to observe how to utilize the GROUP BY command with the COUNT method.

>> SELECT Doc_id, Doc_fname, Doc_lname, Salary, SUM(salary), COUNT(Doc_lname) FROM Doctor GROUP BY Doc_lname;

If we look at the result, it calculates the number of doctors in the table ‘Doctor’ that has the same last name. To determine the sum for every group, we will employ the SUM function.

Use the GROUP BY command for multiple columns:

We will also employ the GROUP BY statement for several columns. Let’s have a look at the instance in which we utilize the GROUP BY statement for two columns.

>> SELECT * FROM Doctor GROUP BY Salary, City;

The query returns the entire data of the table ‘Doctor’. The GROUP BY clause is applied to Salary and City both.


With the help of several illustrations, we have explained when and how to utilize the Group by clause in the preceding article. The GROUP BY statement in SQLite is being applied to combine data in a single record wherein the result of one or even more defined columns is duplicated. This functionality will be used to reduce the extent of entries found by merely looking for distinct column values. We also noticed how the Group by statement could be used to do numerous operations dependent on our needs. We can utilize the GROUP BY command along with the COUNT and SUM functions. And we employ the GROUP BY command for multiple columns.

Source link

“SQLite allows us to run commands straight from a file. This is particularly beneficial if you have a lengthy program, such as establishing several different tables and populating them with data. While using the SQLite command prompt shell, use the .mode dot command in association with the .output or .merienda commands to output your query results to a file, or use the .read command to read the SQL command from the file in the SQLite shell. Here, we will see how to execute the SQL command in the SQLite shell and show the results in the file.

We must first install the SQLite3 shell application before we can use it. If SQLite is installed, then check that the program’s location has been added to our particular PATH environment variable since this will make it easier to access the database file as well as other files we might need. Then, cd to the database file’s folder from a command prompt. Make sure the file is in the same directory folder as the SQLite database you generated.”

Use sqlite3 as a command; this should bring up the SQLite shell, with a screen similar to the one seen below. First, we have specified the path of the folder where our file is then created a database name “hospital.db” by using the sqlite3 command.

The database is successfully created in SQLite; you can see it by using a .databases command in the shell. Now, we have a CREATE statement which is used to create a table in the given database. We created a table with the name “patients.” This table keeps the record of the patients, which includes ID as the primary key with the data type INT, NAME with the CHAR data type, and WARD with the type TEXT.

CREATE TABLE patients(
      ID             INT     ,
      NAME           CHAR(10)   ,
      WARD          FLAOT    

The table is being built, and the columns are being defined. Using the INSERT command, we have now included the data for each column.

INSERT INTO patients VALUES (1, ‘Nayab’, ‘medical’);
INSERT INTO patients VALUES (2, ‘Sadia’,‘cardiology’ );
INSERT INTO patients VALUES (3, ‘Aman’,‘neurosurgery’);
INSERT INTO patients VALUES (4, ‘Balaj’,‘skin specialist’);
INSERT INTO patients VALUES (5, ‘Raima’,‘urology’);

Example 1
We are running SQL commands here, and the results are printed in the file. The following commands must be used to accomplish this: The .header on the command is used to turn on the result set’s heading.

To direct the sqlite3 tool to provide the result in CSV mode, specify the output mode to CSV.

The .output FILENAME command is used to save the outcome of a query to a file. Following the .output command, all subsequent queries’ results will be stored in the file you selected in the FILENAME parameter. The .merienda FILENAME command is used if you just wish to save the outcome of another single query to a file.

We can use the .output command without any arguments to re-display the query’s result to the standard output.

.headers ON
.mode csv
.output File.txt

After using the above command, we have a SELECT statement that displays the table and the table record within our file.

The SQL command runs here when we open our file, and the table record is displayed.

Example 2
As in the above example, we have shown how to insert the record from the shell to the file. Here, we have a SQL select command in the file. This command fetches all the records in the table patients. We have used the method .read file name.

The File.txt script reads from the current folder/directory in this case. If in a separate directory, specify the entire path. By using the .read File.txt in the SQLite shell, the table is viewable here as we have executed the command from the file.

FROM patients

Example 3
Here also, we are executing the SQL command from the file but with the .timer command in the shell. You can use the .timer command to activate or deactivate CPU time measurements. SQLite will maestro and report the operating system duration needed to process each query if this option is enabled. Firstly, we have the UPDATE command, which sets the NAME = UZAMA with the WHERE CLAUSE, which has the ID = 1 in the File.txt.

UPDATE patients SET NAME = ‘Uzama’ WHERE ID = 1

Now, we have set the header on command and also the timer on command. Then, use the .read File.txt to display the results of the command from the File.txt. Notice that the timer is on and showing the record with the updated values as follows:

.header ON
.mode COLUMN
.timer ON
.read File.txt

Example 4
Here’s a different approach to using the .read command. The following SQL command from the File.txt, which we are executing in the SQLite shell.

Without having to open SQLite, you can use the .read command. The contrast between this case and the last one is that the last one was done after you’d already connected to SQLite from within SQLite. This example, however, is executed from outside of SQLite. We have given the command “sqlite3 hospital.db “.read File.txt” within the folder where the database and file are present. The results are represented in the image below.

sqlite3 hospital.db «.read File.txt»

Example 5
This is our third method for executing SQL commands from the file. The command in File.txt is as follows:

SELECT *FROM patients

When connecting to SQLite and opening a database with sqlite3, the following approach forwards the information to the database file.

sqlite3 hospital.db< File.txt

Example 6
Another way to execute SQL commands from the file is by using the init method. Below is the SQL command, which will be executed by the init method.


Hence, the command “sqlite3 hospital.db -init File.txt” also executes the SQL command from the file as shown in the snapshot.

sqlite3 hospital.db init File.txt


In the end, we have shown the four different ways which execute SQL commands from the file. First, we have to place the file and the database on the same path; then, we can execute the several SQL commands successfully. Each method is implemented by displaying the output.

Source link