CREATE TABLE customers(
cid int PRIMARY KEY,
name varchar(20),
surname varchar(30),
gender char(1) CHECK(gender='M' OR gender='F'),
bday DATE CHECK((SYSDATE-bday)>18),
address varchar(20),
city varchar(20),
zip int,
hphone int,
mphone int,
email varchar(30)
);

CREATE TABLE rooms(
roomid int UNIQUE PRIMARY KEY,
cid int FOREIGN KEY REFERENCES customers(cid),
price int CHECK(price>20 AND price<=250),
season varchar(7),
rtype varchar(6) CHECK(rtype='single' OR rtype='double' OR rtype='triple' OR rtype='suite' OR rtype='lux'),
availiable char(1)
);

CREATE TABLE payment(
payid int PRIMARY KEY,
roomid int FOREIGN KEY REFERENCES customers(roomid),
resnum int,
way varchar(6) UNIQUE NOT NULL,
res DATE,
restime TIME,
cout DATE,
cin DATE CHECK(cin<cout),
adv DATE,
pay DATE,
money int NOT NULL
);

INSERT INTO customers VALUES ('1','petros','arkandos','M','1990-01-14','papandreou 23','Athens','11555','2106666661','6978542145','skatanask@gmail.com');
INSERT INTO customers VALUES ('2','kwstas','mpizmpizidis','M','1980-08-14','mixalakopoulou 4','Athens','11554','2102458784','6987452546','jkghjgggomws@gmail.com');
INSERT INTO customers VALUES ('3','nikos','koukos','M','1991-01-13','kautatzogleiou 9','Athens','11556','21569548723','6945878545','ggerino@gmail.com');
INSERT INTO customers VALUES ('4','maria','palamh','F','1967-02-10','karpou 15-20','Larissa','11455','2136569865','6423012456','lolipop@gmail.com');
INSERT INTO customers VALUES ('5','tasos','tsekouras','M','19780-11-14','plataiwn 21','Laxtarissa','11444','2195587845','6987546398','trexwvrady@gmail.com');
INSERT INTO customers VALUES ('6','periklhs','athinaios','M','1990-09-24','akropolhs 1','Thessaloniki','11333','2310457568','6987548725','popotas@yahoo.com');
INSERT INTO customers VALUES ('7','tasia','tzivagera','F','1994-01-04','peiraiws 7','Athens','14520','2105548745','6989658301','sovarinioning@gmail.com');
INSERT INTO customers VALUES ('8','epaminontas','nikodouleadhs','M','1965-06-05','thanatou 8','Athens','12451','2106363245','6945623894','kartoner@yahoo.grm');
INSERT INTO customers VALUES ('9','kwnstantina','eukolopoulou','F','1977-8-7','sugkrou 4','Patra','11025','2104554875','6989698195','papapalalalpala@gmail.com');
INSERT INTO customers VALUES ('10','marina','falirou','F','1949-01-12','palaiou 1337','Athens','11111','2155454785','694542021','ormodox@gmail.com');
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO rooms VALUES ('','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')
INSERT INTO payment VALUES ('','','','','','','','','','','')


SELECT fname, lname, SYSDATE-bday AS age FROM customers WHERE age>30;
SELECT COUNT(name) FROM customers WHERE city='Athens';


SELECT CONCAT('Room number: ', roomid, ', Type: double, availiable.' AS doublesavailiable FROM rooms WHERE (availiable='Y' AND rtype='double');
SELECT CONCAT('Reservation number: ', payid, ' with longest stay duration') AS maxpayid FROM payment WHERE MAX(cout-cin);
SELECT DISTINCT rtype, COUNT(rtype) AS rtypecount FROM rooms WHERE (season='high' AND price>150);