Creating and Managing Databases with SQL

In this lesson, we will explore how to create, modify, and manage databases using SQL (Structured Query Language) commands. By the end of this lesson, you will understand the fundamental concepts of database management, how to create tables, define relationships, and manipulate the data stored within these tables. This knowledge is essential for effective data analytics in finance.

Learning Objectives

By the end of this lesson, you will be able to:

1. Understand the basics of databases and their importance in data management.
2. Create a new database using SQL commands.
3. Define and create tables within a database.
4. Modify existing tables and manage data types.
5. Understand primary keys and foreign keys for establishing relationships.
6. Use SQL commands to manage and manipulate data.

Understanding Databases

A database is a structured collection of data that is stored and accessed electronically. Databases are essential for organizing and managing data efficiently. In finance, databases are used to store information such as transactions, customer data, and financial records.

Real-World Analogy

Think of a database as a digital filing cabinet. Each drawer in the cabinet represents a different category of information (e.g., customer data, transaction records). Within each drawer, there are folders (tables) that hold individual documents (records). This organization allows for easy retrieval and management of information.

Creating a Database

To create a database, you use the CREATE DATABASE SQL command. Here’s how to do it:

CREATE DATABASE finance_db;

This command creates a new database named finance_db. You can replace finance_db with any name that follows the naming conventions of your SQL database system.

The name of the database should be unique and descriptive of its contents. Avoid using spaces and special characters.

Creating Tables

Once you have a database, the next step is to create tables within it. A table is a collection of related data entries that consists of columns and rows. Each column represents a different attribute of the data, while each row represents a single record.

Syntax for Creating a Table

The basic syntax for creating a table is as follows:

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

Example: Creating a Customers Table

Let’s create a table named customers to store customer information:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

In this example: - customer_id is an integer that serves as the primary key, uniquely identifying each customer. - first_name and last_name are variable character fields with a maximum length of 50 characters. - email is a variable character field with a maximum length of 100 characters. - created_at is a datetime field that defaults to the current timestamp when a new record is created.

Modifying Tables

You may need to alter an existing table to add, modify, or delete columns. The ALTER TABLE command is used for this purpose.

Adding a New Column

To add a new column to the customers table, use:

ALTER TABLE customers
ADD phone_number VARCHAR(15);

This command adds a phone_number column to the existing customers table.

Modifying a Column

To change the data type of a column, use:

ALTER TABLE customers
MODIFY email VARCHAR(150);

This command changes the maximum length of the email column from 100 to 150 characters.

Dropping a Column

To remove a column from a table, use:

ALTER TABLE customers
DROP COLUMN phone_number;

This command deletes the phone_number column from the customers table.

Understanding Keys

Keys are critical in relational databases for establishing relationships between tables. The two most common types of keys are:

1. Primary Key: A unique identifier for each record in a table. No two records can have the same primary key value.
2. Foreign Key: A field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a relationship between the two tables.

Example of Primary and Foreign Keys

Let’s create another table called orders that references the customers table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example: - order_id is the primary key for the orders table. - customer_id is a foreign key that references the customer_id in the customers table, establishing a relationship between the two tables.

Managing Data

After creating tables, you will need to insert, update, and delete data. Here are the basic SQL commands for managing data:

Inserting Data

To add a new record to a table, use the INSERT INTO command:

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

This command inserts a new customer record into the customers table.

Updating Data

To modify existing records, use the UPDATE command:

UPDATE customers
SET email = 'john.newemail@example.com'
WHERE customer_id = 1;

This command updates the email address of the customer with customer_id 1.

Deleting Data

To remove a record from a table, use the DELETE command:

DELETE FROM customers
WHERE customer_id = 1;

This command deletes the customer record with customer_id 1 from the table.

Best Practices

• Use Meaningful Names: Choose clear and descriptive names for databases, tables, and columns to enhance readability.
• Normalize Your Data: Organize data to reduce redundancy and improve data integrity. This often involves creating multiple related tables.
• Backup Your Data: Regularly back up your database to prevent data loss.
• Use Constraints: Implement constraints such as NOT NULL, UNIQUE, and CHECK to enforce data integrity.

Common Mistakes and How to Avoid Them

• Forgetting to Specify Data Types: Always define the data type for each column when creating tables to avoid errors.
• Not Using Primary Keys: Always define a primary key for each table to ensure each record is uniquely identifiable.
• Neglecting Relationships: Understand and define relationships between tables using foreign keys to maintain data integrity.

Key Takeaways

• A database is a structured collection of data, essential for organizing and managing information in finance.
• SQL commands such as CREATE, ALTER, INSERT, UPDATE, and DELETE are fundamental for database management.
• Primary keys uniquely identify records in a table, while foreign keys establish relationships between tables.
• Best practices include using meaningful names, normalizing data, and implementing constraints to ensure data integrity.

In the next lesson, titled "SQL Queries: Retrieving Data", we will learn how to retrieve and manipulate data from the databases we have created. This will enable you to extract valuable insights from your financial data, paving the way for effective data analytics in finance.

Practice Exercises

1. Create a Database: Write an SQL command to create a database named financial_records.

2. Create a Products Table: Create a table named products with the following columns: product_id (INT, PRIMARY KEY), product_name (VARCHAR(100)), price (DECIMAL), and stock_quantity (INT).

3. Modify the Products Table: Add a new column category (VARCHAR(50)) to the products table you created in the previous exercise.

4. Insert Data: Write SQL commands to insert three records into the products table.

5. Update Data: Write an SQL command to update the price of a product with a specific product_id.

Practical Assignment

Create a database named company_finance. Within this database, create two tables: employees and departments. The employees table should include employee_id (INT, PRIMARY KEY), first_name (VARCHAR(50)), last_name (VARCHAR(50)), department_id (INT, FOREIGN KEY), and salary (DECIMAL). The departments table should include department_id (INT, PRIMARY KEY) and department_name (VARCHAR(100)). Populate both tables with sample data and demonstrate the relationships between them.

• A database is essential for organizing and managing data in finance.
• SQL commands are used to create, modify, and manage databases and tables.
• Primary keys uniquely identify records, while foreign keys establish relationships between tables.
• Best practices include meaningful naming, data normalization, and data integrity constraints.
• Common mistakes include forgetting to specify data types and neglecting to define relationships between tables.