SQL Database & Table Creation: Beginner's Guide with Examples

SQL Database & Table creation

Structured Query Language (SQL) is the standard language for managing relational databases. Whether you are a beginner or an experienced developer, knowing how to create a database and tables is essential. In this article, we will guide you through the process of creating a database and tables using SQL, with practical examples and sample datasets.

---

Step 1: Creating a Database

A database in SQL is a structured collection of data. You can create a new database using the CREATE DATABASE statement.

Syntax:

CREATE DATABASE sales_db;

This command creates a new database named sales_db. To use this database, run:

USE sales_db;

This sets the active database for your session.

---

Step 2: Creating a Table

After creating the database, the next step is to create a table. A table consists of rows and columns that store structured data. The CREATE TABLE statement is used to define the table structure.

Syntax:

CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    quantity INT,
    price DECIMAL(10,2),
    sale_date DATE
);

This creates a table named sales with the following columns:

• sale_id: A unique identifier for each sale (auto-incremented primary key).
• product_name: Name of the product sold.
• category: Category of the product.
• quantity: Number of items sold.
• price: Price per unit.
• sale_date: Date of the sale.

---

Step 3: Inserting Sample Data

After creating the table, we need some data to work with. The INSERT INTO statement adds records to the table.

Example SQL Script to Insert Sample Data (25 Rows)

INSERT INTO sales (product_name, category, quantity, price, sale_date) VALUES
('Laptop', 'Electronics', 5, 800.00, '2024-01-10'),
('Smartphone', 'Electronics', 10, 500.00, '2024-01-12'),
('Headphones', 'Accessories', 15, 50.00, '2024-01-15'),
('Keyboard', 'Accessories', 8, 30.00, '2024-01-18'),
('Mouse', 'Accessories', 12, 25.00, '2024-01-20'),
('Tablet', 'Electronics', 7, 300.00, '2024-01-22'),
('Monitor', 'Electronics', 4, 200.00, '2024-01-25'),
('Charger', 'Accessories', 20, 15.00, '2024-01-28'),
('Printer', 'Electronics', 3, 150.00, '2024-02-01'),
('External Hard Drive', 'Storage', 6, 120.00, '2024-02-05'),
('USB Cable', 'Accessories', 25, 10.00, '2024-02-07'),
('Gaming Console', 'Electronics', 2, 400.00, '2024-02-10'),
('Router', 'Networking', 5, 70.00, '2024-02-12'),
('Smartwatch', 'Wearables', 10, 250.00, '2024-02-15'),
('Camera', 'Photography', 4, 600.00, '2024-02-18'),
('Tripod', 'Photography', 10, 50.00, '2024-02-20'),
('Speakers', 'Audio', 7, 80.00, '2024-02-22'),
('Projector', 'Electronics', 2, 500.00, '2024-02-25'),
('Flash Drive', 'Storage', 30, 20.00, '2024-02-28'),
('Wireless Earbuds', 'Audio', 12, 150.00, '2024-03-01'),
('Power Bank', 'Accessories', 15, 40.00, '2024-03-03'),
('Graphics Card', 'Computing', 3, 700.00, '2024-03-05'),
('VR Headset', 'Wearables', 4, 350.00, '2024-03-07'),
('Gaming Chair', 'Furniture', 5, 180.00, '2024-03-10'),
('Cooling Pad', 'Accessories', 8, 35.00, '2024-03-12');

These 25 rows of sample data provide a diverse range of products and categories for analysis.

---

Step 4: Retrieving Data from the Table

To view the stored data, use the SELECT statement.

Fetch All Data:

SELECT * FROM sales;

This retrieves all columns and rows from the sales table.

Filter by Category:

SELECT * FROM sales WHERE category = 'Electronics';

This fetches all sales records where the category is Electronics.

Calculate Total Sales Revenue:

SELECT SUM(quantity * price) AS total_revenue FROM sales;

This calculates the total revenue from all sales transactions.

---

Step 5: Updating and Deleting Records

You can modify or remove records using UPDATE and DELETE statements.

Update Price of a Product:

UPDATE sales SET price = 850.00 WHERE product_name = 'Laptop';

Delete a Record:

DELETE FROM sales WHERE product_name = 'Cooling Pad';

---

Conclusion

In this article, we covered how to create a database, define tables, insert sample data, and retrieve useful insights using SQL queries. Mastering these fundamental operations will help you manage and analyze relational data effectively.

Would you like a follow-up article on advanced SQL queries? Let us know in the comments!