_edited_edited.png)
Photo by Marvin Meyer on Unsplash
Data is information, facts, and numbers collected to examine and analyze to help decision-making.
Data has been an important part of human civilization, ever since it existed. Data collected since the prehistoric age has helped us understand the evolution of the human race. We can see that humans have always tried to preserve data. The stone carvings, cave paintings, and ancient scriptures are all some examples of how data has been important to the human race.
Humans have always tried to pass on information or knowledge from one generation to the other. As humans evolved, the ways of preserving or storing data have also evolved. With new technologies coming into the picture, data storage has evolved by leaps and bounds.
The volume of data created has also evolved tremendously. Today we create a humongous amount of data. Whatever we touch has data associated with it. Be it our phone, laptop, tablet, smartwatch, refrigerator, etc. We live in a world where data is of utmost importance. Data is termed as new oil or gold. Data can be used to analyze past and current conditions, and create predictions for the future leading us to a better future.
Our world has become data-driven.
1. With the use of smartphones, we have started generating a lot of data. Data in the form of Photos, videos, documents, and applications on our phones are so huge that we have started relying on Cloud services to store them.
2. Netflix suggests what we would like to watch based on the shows or movies we watch.
3. Data logged in the smartwatch we wear helps us to stay fit.
4. With IoT (Internet of Things) we can connect to machines like refrigerators, home security devices, washing machines, dishwashers, etc.
5. Food delivery apps collect our data and suggest us new restaurants.
6. Social media uses our data for ad recommendations.
7. Facebook, LinkedIn, etc. suggest us new connections and new posts based on our activity.
These are some examples of how our life revolves around the data we generate.
With so much data generated we need to learn how to manage it, to make proper use of the data collected.
We need a system where we can store the data, interact with it and retrieve the required information when needed. This is possible through a database.
A database is an organized collection of data. The database helps in the storage and manipulation of data.
Photo by Sunder Muthukumaran on Unsplash
Different types of databases are as follows:
1. Relational database: Data is organized in the form of rows and columns.
2. Object-oriented database: data is represented and stored in the form of objects.
3. NoSQL database: NoSQL is a nonrelational database that supports unstructured and semi-structured data.
4. Distributed database: data is distributed among different database systems of an organization.
5. Cloud database: The data is stored in a virtual environment. The database runs on a Cloud Computing platform.
6. Centralized database: data is in a centralized database system.
7. Operational database: It is designed to create or update large amounts of data and store transactions performed by multiple users in real time.
We interact with the database using a standard language called Structured Query Language (SQL).
A brief history of SQL:
It's a computer language that was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.
SQL is used to create, maintain and query relational databases.
When IBM created the language, they named it SEQUEL (Structured English Query Language).
They adopted this name because we can query and retrieve information from a database using this language.
The structured English part of the name is because the syntax of the language is very similar to the syntax of ordinary English.
When IBM was ready to release the language as a product, they discovered that the name SEQUEL was already copyrighted by another company.
Hence IBM decided to drop the vowels and called their language SQL.
Now we understand why people half of the people call it SQL and the other half call it SEQUEL.
In the late 1970s, Oracle developed its own SQL-based RDBMS (Relational Database Management System).
Types of SQL
Following are the different types of SQL queries:.
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language(DCL)
4. Transaction Control Language(TCL)
5. Data Query Language (DQL)
1. Data Definition Language (DDL) –
DDL is used to define the database structure or schema.
Some of the DDL commands are:
· CREATE - creates a new table in the database.
Syntax:
CREATE TABLE table_name(column1 DATATYPE,column2 DATATYPE,..);
· ALTER - used to alter the structure of the database.
Syntax :
ALTER TABLE table_name action;
To add a new column in the table:
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify an existing column in the table:
ALTER TABLE MODIFY(COLUMN DEFINITION....);
· Truncate – is used to delete all the rows from the table and free the space containing the table.
Syntax :
TRUNCATE TABLE table_name;
· DROP - used to delete both the structure and records stored in the table.
Syntax:
DROP TABLE table_name;
2. Data Manipulation Language (DML)
Data Manipulation Language (DML) allows us to perform all types of data modification in a database. It allows modification of the database by inserting, modifying, and deleting its data.
Some of the DML commands are:
· INSERT
· UPDATE
· DELETE
· INSERT: This command is used to insert data into the row of a table.
Syntax:
INSERT INTO table_name (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Or
INSERT INTO table_name
VALUES (value1, value2, value3, .... valueN);
· UPDATE:
This command is used to update the value of a column in the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
· DELETE:
It is used to remove one or more rows from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
3. Data Control Language (DCL)
DCL commands are used to grant and take back authority from any database user.
Examples of DCL commands:
· Grant
· Revoke
· Grant:
This command is used to give user access privileges to a database.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
· Revoke
It is useful to back permissions from the user.
Syntax:
REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}
4. Transaction Control Language(TCL)
TCL commands deal with the transaction within the database.
· Commit
It is used to save all the transactions to the database.
Syntax:
COMMIT;
· Rollback
Allows us to undo transactions.
Syntax:
ROLLBACK;
5. Data Query Language (DQL)
DQL is used to return the data from the database.
SELECT is a DQL command.
Syntax:
SELECT column1,column2
FROM table_name
WHERE conditions;
This returns the data for column1 and column2 from the required table.
SELECT * FROM table_name;
This returns all the columns from the required table.
This blog gives us an idea about the importance of data, how it is stored and managed.