Scribd
Upload
52 views2 pages

SQL Project Library System

The document outlines the creation of a database named LibraryDB, which includes tables for Authors, Books, Members, and Loans. It also contains SQL commands to insert sample data into these tables and queries to retrieve information such as available books, members who have borrowed books, and the count of books per author. The structure supports a library management system with relationships between authors, books, and members.

Uploaded by

Bhoomi Agarwal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
52 views2 pages

SQL Project Library System

The document outlines the creation of a database named LibraryDB, which includes tables for Authors, Books, Members, and Loans. It also contains SQL commands to insert sample data into these tables and queries to retrieve information such as available books, members who have borrowed books, and the count of books per author. The structure supports a library management system with relationships between authors, books, and members.

Uploaded by

Bhoomi Agarwal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

CREATE DATABASE IF NOT EXISTS LibraryDB;

USE LibraryDB;

-- Table for Authors


CREATE TABLE Authors (
AuthorID INT PRIMARY KEY,
Name VARCHAR(100),
Country VARCHAR(50)
);

-- Table for Books


CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Genre VARCHAR(50),
AuthorID INT,
YearPublished INT,
Available BOOLEAN,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Table for Members


CREATE TABLE Members (
MemberID INT PRIMARY KEY,
FullName VARCHAR(100),
JoinDate DATE
);

-- Table for Book Loans


CREATE TABLE Loans (
LoanID INT PRIMARY KEY,
BookID INT,
MemberID INT,
LoanDate DATE,
ReturnDate DATE,
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

-- Insert Authors
INSERT INTO Authors VALUES (1, 'J.K. Rowling', 'UK');
INSERT INTO Authors VALUES (2, 'George R.R. Martin', 'USA');
INSERT INTO Authors VALUES (3, 'J.R.R. Tolkien', 'UK');

-- Insert Books
INSERT INTO Books VALUES (101, 'Harry Potter', 'Fantasy', 1, 1997, TRUE);
INSERT INTO Books VALUES (102, 'Game of Thrones', 'Fantasy', 2, 1996, TRUE);
INSERT INTO Books VALUES (103, 'The Hobbit', 'Fantasy', 3, 1937, FALSE);

-- Insert Members
INSERT INTO Members VALUES (201, 'Alice Smith', '2023-01-10');
INSERT INTO Members VALUES (202, 'Bob Johnson', '2023-02-15');

-- Insert Loans
INSERT INTO Loans VALUES (301, 103, 201, '2024-12-01', NULL);

-- Query: List all available books


SELECT * FROM Books WHERE Available = TRUE;

-- Query: Show all members who have borrowed books


SELECT Members.FullName, Books.Title FROM Loans JOIN Members ON Loans.MemberID =
Members.MemberID JOIN Books ON Loans.BookID = Books.BookID;

-- Query: Count books per author


SELECT Authors.Name, COUNT(Books.BookID) AS BookCount FROM Books JOIN Authors ON
Books.AuthorID = Authors.AuthorID GROUP BY Authors.Name;

You might also like