elizabetherissa

[elizabetherissa]

COALESCE in WHERE

so I got I bit confused COALESCE関数はNULLを別の値に置き換えるだけでなく、複数のカラムを統合（マージ）するような機能も持っている

WHERE (... condition...) FROM -> WHERE -> SELECT SELECT Death_Mission,Name FROM astronauts WHERE COALESCE(Death_Mission,'')<>''; COALESCE(Death_Mission,'') returns , ... table data?

Name | Death_Mission someone1 | "" someone2, | "" someone3, | Apolo1 someone4, | "" ... WHERE maybe COALESCE first argument's column is used in the parent Query

[elizabetherissa]

use COALESCE instead of IS NOT NULL

so I wanted to only extract name, death mission of records that they died of mission... so there is two ways to write it Here is the answer from chatGPT

using IN NOT NULL:

SELECT Death_Mission, Name FROM astronauts WHERE Death_Mission IS NOT NULL

using COALESCE:

SELECT Death_Mission, Name FROM astronauts WHERE COALESCE(Death_Mission, '') <> '';

This code uses the COALESCE function to replace null values with an empty string, and then checks if the resulting value is not an empty string. This has the same effect as checking for IS NOT NULL.

[elizabetherissa]

MAX() can be used for matching

I found something interesting here they used SQL function MAX() to search the record that match with the WHERE condition, and if not, it throws null so that you can use COALESCE(), to replace with "CN" select coalesce(max(country_code), 'XX') from postage_table where country_code = 'JP'; coalesce ---------- JP (1 row) select coalesce(max(country_code), 'XX') from postage_table where country_code = 'US'; coalesce ---------- US (1 row) select coalesce(max(country_code), 'XX') from postage_table where country_code = 'CN'; coalesce ---------- XX (1 row)

[elizabetherissa]

DB fiddle

memo: April 1st astronauts schema SQL : https://gist.githubusercontent.com/pamelafox/b98e03caca7d1ec14394a90ec1512cff/raw/651566107c1c8c9e63a181a7989d9bac2da3697b/astronauts.sql Query SQL:

SELECT gender, AVG(Space_Flight_hr) AS average_space_flight_hr FROM astronauts GROUP BY Gender ;

SELECT gender, AVG(Space_Walks_hr) AS average_space_flight_hr FROM astronauts GROUP BY Gender ;

SELECT GroupNum, COUNT(*) AS group_member_number FROM astronauts GROUP BY GroupNum;

SELECT Graduate_Major, AVG(Space_Flight_hr) AS average_space_flight_hr FROM astronauts GROUP BY Graduate_Major HAVING AVG(Space_Flight_hr) < 500 ORDER BY AVG(Space_Flight_hr) ASC;

SELECT Graduate_Major, AVG(Space_Flight_hr) AS average_space_flight_hr FROM astronauts GROUP BY Graduate_Major HAVING AVG(Space_Flight_hr) > 2500 ORDER BY AVG(Space_Flight_hr) DESC;

SELECT AVG(Space_Walks_hr) AS average_space_walk_hour FROM astronauts;

SELECT Military_Branch, AVG(Space_Walks_hr) AS average_space_walk_hour FROM astronauts GROUP BY Military_Branch HAVING AVG(Space_Walks_hr) > 8 ORDER BY AVG(Space_Walks_hr) DESC;

SELECT Graduate_Major, AVG(Space_Walks_hr) AS average_space_walk_hour FROM astronauts GROUP BY Graduate_Major HAVING AVG(Space_Walks_hr) > 8 ORDER BY AVG(Space_Walks_hr) DESC;

SELECT Death_Mission,Name FROM astronauts WHERE COALESCE(Death_Mission,'')<>''; SELECT Death_Mission, Name FROM astronauts WHERE Death_Mission IS NOT NULL;