Notes

SELECT SUM(AMOUNT), CASE WHEN DATE LIKE '%-01-%' THEN 'JANUARY' WHEN DATE LIKE '%-02-%' THEN 'FEBRUARY' WHEN DATE LIKE '%-03-%' THEN 'MARCH' WHEN DATE LIKE '%-04-%' THEN 'APRIL' WHEN DATE LIKE '%-05-%' THEN 'MAY' WHEN DATE LIKE '%-06-%' THEN 'JUNE' WHEN DATE LIKE '%-07-%' THEN 'JULY' WHEN DATE LIKE '%-08-%' THEN 'AUGUST' WHEN DATE LIKE '%-09-%' THEN 'SEPTEMBER' WHEN DATE LIKE '%-10-%' THEN 'OCTOBER' WHEN DATE LIKE '%-11-%' THEN 'NOVEMBER' WHEN DATE LIKE '%-12-%' THEN 'DECEMBER' END AS MONTH FROM PAYMENT GROUP BY MONTH ORDER BY MONTH ASC;
+-------------+---------+
| SUM(AMOUNT) | MONTH |
+-------------+---------+
| 8000 | APRIL |
| 2080428 | JANUARY |
| 10000 | MARCH |
| 1053714 | OCTOBER |
+-------------+---------+
4 rows in set, 12 warnings (0.01 sec)


select NAME , IF( C_ID IN (SELECT CUST_ID FROM DEPOSITOR) ,'YES','NO') AS ISDEPOSITOR FROM CUSTOMER WHERE GENDER='f';
+----------+-------------+
| NAME | ISDEPOSITOR |
+----------+-------------+
| haritha | YES |
| himani | YES |
| saritha | YES |
| sachitra | YES |
| yogitha | YES |
| radhika | NO |
+----------+-------------+
6 rows in set (0.00 sec)


CREATE VIEW BRRANK2 AS SELECT BR_NAME , SUM(BALANCE) , CASE WHEN SUM(BALANCE) >=10000 THEN '1' WHEN SUM(BALANCE) >=8000 AND SUM(BALANCE) < 10000 THEN '2' ELSE '3'END AS RANK FROM ACCOUNT GROUP BY BR_NAME;
Query OK, 0 rows affected (0.04 sec)

mysql> select * FROM BRRANK2;
+---------+--------------+------+
| BR_NAME | SUM(BALANCE) | RANK |
+---------+--------------+------+
| zone1 | 13000 | 1 |
| zone2 | 10000 | 1 |
| zone3 | 6000 | 3 |
| zone4 | 10000 | 1 |
| zone6 | 6000 | 3 |
| zone7 | 9000 | 2 |
| zone8 | 8000 | 2 |
| zone9 | 7000 | 3 |
+---------+--------------+------+
8 rows in set (0.00 sec)

select s1.P_NO,s1.DATE,s2.DATE,CASE WHEN DATEDIFF(s2.DATE,s1.DATE) <=365 THEN 'FIRST YEAR' WHEN DATEDIFF(s2.DATE,s1.DATE) <=730 AND DATEDIFF(s2.DATE,s1.DATE) >365 THEN 'SECOND YEAR' WHEN DATEDIFF(s2.DATE,s1.DATE) <=1095 AND DATEDIFF(s2.DATE,s1.DATE) > 730 THEN 'THIRD YEAR' ELSE 'DEFAULTER' END AS STATUS from PAYMENT as s1,PAYMENT as s2 where s1.P_NO=s2.P_NO and s2.DATE>s1.DATE;
+------+------------+------------+-------------+
| P_NO | DATE | DATE | STATUS |
+------+------------+------------+-------------+
| p1 | 2011-01-09 | 2012-10-25 | SECOND YEAR |
| p1 | 2011-10-08 | 2012-10-25 | SECOND YEAR |
| p1 | 2011-10-11 | 2012-10-25 | SECOND YEAR |
| p1 | 2011-03-11 | 2012-10-25 | SECOND YEAR |
| p1 | 2012-04-11 | 2012-10-25 | FIRST YEAR |
| p1 | 2011-01-09 | 2011-10-08 | FIRST YEAR |
| p1 | 2011-03-11 | 2011-10-08 | FIRST YEAR |
| p2 | 2000-01-09 | 2011-10-09 | DEFAULTER |
| p1 | 2011-01-09 | 2011-10-11 | FIRST YEAR |
| p1 | 2011-10-08 | 2011-10-11 | FIRST YEAR |
| p1 | 2011-03-11 | 2011-10-11 | FIRST YEAR |
| p1 | 2011-01-09 | 2011-03-11 | FIRST YEAR |
| p2 | 2000-01-09 | 2012-03-11 | DEFAULTER |
| p2 | 2011-10-09 | 2012-03-11 | FIRST YEAR |
| p1 | 2011-01-09 | 2012-04-11 | SECOND YEAR |
| p1 | 2011-10-08 | 2012-04-11 | FIRST YEAR |
| p1 | 2011-10-11 | 2012-04-11 | FIRST YEAR |
| p1 | 2011-03-11 | 2012-04-11 | SECOND YEAR |
+------+------------+------------+-------------+
18 rows in set (0.00 sec)


UPDATE CUSTOMER_COPY SET STREET='NH17B' WHERE NAME IN (SELECT DISTINCT C.NAME FROM CUSTOMER C JOIN (SELECT SUM(P.AMOUNT) AS SUM,B.CUST_ID AS
-> C_ID FROM BORROWER B JOIN PAYMENT P ON P.L_NO = B.LOAN_NO GROUP BY B.CUST_ID
-> HAVING SUM >= 10000) R ON R.C_ID = C.C_ID);
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> SELECT * from CUSTOMER_COPY;
+------+----------+------------+--------------+--------+
| C_ID | NAME | STREET | CITY | GENDER |
+------+----------+------------+--------------+--------+
| c1 | haritha | NH17B | machlipatnam | f |
| c2 | himani | NH17B | machlipatnam | f |
| c3 | shankar | kadamba | eluru | m |
| c4 | saritha | kadamba | eluru | f |
| c5 | sachitra | saraswathi | nandigama | f |
| c6 | yogitha | kaveri | hyderabad | f |
| c7 | radhika | kaveri | hyderabad | f |
| c8 | ramu | azad | Vijayawada | m |
+------+----------+------------+--------------+--------+
8 rows in set (0.00 sec)