Basic SQL Part 6 - ตะลุยโจทย์ประธานาธิบดี

Programming, SQL

ตะลุยโจทย์ประธานาธิบดี

เต้นรำพื้นบ้านที่ญี่ปุ่นครั้งแรก โรงแรมบอกว่าตอนเย็นมีเต้นรำ เราก็นึกว่าจะมีสาวญี่ปุ่นมาเต้นเราก็เลยไป สรุปไปมีแต่เราคนเดียวที่ไป เลยได้ท่าเต้นญี่ปุ่นตลกๆกลับมา

ผมเขียนเกี่ยวกับ SQL ไว้ 6 ตอน คุณสามารถกด Link ด้านล่างเพื่ออ่านที่เกี่ยวกับ SQL ตอนต่างๆได้เลย

สำหรับตอนนี้เรามาลุยโจทย์ที่ผมเล่าให้ฟังเมื่อตอนที่แล้วกัน แต่ก่อนตะลุยโจทย์ก็ขอให้ทำการ Download sql แล้ว copy คำสั่งไปใส่ในเว็บ sqliteonline.com ก่อนเพื่อให้มีข้อมูลในการทำโจทย์ครับ แล้วก็ลองทำความเข้าใจแต่ละ Table โดยผมอธิบายว่าแต่ละ Table คือ Table ที่เกี่ยวข้องกับอะไร แต่ไม่ได้ลงว่ามี Column อะไรบ้าง ลองไป SELECT * ดูข้อมูลเล่นๆกันก่อนครับ พออ่านโจทย์แล้วจะได้มีไอเดีย

  • ADMINISTRATION

    เก็บข้อมูลการขึ้นมาดำรงตำแหน่งประธานาธิบดีว่าปีนี้ใครขึ้นมาเป็นประธานาธิบดีโดยเขาเลือกตั้งทุก 4 ปี (ถ้าไม่เกิดกรณีไม่ปกติ) เขาเก็บเป็นครั้งๆเลยว่าครั้งนี้ใครเป็น

  • ADMIN_PR_VP

    เก็บข้อมูลเกี่ยวกับว่าในสมัยนั้นใครเป็นประธานาธิบดี และ ใครเป็นรองประธานาธิบดี

  • ELECTION

    เก็บข้อมูลเกี่ยวกับการเลือกตั้งแต่ละครั้งว่ามีใครเข้าร่วมบ้าง ผลการโหวตเป็นอย่างไร ใครชนะด้วยคะแนนโหวตเท่าไหร่

  • PRESIDENT

    เก็บข้อมูลประธานาธิบดีท่านนั้นว่ามีอายุเท่าไหร่ อยู่พรรคไหน เกิดที่รัฐไหน

  • PRES_HOBBY

    เก็บข้อมูลว่าประธานาธิบดีท่านนั้นมีงานอดิเรกอะไรบ้าง

  • PRES_MARRIAGE

    เก็บข้อมูลว่าประธานาธิบดีแต่งงานกับใครบ้าง ตอนอายุเท่าไหร่ มีลูกกี่คน

  • STATE

    เก็บข้อมูลรัฐของสหรัฐว่าเข้ามาตอนปีที่เท่าไหร่ เข้ามาอันดับที่เท่าไหร่

1. แสดงอายุของภรรยาประธานาธิบดีที่ขณะแต่งงานมีอายุน้อยที่สุด และมีอายุเท่าไหร่

อันนี้ไม่ยากเท่าไหร่ ง่ายจัดๆเลย

1
2
SELECT MIN(SP_AGE)
FROM PRES_MARRIAGE

2. แสดงจํานวนของประธานาธิบดีที่ขณะแต่งงานมีอายุมากกว่าภรรยา 2 ปี

อันนี้อาจจะยากนิดนึงเพราะไม่เคยบอกว่ามันทำได้ แต่ถ้าลองเล่นกับการ + - * / ใน where ผมว่าทำได้ ซึ่งคำตอบก็คือ

1
2
3
4
5
6
7
8
SELECT COUNT(*)
FROM PRES_MARRIAGE
WHERE PR_AGE - SP_AGE = 2 

-- อันนี้ถ้าอยากรู้ว่าใคร
SELECT *
FROM PRES_MARRIAGE
WHERE PR_AGE - SP_AGE = 2

3. หลังจากปี 1870 การเลือกตั้งครั้งใดที่มีผู้สมัครรับเลือกตั้งมากกว่า 2 คน

อันนี้ไม่ยากเลย ใช้ WHERE รวมกับ GROUP , HAVING 

1
2
3
4
5
SELECT ELECTION_YEAR
FROM ELECTION
WHERE ELECTION_YEAR > 1870
GROUP BY ELECTION_YEAR
HAVING COUNT(*) > 2

4. แสดงรายชื่อของประธานาธิบดี จํานวนที่ดํารงตําแหน่ง สําหรับประธานาธิบดีที่ดํารงตําแหน่งนานที่สุด

อันนี้เริ่มซับซ้อนขึ้นมาละ แต่ไม่ยากเลย ก็แค่หาว่าการดำรงตำแหน่งนานที่สุดคือเท่าไหร่ แล้ว WHERE ด้วยค่านั้น ซึ่งเราเคยทำคล้ายๆแบบนี้ไปแล้ว ซึ่งนั่นคือการใช้ Sub query นั่นเอง (ส่วนใครจะใช้ ORDER BY ก็ได้นะครับ)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- ใช้ Sub query
SELECT * 
FROM PRESIDENT 
WHERE YRS_SERV = (
  SELECT MAX(YRS_SERV)
  FROM PRESIDENT
)

-- ใช้ Order by แล้วใช้ Limit ต่อเอา แต่ Limit ไม่ใช่
-- SQL Standard เลยไม่เขียนนะครับ ใครอยากรู้ว่าเขียนยังไงไป
-- ลอง Search ดูได้
SELECT * 
FROM PRESIDENT
ORDER BY YRS_SERV DESC

5. แสดงรายชื่อประธานาธิบดีที่แต่งงานอย่างน้อย 2 ครั้งและในการแต่งงานแต่ละครั้งมีบุตรอย่างน้อย 2 คน

อันนี้ก็ไม่ยากเลยใช้แค่ GROUP BY HAVINE เอาอยู่เลย

1
2
3
4
5
SELECT PRES_NAME
FROM PRES_MARRIAGE
WHERE NR_CHILDREN >= 2
GROUP BY PRES_NAME
HAVING COUNT(*) >= 2

6. แสดงรายชื่อประธานาธิบดีที่ดํารงตําแหน่ง (สมัย) มากกว่าจํานวนครั้งการแต่งงาน

อันนี้เริ่ม Advance ขึ้นมาหน่อย แต่ไม่ยากครับ Sub query ช่วยเราได้

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM PRESIDENT p1
WHERE  
(
    SELECT COUNT(*)
    FROM ADMINISTRATION a1
    WHERE a1.PRES_NAME = p1.PRES_NAME
) > 
(
    SELECT COUNT(*)
    FROM PRES_MARRIAGE p2
    WHERE p2.PRES_NAME = p1.PRES_NAME
)

7. แสดงรายละเอียดของตาราง President สําหรับประธานาธิบดีที่เข้ารับตําแหน่งเป็นคนแรก หลังจากปีที่ประธานาธิบดี Reagan แต่งงานครั้งแรก

มาอันนี้ Advance หน่อย ซึ่งก็ดีเราจะได้เร้าใจกันหน่อย โจทย์ข้อนี้ผมก็อึ้งเหมือนกันว่าจะทำยังไง แต่ผมว่ามันทำได้แหละครับ อย่างแรกต้องหาก่อนว่าท่าน Regan เนี่ยแต่งงานตอนไหน แล้วไปหาว่าการเลือกตั้งหลังจากนั้นใครได้เป็นประธานาธิบดี แค่นี้เราก็จะได้คำตอบแล้ว ซึ่งวิธีของผมคือแบบนี้

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT PRESIDENT.*  -- Query นี้ก็ JOIN กันระหว่าง ADMINISTRATION กับ PRESIDENT แล้วเพื่อเอา
FROM ADMINISTRATION -- YEAR_INAUGURATED ไปใช้ WHERE และเอาข้อมูลจาก PRESIDENT มาแสดง
INNER JOIN PRESIDENT
ON ADMINISTRATION.PRES_NAME = PRESIDENT.PRES_NAME
WHERE YEAR_INAUGURATED = ( 	
    SELECT MIN(YEAR_INAUGURATED)  -- Query นี้หาปีที่น้อยที่สุดที่สุดที่ประธานาธิบดีเข้ารับตำแหน่ง
	FROM ADMINISTRATION 
	WHERE YEAR_INAUGURATED > (
  		SELECT MIN(MAR_YEAR)            -------- Query นี้หาปีการแต่งงานครั้งแรกของ Reagan R
		FROM PRES_MARRIAGE 
		WHERE PRES_NAME = 'Reagan R' 
	)
 )

8. แสดงรายชื่อประธานาธิบดีที่ไม่เคยชนะและไม่เคยแพ้การเลือกตั้ง

เฮ้ยโจทย์ข้อนี้น่าสนใจ ไม่เคยแพ้ ไม่เคยชนะ แต่มาเป็นประธานาธิบดี เออเว้ย ลองทำดูหน่อย ก็คิดง่ายๆว่าถ้าไม่เคยชนะและไม่เคยแพ้ แสดงว่าต้องไม่มีข้อมูลใน Table : ELECTION แน่นอน งั้นก็ใช้ Sub query จัดเลยดิ

1
2
3
4
5
SELECT *
FROM PRESIDENT
WHERE PRESIDENT.PRES_NAME NOT IN (
	SELECT DISTINCT CANDIDATE FROM ELECTION 
)

9. แสดงชื่อรัฐ สมัยการปกครอง ปีที่เข้าร่วมเป็นรัฐหนึ่งในประเทศสหรัฐอเมริกา สําหรับรัฐที่เข้ามาในสมัยการปกครองเดียวกันแต่ต่างปีที่เข้าร่วม

เจอข้อนี้ไปผมก็ช็อกเลย ทำยังไงดีวะ แต่คร่าวๆน่าจะใช้ sub query แล้วแหละ แล้วก็ถ้าเข้ามาปีเดียวกันมันต้องมีค่าเฉลี่ยนใน Group นั้นเท่ากันใช่มะ อ้าวงี้ก็สวยดิ ผมคิดออกละ คุณล่ะครับคิดออกยัง

1
2
3
4
5
6
7
SELECT *
FROM STATE s1
WHERE s1.YEAR_ENTERED != (
    SELECT AVG(s2.YEAR_ENTERED)
    FROM STATE s2
    WHERE s1.ADMIN_ENTERED IS s2.ADMIN_ENTERED
)

10. แสดงชื่อและปีเกิดของประธานาธิบดีผู้ที่มีจํานวนครั้งการแพ้และชนะเลือกตั้งเท่ากัน

ข้อนี้เป็นข้อสุดท้ายละครับที่จะตะลุยโจทย์กัน ซึ่งผมก็ติด Stun นานอยู่เหมือนกัน แต่จริงๆไม่ยากเท่าไหร่เลย ใช้ Correlated subquery หาก็จบนี่ 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT p1.PRES_NAME , p1.BIRTH_YR
FROM PRESIDENT p1
WHERE (
    SELECT COUNT(*) -- หาจำนวนชนะ
    FROM ELECTION e1
    WHERE e1.CANDIDATE = p1.PRES_NAME AND WINNER_LOSER_INDIC = 'W'
) = (
    SELECT COUNT(*) -- หาจำนวนแพ้
    FROM ELECTION e2
    WHERE e2.CANDIDATE = p1.PRES_NAME AND WINNER_LOSER_INDIC = 'L'
) 
AND p1.PRES_NAME IN ( -- อันนี้บอกว่าต้องเคยลงแข่งแล้วแพ้หรือชนะ เพราะมีบางคนไม่ได้ลงแข่งก็เข้ามาเป็นประธานาธิบดี
    SELECT DISTINCT CANDIDATE
    FROM ELECTION
)

จบละจริงๆไม่มีตอนต่อไปเกี่ยวกับเรื่องนี้แล้ว

จริงๆมีโจทย์อีกหลายข้อเลยโดยสามารถไป Download (ตัวโจทย์นี้ผมไปเจอมาใน Internet มันเป็นภาษาไทย ผมเลยคิดว่ามันน่าจะง่ายกว่าเอาโจทย์จากสมุดที่อาจารย์ผมให้มามาโชว์) มาลองทำได้ รับรองว่าด้วยความรู้จาก 5 ตอนที่อธิบายไปสามารถเอาไปทำโจทย์ได้ทุกข้อแน่นอน

เพลงประกอบการเขียน Code

ช่วงนี้กระแส 2021 ราตรีมันดังมาก เรื่องการ Remake หรือเรียกว่า Cover ดี พอนึกถึงเพลงที่เอามาทำใหม่แล้วประทับใจคือเพลง “ค่อยค่อยพูด” เวอร์ชั่นพี่อี๊ด ที่มาเล่นในรายการตำนานหมู่สู้ฟัด ซึ่งพี่อี๊ดแกเอามาเพลงมาทำทำนองใหม่ เปลี่ยนอารมณ์เพลงใหม่หมด เล่นเอาเป็นเพลงสบายๆไปซะงั้นเลย แล้วมารู้ทีหลังด้วยว่าแกทำคนเดียว

เวอร์ชันต้นฉบับของ Y NOT 7