aveek22

ALTER TABLE mumbai_house_price_raw
ADD COLUMN house_price_category VARCHAR(20);
UPDATE mumbai_house_price_raw
SET house_price_category = CASE WHEN price < 20000 THEN 'Below 20K'
																WHEN price >= 20000 AND price < 40000 THEN '20K - 40K'
																WHEN price >= 40000 THEN 'Above 50K'
END;

aveek22

SELECT
house_price_category,
COUNT(1) number_houses
FROM mumbai_house_price_raw
GROUP BY house_price_category;

aveek22

CREATE OR REPLACE VIEW vw_mumbai_houses_airport_5km
AS
SELECT *
FROM mumbai_house_price_raw
WHERE 1=1
AND ST_DISTANCE(
	ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(72.874374 19.096713)',4326), 7755),
	ST_TRANSFORM((geometry),7755)
) <= 5000;

aveek22

CREATE OR REPLACE VIEW vw_mumbai_houses_airport_5km_west_facing
AS
SELECT * FROM vw_mumbai_houses_airport_5km
WHERE 1=1
AND "desc" ~ '^.(W|w)est (F|f)acing.$'

aveek22

Parameters:
  pDbName:
    Type: String
  pDbUser:
    Type: String
  pDbPass:
    Type: String
    Description: "It should be less than 8 characters"

Resources:

aveek22

CREATE TABLE public.sqlshack_regex (
    id integer NOT NULL,
    stockindustry text NOT NULL,
    stockmarket text NOT NULL,
    stocksector text NOT NULL,
    stockname text NOT NULL,
    stockvalue numeric(5,2) NOT NULL,
    stockholder text NOT NULL,

aveek22

DROP TABLE IF EXISTS GreaterManchesterCrime;

CREATE TABLE IF NOT EXISTS GreaterManchesterCrime (
	CrimeID	TEXT PRIMARY KEY ,
	CrimeTS	TIMESTAMP,
	Location	TEXT,
	LSOA	TEXT,
	Type	TEXT,
	Outcome	TEXT,
	theGeom	GEOMETRY

aveek22

SELECT * FROM GreaterManchesterCrime
WHERE CrimeID ~ '^[A-Z].*$'

aveek22

SELECT * FROM GreaterManchesterCrime
WHERE CrimeID ~ '^[0-9].*$'

aveek22

SELECT * FROM GreaterManchesterCrime
WHERE CrimeID ~ '^\d.*$'