Mysql coding for IP students of std.XII

PRACTICAL NO 21

Consider a database LOANS with the following table:

AccNo	Cust_Name	Loan_Amount	Instalments	Int_Rate	Start_Date	Interest
1	R.K. Gupta	300000	36	12.00	19-07-2009
2	S.P. Sharma	500000	48	10.00	22-03-2008
3	K.P. Jain	300000	36	NULL	08-03-2007
4	M.P. Yadav	800000	60	10.00	06-12-2008
5	S.P. Sinha	200000	36	12.50	03-01-2010
6	P. Sharma	700000	60	12.50	05-06-2008
7	K.S. Dhall	500000	48	NULL	05-03-2008

Write SQL commands for the tasks 1 to 35 and write the output for the SQL commands 36 to 40:

Create Database and use it

1.    Create the database LOANS.

mysql> Create database LOANS;

Query OK, 1 row affected (0.05 sec)

2.    Use the database LOANS.

mysql> Use LOANS;

Database changed

Create Table / Insert Into

3.    Create the table Loan_Accounts and insert tuples in it.

mysql> Create table Loan_Accounts

    -> (

    -> AccNo INTEGER(8),

    -> Cust_Name VARCHAR(20),

    -> Loan_Amount INTEGER(20),

    -> Instalments INTEGER(20),

    ->  Int_Rate DECIMAL (4,2),

    -> Start_Date DATE,

    -> Interest INTEGER);

Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO Loan_Accounts VALUES(1,'R.K. Gupta', 300000, 36, 12.00 , '2009-07-19',NULL);

Query OK, 1 row affected (0.38 sec)

mysql> INSERT INTO Loan_Accounts VALUES(2,'S.P.Sharma', 500000, 48,10.00 ,'2008-03-22',NULL);

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Loan_Accounts VALUES(3,'K.P.Jain',300000,36, NULL ,'2007-03-08',NULL);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Loan_Accounts VALUES(4,' M.P. Yadav',800000,60,10.00 ,'2008-12-06',NULL);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Loan_Accounts VALUES(5,' S.P. Sinha',200000, 36,12.50 ,'2010-01-03',NULL);

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Loan_Accounts VALUES(6,' P. Sharma',700000,60,12.50 ,'2008-06-05',NULL);

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Loan_Accounts VALUES(7,' K.S. Dhall', 500000, 48, NULL,'2008-03-05',NULL);

Query OK, 1 row affected (0.03 sec)

     Simple Select

4.    Display the details of all the loans.

 mysql> SELECT * FROM Loan_Accounts ;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta    |      300000   |          36 |    12.00  | 2009-07-19     | NULL   |

|     2 |  S.P. Sharma |      500000  |          48 |    10.00   | 2008-03-22     | NULL  |

|     3 |  K.P. Jain       |      300000  |          36 |     NULL | 2007-03-08     | NULL  |

|     4 |  M.P. Yadav   |      800000  |          60 |    10.00   | 2008-12-06     | NULL  |

|     5 |  S.P. Sinha    |      200000  |          36 |    12.50    | 2010-01-03    | NULL    |

|     6 |  P. Sharma    |      700000  |          60 |    12.50    | 2008-06-05    | NULL    |

|     7 |  K.S. Dhall     |      500000  |          48 |     NULL  | 2008-03-05    | NULL    |

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

7 rows in set (0.00 sec)

5.    Display the AccNo, Cust_Name, and Loan_Amount of all the loans.

mysql> SELECT AccNo, Cust_Name, Loan_Amount  FROM Loan_Accounts ;

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

| AccNo | Cust_Name    | Loan_Amount |

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

|     1 | R.K. Gupta    |      300000 |

|     2 |  S.P. Sharma |      500000 |

|     3 |  K.P. Jain       |      300000 |

|     4 |  M.P. Yadav   |      800000 |

|     5 |  S.P. Sinha    |      200000 |

|     6 |  P. Sharma    |      700000 |

|     7 |  K.S. Dhall     |      500000 |

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

7 rows in set (0.00 sec)

Conditional Select using Where Clause

6.    Display the details of all the loans with less than 40 instalments.

mysql> SELECT * FROM Loan_Accounts WHERE Instalments < 40;

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

| AccNo | Cust_Name   | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta   |      300000  |          36 |    12.00  | 2009-07-19 | NULL |

|     3 |  K.P. Jain     |      300000  |          36 |     NULL | 2007-03-08 | NULL |

|     5 |  S.P. Sinha   |      200000  |          36|    12.50   | 2010-01-03 | NULL |

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

3 rows in set (0.00 sec)

7.    Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.

mysql> SELECT AccNo, Loan_Amount  FROM Loan_Accounts WHERE Start_Date < "2009-04-01";

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

| AccNo | Loan_Amount |

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

|     2 |      500000 |

|     3 |      300000 |

|     4 |      800000 |

|     6 |      700000 |

|     7 |      500000 |

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

5 rows in set (0.02 sec)

8. Display the Int_Rate of all the loans started after 01-04-2009.

mysql> SELECT  Int_Rate  FROM Loan_Accounts WHERE Start_Date > "2009-04-01";

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

| Int_Rate |

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

|    12.00 |

|    12.50 |

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

2 rows in set (0.00 sec)-04-01";set (0.00 sec) (0.00 sec)

Using NULL

8.    Display the details of all the loans whose rate of interest is NULL.

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate  IS NULL;

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

| AccNo | Cust_Name   | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     3 |  K.P. Jain  |      300000 |          36 |     NULL | 2007-03-08 |NULL |

|     7 |  K.S. Dhall |      500000 |          48 |     NULL | 2008-03-05 |NULL |

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

2 rows in set (0.03 sec)

10. Display the details of all the loans whose rate of interest is not NULL.

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate  IS NOT NULL;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta   |      300000 |          36 |    12.00 | 2009-07-19 | NULL |

|     2 |  S.P. Sharma |      500000 |          48 |    10.00 | 2008-03-22 | NULL |

|     4 |  M.P. Yadav  |      800000 |          60 |    10.00 | 2008-12-06 | NULL |

|     5 |  S.P. Sinha  |      200000 |          36 |    12.50 | 2010-01-03 | NULL |

|     6 |  P. Sharma   |      700000 |          60 |    12.50 | 2008-06-05 | NULL |

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

5 rows in set (0.00 sec)

Using DISTINCT Clause

11. Display the amounts of various loans from the table Loan_Accounts. A loan amount should appear only once.

mysql> SELECT DISTINCT(Loan_Amount) FROM Loan_Accounts ;

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

| Loan_Amount |

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

|      300000 |

|      500000 |

|      800000 |

|      200000 |

|      700000 |

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

5 rows in set (0.38 sec)

12. Display the number of instalments of various loans from the table Loan_Accounts.

 An instalment should appear only once..

mysql> SELECT DISTINCT(Instalments) FROM Loan_Accounts ;

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

| Instalments |

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

|          36 |

|          48 |

|          60 |

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

3 rows in set (0.00 sec)

Using Logical Operators (NOT, AND, OR)

13. Display the details of all the loans started after 31-12-2008 for which the number of

instalments are more than 36.

mysql> SELECT * FROM Loan_Accounts WHERE Start_Date> "2008-12-31" AND Instalments > 36;

Empty set (0.00 sec)

14. Display the Cust_Name and Loan_Amount for all the loans which do not have number

of instalments 36.

mysql> SELECT Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Instalments NOT IN(36);

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

| Cust_Name    | Loan_Amount |

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

|  S.P. Sharma |      500000 |

|  M.P. Yadav  |      800000 |

|  P. Sharma   |      700000 |

|  K.S. Dhall  |      500000 |

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

4 rows in set (0.00 sec)

15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount

is less than 500000 or int_rate is more than 12.

mysql> SELECT Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Loan_Amount < 500000 OR Int_Rate > 12;

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

| Cust_Name   | Loan_Amount |

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

| R.K. Gupta  |      300000 |

|  K.P. Jain  |      300000 |

|  S.P. Sinha |      200000 |

|  P. Sharma  |      700000 |

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

4 rows in set (0.00 sec)

16. Display the details of all the loans which started in the year 2009.

mysql> SELECT * FROM Loan_Accounts WHERE YEAR(Start_Date)="2009";

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

| AccNo | Cust_Name  | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta |      300000 |          36 |    12.00 | 2009-07-19 |     NULL |

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

1 row in set (0.00 sec)

17. Display the details of all the loans whose Loan_Amount is in the range 400000 to

500000.

mysql>  SELECT * FROM Loan_Accounts WHERE Loan_Amount>=400000 AND Loan_Amount<=500000;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     2 |  S.P. Sharma |      500000 |          48 |    10.00  | 2008-03-22 | NULL |

|     7 |  K.S. Dhall     |      500000 |          48 |     NULL | 2008-03-05 | NULL |

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

2 rows in set (0.00 sec)

18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate>=11 AND Int_Rate<= 12;

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

| AccNo | Cust_Name  | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta |      300000 |          36 |    12.00 | 2009-07-19 |     NULL |

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

1 row in set (0.00 sec)

Using IN Operator

19. Display the Cust_Name and Loan_Amount for all the loans for which the number of

instalments are 24, 36, or 48. (Using IN operator)

mysql> SELECT Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Instalments IN(24,36,48);

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

| Cust_Name    | Loan_Amount |

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

| R.K. Gupta   |      300000 |

|  S.P. Sharma |      500000 |

|  K.P. Jain   |      300000 |

|  S.P. Sinha  |      200000 |

|  K.S. Dhall  |      500000 |

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

5 rows in set (0.36 sec)

Using BETWEEN Operator

20. Display the details of all the loans whose Loan_Amount is in the range 400000 to

500000. (Using BETWEEN operator)

mysql> SELECT * FROM Loan_Accounts WHERE Loan_Amount BETWEEN 400000 AND 500000;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     2 |  S.P. Sharma |      500000 |          48 |    10.00 | 2008-03-22 | NULL |

|     7 |  K.S. Dhall     |      500000 |          48 |     NULL | 2008-03-05 | NULL |

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

2 rows in set (0.00 sec)

21. Display the details of all the loans whose rate of interest is in the range 11% to 12%.

(Using BETWEEN operator)

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate BETWEEN 11 AND 12;

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

| AccNo | Cust_Name  | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta |      300000 |          36 |    12.00 | 2009-07-19 |     NULL |

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

1 row in set (0.00 sec)ow in set (0.00 sec)SQL - REVISION TOUR

Using LIKE Operator

22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the

Cust_Name ends with 'Sharma'.

mysql> SELECT AccNo,Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Cust_Name like "%Sharma";

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

| AccNo | Cust_Name    | Loan_Amount |

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

|     2 |  S.P. Sharma |      500000 |

|     6 |  P. Sharma   |      700000 |

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

2 rows in set (0.03 sec)

23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the

Cust_Name ends with 'a'.

mysql> SELECT AccNo,Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Cust_Name like "%a";

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

| AccNo | Cust_Name    | Loan_Amount |

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

|     1 | R.K. Gupta   |      300000 |

|     2 |  S.P. Sharma |      500000 |

|     5 |  S.P. Sinha  |      200000 |

|     6 |  P. Sharma   |      700000 |

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

4 rows in set (0.00 sec)

24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the

Cust_Name contains 'a'

mysql> SELECT AccNo,Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Cust_Name like "%a%";

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

| AccNo | Cust_Name    | Loan_Amount |

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

|     1 | R.K. Gupta   |      300000 |

|     2 |  S.P. Sharma |      500000 |

|     3 |  K.P. Jain   |      300000 |

|     4 |  M.P. Yadav  |      800000 |

|     5 |  S.P. Sinha  |      200000 |

|     6 |  P. Sharma   |      700000 |

|     7 |  K.S. Dhall  |      500000 |

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

7 rows in set (0.00 sec)

25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the

Cust_Name does not contain 'P'.

mysql> SELECT AccNo,Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Cust_Name NOT like "%p%";

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

| AccNo | Cust_Name   | Loan_Amount |

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

|     7 |  K.S. Dhall |      500000 |

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

1 row in set (0.00 sec)

26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the

Cust_Name contains 'a' as the second last character.

mysql> SELECT AccNo,Cust_Name, Loan_Amount  FROM Loan_Accounts WHERE Cust_Name like "%a_";

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

| AccNo | Cust_Name   | Loan_Amount |

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

|     4 |  M.P. Yadav |      800000 |

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

1 row in set (0.02 sec)

Using ORDER BY clause

27. Display the details of all the loans in the ascending order of their Loan_Amount.

mysql> SELECT * FROM Loan_Accounts ORDER BY Loan_Amount asc;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     5 |  S.P. Sinha  |      200000 |          36 |    12.50 | 2010-01-03 | NULL |

|     1 | R.K. Gupta   |      300000 |          36 |    12.00 | 2009-07-19 | NULL |

|     3 |  K.P. Jain   |      300000 |          36 |     NULL | 2007-03-08 | NULL |

|     2 |  S.P. Sharma |      500000 |          48 |    10.00 | 2008-03-22 | NULL |

|     7 |  K.S. Dhall  |      500000 |          48 |     NULL | 2008-03-05 | NULL |

|     6 |  P. Sharma   |      700000 |          60 |    12.50 | 2008-06-05 | NULL |

|     4 |  M.P. Yadav  |      800000 |          60 |    10.00 | 2008-12-06 | NULL |

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

7 rows in set (0.00 sec)

28. Display the details of all the loans in the descending order of their Start_Date.

mysql> SELECT * FROM Loan_Accounts ORDER BY Start_Date desc;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     5 |  S.P. Sinha  |      200000 |          36 |    12.50 | 2010-01-03 | NULL |

|     1 | R.K. Gupta   |      300000 |          36 |    12.00 | 2009-07-19 | NULL |

|     4 |  M.P. Yadav  |      800000 |          60 |    10.00 | 2008-12-06 | NULL |

|     6 |  P. Sharma   |      700000 |          60 |    12.50 | 2008-06-05 | NULL |

|     2 |  S.P. Sharma |      500000 |          48 |    10.00 | 2008-03-22 | NULL |

|     7 |  K.S. Dhall  |      500000 |          48 |     NULL | 2008-03-05 | NULL |

|     3 |  K.P. Jain   |      300000 |          36 |     NULL | 2007-03-08 | NULL |

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

7 rows in set (0.00 sec)

29. Display the details of all the loans in the ascending order of their Loan_Amount and

within Loan_Amount in the descending order of their Start_Date.

mysql> SELECT * FROM Loan_Accounts ORDER BY Loan_Amount asc ,Start_Date desc;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     5 |  S.P. Sinha  |      200000 |          36 |    12.50 | 2010-01-03 | NULL |

|     1 | R.K. Gupta   |      300000 |          36 |    12.00 | 2009-07-19 | NULL |

|     3 |  K.P. Jain   |      300000 |          36 |     NULL | 2007-03-08 | NULL |

|     2 |  S.P. Sharma |      500000 |          48 |    10.00 | 2008-03-22 | NULL |

|     7 |  K.S. Dhall  |      500000 |          48 |     NULL | 2008-03-05 | NULL |

|     6 |  P. Sharma   |      700000 |          60 |    12.50 | 2008-06-05 | NULL |

|     4 |  M.P. Yadav  |      800000 |          60 |    10.00 | 2008-12-06 | NULL |

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

7 rows in set (0.00 sec)

Using UPDATE, DELETE, ALTER TABLE

30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.

mysql> UPDATE Loan_Accounts

    -> SET Int_Rate=11.50

    -> WHERE Int_Rate IS NULL;

Query OK, 2 rows affected (0.38 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT*FROM Loan_Accounts ;

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

| AccNo | Cust_Name    | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest |

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

|     1 | R.K. Gupta   |      300000 |          36 |    12.00 | 2009-07-19 | NULL |

|     2 |  S.P. Sharma |      500000 |          48 |    10.00 | 2008-03-22 | NULL |

|     3 |  K.P. Jain   |      300000 |          36 |    11.50 | 2007-03-08 | NULL |

|     4 |  M.P. Yadav  |      800000 |          60 |    10.00 | 2008-12-06 | NULL |

|     5 |  S.P. Sinha  |      200000 |          36 |    12.50 | 2010-01-03 | NULL |

|     6 |  P. Sharma   |      700000 |          60 |    12.50 | 2008-06-05 | NULL |

|     7 |  K.S. Dhall  |      500000 |          48 |    11.50 | 2008-03-05 | NULL |

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

7 rows in set (0.00 sec)

31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more

than 400000.

mysql> UPDATE Loan_Accounts

    -> SET Int_Rate=Int_Rate+0.5

    -> WHERE Loan_Amount >400000;

Query OK, 4 rows affected (0.03 sec)

Rows matched: 4  Changed: 4  Warnings: 0

32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.

mysql> UPDATE Loan_Accounts

    -> SET Int_Rate=(Loan_Amount*Int_Rate*Instalments)/12*100;

33. Delete the records of all the loans whose start date is before 2007.

mysql> DELETE FROM Loan_Accounts WHERE YEAR(Start_Date)<"2007" ;

Query OK, 0 rows affected (0.00 sec)

34. Delete the records of all the loans of 'K.P. Jain'

mysql> DELETE FROM Loan_Accounts WHERE Cust_Name like " K.P. Jain";

Query OK, 1 row affected (0.05 sec)

35. Add another column Category of type CHAR(1) in the Loan table.

mysql> ALTER TABLE Loan_Accounts ADD CATEGORY CHAR(1) ;

Query OK, 6 rows affected (0.19 sec)

Records: 6  Duplicates: 0  Warnings: 0

SQL –

TOUR

Find the Output of the following queries

36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name)

FROM Loan_Accounts WHERE Int_Rate < 11.00;

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

| cust_name    | LENGTH(Cust_Name) | LCASE(Cust_Name) | UCASE(Cust_Name) |

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

|  S.P. Sharma |                12 |  s.p. sharma     |  S.P. SHARMA     |

|  M.P. Yadav  |                11 |  m.p. yadav      |  M.P. YADAV      |

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

2 rows in set (0.02 sec)

37. SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM

Loan_Accounts WHERE Int_Rate > 10.00;

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

| LEFT(Cust_Name, 3) | Right(Cust_Name, 3) | SUBSTR(Cust_Name, 1, 3) |

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

| R.K                | pta                 | R.K                     |

|  S.                | nha                 |  S.                     |

|  P.                | rma                 |  P.                     |

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

3 rows in set (0.00 sec)

38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;

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

| RIGHT(Cust_Name, 3) | SUBSTR(Cust_Name, 5) |

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

| pta                 |  Gupta               |

| rma                 | . Sharma             |

| ain                 | . Jain               |

| dav                 | . Yadav              |

| nha                 | . Sinha              |

| rma                 | Sharma               |

| all                 | . Dhall              |

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

7 rows in set (0.00 sec)

39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;

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

| DAYNAME(Start_Date) |

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

| Sunday              |

| Saturday            |

| Thursday            |

| Saturday            |

| Sunday              |

| Thursday            |

| Wednesday           |

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

7 rows in set (0.00 sec)

40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate > 10;

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

| ROUND(Int_Rate*110/100, 2) |

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

|                      13.20 |

|                      13.75 |

|                      13.75 |

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

3 rows in set (0.00 sec)

Write the output produced by the following SQL commands:

41. SELECT POW(4,3), POW(3,4);

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

| POW(4,3) | POW(3,4) |

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

|       64 |       81 |

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

1 row in set (0.05 sec)

42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);

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

| ROUND(543.5694,2) | ROUND(543.5694) | ROUND(543.5694,-1) |

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

|            543.57 |             544 |                540 |

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

1 row in set (0.00 sec)

43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);

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

| TRUNCATE(543.5694,2) | TRUNCATE(543.5694,-1) |

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

|               543.56 |                   540 |

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

1 row in set (0.00 sec)

44. SELECT LENGTH("Prof. M. L. Sharma");

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

| LENGTH("Prof. M. L. Sharma") |

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

|                           18 |

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

1 row in set (0.00 sec)

45. SELECT CONCAT("SHEIKH", " HAROON") "FULL NAME";

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

| FULL NAME     |

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

| SHEIKH HAROON |

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

1 row in set (0.02 sec)

46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());

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

| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) |

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

|            2015 |               10 |              3 |

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

1 row in set (0.00 sec)

47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()),

DAYNAME(CURDATE());

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

| DAYOFYEAR(CURDATE()) | DAYOFMONTH(CURDATE()) | DAYNAME(CURDATE()) |

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

|                  276 |                     3 | Saturday           |

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

1 row in set (0.00 sec)

48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);

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

| LEFT("Unicode",3) | RIGHT("Unicode",4) |

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

| Uni               | code               |

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

1 row in set (0.00 sec)

49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");

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

| INSTR("UNICODE","CO") | INSTR("UNICODE","CD") |

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

|                     4 |                     0 |

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

1 row in set (0.00 sec)

50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

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

| MID("Informatics",3,4) | SUBSTR("Practices",3) |

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

| form                   | actices               |

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

1 row in set (0.00 sec)