Types of SQL
වර්ගීකරණය
DDL
Structure
Defines the structure (tables, databases).
DML
Data
Manipulates the data inside tables.
Analogy
DDL = Building the house.
DML = Moving furniture inside.
DML = Moving furniture inside.
1. Create Database
Create a container for your data.
CREATE DATABASE school_db;
2. Create Table
Define columns and data types.
CREATE TABLE student (
id INT,
name VARCHAR(50)
);
Types
INT = Numbers
VARCHAR = Text
VARCHAR = Text
3. Alter Table
Modify existing structure.
ALTER TABLE student
ADD COLUMN email VARCHAR(100);
ALTER TABLE student
DROP COLUMN age;
4. Keys
Primary Key: Unique ID (like NIC).
CREATE TABLE staff (
id INT PRIMARY KEY,
name STRING
);
1. Insert Data
ඇතුළත් කිරීම
INSERT INTO student (id, name)
VALUES (10, 'Kamal');
Note Order matters! First value goes to first column.
2. Update Data
වෙනස් කිරීම
UPDATE student
SET name = 'Sunil'
WHERE id = 1;
Warning
Always use WHERE or you will update ALL rows!
3. Delete Data
මැකීම
DELETE FROM student
WHERE id = 1;
4. Select (View)
දත්ත බැලීම
View all data:
SELECT * FROM student;
Filter with Condition:
SELECT * FROM student
WHERE age > 20;
5. Joins
සම්බන්ධ කිරීම
Combine two tables based on a common column.
SELECT s.name, c.course
FROM student AS s
JOIN courses AS c
ON s.cid = c.id;
1. Aggregates
ගණනය කිරීම්
SELECT COUNT(*) FROM student;
SELECT AVG(marks) FROM student;
2. Group By
කාණ්ඩ කිරීම
Group data by a category (e.g., city).
SELECT city, COUNT(*)
FROM customers
GROUP BY city;
3. Order By
පිළිවෙල සැකසීම
SELECT * FROM student
ORDER BY marks DESC;
DESC = High to Low
ASC = Low to High
ASC = Low to High
4. Subqueries
උප විමසුම්
Find student with Max age:
SELECT name FROM student
WHERE age = (
SELECT MAX(age) FROM student
);
5. Wildcards
රටා ගැලපීම
Find names starting with 'S':
SELECT * FROM student
WHERE name LIKE 'S%';