Milestone 3 SQL Queries.rtf

{\rtf1\ansi\ansicpg1252\cocoartf2639
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fnil\fcharset0 HelveticaNeue;\f1\fnil\fcharset0 HelveticaNeue-Bold;}
{\colortbl;\red255\green255\blue255;\red0\green0\blue0;\red0\green0\blue0;\red191\green100\blue38;
}
{\*\expandedcolortbl;;\cssrgb\c0\c0\c0;\csgray\c0\c0;\csgenericrgb\c74902\c39216\c14902;
}
\margl1440\margr1440\vieww13420\viewh13520\viewkind0
\deftab560
\pard\pardeftab560\slleading20\partightenfactor0

\f0\fs26 \cf0 1) Simple: find all employees who work under a certain person\
SELECT employee_id\
FROM TO_Employees JOIN worksUnder wU ON TO_Employees.employee_id = wU.employee_id\
WHERE parent_id = `$\{searchID\}`;\
\
2) Simple: companies that have a certain position in it.\
SELECT CompanyName\
FROM to_employees\
WHERE role LIKE \'93%`$\{search_position\}`%\'94;\
\pard\pardeftab560\slleading20\pardirnatural\partightenfactor0
\cf0 \
\pard\pardeftab560\slleading20\partightenfactor0
\cf0 3) Simple: companies that have an opening of a certain job position\
SELECT employer_name\
FROM handshake_jobs\
WHERE title like \'93%`$\{search_position\}`%\'94;\
\
4) Simple: display company information\
SELECT *\
FROM to_companies\
WHERE companyName LIKE '%$\{search_position\}%';\
\
\pard\pardeftab560\slleading20\partightenfactor0
\cf2 \cb3 5) Simple: Show the list of companies and job opening titles where the job posting is in a specified location and the average early (<5 years of experience) salary at that location meets some threshold. \
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardeftab560\pardirnatural\partightenfactor0
\cf2 WITH ExpSalaryTable AS (SELECT company, AVG(totalyearlycompensation) AS AvgSalary\
                        FROM Salary\
                        GROUP BY company\
                        HAVING AvgSalary > 10000)\
SELECT employer_name AS Company, title AS JobName\
FROM HS_Jobs\
JOIN ExpSalaryTable ON HS_Jobs.employer_name = ExpSalaryTable.company\
WHERE (location_cities_1 LIKE 'New York'\
    OR location_cities_2 LIKE 'New York'\
    OR location_cities_3 LIKE 'New York'\
    OR location_cities_4 LIKE 'New York'\
    OR location_cities_5 LIKE 'New York');\
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardeftab560\pardirnatural\partightenfactor0
\cf4 \
\pard\pardeftab560\slleading20\partightenfactor0
\cf0 \cb1 \
6) Simple: given job opening, give estimated salary for it\
SELECT AVG(basesalary)\
FROM Salary s\
JOIN HS_Jobs h ON h.employer_name = s.company\
  AND h.title = s.title\
WHERE h.title LIKE '%$\{search_position\}%';\
\
7) Complex: Select the names, roles, and descriptions of top ranking people in a company, i.e., the people who are at most 3 degrees of freedom away from the CEO\cf2 \cb3 \
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardeftab560\pardirnatural\partightenfactor0
\cf2 WITH CompanyCEO AS (\
    SELECT employee_id\
    FROM TO_Employees\
    WHERE CompanyName LIKE 'Neuralink' AND role LIKE '%CEO%'\
    ),\
    Dof1 AS (\
        SELECT worksUnder.employee_id\
        FROM worksUnder\
        JOIN CompanyCEO ON worksUnder.parent_id = CompanyCEO.employee_id\
    ),\
    Dof2 AS (\
        SELECT worksUnder.employee_id\
        FROM worksUnder\
        JOIN Dof1 ON Dof1.employee_id = worksUnder.parent_id\
    ),\
    Dof3 AS (\
        SELECT worksUnder.employee_id\
        FROM worksUnder\
        JOIN Dof2 ON Dof2.employee_id = worksUnder.parent_id\
        ),\
    AllDof3Employees AS (\
        SELECT * FROM CompanyCEO\
        UNION (SELECT * FROM Dof1)\
        UNION (SELECT * FROM Dof2)\
        UNION (SELECT * FROM Dof3)\
    )\
SELECT employeeName AS Name, role AS Role, description AS Description\
FROM TO_Employees\
JOIN AllDof3Employees ON TO_Employees.employee_id = AllDof3Employees.employee_id;\
\pard\pardeftab560\slleading20\partightenfactor0
\cf2 \
\pard\pardeftab560\slleading20\partightenfactor0
\cf0 \cb1 \
8) Complex: Similar people recommendation: select 5 people within 2 degrees of freedom from person with similar job role to user\
# unoptimized version, 13 sec runtime\
WITH desiredRole AS (\
                    SELECT role\
                    FROM TO_Employees\
                    WHERE employee_id = @inputPerson\
                    ),\
                    deg0 AS (\
                    SELECT *\
                    FROM TO_Employees NATURAL JOIN desiredRole d\
                    ),\
                    deg1 AS (\
                        SELECT *\
                        FROM (SELECT deg0.employee_id FROM worksUnder JOIN deg0 ON deg0.employee_id = worksUnder.parent_id) boss\
                        UNION (SELECT deg0.employee_id FROM worksUnder JOIN deg0 ON deg0.employee_id = worksUnder.employee_id)\
                    ),\
                    deg2 AS (\
                        SELECT *\
                        FROM (SELECT deg1.employee_id FROM worksUnder JOIN deg1 ON deg1.employee_id = worksUnder.parent_id) boss\
                        UNION (SELECT deg1.employee_id FROM worksUnder JOIN deg1 ON deg1.employee_id = worksUnder.employee_id)\
                    ),\
                    alldegs AS (\
                            SELECT employee_id FROM deg0\
                            UNION (SELECT * FROM deg1)\
                            UNION (SELECT * FROM deg2)\
                    )\
                SELECT TO_Employees.employee_id AS employee_id, employeeName, CompanyName, role\
                FROM TO_Employees\
                JOIN alldegs ON TO_Employees.employee_id = alldegs.employee_id\
                ORDER BY employeeName\
                LIMIT 5;\
\
\
# optimized version, ~1 sec runtime\
WITH desiredRole AS (\
                    SELECT role\
                    FROM TO_Employees\
                    WHERE employee_id = @inputPerson\
                    ),\
                    deg0 AS (\
                    SELECT employee_id\
                    FROM TO_Employees JOIN desiredRole d ON TO_Employees.role = d.role\
                    LIMIT 5\
                    ),\
                    deg1 AS (\
                        SELECT employee_id\
                        FROM (SELECT deg0.employee_id FROM worksUnder JOIN deg0 ON deg0.employee_id = worksUnder.parent_id) boss\
                        UNION ALL (SELECT deg0.employee_id FROM worksUnder JOIN deg0 ON deg0.employee_id = worksUnder.employee_id)\
                        LIMIT 5\
                    ),\
                    deg2 AS (\
                        SELECT employee_id\
                        FROM (SELECT deg1.employee_id FROM worksUnder JOIN deg1 ON deg1.employee_id = worksUnder.parent_id) boss\
                        UNION ALL (SELECT deg1.employee_id FROM worksUnder JOIN deg1 ON deg1.employee_id = worksUnder.employee_id)\
                        LIMIT 5\
                    ),\
                    alldegs AS (\
                            SELECT * FROM deg0\
                            UNION ALL (SELECT * FROM deg1)\
                            UNION ALL (SELECT * FROM deg2)\
                    )\
                SELECT DISTINCT (TO_Employees.employee_id) AS employee_id, employeeName, CompanyName, role\
                FROM TO_Employees\
                JOIN alldegs ON TO_Employees.employee_id = alldegs.employee_id\
                ORDER BY employeeName\
                LIMIT 5;\
\
9) Complex: Find jobs from company where no one is remote.\
SELECT *\
FROM HS_Jobs H\
WHERE H.employer_name NOT IN (\
    SELECT CompanyName\
    FROM TO_Employees\
    WHERE remote = 1\
    ) AND H.remote = 0\
ORDER BY employer_name;\
\
optimized: completes less than 1 sec:\
SELECT *\
FROM HS_Jobs\
WHERE remote = 0\
ORDER BY employer_name;\
\
\
\
10) Complex: Given a person, find the highest paying open position with the same title as that person\
#Unoptimized, doesn\'92t complete after 30+ seconds\
WITH eq_role_postings AS (\
     SELECT job_id, TO_Employees.role AS role, salary, company\
     FROM ((SELECT HS_Jobs.id AS job_id, HS_Jobs.title AS role, basesalary + bonus AS salary, company\
           FROM HS_Jobs JOIN Salary S\
             ON HS_Jobs.title = S.title) open_sals\
         JOIN TO_Employees ON TO_Employees.role = open_sals.role)\
     WHERE employee_id = @empl_id\
 )\
 SELECT *\
 FROM eq_role_postings\
 WHERE salary >= ALL (\
     SELECT salary\
     FROM eq_role_postings\
 );\
\
#optimized, completes after ~1 second\
WITH eq_role_postings AS (SELECT open_sals.role, job_id, salary, company\
                          FROM (SELECT *\
                                FROM (SELECT role\
                                      FROM TO_Employees\
                                      WHERE employee_id = $\{employee_id\}) employee_reduce\
                                         Natural JOIN\
                                     (SELECT Salary.title AS role, company, MAX(basesalary + bonus) AS salary\
                                      FROM Salary\
                                      GROUP BY title, company) sal_reduce) open_sals\
                                JOIN\
                                (SELECT HS_Jobs.title AS role, HS_Jobs.id AS job_id, employer_name\
                                      FROM HS_Jobs) job_reduce\
                                ON company=employer_name AND open_sals.role=job_reduce.role\
)\
SELECT *\
FROM eq_role_postings\
WHERE salary >= ALL (\
    SELECT salary\
    FROM eq_role_postings\
);\
\
\
\
\
AWS Database info\
db instance identifier: companies-instance\
master username: companiesadmin\
masterpassword: grapes!!
\f1\b\fs32 \

\f0\b0\fs26 Endpoint: companies-instance.cikaustfuy1j.us-east-1.rds.amazonaws.com\
Port: 3306}