=======================================
tips
=======================================

=======================================
count the number of occurrences of values from any column
---------------------------------------

CREATE TABLE `survey` (
  `id` int(1) UNSIGNED NOT NULL,
  `f1` varchar(16) NOT NULL,
  `f2` varchar(16) NOT NULL,
  `f3` varchar(16) NOT NULL,
  `f4` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CHECKSUM=1 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT;

INSERT INTO `survey`
(`id`, `f1`, `f2`, `f3`, `f4`)
VALUES
(1, 'apple', 'orange', 'pear', 'grape'),
(2, 'grape', 'pear', 'orange', 'apple'),
(3, 'banana', 'lemon', 'cherry', 'strawberry'),
(4, 'pear', 'grape', 'banana', 'lemon'),
(5, 'watermelon', 'apple', 'strawberry', 'cherry');


# desired output

3 apple
3 grape
3 pear
2 banana
2 cherry
2 lemon
2 strawberry
1 orange
1 watermelon

# solution

select count(f) as q, f
from
(
  select f1 as f from survey
    union all
  select f2 from survey
    union all
  select f3 from survey
    union all
  select f4 from survey
) as tmp
group by f
order by q desc, f asc;


=======================================
insert no duplicates
---------------------------------------

INSERT INTO account (
  first,
  last,
  email
)
SELECT * FROM (SELECT david AS first, bowie AS last, david.bowie@gmail.com AS email) AS tmp
WHERE NOT EXISTS (
  SELECT email FROM account WHERE email LIKE david.bowie@gmail.com LIMIT 1
) LIMIT 1


=======================================
names
---------------------------------------

# stay away from using theses reserved keywords when naming anything

https://dev.mysql.com/doc/refman/8.4/en/keywords.html

=======================================