Hello Techie! Today we're diving into the world of databases and database management systems. Databases play an important role in managing and organizing vast volumes of data efficiently. Understanding the basics of databases and database management systems (DBMS) is crucial for anyone navigating the data-driven landscape.

Introduction :

A Database is like a digital collection of information. It help us to store, fetch, delete or analyse data quickly for making smarter decisions. Think of as a way to neatly organise data in tables. schemas, views and reports.

The Database Management System (DBMS) is a software tool which is used to manage our data which is stored in database. Examples of popular databases are Oracle, MySQL, PostgreSQL, MongoDB, InfluxDB etc. 

You can check out the ranking according to popularity and information of any database management system by clicking here.

Accessing a database directly isn't common; instead, we use a Database Management System. DBMS is a software application responsible for performing various operations on the database, such as inserting, creating, finding, and deleting data.

Types of Database : 

Generally database are classified as Relational and Non-Relational (NoSQL).
A Relational Database is a type of database that organizes and stores data in tables with rows and columns. In this structured system, each row represents a record, and each column represents a specific attribute or field of that record. The tables are interconnected based on relationships between the data, providing a clear and efficient way to manage and retrieve information. Relational databases use a standardized query language, often SQL (Structured Query Language), to perform operations such as inserting, updating, querying, and deleting data. 
Examples of relational database management systems (RDBMS) include Oracle, MySQL, PostgreSQL,, Microsoft SQL Server etc.

SQL (Structural Query Language):

It is a set of SQL commands that can be used to define the database schema.

• CREATE: This command is used to create a new database or its objects such as tables, indexes, functions, views, stored procedures, and triggers.
• ALTER: This command is used to modify the structure of the database. It can be used to add, modify, or delete columns in an existing table.
• ADD: This sub-command is used to add a new column to an existing table.
• RENAME: This sub-command is used to rename an object that already exists in the database.
• DROP: This sub-command is used to delete an object from the database.
• TRUNCATE: This command is used to remove all records from a table, including all spaces allocated for the records are removed. It is used to permanently delete data and does auto-commit on rollback.

2. DML (Data Manupilation Language)

It is a set of SQL commands that can be used to manipulate data stored in a database.

• INSERT: This command is used to insert new data into a table.

• DELETE: This command is used to delete data from a table. By calling rollback, data will be retained temporarily.

• UPDATE: This command is used to modify existing data in a table.

NOTE:
TRUNCATE : Used to permanently delete data (auto commit on rollback)
DELETE : To temporary delete data . By calling rollback data will retain

3. DCL (Data Control Language)

Deal with the rights, permissions, and other controls of the database system.

• GRANT: This command is used to give users access privileges to the database.

• REVOKE: This command is used to withdraw the user’s access privileges given by using the GRANT command.

4. TCL (Transaction Control Language)

It is a set of SQL commands that can be used to manage transactions in a database. A transaction is a sequence of operations that are executed as a single unit of work. TCL commands are used to manage the changes made by DML statements.

• COMMIT: This command is used to save all the transaction-related changes permanently to the disk.
• ROLLBACK: This command is used to undo the changes made to the database since the last commit.
• SAVEPOINT: This command is used to set a savepoint within a transaction.

5. DQL (Data Query Language)

It is an essential tool for anyone working with databases, as it allows you to interact with your data in a meaningful way.

• SELECT : This command is used to retrieve data from one or more tables in a database. It is used for performing queries on the data within schema objects. 
• FROM: This clause helps you identify the source of data.

NOTE:
SELECT and FROM are mandatory clauses.

Here are some optional clauses that can be used 

• WHERE: This clause filters the data.
• GROUP BY: This clause groups the filtered data into buckets.
• HAVING: This clause filters the result of GROUP BY.
• ORDER BY: This clause orders the result.

What is Keys in SQL ?

In SQL databases, keys play a crucial role in organizing and establishing relationships between tables. There are several types of keys, each serving a specific purpose. 

Let's explore them in detail:

1. Primary Key (PK):

Definition: A primary key uniquely identifies each record in a table and ensures that there are no duplicate values.

Example:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Name VARCHAR(50),

    Age INT

);

In this example, StudentID is the primary key, and each student in the "Students" table will have a unique identifier.

NOTE:
Primary Key(PK) is a column (or set of columns) in a table that uniquely identifies each row. (a unique id).
There is only 1 PK & it should be NOT null.

2. Foreign Key (FK):

Definition: A foreign key establishes a link between two tables by referencing the primary key of another table.

Example:

CREATE TABLE Courses (

    CourseID INT PRIMARY KEY,

    CourseName VARCHAR(50)

);

CREATE TABLE Enrollments (

    EnrollmentID INT PRIMARY KEY,

    StudentID INT,

    CourseID INT,

    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

Here, StudentID and CourseID in the "Enrollments" table are foreign keys referencing the primary keys in the "Students" and "Courses" tables, respectively.

NOTE:
A Foreign Key(FK) is a column (or set of columns) in a table that refers to the primary key in another table.
There can be Multiple FKs.
FKs can have Duplicate & Null values.

Understanding and appropriately using these keys is essential for maintaining data integrity and building effective relationships between tables in a SQL database.

Now move on to another imortant concet in SQL Database.

What is constrains in SQL Database ?

In SQL databases, constraints are rules or conditions applied to a table column or a set of columns, ensuring the accuracy, integrity, and reliability of the data stored. Constraints help enforce business rules and maintain data consistency. 

Let's explore various types of constraints in detail:

1. NOT NULL Constraint:

Definition: Ensures that a column cannot have a NULL (empty) value.

Example:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    Age INT

);

In this example, both FirstName and LastName columns must have values, and they cannot be NULL.

2. UNIQUE Constraint:

Definition: Ensures that all values in a column (or a set of columns) are unique, except for NULL values.

Example:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    RollNumber VARCHAR(10) UNIQUE,

    Email VARCHAR(50) UNIQUE

);

Here, both RollNumber and Email must have unique values across all records in the "Students" table.

3. PRIMARY KEY Constraint:

Definition: Combines the NOT NULL and UNIQUE constraints. It uniquely identifies each record in a table.

example of primary key is already explained in what is what is Keys in SQL Topic.

4. FOREIGN KEY Constraint:

Definition: Creates a link between two tables by enforcing referential integrity. It ensures that values in one table's column correspond to the values in another table's column.

example of foreign key is already explained in What is Keys in SQL Topic.

5. CHECK Constraint:

Definition: Specifies a condition that values in a column must satisfy for a record to be valid.

Example:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    Salary DECIMAL(10, 2) CHECK (Salary > 0)

);

This ensures that the Salary column in the "Employees" table must always have a value greater than 0.

6. DEFAULT Constraint:

Definition: Specifies a default value for a column, which is used when an explicit value is not provided during an INSERT operation.

Example:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Grade CHAR(1) DEFAULT 'A'

);

If a grade is not specified during the insertion of a new student, the Grade column will default to 'A'.

Constraints are crucial for maintaining data accuracy and integrity in SQL databases, ensuring that the data follows predefined rules and meets specific criteria.

Joins in SQL 

In SQL databases, Joins are used to combine rows from two or more tables based on related columns. The concept of joins allows you to retrieve data that spans across multiple tables by specifying how the tables are related. There are several types of joins, each serving a specific purpose:

1. INNER JOIN:

Returns only the rows where there is a match in both tables based on the specified condition.

2. LEFT (OUTER) JOIN:

Returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

3. RIGHT (OUTER) JOIN:

Returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

4. FULL (OUTER) JOIN:

Returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the table without a match.

In below image you can see visulisation of Joins in SQL. 

5. LEFT EXCLUSIVE Join : 

In a left exclusive join, you want to find rows in the left table that do not have a match in the right table. You can achieve this using a LEFT JOIN and checking for NULL in columns from the right table.

6. RIGHT EXCLUSIVE Join: 

In a right exclusive join, you want to find rows in the right table that do not have a match in the left table. This can be achieved using a RIGHT JOIN and checking for NULL in columns from the left table.

Understanding and effectively using joins are essential skills in SQL, allowing you to retrieve and analyze data from multiple tables in a relational database.

To begin with SQL queries, check out the blog at the link provided. It's like a beginner's guide to playing with data using simple commands. Dive in, and you'll learn how to talk to databases and make them do what you want!

Wrapping Up

Exploring SQL databases reveals a world of tools for managing data. Think of tables as the organizers, keys as the unique identifiers, and constraints as the rules that keep data in check. SQL language is like the communication tool that lets us talk to the database and get the information we need.

In the end, knowing about SQL databases is like having a superpower to organize, understand, and use data smartly. It's not just tech talk; it's the key to making better decisions with the information we have.

Let me know what you think in the comments.

I'm not only here to chat but also to listen to you! I'd appreciate your feedback on this article and any suggestions for making it better. Let's work together to make understanding IoT easier. Let's discover the potential of IoT together!
Don't forget to share this article with your friends.