Part 1: SQL Basics with Project | DDL & DML Explained (Online Auction System)

In this video, we explore SQL concepts using a real-world Online Auction System built with Python Django. Instead of learning SQL in isolation, we will understand how DDL (Data Definition Language), DML (Data Manipulation Language), and SELECT queries are used in a practical project involving Users, Auction Items, Bids, and Transactions. 1. DDL – Data Definition Language Used to define or modify the structure of the database (tables, schema, constraints) Commands under DDL: CREATE → Create database objects (tables, views) ALTER → Modify structure (add/remove columns) DROP → Delete table/database permanently TRUNCATE → Remove all data from table (structure remains) RENAME → Rename table 2. DML – Data Manipulation Language Used to manipulate data inside tables Commands under DML: INSERT → Add new records UPDATE → Modify existing records DELETE → Remove records Database of: Auction Online System _____________________________ CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, email TEXT UNIQUE NOT NULL, password TEXT NOT NULL, role TEXT CHECK (role IN ('Buyer', 'Seller', 'Admin')) NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE auction_items ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT, base_price REAL NOT NULL, current_price REAL, start_time TEXT DEFAULT CURRENT_TIMESTAMP, end_time TEXT, seller_id INTEGER, FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE ); CREATE TABLE bids ( id INTEGER PRIMARY KEY AUTOINCREMENT, amount REAL NOT NULL, timestamp TEXT DEFAULT CURRENT_TIMESTAMP, user_id INTEGER, auction_id INTEGER, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (auction_id) REFERENCES auction_items(id) ON DELETE CASCADE ); CREATE TABLE transactions ( id INTEGER PRIMARY KEY AUTOINCREMENT, auction_id INTEGER UNIQUE, buyer_id INTEGER, final_price REAL, status TEXT CHECK (status IN ('Pending', 'Completed')) DEFAULT 'Pending', payment_date TEXT DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (auction_id) REFERENCES auction_items(id) ON DELETE CASCADE, FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE ); ------------------------ 1. USERS: INSERT INTO users (username, email, password, role) VALUES ('seller_1', '[email protected]', 'pass123', 'Seller'); INSERT INTO users (username, email, password, role) VALUES ('buyer_1', '[email protected]', 'pass123', 'Buyer'); INSERT INTO users (username, email, password, role) VALUES ('buyer_2', '[email protected]', 'pass123', 'Buyer'); INSERT INTO users (username, email, password, role) VALUES ('admin_1', '[email protected]', 'pass123', 'Admin'); _______________________ 2. AUCTION ITEMS (Seller = id 1) INSERT INTO auction_items (title, description, base_price, current_price, seller_id) VALUES ('Laptop', 'Gaming Laptop', 50000, 50000, 1); INSERT INTO auction_items (title, description, base_price, current_price, seller_id) VALUES ('iPhone', 'iPhone 14 Pro', 80000, 80000, 1); _______________________ 3. BIDS (Buyers = id 2, 3) INSERT INTO bids (amount, user_id, auction_id) VALUES (52000, 2, 1); INSERT INTO bids (amount, user_id, auction_id) VALUES (55000, 3, 1); INSERT INTO bids (amount, user_id, auction_id) VALUES (85000, 2, 2); _________________________ 4. UPDATE CURRENT PRICE UPDATE auction_items SET current_price = 55000 WHERE id = 1; UPDATE auction_items SET current_price = 85000 WHERE id = 2; __________________________ 5. TRANSACTIONS INSERT INTO transactions (auction_id, buyer_id, final_price, status) VALUES (1, 3, 55000, 'Completed'); —------------------------------------------ Basic Queries: 🔹 1. Get all users SELECT * FROM users; 🔹 2. Get only usernames and roles SELECT username, role FROM users; Purpose: Understand projection (selecting specific columns) 🔹 3. Get all auction items SELECT * FROM auction_items; 🔹 4. Get auction titles and prices SELECT title, base_price, current_price FROM auction_items; 🔹 5. Get all bids SELECT * FROM bids; 🔹 6. Get bids greater than 50,000 🔹 7. Get all buyers only SELECT * FROM users WHERE role = 'Buyer'; 🔹 8. Get auctions sorted by price SELECT * FROM auction_items ORDER BY current_price DESC;