I have created a two tables and inserted a few data into those tables... but i'm trying to list out all the pilots and the number of types of airplane each is able to fly with the pilot's name and license number. I'm not quite sure how to do it. Can someone help me out?

Table: pilot
person_nbr license_nbr last_name first_name
1001 5033 Hui A
1002 4848 Chan C
1003 3214 Smith B
1004 1423 Wilson D

Table: files
license_nbr plane_type
5033 single engine
4848 dual engine
4848 single engine
1423 single engine
1423 dual engine
4848 turbo props

I tried using
SELECT license_nbr, COUNT(DISTINCT plane_type)
FROM files
GROUP BY license_nbr
the result is what i wanted but i don't know how i can get the pilot's last name and first name in there as well.

Do a join from the pilot to the files table:

SELECT a.licence_nbr, a.last_name, a.first_name, COUNT(DISTINCT b.plane_type)
FROM pilot a LEFT OUTER JOIN files b ON a.licence_nbr = b.licence_nbr
GROUP BY a.licence_nbr

This way you'll also get pilots that have 0 plane types

For some reason there is an error message that poops up saying

SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.plane_type)
2 FROM pilot a, files b
3 WHERE a.license_nbr=b.license_nbr;
SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.plane_type)
*
ERROR at line 1:
ORA-00937: not a single-group group function

it works without the COUNT command.. just with b.plane_type
what should i do??

You need to group by license_nbr

(GROUP BY a.license_nbr) as the last line.

This is what I get when I use GROUP By

SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.plane_type)

FROM pilot a, files b
WHERE a.license_nbr=b.license_nbr
GROUP BY a.license_nbr;
2 3 4 SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.pl
ane_type)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Did I do something wrong?

You're not actually putting a space in DISTINCT are you?

You've also got to group by all fields that are not part of an aggregate function:

Thank you so much~ I finally understand what to do when joining tables. Thank you!