SQL for Data Science

What is SQL
Ans: Structured Query Language 

DBMS = Data base management System

MySQL is DBMS

RDBMS = Relational based database management system =  basically data alg alg column me hota hai use merge krne ke liye relation to hona chahiye use maintain krna hi RDBMS, Data me primary key aur foreign key hoti hai

RDBMS (Relational Database Management System)
RDBMS ek aisa database management system hota है jisme data tables (rows + columns) ke form me store kiya जाता है, aur tables relationships ke through connected hote हैं.

---

⭐ RDBMS Kya Hotा Hai?

RDBMS = Relational Database Management System
Ye ek software hotा है jo:

• Data ko tables me store karta है

• Tables ke beech relationships banata है

• Data ko CRUD operations (Create, Read, Update, Delete) se manage karta है

• SQL language ka use karta है

📌 RDBMS Ka Structure

1️⃣ Table

• Data rows (records) aur columns (fields) me hotा है
  Example: Customers table

2️⃣ Row (Record / Tuple)

• Table ka ek complete data entry
  Example: Ek customer ki puri details

3️⃣ Column (Field / Attribute)

• Table ka specific data point
  Example: Name, Email, Age

4️⃣ Primary Key

• Ek unique column jo har row ko identify kare
  Example: Customer_ID

5️⃣ Foreign Key

• Ek table ka column jo dusre table ki primary key ko refer kare

• Dono tables ke beech relation banata hai

>>. ER Diagram (Entity–Relationship Diagram)

⭐ ER Diagram Kya Hotा Hai?

ER Diagram एक visual diagram होता है जो दिखाता है:

• कौन-कौन सी tables होंगी (Entities)

• Unke columns kya होंगे (Attributes)

• Tables kaise connected होंगी (Relationships)

• Kaun Primary Key hai

• Kaun Foreign Key hai

>>>Constraint (कन्स्ट्रेंट)
Database में constraint वो rules होते हैं जो ensure करते हैं कि डेटा हमेशा सही, valid, consistent और secure रहे।

ये RDBMS में लगने वाले restrictions होते हैं ताकि गलत या invalid value insert न हो सके।

---

⭐ Constraint क्या करता है?

• गलत data को रोकता है

• data consistency maintain करता है

• tables के बीच सही relationship बनाता है

• duplicate, null, invalid values को avoid करता है

---

⭐ Types of Constraints (सबसे important)

1️⃣ PRIMARY KEY Constraint

• हर row को uniquely identify करता है

• Null नहीं हो सकता

• Duplicate नहीं हो सकता

Example:

PRIMARY KEY (customer_id)

---

2️⃣ FOREIGN KEY Constraint

• दो tables को जोड़ता है

• दूसरी table की primary key को refer करता है

Example:

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

---

3️⃣ UNIQUE Constraint

• value unique होनी चाहिए

• NULL allowed है (except some DBs)

Example:

UNIQUE (email)

---

4️⃣ NOT NULL Constraint

• Column कभी empty नहीं हो सकता

Example:

name VARCHAR(50) NOT NULL

---

5️⃣ CHECK Constraint

• Condition enforce करता है

• गलत values को रोकता है

Example:

CHECK (age >= 18)

---

6️⃣ DEFAULT Constraint

• Value ना मिले तो default value डाल देता है

Example:

status VARCHAR(10) DEFAULT 'Active'

---

⭐ Simple Example (All Constraints)

CREATE TABLE Students(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    email VARCHAR(100) UNIQUE,
    course VARCHAR(50) DEFAULT 'BCA'
);



>>Commands of SQL 📘 SQL Commands – Notes
⭐ 1) DDL – Data Definition Language

Structure (table/database/schema) create–modify–delete करने के commands

Command	काम
CREATE	नई table/database बनाना
ALTER	existing table में column add/modify/drop
DROP	table/database permanent delete
TRUNCATE	table का data delete (structure same)
RENAME	table का नाम बदलना
Example:
CREATE TABLE Students(id INT, name VARCHAR(50));
ALTER TABLE Students ADD age INT;
DROP TABLE Students;

⭐ 2) DML – Data Manipulation Language

Data insert, update, delete करने के commands

Command	काम
INSERT	New data डालना
UPDATE	Existing data बदलना
DELETE	Data delete करना
Example:
INSERT INTO Students VALUES (1, 'Rahul', 20);
UPDATE Students SET age = 21 WHERE id = 1;
DELETE FROM Students WHERE id = 1;

⭐ 3) DQL – Data Query Language

Data fetch/read करने का command

Command	काम
SELECT	Data read करना
Example:
SELECT name, age FROM Students;

⭐ 4) TCL – Transaction Control Language

Transaction को manage करने वाले commands (banking-type operations)

Command	काम
COMMIT	Changes permanently save
ROLLBACK	Undo previous changes
SAVEPOINT	Intermediate rollback point बनाना
Example:
COMMIT;
ROLLBACK;
SAVEPOINT point1;

⭐ 5) DCL – Data Control Language

Permissions और security control करना

Command	काम
GRANT	Permission देना
REVOKE	Permission वापस लेना
Example:
GRANT SELECT ON Students TO user1;
REVOKE SELECT ON Students FROM user1;

⭐ SQL Commands – 1 Page Summary (Exam-Ready)
Category	Commands	काम
DDL	CREATE, ALTER, DROP, TRUNCATE, RENAME	Structure define/change
DML	INSERT, UPDATE, DELETE	Data change
DQL	SELECT	Data read
TCL	COMMIT, ROLLBACK, SAVEPOINT	Transaction control
DCL	GRANT, REVOKE	Permission control

NOTE: SQL IS INSENSITIVE LANGUAGE, MEANS LOWERCASE/UPPERCASE DOESN'T MATTER
-- DOUBLE HYPHEN FOR WRITE COMMENTS 
THERE IS NO CONCEPT OF NEWLINE/INDENTATION IN SQL

DATA DEFINITION LANGUAGE :
1) Create: 
Create Database NewDatabase; --I'm creating a database and command will end with ; semicolon 
use NewDatabase; --I'm in this database
Show table: --it will show tables under this database 
Create table myTable; --it will create table with name myTable


NOTE: REST DDL COMMANDS NOTES WILL BE SAME AS PER DATA ANALYSIS SQL