Repository navigation
Expand file tree
/
Copy pathPostgreSQL
More file actions
106 lines (87 loc) · 2.43 KB
/
Copy pathPostgreSQL
File metadata and controls
106 lines (87 loc) · 2.43 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
create database library_db;
-- Table: members
CREATE TABLE members (
member_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
join_date DATE
);
-- Table: books
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title VARCHAR(200),
author VARCHAR(100),
published_year INT,
copies_available INT
);
-- Table: borrowings
CREATE TABLE borrowings (
borrow_id SERIAL PRIMARY KEY,
member_id INT REFERENCES members(member_id),
book_id INT REFERENCES books(book_id),
borrow_date DATE,
return_date DATE
);
-- Table: fines
CREATE TABLE fines (
fine_id SERIAL PRIMARY KEY,
borrow_id INT REFERENCES borrowings(borrow_id),
fine_amount DECIMAL(6,2),
paid BOOLEAN DEFAULT FALSE
);
-- Sample member
INSERT INTO members (name, email, join_date) VALUES
('Alice Kumar', 'alice@example.com', '2023-05-01'),
('Bob Mehta', 'bob@example.com', '2023-06-15');
-- Sample book
INSERT INTO books (title, author, published_year, copies_available) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 1925, 3),
('1984', 'George Orwell', 1949, 2);
-- Sample borrowing
INSERT INTO borrowings (member_id, book_id, borrow_date, return_date) VALUES
(1, 1, '2024-06-01', '2024-06-10'),
(2, 2, '2024-06-03', NULL);
SELECT b.title, m.name
FROM borrowings br
JOIN books b ON br.book_id = b.book_id
JOIN members m ON br.member_id = m.member_id
WHERE br.return_date IS NULL;
SELECT m.name, COUNT(*) AS books_borrowed
FROM borrowings br
JOIN members m ON br.member_id = m.member_id
GROUP BY m.name;
CREATE VIEW active_borrowings AS
SELECT * FROM borrowings WHERE return_date IS NULL;
CREATE OR REPLACE FUNCTION calculate_fine(due_date DATE, return_date DATE)
RETURNS NUMERIC AS $$
DECLARE
fine NUMERIC := 0;
days_late INT;
BEGIN
IF return_date > due_date THEN
days_late := return_date - due_date;
fine := days_late * 5; -- ₹5 per day
END IF;
RETURN fine;
END;
$$ LANGUAGE plpgsql;
SELECT calculate_fine('2025-06-01', '2025-06-10');
CREATE TABLE test_cases (
id SERIAL PRIMARY KEY,
due_date DATE,
return_date DATE
);
INSERT INTO test_cases (due_date, return_date) VALUES
('2025-06-01', '2025-06-10'),
('2025-06-05', '2025-06-04'),
('2025-06-10', '2025-06-15'),
('2025-06-01', '2025-06-01');
CREATE OR REPLACE VIEW test_case_results AS
SELECT
id,
due_date,
return_date,
calculate_fine(due_date, return_date) AS fine
FROM test_cases;
SELECT * FROM test_cases;
SELECT * FROM test_case_results;