cs621_lab_exam.sql

select * from cs621labexam1;

--1
select * from cs621labexam1 where char_length(observer) > 9;

--2 
select * from cs621labexam1
where 1=1
and date_part('month', ts) = 01
and date_part('year', ts) = 2019
and rainfall >= 40
and rainfall <= 50;

--3 
select * from cs621labexam1
where 1=1
and tindex ~ '^4444-.*-44$';


--4
select * from cs621labexam1
where 1=1
and tindex ~ '^(2|3|5|7){4}-.*-(2|3|5|7){2}$';



--5
select * from cs621labexam1
where 1=1
and tindex ~ '^.*(A|E|I|O|U){2,}.*$'
and rainfall < 30;


--6
select *
,st_distance(
	st_transform(thegeom,27700),
	st_transform(st_geomfromtext('POINT(-0.1936 51.4579)',4326),27700)
) as distance
from cs621labexam1
where 1=1
and st_distance(
	st_transform(thegeom,27700),
	st_transform(st_geomfromtext('POINT(-0.1936 51.4579)',4326),27700)
) <= 6000;


--7
select * from cs621labexam1
where 1=1
and st_distance(
	st_transform(thegeom,27700),
	st_transform(st_geomfromtext('POINT(-0.121661 51.500835)',4326),27700)
) < 10000
and date_part('month', ts) >= 03
and date_part('year', ts) >= 2019;


--8
select * from cs621labexam1
where 1=1
and st_distance(
	st_transform(thegeom,27700),
	st_transform(st_geomfromtext('POINT(-0.1926 51.4556)',4326),27700)
) < 8500
and (
	date_part('hour', ts) >= 20
	or
	rainfall >= 20
);


--9
select * from cs621labexam1
where 1=1
and tindex ~ '^.*(2|3|5|7)$'
and observer ~ '^.*(2|3|5|7)$';


--10
select * from cs621labexam1
where 1=1
and rainfall > 40
and date_part('month', ts) = 04
and date_part('year', ts) = 2019
order by ts asc;



--11
select * from cs621labexam1
where 1=1
and date_part('month', ts) = 05
and date_part('year', ts) = 2019 
and date_part('hour', ts) + date_part('minute', ts)  + date_part('second', ts) > 10 * rainfall;



--12
select * from cs621labexam1
where 1=1
and st_distance(
	st_transform(thegeom,27700),
	st_transform(st_geomfromtext('POINT(-0.52381 51.59723)',4326),27700)
) >= 35000 
and date_part('month', ts) = 04
and date_part('year', ts) = 2019
and observer ~ '^.*\d{2,}$';