18 SUQUERY in SELECT Clause

SELECT first_name, last_name, salary, (SELECT first_name FROM employees limit 1 )

FROM employees --senseless query but is not illegal

Exercises

1) Returns all employees that work in the electronics DIVISION

2) Return all employees that work in Asia and Canada and make more than 130 000

WHERE country IN (‘Asia’, ’Canada)

3) Subquery in the SELECT clause first_name of employee, how much less he earns compared to
The highest paid in the company, keep query that they are working in Canada and Asia.

SELECT first_name,(SELECT MAX(salary) FROM employees) max_salary, salary original_salary, ((SELECT

MAX(salary) FROM employees) - salary ) substraction

FROM employees

GROUP BY first_name, salary

ORDER BY first_name

19 Subqueries using ANY and ALL

ANY/ALL ---- subquery

HAVING ---- grouping

Region_id > ALL

Are used with WHERE and HAVING

EXERCISE Write a query that returns all of those employees that work in the kids division AND

The dates at which those employees were hired are greater than all of the hired_dates of employees
who

Work in the maintenance department

2) Give the most frequent salaries MODE

Highest value towards the top

--20

Disctinct will not work

Write a query just to show names once with one id

USING SUBQUERIES INSTEAD OF COLUMNS creates more flexibility, specially when data is deleted

2) compute average excluding the minimum and maximum amount

Outlier

SELECT first_name, region_id, salary, (SELECT MAX(salary) FROM employees)

FROM employees

WHERE region_id IN(SELECT region_id FROM regions WHERE country IN('Asia','Canada'))

GROUP BY first_name, region_id, salary

SELECT MAX(salary)

FROM employees

SELECT first_name, region_id, salary, (SELECT MAX(salary) FROM employees)

FROM employees

SELECT first_name, department, (SELECT MAX(salary) FROM employees) max_salary, salary

original_salary, ((SELECT MAX(salary) FROM employees) - salary ) substraction

FROM employees

GROUP BY first_name, department, salary

ORDER BY first_name

--19

SELECT * FROM employees

WHERE region_id > ALL(SELECT region_id FROM regions WHERE country='United States' )

--CHALLENGE

SELECT first_name, department, hire_date

FROM employees

WHERE department = ANY (SELECT department FROM departments WHERE division='Kids')

AND hire_date > ALL (SELECT hire_date FROM employees WHERE department='Maintenance')

--1st way

select salary FROM(

SELECT salary, COUNT(salary) AS count

FROM employees

--WHERE count = 1

--WHERE salary = ANY (SELECT COUNT(salary) FROM employees )

GROUP BY salary

ORDER BY COUNT(salary) desc, salary desc

LIMIT 1) a

-2nd way

SELECT salary FROM employees

GROUP BY salary

HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM employees GROUP BY salary)

ORDER BY salary desc

LIMIT 1
--20

--a

CREATE table dupes (id integer, name varchar(10));

INSERT INTO dupes VALUES(1,'FRANK');

INSERT INTO dupes VALUES(2,'FRANK');

INSERT INTO dupes VALUES(3,'ROBERT');

INSERT INTO dupes VALUES(4,'ROBERT');

INSERT INTO dupes VALUES(5,'SAM');

INSERT INTO dupes VALUES(6,'FRANK');

INSERT INTO dupes VALUES(7,'PETER');

SELECT *

FROM dupes

WHERE id IN(

SELECT min(id)

FROM dupes

--WHERE name IN(SELECT DISTINCT name FROM dupes)

GROUP BY name

ORDER BY min(id)

DELETE

FROM dupes

WHERE id NOT IN(

SELECT min(id)

FROM dupes

--WHERE name IN(SELECT DISTINCT name FROM dupes)

GROUP BY name

ORDER BY min(id)

drop tables dupes

--b

SELECT ROUND(AVG(salary))

FROM employees

WHERE salary NOT IN ((SELECT MIN(salary) FROM employees), (SELECT MAX(salary) FROM employees))

--20 CASE

SELECT total, COUNT(*)

FROM (SELECT first_name, salary,

CASE

WHEN salary < 100000 THEN 'UNDER PAID'

WHEN salary > 100000 AND salary < 160000 THEN 'PAID WELL'

WHEN salary > 160000 THEN 'EXECUTIVE'

ELSE 'UNPAID'

END as total

FROM employees

ORDER BY salary desc) case_table

--WHERE salary < 100000

--OR salary > 100000 AND salary < 160000

--OR salary > 160000

GROUP BY total

TRANSPOSING DATA
--22 Show the name of the highest employee in another column

SELECT total, COUNT(*)

FROM (SELECT first_name, salary,

CASE

WHEN salary < 100000 THEN 'UNDER PAID'

WHEN salary > 100000 AND salary < 160000 THEN 'PAID WELL'

WHEN salary > 160000 THEN 'EXECUTIVE'

ELSE 'UNPAID'

END as total

FROM employees

ORDER BY salary desc) case_table

--WHERE salary < 100000

--OR salary > 100000 AND salary < 160000

--OR salary > 160000

GROUP BY total

SELECT *

FROM employees

--20

--1

SELECT SUM (CASE WHEN department = 'Sports' THEN 1 ELSE 0 END) as sports_employees,

SUM (CASE WHEN department = 'Tools' THEN 1 ELSE 0 END) as tools_employees,

SUM (CASE WHEN department = 'Clothing' THEN 1 ELSE 0 END) as clothing_employees,

SUM (CASE WHEN department = 'Computers' THEN 1 ELSE 0 END) as computers_employees

FROM employees

--2

SELECT first_name,

CASE WHEN region_id = '1' THEN(SELECT country FROM regions WHERE region_id=1) END region_1,

CASE WHEN region_id = '2' THEN(SELECT country FROM regions WHERE region_id=2) END region_2,

CASE WHEN region_id = '3' THEN(SELECT country FROM regions WHERE region_id=3) END region_3,

CASE WHEN region_id = '4' THEN(SELECT country FROM regions WHERE region_id=4) END region_4,

CASE WHEN region_id = '5' THEN(SELECT country FROM regions WHERE region_id=5) END region_5,

CASE WHEN region_id = '6' THEN(SELECT country FROM regions WHERE region_id=6) END region_6,

CASE WHEN region_id = '7' THEN(SELECT country FROM regions WHERE region_id=7) END region_7

FROM employees

--3

SELECT (COUNT(region_1)+COUNT(region_2)+COUNT(region_3)) United_States,

(COUNT(region_4)+COUNT(region_5)) Asia, (COUNT(region_6)+COUNT(region_7)) Canada

FROM

(SELECT

CASE WHEN region_id = '1' THEN(SELECT country FROM regions WHERE region_id=1) END region_1,

CASE WHEN region_id = '2' THEN(SELECT country FROM regions WHERE region_id=2) END region_2,

CASE WHEN region_id = '3' THEN(SELECT country FROM regions WHERE region_id=3) END region_3,

CASE WHEN region_id = '4' THEN(SELECT country FROM regions WHERE region_id=4) END region_4,

CASE WHEN region_id = '5' THEN(SELECT country FROM regions WHERE region_id=5) END region_5,

CASE WHEN region_id = '6' THEN(SELECT country FROM regions WHERE region_id=6) END region_6,

CASE WHEN region_id = '7' THEN(SELECT country FROM regions WHERE region_id=7) END region_7
FROM employees)a

SELECT *

FROM regions

--23

--a

SELECT department,

(SELECT count(department)

FROM employees e2 WHERE e1.department = e2.department) as

avg_department_salary

FROM employees e1

GROUP BY department

ORDER BY count(department) desc

LIMIT 14

--b -- 2 subqueries

SELECT department, (SELECT max(salary) FROM employees WHERE department = d.department)

--SUBQUERY only returns one column

FROM departments d

WHERE 38 < (SELECT count(*)

FROM employees e2

WHERE d.department = e2.department)

SELECT first_name, department, salary

FROM employees
ORDER BY salary desc

23-

27-

Sources of data

SELECT first_name, country

FROM employees, regions

WHERE employees.region_id = regions.region_id

SELECT first_name, email, e.department, division, country

FROM employees e, departments d, regions r

WHERE e.department = d.department

AND e.region_id = r.region_id

AND email IS NOT NULL

SELECT country, count(first_name)

FROM (SELECT * FROM regions r), employees e -- non correlated subquiry

WHERE e.region_id = r.region_id

GROUP BY country

ORDER BY count(first_name)

SELECT department, count(first_name)

FROM employees

GROUP BY department

EXERCISE Every department each number of employees per department

At the end total

28 Cartesian product with the CROSS join

Gives all probs

SELECT COUNT (*)

FROM (SELECT *

FROM employees a, employee b

) sub returns 1 million, it is a cartesian product

SELECT *

FROM employees a CROSS JOIN departments b

29 more exercises in subqueries and joins

29 ½ VIEW = virtual table based on queries

Schemas , tables / views

CREATE VIEW v_employee_information AS

Enter query here

Select * from
v_employee_ information – is not a table, and cannot be altered

INLINE VIEW

SELECT *

FROM (SELECT * FROM departments)

31 ORDER BY in the Window option

--27

SELECT department
FROM employees

EXCEPT --takes the first set and removes the ones coinciding in the second one

--UNION --automatically eliminates duplicates , UNION ALL does not eliminate duplicates , columns must
match(same data type)

SELECT department

FROM departments

--27

SELECT DISTINCT department

FROM departments

EXCEPT --takes the first set and removes the ones coinciding in the second one --MINUS is used in others
db

--UNION --automatically eliminates duplicates , UNION ALL does not eliminate duplicates , columns must
match(same data type)

SELECT DISTINCT department

FROM employees

UNION

SELECT country

FROM regions

--ORDER BY should go by the end

SELECT department, COUNT(*)

FROM employees

GROUP BY department

UNION ALL

SELECT 'TOTAL', COUNT(*)

FROM employees

--30
SELECT first_name, department,

--(SELECT COUNT(*) FROM employees e1 WHERE e1.department=e2.department) only returns 999

COUNT(*) OVER(partition by department) dept_count,--detach results , windowing function

region_id,

COUNT(*) OVER(partition by region_id) region_count

FROM employees e2

ORDER BY department, first_name

SELECT first_name, department, count(*) over(PARTITION BY department)

FROM employees

WHERE region_id = 3

--31 the running total

SELECT first_name, hire_date, salary,

SUM(salary) OVER(ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT

ROW) as running_total_of_salaries

--tabulated row , unbounded = preceding salary, data frame , added to a frame of reference

FROM employees

SELECT first_name, hire_date, salary,

SUM(salary) OVER(PARTITION BY department ORDER BY hire_date ) as running_total_of_salaries

FROM employees

--adjecent salaries

SELECT first_name, hire_date, salary,

SUM (salary) OVER(ORDER BY hire_date ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW) --total
of all employees

FROM EMPLOYEES
SELECT SUM(salary) from employees

-- 32 LEAD and LAG functions

SELECT first_name, last_name, salary,

LEAD(salary) OVER() next_salary

FROM employees

SELECT first_name, last_name, salary,

LAG(salary) OVER() previous_salary

FROM employees

SELECT department, last_name, salary,

LAG(salary) OVER(ORDER BY salary desc) next_higher_salary

FROM employees

SELECT department, last_name, salary,

LEAD(salary) OVER(PARTITION BY department ORDER BY salary desc) closest_lower_salary

FROM employees