Rakibul Haque, MySQL

Rakibul Haque

#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.salary 
from tbl_person as p join work as w on p.work_id = w.id;

#2. List the count of people for each company 
#Company A, 5
#Company B, 3

select w.name, count(w.name)
from tbl_person as p join work as w on p.work_id = w.id
group by w.name;

#3. Find all jobs with no current employees. 
select * from work
where id not in (
    select distinct work_id from tbl_person
    );

#4. Average salary of people by their hobbies
#Sailing, 1400000

select p.hobby, avg(w.salary)
from tbl_person p join work w on p.work_id = w.id
group by p.hobby;


run \| edit \| history \| help	0