SQL

Lecture Notes: Introduction to SQL (Structured Query Language)

Course: Introduction to Database Management
Topic: Relational Databases & SQL
Target Audience: Beginners with basic knowledge of spreadsheets (like Excel).

1. Why SQL? The Problem with Flat Files (Spreadsheets)

Imagine you are managing a university's data using a single massive Excel spreadsheet called students_and_courses.xlsx.

You have columns for Student_Name, Student_ID, Course_Name, Professor, and Grade.

If a student takes 5 courses, their name and ID are repeated 5 times (Data Redundancy).

If a professor changes their name, you must update it in hundreds of rows (Update Anomaly).

If you delete the last student in a course, you lose the course information entirely (Deletion Anomaly).

The Problems:

Data Redundancy: Wasted storage and messy data.

Inconsistency: A single fact exists in multiple places; they might not match.

No Integrity: Nothing stops you from entering "A+" in a Grade column meant for numbers, or entering a non-existent Student ID.

Inflexibility: You can't easily ask complex questions like "Which professors teach more than 3 courses?"

SQL & Relational Databases solve this by separating data into related, structured tables and enforcing strict rules on that data.

2. What is SQL and a Relational Database?

A Relational Database is a collection of data organized into Tables (Relations).
SQL (Structured Query Language) is the programming language we use to create, modify, and retrieve that data.

A Table is a grid of data with strict columns.
Analogy: Think of a Table Schema as a digital filing cabinet drawer with labeled folders.

The Table Name (e.g., Students) is the drawer.

The Columns (e.g., ID, Name) are the folder labels.

The Rows (e.g., 1, Alice) are the individual documents inside.

3. The Four Core Concepts of SQL (Mirroring OOP)

While OOP has 4 pillars, SQL has 4 essential building blocks to ensure your data is powerful and safe.

Concept 1: Tables, Rows, and Columns (Classes, Objects, Attributes)

Table (Class): The blueprint defining the structure.

Row / Record (Object): A single, unique instance of that blueprint.

Column / Field (Attribute): A specific piece of data for that instance.

Example (Code):

sql

-- This is the BLUEPRINT (The CLASS) CREATE TABLE Students (    StudentID INT,    FirstName VARCHAR(50),    LastName VARCHAR(50),    EnrollmentDate DATE ); -- These are the OBJECTS (The ROWS) INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate) VALUES (1, 'Alice', 'Smith', '2025-01-15'); INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate) VALUES (2, 'Bob', 'Johnson', '2025-01-16');

Concept 2: Constraints (Encapsulation / Data Integrity)

Definition: Rules applied to columns to protect data integrity and prevent invalid data from entering the table. This is the database equivalent of "private attributes" and validation in OOP.

Why? If your application makes a mistake, the database will reject the bad data, acting as a safety net.

Example (Code):

sql

CREATE TABLE Products (    ProductID INT PRIMARY KEY,          -- Ensures every row is unique (like a unique Object ID)    ProductName VARCHAR(100) NOT NULL,  -- Prevents empty values (data cannot be 'None')    Price DECIMAL(10,2) CHECK (Price > 0), -- Validates that price is always positive    Category VARCHAR(50) DEFAULT 'General' -- Provides a default if none is given ); -- This will FAIL because of the CHECK constraint: INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Laptop', -10); -- Error: CHECK constraint failed!

Concept 3: Views (Abstraction / Hiding Complexity)

Definition: A View is a virtual table based on the result of a complex SQL query. It does not store data itself; it just shows specific data to the user.

Analogy: It acts like a GUI Dashboard in a car. You don't see the thousands of engine sensors; you just see an easy-to-read "Speedometer" and "Fuel Gauge".

Why? It hides complex joins and calculations from the end-user or application developer, making life simpler and more secure.

Example (Code):

sql

-- Under the hood, this joins 3 tables to get the full order details. -- The user doesn't want to write this every time. CREATE VIEW CustomerOrderSummary AS SELECT Customers.Name, Orders.OrderDate, Products.ProductName, OrderItems.Quantity FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID JOIN Products ON OrderItems.ProductID = Products.ProductID; -- The user just uses the simple VIEW (Abstraction): SELECT * FROM CustomerOrderSummary WHERE Name = 'Alice'; -- They see a clean, simple report without knowing the messy join logic!

Concept 4: Joins and Foreign Keys (Relationships / Polymorphism)

Definition: The power of SQL is not in a single table, but in relating tables to each other. A Foreign Key is a column in one table that points to the Primary Key of another table. Joins are the SQL operations that combine data from these related tables.

Why? This eliminates redundancy (you don't duplicate professor info for every student) and allows you to combine data dynamically—similar to how Polymorphism allows different object types to work together via a shared interface.

Example (Code):

sql

-- Table 1: Professors (Parent) CREATE TABLE Professors (    ProfessorID INT PRIMARY KEY,    Name VARCHAR(100) ); -- Table 2: Courses (Child) CREATE TABLE Courses (    CourseID INT PRIMARY KEY,    Title VARCHAR(100),    ProfessorID INT,    FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID) -- The Relationship ); -- Inserting data INSERT INTO Professors VALUES (1, 'Dr. Smith'); INSERT INTO Courses VALUES (101, 'Physics 101', 1); -- The JOIN (Combining data from both tables) SELECT Courses.Title, Professors.Name FROM Courses JOIN Professors ON Courses.ProfessorID = Professors.ProfessorID; -- Output: Title: Physics 101, Name: Dr. Smith

4. The 4 Main SQL Operations (CRUD)

Just like OOP objects have methods (behaviors), SQL rows have operations. We call these CRUD:

Example (Putting it all together):

sql

-- CREATE (Add a new student) INSERT INTO Students (ID, Name) VALUES (3, 'Charlie'); -- READ (Get all students) SELECT * FROM Students; -- UPDATE (Charlie changes his name) UPDATE Students SET Name = 'Charles' WHERE ID = 3; -- DELETE (Remove a student) DELETE FROM Students WHERE ID = 3;

5. Normalization (The "Design Pattern" of SQL)

Just as OOP has Design Patterns (like Singleton, Factory), SQL has Normalization.
Normalization is the process of organizing columns and tables to minimize data redundancy.

The Rule of Thumb:

One fact, one place.

If you find yourself typing the same data over and over across different rows, it's time to split that data into its own table and link it with a Foreign Key (exactly like we did with Professors and Courses above).

6. Summary Table: OOP vs. SQL

7. Practice Exercise (Parallel to the OOP Library)

Task: Design a simple database system for a Library.

Create a Books table.

Columns: BookID (Primary Key), Title, Author, ISBN (Unique), IsCheckedOut (Boolean/Integer 0 or 1).

Create a Borrowers table.

Columns: BorrowerID (Primary Key), Name, Email.

Create a Loans table (The relationship link).

Columns: LoanID (Primary Key), BookID (Foreign Key), BorrowerID (Foreign Key), DateBorrowed, DateReturned.

SQL Queries to write:

Insert 2 books and 2 borrowers.

Write a SELECT query using a JOIN to show which Borrower has which Book.

Write an UPDATE query to mark a book as "Checked Out" when borrowed.