-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  yearbirthday TEXT NOT NULL,
  register BINARY(4)  ,
  datum DATE ,
  town CHAR (25)  ,
  taxes NUMERIC ,
  hourstime TIMESTAMP  
);
--création de une deuxième table
  CREATE TABLE students2 (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL  
  );
--création de une troisième table 
  CREATE ASSERTION
    CHECK (NOT EXISTS (SELECT * FROM students, students2
      WHERE students.name = students2.name ));

-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M', '1970','0010','1969-07-20','Washington','25','20206-03-21 24:00:00');
INSERT INTO students VALUES (2, 'Joanna','F','2000','0001','2002-10-25','New-York city','21','1977-03-25 13:03:02');
INSERT INTO students VALUES (3, 'Carine','F','2006','0011','2015-10-25','Los Angeles','27','1975-03-23 08:25:00');
INSERT INTO students VALUES (4, 'Sophie', 'F','2020','0101','1998-09-25','Boston','33','1970-03-31 15:50:00');
INSERT INTO students VALUES (5, 'Joséphine','F','2002','1111','2003-11-11','Silverspring','15','1972-10-27 14:22:00');
INSERT INTO students VALUES (6,'Marc','M','2015','1001','1975-03-23','Paris','22', '2023-11-24 09:10:00');
INSERT INTO students VALUES (7,'Yawa','F','2018','1110','1970-03-31','Bruxelles','17','2025-06-01 08:00:00');
INSERT INTO students VALUES (8,'Bonange','F','2016','1100','1972-10-27','Munich','28', '2026-06-04 22:10:50');
INSERT INTO students VALUES (9,'Gillot','F','1963','1010','1963-05-26','Rome','35', '2026-06-04 10:30:00');
INSERT INTO students VALUES ( 10,'Collard','M','1950', '1100','1960-05-25','Berlin','27', '2024-06-04 10:30:00');

INSERT INTO students2 VALUES (1,'Carine');
INSERT INTO students2 VALUES (2,'Nadine' );

    -- fetch some values
SELECT * FROM students WHERE gender = 'F' or gender = 'M' ;
SELECT * FROM students2 WHERE name = 'Nadine' ;