24 September 2023
(a)
There exists a table CUSTOM containing the columns Customer number
(NO, integer), Customer Name(CNAME, character). Balance due(BALA, numeric) and date of transaction(DT, date)
Write MySQL statements to
Display the customer number, minimum balance due and total of balances due grouped by customer number.
(ii)
Display the customer number, maximum balance due and number of balances due grouped by customer number.
(ill) Display all the rows where the balance due is less than average
balance due.
(iv)
Display all the rows where the name ends with 'A'.
Display the customer number, balance due and date of transaction for date of transaction before July 25, 2013.
(b)
There exists a table LIBRARY having the columns Book Number (BKNO, integer), Name of the book (BKNAME, character), Type of book (TYPE, character) Number of copics(NOC, integer), value of the book (VALU, numeric) and Date of Publication (DP, date). Write MySQL queries for the following
(i)
Display all the rows from this table where the value of the book is below the average book value.
(ii)
Display the columns Book number, Name of the book and Date of Publication from this table where the value of the book is equal to the highest book value.
(ill) Display Type of book and average value of the books from
this table grouped as per type.
(iv)
Display the Book Number, Value of the book and *scrap value' which is 5% of the value of the book from this table.
(v)
Display all the rows where the value of the book is above 1000.