ข้อสอบปรนัย ฐานข้อมูลและ SQL ฟรี พร้อมคำใบ้และคำอธิบาย สามระดับ: ศัพท์สำคัญและข้อเท็จจริง, การประยุกต์ใช้แนวคิด, กรณีศึกษาและการวิเคราะห์
ข้อจำกัด (constraint) ใดรับรองว่าคอลัมน์ไม่มีทางเว้นว่าง (NULL)?
เก่งมาก อยากทำข้อต่อไปไหมพร้อมสำหรับข้อต่อไปหรือยัง? โจทย์ใหม่ 5 ข้อทุกวัน ในทุกหัวข้อ
ฐานข้อมูลเชิงสัมพันธ์ (relational database) เก็บข้อมูลในตาราง ตารางเหมือนสเปรดชีต: แต่ละแถวคือหนึ่งระเบียน แต่ละคอลัมน์คือหนึ่งคุณสมบัติ
ร้านค้าอาจมีตาราง customers ที่มีคอลัมน์ id, name และ city และตาราง orders ที่มีคอลัมน์ id, customer_id และ total
คีย์หลัก (primary key) คือคอลัมน์หรือชุดคอลัมน์ที่ระบุแต่ละแถวได้อย่างไม่ซ้ำ ห้ามซ้ำและห้ามว่าง ในที่นี้คือ id ของแต่ละตาราง
คีย์นอก (foreign key) คือคอลัมน์ที่อ้างถึงคีย์หลักของตารางอื่น ใน orders customer_id บอกว่าลูกค้าคนใดสั่งซื้อ
นี่คือวิธีเชื่อมตาราง: ลูกค้าหนึ่งคนมีคำสั่งซื้อได้หลายรายการ แต่แต่ละคำสั่งซื้อเป็นของลูกค้าหนึ่งคน นั่นคือความสัมพันธ์หนึ่งต่อกลุ่ม (one-to-many)
SQL (Structured Query Language) คือภาษาที่ใช้สร้างตาราง เปลี่ยนข้อมูล และถามคำถามเกี่ยวกับข้อมูล
คำตอบ: คอลัมน์หรือชุดคอลัมน์ที่ระบุแต่ละแถวได้อย่างไม่ซ้ำ. คีย์หลักไม่ซ้ำและไม่เป็น NULL จึงเลือกได้เพียงแถวเดียวพอดี คอลัมน์ที่อ้างถึงตารางอื่นคือคีย์นอก
คำถามส่วนใหญ่เกี่ยวกับคำสั่ง SELECT ประกอบด้วยส่วนคำสั่ง (clause) ที่เขียนตามลำดับนี้เสมอ:
ฟังก์ชันรวม (aggregate function) สรุปหลายแถวเป็นค่าเดียว: COUNT, SUM, AVG, MIN และ MAX
ฐานข้อมูลไม่ทำงานตามลำดับที่คุณเขียน มันทำ FROM ก่อน แล้ว WHERE, GROUP BY, HAVING, SELECT และสุดท้าย ORDER BY
คำสั่งอื่นเปลี่ยนข้อมูล: INSERT เพิ่มแถว UPDATE เปลี่ยนแถว และ DELETE ลบแถว CREATE, ALTER และ DROP เปลี่ยนตารางเอง
คำตอบ: HAVING. ค่านับมีเมื่อแถวถูกจัดกลุ่มแล้วเท่านั้น และ HAVING กรองกลุ่ม WHERE ทำก่อนการจัดกลุ่ม จึงใช้ฟังก์ชันรวมอย่าง COUNT ไม่ได้
การรวมตาราง (join) รวมแถวของสองตารางโดยใช้เงื่อนไข ปกติคือคีย์นอก = คีย์หลัก:
นอร์มัลไลเซชัน (normalisation) แยกตารางเพื่อให้ทุกข้อเท็จจริงถูกเก็บครั้งเดียว ช่วยหลีกเลี่ยงการแก้ข้อเท็จจริงเดียวกันในหลายที่
ทรานแซกชัน (transaction) จัดกลุ่มคำสั่งเป็นหน่วยเดียว COMMIT ทำให้ถาวรและ ROLLBACK ยกเลิก ทรานแซกชันเป็นไปตาม ACID: atomicity (ทำทั้งหมดหรือไม่ทำเลย) consistency isolation และ durability
คำตอบ: ลูกค้าปรากฏหนึ่งครั้ง โดยคอลัมน์คำสั่งซื้อเป็น NULL. LEFT JOIN เก็บทุกแถวของตารางซ้ายและเติมด้านขวาที่ขาดด้วย NULL การตัดลูกค้าออกคือสิ่งที่ INNER JOIN ทำ
เพื่อเขียนคิวรี หรือหาว่าคิวรีที่ให้มาคืนค่าอะไร:
สำหรับคำถามทฤษฎี ให้ระบุแนวคิดก่อน (คีย์ ชนิดการรวมตาราง รูปนอร์มัล คุณสมบัติ ACID) แล้วเลือกตัวเลือกที่ตรงกับนิยามของมัน
ทำตามทุกขั้นตอน ลองทำนายขั้นถัดไปก่อนเปิดดู
ตาราง customers มีแถว (1, Ana), (2, Ben) และ (3, Cas) ตาราง orders มีแถว (id, customer_id) = (10, 1), (11, 1), (12, 2) และ (13, 1) คิวรีนี้คืนค่าอะไร SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name
ลูกค้าแต่ละคนถูกจับคู่กับคำสั่งซื้อที่มี id ของเขา Ana ตรงกับคำสั่งซื้อ 10, 11 และ 13 Ben ตรงกับคำสั่งซื้อ 12
Cas ไม่มีคำสั่งซื้อ เพราะเป็น LEFT JOIN Cas จึงคงอยู่ในผลลัพธ์ โดย o.id เป็น NULL
นั่นคือ 5 แถว
GROUP BY c.name สร้างสามกลุ่ม: Ana 3 แถว Ben 1 แถว และ Cas 1 แถว
COUNT(o.id) นับเฉพาะค่าที่ไม่ใช่ NULL Ana ได้ 3 และ Ben ได้ 1
Cas มีหนึ่งแถว แต่ o.id เป็น NULL ดังนั้น Cas ได้ 0
Ana, Ben และ Cas รวมกันมี 3 + 1 + 0 = 4 คำสั่งซื้อ เท่ากับจำนวนแถวใน orders ถ้าใช้ COUNT(*) แทน Cas จะได้ 1 อย่างผิด ๆ เพราะ COUNT(*) นับตัวแถวเอง
Ana 3, Ben 1, Cas 0
คำตอบ: 0. COUNT(col) นับเฉพาะค่าที่ไม่ใช่ NULL จึงคืน 0 COUNT(*) นับแถวและจะคืน 10
เมื่อให้คิวรี ให้ไล่บนไม่กี่แถวตามลำดับ FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY กับดักส่วนใหญ่ปรากฏภายในสามแถว
UNION ทำอะไรกับผลลัพธ์ของคิวรี SELECT สองคิวรี?
UNION ซ้อนแถวของทั้งสองคิวรีและลบแถวซ้ำ ส่วน UNION ALL เก็บแถวซ้ำไว้
SELECT SUM(amount) FROM orders คืนอะไรเมื่อ WHERE ไม่ตรงกับแถวใดเลย?
SUM, AVG, MIN และ MAX คืน NULL เมื่อเซตว่าง ส่วน COUNT คืน 0 COALESCE(SUM(amount), 0) ให้ 0 แทน
SELECT name, dept, AVG(salary) OVER (PARTITION BY dept) FROM emp คืนอะไร?
ต่างจาก GROUP BY ฟังก์ชันหน้าต่างคงทุกแถวนำเข้าและเพิ่มค่าที่คำนวณบนพาร์ทิชันของมัน ในที่นี้คือแผนก
ร้านค้าอาจมีตาราง customers ที่มีคอลัมน์ id, name และ city และตาราง orders ที่มีคอลัมน์ id, customer_id และ total
นี่คือวิธีเชื่อมตาราง: ลูกค้าหนึ่งคนมีคำสั่งซื้อได้หลายรายการ แต่แต่ละคำสั่งซื้อเป็นของลูกค้าหนึ่งคน นั่นคือความสัมพันธ์หนึ่งต่อกลุ่ม (one-to-many)
ทุกคำถามมีสี่ตัวเลือก และถูกเพียงข้อเดียว ติดขัดใช่ไหม เปิดคำใบ้เล็ก ๆ ก่อน และเปิดคำอธิบายฉบับเต็มเมื่อจำเป็นเท่านั้น