Cacher is the code snippet organizer for pro developers

--- Select

SELECT * FROM actor;
SELECT actor_id FROM actor;
SELECT first_name, last_name, email FROM customer;

--- Distinct 
SELECT DISTINCT release_year FROM film;
SELECT DISTINCT rental_rate FROM film;
SELECT DISTINCT rating FROM film;

--- Where
SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie' AND last_name = 'Rice';
SELECT * FROM customer WHERE first_name = 'Jared';
SELECT * FROM payment where amount = 7.99;
SELECT * FROM payment where amount <= 4.99;
SELECT * FROM customer WHERE store_id = 1 AND address_id > 5;
SELECT email FROM customer WHERE first_name = 'Nancy' AND last_name = 'Thomas'
SELECT description FROM film WHERE title = 'Outlaw Hanky';
SELECT phone FROM address WHERE address='259 Ipoh Drive';

--- Count 
SELECT COUNT(*) FROM payment;
SELECT COUNT(DISTINCT(amount)) FROM payment;

--- LIMIT
SELECT * FROM customer LIMIT 5;

--- ORDER
SELECT first_name, last_name FROM customer ORDER BY first_name;
SELECT first_name, last_name FROM customer ORDER BY first_name ASC; --- default 
SELECT first_name, last_name FROM customer ORDER BY first_name DESC;
SELECT first_name, last_name FROM customer ORDER BY first_name ASC, last_name DESC;
SELECT first_name FROM customer ORDER BY last_name; --- doesn't support by all sql
SELECT customer_id, amount FROM payment ORDER BY amount DESC LIMIT 10;
SELECT title FROM film  WHERE film_id<=5 ORDER BY film_id;
SELECT title FROM film ORDER BY film_id LIMIT 5;

--- BETWEEN
SELECT customer_id, amount FROM payment WHERE amount NOT BETWEEN 8 AND 9;
SELECT amount, payment_date FROM payment WHERE payment_date BETWEEN '2007-02-07' AND '2007-02-15';

--- IN
SELECT customer_id, rental_id, return_date
FROM rental
WHERE customer_id IN (1,2)
ORDER BY return_date DESC;

--- LIKE
SELECT first_name, last_name 
FROM customer
WHERE first_name LIKE 'Jen%';

SELECT first_name, last_name 
FROM customer
WHERE first_name LIKE '%y';

SELECT first_name, last_name 
FROM customer
WHERE first_name LIKE '%er%';

--- _ single % multi
SELECT first_name, last_name 
FROM customer
WHERE first_name LIKE '_her%';

SELECT first_name, last_name 
FROM customer
WHERE first_name NOT LIKE 'Jen%';

--- ILIKE case insensitive
SELECT first_name, last_name 
FROM customer
WHERE first_name ILIKE 'BAR%';

SELECT COUNT(amount) FROM payment WHERE amount > 5;
SELECT COUNT(*) FROM actor WHERE first_name LIKE 'P%';
SELECT COUNT(DISTINCT(district)) FROM address;
SELECT DISTINCT(district) FROM address;
SELECT COUNT(*) FROM film WHERE rating='R' AND replacement_cost BETWEEN 5 AND 15;
SELECT COUNT(*) FROM film WHERE title LIKE '%Truman%';

--- AVG
SELECT ROUND(AVG(amount), 5) FROM payment;

--- MIN
SELECT MIN(amount) FROM payment;

--- MAX
SELECT MAX(amount) FROM payment;

--- SUM
SELECT SUM(amount) FROM payment;

--- GROUP BY
SELECT customer_id FROM payment GROUP BY customer_id;
SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id;
SELECT SUM(amount) FROM payment GROUP BY customer_id;

SELECT customer_id, SUM(amount) FROM payment 
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

SELECT staff_id, COUNT(payment_id)
FROM payment
GROUP BY staff_id;

SELECT rating, COUNT(rating) FROM film
GROUP BY rating;

SELECT rental_duration, COUNT(rental_duration)
FROM film
GROUP BY rental_duration;

SELECT rating, ROUND(AVG(rental_rate), 2)
FROM film
GROUP BY rating;

SELECT staff_id, COUNT(amount), SUM(amount) FROM payment GROUP BY staff_id;
SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC LIMIT 5;

--- HAVING
SELECT customer_id, SUM(amount)
FROM payment 
GROUP BY customer_id
HAVING SUM(amount) > 200;

SELECT store_id, COUNT(customer_id)
FROM customer
GROUP BY store_id
HAVING COUNT(customer_id) > 300;

SELECT rating, ROUND(AVG(rental_rate), 2)
FROM film
WHERE rating IN ('R', 'G', 'PG')
GROUP BY rating
HAVING ROUND(AVG(rental_rate), 2) < 3;

SELECT customer_id, COUNT(amount) FROM payment GROUP BY customer_id
HAVING COUNT(amount) >= 40; 

SELECT rating, ROuND(AVG(rental_duration), 2) FROM film GROUP BY rating HAVING ROuND(AVG(rental_duration), 2) > 5;


SELECT customer_id, SUM(amount) FROM payment WHERE staff_id=2 GROUP BY customer_id 
HAVING SUM(amount)>=110; 

SELECT COUNT(title) FROM film WHERE title LIKE 'J%';

SELECT customer_id, first_name, last_name FROM customer
WHERE first_name LIKE 'E%' AND address_id < 500
ORDER BY customer_id DESC
LIMIT 1;

--- AS
SELECT payment_id AS my_payment_column
FROM payment;

SELECT customer_id, SUM(amount) AS total_spent
FROM payment
GROUP BY customer_id;

--- JOIN

SELECT 
customer.customer_id,
first_name,
last_name,
email,
amount,
payment_date
FROM customer
INNER JOIN payment ON payment.customer_id = customer.customer_id;

SELECT title, COUNT(title) AS copies_at_store1 FROM inventory
INNER JOIN film ON inventory.film_id = film.film_id
WHERE store_id = 1
GROUP BY title
ORDER BY title;

SELECT title, name AS movie_language
FROM film
INNER JOIN language ON language.language_id = film.language_id;

SELECT title, name AS movie_language
FROM film AS fm
INNER JOIN language AS lan ON lan.language_id = fm.language_id;

--- LEFT OUTER JOIN

SELECT film.film_id, film.title, inventory_id
FROM film
LEFT OUTER JOIN inventory ON inventory.film_id = film.film_id
WHERE inventory.film_id IS NULL
ORDER BY film.film_id;

SELECT film.film_id, film.title, inventory_id
FROM film
LEFT OUTER JOIN inventory ON inventory.film_id = film.film_id
WHERE inventory_id IS NULL
ORDER BY title;

--- extract

SELECT SUM(amount), extract(month from payment_date) AS month
FROM payment
GROUP BY month
ORDER BY SUM(amount) DESC
LIMIT 1;

--- math
SELECT customer_id+rental_id FROM payment;
SELECT rental_id/customer_id FROM payment;
SELECT round(AVG(amount),2) FROM payment;

--- String
SELECT first_name || ' ' || last_name AS full_name FROM customer;
SELECT lower(first_name) FROM customer;

--- SUBQUERY

SELECT title, rental_rate 
FROM film
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film);

SELECT film_id, title 
FROM film
WHERE film_id IN (SELECT inventory.film_id FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30') ORDER BY film_id;


SELECT a.customer_id, a.first_name, a.last_name, b.customer_id, b.first_name, b.last_name
FROM customer AS a, customer AS b 
WHERE a.first_name = b.last_name;

SELECT a.customer_id, a.first_name, a.last_name, b.customer_id, b.first_name, b.last_name
FROM customer AS a
JOIN customer AS b 
ON a.first_name = b.last_name;

1.
SELECT * FROM cd.facilities;
2. 
SELECT name, membercost FROM cd.facilities;
3.
SELECT * FROM cd.facilities WHERE membercost > 0;
4. 
SELECT facid, name, membercost, monthlymaintenance 
FROM cd.facilities WHERE membercost > 0 AND monthlymaintenance / 50 > membercost;
5.
SELECT * FROM cd.facilities WHERE name LIKE '%Tennis%'
6. 
SELECT * FROM cd.facilities WHERE facid IN (1, 5);
7. 
SELECT memid, surname, firstname, joindate FROM cd.members WHERE joindate >= '2012-09-01';
8.
SELECT DISTINCT surname FROM cd.members ORDER BY surname LIMIT 10;
9.
SELECT * FROM cd.members ORDER BY joindate DESC LIMIT 1;
SELECT MAX(joindate) as lastest from cd.members;
10.
SELECT COUNT(*) FROM cd.facilities WHERE guestcost >= 10;
11.
SKIP
12. 
SELECT facid, SUM(slots)  FROM cd.bookings 
WHERE starttime >= '2012-09-01' AND starttime < '2012-10-01'
GROUP BY facid
ORDER BY SUM(slots);
13.
SELECT facid, SUM(slots)  FROM cd.bookings 
GROUP BY facid
HAVING SUM(slots) > 1000;
14.
SELECT name, cd.bookings.facid, starttime FROM cd.bookings 
INNER JOIN cd.facilities ON cd.bookings.facid = cd.facilities.facid
WHERE name LIKE '%Tennis Court%' AND (starttime>='2012-09-21' AND starttime<'2012-09-22') ORDER BY starttime;
15.
SELECT surname, firstname, cd.bookings.memid, starttime 
FROM cd.bookings 
INNER JOIN cd.members ON cd.bookings.memid = cd.members.memid
WHERE surname='Farrell' AND firstname='David';


--- CREATE TABLE
CREATE TABLE account(
user_id serial PRIMARY KEY,
username VARCHAR (50) UNIQUE NOT NULL,
password VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

CREATE TABLE role(
role_id serial PRIMARY KEY,
role_name VARCHAR (255) UNIQUE NOT NULL
);

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE link(
	ID serial PRIMARY KEY,
	url VARCHAR(255) NOT NULL,
	name VARCHAR(255) NOT NULL,
	description VARCHAR(255),
	rel VARCHAR(50)
);

INSERT INTO link(url, name)
VALUES
('www.google.com', 'Google');

INSERT INTO link(url, name)
VALUES
('www.yahoo.com', 'Yahoo');

INSERT INTO link (url, name)
VALUES
('www.bing.com', 'Bing'),
('www.amazon.com', 'Amazon');

CREATE TABLE link_copy (LIKE link);

INSERT INTO link_copy
SELECT * FROM link 
WHERE name='Bing';

INSERT INTO link(url, name)
VALUES
('www.a.com', 'A'),
('www.b.com', 'B');

INSERT INTO link_copy
SELECT * FROM link;

UPDATE link
SET description = 'Empty Description';

UPDATE link 
SET description='Name starts with an A'
WHERE name LIKE 'A%';

UPDATE link
SET description = name;

UPDATE link
SET description = 'New Description'
WHERE id = 1
RETURNING id,url,name,description;

DELETE FROM link
WHERE name LIKE 'B%';

DELETE FROM link
WHERE name = 'A'
RETURNING *;

DROP TABLE IF EXISTS link;

CREATE TABLE link(
	link_id serial PRIMARY KEY,
	title VARCHAR(512) NOT NULL,
	url VARCHAR(1024) NOT NULL UNIQUE
);

ALTER TABLE link ADD COLUMN active boolean;

ALTER TABLE link DROP COLUMN active;

ALTER TABLE link RENAME COLUMN title TO new_title_name;

ALTER TABLE link RENAME TO url_table;

DROP TABLE test_two;
DROP TABLE IF EXISTS test_two;

DROP TABLE IF EXISTS test_two RESTRICT;

CREATE TABLE new_users(
	id serial PRIMARY KEY,
	first_name VARCHAR(50),
	birth_date DATE CHECK(birth_date > '1900-01-01'),
	join_date DATE CHECK(join_date > birth_date),
	salary integer CHECK(salary > 0)
);

--- Error by contraint salary 
INSERT INTO new_users(first_name, birth_date, join_date, salary)
VALUES ('Joe', '1980-02-02', '1990-04-04', -10);

CREATE TABLE checktest(
	sales integer CONSTRAINT positive_sales CHECK(sales>0)
);

CREATE TABLE learn_null(
	first_name VARCHAR(50),
	sales integer NOT NULL
);

INSERT INTO learn_null(first_name, sales)
VALUES ('John', 12);

CREATE TABLE people(
	id serial PRIMARY KEY,
	first_name VARCHAR(50),
	email VARCHAR(100) UNIQUE
);

INSERT INTO people(id, first_name, email) 
VALUES (1, 'Joe', 'joe@joe.com');

--- Error duplicate
INSERT INTO people(id, first_name, email)
VALUES (2, 'Joseph', 'joe@joe.com');

CREATE TABLE students(
	student_id serial PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	homerooom_number VARCHAR(50),
	phone VARCHAR(50) NOT NULL UNIQUE,
	email VARCHAR(50) UNIQUE,
	graduation_year DATE
);

CREATE TABLE teachers(
	teacher_id serial PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	homerooom_number VARCHAR(50),
	department VARCHAR(50),
	phone VARCHAR(50) NOT NULL UNIQUE,
	email VARCHAR(50) UNIQUE
);

--- Solution

CREATE TABLE students(
student_id serial PRIMARY KEY,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL, 
homeroom_number integer,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(115) UNIQUE,
grad_year integer);

CREATE TABLE teachers(
teacher_id serial PRIMARY KEY,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL, 
homeroom_number integer,
department VARCHAR(45),
email VARCHAR(20) UNIQUE,
phone VARCHAR(20) UNIQUE);

INSERT INTO students(first_name,last_name, homeroom_number,phone,grad_year)VALUES ('Mark','Watney',5,'7755551234',2035);
INSERT INTO teachers(first_name,last_name, homeroom_number,department,email,phone)VALUES ('Jonas','Salk',5,'Biology','jsalk@school.org','7755554321');

CREATE VIEW customer_info AS
SELECT first_name, last_name, email, address, phone
FROM customer
JOIN address
ON customer.address_id = address.address_id;

SELECT * FROM customer_info;

ALTER VIEW customer_info RENAME TO customer_master_list;


--- OTHER Lecture

SELECT * 
FROM employees
WHERE email IS NULL;

SELECT * 
FROM employees
WHERE NOT email IS NULL;

SELECT * 
FROM employees
WHERE email IS NOT NULL;

SELECT * 
FROM employees
WHERE department = 'Sports'
OR department = 'First Aid'
OR department = 'Toys'
OR department = 'Garden';

SELECT * 
FROM employees 
WHERE department IN ('Sports', 'First Aid', 'Toys', 'Garden');

SELECT * 
FROM employees 
WHERE salary BETWEEN 80000 AND 100000;

SELECT first_name, hire_date FROM employees
WHERE salary > 165000 OR (department='Sports' and gender='M');

SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2002-01-01' AND '2004-01-01';

SELECT *
FROM employees
WHERE (gender='M' AND salary > 40000 AND salary < 100000 AND department='Automotive') 
OR (gender='F' AND department='Toys');

SELECT *
FROM employees
ORDER BY department DESC;

SELECT DISTINCT department
FROM employees;

SELECT DISTINCT department
FROM employees
ORDER BY 1
LIMIT 10;

SELECT DISTINCT department
FROM employees
ORDER BY 1
FETCH FIRST 10 ROWS ONLY;

SELECT DISTINCT department as sorted_departments
FROM employees
ORDER BY 1 DESC
FETCH FIRST 3 ROWS ONLY;

SELECT first_name, last_name as "Last Name", department, salary as "Yearly Salary"
FROM employees;

SELECT student_name
FROM students
WHERE age BETWEEN 18 AND 20;

SELECT *
FROM students
WHERE student_name like '%ch%'
OR student_name like '%nd';

SELECT student_name
FROM students
WHERE (student_name like '%ae%' OR student_name like '%ph%')
AND age != 19;

SELECT student_name
FROM students
ORDER BY age DESC;

SELECT student_name, age
FROM students
ORDER BY age DESC
LIMIT 4;

SELECT *
FROM students
WHERE AGE <= 20 
AND ( student_no BETWEEN 3 AND 5 OR student_no = 7 )
OR (AGE > 20 AND student_no >= 4);

SELECT UPPER(first_name) FROM employees;

SELECT LENGTH(first_name), LOWER(department)
FROM employees;

SELECT TRIM('    HELLO THERE    ');

SELECT first_name ||' '|| last_name full_name, (salary > 140000) is_highly_paid
FROM employees ORDER BY salary desc;

SELECT department, ('Clothing' IN (department, first_name))
FROM employees;

SELECT 'Clothing' IN ('Clothing', 'furniture', 'phones');

SELECT department, (department like '%oth%')
from employees;

SELECT SUBSTRING('This is test data' FROM 9 FOR 4) test_data_extracted

SELECT department, REPLACE(department, 'Clothing', 'Attire') modified_data
FROM departments;

SELECT department, 
REPLACE(department, 'Clothing', 'Attire') modified_data,
department || ' department' "Complete Department Name"
FROM departments;

SELECT SUBSTRING(email, POSITION('@' IN email) + 1)
FROM employees;

SELECT email, SUBSTRING(email, POSITION('@' IN email) + 1) formated_text
FROM employees;

SELECT COALESCE(email, 'NONE') as email
FROM employees;

SELECT SUM(salary)
FROM employees;

SELECT SUM(salary)
FROM employees
WHERE department='Clothing';

SELECT SUM(salary) 
FROM employees
WHERE department='Toys';

SELECT last_name || ' ' || 'works in the '|| department || 
' department'
FROM professors

SELECT 'It is ' || (salary > 95000) ||
' that professor ' || last_name || ' is highly paid'
FROM professors

SELECT last_name, 
       UPPER(SUBSTRING(department, 1, 3)) as department, 
       salary, hire_date
FROM professors

SELECT MAX(salary) as higest_salary,
MIN(salary) as lowest_salary
FROM professors
WHERE last_name != 'Wilson'

SELECT MIN(hire_date) 
FROM professors

SELECT * FROM cars GROUP BY make;
SELECT COUNT(*) FROM cars GROUP BY make;
SELECT make, COUNT(*) FROM cars GROUP BY make;

SELECT department, SUM(salary)
FROM employees
WHERE region_id in (4,5,6,7)
GROUP BY department

SELECT department, count(*) total_number_employees, 
ROUND(AVG(salary),2) avg_sal, MIN(salary) min_sal, MAX(salary) as max_sal
FROM employees
WHERE salary > 70000
GROUP BY department
ORDER BY total_number_employees DESC;

SELECT department, gender, count(*) 
FROM employees
GROUP BY department, gender
ORDER BY department;

SELECT department, count(*) 
FROM employees
GROUP BY department
HAVING count(*) > 35
ORDER BY department;

SELECT first_name, count(*) 
FROM employees
GROUP BY first_name
HAVING count(*) > 2

SELECT department FROM employees GROUP BY department;

SELECT SUBSTRING(email, POSITION('@' IN email) + 1) email_domain, count(*) domain_count
FROM employees 
WHERE email IS NOT NULL
GROUP BY email_domain
ORDER BY domain_count DESC;

SELECT gender, region_id, 
MIN(salary) min_salary, MAX(salary) max_salary, 
ROUND(AVG(salary)) avg_salary
FROM employees
GROUP BY gender, region_id
ORDER BY gender, region_id;

SELECT state
FROM fruit_imports 
GROUP BY state
ORDER BY SUM(supply) desc
LIMIT 1

SELECT season, MAX(cost_per_unit) highest_cost_per_unit
FROM fruit_imports
GROUP BY season

SELECT state
FROM fruit_imports
GROUP BY state, name
HAVING COUNT(name) > 1

SELECT season, COUNT(name)
FROM fruit_imports
GROUP BY season
HAVING count(name) = 3 OR count(name) = 4

SELECT state, SUM(supply * cost_per_unit) total_cost
FROM fruit_imports
GROUP BY state
ORDER BY total_cost desc
LIMIT 1

SELECT COUNT(COALESCE(fruit_name, 'SOMEVALUE'))
FROM fruits;

SELECT e.department
FROM employees e, departments d;

SELECT * FROM employees
WHERE department NOT IN (SELECT department FROM departments);

SELECT a.first_name, a.salary
FROM (SELECT * FROM employees WHERE salary > 150000) a;

SELECT a.employee_name, yearly_salary, b.department
FROM (SELECT first_name employee_name, salary yearly_salary FROM employees WHERE salary > 150000) a, 
(SELECT department FROM departments) b;

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

SELECT * FROM employees
WHERE department 
IN (SELECT department FROM departments WHERE division = 'Electronics');

SELECT * FROM employees
WHERE salary > 130000
AND region_id IN (SELECT region_id FROM regions WHERE country = 'Asia' OR country = 'Canada')

SELECT * FROM employees
WHERE salary > 130000
AND region_id IN (SELECT region_id FROM regions WHERE IN('Asia', 'Canada'))

SELECT * FROM employees
WHERE salary > 130000
AND region_id IN (SELECT region_id FROM regions WHERE country IN('Asia', 'Canada'))

SELECT first_name, department, salary, (SELECT MAX(salary) FROM employees),
(SELECT MAX(salary) FROM employees) - salary 
FROM employees
WHERE region_id IN (SELECT region_id FROM regions WHERE country IN ('Asia', 'Canada'));

SELECT * FROM employees 
WHERE region_id > ANY (SELECT region_id FROM regions WHERE country='United States');

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

SELECT * FROM employees
WHERE department = ANY (SELECT department FROM departments WHERE division='Kids' ) AND 
hire_date > ALL (SELECT hire_date FROM employees WHERE department='Maintenance');

SELECT salary FROM (
	SELECT salary, COUNT(*)
	FROM employees
	GROUP BY salary 
	ORDER BY count(*) DESC, salary DESC
	LIMIT 1
) a

SELECT salary 
FROM employees 
GROUP BY salary 
HAVING count(*) >= ALL (SELECT count(*) FROM employees GROUP BY salary)
ORDER BY salary DESC
LIMIT 1;

SELECT min(id), name
FROM dupes
GROUP BY name

SELECT * FROM dupes
WHERE id IN (
	SELECT min (id)
	FROM dupes
	GROUP BY name
);

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


SELECT student_name
FROM students WHERE student_no 
                    IN (SELECT student_no 
                        FROM student_enrollment
                        WHERE course_no 
                        IN ( SELECT course_no 
                            FROM courses 
                            WHERE course_title 
                                IN ('Physics', 'US History')));
                                
                                
                                
                                
SELECT student_name 
FROM students 
WHERE student_no 
IN (
    SELECT student_no FROM (
        SELECT student_no, COUNT(course_no) course_cnt
        FROM STUDENT_ENROLLMENT
        GROUP BY student_no
        ORDER BY course_cnt desc
        LIMIT 1
    )a
)

SELECT *
FROM students
WHERE age = (SELECT MAX(age) FROM students);

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
FROM employees
ORDER BY salary DESC;

SELECT a.category, 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 category
FROM employees
ORDER BY salary DESC
) a GROUP BY a.category;

SELECT a.category, COUNT(*) FROM (
SELECT first_name, salary,
CASE 
	WHEN salary < 100000 THEN 0
	WHEN salary > 100000 AND salary < 160000 THEN 1
	WHEN salary > 160000 THEN 2
	ELSE 99999
END as category
FROM employees
ORDER BY salary DESC
) a GROUP BY a.category;

SELECT SUM( CASE WHEN salary < 100000 THEN 1 ELSE 0 END ) as under_paid,
SUM( CASE WHEN salary > 100000 AND salary < 150000 THEN 1 ELSE 0 END) as paid_well,
SUM( CASE WHEN salary > 150000 THEN 1 ELSE 0 END ) as executive
FROM employees;

SELECT SUM( CASE WHEN salary < 100000 THEN 1 ELSE 0 END ) as under_paid,
SUM( CASE WHEN salary > 100000 AND salary < 150000 THEN 1 ELSE 0 END) as paid_well
FROM employees;

SELECT department, count(*)
FROM employees
WHERE department IN ('Sports', 'Tools', 'Clothing', 'Computers')
GROUP BY department;

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;

SELECT first_name, 
CASE WHEN region_id=1 THEN (SELECT country FROM regions WHERE region_id=1) END region_id_1,
CASE WHEN region_id=2 THEN (SELECT country FROM regions WHERE region_id=2) END region_id_2,
CASE WHEN region_id=3 THEN (SELECT country FROM regions WHERE region_id=3) END region_id_3,
CASE WHEN region_id=4 THEN (SELECT country FROM regions WHERE region_id=4) END region_id_4,
CASE WHEN region_id=5 THEN (SELECT country FROM regions WHERE region_id=5) END region_id_5,
CASE WHEN region_id=6 THEN (SELECT country FROM regions WHERE region_id=6) END region_id_6,
CASE WHEN region_id=6 THEN (SELECT country FROM regions WHERE region_id=7) END region_id_7
FROM employees;

SELECT united_states + asia + canada FROM (
SELECT COUNT(a.region_id_1) + COUNT(a.region_id_2) + COUNT(a.region_id_3) as United_states,
COUNT(a.region_id_4) + COUNT(a.region_id_5) as Asia,
COUNT(a.region_id_6) + COUNT(a.region_id_7) as Canada
FROM (
SELECT first_name, 
CASE WHEN region_id=1 THEN (SELECT country FROM regions WHERE region_id=1) END region_id_1,
CASE WHEN region_id=2 THEN (SELECT country FROM regions WHERE region_id=2) END region_id_2,
CASE WHEN region_id=3 THEN (SELECT country FROM regions WHERE region_id=3) END region_id_3,
CASE WHEN region_id=4 THEN (SELECT country FROM regions WHERE region_id=4) END region_id_4,
CASE WHEN region_id=5 THEN (SELECT country FROM regions WHERE region_id=5) END region_id_5,
CASE WHEN region_id=6 THEN (SELECT country FROM regions WHERE region_id=6) END region_id_6,
CASE WHEN region_id=7 THEN (SELECT country FROM regions WHERE region_id=7) END region_id_7
FROM employees
	) a ) b;

SELECT name, total_supply,
CASE WHEN total_supply < 20000 THEN 'LOW'
     WHEN total_supply >= 20000 AND total_supply <= 50000 THEN 'ENOUGH'
     WHEN total_supply > 50000 THEN 'FULL'
END as category
FROM (
SELECT name, sum(supply) total_supply
FROM fruit_imports
GROUP BY name
    ) a;
    
SELECT SUM(CASE WHEN season = 'Winter' THEN total_cost end) as Winter_total,
SUM(CASE WHEN season = 'Summer' THEN total_cost end) as Summer_total,
SUM(CASE WHEN season = 'Spring' THEN total_cost end) as Spring_total,
SUM(CASE WHEN season = 'Fall' THEN total_cost end) as Spring_total,
SUM(CASE WHEN season = 'All Year' THEN total_cost end) as Spring_total
FROM (
select season, sum(supply * cost_per_unit) total_cost
from fruit_imports
group by season
    ) a;
    
SELECT 
first_name, 
salary 
FROM employees e1
WHERE salary > (SELECT round(AVG(salary)) FROM employees e2 WHERE e1.region_id = e2.region_id);

SELECT 
first_name, 
department,
salary,
(SELECT round(AVG(salary)) FROM employees e2 WHERE e1.region_id = e2.region_id) as avg_department_salary
FROM employees e1

SELECT department
FROM departments d
WHERE 38 < (SELECT COUNT(*) FROM employees e WHERE e.department = d.department);

SELECT DISTINCT department
FROM employees e1
WHERE 38 < (SELECT COUNT(*) FROM employees e2 WHERE e1.department = e2.department);

SELECT department
FROM employees e1
WHERE 38 < (SELECT COUNT(*) FROM employees e2 WHERE e1.department = e2.department) GROUP BY department;

SELECT department, (SELECT MAX(salary) FROM employees WHERE department = d.department )
FROM departments d;

SELECT department, max(salary)
FROM employees
GROUP BY department;

-- my answer

SELECT department, first_name, salary,  
CASE WHEN salary = (SELECT MAX(salary) FROM employees e3 WHERE e1.department = e3.department ) THEN 'HIGHEST SALARY'
     WHEN salary = (SELECT MIN(salary) FROM employees e3 WHERE e1.department = e3.department ) THEN 'LOWEST SALARY' END as salary_in_department
FROM employees e1
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department = e2.department)
OR salary = (SELECT min(salary) FROM employees e2 WHERE e1.department = e2.department) 
ORDER BY department;

-- teacher answer 
SELECT department, first_name, salary,
CASE WHEN salary = max_by_department THEN 'HIGHEST SALARY'
	 WHEN salary = min_by_department THEN 'LOWEST SALARY'
END as salary_in_department
FROM (
SELECT department, first_name, salary,
	(SELECT max(salary) FROM employees e2
	 WHERE e1.department = e2.department) as max_by_department,
	(SELECT min(salary) FROM employees e2
	 WHERE e1.department = e2.department) as min_by_department
FROM employees e1
) a 
WHERE salary = max_by_department
	OR salary = min_by_department
ORDER BY 1;
	
SELECT first_name, email, employees.department, division, country 
FROM employees, departments, regions
WHERE employees.department = departments.department
AND employees.region_id = regions.region_id
AND email IS NOT NULL;

SELECT country, count(employee_id)
FROM employees e, regions r
WHERE e.region_id = r.region_id
GROUP BY country;

SELECT first_name, country
FROM employees INNER JOIN regions 
ON employees.region_id = regions.region_id;

SELECT first_name, email, division
FROM employees INNER JOIN departments
ON employees.department = departments.department
WHERE email IS NOT NULL;

SELECT first_name, email, division, country
FROM employees INNER JOIN departments
ON employees.department = departments.department
INNER JOIN regions ON employees.region_id = regions.region_id
WHERE email IS NOT NULL;

SELECT DISTINCT employees.department, departments.department
FROM employees INNER JOIN departments ON employees.department = departments.department;

SELECT DISTINCT employees.department emplyees_department, 
			departments.department departments_department
FROM employees LEFT JOIN departments ON employees.department = departments.department;

SELECT DISTINCT employees.department emplyees_department, 
			departments.department departments_department
FROM employees RIGHT JOIN departments ON employees.department = departments.department;

SELECT DISTINCT employees.department emplyees_department
FROM employees LEFT JOIN departments ON employees.department = departments.department 
WHERE departments.department IS NULL;

SELECT DISTINCT employees.department emplyees_department,
			departments.department departments_department
FROM employees FULL OUTER JOIN departments ON employees.department = departments.department;

SELECT department 
FROM employees
UNION
SELECT department
FROM departments;

SELECT DISTINCT department 
FROM employees
UNION ALL
SELECT department
FROM departments;

SELECT department 
FROM employees
UNION ALL
SELECT department
FROM departments
ORDER BY department;

SELECT DISTINCT department 
FROM employees
EXCEPT
SELECT department
FROM departments;

SELECT department, COUNT(*)
FROM employees
GROUP BY department
UNION ALL
SELECT 'TOTAL', COUNT(*)
FROM employees;

SELECT COUNT(*) FROM (
	SELECT * 
	FROM employees a, employees b
) sub

SELECT * 
FROM employees a CROSS JOIN departments b

SELECT first_name, department, hire_date, country FROM employees e
INNER JOIN regions r ON e.region_id = r.region_id 
WHERE hire_date = (SELECT min(hire_date) FROM employees e2)
UNION
SELECT first_name, department, hire_date, country FROM employees e
INNER JOIN regions r ON e.region_id = r.region_id 
WHERE hire_date = (SELECT MAX(hire_date) FROM employees e2);

SELECT first_name, department, hire_date, country FROM employees e
INNER JOIN regions r ON e.region_id = r.region_id 
WHERE hire_date = (SELECT min(hire_date) FROM employees e2)
UNION ALL
SELECT first_name, department, hire_date, country FROM employees e
INNER JOIN regions r ON e.region_id = r.region_id 
WHERE hire_date = (SELECT MAX(hire_date) FROM employees e2);

(SELECT first_name, department, hire_date, country FROM employees e
INNER JOIN regions r ON e.region_id = r.region_id 
WHERE hire_date = (SELECT min(hire_date) FROM employees e2 ) LIMIT 1)
UNION 
SELECT first_name, department, hire_date, country FROM employees e
INNER JOIN regions r ON e.region_id = r.region_id 
WHERE hire_date = (SELECT MAX(hire_date) FROM employees e2)
ORDER BY hire_date;

SELECT student_name, se.course_no, p.last_name 
FROM students s 
INNER JOIN student_enrollment se 
    ON s.student_no = se.student_no
INNER JOIN teach t 
    ON se.course_no = t.course_no
INNER JOIN professors p 
    ON t.last_name = p.last_name
ORDER BY student_name;

SELECT student_name, course_no, min(last_name)
FROM (
SELECT student_name, se.course_no, p.last_name 
FROM students s 
INNER JOIN student_enrollment se 
    ON s.student_no = se.student_no
INNER JOIN teach t 
    ON se.course_no = t.course_no
INNER JOIN professors p 
    ON t.last_name = p.last_name
    ) a
    GROUP BY student_name, course_no
    ORDER BY student_name, course_no;
    
SELECT first_name
   FROM employees outer_emp
   WHERE salary > (
     SELECT AVG(salary)
       FROM employees
       WHERE department = outer_emp.department);
       
       
SELECT s.student_no, student_name, course_no
FROM students s LEFT JOIN student_enrollment se
    ON s.student_no = se.student_no;
    
-- Window

SELECT first_name, department, count(*)
FROM employees
GROUP BY department, first_name;

SELECT first_name, department, (SELECT COUNT(*) FROM employees e1 WHERE e1.department = e2.department)
FROM employees e2
GROUP BY department, first_name;

SELECT first_name, department,
COUNT(*) OVER(PARTITION BY department)
FROM employees e2;

SELECT first_name, department,
SUM(salary) OVER(PARTITION BY department)
FROM employees e2;

SELECT first_name, department,
COUNT(*) OVER(PARTITION BY department) dept_count,
region_id,
COUNT(*) OVER(PARTITION BY region_id) region_count
FROM employees e2;

SELECT first_name, department, COUNT(*) over (PARTITION BY department)
FROM employees
WHERE region_id = 3;

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
FROM employees;

SELECT first_name, hire_date, department, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) as running_total_of_salaries
FROM employees;

SELECT first_name, hire_date, department, salary,
SUM(salary) OVER(ORDER BY hire_date ROWS BETWEEN 1000 PRECEDING 
				 AND CURRENT ROW)
				 
SELECT first_name, email, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees;

SELECT * FROM (
SELECT first_name, email, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees
) a
WHERE rank = 8;


SELECT first_name, email, department, salary,
NTILE(5) OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees;

SELECT first_name, email, department, salary,
MAX(salary) OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees
EXCEPT
SELECT first_name, email, department, salary,
first_value(salary) OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees;

SELECT first_name, email, department, salary,
nth_value(salary, 5) OVER(PARTITION BY department ORDER BY first_name) nth_value
FROM employees;

SELECT first_name, last_name, salary, 
LEAD(salary) OVER() next_salary
FROM employees;

SELECT first_name, last_name, salary, 
LEAD(salary) OVER(PARTITION BY department ORDER BY salary DESC) previous_salary
FROM employees;

SELECT continent, country, city, SUM(units_sold)
FROM sales
GROUP BY GROUPING SETS(continent, country, city);

SELECT continent, country, city, SUM(units_sold)
FROM sales
GROUP BY GROUPING SETS(continent, country, city, ());

SELECT continent, country, city, SUM(units_sold)
FROM sales
GROUP BY ROLLUP(continent, country, city);

SELECT continent, country, city, SUM(units_sold)
FROM sales
GROUP BY GROUPING SETS(continent, country, city);

SELECT continent, country, city, SUM(units_sold)
FROM sales
GROUP BY CUBE(continent, country, city);


-- Write a query that finds students who do not take CS180.

SELECT * FROM students
WHERE student_no NOT IN (
    SELECT student_no
    FROM student_enrollment
    WHERE course_no = 'CS180'
    );
    
SELECT s.student_no, s.student_name, s.age
FROM students s LEFT JOIN student_enrollment se 
    ON s.student_no = se.student_no
GROUP BY s.student_no, s.student_name, s.age
HAVING MAX(CASE WHEN se.course_no = 'CS180'
           THEN 1 ELSE 0 END) = 0
           
-- Write a query to find students who take CS110 or CS107 but not both.
SELECT s.*
FROM students s, student_enrollment se
WHERE s.student_no = se.student_no
AND se.course_no IN ('CS110', 'CS107')
AND s.student_no NOT IN ( SELECT a.student_no
                          FROM student_enrollment a, student_enrollment b
                          WHERE a.student_no = b.student_no
                          AND a.course_no = 'CS110'
                          AND b.course_no = 'CS107')
                          
SELECT s.student_no, s.student_name, s.age
FROM students s, student_enrollment se
WHERE s.student_no = se.student_no
GROUP BY s.student_no, s.student_name, s.age
HAVING SUM(CASE WHEN se.course_no IN ('CS110', 'CS107')
           THEN 1 ELSE 0 END ) = 1
           
-- Write a query to find students who take CS220 and no other courses.
SELECT s.*
FROM students s, student_enrollment se
WHERE s.student_no = se.student_no
AND s.student_no NOT IN ( SELECT student_no
                          FROM student_enrollment
                          WHERE course_no != 'CS220')
                          
SELECT s.*
FROM students s, student_enrollment se1,
     (SELECT student_no FROM student_enrollment
      GROUP BY student_no
      HAVING count(*) = 1) se2
WHERE s.student_no = se1.student_no
AND se1.student_no = se2.student_no
AND se1.course_no = 'CS220'

-- Write a query that finds those students who take at most 2 courses. Your query should exclude students that don't take any courses as well as those  that take more than 2 course. 
SELECT s.student_no, s.student_name, s.age
FROM students s, student_enrollment se
WHERE s.student_no = se.student_no
GROUP BY s.student_no, s.student_name, s.age
HAVING COUNT(*) <= 2

-- Write a query to find students who are older than at most two other students.
SELECT s1.*
FROM students s1
WHERE 2 >= (SELECT count(*)
            FROM students s2
            WHERE s2.age < s1.age)