DBMS Lab Cycle 5

   5. The schema of book store database is as follows

customer (cust_id, cust_name, cust_phone)

book (book_id, title, auth_name, unit_price, pub_name, pub_year)

sale (sale_id, cust_id, book_id, order_date, quantity)

author(auth_name, country)

publisher(pub_name, pub_addr)

first attribute in each table is the primary key, foreign keys have same name as primary key.

i. Calculate the sales for a financial year 2021-22? 

ii. Display publisher wise total price of books published, except for a particular publisher PHI

iii. Write the SQL Query to Find the publisher wise total price of books published

iv. Write the SQL Query to display the name of the customer and title of the books purchased by them.

v.Find the title of books with author name and country published in year 2021;

vi. Get the titles and author name of all books written by authors not living in india.

vii.Write the SQL Query to display the cusomer ids and the total quantity of books purchased by them in the decreasing order of total quantity purcased.

TEST CASES

mysql> select * from customer;

+---------+-----------+------------+

| cust_id | cust_name | cust_phone |

+---------+-----------+------------+

|     100 | hari      |      12345 |

|     101 | john      |      23456 |

|     102 | ali       |      34567 |

+---------+-----------+------------+

mysql> select * from author;

+-----------+---------+

| auth_name | country |

+-----------+---------+

| Brown     | US      |

| Forouzan  | US      |

| Navathe   | India   |

| Samantha  | India   |

| Tanenbaum | US      |

| V Prasad  | India   |

+-----------+---------+

mysql> select * from publisher;

+----------+----------+

| pub_name | pub_addr |

+----------+----------+

| Pearson  | London   |

| PHI      | Delhi    |

| TMH      | UP       |

+----------+----------+

mysql> select * from book;

+---------+--------+-----------+------------+----------+----------+

| book_id | title  | auth_name | unit_price | pub_name | pub_year |

+---------+--------+-----------+------------+----------+----------+

|    2001 | DS     | Samantha  |        500 | PHI      |     2020 |

|    2002 | CP     | V Prasad  |        300 | TMH      |     2019 |

|    2003 | DBMS   | Navathe   |        900 | Pearson  |     2018 |

|    2004 | DC     | Forouzan  |        700 | TMH      |     2021 |

|    2005 | CN     | Tanenbaum |        800 | Pearson  |     2018 |

|    2006 | Python | Brown     |        800 | TMH      |     2021 |

+---------+--------+-----------+------------+----------+----------+

mysql> select * from sale;

+---------+---------+---------+------------+----------+

| sale_id | cust_id | book_id | order_date | quantity |

+---------+---------+---------+------------+----------+

|     301 |     100 |    2001 | 2021-01-02 |        3 |

|     302 |     100 |    2002 | 2019-04-02 |        4 |

|     303 |     100 |    2003 | 2019-04-02 |        2 |

|     304 |     101 |    2002 | 2021-05-02 |        2 |

|     305 |     101 |    2005 | 2021-05-02 |        1 |

|     306 |     102 |    2006 | 2022-01-02 |        2 |

+---------+---------+---------+------------+----------+