Structured Query Language — SQL

downloaded from freepik

“A language that talks to database” — Part 1

What Is SQL?

Did you realize that your action on website (click, scroll, etc) are generates tons of data? That data are stored in different storage mediums such as databases, file systems, etc.

Now, the question is how to store and access data from the database?

Yes, we need a language that can talks to our database.

SQL — Structured Query Language is a language used in programming and designed to communicate with a database, SQL let us access and manipulate data held in RDBMS (Relational Database Management System).

SQL are very important when we’re talking about data analytics.

Data engineer and database administrator use SQL to perform essential task about data structures and security. Data scientist use SQL for analyses and manipulate data and pour the result into their model. Data analyst use SQL to interpret complex digital data into something useful (insight).

Types of SQL commands

In order to communicate with database, we need some commands, here are the types of SQL commands:

DDL — Data Definition Languages

A command that manipulate the database structure, such as CREATE, INSERT, DROP, and ALTER. For example, here are the syntax to create database named sales

CREATE DATABASE sales

another example, the syntax to create table with names product_sales which has 2 columns namely with the name product_id with integer data type and product_name with varchar data type:

CREATE TABLE product_sales( 
        product_id int,
        product_name varchar (255))

DML — Data Manipulation Language

A command that manipulate data in a database such as INSERT , SELECT, UPDATE, and DELETE. For example we will filling the data in tables we create before, for the product_id column contains data: 1, 2, 3 for the product_name column contains data: Apel, Pisang, Jeruk

INSERT INTO product_sales (product_id, product_name)
VALUES (1,'Apel'),(2,'Pisang'),(3,'Jeruk')

After fill the data, we can see our output using SELECT command

SELECT * FROM product_sales

and the result is

DCL — Data Control Language

A command that control the user access to data such as GRANT and REVOKE

TCL — Transactional Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that’s why they cannot be used while creating tables or dropping them. Here are some commands that come under TCL: COMMIT, ROLLBACK, SAVEPOINT.