DROP TABLE IF EXISTS student;
CREATE TABLE student 
(id INTEGER PRIMARY KEY AUTOINCREMENT, 
fname TEXT, 
lname TEXT, 
born DATE, 
country TEXT);

DROP TABLE IF EXISTS professor;
CREATE TABLE professor 
(id INTEGER PRIMARY KEY AUTOINCREMENT, 
fname TEXT, 
lname TEXT, 
born DATE, 
country TEXT,
quote TEXT,
department_id INTEGER);

DROP TABLE IF EXISTS department;
CREATE TABLE department 
(id INTEGER PRIMARY KEY,
name TEXT,
head_id INTEGER);

DROP TABLE IF EXISTS course;
CREATE TABLE course
(id INTEGER PRIMARY KEY,
name TEXT,
semester CHAR,
year INTEGER,
credits INTEGER);

DROP TABLE IF EXISTS offeredby;
CREATE TABLE offeredby(
course_id INTEGER,
department_id INTEGER,
PRIMARY KEY(course_id,department_id));

DROP TABLE IF EXISTS teaches;
CREATE TABLE teaches 
(professor_id INTEGER,
course_id INTEGER,
PRIMARY KEY (course_id,professor_id));

DROP TABLE IF EXISTS takes;
CREATE TABLE takes
(student_id INTEGER,
course_id INTEGER,
grade FLOAT,
PRIMARY KEY(student_id, course_id));

DROP TABLE IF EXISTS mentors;
CREATE TABLE mentors
(mentor_id INTEGER,
mentee_id INTEGER,
PRIMARY KEY (mentor_id, mentee_id)); 

INSERT INTO department
VALUES (1,'Computer Science',null);

INSERT INTO department
VALUES (2,'Mathematics',null);

INSERT INTO department
VALUES (3,'Linguistics',null);


INSERT INTO student (fname, lname, born, country)
VALUES ('Bob', 'Dylan', '2000-10-15', 'USA');

INSERT INTO student (fname, lname, born, country)
VALUES ('Leonard', 'Cohen', '1997-02-23', 'Canada');

INSERT INTO student (fname, lname, born, country)
VALUES ('Joe', 'Dassin', '1999-04-19', 'France');

INSERT INTO student (fname, lname, born, country)
VALUES ('Jennifer', 'Lopez', '1999-12-30', 'Mexico');

INSERT INTO student (fname, lname, born, country)
VALUES ('Lady', 'Gaga', '2001-05-19', 'USA');

INSERT INTO student (fname, lname, born, country)
VALUES ('John', 'Lennon', '2001-01-01', 'England');

INSERT INTO professor (fname, lname, born, country, quote, department_id)
VALUES ('Luciano', 'Pavarotti', '1954-05-18', 'Italy', 
'You don''t need any brains to listen to music',1);

INSERT INTO professor (fname, lname, born, country, quote, department_id)
VALUES ('Placido', 'Domingo', '1951-10-18', 'Spain', 
'My strength is my enthusiasm',1);

INSERT INTO professor (fname, lname, born, country, quote, department_id)
VALUES ('Jose', 'Carreras', '1960-10-16', 'Spain', 
'Yes, you must have the courage of being free',1);

INSERT INTO professor (fname, lname, born, country, quote, department_id)
VALUES ('Anna', 'Netrebko', '1971-11-11', 'Russia', 
'I know what I''m missing',2);

INSERT INTO professor (fname, lname, born, country, quote, department_id)
VALUES ('Maria', 'Callas', '1951-02-03', 'USA', 
'You are born an artist or you are not',2);

INSERT INTO course
VALUES (1, 'Algorithms','S',2018,4);

INSERT INTO course
VALUES (2, 'Databases','F',2017,4);

INSERT INTO course
VALUES (3, 'Python','F',2017,3);

INSERT INTO course
VALUES (4, 'NLP','S',2017,4);

INSERT INTO course
VALUES (5, 'Linear Algebra','F',2017,3);

INSERT INTO course
VALUES (6, 'Linear Algebra','S',2018,3);

INSERT INTO offeredby
VALUES (1,1),(2,1),(3,1),(4,3),(5,2),(6,2);

INSERT INTO teaches
VALUES (1,1), (1,2), (2,2), (3,3), (4,3), (5,4), (5,5);

INSERT INTO takes
VALUES (1,1,90),(1,4,85),
(2,1,100),(2,2,95),(2,4,98),
(3,3,45),(3,5,null),
(4,3,70),(4,4,90),(4,5,null),
(6,2,90),(6,4,100);

INSERT INTO mentors 
VALUES (1,2), (1,3), (1,4), (3,5);

UPDATE department
SET head_id = 2 WHERE id = 1;

UPDATE department
SET head_id = 5 WHERE id = 2;