Structured query language

Module 1: Database na Enna? (Introduction)

Database (DB): Idhu data-va store panni vekkura oru digital idam. Udharanam: Unga phone contacts, bank account details.

SQL (Structured Query Language): Idhu andha database kitta pesuradhukku use aagura oru 'mozhi' (language). Data-va paaka, serkka, azhikka, maatha use aagum.

RDBMS (Relational Database): Idhu data-va 'Tables' (like Excel sheets) la store pannum. E.g., MySQL, PostgreSQL, MS SQL Server.

Table: Idhu rows (varisaigal) and columns (niralkal) konda data set.

Module 2: Data-va Eppadi Paakuradhu? (SELECT, FROM, WHERE)

Example 1: Ella students details-ayum paaka

SELECT * FROM Students;

(* 'asterisk' na 'ella columns' nu artham)

Example 2: Students-oda paer matrum city mattum paaka

SELECT FirstName, City FROM Students;

Example 3: Chennai-la irukkura students-a mattum paaka

SELECT * FROM Students WHERE City = 'Chennai';

Example 4: 20 vayasukku maela irukkura students

SELECT * FROM Students WHERE Age > 20;

Module 3: Data-va Filter Pannuradhu (Advanced Filters)

Example 1: AND (Rendum unmaiya irukkanum)

Chennai-la irukkura, 20 vayasulla students.

SELECT * FROM Students
WHERE City = 'Chennai' AND Age = 20;

Example 2: OR (Edhavadhu onnu unmaiya irundha podhum)

Chennai 'alladhu' Madurai-la irukkura students.

SELECT * FROM Students
WHERE City = 'Chennai' OR City = 'Madurai';

Example 3: IN (Pala values-a check panna)

Short-a eppadi eludhalam (OR-ku badhil):

SELECT * FROM Students
WHERE City IN ('Chennai', 'Madurai');

Example 4: LIKE (Pattern matching)

'K' la aarambikura paer (FirstName) konda students.

SELECT * FROM Students
WHERE FirstName LIKE 'K%';

(% na 'edhu venalum irukkalam' nu artham)

Example 5: BETWEEN (Oru range-kulla)

Age 20-kum 21-kum naduvula (rendum serthu).

SELECT * FROM Students
WHERE Age BETWEEN 20 AND 21;

Module 4: Data-va Varisai Paduthuradhu (Sorting & Limiting)

Example 1: ORDER BY (Varisai paduthu)

Students-a avanga Age (vayasu) padi varisai paduthu (chinnadhula irundhu perusu - ASC).

SELECT * FROM Students
ORDER BY Age;

(Default-a 'ASC' - Ascending order)

Example 2: ORDER BY DESC (Perusula irundhu chinnadhu)

Students-a avanga Age padi 'DESC' (Descending) order-la varisai paduthu.

SELECT * FROM Students
ORDER BY Age DESC;

Example 3: LIMIT (Konjam data mattum)

Mudhal 3 students-a mattum kaatu.

SELECT * FROM Students
LIMIT 3;

Example 4: DISTINCT (Duplicate illama)

Students irukkura oorgal (Cities) pattiyal (duplicate illama).

SELECT DISTINCT City FROM Students;

Module 5: Data-va Surokkama Paakuradhu (Aggregate Functions)

Example 1: COUNT

Mothama ethana students irukkanga?

SELECT COUNT(*) FROM Students;

Example 2: COUNT with WHERE

Chennai-la motham ethana students?

SELECT COUNT(*) FROM Students WHERE City = 'Chennai';

Example 3: SUM

Motha marks (ellam courses serthu) evlo?

SELECT SUM(Marks) FROM Courses;

Example 4: AVG, MAX, MIN

Python course-oda sarasari (AVG), adhigapatcha (MAX), kuraINDhapatcha (MIN) marks.

SELECT AVG(Marks), MAX(Marks), MIN(Marks)
FROM Courses
WHERE CourseName = 'Python';

Example 5: NULL values

Note: Aggregate functions (SUM, AVG, etc.) `NULL` values-a ignore pannidum. `COUNT(*)` mattum `NULL` row-ayum serthu ennum.

`Courses` table-la, `COUNT(Marks)` 5 nu varum, aana `COUNT(*)` 6 nu varum (Ram-oda row serthu).

SELECT COUNT(*), COUNT(Marks) FROM Courses;

Module 6: Data-va Group Pannuradhu (GROUP BY, HAVING)

Example 1: GROUP BY

Ovvoru city-layum ethana students irukkanga?

SELECT City, COUNT(*)
FROM Students
GROUP BY City;

Example 2: GROUP BY (multiple columns)

Ovvoru city-la, ovvoru age-layum ethana students?

SELECT City, Age, COUNT(*)
FROM Students
GROUP BY City, Age;

Example 3: GROUP BY with Aggregates

Ovvoru course-kum sarasari (AVG) mark evlo?

SELECT CourseName, AVG(Marks)
FROM Courses
GROUP BY CourseName;

Example 4: HAVING

Sarasari (AVG) mark 85-ku maela irukkura courses mattum.

SELECT CourseName, AVG(Marks)
FROM Courses
GROUP BY CourseName
HAVING AVG(Marks) > 85;

Module 7: Pala Tables-a Serkuradhu (JOINs)

Example 1: INNER JOIN

Rendu table-layum match aagura data-va mattum eduthu serkkum.

Student paer (Students table) matrum avanga edutha course (Courses table) serthu paaka.

SELECT S.FirstName, S.City, C.CourseName, C.Marks
FROM Students S
INNER JOIN Courses C ON S.StudentID = C.StudentID;

(Inga `S` and `C` enbadhu tables-ku namma kudukura 'short names' (alias))

Example 2: LEFT JOIN

Idhadhu pakkam (LEFT) irukkura table (Students) oda ella data-vum varum. Valadhu pakkam (Courses) match irundha, adha kaatum, illana `NULL` kaatum.

Ella students-ayum kaatu, avanga course eduthurundha adhayum kaatu (course edukkalana `NULL` varum).

SELECT S.FirstName, C.CourseName, C.Marks
FROM Students S
LEFT JOIN Courses C ON S.StudentID = C.StudentID;

(Indha example-la, `Students` table-la irukkura ellarume `Courses` table-layum irukuradhala, result `INNER JOIN` madhiriye irukkum. Aana oru student course edukalana, LEFT JOIN la avanga paer `NULL` course-oda varum.)

Module 8: Data-va Maathuradhu (INSERT, UPDATE, DELETE)

Warning: Indha commands data-va maathidum. Romba gavanama use pannanum!

Example 1: INSERT (Pudhu data-va serkka)

Pudha oru student-a serkka.

INSERT INTO Students (StudentID, FirstName, City, Age)
VALUES (106, 'Mani', 'Trichy', 22);

Example 2: UPDATE (Irukkura data-va maatha)

Arun (ID 103) oda city-a 'Coimbatore'-ku maatha.

UPDATE Students
SET City = 'Coimbatore'
WHERE StudentID = 103;

(GAVANAM: `WHERE` clause illama `UPDATE` pannadhaenga! Ella rows-um maaridum!)

Example 3: DELETE (Data-va azhikka)

Mani (ID 106) student record-a azhikka.

DELETE FROM Students
WHERE StudentID = 106;

(GAVANAM: `WHERE` clause illama `DELETE` pannadhaenga! Ella data-vum azhinjirum!)

Bonus Module: Tables-a Urvakkuradhu (CREATE, DROP)

Example 1: CREATE TABLE

Pudusa oru table (e.g., `Teachers`) urvakka.

CREATE TABLE Teachers (
    TeacherID INT,
    TeacherName VARCHAR(100),
    Department VARCHAR(50)
);

(`INT` na number, `VARCHAR` na text/string)

Example 2: DROP TABLE

Oru table-a full-a azhikka (data + structure).

DROP TABLE Teachers;