Databases & Database Manipulation

This article is for those who want to learn about databases, learn to manipulate data, get a quick introduction to SQL.

What is Database?

A database is a collection of electronically stored data or information on a computer or server system.

Databases are mostly stored on a DBMS (DataBase Management System), and we use DBMS when we want to operate on databases.

Databases nowadays mostly keep data in a tabular format, we mostly use the SQL (Structured Query Language) to operate on this data.

Types of Database

There are many different types of database software and variants on the market, in 2021 there are 10 types of databases, but we’ll explain the 5 most used database types.

1 – Relational Databases

The relational database entered our lives in the 1970s. Its data is stored in a table (in row and column format), it is one of the most used database types.

The relational database management system (RDBMS) is software for creating, updating, and administering relational databases.

Example RDBMS: MySQL, SQLite, Oracle Database, Microsoft SQL…

2 – NoSQL Databases

All databases that differ (segregate) from conventional relational database management systems are all included in this type.

It is not mandatory to use fixed signage storage method in these databases. Usually, horizontal scaling is used in NoSQL Databases.

Example: Apache Cassandra and MongoDB…

3 – Columnar Databases

Columnar databases, also called columnar data stores, store data in columns rather than rows. A separate column is used for each data group.

With horizontal scaling, you can save hardware power and increase efficiency. You can explore the storage format better with the diagram below.

Examples: Google BigQuery, Cassandra, HBase, MariaDB, Azure SQL Data Warehouse…

4 – Wide Column Databases

In Wide Column Database, data is stored in column families rather than rows and columns. This type of database can store petabytes of data.

A data column group stores the rows in the entire column so it is efficient when processing. See the diagram below for more information.

Examples: BigTable, Apache Cassandra, and Scylla…

5 – Cloud Databases

A cloud database refers to any database that’s designed to run in the cloud. Like other cloud-based applications, cloud databases offer flexibility and scalability.

One of the biggest advantages of using databases on cloud services is low maintenance.

Examples: Microsoft Azure SQL Database, Amazon Relational Database Service, Oracle Autonomous Database…

Databases vs Datasets

The concepts of Dataset and Database are often confused with each other because they both definition like same.

Dataset is a structured collection of data, usually associated with a unique workgroup.

The database is an electronic system that contains more than one dataset, which allows the data to be easily accessed, manipulated, and updated.

Similarities & Differences: Database and Spreadsheets

One of the concepts confused with the database is SpreadSheet, applications such as Excel and Google Sheets are not databases, let’s start by explaining this.

Basically, a database is a computer program designed to store, process, and retrieve information. Databases store data mostly in tables.

Tables are similar to spreadsheets, the biggest difference is that in the database, tables are related to each other and there is no such relationship in spreadsheets.

Database Manipulation in SQLite

In this section we will see how to manipulate data in relational database management systems, this section will also contain useful information about SQL.

Since I want to install a stable version of Sqlite, install the zip file containing the source codes on your computer.

1 – Creating Database

$ sqlite3 databaseName.db

Now let’s make some data entries to manipulate the data, and we will use this data in manipulation later.

CREATE TABLE Users(
   userName TEXT,
   userID INTEGER,
   userPassword TEXT
);

We currently have a User table with columns userName, userID, and userPassword. Don’t forget to add the data type to hold before each column is finished.

You can view how many tables have been created and these table names with the help of the below command.

sqlite> .tables
Output: Users

We have created the first table in the database, it is time to enter data into this table. MySQL, Oracle Database, etc. The commands you use in RDBMS are valid in SQLlite.

INSERT INTO Users
VALUES ("Admin" , 1 , "12Admin12");

The data here will be written in the first blank line in the Users table, you must enter the data according to the column order.

When you enter data, the data you enter is written under the previous data, for example, when I enter data once again, it will be printed on the row below the row.

You can use a viewer to make it easier to create a database and transfer data. Database Browser is currently a nice viewer for SQLite.

2 – Manipulation Part

Now we will learn to manipulate a sample database that we will create. The data in the attention database does not reflect the truth.

I created a Companies table in the database, this table keeps the name, sector, earnings, expense data of the companies.

Although the database is small, it is suitable for manipulations, now let’s look at the manipulation commands step by step.

1 – Adding New Record in Database

The Data Manipulation section will consist of 3 subsections: adding new records, deleting records, and updating records.

sqlite> SELECT * FROM Companies;

You can view the data of the table you want with the SELECT command, now let’s make a new data entry and view it again.

INSERT INTO Companies
VALUES("Company 11" , "Financials" , 34879456 , 23673245);

You can also enter the data in Float type, now let’s print the whole database and see if the new column has been added.

sqlite> SELECT * FROM Companies;

The new column has been successfully added, now let’s see how we can delete it when we entered the data incorrectly or we no longer need that data.

2 – Deleting Record in Database

We must use a condition to delete a record from the database. For example, in this section, I will delete the data containing the Company 11 variable in the Name.

DELETE FROM Companies 
WHERE Name == "Company 11";

You can limit the data to be deleted by creating more than one condition. For example, the following command deletes the ones that are more than the expense income.

DELETE FROM Companies 
WHERE AnnualIncome < AnnualLoss;
3 – Update Record in Database

Sometimes you may want to update the data instead of deleting it, or you may want to correct the wrong information instead of destroying the entire row.

For this, there is an UPDATE query in SQL that allows you to update your data according to a condition.

UPDATE Companies
SET Name = "Company 1"
WHERE Name = "Company 2";
Company 1|Health Care|3.400.000|762.987

Let’s write the new income and expenses data, it will be enough to update the old data for this, deleting the data and adding it again is bad in terms of efficiency.

UPDATE Companies
SET AnnualIncome = 351453299.93
SET AnnualLoss = 962987.98
WHERE Name == "Company 1";
-- Old
Company 1|Health Care|3.400.000|762987.98

-- New
Company 1|Health Care|351453299.93|962987.98

If you have a database where you keep annual earnings, income and expenses will change every year. Just updating 2 columns is convenient than adding them again.

Leave a Reply

Your email address will not be published. Required fields are marked *