Creating and Managing Databases

Key Concepts

1. Database Creation
2. Table Creation
3. Data Manipulation
4. Database Maintenance

1. Database Creation

Creating a database is the first step in setting up a relational database management system (RDBMS). A database is a container for tables, views, indexes, and other database objects. The CREATE DATABASE statement is used to create a new database.

Example:

CREATE DATABASE CompanyDB;
    

This command creates a new database named CompanyDB.

2. Table Creation

Tables are the fundamental structures within a database where data is stored. Each table consists of rows and columns. The CREATE TABLE statement is used to create a new table within a database.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);
    

This command creates a table named Employees with columns for EmployeeID, Name, Department, and Salary.

3. Data Manipulation

Data manipulation involves inserting, updating, and deleting data within a database. The INSERT, UPDATE, and DELETE statements are used for these operations.

Example of inserting data:

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES (1, 'John Doe', 'Sales', 50000.00);
    

This command inserts a new employee record into the Employees table.

Example of updating data:

UPDATE Employees
SET Salary = 55000.00
WHERE EmployeeID = 1;
    

This command updates the salary of the employee with EmployeeID 1.

Example of deleting data:

DELETE FROM Employees
WHERE EmployeeID = 1;
    

This command deletes the employee record with EmployeeID 1.

4. Database Maintenance

Database maintenance involves tasks such as backing up data, optimizing performance, and ensuring data integrity. Common maintenance tasks include creating backups, indexing tables, and monitoring database performance.

Example of creating a backup:

BACKUP DATABASE CompanyDB
TO DISK = 'C:\Backup\CompanyDB.bak';
    

This command creates a backup of the CompanyDB database to the specified disk location.

Example of indexing a table:

CREATE INDEX idx_Name
ON Employees (Name);
    

This command creates an index on the Name column of the Employees table to improve query performance.