Getting Started with SQL Database

What is a Database?

A database is an organized collection of information that allows data to be stored, managed, searched, and updated efficiently. Applications use databases whenever they need to keep information for later use.

For example, a university system may store students, courses, teachers, marks, and registrations in a database. An e-commerce website may store products, customers, orders, and payments.

"A database gives an application a structured place to store and manage information."

What is SQL?

SQL stands for Structured Query Language. It is a language used to communicate with relational databases. With SQL, we can retrieve data, add new records, change existing records, delete records, and work with relationships between tables.

SQL is used by many relational database management systems, including MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.

Tables

A relational database stores data in tables. A table consists of rows and columns. Each row normally represents one record, while each column represents a particular attribute of that record.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);

For example, a students table can contain one row for each student and columns for the student's ID, name, age, and email.

Primary Key

A primary key is a column, or combination of columns, that uniquely identifies each record in a table. A primary key cannot contain duplicate values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

In this example, student_id identifies each student uniquely.

Foreign Key

A foreign key is a column that creates a relationship between tables by referring to a primary key in another table. It helps connect related data and maintain referential integrity.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

SELECT

The SELECT statement is used to retrieve data from a table.

SELECT * FROM students;

You can also select specific columns instead of retrieving every column.

SELECT name, email
FROM students;

INSERT

The INSERT statement adds new records to a table.

INSERT INTO students (student_id, name, age)
VALUES (1, 'Ali', 20);

After running this command, a new student record is added to the students table.

UPDATE

The UPDATE statement changes existing records. A WHERE condition is usually important so that only the intended records are changed.

UPDATE students
SET age = 21
WHERE student_id = 1;

DELETE

The DELETE statement removes records from a table. Using WHERE helps make sure that only the required record or records are removed.

DELETE FROM students
WHERE student_id = 1;

WHERE

The WHERE clause filters records based on a condition. It can be used with commands such as SELECT, UPDATE, and DELETE.

SELECT *
FROM students
WHERE age > 18;

This query returns students whose age is greater than 18.

JOINs

JOINs are used to combine related data from multiple tables. They are especially useful when information is divided into separate tables to reduce duplication and keep the database organized.

An INNER JOIN returns records where the join condition matches in both tables.

SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments
    ON students.student_id = enrollments.student_id
INNER JOIN courses
    ON enrollments.course_id = courses.course_id;

This query can be used to find which courses each student is enrolled in.

Relationships

Relationships describe how records in different tables are connected. Common relationship types include one-to-one, one-to-many, and many-to-many.

• One-to-one: One record in one table is associated with one record in another table.
• One-to-many: One record can be associated with many records, such as one customer having many orders.
• Many-to-many: Many records can be associated with many records, such as students enrolling in multiple courses and courses containing multiple students.

Practical examples

Imagine an online store with three tables: customers, products, and orders. A customer can place multiple orders, while each order can contain information about products that were purchased.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

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

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

We can then retrieve customer orders with a JOIN.

SELECT customers.name,
       products.product_name,
       orders.quantity
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id
JOIN products
    ON orders.product_id = products.product_id;

This is the basic idea behind many real-world database systems. Instead of putting every piece of information into one huge table, related information is organized into separate tables and connected using keys.

Conclusion

Databases provide a structured way to store and manage information, while SQL provides the language used to work with relational databases. Understanding tables, primary keys, foreign keys, CRUD operations, WHERE conditions, JOINs, and relationships gives you a strong foundation for database development.

These concepts are used in almost every type of software system, from university management systems and airline applications to banking platforms and online stores. Once the fundamentals are clear, more advanced database concepts become much easier to learn.

---

A strong understanding of databases is one of the most useful foundations for becoming a better software developer. 🧡