🤠 Group by, Having, Join, View, Sub query, Scala sub query, Case [SQL]

Tables in database (mysql)

• member table

• job table

💎 Group By

I need to know how many members belong to each job_id

SELECT job_id, count(*) FROM members GROUP BY job_id;

By the way, You can't use "as" to column which is used by GROUP BY

-- Error !!
SELECT job_id as 'This is job id', count(*) FROM members GROUP BY job_id;

🙄 Why? Because SELECT SQL order is like that

FROM
↓
GROUP BY
(I don't know what is 'This is job id', nobody defined it yet)
↓
SELECT
(Here define job_id "This is job id")

▼ I wrote article about SELECT ORDER

🤹‍♀️ SELECT ORDER [SQL]

Kaziu ・ Apr 3 '22

#sql #mysql #database #beginners

💎 Having

We created this record by GROUP BY

now We need to know job_id which has two members

SELECT job_id, COUNT(*) FROM members GROUP BY job_id HAVING COUNT(*) = 2;

🤩 Tadaaam

↑ But it is a little hard to see right? Because there is no "job name"

💎 Join

If you know job name as well, you need to integrate job table

SELECT job_id, jobs.name, COUNT(*) FROM members INNER JOIN jobs ON jobs.id=members.job_id GROUP BY job_id HAVING COUNT(*) = 2;

(We will use new table from here)

• products table

💎 View

Save SELECT SQL which we often use and we can use the same as table, like virtual table.

for example, maybe you would often search number of products of category group

SELECT category, COUNT(*) FROM Products GROUP BY category;

😭 But all the time you have to write in down, it's not weird if your fingers burn out.

in like this case, you should use VIEW!!

CREATE VIEW ProductSum (category, count_product)
AS
SELECT category, COUNT(*) FROM Products GROUP BY category;

How should you get record? it's so simple

SELECT category, count_product FROM ProductSum;

📕 Difference between View and Table

• reduce data because view has just SELECT SQL
• flexible for changing database content

💎 Sub query

Single use version of view

SELECT category, count_product
FROM (SELECT category, COUNT(*) AS count_product
       FROM Products GROUP BY category)
AS ProductSum;

💎 Scala sub query

📕 What is scala?

single value like char,int,float.
Not like Array,Object

📕 For example

SELECT name, selling_price FROM Products
WHERE selling_price > (SELECT AVG(selling_price) FROM Products);

SELECT AVG(selling_price) FROM Products = 2678.75, so it is scala value.

💎 Case

We know sale product's price informations now.

▼ conditions

SELECT name,
  CASE WHEN price_on_sale >= 5000 THEN price_on_sale * 0.8
       WHEN price_on_sale >= 1000 THEN price_on_sale * 0.9
       ELSE price_on_sale
  END AS "sales price"
FROM Products;

▼ result

😎 Thank you for reading