How to Create Tables in SQL

-- Create a simple table
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com'),
  ('charlie', 'charlie@example.com');

-- View the table
SELECT * FROM users;

-- Create table with multiple constraints
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL CHECK(price > 0),
  stock INTEGER DEFAULT 0 CHECK(stock >= 0),
  category TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(name, category)
);

-- Insert products
INSERT INTO products (name, price, stock, category) VALUES
  ('Laptop', 999.99, 50, 'Electronics'),
  ('Mouse', 29.99, 200, 'Electronics'),
  ('Desk', 299.99, 30, 'Furniture'),
  ('Chair', 199.99, 45, 'Furniture');

SELECT * FROM products;

-- Create table with foreign key
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  product_id INTEGER,
  quantity INTEGER CHECK(quantity > 0),
  order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Insert orders
INSERT INTO orders (user_id, product_id, quantity) VALUES
  (1, 1, 1),
  (2, 2, 2),
  (1, 3, 1);

SELECT * FROM orders;