Importance and Use of SQL Database

What is SQL?

Structured Query Language (SQL) is a standardized programming language used to manage and manipulate relational databases. It allows users to perform various operations on the data stored in these databases, such as querying, updating, inserting, and deleting records.

Structured Query Language is grouped into categories which includes;

• Data Querying: SQL is primarily used to query data from a database. For example, you can retrieve specific information from a large dataset using SQL queries.

• Data Manipulation: It allows you to insert, update, and delete data within the database.

• Data Definition: SQL is used to define the structure of the data, such as creating, altering, and dropping tables and other database objects.

• Data Control: It includes commands to control access to data within the database, ensuring security and integrity.

Step by step guide on how to Create and Edit Table using SQLite3

What is SQLite3?

SQLite3 is a powerful, lightweight, and self-contained SQL database engine that is widely used for developing applications. Whether you’re building a small application or a large-scale project. Understanding how to create and edit tables in SQLite3 is essential. Let’s walk through the basics of setting up your database, creating tables, and modifying them as your data requirements evolve.

Let’s start with the fundamental SQL commands that is needed to create tables, also explore how to edit existing tables by adding, modifying, or deleting columns. We should have a solid understanding of how to manage our database schema effectively using SQLite3 at the end of this article.

Step one: How to set up SQLite3.

Download and Install SQLite3 through the browser search engine.

• search for SQLite3, click on the download button

  

  On the new page, click on “Precompiled Binaries for Windows” and click on “A bundle of command-line tools”

  

  • After downloading the Zipped file > create a new folder on your local c:/ drive name it sqlite3 > move the downloaded file to the newly created folder > Right click on the zipped file and extract.

    

    

• From the extracted file > launch the Sqlite3 Application

  

• Open a terminal (cmd prompt or cmder or Windows Powershell etc).

  for this practice, we would be using windows cmd prompt

Launch the cmd prompt (windows + R and then type cmd and hit enter) > copy the Sqlite3 path on the windows explorer and paste in the cmd.

Step Two: How to create a table.

2.1 Create a Database.

• To create a database: on the cmd > type the cmdlet - sqlite3 name of the database and use the .db

  Example: cmdlet -sqlite3 Muyiwa.db – this command creates the database file

  

• By default, the database is not feasible on the file explorer. To make this visible > on the cmd > type .database

• To view various kinds of commands applicable on the sqlite3 > use the .help

2.2 Create a Table.

• To create a table: we use the cmdlet CREATE TABLE

CREATE TABLE temidun (

order_id INTEGER,

customer_name TEXT NOT NULL,

food_item TEXT NOT NULL,

quantity INTEGER NOT NULL,

order_date TEXT NOT NULL,

Phone INTEGER NOT NULL

);

To Export the created file, kindly do the following:

Turn on the header: .headers on

Set the output mode to csv: .mode csv

Direct the output to a csv file: .output muyiwa.csv (name of the file)

Execute the SQL to select the data you want to export: SELECT * FROM temidun;

Example:

.headers on

.mode csv

.output muyiwa.csv

SELECT * FROM temidun;

2.3 Insert Into Table.

• To Insert, we use the cmdlet INSERT INTO

INSERT INTO temidun (order_id, customer_name, food_item, quantity, order_date, phone)

VALUES (1, 'folorunsho', 'eforiro', 3.2, '2024-2-15', '07031136297');

INSERT INTO temidun (order_id, customer_name, food_item, quantity, order_date, phone)

VALUES (2, 'kehinde', 'pounded yam', 4, '2024-3-20', '08039291125');

INSERT INTO temidun (order_id, customer_name, food_item, quantity, order_date, phone)

VALUES (3, 'pehinde', 'okra', 5.2, '2024-3-23', '07038291125');

INSERT INTO temidun (order_id, customer_name, food_item, quantity, order_date, phone)

VALUES (4, 'taiwo', 'pepper soup', 6, '2024-4-14', '08039150000');

.headers on

.mode csv

.output muyiwa.csv

SELECT * FROM temidun;

2.4 To Select from the Table

• To Select, we use the cmdlet SELECT * FROM

• To select all columns in the table: SELECT * FROM tablename; i.e SELECT * FROM temidun

• To Select specific columns in the table: SELECT column1, column2 FROM tablename;

• To Filter results with WHERE clause: SELECT * FROM tablename WHERE condition;

Example

:SELECT * FROM temidun WHERE quantity = 3.2;

.headers on

.mode csv

.output muyiwa3.csv

SELECT * FROM temidun;

2.5 To Alter Table

• To Alter Table, we use the cmdlet ALTER TABLE

Example:

• ALTER TABLE temidun ADD COLUMN delivery_address TEXT;

• ALTER TABLE temidun RENAME COLUMN phone TO contact_number;

.headers on

.mode csv

.output muyiwa1.csv

SELECT * FROM temidun;

2.6 To Update Table

• To Update Table, we use the cmdlet UPDATE

Example:

UPDATE temidun

SET customer_name = 'Iyin Ade'

WHERE customer_name = kehinde;

UPDATE temidun

SET quantity = 1

WHERE quantity = 6;

.headers on

.mode csv

.output muyiwa4.csv

SELECT * FROM temidun;

2.7 To delete in a Table

• To delete entries in Table, we use the cmdlet DELETE

Example:

DELETE FROM temidun WHERE quantity = 4;

DELETE FROM temidun WHERE quantity = 1;

.headers on

.mode csv

.output muyiwa5.csv

SELECT * FROM temidun;

2.8 To Delete Column from a Table.

• To delete column in Table, we use the cmdlet DROP

Example:

ALTER TABLE temidun

DROP COLUMN delivery_address;

.headers on

.mode csv

.output muyiwa6.csv

SELECT * FROM temidun;

Thank you for subscribing!