SQLize | PHPize | SQLtest

CREATE TABLE movies ( id int CONSTRAINT firstkey PRIMARY KEY, title varchar (50), country varchar (50), date_prod date, director varchar(50), type varchar(20) ); INSERT INTO movies VALUES (1, 'The Matrix', 'USA', '1999-03-24', 'The Wachowskis', 'science fiction'); INSERT INTO movies VALUES (2, 'Men In Black', 'USA', '1999-05-18', 'Barry Sonnenfeld', 'science fiction'); INSERT INTO movies VALUES (3, 'Men In Black', 'USA', '1998-05-18', 'Barry Sonnenfeld', 'science fiction'); INSERT INTO movies VALUES (4, 'Men In Black', 'USA', '1997-05-18', 'Barry Sonnenfeld', 'superhero film'); INSERT INTO movies VALUES (5, 'Batman', 'USA', '1996-05-18', 'Barry Sonnenfeld', 'SUPERHERO FILM'); UPDATE movies SET TYPE = 'SUPERHERO FILM' WHERE id=5; UPDATE movies SET TYPE = 'superhero film' WHERE id=4; SELECT * FROM movies WHERE TYPE LIKE 'SUPERHERO%'; SELECT * FROM movies WHERE TYPE ILIKE 'SUPERHERO%'; ALTER TABLE movies ADD COLUMN email varchar (50); ALTER TABLE movies ADD COLUMN phone varchar (50); -- Added missing semicolon UPDATE movies SET email = 'truemail@bigcompany.com' where id=1; UPDATE movies SET email = 'truemail@аfalsecompany' where id=2; UPDATE movies SET email = 'nomailsomecompany.com' where id=3; UPDATE movies SET email = 'false@mail@smallcom#pany.com' where id=4; UPDATE movies SET email = 'othermail@smallcompany.com' where id=5; SELECT * FROM movies WHERE email LIKE '%@%'; SELECT * FROM movies WHERE email LIKE '%@%.%'; -- Changed REGEXP_LIKE to the appropriate syntax for your database -- PostgreSQL version: SELECT * FROM movies WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'; -- Or for MySQL: -- SELECT * FROM movies WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'; -- Similarly for the UPDATE: UPDATE movies SET email = 'defaultmail@mail.com' WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'; UPDATE movies SET phone = '+7123456789' where id =1; UPDATE movies SET phone = '+7123456788' where id =2; UPDATE movies SET phone = '+7123456777' where id =3; UPDATE movies SET phone = '+7123455555' where id =4; UPDATE movies SET phone = '+7123444444' where id =5; -- Changed REGEXP_LIKE to appropriate syntax: SELECT * FROM movies WHERE phone = '^[^9]{8,}$'; SELECT * FROM movies WHERE phone = '^\+?[0-8]{8,}$'; SELECT * FROM movies WHERE phone = '^[^986]{8,}$'; SELECT * FROM movies WHERE phone = '^\+?[0-4,7]{8,}$';