SQL Setup & DDL — CREATE, ALTER, DROP

9. Practice Problems

Each problem has a Show Answer button. Most answers are runnable — click Run to see the result on the spot. Solve it yourself first, then check.

1. Create a table teachers with columns id (PK), name (NOT NULL), email (UNIQUE), salary (positive only). Insert two rows and select them.
   একটি teachers table বানান যাতে id, name, email এবং salary থাকবে; দুটি row insert করে দেখান।
   ✨ Show Answer (উত্তর দেখুন)

   ans1.sql

   CREATE TABLE teachers (
       id     INTEGER PRIMARY KEY,
       name   TEXT    NOT NULL,
       email  TEXT    UNIQUE,
       salary NUMERIC CHECK (salary > 0)
   );
   INSERT INTO teachers(name, email, salary) VALUES
       ('Prof. Jamilur Rahman', 'jamil@buet.ac.bd', 85000),
       ('Dr. Tania Ahmed',     'tania@du.ac.bd',   92000);
   SELECT * FROM teachers;

   Run Copy Open in SQLite Online

2. Add a phone column (TEXT, UNIQUE) to an existing customers table.
   বিদ্যমান customers table-এ একটি phone column যোগ করুন।
   ✨ Show Answer

   ans2.sql

   ALTER TABLE customers ADD COLUMN phone TEXT UNIQUE;
   UPDATE customers SET phone = '01711-000001' WHERE id = 1;
   UPDATE customers SET phone = '01711-000002' WHERE id = 2;
   SELECT * FROM customers;

   Run Copy Open in SQLite Online

3. Rename the column name in customers to full_name.
   customers table-এ name column-এর নাম বদলে full_name করুন।
   ✨ Show Answer

   ans3.sql

   ALTER TABLE customers RENAME COLUMN name TO full_name;
   SELECT * FROM customers;

   Run Copy Open in SQLite Online

4. Create a table library_books where isbn is the primary key and copies defaults to 1.
   একটি library_books table বানান, যেখানে isbn primary key এবং copies এর default value ১।
   ✨ Show Answer

   ans4.sql

   CREATE TABLE library_books (
       isbn   TEXT    PRIMARY KEY,
       title  TEXT    NOT NULL,
       author TEXT    NOT NULL,
       copies INTEGER NOT NULL DEFAULT 1 CHECK(copies >= 0)
   );
   INSERT INTO library_books(isbn, title, author) VALUES
       ('978-984-401-001-1', 'Pother Pachali', 'Bibhutibhushan');
   SELECT * FROM library_books;

   Run Copy Open in SQLite Online

5. Drop the teachers table if it exists, then verify it is gone using sqlite_master.
   teachers table-টি drop করুন এবং পরে sqlite_master দিয়ে যাচাই করুন।
   ✨ Show Answer

   ans5.sql

   DROP TABLE IF EXISTS teachers;
   SELECT name FROM sqlite_master WHERE type = 'table';

   Run Copy Open in SQLite Online

6. Design two related tables departments and employees with a foreign key from employee to department.
   দুটি সম্পর্কিত table — departments এবং employees — foreign key সহ ডিজাইন করুন।
   ✨ Show Answer

   ans6.sql

   PRAGMA foreign_keys = ON;
   
   CREATE TABLE departments (
       id   INTEGER PRIMARY KEY,
       name TEXT NOT NULL UNIQUE
   );
   
   CREATE TABLE employees (
       id            INTEGER PRIMARY KEY,
       name          TEXT NOT NULL,
       department_id INTEGER NOT NULL,
       FOREIGN KEY (department_id) REFERENCES departments(id)
   );
   
   INSERT INTO departments(name) VALUES ('Engineering'), ('Finance');
   INSERT INTO employees(name, department_id) VALUES
       ('Mahmud', 1), ('Sabina', 2);
   
   SELECT e.name, d.name AS department
   FROM employees e JOIN departments d ON d.id = e.department_id;

   Run Copy Open in SQLite Online

7. Why does SQLite store dates as TEXT rather than DATE? Answer in two sentences.
   SQLite কেন date-কে TEXT হিসেবে রাখে, DATE নয়? দুই বাক্যে ব্যাখ্যা করুন।
   ✨ Show Answer

   Answer: SQLite was designed as an extremely small embedded engine with only five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) and no native date type. Storing dates as ISO-8601 TEXT ('YYYY-MM-DD HH:MM:SS') keeps the engine tiny while still allowing correct lexicographic ordering and the full date() / strftime() function family.

   SQLite-কে অত্যন্ত ছোট embedded engine হিসেবে ডিজাইন করা হয়েছে; এতে মাত্র পাঁচটি storage class আছে এবং কোনো native date type নেই। ISO-8601 ফরম্যাটে TEXT হিসেবে date রাখলেই engine ছোট থাকে, lexicographic sort ঠিকঠাক কাজ করে এবং date()/strftime() ফাংশনগুলোও ব্যবহার করা যায়।

8. Add a CHECK constraint to ensure mobile-recharge amount is between 10 and 5000 BDT.
   Mobile recharge amount ১০ থেকে ৫০০০ টাকার মধ্যে আছে কিনা যাচাইয়ের জন্য CHECK যোগ করুন।
   ✨ Show Answer

   ans8.sql

   CREATE TABLE recharges (
       id     INTEGER PRIMARY KEY,
       phone  TEXT    NOT NULL,
       amount NUMERIC NOT NULL CHECK(amount BETWEEN 10 AND 5000)
   );
   INSERT INTO recharges(phone, amount) VALUES
       ('01911-111111', 50),
       ('01911-222222', 1000);
   SELECT * FROM recharges;

   Run Copy Open in SQLite Online

9. Use CREATE TABLE IF NOT EXISTS to make a "first run safe" creation script.
   CREATE TABLE IF NOT EXISTS দিয়ে এমন script লিখুন যা প্রথম এবং পরবর্তী রান উভয়েই কাজ করবে।
   ✨ Show Answer

   ans9.sql

   CREATE TABLE IF NOT EXISTS notes (
       id      INTEGER PRIMARY KEY,
       body    TEXT    NOT NULL,
       created TEXT    DEFAULT (datetime('now'))
   );
   -- Running this twice is harmless; the second time is a no-op.
   CREATE TABLE IF NOT EXISTS notes (
       id INTEGER PRIMARY KEY, body TEXT
   );
   SELECT name FROM sqlite_master WHERE type = 'table';

   Run Copy Open in SQLite Online

10. Create a composite primary key (course_id, student_id) for an enrollments table.
    একটি enrollments table-এ (course_id, student_id) — composite primary key দিন।
    ✨ Show Answer

    ans10.sql

    CREATE TABLE enrollments (
        course_id  INTEGER NOT NULL,
        student_id INTEGER NOT NULL,
        grade      TEXT,
        PRIMARY KEY (course_id, student_id)
    );
    INSERT INTO enrollments VALUES (101, 1, 'A'), (101, 2, 'B+');
    SELECT * FROM enrollments;

    Run Copy Open in SQLite Online

11. Make a products table where price must be greater than zero and stock non-negative.
    এমন products table বানান যেখানে price ০-এর বেশি ও stock non-negative।
    ✨ Show Answer

    ans11.sql

    CREATE TABLE products (
        id    INTEGER PRIMARY KEY,
        title TEXT    NOT NULL,
        price NUMERIC NOT NULL CHECK(price > 0),
        stock INTEGER NOT NULL DEFAULT 0 CHECK(stock >= 0)
    );
    INSERT INTO products(title, price, stock) VALUES
        ('Walton fan', 3500, 12),
        ('Pran juice 1L', 95, 200);
    SELECT * FROM products;

    Run Copy Open in SQLite Online

12. List the difference between DELETE and DROP in three short bullet points.
    তিনটি ছোট bullet-এ DELETE এবং DROP-এর পার্থক্য লিখুন।
    ✨ Show Answer

    • DELETE removes rows; the table and its schema remain.
    • DROP removes the entire table — schema, indexes, triggers, everything.
    • DELETE can be filtered by WHERE; DROP cannot.

    DELETE শুধু row মুছে; DROP পুরো table মুছে দেয়।

13. In one statement, drop a column from an existing SQLite table (3.35+).
    SQLite ৩.৩৫+ ব্যবহার করে এক statement-এ একটি column drop করুন।
    ✨ Show Answer

    ans13.sql

    ALTER TABLE temp_t DROP COLUMN b;
    SELECT * FROM temp_t;

    Run Copy Open in SQLite Online

14. Why is putting NOT NULL on a column "free protection"? Explain in 2 sentences.
    কেন একটি column-এ NOT NULL দেওয়া "ফ্রি সুরক্ষা"? দুই বাক্যে ব্যাখ্যা করুন।
    ✨ Show Answer

    Answer: A NULL value silently disables many query semantics — comparisons return UNKNOWN, aggregates skip the row, and joins behave unexpectedly. Forbidding NULLs at the database level eliminates an entire class of bugs that the application would otherwise have to handle case by case.

    NULL value-এর কারণে অনেক query চুপচাপ ভুল ফল দেয় — তুলনা UNKNOWN হয়, aggregate row বাদ দেয়, join-এর আচরণ বদলায়। তাই database-এই NULL forbid করলে অনেক bug-ই আগে থেকে আটকে যায়।