Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 17
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| 4mt21ai017_library |
| 4mt21ai017_movie |
| 4mt21ai017_order |
| college |
| cs050 |
| information_schema |
| library |
| movie |
| mysql |
| orders |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
14 rows in set (0.00 sec)

mysql> use 4mt21ai017_library;

Database changed
mysql> show tables;
+------------------------------+
| Tables_in_4mt21ai017_library |
+------------------------------+
| book |
| book_authors |
| book_copies |
| book_lending |
| card |
| library_programme |
| pub_year |
| publisher |
+------------------------------+
8 rows in set (0.00 sec)

mysql> desc book;

+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| book_id | int | NO | PRI | NULL | |
| title | varchar(20) | YES | | NULL | |
| publisher_name | varchar(25) | YES | MUL | NULL | |
| pub_year | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc book_authors;

+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| book_id | int | YES | MUL | NULL | |
| author_name | varchar(25) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc book_copies;

+--------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+-------+
| book_id | int | NO | PRI | NULL | |
| programme_id | int | NO | PRI | NULL | |
| no_of_copies | int | YES | | NULL | |
+--------------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc book_lending;

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| book_id | int | NO | PRI | NULL | |
| programme_id | int | NO | PRI | NULL | |
| card_id | int | NO | PRI | NULL | |
| date_out | varchar(20) | YES | | NULL | |
| due_date | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc card;

+---------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| card_id | int | NO | PRI | NULL | |
+---------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc library_programme;

+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| programme_id | int | NO | PRI | NULL | |
| programme_name | varchar(25) | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc pub_year;

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pub_year | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc publisher;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(25) | NO | PRI | NULL | |
| address | varchar(30) | YES | | NULL | |
| phone | bigint | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from book;

+---------+-------------------+--------------------+-------------+
| book_id | title | publisher_name | pub_year |
+---------+-------------------+--------------------+-------------+
| 101 | 2 States | Arihant publishers | 19 May 2017 |
| 104 | Screwed by Murder | Penguin books | 6 Aug 2017 |
| 105 | Merry Christmas | Xpert books | 17 Dec 2022 |
+---------+-------------------+--------------------+-------------+
3 rows in set (0.00 sec)

mysql> select * from book_authors;

+---------+-----------------+
| book_id | author_name |
+---------+-----------------+
| 101 | Chetan Bhagat |
| 104 | Saurabh Katyal |
| 105 | Sriram Raghavan |
+---------+-----------------+
3 rows in set (0.00 sec)

mysql> select * from book_lending;

+---------+--------------+---------+------------+------------+
| book_id | programme_id | card_id | date_out | due_date |
+---------+--------------+---------+------------+------------+
| 101 | 1 | 2001 | 14jan2017 | 20jan2017 |
| 104 | 4 | 2001 | 20marc2017 | 2apr2017 |
| 105 | 5 | 2001 | 6feb2017 | 25marc2017 |
+---------+--------------+---------+------------+------------+
3 rows in set (0.00 sec)

mysql> select * from book_copies;

+---------+--------------+--------------+
| book_id | programme_id | no_of_copies |
+---------+--------------+--------------+
| 101 | 1 | 5260 |
| 104 | 4 | 6875 |
| 105 | 5 | 7896 |
+---------+--------------+--------------+
3 rows in set (0.00 sec)

mysql> select * from card;

+---------+
| card_id |
+---------+
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| 2005 |
+---------+
5 rows in set (0.00 sec)

mysql> select * from pub_year;

+-------------+
| pub_year |
+-------------+
| 19 May 2017 |
| 6 Aug 2017 |
| 17 Dec 2022 |
+-------------+
3 rows in set (0.00 sec)

mysql> select * from library_programme;

+--------------+----------------+------------------+
| programme_id | programme_name | address |
+--------------+----------------+------------------+
| 1 | Rom-com | Mumbai |
| 2 | Folk-lore | Varanasi |
| 3 | Biography | Assam |
| 4 | Crome-thriller | Himachal Pradesh |
| 5 | Dark-comedy | Kerala |
+--------------+----------------+------------------+
5 rows in set (0.00 sec)

mysql> select * from publisher;

+--------------------+--------------------------+------------+
| name | address | phone |
+--------------------+--------------------------+------------+
| Arihant publishers | Jai-colony Uttar-Pradesh | 8976589645 |
| Bandup books | Raniganj villa Udgaon | 7895469856 |
| Geeko books | Andhadhun complex,Bandra | 7760069856 |
| Penguin books | 18-Rani-penthouse Kasoli | 9876548698 |
| Xpert books | Freddy colony,ooty | 8795645689 |
+--------------------+--------------------------+------------+
5 rows in set (0.00 sec)

mysql> select a.book_id,a.title,a.publisher_name,b.author_name,c.no_of_copies,

-> d.programme_id from book a,book_authors b,book_copies c,
-> library_programme d where a.book_id=b.book_id and a.book_id=c.book_id and
c.programme_id=d.programme_id;
+---------+-------------------+--------------------+-----------------
+--------------+--------------+
| book_id | title | publisher_name | author_name | no_of_copies
| programme_id |
+---------+-------------------+--------------------+-----------------
+--------------+--------------+
| 101 | 2 States | Arihant publishers | Chetan Bhagat | 5260
| 1 |
| 104 | Screwed by Murder | Penguin books | Saurabh Katyal | 6875
| 4 |
| 105 | Merry Christmas | Xpert books | Sriram Raghavan | 7896
| 5 |
+---------+-------------------+--------------------+-----------------
+--------------+--------------+
3 rows in set (0.00 sec)

mysql> select card_id from book_lending where due_date between "14jan2017" and
"6feb2017" group by card_id having count(*)>0;
+---------+
| card_id |
+---------+
| 2001 |
+---------+
1 row in set (0.00 sec)

mysql> delete from book where book_id=101;

Query OK, 1 row affected (0.01 sec)

mysql> select * from book;

+---------+-------------------+----------------+-------------+
| book_id | title | publisher_name | pub_year |
+---------+-------------------+----------------+-------------+
| 104 | Screwed by Murder | Penguin books | 6 Aug 2017 |
| 105 | Merry Christmas | Xpert books | 17 Dec 2022 |
+---------+-------------------+----------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from pub_year;

+-------------+
| pub_year |
+-------------+
| 6 Aug 2017 |
| 17 Dec 2022 |
+-------------+
2 rows in set (0.00 sec)

mysql> create view book_list as select

b.book_id,b.title,c.no_of_copies,l.programme_id from book b,book_copies c,
->book_lending l where b.book_id=c.book_id and c.programme_id=l.programme_id;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from book_list;

+---------+-------------------+--------------+--------------+
| book_id | title | no_of_copies | programme_id |
+---------+-------------------+--------------+--------------+
| 104 | Screwed by Murder | 6875 | 4 |
| 105 | Merry Christmas | 7896 | 5 |
+---------+-------------------+--------------+--------------+
2 rows in set (0.00 sec)

mysql> show tables;

+------------------------------+
| Tables_in_4mt21ai017_library |
+------------------------------+
| book |
| book_authors |
| book_copies |
| book_lending |
| book_list |
| card |
| library_programme |
| pub_year |
| publisher |
+------------------------------+
9 rows in set (0.00 sec)
mysql>