How can I find state name which has the most vehicle accidents in the database?
A join should do, and then select the state name instead. 

SELECT US.STATE_NAME, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
WHERE AD.STATE_CODE = US.STATE_CODE
GROUP BY US.STATE_NAME
ORDER BY COUNT(*) DESC