SQL BASIC

🗄️ SQL – Complete Core Notes

Relational Databases (RDBMS), DDL, DML, CRUD Operations, Clauses, Joins aur Aggregations ka comprehensive reference guide.

1️⃣ SQL Kya Hai?

Definition: SQL (Structured Query Language) ek standard programming language hai jo Relational Database Management Systems (RDBMS) jaise MySQL, PostgreSQL, Oracle aur SQL Server me data store, retrieve, update aur manage karne ke liye use hoti hai.

RDBMS Table Architecture: Relational Database me data Tables ke format me store hota hai:

• Row (Record / Tuple): Kisi ek single entity ka complete data (e.g., student ka pura record).
• Column (Field / Attribute): Data ki specific property (e.g., id, name, age).

• Standard Language: ANSI aur ISO standard database language hai.
• ACID Properties: Transactions ko secure, consistent aur reliable banati hai (Atomicity, Consistency, Isolation, Durability).
• Backend Backbone: Har dynamic web aur mobile application ka backend data access karne ke liye SQL use karta hai.

---

2️⃣ SQL Core CRUD Operations

CRUD Operation	SQL Command	Sub-Language Category	Example Syntax
Create (Insert)	INSERT INTO	DML (Data Manipulation)	INSERT INTO students (id, name, age) VALUES (1, 'Rahul', 20);
Read (Select)	SELECT	DQL (Data Query)	SELECT * FROM students;
Update (Modify)	UPDATE	DML (Data Manipulation)	UPDATE students SET age = 21 WHERE id = 1;
Delete (Remove)	DELETE	DML (Data Manipulation)	DELETE FROM students WHERE id = 1;

---

3️⃣ Creating a Table & Data Types

Database me naya structured table create karne ke liye CREATE TABLE statement use kiya jata hai:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data Type / Constraint	Description	Example
INT	Whole numbers (integers) store karta hai	101, -5
VARCHAR(n)	Variable-length text string (max length = n)	'Rahul Sharma'
PRIMARY KEY	Har row ko uniquely identify karta hai (Not Null + Unique)	id INT PRIMARY KEY
NOT NULL	Field ko khali (NULL) chhodne se rokta hai	name VARCHAR(50) NOT NULL

---

4️⃣ Selecting & Filtering Data

-- Sabhi columns select karna
SELECT * FROM students;

-- Specific columns select karna
SELECT name, age FROM students;

-- WHERE clause se conditional filtering
SELECT * FROM students WHERE age > 18;
SELECT * FROM students WHERE age >= 18 AND name = 'Rahul';
SELECT * FROM students WHERE name LIKE 'R%'; -- R se start hone wale naam

Interactive SQL Query Execution Simulator

-- Click any query button above to simulate executing SQL on `students` table...
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | Rahul | 20 |
| 2 | Anita | 22 |
| 3 | Ravi | 19 |
| 4 | Pooja | 24 |
+----+-------+-----+

---

5️⃣ Sorting & Aggregate Functions

1. Sorting (ORDER BY):

SELECT * FROM students ORDER BY age ASC;   -- Chhote se bada (Default)
SELECT * FROM students ORDER BY name DESC; -- Z se A (Descending)

2. Aggregate Functions:

Function	Description	Syntax Example
COUNT()	Total matching rows ki ginti karta hai	SELECT COUNT(*) FROM students;
SUM()	Numeric column ka total grand sum nikalta hai	SELECT SUM(marks) FROM exam;
AVG()	Numeric column ka average calculate karta hai	SELECT AVG(age) FROM students;
MAX() / MIN()	Highest aur Lowest value return karta hai	SELECT MAX(age), MIN(age) FROM students;

---

6️⃣ SQL Joins (Combining Multiple Tables)

Jab do alag-alag tables ke beech related column (Primary Key ↔ Foreign Key) ho, tab data jodne ke liye JOIN use hota hai:

Join Type	Output Condition
INNER JOIN	Sirf wahi rows aati hain jo dono tables me match karti hain.
LEFT JOIN	Left table ki saari rows + Right table ki sirf matching rows.
RIGHT JOIN	Right table ki saari rows + Left table ki sirf matching rows.

-- INNER JOIN Example: Students aur unke Exam Marks fetch karna
SELECT students.name, marks.score, marks.subject
FROM students
INNER JOIN marks ON students.id = marks.student_id;

💡 Crucial Safety Tip: UPDATE ya DELETE command chalate waqt hamesha WHERE clause verify karein. Bina WHERE ke command chalane par poore table ka sara data ek sath update ya delete ho jayega!