SQL Basics: Simple NULL handling

For this challenge you need to create a SELECT statement, this select statement must have NULL handling, using COALESCE and NULLIF.

If no name is specified you must replace with [product name not found]

If no card_name is specified you must replace with [card name not found]

If no price is specified you must throw away the record, you must also filter the dataset by prices greater than 50.

eusales table schema

• id
• name
• price
• card_name
• card_number
• transaction_date

resultant table schema

• id
• name
• price (greater than 50.00)
• card_name
• card_number
• transaction_date

 

Solution:

SELECT
  id,
  COALESCE(NULLIF(name,''), '[product name not found]') AS name,
  price,
  COALESCE(NULLIF(card_name,''), '[card name not found]') AS card_name,
  card_number,
  transaction_date
FROM 
  eusales
WHERE
  price > 50.00

 

Result:

id	name	price	card_name	card_number	transaction_date
1	[product name not found]	93.45	Savion Mayert	1211-1221-1234-2201	2019-08-09
2	Durable Bronze Chair	67.98	Noemi Toy III	1212-1221-1121-1234	2020-04-26
3	Intelligent Copper Hat	95.64	Trever Watsica	1234-2121-1221-1211	2015-04-04
6	Intelligent Iron Lamp	82.89	Jordi Lind	1211-1221-1234-2201	2014-04-27
9	Rustic Bronze Shoes	58.69	Cary Dietrich	1211-1221-1234-2201	2015-09-10
14	Gorgeous Iron Bottle	62.99	Ms. Reed Wilderman	1211-1221-1234-2201	2010-08-18
16	Ergonomic Bronze Gloves	72.64	Monique Grady	1228-1221-1221-1431	2019-01-06
21	Synergistic Concrete Bench	86.28	Alana Kunze V	1212-1221-1121-1234	2013-07-22
22	[product name not found]	69.79	Adrain Rolfson I	1212-1221-1121-1234	2010-02-07
23	Intelligent Silk Chair	72.33	Crystel Koepp	1212-1221-1121-1234	2022-01-10
25	Gorgeous Paper Plate	63.22	Mrs. Furman Carroll	1228-1221-1221-1431	2019-12-12
28	Practical Plastic Shirt	61.52	Katelin Langosh	1211-1221-1234-2201	2017-08-11
30	Awesome Plastic Bottle	79.05	Brant Schultz	1234-2121-1221-1211	2011-08-31
33	Enormous Granite Computer	54.1	Elinore Bednar	1212-1221-1121-1234	2013-04-10

 

 

Reference:

[PostgreSQL] COALESCE, NULLIF, SIGN, IS DISTINCT FROM