1.1. UPDATE Presidents SET LastName='Roosevelt' WHERE LastName LIKE 'Franklin';

1.2. SELECT * FROM Presidents WHERE (LastName >= 'Franklin' AND LastName <= 'Roosevelt')

1.3. select distinct salary from Salaries order by salary DESC OFFSET 9 LIMIT 1;


2.1

# Create database

CREATE DATABASE sql_task;
\connect sql_task


# CREATE Tables

CREATE TABLE Tags (
	discipline_tag VARCHAR(40) UNIQUE
        );


CREATE TABLE Persons (
        person_id serial NOT NULL UNIQUE,
        first_name varchar(40) NOT NULL,
        last_name varchar(40) NOT NULL,
        email varchar(80) NOT NULL UNIQUE,
        PRIMARY KEY (person_id)
        );

CREATE TABLE Courses (
	course_id serial NOT NULL UNIQUE,
	course_name VARCHAR(90) NOT NULL,
	course_tag VARCHAR(40) REFERENCES Tags(discipline_tag),
	start_date DATE,
	end_date DATE
	);

CREATE TABLE person_course_join_table (
	person INT REFERENCES Persons(person_id),
	course INT REFERENCES Courses(course_id),
	UNIQUE (person, course)
	);


# Populate some data

INSERT INTO Tags (discipline_tag) VALUES ('Math');
INSERT INTO Tags (discipline_tag) VALUES ('Geography');
INSERT INTO Tags (discipline_tag) VALUES ('History');
INSERT INTO Tags (discipline_tag) VALUES ('Physics');
INSERT INTO Tags (discipline_tag) VALUES ('Literature');
INSERT INTO Tags (discipline_tag) VALUES ('Design');


INSERT INTO Persons (first_name, last_name, email) VALUES ('Anthony','Freeman','anthony@maytech.net');
INSERT INTO Persons (first_name, last_name, email) VALUES ('Emilia','Prygrodska','mila@rambler.ru');
INSERT INTO Persons (first_name, last_name, email) VALUES ('Olga','Mlynko','mlynkolga@gmail.com');
INSERT INTO Persons (first_name, last_name, email) VALUES ('Taras','Kozak','tarskozak@gmail.com');
INSERT INTO Persons (first_name, last_name, email) VALUES ('Oleg','Doskach','oleg45d@gmail.com');
INSERT INTO Persons (first_name, last_name, email) VALUES ('Taras','Sosnovskyi','taras@comfort-service.com');


INSERT INTO Courses (course_name, course_tag, start_date, end_date) VALUES ('Addictive Math','Math','2016-10-21','2016-11-02');
INSERT INTO Courses (course_name, course_tag, start_date, end_date) VALUES ('Physics around us','Physics','2016-11-01','2016-11-30');
INSERT INTO Courses (course_name, course_tag, start_date, end_date) VALUES ('Fashion Design','Design','2016-11-12','2016-11-30');
INSERT INTO Courses (course_name, course_tag, start_date, end_date) VALUES ('WEB Design','Design','2017-01-01','2017-02-16');

INSERT INTO person_course_join_table (person, course) VALUES ((SELECT person_id FROM Persons LIMIT 1), (SELECT course_id FROM Courses LIMIT 1) );
INSERT INTO person_course_join_table (person, course) VALUES ((SELECT person_id FROM Persons LIMIT 1 OFFSET 1), (SELECT course_id FROM Courses LIMIT 1 OFFSET 1) );
INSERT INTO person_course_join_table (person, course) VALUES ((SELECT person_id FROM Persons LIMIT 1 OFFSET 2), (SELECT course_id FROM Courses LIMIT 1 OFFSET 2) );
INSERT INTO person_course_join_table (person, course) VALUES ((SELECT person_id FROM Persons LIMIT 1 OFFSET 3), (SELECT course_id FROM Courses LIMIT 1 OFFSET 3) );



# QUERYING

a) SELECT course_name, course_tag FROM Courses;

      course_name       |  course_tag
-------------------+---------
 Addictive Math    | Math
 Physics around us | Physics
 Fashion Design    | Design
 WEB Design        | Design
(4 rows)


b) SELECT first_name,Last_name,email FROM Persons LEFT JOIN person_course_join_table ON Persons.person_id=person_course_join_table.person where person_course_join_table.person IS NULL;

 first_name | last_name  |           email
------------+------------+---------------------------
 Oleg       | Doskach    | oleg45d@gmail.com
 Taras      | Sosnovskyi | taras@comfort-service.com
(2 rows)


c) SELECT * FROM Tags WHERE discipline_tag IN (SELECT course_tag From Courses);

  discipline_tag
---------
 Math
 Physics
 Design
(3 rows)


d) SELECT EXISTS (SELECT 1 From Courses WHERE course_tag='Design');

bool
------
 t
(1 row)


e) SELECT course_name FROM Courses WHERE EXTRACT(WEEK FROM end_date) = EXTRACT(WEEK FROM NOW());

 course_name
----------------
 Addictive Math
(1 row)


f) SELECT * FROM Courses ORDER BY (end_date - start_date) DESC LIMIT 1;

 person_id |   course_name   | course_tag  | start_date |  end_date
------+------------+--------+------------+------------
    4 | WEB Design | Design | 2017-01-01 | 2017-02-16
(1 row)