SQL চিটশিট

CREATE DATABASE my_database;
USE my_database;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

INSERT INTO students (id, name, age, city)
VALUES (1, 'আনিস', 20, 'ঢাকা');

INSERT INTO students VALUES 
(2, 'করিম', 22, 'চট্টগ্রাম'),
(3, 'সেলিম', 19, 'সিলেট');

SELECT * FROM students; -- সব কলাম
SELECT name, age FROM students; -- নির্দিষ্ট কলাম

SELECT * FROM students WHERE age > 20;

SELECT * FROM students ORDER BY age ASC; -- ক্রমবর্ধমান

UPDATE students
SET age = 21
WHERE name = 'আনিস';

DELETE FROM students WHERE id = 2;

DROP TABLE students;

SELECT * FROM students WHERE age BETWEEN 18 AND 22 AND city = 'ঢাকা';

SELECT * FROM students WHERE age > 18 AND city != 'ঢাকা';

SELECT AVG(age) AS avg_age FROM students WHERE city = 'ঢাকা';

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city;

SELECT city, COUNT(*) AS total
FROM students
GROUP BY city
HAVING COUNT(*) > 1;

CREATE TABLE grades (
    student_id INT,
    grade VARCHAR(2)
);
 
INSERT INTO grades VALUES (1, 'A'), (2, 'B');
 
SELECT students.name, grades.grade
FROM students
INNER JOIN grades ON students.id = grades.student_id;

SELECT name
FROM students
WHERE id IN (SELECT student_id FROM grades WHERE grade = 'A');

SELECT name FROM students
UNION
SELECT name FROM teachers;

CREATE INDEX idx_name ON students(name);

CREATE VIEW dhaka_students AS
SELECT * FROM students WHERE city = 'ঢাকা';
 
SELECT * FROM dhaka_students;

ALTER TABLE students
ADD COLUMN email VARCHAR(100);
 
ALTER TABLE students
DROP COLUMN email;

ALTER TABLE students
ADD CONSTRAINT unique_name UNIQUE (name);

START TRANSACTION;
 
INSERT INTO students VALUES (4, 'আলী', 23, 'রাজশাহী');
UPDATE students SET age = 24 WHERE id = 4;
 
COMMIT; -- স্থায়ী করা
-- ROLLBACK; -- বাতিল করা

-- ডাটাবেস তৈরি
CREATE DATABASE school;
USE school;
 
-- টেবিল তৈরি
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50)
);
 
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);
 
-- ডেটা যোগ
INSERT INTO students (name, age, city) VALUES
('আনিস', 20, 'ঢাকা'),
('করিম', 22, 'চট্টগ্রাম');
 
INSERT INTO courses VALUES
(1, 'গণিত', 1),
(2, 'বিজ্ঞান', 2);
 
-- কোয়েরি
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.id = c.student_id
WHERE s.city = 'ঢাকা';