Let’s know more about Basic SQL Concept

A simplified guide version of what is Structured Query Language (SQL) with example

7 min readJun 16, 2024

Hello, I’m Jonathan a mere data enthusiast, I’m learning while writing article that I find interesting in medium, and this is the let’s know more about series.

Introduction

Hey there! I see you’ve just landed on my article that explains the basic concepts of Structured Query Language (SQL), but do you know what kind of language this is? What kind of tasks can it perform? Without further ado, let’s dig deeper into this.

What is Structured Querly Language (SQL)?

Structured Query Language (SQL) is a programming language that let you access and manipulate databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. Unlike other programming language, SQL is declarative, strict, and only used for special purpose in relational database management. But of course, it has various statements to command the database.

Basic Query

Now you know what SQL is, let’s move to basic query. Where in this article basic means most fundamental query (it is not Postgre, Microsoft, or Oracle, just standard SQL). Mostly simple basic query I’ve encountered so far start with SELECT statement and the other statement will fill up after, like WHERE statement to announce some condition, and ORDER BY statement to sort table rows by specific column. Here is the example of the simple basic query:

SELECT * FROM Employees
WHERE YEAR(BirthDate) > '1950'
ORDER BY LastName

What that example query does is calling all data from table Employees where the condition is the birth date of the employee must be after year 1950 and the table sorted by employee’s last name.

Now let’s learn these statements, in this article there will be five categories of statements. It will be unveiled in the below:

1. Data Query Language (DQL)

This category is the one responsible for reading, or querying data from the database. This category only consists of SELECT statement. SELECT statement is used to retrieve data from the database, it has to be use with FROM statement and if there’s more than one table to connect with, use JOINstatement (but since this explanation is long, there will be another article for this).

SELECT LastName, FirstName FROM Employees

2. Data Definition Language (DDL)

This category is responsible for defining the way data is structured in the database (define the database schema). In SQL this category consists of five statements,

CREATE -create new objects or new database

CREATE  TABLE people( people_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50))

Table after Create Query executed

DROP -delete existing objects from the database or the database itself

DROP TABLE people

ALTER -update objects, altering the structures from the database

ALTER TABLE people ADD COLUMN phone VARCHAR(20)

Table after Alter Query executed

RENAME -rename an existing object in the database

RENAME TABLE people TO more_people

TRUNCATE -remove all records from a table

TRUNCATE TABLE more_people

3. Data Control Language (DCL)

This category has statements that controls access data and to the database. There are two important statements that falls to this category,

GRANT -assigns new privileges to a role or user account

GRANT SELECT, INSERT ON people TO people_admin

REVOKE -removes previously granted privileges (undoing GRANT statement)

REVOKE ALL PRIVILEGES ON people FROM people_admin

4. Data Manipulation Language (DML)

This one category responsible for adding, editing, or deleting data from the database. This category consists of six statements,

INSERT - insert new data into a table

INSERT INTO people (people_id, first_name, last_name, phone)
VALUES 
  ('9001', 'Johny', 'Doesnewer', '6852456'),
  ('9002', 'Starly', 'Dwikorasa', '8526549'),
  ('9003', '', '', '9874532')

UPDATE - updates existing data from the database

UPDATE people SET phone = 'unavailable' WHERE first_name = '' AND last_name =''

DELETE - deletes existing data from the database

DELETE FROM people WHERE people_id = 9003

LOCK -restrict access to a table, to undo the restriction, just use UNLOCK statement

LOCK TABLE people WRITE

CALL -call and execute previously defined or stored procedure

--create an example stored procedure
DELIMITER //

CREATE PROCEDURE GetPeopleDetails(IN PeoID INT)
BEGIN
    SELECT * FROM people WHERE people_id = PeoID;
END //

DELIMITER ;

--call the stored procedure
CALL GetPeopleDetails(your_people_id);

EXPLAIN -describe access path to data

EXPLAIN SELECT * FROM people WHERE people_id = '9001'

5. Transaction Control Language (TCL)

This category responsible to control and manage transactions to ensure data integrity and consistency, it has only two outputs, success or failure.

BEGIN -start a new transaction, short for BEGIN TRANSACTION statement.

BEGIN;
    UPDATE people SET first_name = 'Xavier' WHERE people_id = '9001';
    DELETE FROM people WHERE people_id = '9002';
COMMIT;

COMMIT - save all changes permanently made during the current transaction. This query can only be used after BEGIN statement, once a transaction is committed, it cannot be rolled back.

BEGIN;
    UPDATE people SET first_name = 'Xavier' WHERE people_id = '9001';
    DELETE FROM people WHERE people_id = '9002';
COMMIT;

ROLLBACK -undoes all changes made during the current transaction if you decide not to commit them. This query can only be used after BEGIN statement. It can also be used to revert to a savepoint within a transaction.

BEGIN;
    UPDATE people SET first_name = 'Javier' WHERE people_id = '9001';
    INSERT INTO people (people_id, first_name, last_name, phone)
    VALUES 
      ('9007', 'Joko', 'Suseno', '6888456');
ROLLBACK;

SAVEPOINT -sets a savepoint within a transaction, which is a point to which you can later roll back if needed.

BEGIN;
    INSERT INTO people (people_id, first_name, last_name, phone)
    VALUES 
      ('9007', 'Joko', 'Suseno', '6888456');
SAVEPOINT people_sp1;

NB: With this, now we can rollback to people_sp1 when we need it.

Most Important Statements

These are just some of most important statements I encountered so far:

SELECT: Used to retrieve data from a database.
INSERT: Used to add new data to a database.
UPDATE: Used to modify existing data in a database.
DELETE: Used to remove data from a database.
CREATE TABLE: Used to create a new table in a database.
ALTER TABLE: Used to modify the structure of an existing table.
DROP TABLE: Used to delete an entire table from a database.
WHERE: Used to filter rows based on a specified condition.
ORDER BY: Used to sort the result set in ascending or descending order.
JOIN: Used to combine rows from two or more tables based on a related column between them.

What’s Next?

Learn various techniques like WITH query (CTE), in order to improve more your query skills. Or you can learn another type of SQL like Server SQL (Oracle, Microsoft, and PostgreSQL) where you can learn STARTUP and SHUTDOWN statements. There are so many ways to advance your skills.

Conclusion

To wrap things up, Data Query Language (DQL) for querying data. Data Manipulation Language (DML) for editing data. Data Definition Language (DDL) for structuring data. Data Control Language (DCL) for administering the database. Transaction Control Language (TCL) for controlling transactions.

That’s all, thank you for reading my Basic SQL Concepts, see you next time for another articles. Stay tuned!