قاعدة بيانات مكتبة المدرسة

CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  Title TEXT NOT NULL,
  Author TEXT,
  Copies INTEGER DEFAULT 1
);
CREATE TABLE Students (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Class TEXT
);
CREATE TABLE Loans (
  LoanID INTEGER PRIMARY KEY,
  BookID INTEGER REFERENCES Books(BookID),
  StudentID INTEGER REFERENCES Students(StudentID),
  LoanDate TEXT,
  ReturnDate TEXT
);

INSERT INTO Books (BookID, Title, Author, Copies) VALUES
 (1, 'أساسيات الشبكات', 'وزارة التربية', 3),
 (2, 'البرمجة بلغة C#', 'وزارة التربية', 2),
 (3, 'صيانة الحاسوب', 'وزارة التربية', 1);
INSERT INTO Students (StudentID, Name, Class) VALUES
 (1, 'أحمد', 'الثالث 1'), (2, 'سارة', 'الثالث 2');
INSERT INTO Loans (LoanID, BookID, StudentID, LoanDate, ReturnDate) VALUES
 (1, 1, 1, '2026-10-01', NULL),
 (2, 2, 2, '2026-10-03', '2026-10-08');

SELECT Students.Name, Books.Title, Loans.LoanDate
FROM Loans
JOIN Students ON Loans.StudentID = Students.StudentID
JOIN Books ON Loans.BookID = Books.BookID
ORDER BY Loans.LoanDate DESC;

SELECT Books.Title, Students.Name
FROM Loans
JOIN Books ON Loans.BookID = Books.BookID
JOIN Students ON Loans.StudentID = Students.StudentID
WHERE Loans.ReturnDate IS NULL;

UPDATE Loans SET ReturnDate = '2026-10-10' WHERE LoanID = 1;

SELECT Books.Title, COUNT(*) AS Times
FROM Loans JOIN Books ON Loans.BookID = Books.BookID
GROUP BY Books.Title
ORDER BY Times DESC;