Solution of Real Estate Use Case
SOLUTION OF REAL ESTATE USE CASE
create table real1(street string,city string,zip int,state
string,beds int,baths int,sq_ft int,type string,sale_date string,price
int,lalitude string,longitude string)
row format
delimited
fields
terminated by',';
load
data local inpath '/home/cloudera/hadoopData/real state.csv' into table real1;
1. Problem Statement:
City wise list all
the Villa which is not less than ten thousand.
select
city from real where ((price >10000) and (type=='villa')) group by city;
2. Problem Statement:
In GALT city which
residential type has more than 800sq__ft. Display their respective details
street,sq__ft,sale_date,city.
select
street,sq_ft,sale_date,city from real1 where ((city=='GALT') AND
(sq_ft>800));
3. Problem Statement:
Which is the
cheapest Villa in CA. name the city,street and price for the Villa.
select
city,street,price from real1 where type=='villa' order by price limit 1;
4. Problem Statement:
List top 5 residency
details which lie in the budget of 60000-120000, an
area more than 1450, sold after 17th may, min bedroom 3 and, min bathroom 2.
select
street,city,zip,state,beds,baths,sq_ft,type,price from real1
where((price>=60000) AND (price<=120000) AND (sq_ft>1450) AND
(beds>3) AND(baths>2)) order by price limit 5;
Here only one house
address is selected under above
condition.
5.
Problem Statement:
separate list of
residential apartments with more than 2 beds. Also include columns in following
order City,Baths,Sq_feet,Price,flat_type,Beds respectively.
create table
intrestedBhk(city STRING,baths INT,sq_ft INT,price INT)
partitioned by(type STRING,beds INT)
row format delimited
fields terminated by ','
stored as textfile;
INSERT INTO TABLE intrestedBhk partition(type,beds) select
city,baths,sq_ft,price,type,beds from real1 where (beds>2 and
type='Residential');
To view separated list of residential apartment click
the given link
https://drive.google.com/open?id=1SiUdrWoi7h5pzXBQ_Id3S3n-kn0zkNYv
Comments
Post a Comment