Home » Courses » SQL Crash Course

Database Basics

Lesson 2: Database Basics

In this lesson, we will learn the fundamentals of databases and how to interact with them using SQL. We’ll cover basic concepts, practical examples, and activities to help you understand how databases work.

What is a Database?

A database is a structured collection of data. It is designed to store, manage, and retrieve information efficiently. A database organizes data in tables, rows, and columns, making it easy to access and update.

Components of a Database Table

Table: A collection of related data organized into rows and columns.
Column: A single data type or attribute. For example, Name, Age, Salary.
Row: A record or individual data entry in the table.

Example of a Database Table

EmployeeID	Name	Department	Salary
1	John Doe	HR	50000
2	Jane Smith	IT	60000

In this table:

Columns: EmployeeID, Name, Department, Salary
Rows: Individual records for each employee, such as John Doe and Jane Smith.

Types of Databases

There are two main types of databases:

Relational Database (RDBMS): Stores data in tables with relationships between them. Example: MySQL, PostgreSQL, SQL Server.
Non-relational Database (NoSQL): Stores data in a non-tabular format. Example: MongoDB, CouchDB.

SQL in Database Management

SQL (Structured Query Language) is the language we use to interact with relational databases. It allows us to perform operations such as creating, reading, updating, and deleting data (CRUD operations).

Basic SQL Queries

Here are some fundamental SQL commands that you’ll use to manage data in a database:

1. Create a Database

CREATE DATABASE SchoolDB;

This command creates a new database called “SchoolDB”.

2. Create a Table

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

This command creates a table called “Students” with columns for StudentID, FirstName, LastName, Age, and Grade.

3. Insert Data into a Table

INSERT INTO Students (StudentID, FirstName, LastName, Age, Grade)
    VALUES (1, 'John', 'Doe', 15, 'A');

This command inserts a record for a student with ID 1, FirstName “John”, LastName “Doe”, Age 15, and Grade “A”.

4. Select Data from a Table

SELECT * FROM Students;

This command retrieves all data from the “Students” table.

5. Update Data

UPDATE Students
    SET Grade = 'B'
    WHERE StudentID = 1;

This command updates the grade of the student with ID 1 to ‘B’.

6. Delete Data

DELETE FROM Students
    WHERE StudentID = 1;

This command deletes the record of the student with ID 1.

Activity-based Learning

Now, let’s practice some basic SQL commands with these exercises:

Exercise 1: Create a Database

Write the SQL query to create a database called “LibraryDB”.

CREATE DATABASE LibraryDB;

Exercise 2: Create a Books Table

Write the SQL query to create a table called “Books” with the following columns:

BookID: Integer, Primary Key
Title: Text
Author: Text
YearPublished: Integer

Exercise 3: Insert Data

Write the SQL query to insert the following data into the “Books” table:

BookID: 1, Title: “SQL for Beginners”, Author: “John Smith”, YearPublished: 2020
BookID: 2, Title: “Advanced SQL”, Author: “Jane Doe”, YearPublished: 2021

Exercise 4: Retrieve Data

Write the SQL query to retrieve all books published after 2019.

SELECT * FROM Books
    WHERE YearPublished > 2019;

Exercise 5: Update Data

Write the SQL query to update the “Author” of the book with BookID 2 to “Alice Johnson”.

UPDATE Books
    SET Author = 'Alice Johnson'
    WHERE BookID = 2;

Exercise 6: Delete Data

Write the SQL query to delete the book with BookID 1.

DELETE FROM Books
    WHERE BookID = 1;

Conclusion

In this lesson, we’ve learned about databases, SQL commands, and how to manage data with SQL. By practicing the queries, you can build a strong foundation in database management.

Don’t forget to complete the exercises to reinforce your learning!

SQL Crash Course