(i) To display names of stores along with Sales Amount of those stores that have ‘fashion’ anywhere

Question

Write commands in SQL for (i) to (iv) and output for (v) and (vi). 

Table : Store

StoreId	Name	Location	City	NoOfEmp	DateOpen	SalesAmt
S101	Planet Fashion	Bandra	Mumbai	7	2015-10-16	40000
S102	Vogue	Karol Bagh	Delhi	8	2015-07-14	120000
S103	Trends	Powai	Mumbai	10	2015-06-24	30000
S104	SuperFashion	Thane	Mumbai	11	2015-02-06	45000
S105	Annabelle	South Extn.	Delhi	8	2015-04-09	60000
S106	Rage	Defence Colony	Delhi	5	2015-03-01	20000

(i) To display names of stores along with Sales Amount of those stores that have ‘fashion’ anywhere in their store names.

(ii) To display Stores names, Location and Date Of Open of stores that were opened before 1st March, 2015.

(iii) To display name and location of those store which have either ‘u’ as second character in their name.

(iv) To display the City and the number of stores located in that City, only if number of stores is more than 2.

(v) Select Min(Date Open) from Store;

(vi) Select Count(Storeid), Noofemp From Store Group By Noofemp Having Max(Salesamt)<60000;

Answer 1

mysql> Select Name,SalesAmt from Store order by noOfEmp; 

mysql> Select city, sum(SalesAmt) from store group by City; 

mysql> Select count(*),City from store group by City having count(*)>2;

mysql> Select Min(DateOpen) from Store;

Min(DateOpen) 

2015-02-06 

mysql> Select Count(StoreId), NoOfEmp from Store group by 

NoOfemp having max(SalesAmt)<60000;

Count(StoreId)	NoOfEmp
1	10
1	11
1	5
1	7