Library Database SQL Tutorial 📚

Welcome to our SQL Tutorial! In this comprehensive guide, we'll be building a Library Database from scratch. By the end, you'll have a solid understanding of SQL and be able to create, manage, and query your own databases. Let's get started!

What is SQL? 💡

SQL (Structured Query Language) is a language used to communicate with databases. It's essential for managing, manipulating, and extracting data from databases, making it an indispensable tool for developers and data analysts.

Creating a Database 🎯

Before we dive into tables, let's create a database called "Library."

sql

CREATE DATABASE Library;

📝 Note: To use the database, you'll need to select it first:

sql

USE Library;

Creating Tables 🎯

Now, let's create some tables for our Library Database. We'll need tables for Books, Authors, and Members.

Books Table 📝

sql

CREATE TABLE Books (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    author_id INT,
    publication_year INT,
    availability BOOLEAN
);

📝 Note:

id is the primary key, which uniquely identifies each book.
VARCHAR(255) is used for strings of up to 255 characters.
BOOLEAN represents true/false values.

Authors Table 📝

sql

CREATE TABLE Authors (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Members Table 📝

sql

CREATE TABLE Members (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255),
    book_id INT,
    FOREIGN KEY (book_id) REFERENCES Books(id)
);

📝 Note:

FOREIGN KEY establishes a link to another table.
book_id in the Members table refers to the id in the Books table.

Inserting Data 🎯

Now that we have our tables, let's insert some data.

sql

INSERT INTO Authors (id, first_name, last_name)
VALUES (1, 'John', 'Doe');

Querying Data 🎯

Now that we have some data, let's learn how to query it.

sql

SELECT * FROM Authors;

This will return all authors in our database.

Practice Time 🎯

Continue learning more about SQL, including updates, deletes, joins, and more! Stay tuned for Part 2 of our Library Database SQL Tutorial. Happy coding! 🚀