INSERT INTO GROUPS(groupname)
VALUES ('Grupa de bossi');
INSERT INTO GROUPS(groupname)
VALUES ('Grupa de fraeri');

INSERT INTO STUDENTS(username, password, fullname, username_ia, username_pbinfo, username_timus, username_codeforces)
VALUES ('andrei', 'parola', 'Andrei Popovici', 'warrior98', '', '199116WF', 'warrior98');
INSERT INTO STUDENTS(username, password, fullname, username_ia, username_pbinfo, username_timus, username_codeforces, groupname)
VALUES ('gicu', 'parola2', 'Gicu Gicu', 'gicu_infoarena', 'gicu_pbinfo', 'gicu_timus', 'gicu_codeforces', 'Grupa de fraeri');

INSERT INTO PROBLEMS(id_problema, site)
VALUES ('base3', 'infoarena', 9);
INSERT INTO PROBLEMS(id_problema, site)
VALUES ('1040B', 'codeforces', 7);
INSERT INTO PROBLEMS(id_problema, site)
VALUES ('1513', 'timus', 5);

INSERT INTO SOLVED(FK_username, FK_id_problema)
VALUES ('andrei', 'base3');
INSERT INTO SOLVED(FK_username, FK_id_problema)
VALUES ('gicu', '1040B');

SELECT * FROM STUDENTS;
SELECT * FROM GROUPS;
SELECT * FROM SOLVED;
SELECT * FROM PROBLEMS;
SELECT * FROM SCHEDULE;

INSERT INTO PROBLEMS(id_problema, site)
VALUES ('xor3', 'infoarena', 10);
INSERT INTO PROBLEMS(id_problema, site)
VALUES ('2654', 'pbinfo', 10);
INSERT INTO PROBLEMS(id_problema, site)
VALUES ('elicoptere', 'infoarena', 7);
INSERT INTO PROBLEMS(id_problema, site)
VALUES ('1013', 'timus', 6);
INSERT INTO PROBLEMS(id_problema, site)
VALUES ('dlboss', 'infoarena', 6);

INSERT INTO SOLVED(FK_username, FK_id_problema)
VALUES ('andrei', '1013');

SELECT *
FROM PROBLEMS
WHERE site = 'infoarena'

UNION

SELECT *
FROM PROBLEMS
WHERE site = 'timus'

INSERT INTO SCHEDULE(cod, DAY, HOUR, username)
VALUES (1, '2018-11-16', '17:00:00', 'andrei');
INSERT INTO SCHEDULE(cod, DAY, HOUR, username)
VALUES (2, '2018-11-17', '18:00:00', 'andrei');

SELECT STUDENTS.username, SCHEDULE.DAY
FROM STUDENTS
LEFT JOIN SCHEDULE
ON STUDENTS.username = SCHEDULE.username;

SELECT STUDENTS.username, SCHEDULE.DAY
FROM STUDENTS
INNER JOIN SCHEDULE
ON STUDENTS.username = SCHEDULE.username;

SELECT PROBLEMS.site, STUDENTS.fullname
FROM STUDENTS
LEFT OUTER JOIN SOLVED
    ON STUDENTS.username = SOLVED.FK_username
LEFT OUTER JOIN PROBLEMS
    ON PROBLEMS.id_problema = SOLVED.FK_id_problema;

SELECT COUNT(id_problema), site
FROM PROBLEMS
GROUP BY site
HAVING COUNT(id_problema) >= 2;

SELECT AVG(CAST(difficulty AS FLOAT)), site
FROM PROBLEMS
WHERE site IN (
    SELECT site
    FROM PROBLEMS
    GROUP BY site
    HAVING COUNT(id_problema) >= 2
)
GROUP BY site;

SELECT MIN(difficulty), site
FROM PROBLEMS
GROUP BY site;