SQL Tutorial: Building a Banking System 🎯

Welcome to CodeYourCraft's SQL Tutorial! In this lesson, we'll be building a simplified banking system to learn SQL from scratch. By the end, you'll have a solid understanding of SQL and be ready to tackle real-world database projects. 💡 Pro Tip: Bookmark this page for future reference!

What is SQL? 📝

SQL (Structured Query Language) is a standard language used for managing and manipulating databases. It allows you to create, query, update, and delete data in a database.

Getting Started 🔧

Before we dive into the banking system, let's set up a SQLite database for our project.

sql

-- Create a new SQLite database named 'banking_system.db'
.open banking_system.db
-- Verify the database version
.ver

Now that our database is ready, let's create the tables we'll need for our banking system.

Creating Tables 📝

Customers Table

sql

-- Create customers table with columns: id (primary key), name, email, and phone
CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT NOT NULL
);

Accounts Table

sql

-- Create accounts table with columns: id (primary key), customer_id (foreign key), account_number, and balance
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    account_number TEXT NOT NULL UNIQUE,
    balance REAL NOT NULL
);

Inserting Data 📝

Now that our tables are created, let's insert some data.

Inserting Customer Data

sql

-- Insert a new customer (John Doe, john.doe@example.com, 1234567890)
INSERT INTO customers (name, email, phone) VALUES ('John Doe', 'john.doe@example.com', '1234567890');

Inserting Account Data

sql

-- Insert a new account for John Doe (1234567890, 1234567890001, 5000.00)
INSERT INTO accounts (customer_id, account_number, balance) VALUES (LAST_INSERT_ROWID, '1234567890001', 5000.00);

Queries 📝

Fetching Customer Data

sql

-- Fetch all customers
SELECT * FROM customers;

-- Fetch customer with id 1
SELECT * FROM customers WHERE id = 1;

Fetching Account Data

sql

-- Fetch all accounts
SELECT * FROM accounts;

-- Fetch account for customer with id 1
SELECT * FROM accounts WHERE customer_id = (SELECT id FROM customers WHERE name = 'John Doe');

Updating Data 📝

Updating Customer Data

sql

-- Update John Doe's email to johndoe@example.com
UPDATE customers SET email = 'johndoe@example.com' WHERE name = 'John Doe';

Updating Account Data

sql

-- Deposit 1000.00 into John Doe's account
UPDATE accounts SET balance = balance + 1000.00 WHERE customer_id = (SELECT id FROM customers WHERE name = 'John Doe');

Deleting Data 📝

Deleting Customer Data

sql

-- Delete John Doe (be careful!)
DELETE FROM customers WHERE name = 'John Doe';

Deleting Account Data

sql

-- Delete John Doe's account
DELETE FROM accounts WHERE customer_id = (SELECT id FROM customers WHERE name = 'John Doe');

Quiz 📝

Wrapping Up 📝

Congratulations on completing this SQL tutorial! You've learned how to create a simple banking system using SQL, including creating tables, inserting data, querying data, updating data, and deleting data. With this newfound knowledge, you're ready to tackle more complex database projects.

Remember, practice makes perfect, so don't be afraid to experiment with SQL queries on your own. Happy coding! 💡 Pro Tip: Use the SQLite command line to practice your SQL skills!

Stay tuned for more SQL tutorials at CodeYourCraft, where we help you master programming through practical, beginner-friendly lessons. Until next time! 🚀