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
|
drop table if exists borrowed_book;
drop table if exists book;
drop table if exists user_account;
create table user_account (
uid serial primary key,
username text unique not null,
name text,
password text
);
insert into user_account (username, name) values ('kamal', 'Kamal Wickramanayake');
insert into user_account (username, name) values ('tharindu', 'Tharindu Fernando');
create table book (
bookid serial primary key,
name text,
author text
);
insert into book (name, author) values ('Tasty Cooking', 'Bhagya Ratnayake');
insert into book (name, author) values ('Smiling Kitty', 'Seema Farwin');
create table borrowed_book (
uid integer references user_account(uid) on delete restrict,
bookid integer references book(bookid) on delete restrict,
borrowed_time timestamp
);
-- Warning! We assume uid and bookid to be consecutive integers starting from 1
-- uid 1 borrowed bookid 1
insert into borrowed_book (uid, bookid) values (1, 1);
-- uid 1 borrowed bookid 2
insert into borrowed_book (uid, bookid) values (1, 2);
-- uid 2 borrowed bookid 2
insert into borrowed_book (uid, bookid) values (2, 2);
-- List all borrowed book data
select * from borrowed_book ;
-- Print the book names of bookes borrowed by user with the username 'kamal'
select b.name from book as b, user_account as u, borrowed_book as bw where u.username = 'kamal' and u.uid = bw.uid and bw.bookid = b.bookid;
|
