SELECT and WHERE / Project database
1. select * from person;
2. select firstname, lastname from person order by lastname;
3. select distinct city from person;
4. select * from person where firstname='Jim';
5. select * from person where city='Turku' and birth_year > 1980;
6. select * from project where place is NULL;
7. select * from project where NOT place is NULL;
8. select firstname, lastname, year(curdate()) - birth_year as 'age' from person;

*****************************************************

AGGREGATE FUNCTIONS /Project database

1. SELECT MAX(salary) , MIN(salary) FROM person;
2. SELECT COUNT(id_project) FROM project;
3. SELECT AVG(birthyear) FROM person;
4. SELECT COUNT(id_person) FROM person WHERE city='Turku';
5. SELECT SUM(salary) FROM person WHERE city='Turku';
6. SELECT city,SUM(salary) AS 'Sum of Salaries' FROM person GROUP BY city;
7. SELECT city,ROUND(AVG(salary),2) AS 'Average Salaries' FROM person GROUP BY city;
8. SELECT id_project ,SUM(work_hour)  FROM hour GROUP BY id_project;
9. SELECT id_project ,SUM(work_hour)  FROM hour GROUP BY id_project HAVING SUM(work_hour) >= 500;
10. SELECT city, GROUP_CONCAT(lastname) AS 'person' FROM person GROUP BY city;
11. SELECT place, GROUP_CONCAT(pname) AS 'project' FROM project GROUP BY place;

*****************************************************

JOIN /Project database

1.  SELECT lastname, work_hour 
    FROM person INNER JOIN hour ON person.id_person=hour.id_person;

2.  SELECT lastname, SUM(work_hour) AS 'Sum of hours' 
    FROM person 
    INNER JOIN hour ON person.id_person=hour.id_person 
    GROUP BY person.id_person;

3.  SELECT pname, SUM(work_hour) AS 'Sum of hours' 
    FROM project 
    INNER JOIN hour ON project.id_project=hour.id_project 
    GROUP BY project.id_project;

4.  SELECT lastname, pname, work_hour 
    FROM person 
    INNER JOIN hour ON person.id_person= hour.id_person 
    INNER JOIN project ON project.id_project=hour.id_project;

5.  SELECT pname
    FROM person 
    INNER JOIN hour ON person.id_person= hour.id_person 
    INNER JOIN project ON project.id_project=hour.id_project
    WHERE lastname='Morrison';

6.  SELECT pname, lastname, work_hour
    FROM person 
    INNER JOIN hour ON person.id_person= hour.id_person 
    RIGHT JOIN project ON project.id_project=hour.id_project;


*****************************************************

SUBQUERY /Project database

1.  SELECT lastname FROM person WHERE city =(
    SELECT city FROM person WHERE lastname='Morrison');

2.  SELECT pname FROM project WHERE place =(
    SELECT city FROM person WHERE lastname='Morrison');

3.  SELECT pname FROM project WHERE id_project IN (
        SELECT id_project FROM hour WHERE id_person = (
            SELECT id_person FROM person WHERE  lastname='Morrison'));


*****************************************************

UNION /Project database

1.  SELECT city, COUNT(city) AS 'Amount' FROM person 
    GROUP BY city
    UNION
    SELECT 'TOTAL', COUNT(city) FROM person; 

2.  SELECT project.id_project, pname, SUM(work_hour) AS 'sum of hour' 
    FROM project INNER JOIN hour ON project.id_project = hour.id_project 
    GROUP BY project.id_project
    UNION
    SELECT 'PROJ','TOTAL', SUM(work_hour) FROM hour; 

3.  SELECT pname, place FROM project WHERE place IS NOT NULL 
    UNION
    SELECT pname, 'NO PLACE' FROM project WHERE place IS NULL; 

4.  SELECT place, GROUP_CONCAT(pname) AS 'project' 
    FROM project     
    GROUP BY place;

5.  SELECT place, GROUP_CONCAT(pname) AS 'project' FROM project     
    WHERE place IS NOT NULL GROUP BY place
    UNION
    SELECT 'No Place', GROUP_CONCAT(pname) AS 'project' FROM project WHERE place IS  NULL GROUP BY place;


*****************************************************

PROCEDURES /Project database

1.

DELIMITER //
CREATE PROCEDURE addHours(IN id_per INT, IN id_proj INT, IN amount INT )
BEGIN   
    INSERT INTO hour(id_person, id_project, work_hour) VALUES(id_per, id_proj, amount);
END //
DELIMITER ;

2.

DELIMITER //
CREATE PROCEDURE addHoursNew(IN id_per INT, IN id_proj INT, IN amount INT )
BEGIN  
    SET @test1=(SELECT COUNT(*) FROM hour WHERE id_person=id_per AND id_project=id_proj);
    IF @test1 >0 THEN
        UPDATE hour SET work_hour=work_hour+amount WHERE id_person=id_per AND id_project=id_proj;
    ELSE 
        INSERT INTO hour(id_person, id_project, work_hour) VALUES(id_per, id_proj, amount);
    END IF;
END //
DELIMITER ;