SQL LITE SOT

gistfile1.sql

create table cities (
  code varchar(3) primary key,
  name text
);

create table people (
  email text check ( email like '%@%' ),
  gender text not null check ( gender in ('M', 'F') ),
  name text not null,
  password text,
  age int not null,
  city_code varchar(3) references cities(code),
  primary key (email)
);



INSERT INTO cities VALUES ( 'wlg', 'Wellington' );
INSERT INTO cities VALUES ( 'akl', 'Auckland' );
INSERT INTO cities VALUES ( 'chc', 'Christchurch' );

INSERT INTO people (email, gender, name, age, city_code)
VALUES ( '[email protected]', 'M',
         'Ben Hana', 38, 'wlg' );

INSERT INTO people (email, gender, name, age, city_code)
VALUES ( '[email protected]', 'F',
         'Helen Clark', 58, 'wlg' );

INSERT INTO people (email, gender, name, age, city_code)
VALUES ( '[email protected]', 'M',
         'John Key', 47, 'akl' );

INSERT INTO people (email, gender, name, age, city_code)
VALUES ( '[email protected]', 'F',
         'Kerry Prendergast', 63, 'chc' );


SELECT * from cities;

SELECT name, age
FROM people
WHERE gender = 'M'
  AND age BETWEEN 20 AND 40;

SELECT *
FROM people
WHERE gender = 'F';

CREATE VIEW women
AS
  SELECT email, name, password, age, city_code
  FROM people
  WHERE gender = 'F';

CREATE VIEW men
AS
  SELECT email, name, password, age, city_code
  FROM people
  WHERE gender = 'M';

SELECT *
FROM men
    JOIN women;


SELECT men.name, women.name
FROM men, women
WHERE men.city_code = women.city_code AND
      CASE WHEN men.age > women.age
       THEN women.age > ((men.age / 2) + 7)
       ELSE men.age > ((women.age / 2) + 7)
      END;


UPDATE people
SET password = substr( name, 0, 5 );


UPDATE people
SET password = 'waka'
WHERE email = '[email protected]';


DELETE FROM people
WHERE email = '[email protected]';