Visualize SQL | Let's use HAVING,CASE,EXISTS

# Beginning
This technique is based on SQL Puzzle and Answers # 20 test results
It is better with 『SQL Puzzle and Answers』 to read this article.

There are four subjects which are 『Reading』『Math』『Chemistry』『Chemistry』
Each subject has different test_steps
『Reading』has two test_steps and『Chemistry』has three test_steps・・・
A completion date will be entered when all test_steps are done

Let's find subjects that all steps are done.

# table and data
```SQL:SQL
CREATE TABLE TestResults(
	test_name CHAR(20) NOT NULL,
	test_step INTEGER NOT NULL,
	comp_date DATE, -- NULL is not complete
	PRIMARY KEY (test_name, test_step)
);

INSERT INTO TestResults VALUES('Reading', 1, '2006-03-10');
INSERT INTO TestResults VALUES('Reading', 2, '2006-03-12');
INSERT INTO TestResults VALUES('Math', 1, NULL);
INSERT INTO TestResults VALUES('Math', 2, '2006-03-12');
INSERT INTO TestResults VALUES('Chemistry', 1, '2006-03-08');
INSERT INTO TestResults VALUES('Chemistry', 2, '2006-03-12');
INSERT INTO TestResults VALUES('Chemistry', 3, '2006-03-15');
INSERT INTO TestResults VALUES('Music', 1, '2006-03-10');
INSERT INTO TestResults VALUES('Music', 2, NULL);
INSERT INTO TestResults VALUES('Music', 3, '2006-03-14');
```

▼ Output

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ffaf9418-9f3e-cf9e-9596-78dd9efbfb47.png)



# Subject that has empty comp_date
```SQL:SQL
-- a1: subject that has empty comp_date
SELECT T1.*,
  (SELECT test_name FROM TestResults AS T2 
   WHERE T1.test_name = T2.test_name AND T2.comp_date IS NULL) AS a1
FROM TestResults AS T1
```

▼ Output


If there is a subject that has empty comp_date in T1 table
taking out its test_name from T2 table
　⇒　this output shows in SELECT statement as a1

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9f408842-76ce-03b0-6225-a0a4d0b27550.png)

# subject that all comp_date are filled
```SQL:SQL
-- subjects that are the opposite of the above results
SELECT T1.*
FROM TestResults AS T1
WHERE NOT EXISTS (
	SELECT * FROM TestResults AS T2
	WHERE T1.test_name = T2.test_name AND T2.comp_date IS NULL
)
```
▼ Output

|test_name|test_step|comp_date|
|---|---|---|
|Reading|1|2006-03-10|
|Reading|2|2006-03-12|
|Chemistry|1|2006-03-08|
|Chemistry|2|2006-03-12|
|Chemistry|3|2006-03-15|

Taking out the subjects that all comp_date are filled
In other words, the subjects **except subjects** that `all comp_date are not filled`

this parts ・・・ **NOT EXISTS()**
try to read like below

picking up test_name from TestResults which **do not include** `『Math』 and 『Music』`
because they have empty comp_date

『Reading』 and 『Chemistry』are left in TestResults AS T1 table


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5a0f436d-92c8-d3b6-257e-1d8173078a37.png)

# Answer SQL(part 1)
```SQL:SQL
SELECT DISTINCT test_name -- there are same subjects, make them unique
FROM TestResults AS T1
WHERE NOT EXISTS (
	SELECT * FROM TestResults AS T2
	WHERE T1.test_name = T2.test_name AND T2.comp_date IS NULL
)
```
▼ Output

|test_name|
|---|
|Reading|
|Chemistry|

# ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ Different Idea ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓



# Use advantage of COUNT function
```SQL:SQL
SELECT T1.*,
	(SELECT array_agg(comp_date) FROM TestResults AS T2 
	 WHERE T1.test_name = T2.test_name),
	 
	(SELECT COUNT(*) FROM TestResults AS T2 
	 WHERE T1.test_name = T2.test_name) AS a2,
	 
	(SELECT COUNT(comp_date) FROM TestResults AS T2 
	 WHERE T1.test_name = T2.test_name) AS a3
	 
FROM TestResults AS T1
```

▼ Output

COUNT(*) and COUNT(field) have different results in some cases

COUNT(*) counts rows include NULL
　⇒　a2

COUNT(field) does not count 『field』 which is NULL
　⇒　a3

Using this advantage, taking out 『Reading』and『Chemistry』


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/589ebd3d-8a46-0693-4352-dca1745b0089.png)


# Check the return value of COUNT()
```SQL:SQL
SELECT test_name,
		COUNT(*),
		COUNT(comp_date)
FROM TestResults
GROUP BY test_name
```
▼ Output
![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d21ed6e5-961f-1134-38a9-db86be6e72e1.png)

If all test_step are done, all comp_date are filled(not NULL)
In this case, output of COUN(*) and output of COUNT(comp_date) are the same.

Howver, all test_step are not done, 
In this case, output of COUN(*) and output of COUNT(comp_date) are not the same
because COUNT(comp_date) does not count NULL

Using this advantage of COUNT function
it is possible to find subject that all test_step are done.

# Answer SQL (part 2)
```SQL:SQL
SELECT test_name
FROM TestResults
GROUP BY test_name
HAVING COUNT(*) = COUNT(comp_date)  --chose subjects that all test_step are done
```
▼ Output

|test_name|
|---|
|Reading|
|Chemistry|

# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)