sql useful queries

  using update
suppose customeramount in 10% increase used bellow query
employee salary _% increase what ever may be used bellow query


1."update Customer set CustomerAmount=CustomerAmount+(CustomerAmount/100*10) where CustomerCode=1001"


inner join using multiple tables group by query

Tables

User details

userid	username	firstname	lastname	cityid
1	ravi	ravi	r	1
2	mohan	mohan	m	2
3	gopal	gopal	g	3
4	raju	raju	r	4
5	ramu	ramu	r	5

Usermaster

userid	itemid	qty
1	1	10
2	2	20
3	3	15
4	4	12
5	2	15
2	3	15

Citymaster

id	name
1	kandukuru
2	ongole
3	pokur
4	sakhavaram
5	naldalpur

Itemmaster

itemid	itemname	cost	itemdiscrptin
1	pen	20	writing
2	pencil	10	writing
3	Skach pen	5	drawing
4	paper	50	writing

 

2.select userdetails.firstname+userdetails.lastname as fullname,citymaster.name,itemmaster.itemname,usermaster.qty*itemmaster.cost as totalcost
from userdetails join citymaster on userdetails.cityid=citymaster.id join usermaster on usermaster.userid=userdetails.userid join itemmaster
on usermaster.itemid=itemmaster.itemid

3.select firstname+lastname as fullname,name,itemname,qty*cost as totalcost
from userdetails join citymaster on userdetails.cityid=citymaster.id join usermaster on usermaster.userid=userdetails.userid join itemmaster
on usermaster.itemid=itemmaster.itemid

4.select citymaster.name,count(*) as noofpeople from citymaster join userdetails on citymaster.id=userdetails.cityid group by citymaster.name

5.  select citymaster.name,sum(usermaster.qty) as count from citymaster join userdetails on citymaster.id=userdetails.cityid join usermaster
  on userdetails.userid=usermaster.userid join itemmaster on usermaster.itemid=itemmaster.itemid group by citymaster.name having sum(usermaster.qty)  >20