SQL Basics

Hey students! 👋 Welcome to your journey into the world of databases and SQL! In this lesson, you'll discover how to communicate with databases using SQL (Structured Query Language) - the universal language that lets you ask databases questions and get answers back. By the end of this lesson, you'll be able to retrieve, add, modify, and delete data from databases, plus learn how to filter, sort, and combine information from multiple tables. Think of SQL as your superpower for managing the massive amounts of data that power everything from social media apps to online shopping sites! 🚀

What is SQL and Why Does It Matter?

SQL stands for Structured Query Language, and it's the standard language used to communicate with relational databases. Imagine a database as a digital filing cabinet with multiple drawers (tables), and SQL is your way of asking the cabinet to show you specific files, add new ones, update existing information, or remove outdated records.

Every time you search for a video on YouTube, check your bank balance online, or scroll through Instagram, SQL queries are working behind the scenes to fetch exactly the information you need from massive databases containing millions of records. Companies like Netflix use SQL to recommend shows based on your viewing history, while banks use it to track transactions and detect fraud patterns.

A relational database organizes data into tables, which are like spreadsheets with rows and columns. Each row represents a single record (like one student's information), and each column represents a specific attribute (like name, age, or grade). The real power comes when you can connect related tables together - for example, linking a "Students" table with a "Classes" table to see which students are enrolled in which subjects.

The Four Essential SQL Commands

The foundation of SQL rests on four main commands that allow you to manipulate data: SELECT (retrieve), INSERT (add), UPDATE (modify), and DELETE (remove). Let's explore each one with practical examples!

SELECT: Retrieving Data 📊

The SELECT command is your go-to tool for asking the database questions. It's like asking "Can you show me...?" The basic syntax is straightforward:

SELECT column_name FROM table_name;

Let's say you have a table called "students" with columns for student_id, first_name, last_name, age, and grade. To see all students' names, you'd write:

SELECT first_name, last_name FROM students;

If you want to see everything in the table, use the asterisk (*) wildcard:

SELECT * FROM students;

INSERT: Adding New Data ➕

When you create a new social media account or add a song to your playlist, an INSERT command is adding that information to a database. The syntax looks like this:

INSERT INTO table_name (column1, column2, column3) 
VALUES (value1, value2, value3);

To add a new student to our students table:

INSERT INTO students (first_name, last_name, age, grade) 
VALUES ('Emma', 'Johnson', 16, 11);

UPDATE: Modifying Existing Data ✏️

Sometimes information changes - students move up a grade, people change their email addresses, or product prices get updated. The UPDATE command handles these modifications:

UPDATE table_name 
SET column1 = new_value 
WHERE condition;

To update Emma's grade from 11 to 12:

UPDATE students 
SET grade = 12 
WHERE first_name = 'Emma' AND last_name = 'Johnson';

The WHERE clause is crucial here - without it, you'd accidentally update every student's grade!

DELETE: Removing Data 🗑️

When data is no longer needed, the DELETE command removes it permanently:

DELETE FROM table_name WHERE condition;

To remove a student who has left the school:

DELETE FROM students 
WHERE student_id = 12345;

Filtering and Sorting Your Results

Raw data isn't very useful if you can't find what you're looking for quickly. SQL provides powerful tools to filter and organize your results.

The WHERE Clause: Your Data Filter 🔍

The WHERE clause acts like a search filter, showing only records that meet specific conditions. You can use various operators:

$- Equal to (=): WHERE grade = 10$

• Not equal to (<> or !=): WHERE grade <> 9
• Greater than (>): WHERE age > 16
• Less than (<): WHERE age < 18
• LIKE for pattern matching: WHERE first_name LIKE 'J%' (names starting with J)

You can combine conditions using AND, OR, and NOT:

SELECT * FROM students 
WHERE grade = 11 AND age >= 16;

ORDER BY: Sorting Your Results 📈

The ORDER BY clause sorts your results in ascending (ASC) or descending (DESC) order:

SELECT * FROM students 
ORDER BY last_name ASC;

You can sort by multiple columns too:

SELECT * FROM students 
ORDER BY grade DESC, last_name ASC;

This would show students sorted by grade (highest first), and within each grade, sorted alphabetically by last name.

Joining Tables: Connecting Related Data

Real-world databases rarely store all information in a single table. Instead, they use multiple related tables to avoid redundancy and maintain data integrity. This is where JOIN operations become essential.

Understanding Relationships 🔗

Imagine you have two tables: "students" and "enrollments". The students table contains personal information, while the enrollments table shows which classes each student is taking. Both tables share a common field (student_id) that links them together.

INNER JOIN: Finding Matching Records

An INNER JOIN returns only records that have matching values in both tables:

SELECT students.first_name, students.last_name, enrollments.class_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id;

This query would show the names of students along with their enrolled classes, but only for students who are actually enrolled in at least one class.

LEFT JOIN: Including All Records from the First Table

A LEFT JOIN returns all records from the left table, even if there's no match in the right table:

SELECT students.first_name, students.last_name, enrollments.class_name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id;

This would show all students, including those not enrolled in any classes (their class_name would appear as NULL).

Practical Example: School Database 🏫

Let's put it all together with a realistic scenario. Suppose you're helping your school's administration system. You need to find all Year 11 students enrolled in Computer Science, sorted by their last names:

SELECT s.first_name, s.last_name, s.age
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
WHERE s.grade = 11 AND e.class_name = 'Computer Science'
ORDER BY s.last_name ASC;

Notice how we used table aliases (s for students, e for enrollments) to make the query more readable.

Conclusion

Congratulations students! 🎉 You've just learned the fundamental building blocks of SQL that power virtually every digital application you use daily. You now understand how to retrieve data with SELECT, add new records with INSERT, modify existing information with UPDATE, and remove outdated data with DELETE. You've also mastered the art of filtering results with WHERE clauses, organizing data with ORDER BY, and connecting related information using JOIN operations. These skills form the foundation of database management and are essential for anyone pursuing computer science, data analysis, or software development.

Study Notes

• SQL (Structured Query Language) - The standard language for communicating with relational databases

• SELECT - Retrieves data from tables: SELECT column_name FROM table_name;

• INSERT - Adds new records: INSERT INTO table_name (columns) VALUES (values);

• UPDATE - Modifies existing records: UPDATE table_name SET column = value WHERE condition;

• DELETE - Removes records: DELETE FROM table_name WHERE condition;

• WHERE clause - Filters results based on conditions using operators (=, <>, >, <, LIKE)

• ORDER BY - Sorts results in ascending (ASC) or descending (DESC) order

• INNER JOIN - Returns only matching records from both tables

• LEFT JOIN - Returns all records from left table, matching records from right table

• Wildcard (*) - Selects all columns from a table

• Table aliases - Shortened table names (e.g., students s) for cleaner queries

• Logical operators - AND, OR, NOT for combining conditions in WHERE clauses

• Pattern matching - LIKE operator with % wildcard for partial matches