Example HTML page

MySQL Assignment 6

Hi,

The following questions are based on UPDATE and DELETE statements. If you are viewing this page in mobile select the desktop option mode for a clear view of the tables given below.

1. Create a table with name ‘loan’. The attributes are loan_number, name, amount, start_date, end_date, emi.

Constraints:

‘loan_number’ is primary key and auto incremented.

‘name’ should allow maximum ’10’ characters.

’emi’ is a float value.

2. Insert the following records in to the table and print the total data in ‘loan’ table.

+-------------+----------+--------+------------+------------+--------+------+
| loan_number | name     | amount | start_date | end_date   | tenure | emi  |
+-------------+----------+--------+------------+------------+--------+------+
|           1 | sudhakar |  10000 | 2015-11-23 | 2019-11-23 |     24 |  600 |
|           2 | binish   |  20000 | 2017-11-23 | 2019-11-23 |     24 | 1200 |
|           3 | jai      |  10000 | 2017-10-23 | 2018-10-23 |     12 | 1000 |
|           4 | sowmya   |  15000 | 2016-10-13 | 2018-10-13 |     24 |  700 |
|           5 | raziya   |  30000 | 2014-10-13 | 2018-10-13 |     48 |  800 |
+-------------+----------+--------+------------+------------+--------+------+

3. There is a small mistake in the above table i.e. start date of the loan_number ‘1’ should be ‘2017-11-2018’. Please change it and print the result.

+-------------+----------+--------+------------+------------+--------+------+
| loan_number | name     | amount | start_date | end_date   | tenure | emi  |
+-------------+----------+--------+------------+------------+--------+------+
|           1 | sudhakar |  10000 | 2017-11-23 | 2019-11-23 |     24 |  600 |
|           2 | binish   |  20000 | 2017-11-23 | 2019-11-23 |     24 | 1200 |
|           3 | jai      |  10000 | 2017-10-23 | 2018-10-23 |     12 | 1000 |
|           4 | sowmya   |  15000 | 2016-10-13 | 2018-10-13 |     24 |  700 |
|           5 | raziya   |  30000 | 2014-10-13 | 2018-10-13 |     48 |  800 |
+-------------+----------+--------+------------+------------+--------+------+

4. ‘Sowmya’ has taken ‘10000’ extra and requested to update her total loan to 25000 under the same loan_number with ’emi’ of 1200.  Please change it, now your table should print something like this.

+-------------+----------+--------+------------+------------+--------+------+
| loan_number | name     | amount | start_date | end_date   | tenure | emi  |
+-------------+----------+--------+------------+------------+--------+------+
|           1 | sudhakar |  10000 | 2017-11-23 | 2019-11-23 |     24 |  600 |
|           2 | binish   |  20000 | 2017-11-23 | 2019-11-23 |     24 | 1200 |
|           3 | jai      |  10000 | 2017-10-23 | 2018-10-23 |     12 | 1000 |
|           4 | sowmya   |  25000 | 2016-10-13 | 2018-10-13 |     24 | 1200 |
|           5 | raziya   |  30000 | 2014-10-13 | 2018-10-13 |     48 |  800 |
+-------------+----------+--------+------------+------------+--------+------+

5. An employee with name ‘ramana’ requested 50000 loan amount in the tenure of 12 months from the current_date with an ’emi’ of 5000. please insert this record in the loan table.

Note: Use current_date only to insert the current_date.

+-------------+----------+--------+------------+------------+--------+------+
| loan_number | name     | amount | start_date | end_date   | tenure | emi  |
+-------------+----------+--------+------------+------------+--------+------+
|           1 | sudhakar |  10000 | 2017-11-23 | 2019-11-23 |     24 |  600 |
|           2 | binish   |  20000 | 2017-11-23 | 2019-11-23 |     24 | 1200 |
|           3 | jai      |  10000 | 2017-10-23 | 2018-10-23 |     12 | 1000 |
|           4 | sowmya   |  25000 | 2016-10-13 | 2018-10-13 |     24 | 1200 |
|           5 | raziya   |  30000 | 2014-10-13 | 2018-10-13 |     48 |  800 |
|           6 | ramana   |  50000 | 2018-11-23 | 2019-11-23 |     12 | 5000 |
+-------------+----------+--------+------------+------------+--------+------+

6. Add a new column ‘PAN’ number in the loan table. The PAN data type is variable length character of size 10.

7. Suddenly ‘ramana’ has transferred to other place and requested ‘sudhakar’ to pay the loan amount. Now please update the name of ‘ramana’ to ‘sudhakar’. Your table should print something like this.

+-------------+----------+--------+------------+------------+--------+------+------+
| loan_number | name     | amount | start_date | end_date   | tenure | emi  | PAN  |
+-------------+----------+--------+------------+------------+--------+------+------+
|           1 | sudhakar |  10000 | 2017-11-23 | 2019-11-23 |     24 |  600 | NULL |
|           2 | binish   |  20000 | 2017-11-23 | 2019-11-23 |     24 | 1200 | NULL |
|           3 | jai      |  10000 | 2017-10-23 | 2018-10-23 |     12 | 1000 | NULL |
|           4 | sowmya   |  25000 | 2016-10-13 | 2018-10-13 |     24 | 1200 | NULL |
|           5 | raziya   |  30000 | 2014-10-13 | 2018-10-13 |     48 |  800 | NULL |
|           6 | sudhakar |  50000 | 2018-11-23 | 2019-11-23 |     12 | 5000 | NULL |
+-------------+----------+--------+------------+------------+--------+------+------+

8. Update the PAN number of ‘sudhakar’ with ‘BGPPM0123F’

9. Print the loan amount of ‘raziya’.

10. Print the loan amount of ‘sudhakar’.

11. Print the loan_number, amount and emi of ‘rathnam’.

12. ‘Sudhakar’ has paid the total amount of loan_number ‘1’, please delete the entry from the table.

13. ‘Sowmya’ also paid the amount, please remove the record from the table.

14. Print the ‘loan_number’ and ‘amount’ of ‘sudhakar’ now.

15. Print the loan ‘end_date’ of ‘binish’.

16. Print the name of the person whose ‘loan_number’ is 3.

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest