ORACLE SQL Need help on changing the code to display only the states listed belo

Question

ORACLE SQL

Need help on changing the code to display only the states listed below.

What is now wanted is a plot for California, New York, Florida, and Illinois only. Change the query above to select only those states (STATE field in the where clause) and create the drawing.

SELECT zc.zipcode, longitude, latitude, numords,
(CASE WHEN hh = 0 THEN 0.0 ELSE numords*1.0/hh END) as penetration
FROM dw.zipcensus zc JOIN
(SELECT zipcode, COUNT(*) as numords
FROM dw.orders
GROUP BY zipcode) o
ON zc.zipcode = o.zipcode
WHERE latitude BETWEEN 20 and 50 AND
longitude BETWEEN -135 AND -65

Explanation / Answer

SELECT zc.zipcode, longitude, latitude, numords,
(CASE WHEN hh = 0 THEN 0.0 ELSE numords*1.0/hh END) as penetration
FROM dw.zipcensus zc JOIN
(SELECT zipcode, COUNT(*) as numords
FROM dw.orders
WHERE UPPER(state) IN ('CA', 'NY', 'FL', 'IL')
GROUP BY zipcode) o
ON zc.zipcode = o.zipcode
WHERE latitude BETWEEN 20 and 50 AND
longitude BETWEEN -135 AND -65;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.