323. Creating Databases and Tables

Syntax for Creating Databases:

CREATE DATABASE database_name;

Syntax for Creating Tables:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Examples:

Creating a Database:

CREATE DATABASE TechTreasure;

Creating a Table:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

1. Creating a Database

To start, you need to create a database where your tables and data will be stored. Here’s how to create a database:

CREATE DATABASE my_store;

This SQL command creates a new database called my_store. Once the database is created, you can switch to it using:

USE my_store;

2. Creating a Table

After creating the database, you can create a table within it. A table consists of columns, each of which is defined by a name and a data type.

Example: Creating a products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• product_id: An integer that uniquely identifies each product. This column is defined as the PRIMARY KEY, which ensures that each product has a unique product_id.

• product_name: A variable character string with a maximum length of 100. The NOT NULL constraint ensures that every product must have a name.

• category: A variable character string with a maximum length of 50. It categorizes the product (e.g., electronics, clothing).

• price: A decimal number with up to 10 digits in total and 2 digits after the decimal point, representing the product price.

• stock_quantity: An integer representing the quantity of the product in stock. The DEFAULT 0 constraint sets the default stock quantity to 0 if no value is provided.

• created_at: A timestamp that records when the product was added to the database. The DEFAULT CURRENT_TIMESTAMP constraint automatically sets this value to the current date and time when the product is inserted.

Importance of Defining Primary Keys and Choosing Appropriate Data Types

1. Defining Primary Keys

• Uniqueness: The primary key ensures that each record in the table is unique. No two rows can have the same value for the primary key.

• Indexing: Most database systems automatically create an index on the primary key, which speeds up query performance.

• Relationships: Primary keys are used to establish relationships between tables (e.g., a foreign key in another table can reference this primary key).

2. Choosing Appropriate Data Types

• Data Integrity: Selecting the correct data type ensures that the data stored in each column is consistent and valid. For example, using VARCHAR for text and INT for numbers prevents invalid data entries.

• Storage Efficiency: Proper data types optimize storage usage. For instance, using VARCHAR(50) instead of VARCHAR(255) for a name column saves space if names are generally short.

• Performance: The right data types can enhance query performance. Numeric operations on INT columns are generally faster than on VARCHAR columns.

Example: Creating Tables with Different Data Types

Here’s an example of another table, customers, with various data types:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(15),
    birthdate DATE,
    loyalty_points INT DEFAULT 0,
    registered_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

• customer_id: An auto-incrementing integer that uniquely identifies each customer.

• first_name and last_name: Text columns for storing the customer’s first and last names, respectively.

• email: A text column with a UNIQUE constraint to ensure no two customers have the same email address.

• phone_number: A text column for storing the customer’s phone number.

• birthdate: A date column for storing the customer’s birth date.

• loyalty_points: An integer column that defaults to 0, representing points earned by the customer.

• registered_at: A datetime column that records when the customer was registered.

By carefully defining the primary keys and selecting appropriate data types, you ensure that your database is robust, efficient, and capable of handling your application’s needs.