/* 012 - BETWEEN (between two values) */
CREATE TABLE tOne(
id INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
age INT NOT NULL CHECK(age >= 18),
gender VARCHAR(1) NOT NULL,
phone VARCHAR(10) NOT NULL UNIQUE,
city VARCHAR(15) NOT NULL DEFAULT 'Agra'
);
INSERT INTO tOne(id,name,age,gender,phone,city)
VALUES
(1,"Ram Kumar","23","M","4022155","Agra"),
(2,"Sarita Kumari","21","F","4034421","Delhi"),
(3,"Salman Khan","20","M","4056221","Agra"),
(4,"Juhi Chawla","28","F","4022156","Bhopal"),
(5,"Anil Kapoor","22","M","4025221","Agra"),
(6,"John Abraham","21","M","4056776","Delhi");
SELECT * FROM tOne WHERE age BETWEEN 18 AND 20;
SELECT * FROM tOne WHERE age NOT BETWEEN 18 AND 20;
SELECT * FROM tOne WHERE id BETWEEN 2 AND 5;
SELECT * FROM tOne WHERE id NOT BETWEEN 2 AND 5;
SELECT * FROM tOne WHERE name BETWEEN "a" AND "k";
SELECT * FROM tOne WHERE name BETWEEN "anil" AND "kamal"; -- first characters
SELECT * FROM tOne WHERE date BETWEEN "a" AND "k";
-- between for dates
CREATE TABLE persons(
id INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
birth_date DATE NOT NULL
);
INSERT INTO persons(id,name,birth_date)
VALUES
(1,"Ram","1995-02-10"),
(2,"Madan","1995-11-03"),
(3,"Salman","1996-06-12"),
(4,"Shoiab","1995-04-21"),
(5,"juhi","1996-09-25");
SELECT * FROM persons WHERE birth_date BETWEEN "1995-01-01" AND "1995-06-30";