<jupyter_start><jupyter_text>SQL for Analyst 4 - Join<jupyter_code># set MySQL URL
user = "dz3vg"
password = ""
host = "localhost"
port = 3306
dbname = "dataapplab_db"
with open("MySQL.key", "r") as file:
    password = file.read()
connection_string = f"mysql+mysqlconnector://{user}:{password}@{host}:{port}/{dbname}"

# connect to MySQL server
%load_ext sql
%sql $connection_string

# clean password
password = ""
connection_string = ""
%sql use hr;
%%sql

select
    *
from
    employees, departments
limit 10
;
%%sql

select
    count(distinct employee_id)
from
    employees
;
%%sql

select
    count(distinct department_id)
from
    departments
;
%%sql

select
    count(*)
from
    employees, departments
;
%%sql

select
    *
from
    employees, departments
where
    employees.department_id = departments.department_id
limit 10
;
%%sql

select
    *
from
    employees
where
    employee_id = 100
;
%%sql

select
    *
from
    departments
where
    department_id = 90
;
%%sql

select
    concat(e.first_name, ' ', e.last_name) as full_name, 
    e.department_id, 
    d.department_name
from
    departments as d
        join
    employees as e on d.department_id = e.department_id
order by d.department_id
limit 10
;
%%sql

select
    concat(e.first_name, ' ', e.last_name) as full_name, 
    e.department_id, 
    d.department_name
from
    departments as d
        join
    employees as e on d.department_id = e.department_id
order by full_name
limit 10
;
%%sql

select
    concat(e.first_name, ' ', e.last_name) as full_name, 
    e.department_id, 
    d.department_name
from
    departments as d
        join
    employees as e on d.department_id = e.department_id
order by full_name desc
limit 10
;
%%sql

select
    concat(e.first_name, ' ', e.last_name) as full_name, 
    e.department_id, 
    d.department_name
from
    departments as d
        left join
    employees as e on d.department_id = e.department_id
limit 100, 10
;
%%sql

select
    concat(e.first_name, ' ', e.last_name) as full_name, 
    e.department_id as department_id_e, 
    d.department_id as department_id_d, 
    d.department_name
from
    departments as d
        left join
    employees as e on d.department_id = e.department_id
limit 100, 10
;
%%sql

select
    e.first_name, 
    e.last_name, 
    d.department_id, 
    l.city, 
    l.state_province
from
    employees as e
        join
    departments as d on e.department_id = d.department_id
        join
    locations as l on d.location_id = l.location_id
limit 10
;
%%sql

select
    first_name, 
    last_name, 
    salary, 
    (case
        when salary between 1000 and 2999 then '#A'
        when salary between 3000 and 5999 then '#B'
        when salary between 6000 and 9999 then '#C'
        when salary between 10000 and 14999 then '#D'
        when salary between 15000 and 24999 then '#E'
        when salary between 25000 and 40000 then '#F'
        else 'G+'
    end) as job_grades
from
    employees
limit 10
;
%%sql

select
    e.first_name, 
    e.last_name, 
    d.department_id, 
    d.department_name
from
    employees as e
        left join
    departments as d on e.department_id = d.department_id
where
    e.department_id in (40, 80)
order by e.last_name
limit 10
;
%%sql

select
    e.first_name, 
    e.last_name, 
    d.department_id, 
    d.department_name
from
    employees as e
        left join
    departments as d on e.department_id = d.department_id and e.department_id in (40, 80)
order by e.last_name
limit 10
;
%%sql

select
    e.first_name, 
    e.last_name, 
    d.department_id, 
    d.department_name
from
    employees as e
        join
    departments as d on e.department_id = d.department_id and e.department_id in (40, 80)
order by e.last_name
limit 10
;
%%sql

select
    d.department_id, 
    d.department_name, 
    e.first_name, 
    e.last_name
from
    employees as e
        right join
    departments as d on e.department_id = d.department_id
limit 10
;
%%sql

select
    d.department_id, 
    d.department_name, 
    e.first_name, 
    e.last_name
from
    employees as e
        right join
    departments as d on e.department_id = d.department_id
where
    e.first_name is null
limit 10
;
%%sql

select
    e.first_name, 
    e.last_name, 
    e.salary
from
    employees as e
        join
    employees as s on e.salary < s.salary and s.employee_id = 182
limit 10
;
%%sql

select
    first_name, 
    last_name, 
    salary
from
    employees
where
    salary < (select
            salary
        from
            employees
        where
            employee_id = 182)
limit 10
;
%%sql

select
    country_name, 
    city, 
    department_name
from
    countries
        join
    locations using (country_id)
        join
    departments using (location_id)
limit 10
;<jupyter_output>* mysql+mysqlconnector://dz3vg:***@localhost:3306/dataapplab_db
10 rows affected.
{'fraction_non_alphanumeric': 0.07733333333333334, 'fraction_numerical': 0.02666666666666667, 'mean_word_length': 2.553935860058309, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '507319', 'license': 'MIT', 'n_tokens_mistral': 1889, 'n_tokens_neox': 1718, 'n_words': 492, 'path': 'Data Scientist Bootcamp/MySQL for Analyst/SQL-for-Analyst-4-Join.ipynb', 'repo_name': 'smartzdp/Data-Application-Lab', 'size': '4875'}