Topic: SQL Basics and Database Operations

Introduction

Before this unit, I only knew a little about databases, like tables and simple queries. I heard about SQL but thought it was just another programming language. After learning Unit 3, I now see SQL is not just a language but a very powerful tool that helps in managing data. It is used everywhere, from websites to banks, and helps keep data organized and safe. One thing that really amazed me is how databases can handle big real-world problems like airline ticketing systems. The exercises we did, where we created and queried a database, helped me connect the theory with real-world use, making learning more fun and useful.

Important Lessons from Unit 3

Understanding ACID Properties

The unit started with ACID properties, which are important because they make sure databases work correctly and safely:

• Atomicity: A transaction happens fully or not at all. Like if you transfer money, it should be completed, or nothing should happen. No half transactions!
• Consistency: Data should always be correct and follow all rules, no matter what.
• Isolation: Even if many people use the database at the same time, their actions won’t mess up each other’s work.
• Durability: Once data is saved, it stays saved even if the system crashes.

These properties showed me why databases are reliable and trusted in important areas like banking and hospitals.

SQL: The Language That Runs Databases

SQL has different parts, each with a specific job:

1. DDL (Data Definition Language) – Used for creating and changing database structure (e.g., CREATE, ALTER, DROP).
2. DML (Data Manipulation Language) – Used for adding, updating, and deleting data (e.g., INSERT, UPDATE, DELETE).
3. DCL (Data Control Language) – Controls who can access the database (e.g., GRANT, REVOKE).
4. TCL (Transaction Control Language) – Manages transactions (e.g., COMMIT, ROLLBACK).

Learning these made me understand how databases are structured and managed.

Creating Database Structures

I learned how to create a database and tables:

CREATE DATABASE AirlineDB;

CREATE TABLE Users (
    Username VARCHAR(255) PRIMARY KEY,
    DOB DATE,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Gender VARCHAR(50)
);

I also learned how tables can be linked together using foreign keys:

CREATE TABLE Flight (
    FlightNo VARCHAR(10) PRIMARY KEY,
    DepTime TIMESTAMP,
    Destination VARCHAR(255),
    Source VARCHAR(255),
    ArrTime TIMESTAMP,
    Username VARCHAR(255),
    FOREIGN KEY (Username) REFERENCES Users(Username)
);

This helped me see how real-world databases store and organize data.

Querying Data

SQL helps in getting useful data from databases. I learned many ways to do this:

• Getting all users:

SELECT * FROM Users;

• Filtering data:

SELECT Username, FirstName, LastName FROM Users WHERE Gender = 'Male';

• Sorting results:

SELECT * FROM Users ORDER BY LastName DESC;

• Searching with patterns:

SELECT Username FROM Users WHERE Username LIKE 'user%';

These skills are very useful when working with databases in real applications.

Joining Tables

One of the most important things I learned was how to join tables to get related data:

SELECT Ticket.PNR_No, Ticket.FlightNo, Ticket.PassengerName
FROM Ticket
JOIN Users ON Ticket.BookedByUser = Users.Username
WHERE Users.Gender = 'Male';

This makes it easy to get combined data from multiple tables at once.

Handling NULL Values

I also learned how to deal with missing data. NULL means the value is unknown or not given.

• Find users who have no email:

SELECT * FROM Users WHERE Email IS NULL;

• Find users who have an email:

SELECT * FROM Users WHERE Email IS NOT NULL;

This is important because real-world data is often incomplete, and we need to handle it properly.

Advanced SQL Features

I also learned some advanced SQL techniques like:

• Nested Subqueries: Queries inside other queries for complex filtering.
• Common Table Expressions (CTEs): Temporary tables for cleaner queries.
• Window Functions: Special functions for ranking and calculations over groups of data.

For example, ranking flight fares:

SELECT FlightNo, Fare, RANK() OVER (ORDER BY Fare DESC) AS Rank FROM Class;

These advanced features make SQL more powerful for data analysis.

My Experience and Reflections

At first, SQL felt difficult because of its strict syntax, but after practice, I realized it follows simple logic. Learning how databases actually work has made me more confident in handling real-world data problems.

One of the most useful things was understanding how to properly design a database. A good design makes data retrieval easy and efficient, while a bad design can cause many problems.

I also started applying SQL in personal projects, like creating a small database to track my expenses. It has already helped me see my spending patterns clearly.

The hardest part for me was learning nested subqueries and window functions. These require a different way of thinking, but once I got them, I saw how useful they are for complex data analysis.

Conclusion

This unit gave me a deep understanding of SQL and databases. From the basics of ACID properties to writing advanced queries, I learned how databases store, retrieve, and manage data efficiently.

Creating an airline ticketing system as a project made everything more practical and fun. Now, I understand how real-world applications use databases and how important they are in daily life.

As I continue learning, I want to explore database optimization, indexing, and even NoSQL databases. This unit has given me the foundation to move forward with confidence in database management.

SQL was scary at first, but now I see it as an amazing tool that helps solve data problems. I’m excited to keep improving and applying what I’ve learned!