Charles Chung, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

/*
Table 1- Person:  id, name, hobby, work_id    // Hobby: FISHING, SAILING
Table 2- Work  :  id, job_position, salary, comp_name

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS work;
DROP TABLE IF EXISTS tbl_person;

SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE work (
     id INT NOT NULL,
     job_position CHAR(30) NOT NULL,
     name CHAR(30) NOT NULL,     
     salary INT,
     PRIMARY KEY (id)
);

CREATE TABLE tbl_person(
     id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     hobby CHAR(30) NOT NULL,    
     work_id INT,
     PRIMARY KEY (id),
     FOREIGN KEY (work_id)
      REFERENCES work(id)
);

INSERT INTO work (id, job_position, name, salary) VALUES(1, "software developer", "Company A", 110000);
INSERT INTO work (id, job_position, name, salary) VALUES(2, "software architect", "Company A", 130000);
INSERT INTO work (id, job_position, name, salary) VALUES(3, "QA Analyst", "Company A", 55000);
INSERT INTO work (id, job_position, name, salary) VALUES(4, "software engineer", "Company B", 70000);
INSERT INTO work (id, job_position, name, salary) VALUES(5, "electric engineer", "Company C", 75000);
INSERT INTO work (id, job_position, name, salary) VALUES(6, "qa analyst", "Company C", 75000);
SELECT * FROM work;

INSERT INTO tbl_person(name, hobby, work_id) VALUES("Eric", "Skiing", 3);
INSERT INTO tbl_person(name, hobby, work_id) VALUES("Stewart", "Music", 2);
INSERT INTO tbl_person(name, hobby, work_id) VALUES("Nancy", "Sailing", 1);
INSERT INTO tbl_person(name, hobby, work_id) VALUES("Jen", "Sailing", 1);
INSERT INTO tbl_person(name, hobby, work_id) VALUES("Paul", "Music", 4);
INSERT INTO tbl_person(name, hobby, work_id) VALUES("Dan", "Music", 5);
SELECT * FROM tbl_person;

#TODO: SQL HERE
#1: List Person's name, hobby, job position, company name, salary
select p.name, p.hobby, w.job_position, w.name, w.salary
from work w
join tbl_person p
where p.work_id=w.id;

#2. List the count of people for each company 
#Company A, 5
#Company B, 3
select w.name, count(p.name)
from work w
join tbl_person p
where p.work_id=w.id
group by w.name;

#3. Find all jobs with no current employees. 
select w.job_position
from work w
left join tbl_person p on w.id = p.work_id
where p.name is null;

#4. Average salary of people by their hobbies
#Sailing, 1400000
select p.hobby, avg(salary)
from work w
join tbl_person p
where w.id=p.work_id
group by p.hobby;


absolute service time: 0,42 sec