Library Management | Skillpundit | India's No.1 | Learning

Tables Overview

S.No	Table Name	No. of Fields	Demo Data
S.No	Table Name	No. of Fields	Create	Insert
1	authors	4	Create	Insert
2	class	4	Create	Insert
3	subject	5	Create	Insert
4	shelfs	4	Create	Insert
5	books	9	Create	Insert
6	members	7	Create	Insert
7	members_ships	5	Create	Insert
8	memberships_types	5	Create	Insert
9	issue_books	7	Create	Insert
10	fines	6	Create	Insert

Table Structure: authors

Field Name	Data Type
Field Name	Id	int
author_name	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "authors"

CREATE TABLE authors (
id int,
author_name varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE authors
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "authors"

INSERT INTO authors (id, author_name, created_at, updated_at) VALUES
( 1, 'Brian Christian', '2020-12-11 12:35:02', '2020-12-11 12:35:02'),
( 2, 'Thomas. H. Cormen','2020-12-11 12:35:02','2020-12-11 12:35:02'),
( 3, 'Brian W. Kernighan','2020-12-11 12:35:02','2020-12-11 12:35:02');

Table Structure: class

Field Name	Data Type
Field Name	Id	int
class	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "class"

CREATE TABLE class (
id int,
class varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE class
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "class"

INSERT INTO class (id, class, created_at, updated_at) VALUES
(1, '10th', '2020-12-11 12:33:51', '2020-12-11 12:33:51'),
(2, 'Inter', '2020-12-11 12:33:51', '2020-12-11 12:33:51'),
(3, 'B.tech', '2020-12-11 12:33:51', '2020-12-11 12:33:51');

Table Structure: subject

Field Name	Data Type
Field Name	Id	int
class_id	varchar
subject	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "subject"

CREATE TABLE subjects (
id int,
class_id varchar(100),
subject varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE subjects
ADD PRIMARY KEY (`id`);

Following SQL inserts 5 records in "subjects"

INSERT INTO subjects (id, class_id, subject, created_at, updated_at) VALUES
(1, '1', 'english', '2020-12-11 12:33:30', '2020-12-11 12:33:30'),
(2, '2', 'mathematics', '2020-12-11 12:33:30', '2020-12-11 12:33:30'),
(3, '3', 'science', '2020-12-11 12:33:30', '2020-12-11 12:33:30');

Table Structure: shelfs

Field name	Data type
Field name	Id	int
shelf	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "shelfs"

CREATE TABLE shelfs (
id int,
shelf varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE shelfs
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "shelfs"

INSERT INTO shelfs (`id`, `shelf`, `created_at`, `updated_at`) VALUES
(1, 'Ashelf', '2020-12-11 12:33:06', '2020-12-11 12:33:06'),
(2, 'Bshelf', '2020-12-11 12:33:06', '2020-12-11 12:33:06'),
(3, 'Cshelf', '2020-12-11 12:33:06', '2020-12-11 12:33:06');

Table Structure: books

Field name	Data type
Field name	Id	int
book_name	varchar
book_entry_date	date
writer	varchar
class	varchar
subject	varchar
shelf_number	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "books"

CREATE TABLE books (
id int,
book_name varchar(100),
book_entry_date date,
writer varchar(100),
class varchar(100),
subject varchar(100),
shelf_number varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE books
ADD PRIMARY KEY (`id`);

Following SQL inserts 9 records in "books"

INSERT INTO books (id, book_name, book_entry_date, writer, class, subject, shelf_number, created_at, updated_at) VALUES
(1, 'Englishbook', '2020-05-05', '1', '1', '1', '1', '2020-12-11 12:15:06', '2020-12-11 12:44:49'),
(2, 'mathematics book', '2020-05-06','2', '2', '2', '2', '2020-12-11 12:15:06', '2020-12-11 12:15:06'),
(3, 'Science Book', '2020-05-07','3', '3', '3', '3', '2020-12-11 12:15:06', '2020-12-11 12:15:06');

Table Structure: members

Field name	Data type
Field name	Id	int
member_id	varchar
address	varchar
mobile	varchar
alternate_mobile	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "members"

CREATE TABLE members (
id int,
member_id varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE members
ADD PRIMARY KEY (`id`);

Following SQL inserts 7 records in "members"

INSERT INTO members (id, member_id, address, mobile, alternate_mobile, created_at, updated_at) VALUES
(1, 'M001', 'Hyderabad', '7207255438', '7207255438', '2020-12-11 12:19:34', '2020-12-11 12:47:04'),
(2, 'M002', 'Mumbai', '9563245874','9564211587', '2020-12-11 12:19:34', '2020-12-11 12:19:34'),
(3, 'M003', 'Delhi', '6985422458', '5874522145', '2020-12-11 12:19:34', '2020-12-11 12:19:34');

Table Structure: member_ships

Field name	Data type
Field name	Id	int
member_id	varchar
membership_type	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "member_ships"

CREATE TABLE member_ship (
id int,
member_id varchar(100),
membership_type varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE member_ship
ADD PRIMARY KEY (`id`);

Following SQL inserts 5 records in "member_ship"

INSERT INTO member_ship (id, member_id,membership_type, created_at, updated_at) VALUES
(1, 'M001',’1’, '2020-12-11 12:47:31', '2020-12-11 12:47:31'),
(2, 'M002',’2’, '2020-12-11 12:47:31','2020-12-11 12:47:31'),
(3, 'M003',’3’, '2020-12-11 12:47:31', '2020-12-11 12:47:31');

Table Structure: memberships_types

Field name	Data type
Field name	Id	int
membership_type	varchar
number_of_books	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "memberships_types"

CREATE TABLE memberships_types (
id int,
membership_types varchar(100),
number_of_books varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE membership_types
ADD PRIMARY KEY (`id`);

Following SQL inserts 5 records in "membership_types"

INSERT INTO membership_types (id, membership_type,number_of_books, created_at, updated_at) VALUES
(1, Premium,’20’, '2020-12-11 12:47:31', '2020-12-11 12:47:31'),
(2, 'Gold',’10’, '2020-12-11 12:47:31','2020-12-11 12:47:31'),
(3, 'Silver',’5’,'2020-12-11 12:47:31', '2020-12-11 12:47:31');

Table Structure: issue_books

Field name	Data type
Field name	Id	int
member_id	Varchar
book_id	Varchar
issue_date	Date
return_date	Date
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "issue_books"

CREATE TABLE issue_books (
id int,
member_id varchar(100),
book_id varchar(100),
issue_date date,
return_date date,
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE issue_books
ADD PRIMARY KEY (`id`);

Following SQL inserts 7 records in "issue_books"

INSERT INTO issue_books (id, member_id, book_id, issue_date, return_date, created_at, updated_at) VALUES
(1, 'M001', '1', '2020-12-11', '2020-12-20', '2020-12-11 12:23:44', '2020-12-11 12:23:44'),
(2, 'M002', '2', '2020-12-10', '2020-12-15', '2020-12-11 12:23:44', '2020-12-11 12:23:44'),
(3, 'M003', '3', '2020-11-15', '2020-12-05', '2020-12-11 12:23:44', '2020-12-11 12:23:44');

Table Structure: fines

Field name	Data type
Field name	Id	int
member_id	varchar
book_return_date	date
fine	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "fines"

CREATE TABLE fines (
id int,
member_id varchar(100),
book_return_date date,
fine varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE fines
ADD PRIMARY KEY (`id`);

Following SQL inserts 6 records in "fines"

INSERT INTO fines (id, member_id, book_return_date, fine, created_at, updated_at) VALUES
(1, 'M001', '2020-12-20', '120', '2020-12-11 12:25:41', '2020-12-11 12:25:41'),
(2, 'M002', '2020-12-15', '100', '2020-12-11 12:25:41', '2020-12-11 12:25:41'),
(3, 'M003', '2020-12-05', '250', '2020-12-11 12:25:41', '2020-12-11 12:25:41');

Questions

1. How many types of memberships are there?

A. SELECT * FROM `membership_types`

2. Display the members' details in this system?

A. SELECT* FROM `members`

3. Display the shelf names?

A. SELECT * FROM `shelfs`

4. Dispaly the author name of the book in our system?

A. SELECT * FROM `authors`

5. Display the count of members in our system?

A. SELECT count(*) FROM `members`

6. Display the date when the book is entered?

A. SELECTb.id,b.book_name,b.book_entry_date from books b

7. How much fine has been paid based on member id?

A. SELECT f.id,f.member_id,f.fine from fines f where f.member_id="M001"

8. Dispay the count of books in our system?

A. SELECT * FROM members where DATE(created_at) = DATE('2020-12-11')

9. For which class books are there in our system?

A. SELECT b.id,b.book_name,c.class from books b left join class c on b.class=c.id

10. Display on which date the record is entered?

A. SELECT count(*) FROM `books`

11. For which subject which author is assigned in this system?

A. SELECT b.id,b.book_name,a.author_name from books b LEFT join authors a on b.writer=a.id

12. Display the return date of the book issue based on member id?

A. SELECT ib.id,ib.member_id,ib.return_date from issue_books ib where ib.member_id="M001"

13. In which shelf the particular book is available?

A. SELECTb.id,b.book_name,s.shelf from books b left join shelfs s on b.shelf_number=s.id

14. Which subjects books are there in our system?

A. SELECT s.id,c.class,s.subject from subjects s left join class c on s.class_id=c.id

15. Display which member took which membership type?

A. SELECT ms.id,ms.member_id,mt.membership_types from member_ship ms left join membership_types mt on ms.membership_type=mt.id

Web Tutorials

</> SQL Tutorial

Library Management Project

Database Name: library_management

Practical Questions