SQL: Databases

1. What is a Database?

A database is an organized collection of data that can be easily accessed, managed, and updated.
In SQL terms, a database stores multiple tables, along with relationships, constraints, and other objects.

Example:

• Database → SchoolDB

• Tables inside → Students, Teachers, Courses

2. Types of Databases

Although SQL is mostly associated with relational databases, there are multiple types:

3. SQL Databases and RDBMS

RDBMS (Relational Database Management System) is the software used to manage SQL databases.
Key features:

• Tables with rows (records) and columns (fields)

• Primary keys to uniquely identify rows

• Foreign keys to link related tables

• Data integrity rules

4. Creating a Database in SQL

-- Create a new database
CREATE DATABASE SchoolDB;

-- Use a database
USE SchoolDB;

5. Deleting a Database

Caution: This removes the database permanently.

DROP DATABASE SchoolDB;

6. Showing Databases

SHOW DATABASES;

7. Importing and Exporting Databases

• Export:

  mysqldump -u root -p SchoolDB > backup.sql
  

• Import:

  mysql -u root -p SchoolDB < backup.sql
  

8. Common SQL Database Objects

Inside a database, you can have:

1. Tables – Store data

2. Views – Virtual tables from queries

3. Stored Procedures – Predefined SQL routines

4. Triggers – Automatic actions when events happen

5. Indexes – Speed up searches

9. Example: School Database

CREATE DATABASE SchoolDB;
USE SchoolDB;

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Grade VARCHAR(5)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    TeacherID INT
);

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    TeacherName VARCHAR(100)
);

10. Best Practices for SQL Databases

• Always name databases and tables clearly

• Use normalization to avoid redundant data

• Create indexes for faster queries

• Regularly backup databases

• Use transactions for safe updates