SQL

Table of content

• To make a database and table
• Odd/even numbers
• Group by
  • Group by two columns
• Sub table
• Sort
• Window functions
  • Select top n values
  • Lag
• Change values

This post includes some commonly used code snippets.

To make a database and table

create database candidates;
use candidates;
create table candidateColleges(
    college_id int PRIMARY KEY, 
    candidate_name varchar(100));
desc candidateColleges;
INSERT INTO candidateColleges (college_id, candidate_name)
VALUES
    (123456, 'john_smith'),
    (123456, 'sarah_daniels'),
    (123457, 'tim_cook'),
    (123457, 'lisa_perelli'),
    (123457, 'jenny_west'),
    (123457, 'karl_tran'),
    (123457, 'tammy_turner');

CREATE TABLE sales (
	sale_id INT,
    product_id INT,
    store_id INT,
    date DATETIME
);
INSERT INTO sales (sale_id, product_id, store_id, date)
VALUES
	(1, 31331, 91110, '2020-2-20'),
    (1, 31331, 91110, '2020-2-20'),
    (2, 34611, 57507, '2020-2-20'),
	(3, 26583, 37340, '2020-2-20'),
	(3, 34611, 32016, '2020-2-20'),
	(3, 20267, 99525, '2020-2-21'),
	(4, 31331, 99525, '2020-2-21'),
	(5, 49760, 99525, '2020-2-21'),
	(6, 34611, 57507, '2020-2-21'),
	(7, 31331, 91110, '2020-2-21');

Odd/even numbers

The code for dealing with odd/even numbers in SQL is below. The code comes from here

SELECT * 
FROM table_name 
WHERE mod(column_name,2) = 0;

Group by

Group by two columns

SELECT creator_id
FROM all_donations
GROUP BY creator_id, viewer_id

Sub table

SELECT creator_id
FROM (
    SELECT creator_id, SUM(donation_amount)
    FROM all_donations
) AS s
...

Sort

ORDER BY

Window functions

The window function is a more advanced technique in SQL. Here is a good resource for learning about the window function. The important commands such as order and partition define what is referred to as the “window”.

Select top n values

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Select the second largest from each group

SELECT *
FROM animal_weights AS a
LEFT JOIN zoo_directory z ON a.zoo_ID = z.zoo_ID
WHERE
(SELECT COUNT(*) FROM animal_weights AS b
WHERE b.zoo_ID = a.zoo_ID AND b.weight_lbs > a.weight_lbs) = 1;

Lag

SELECT employee_ID, 
	salary, 
	year,
	LAG(salary, 1) OVER
		(PARTITION BY employee_ID ORDER BY year) AS lag_salary
FROM employee_salaries

Change values

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;