You have two lists of values to correlate.
Which query lists all names in colors1 and how many total matches are there in colors2?
A.
SELECT colors1 .name.count (colors2.name)
FROM colors1. Colors2
WHERE
Colors1. Name = (SELECT DISTINCT name FROM colors2 WHERE
colors1.name=colors2.name)
GROUP BY colorse1.name,
B.
SELECT colors1.name, count(colorse2. Name)
FROM colorse1 .name =colors2.name
WHERE colors1. Name =colors2.name
GROUP BY colors1.name,
C.
SELECT colors1. Name count (colors2.name)
FROM colors1
INNER JOIN colors2
on colors1. Name =colors2. Name
GROUP BY colors1 .name;
D.
SELECT colors1.name, count (colors2.name)
FROM JOIN colors2
on colors1 .name =colors2.name
GROUP BY colors1.name;
Correct answer is indeed D but there’s a “LEFT” keyword for join missing … Just a typo !
Correct query is following:
SELECT colors1.name, count(colors2.name)
FROM colors1 left JOIN colors2
on colors1.name =colors2.name
GROUP BY colors1.name
It looks like D
D
E is correct, Tested!!!
mysql> SELECT color1.name, count(color2.name)
-> FROM color2
-> RIGHT JOIN color1
-> on color1.name =color2.name
-> GROUP BY color1.name;
+——–+——————–+
| name | count(color2.name) |
+——–+——————–+
| blue | 2 |
| gold | 0 |
| green | 2 |
| red | 1 |
| silver | 0 |
+——–+——————–+
5 rows in set (0.00 sec)