SQL_Structure

27 Aug, 2025

This part is generally taught and learnt at the very start of learning SQL or any DB in general. I too did learn it before hand but I kind of prefer to actually get the hang of what DB is and how its used. I've already covered the basics specially CRUD.

Now since I've done CRUD, I know what to expect in terms of "data", and how we are actually handle data. Since I already know what to expect and how handling data looks and feels, learning structure now feels less of theory and more of "why actually we need it ?...".

Long story short I prefer a more hands on practical approach than theory so it was better to get my hands dirty with actual data handling using CRUD and then learn stuff like "schema", "DDL" and "DML".

Lets Begin !

🧪 Data Definition Language (DDL)

A very crucial part of any DB. DDL generally refers to a set of Clauses or instruction that shape how data will get stored.

For example, CREATE is a part of DDL as its use to "define" a table. We create a table using that clause, we are "defining" how a table, a container to store data will look like. We are writing its definition, we are describing how it should contain data.

CREATE TABLE new(id INTEGER PRIMARY KEY, name TEXT NOT NULL);

In the above example we are creating a table called "new". We are defining the structure of how table "new" will look like.

It will have a id column which is a integer and set as PRIMARY KEY, it also contains a name column which is can store TEXT values and cannot be NULL !

With that line we basically defined the whole thing the structure, what to store, what not to store in the table everything. And to do this CREATE is used which is a data definition language.

We can also add additional columns to existing tables.

We can use the ALTER clause to do that. The ALTER clause is also part of DDL which allows us to add columns to a table.

ALTER TABLE new ADD COLUMN age INTEGER;

This will add a age column which can store integer values.

The general syntax for ALTER is:

ALTER TABLE <table_name> ADD COLUMN <new_column> <column_data_type>

We can add multiple columns by separating them using , (comma).

⛏ Data Manipulation Language (DML)

Similar to DDL its also generally refers to a set of Clauses or instruction but to Manipulate data. This means that its used to change or do any kind of manipulation to existing data.

We are already very much familiar with this. CRUD !

Doing CRUD is manipulating data, we create data, read them, update them, delete them. Its data manipulation. That's it !

If you know CRUD you already know DML. Its simple AF !

Just for formality here is an insert and update query:

• INSERT QUERY:

INSERT INTO new VALUES(1, 'Nexus', 22);

• UPDATE QUERY:

UPDATE new SET name = 'Niko', age = 30 WHERE id = 1;

THATS IT !

That's DML, really ! 🙃

🧱 Schema

Schema is the term used to refer to the structure of a table. Each row, constraint, relation, join, everything is part of a schema. If a table exists then it is built on and has a valid schema.

Lets briefly see the parts of a schema:

📐 Constraints

These define how data in a particular row will behave. These are Clauses used when creating a table and we are very familiar with some.

There maybe even more but for now we only work on these. And already worked on multiple ones so no need to show here, will see FOREIGN KEY later and a quick code for how CHECK is used ->

age INT CHECK (age >= 18); /*Only allows 18 and 18+ ages*/

🕸 Relations

Finally "RELATIONAL DBMS" lol, This is a core reason why its called "relational". Relations literally mean connecting 2 or more tables.

Why would someone want to do that ? Because of modularity and reducing redundancy and increasing efficiency. I know too much technical words lets understand in "human understandable" manner !

Forget relations for a bit and think this ->

We already have a DB called gameShop and inside it we have a table called customers. It stores customers' data, Good enough.

Now I will need a way to store orders too right (its a game shop database) ? Spoiler alert : YES ! We will make another table for orders and store the orders there, till now good. Now for every order we need to determine which customer placed that order.

How can we do it ? We can store the id that is used in the customers table in each order row. But then we got redundant values (redundant means duplicate) across 2 tables, this may seem harmless but imagine 2 more tables using and same id from customers table ? Now for every entry id will be repeated 4 times which is not good.

So rather than having standalone tables we connect tables tables and we say we got a "relation" between 2 tables.

• This helps in modularity (We don't store everything in one table so its easy to manage).
• This reduces redundancy (We just reference to the id rather than storing itself for the other table).
• This increases efficiency (The whole structure is less cluttered, workflows become easier and faster).

We generally use FOREIGN KEY to connect 2 tables using the PRIMARY KEY.

We definately will learn how to connect 2 tables but not now, LATER.

⏺ Attributes and Records

• Attributes: Fancy name for columns in a table. They describe the properties of data (like id, name, age).

• Records (or tuples): Fancy name for rows in a table. Each row is one record containing values for each attribute.

...And we are done with at least the core concept of structure.

Follow ORDER BY to continue from here.

Browse Full SQL Page.

#SQL on the go