## ✅ Q-1: Create DB, table with types, insert, display with types

sql
-- Open SQLite Command Line and create DB
sqlite3 mydb1.db

-- Create table T1
CREATE TABLE T1 (
  c1 TEXT,
  c2 NULL,
  c3 INT,
  c4 NUMERIC,
  c5 FLOAT,
  c6 VARCHAR(20),
  c7 BLOB,
  c8 DATE,
  c9 BOOLEAN,
  c10,           -- column with no type
  c11 REAL
);

-- Insert values using different manifest types
INSERT INTO T1 VALUES (
  'Hello', NULL, 100, 123.45, 3.14, 'Test', X'53514C697465', '2025-07-30', 1, 'NoType', 98.76
);

-- Display data
.mode column
SELECT * FROM T1;

-- Show data types using typeof()
SELECT 
  typeof(c1) AS c1_type,
  typeof(c2) AS c2_type,
  typeof(c3) AS c3_type,
  typeof(c4) AS c4_type,
  typeof(c5) AS c5_type,
  typeof(c6) AS c6_type,
  typeof(c7) AS c7_type,
  typeof(c8) AS c8_type,
  typeof(c9) AS c9_type,
  typeof(c10) AS c10_type,
  typeof(c11) AS c11_type
FROM T1;


---

## ✅ Q-2: Transactions with COMMIT & ROLLBACK

sql
-- Create table for transaction
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  name TEXT,
  balance INTEGER
);

-- Insert sample accounts
INSERT INTO accounts (name, balance) VALUES ('Alice', 5000), ('Bob', 3000);

-- Transfer 1000 from Alice to Bob using COMMIT
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Bob';
COMMIT;

-- Check balances
SELECT * FROM accounts;

-- Try increasing 10% temporarily using ROLLBACK
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance * 1.1;
SELECT * FROM accounts; -- see temporary increase
ROLLBACK;

-- Verify rollback
SELECT * FROM accounts;


---

## ✅ Q-3: Filtering Queries with Example

Create sample data:

sql
CREATE TABLE students (
  id INTEGER,
  name TEXT,
  marks INTEGER,
  subject TEXT
);

INSERT INTO students VALUES
(1, 'Alice', 75, 'Math'),
(2, 'Bob', 85, 'Math'),
(3, 'Alice', 90, 'Science'),
(4, 'David', 80, 'Math'),
(5, NULL, 70, 'Science'),
(6, 'Eva', NULL, 'Science');


### Filtering Examples:

sql
-- DISTINCT
SELECT DISTINCT name FROM students;

-- WHERE
SELECT * FROM students WHERE marks > 80;

-- BETWEEN
SELECT * FROM students WHERE marks BETWEEN 70 AND 85;

-- IN
SELECT * FROM students WHERE name IN ('Alice', 'Eva');

-- LIKE
SELECT * FROM students WHERE name LIKE 'A%';

-- UNION
SELECT name FROM students WHERE subject = 'Math'
UNION
SELECT name FROM students WHERE subject = 'Science';

-- INTERSECT
SELECT name FROM students WHERE subject = 'Math'
INTERSECT
SELECT name FROM students WHERE subject = 'Science';

-- EXCEPT
SELECT name FROM students WHERE subject = 'Math'
EXCEPT
SELECT name FROM students WHERE subject = 'Science';

-- LIMIT
SELECT * FROM students LIMIT 3;

-- IS NULL / IS NOT NULL
SELECT * FROM students WHERE name IS NULL;
SELECT * FROM students WHERE marks IS NOT NULL;

-- GROUP BY + aggregate
SELECT subject, AVG(marks) AS avg_marks FROM students GROUP BY subject;

-- ORDER BY
SELECT * FROM students ORDER BY marks DESC;

-- CASE (Conditional logic)
SELECT name, marks,
  CASE 
    WHEN marks >= 85 THEN 'A'
    WHEN marks >= 70 THEN 'B'
    ELSE 'C'
  END AS Grade
FROM students;

Here is the full SQLite-compatible SQL code with *composite keys, **alter table, and **PRAGMA foreign\_keys* for the operations asked:

---

### ✅ Step-by-step SQL Queries:

sql
-- Enable foreign key constraints in SQLite
PRAGMA foreign_keys = ON;

-- 1) Create STUDENT table with Composite Primary Key (rno, year)
CREATE TABLE STUDENT (
    rno INTEGER,
    year INTEGER,
    name TEXT,
    PRIMARY KEY (rno, year)
);

-- Create RESULT table with Composite Foreign Key (rno, year)
CREATE TABLE RESULT (
    rno INTEGER,
    year INTEGER,
    percentage REAL,
    FOREIGN KEY (rno, year) REFERENCES STUDENT(rno, year)
);


---

sql
-- 2) Insert minimum 5 records in both tables
INSERT INTO STUDENT (rno, year, name) VALUES
(1, 2023, 'Amit'),
(2, 2023, 'Bhavna'),
(3, 2023, 'Chirag'),
(4, 2023, 'Disha'),
(5, 2023, 'Esha');

INSERT INTO RESULT (rno, year, percentage) VALUES
(1, 2023, 85.5),
(2, 2023, 78.0),
(3, 2023, 90.2),
(4, 2023, 67.4),
(5, 2023, 72.8);


---

sql
-- 3) Add new column 'mobnum' in STUDENT table
ALTER TABLE STUDENT ADD COLUMN mobnum TEXT;

-- 4) Rename column 'mobnum' to 'mobile'
-- ⚠ SQLite does not support RENAME COLUMN directly before version 3.25.0
-- So we will use this if supported:
ALTER TABLE STUDENT RENAME COLUMN mobnum TO mobile;


---

sql
-- 5) Insert mobile number 9876543210 for roll no 1
UPDATE STUDENT SET mobile = '9876543210' WHERE rno = 1 AND year = 2023;

-- 6) Drop column 'mobile' from STUDENT table
-- ⚠ SQLite doesn't support DROP COLUMN directly, so workaround needed:
-- Create new table without mobile column, copy data, drop old table, rename new one

CREATE TABLE STUDENT_NEW (
    rno INTEGER,
    year INTEGER,
    name TEXT,
    PRIMARY KEY (rno, year)
);

INSERT INTO STUDENT_NEW (rno, year, name)
SELECT rno, year, name FROM STUDENT;

DROP TABLE STUDENT;

ALTER TABLE STUDENT_NEW RENAME TO STUDENT;


---

sql
-- 7) Delete one record from STUDENT table (whose reference exists in RESULT)
-- This will fail due to foreign key constraint (which is expected)
DELETE FROM STUDENT WHERE rno = 2 AND year = 2023;
-- 🔴 This will give an error: FOREIGN KEY constraint failed

-- 8) Change roll no of any one student (whose reference exists in RESULT)
-- This will also fail due to foreign key constraint (no ON UPDATE CASCADE)
UPDATE STUDENT SET rno = 10 WHERE rno = 3 AND year = 2023;
-- 🔴 This will give an error: FOREIGN KEY constraint failed


---

### 🔍 Notes:

* SQLite does not allow altering primary/foreign key constraints after creation — you must recreate the table for such changes.
* PRAGMA foreign_keys = ON must be used every time you connect if foreign keys are to work.
* Column rename/drop operations are limited in older SQLite versions.

---

Would you like the same example for *MySQL* or *SQL Server* too?

---

## ✅ Q-4: JOINS in SQLite

sql
-- Table 1: employees
CREATE TABLE employees (
  emp_id INTEGER,
  name TEXT,
  dept_id INTEGER
);

-- Table 2: departments
CREATE TABLE departments (
  dept_id INTEGER,
  dept_name TEXT
);

-- Insert data
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 30);
INSERT INTO departments VALUES (10, 'HR'), (20, 'IT'), (40, 'Finance');

-- INNER JOIN
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- NATURAL JOIN
SELECT * FROM employees NATURAL JOIN departments;

-- LEFT JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- RIGHT JOIN (simulate using LEFT JOIN + reverse)
SELECT d.dept_name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id;

-- FULL OUTER JOIN (simulate using UNION)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id;

-- CROSS JOIN
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- SELF JOIN (example: employee with same department)
SELECT e1.name AS Employee1, e2.name AS Employee2, e1.dept_id
FROM employees e1
JOIN employees e2 ON e1.dept_id = e2.dept_id AND e1.emp_id != e2.emp_id;


---

If you want this in a downloadable .sql file format or want to automate this using a batch file, let me know!