A Beginner’s Guide to MySQL and SQL Sub-Languages

MySQL is one of the most widely used open-source relational database management systems (RDBMS) in the world. It’s fast, reliable, and forms the backbone of countless web applications, including Facebook, Twitter, and YouTube. At the heart of MySQL is SQL (Structured Query Language) — a standard language used for accessing and manipulating databases.

In this blog, we’ll explore the fundamentals of SQL and dive into its sub-languages that make MySQL such a powerful tool for data management.

🧠 Why SQL?

SQL (pronounced as “sequel”) was developed by IBM in the 1970s and became an ANSI and ISO standard in the 1980s. It allows developers to easily:

Add, modify, and delete data
Retrieve specific information quickly
Manage user access and control data transactions

SQL is case-insensitive, making it user-friendly for beginners.

🛠️ The 5 Sub-Languages of SQL

SQL is categorized into five core sub-languages, each serving a specific purpose:

1. DDL – Data Definition Language

DDL commands are used to define and modify the structure of a database. These commands are auto-committed, meaning changes are permanent once executed.

Common DDL Commands:

CREATE: Creates a new table.
ALTER: Modifies an existing table (e.g., add or change columns).
RENAME: Changes the name of a table.
DROP: Deletes a table permanently.
TRUNCATE: Deletes all rows but retains the table structure.

🧾 Example:

sqlCopyEditCREATE TABLE Student (
  Roll_number INTEGER(5),
  Name VARCHAR(30),
  Age INTEGER(3),
  City VARCHAR(30)
);

2. DML – Data Manipulation Language

DML commands are used to modify data in a database. They are not auto-committed, which means you can roll back changes if needed.

Key DML Commands:

INSERT: Adds new records.
UPDATE: Modifies existing records.
DELETE: Removes records.

🧾 Example:

sqlCopyEditINSERT INTO Student VALUES (1, 'Rohini', 20, 'Delhi');

3. DQL – Data Query Language

Used solely for querying data from the database using the SELECT command.

🧾 Example:

sqlCopyEditSELECT * FROM Student WHERE Name = 'Rohini';

4. DCL – Data Control Language

DCL is about permissions and access control. It determines who can access or modify data.

DCL Commands:

GRANT: Gives access rights to users.
REVOKE: Removes access rights.

🧾 Example:

sqlCopyEditGRANT ALL ON Salary_detail TO 'Ashish'@'localhost';

5. TCL – Transaction Control Language

TCL commands manage database transactions and are used with DML commands to control data integrity.

TCL Commands:

COMMIT: Saves changes permanently.
ROLLBACK: Undoes changes since the last commit.
SAVEPOINT: Creates a point to roll back to later.

🧾 Example:

sqlCopyEditINSERT INTO Student VALUES (2, 'Rajat', 21, 'Delhi');
COMMIT;

✅ Summary

Sub-language	Purpose	Common Commands
DDL	Define database structure	`CREATE`, `ALTER`, `DROP`
DML	Manage data records	`INSERT`, `UPDATE`, `DELETE`
DQL	Query data	`SELECT`
DCL	Access control	`GRANT`, `REVOKE`
TCL	Manage transactions	`COMMIT`, `ROLLBACK`, `SAVEPOINT`

🔗 Understanding SQL JOINs in MySQL

In real-world applications, data is often spread across multiple tables. JOINs allow you to combine rows from two or more tables based on related columns, providing a holistic view of your data.

📚 Types of SQL JOINs

INNER JOIN – Returns only the matching rows from both tables.
LEFT JOIN (LEFT OUTER JOIN) – Returns all rows from the left table, and matched rows from the right table. Unmatched right-side rows will have NULLs.
RIGHT JOIN (RIGHT OUTER JOIN) – Returns all rows from the right table, and matched rows from the left table.
FULL JOIN (FULL OUTER JOIN) – Returns rows when there is a match in one of the tables. Not directly supported in MySQL but can be simulated using UNION.
CROSS JOIN – Returns the Cartesian product of both tables (every row from the first table joined with every row from the second).

🔍 Example Scenario

Let’s say we have two tables:

`Student` Table

Roll_number	Name	Age	City
1	Raj	20	Delhi
2	Priya	21	Mumbai

`Marks` Table

Roll_number	Subject	Score
1	Math	85
2	English	90

1. INNER JOIN

Returns students who have corresponding marks.

sqlCopyEditSELECT Student.Name, Marks.Subject, Marks.Score
FROM Student
INNER JOIN Marks ON Student.Roll_number = Marks.Roll_number;

🟢 Result:

Name	Subject	Score
Raj	Math	85
Priya	English	90

2. LEFT JOIN

Returns all students, with their marks if available.

sqlCopyEditSELECT Student.Name, Marks.Subject, Marks.Score
FROM Student
LEFT JOIN Marks ON Student.Roll_number = Marks.Roll_number;

🟢 Result:

Name	Subject	Score
Raj	Math	85
Priya	English	90

(If a student had no marks, their score would be NULL.)

3. RIGHT JOIN

Returns all marks records, with student names if available.

sqlCopyEditSELECT Student.Name, Marks.Subject, Marks.Score
FROM Student
RIGHT JOIN Marks ON Student.Roll_number = Marks.Roll_number;

🟢 *Same result as INNER JOIN here, but shows more rows if Marks table had extra entries.

4. FULL JOIN (Simulated)

Combine all records from both tables, even if there is no match.

sqlCopyEditSELECT Student.Name, Marks.Subject, Marks.Score
FROM Student
LEFT JOIN Marks ON Student.Roll_number = Marks.Roll_number
UNION
SELECT Student.Name, Marks.Subject, Marks.Score
FROM Student
RIGHT JOIN Marks ON Student.Roll_number = Marks.Roll_number;

5. CROSS JOIN

Returns every combination of students and subjects.

sqlCopyEditSELECT Student.Name, Marks.Subject
FROM Student
CROSS JOIN Marks;

✅ When to Use JOINs?

Use FULL JOIN to merge everything.

Use INNER JOIN when you only want matching records.

Use LEFT JOIN to keep all data from the left table.

Use RIGHT JOIN if the right table has mandatory data to retain.

Use CROSS JOIN for combinatoric data generation.

❓ Frequently Asked Questions

Q1: What are the five types of SQL sub-languages?
A: DDL, DML, DQL, DCL, and TCL.

Q2: What is the difference between DDL and DML?
A: DDL modifies the structure of the database (e.g., creating tables), while DML deals with the data inside those tables (e.g., inserting rows).

Q3: What are the two main categories of SQL?
A: DDL and DML are often considered the primary functional categories of SQL.

Watch the video for your reference part -1 :