db.txt

open mysql 
	
	database create 
	
	create database expenseapp; 
	
	use expenseapp; 
	
	create table users (
		userId integer primary key auto_increment,
		firstName varchar(30),
		lastName varchar(30),
		email varchar(50),
		password varchar(80),
		role integer
	);
	
	1 -> admin 
	2 -> user/buyer 
	
	desc users;
	
	
insert into users (firstName,lastName,email,password,role) 
	values ('ram','sita','ram@sita.com','ravan');
	
	// Add Column in Table
alter table users add otp varchar(10); 


update user set otp = ? where email = ? 


-----------------------------------------

create table category(
	categoryId integer primary key auto_increment,
	categoryName varchar(30),
	deleted boolean  
);

create table subcategory(
	subCategoryId integer primary key auto_increment,
	subCategoryName varchar(30),
	categoryId integer references category(categoryId)
);

-------------------------------------------------------------

Expense Table

create table expense(
	expenseId integer primary key auto_increment,
	title varchar(30),
	categoryId integer references category(categoryId),
	subCategoryId integer references subcategory(subcategoryId),
	vendorId integer references vendor(vendorId),
	accountTypeId integer references accountType(accountTypeId),
	statusId integer references status(statusId),
	amount integer,
	date varchar(30),
	description varchar(150),
	userId integer integer references users(userId)
	);
	 
-----------------------------------------------------------------------

To Rename Column Name

alter table expense rename column amount to ammount;


--------------------------------------------------------------------------
delete Row in dataTable

delete from subcategory where subCategoryId = 17;

-------------------------------------------------------------------------

update subcategory set subCategoryName = '' where subCategoryId =  ;

------------------------------------------------------------------------------

Multiple row Deleted

delete from expense where expenseId in (5,6);



---------------------------------------------------------------------------------

Table Income

create table income(
    -> incomeId integer primary key auto_increment,
    -> title varchar(30),
    -> date varchar(30),
    -> userId integer references users(userId),
    -> accountTypeId integer references accountType(accountTypeId),
    -> description varchar(150),
    -> statusId integer references status(statusId),
    -> ammount integer
    -> );



-----------------------------------------------------------------------------------------

View Expense Content

Expense Query

  select e.title, e.ammount, e.date, e.description, c.categoryName, sc.subcategoryName, v.vendorName, a.accountType, s.statusShow from expense e, category c, subcategory sc, vendor v, accountType a, status s where e.categoryId = c.categoryId and e.subCategoryId = sc.subCategoryId and e.vendorId = v.vendorId and e.accountTypeId = a.accountTypeId and e.statusId = s.statusId and userId = ?; 

 O/P for userId -> 11
 +--------------+---------+------------+------------------------------------------------------+--------------+-----------------+------------+-------------+------------+
 | title        | ammount | date       | description                                          | categoryName | subcategoryName | vendorName | accountType | statusShow |
 +--------------+---------+------------+------------------------------------------------------+--------------+-----------------+------------+-------------+------------+
 | With Friends |     700 | 2023-03-12 | Bought Three Cold Coffe in StarBucks. I paid 700 rs. | Food         | Break Fast      | StarBucks  | credit card | paid       |
 | mobile       |   30000 | 2023-03-13 | Mobile Phhoen                                        | Electronics  | Bike            | Croma      | cash        | paid       |
 | Furniture    |   10000 | 2023-03-14 | purchased 2 chairs                                   | house        | Chair           | Croma      | cash        | paid       |
 | Lunch        |    5000 | 2023-04-01 | With friends                                         | Food         | Break Fast      | McDonald's | cash        | paid       |
 +--------------+---------+------------+------------------------------------------------------+--------------+-----------------+------------+-------------+------------+
















// Some Useful Queries

    insert into income (userId, date, title, accountTypeId, description, statusId, ammount) values (11, '2023-03-27', 'Stock Market', 2, 'profit in treding', 5, 50000);
    
    select sum(ammount) from income where date like '2023-%';
    
    delete from income where incomeId = 6;
    
    update users set gender = 'male' where userId in (7,8,9,10,11,12,18);
    
     update users set joindate = '01-03-2023' where userId in (7);
     
     select count(*) from users where joindate = '08-03-2023';
     
     select count(*) from users where joindate like '%-03-%';
     
     select count(*) from users where joindate like '%-03-2023';
     
      SELECT COUNT(*) FROM users WHERE joindate BETWEEN '2023-03-01' AND '2023-03-31';
  
---------------------------------------------------------------------------------------------------------------------------------------------------------
1).select MONTH(date) ,sum(ammount) from expense group by month(date); 
---> above Query will give you Month Number and Monthly Expense Ammount 

2). select MONTH(date) as month ,sum(ammount) as expenseAmmount from expense group by month(date);
--->  Abobe Query will give same output as mentioned above but it will Change the column Name "(*) As Month & (*) As expenseAmmount"  (Alias)  

3). select MONTH(date) as month ,sum(ammount) as expenseAmmount from expense group by month(date) order by MONTH(date);
---> Update from Above -> order by MONTH(date) <=> ABove updation Query givr data in Accending Month Data
O/P 
	+-------+----------------+
	| month | expenseAmmount |
	+-------+----------------+
	|     1 |          11500 |
	|     2 |          15000 |
	|     3 |          83000 |
	|     4 |           6400 |
	|     5 |           4000 |
	|     6 |           5000 |
	+-------+----------------+


4). select MONTH(date) as month ,sum(ammount) as expenseAmmount from expense group by month(date) order by MONTH(date) desc;
--->  Update from Above -> order by MONTH(date) desc <=> Give Data in Descendin Order Month [ex(12 to 1 where 12=december)]

5). select monthname(date) as month ,sum(ammount) as expenseAmmount from expense group by monthname(date) order by month(date);
---> Update from Above -> monthname(date) this will give data by Month Name (May,April) in accending order by order by MONTH(date)

6). select monthname(date) as month , sum(ammount) as expenseAmmount from expense where year(date) = 2023 group by monthname(date) order by monthname(date);
---> update from Above -> where year(date) = 2023 <=> above query will give data of All month of current year
O/P 
	+----------+----------------+
	| month    | expenseAmmount |
	+----------+----------------+
	| April    |           6400 |
	| February |          15000 |
	| January  |          11500 |
	| June     |           5000 |
	| March    |          83000 |
	| May      |           4000 |
	+----------+----------------+

7). select monthname(date) as month , sum(ammount) as expenseAmmount from expense where year(date) = 2023 group by monthname(date),month(date)  order by month(date);
---> update from Above -> group by monthname(date),month(date)  order by month(date); <=> This Query Will give data of assending order by Month Name
O/P
	+----------+----------------+
	| month    | expenseAmmount |
	+----------+----------------+
	| January  |          11500 |
	| February |          15000 |
	| March    |          83000 |
	| April    |           6400 |
	| May      |           4000 |
	| June     |           5000 |
	+----------+----------------+
------------------------------------------------------------------------------------------------------------------------------------------------------------------

select monthname(orderdate) as month , sum(totalAmount) as orderAmount from orders where year(orderDate) = 2023 group by monthname(orderdate) order by month(orderDate);


Each Users Number of Expenses
--> select userId , count(expenseId) from expense group by userId;



-----------------------------------------------------------------------------------
 FOR uopload image add URL Data for 1024 char(1024)
--> alter table users add column imageUrl varchar(1024);

To upload imgae We need to add these maven dependencies

apache commons 
	io
	file-upload

-------------------------------------------------------

This Query will give number of Transaction in Every Account ex (CreditCard = 4, cash = 7)

 SELECT a.accountType AS PaymentType, COUNT(e.accountTypeId) AS transaction  FROM accountType a LEFT JOIN expense e ON a.accountTypeId = e.accountTypeId group by a.accountTypeId;
O/P :-
	+-------------+-------------+
	| PaymentType | transaction |
	+-------------+-------------+
	| cash        |           7 |
	| credit card |           3 |
	| debit card  |           3 |
	| cheque      |           2 |
	+-------------+-------------+


--> select c.categoryName as category , count(e.categoryId) as Spending from category c LEFT JOIN expense e ON c.categoryId = e.categoryId group by c.categoryId;
 	+-----------------+----------+
	| category        | Spending |
	+-----------------+----------+
	| Lunch           |        0 |
	| Dinner          |        0 |
	| BreakFast       |        0 |
	| Coffee          |        0 |
	| Automobile      |        2 |
	| Groceary        |        1 |
	| house           |        1 |
	| Electronics     |        1 |
	| Repairing       |        0 |
	| Medical         |        0 |
	| Clothes         |        0 |
	| Food            |        5 |
	| Stationery      |        1 |
	| House Hold      |        2 |
	| Books for maths |        0 |
	| Other           |        3 |
	| D-Mart          |        0 |
	+-----------------+----------+
	
 mysql> select v.vendorName as vendor, count(e.vendorId) as Spent from vendor v LEFT JOIN expense e ON v.vendorId = e.vendorId group by v.vendorId;

	+----------------+-------+
	| vendor         | Spent |
	+----------------+-------+
	| StarBucks      |     2 |
	| McDonald's     |     2 |
	| BBQ Nation     |     1 |
	| BBQ Nation     |     0 |
	| Croma          |     4 |
	| Domino's Pizza |     1 |
	| College        |     3 |
	| Other          |     2 |
	| dmy            |     0 |
	| D-Mart         |     1 |
	+----------------+-------+	


For Specific User
mysql>  select monthname(date) as month , sum(ammount) as expenseAmmount from expense where year(date) = 2023 and userId = 11 group by monthname(date),month(date)  order by month(date)  ;
	+-------+----------------+
	| month | expenseAmmount |
	+-------+----------------+
	| March |          10700 |
	| April |           5000 |
	+-------+----------------+


for All users

mysql> select s.statusShow AS Status, COUNT(e.statusId) AS Transaction from status s LEFT JOIN Expense e ON s.statusId = e.statusId GROUP BY s.statusId;
	+-----------------+-------------+
	| Status          | Transaction |
	+-----------------+-------------+
	| paid            |           0 |
	| unpaid          |           0 |
	| unpaid          |           0 |
	| unpaid          |           0 |
	| paid            |           9 |
	| partial paid    |           1 |
	| asd             |           0 |
	| unpaid          |           3 |
	| Not Decided Yet |           1 |
	| Dummy           |           0 |
	| Dmy             |           0 |
	+-----------------+-------------+

for Specific users

mysql> select s.statusShow AS Status, COUNT(e.statusId) AS Transaction from status s LEFT JOIN Expense e ON s.statusId = e.statusId WHERE userId = 27 GROUP BY s.statusId;
	+--------------+-------------+
	| Status       | Transaction |
	+--------------+-------------+
	| unpaid       |           1 |
	| partial paid |           1 |
	| paid         |           2 |
	+--------------+-------------+

mysql> select e.userId, u.firstName ,e.title from expense e LEFT JOIN users u ON u.userId = e.userId;
	+--------+------------+-------------------+
	| userId | firstName  | title             |
	+--------+------------+-------------------+
	|     11 | VAIBHAV    | With Friends      |
	|     11 | VAIBHAV    | Furniture         |
	|      8 | Vaibhav    | Pizza             |
	|     27 | salCollege | College Fees      |
	|     11 | VAIBHAV    | Lunch             |
	|      4 | Elon       | dmy               |
	|      4 | Elon       | dmy 2             |
	|     27 | salCollege | College Fees      |
	|     27 | salCollege | Canteen BreakFast |
	|     27 | salCollege | Coolege Books     |
	|     18 | z          | Grocery           |
	|     18 | z          | Shopping          |
	|      8 | Vaibhav    | Dummy             |
	|      5 | admin      | Fees              |
	+--------+------------+-------------------+

userId = 11 
mysql> SELECT e.title, e.ammount, e.date, s.statusShow
    -> FROM expense e
    -> LEFT JOIN status s ON e.statusId = s.statusId
    -> WHERE userId = 11
    -> GROUP BY e.title, e.ammount, e.date, s.statusShow
    -> ORDER BY e.date DESC;
	+--------------+---------+------------+--------------+
	| title        | ammount | date       | statusShow   |
	+--------------+---------+------------+--------------+
	| Grocery      |    2000 | 2023-04-11 | partial paid |
	| Lunch        |    5000 | 2023-04-01 | paid         |
	| Furniture    |   10000 | 2023-03-14 | paid         |
	| With Friends |     700 | 2023-03-12 | paid         |
	+--------------+---------+------------+--------------+
	
userId = 27 

mysql> SELECT e.title, e.ammount, e.date, s.statusShow from expense e LEFT JOIN status s ON e.statusId = s.statusId WHERE userId = 27 
		GROUP BY e.title, e.ammount, e.date, s.statusShow ORDER BY e.date DESC;
	+-------------------+---------+------------+--------------+
	| title             | ammount | date       | statusShow   |
	+-------------------+---------+------------+--------------+
	| Canteen BreakFast |     200 | 2023-04-03 | paid         |
	| College Fees      |   32000 | 2023-03-30 | unpaid       |
	| College Fees      |   15000 | 2023-02-04 | partial paid |
	| Coolege Books     |    1500 | 2023-01-12 | paid         |
	+-------------------+---------+------------+--------------+
 
All Expense Detail For AdminPanel

mysql> select e.userId, e.expenseId, e.title, e.ammount, e.date, e.description, c.categoryName, sc.subcategoryName, v.vendorName, a.accountType, s.statusShow from expense e, category c, subcategory sc, vendor v, accountType a, status s where e.categoryId = c.categoryId and e.subCategoryId = sc.subCategoryId and e.vendorId = v.vendorId and e.accountTypeId = a.accountTypeId and e.statusId = s.statusId and expenseId = 12;

	+--------+-----------+-------+---------+------------+-------------+--------------+-----------------+----------------+-------------+-----------------+
	| userId | expenseId | title | ammount | date       | description | categoryName | subcategoryName | vendorName     | accountType | statusShow      |
	+--------+-----------+-------+---------+------------+-------------+--------------+-----------------+----------------+-------------+-----------------+
	|      8 |        12 | Pizza |     300 | 2023-03-29 | Pizza Party | Food         | Break Fast      | Domino's Pizza | debit card  | Not Decided Yet |
	+--------+-----------+-------+---------+------------+-------------+--------------+-----------------+----------------+-------------+-----------------+

mysql> select monthname(date) as income_month, sum(ammount) as Income from income where year(date) = 2023 and userId = 11 group by monthname(date) , month(date) order by month(date);
	+--------------+--------+
	| income_month | Income |
	+--------------+--------+
	| March        | 152000 |
	| April        |   3000 |
	+--------------+--------+

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

mysql> select c.categoryName as category , count(e.expenseId) as NoExoense from category c LEFT JOIN expense e on e.categoryId = c.categoryId GROUP BY c.categoryId;
	+-----------------+-----------+
	| category        | NoExoense |
	+-----------------+-----------+
	| Lunch           |         0 |     For Admin but USer side this query dont get O/P 
	| Dinner          |         0 |
	| BreakFast       |         0 |		in this query i 
	| Coffee          |         0 |
	| Automobile      |         1 |     Category LEFT JOIN Expense
	| Groceary        |         1 |
	| House           |         1 |
	| Electronics     |         0 |
	| Repairing       |         0 |
	| Medical         |         0 |
	| Clothes         |         0 |
	| Food            |         7 |
	| Stationery      |         1 |
	| House Hold      |         3 |
	| Books for maths |         0 |
	| Other           |         3 |
	| D-Mart          |         0 |
	| dddddddddddddd  |         0 |
	+-----------------+-----------+
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

=========================================================================================================
 Simple JOIN Query
mysql>  SELECT c.categoryName, COUNT(*) AS Transaction  FROM category c  JOIN expense e ON e.categoryId = c.categoryId GROUP BY c.categoryName ORDER BY c.categoryName;
	+--------------+------------------+
	| categoryName | Transaction	  |
	+--------------+------------------+
	| Automobile   |                1 |
	| Food         |                7 |
	| Groceary     |                1 |      
	| House        |                1 |
	| House Hold   |                3 |
	| Other        |                3 |
	| Stationery   |                1 |
	+--------------+------------------+
=========================================================================================================
LeftJoin Query Good Query for both side
mysql> SELECT c.categoryName , count(*) AS Transaction FROM expense e LEFT JOIN category c ON e.categoryId = c.categoryId GROUP BY c.categoryId;
	+--------------+------------------+
	| categoryName | Transaction	  |			Expense LEFT JOIN Category  
	+--------------+------------------+
	| Automobile   |                1 |          
	| Food         |                7 |		
	| Groceary     |                1 |
	| House        |                1 |        
	| House Hold   |                3 |
	| Other        |                3 |
	| Stationery   |                1 |
	+--------------+------------------+
=========================================================================================================
for userId = 11
mysql>  SELECT c.categoryName, COUNT(*) AS Transaction  FROM category c  JOIN expense e ON e.categoryId = c.categoryId where userId =11 GROUP BY c.categoryName ORDER BY c.categoryName;
	+--------------+------------------+
	| categoryName | Transaction	  |
	+--------------+------------------+
	| Food         |                3 |
	| House        |                1 |
	| House Hold   |                1 |
	| Other        |                1 |
	+--------------+------------------+
	
	for userId = 11
mysql> select v.vendorName as vendor , count(*) as Transaction FROM expense e LEFT JOIN vendor v ON e.vendorId = v.vendorId where userId = 11 GROUP BY v.vendorName;
	+----------------+-------------+
	| vendor         | Transaction |
	+----------------+-------------+
	| StarBucks      |           1 |
	| Croma          |           2 |
	| McDonald's     |           1 |
	| College        |           1 |
	| D-Mart         |           1 |
	| Domino's Pizza |           1 |
	+----------------+-------------+
	
	Admin Side
mysql> SELECT c.categoryName, sum(ammount) AS TransactionAmmount FROM category c  JOIN expense e ON e.categoryId = c.categoryId where date like '2023-04-%' GROUP BY c.categoryName ORDER BY c.categoryName;
+--------------+--------------------+
| categoryName | TransactionAmmount |
+--------------+--------------------+
| Automobile   |                500 |
| Electronics  |               2000 |
| Food         |               5910 |
| House Hold   |               2700 |
| Other        |               5000 |
+--------------+--------------------+