Ministry of Road Transport and Highways plans to construct a highway in south India. To start the project, Highway department plans to acquire the lands in south India. The middle level management collects all the data for the acquisition lands and presented in the below data structure with the help of data specialist. The management and financial team requested set of details to make few decisions and payments.

As a technical person you are requested to understand the data structure and answer to the management and financial queries.


	

		

			

			
TABLE NAME
			
			

			
TABLE DESCRIPTION
			
		
		

			

			
STATES
			
			

			
This table contains details about States
			
		
		

			

			
DISTRICT
			
			

			
This table contains details about Districts
			
		
		

			

			
DEPARTMENT
			
			

			
This table contains details about Department
			
		
		

			

			
LAND
			
			

			
This table contains details about Lands
			
		
		

			

			
LAND_DETAIL
			
			

			
This table contains details about Land details
			
		
	
 

 
Write a query to display the list of districts and their states which has less land acquisition?
 

 SELECT D.DISTRICT_CODE,D.DISTRICT_NAME,S.STATE_NAME, SUM(LD.AREA_IN_SQMT) AS LAND_ACQUISITION FROM DISTRICT D INNER JOIN LAND_DETAIL LD ON D.DISTRICT_CODE = LD.DISTRICT_CODE INNER JOIN STATES S ON S.STATE_CODE = LD.STATE_CODE GROUP BY D.DISTRICT_CODE,D.DISTRICT_NAME,S.STATE_NAME ORDER BY LAND_ACQUISITION; 

Validate the less land acquisition for Districts and States

Validate the less land acquisition for Districts and States