Organizing and Accessing Data using SQL in the Digital Age

What is Database?

Databases marked its origin after the invention of computers. Since before 1950's data used to be stored in books, registers, journals, libraries which used to take humongous space and mostly a hectic task to get way back to data. So, initially data used to stored in computers but used to navigate through data to find useful results, Hence concepts of Database emerged where large collection of data can be store in tabular format making the data organized and easy accessibility of data within the computer electronically. Here referred data can be image, text, numeric values.

Database can have structured/unstructured data, but it has very vast implications in every industry may be its Airline industry, retail industry, e-commerce, food industry, banks etc where they deal with huge customer and need to store customer related information, product related information, sales details, payments details and tons of other activities which is not possible via journals and libraries, Databases are extensively used in every field as after invention of internet the world is moving rapidly and the pace and be matched with the help of Databases.

There are three types of Databases:

• Relational database -MySQl, SQL server,IBM Studio DB2 , Oracle DB
• Object oriented database -PostgreSQL
• NoSql database -MongoDb

What is Relational database?

Relational database or RDBMS stands for Relational database management system is a type of data base where the data is stored in the form of Rows and Columns and the relation between data is created based on some unique id/fields which is a key that defines the data uniquely.

In relational data base we can easily track the relation , suppose we have one table with Employee details which has employee Id as Key and other Table with department name where department Id as primary key but the employee table will as have one dept id column which is a foreign key to Department table through
which the relation is established.

Examples- MySQl, SQL server, IBM Studio DB2 , Oracle DB, PostgreSQL, SQLite, Azure SQL, Google Cloud SQL

What is SQL?

Structured query language / SQL is backbone of DBMS , SQL is a set of commands through which we can Create a table , insert, delete, retrieve within a table .
SQL code is useful in relational DB to retrieve manage , update data in an efficient manner.

Four types of SQL Commands are follow below:-

1. DDL (Data definition language) - DDL are the set commands dealing with defining structures and relationship with the data inside the table like creating a tables , deleting a table which directly impact the structure if table, schema and its indexes.

Example: CREATE, ALTER, DROP, TRUNCATE, RENAME

• CREATE Command

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    .....
);

CREATE TABLE Employee(
    EmpID INT PRIMARY KEY,
    Emp_Name VARCHAR(50),
    Country VARCHAR(50),
    Phone INT (10)
);

• ALTER Command

ALTER TABLE table_name
COMMAND column_name datatype;

ALTER TABLE Employee
RENAME COLUMN EmpName to EmpFirstName;

ALTER TABLE Employee
ADD Email varchar(255);

• DROP Command

DROP TABLE emplyee;

• TRUNCATE Command

 TRUNCATE TABLE emplyee;

2. DML (Data manipulation language) - DML command is used to modify row-level data such as insert a new record into table, update the record and Delete any record based on conditions within a table.

Example:INSERT, UPDATE, DELETE

• INSERT Command

INSERT INTO employee (column1, column2, column3) VALUES (value1, value2, value3);

• UPDATE Command

UPDATE Employee
SET EmpName = 'John', Country= 'U.S.A'
WHERE EmpID = 1;

• DELETE Command

DELETE FROM table_name WHERE condition;

DELETE FROM employee WHERE EmpID=1 and Country='U.S.A';

3. DCL (Data control language) - DCL command is used for proving rights and permission of Database systems, mainly as administrator task.

Example: GRANT, REVOKE

4. TCL (Transaction control language) - TCL used for group of task whether beginning or ending of task if a task fails the transaction fails. These tasks basically used for maintaining integrity to data.
   SQL Transactions is ruled by ACID properties.

Example:BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVE POINT, SET TRANSACTION

• TCL Command

BEGIN TRANSACTION -BEGIN TRANSACTION
COMMIT- COMMIT;
ROLLBACK- ROLLBACK;
SAVEPOINT- SAVEPOINT savepointname;

Notes: In essence, databases provide the structured storage for vast amounts of information, and SQL is the universal language used to effectively manage, access, and utilize that data in relational systems. Their combined use underpins most modern applications, business intelligence, and critical operational systems across nearly every sector.