Notes

Write a note in this area. It's really easy to sh#1. (Union) Write a query to find a list of grocery items that you can buy at
Croger or A-Town International. (0.25 pt)
select itemname from croger union select itemname from atown;
#2. (Intersect) Write a query to find a list of grocery items that you can buy at
both Croger and A-Town International. (0.25 pt)
select itemname from croger inner join atown using (itemname);
#3. (Minus) Write a query to find a list of grocery items that you can buy only at
A-Town International. (0.25 pt)
select a.itemname from atown a left join croger b using (itemname) where b.itemname
is null;
#4. (CASE) Among five category values in the Croger table, we are particularly
interested in dairy and vegetables. Write a query to show the two values as they
are but convert the rest of the values into Other. (0.25 pt)
select *, case when categoryname = 'dairy' then 'diary' when categoryname =
'vegetables' then 'vegetables' else 'other' end as categoryname from croger;
#5. (CASE) Write a query to convert the month numbers in the monthnum column to
corresponding month names. (0.5 pt)
select city, aqi, case
when monthnum = 1 then 'January'
when monthnum = 2 then 'February'
when monthnum = 3 then 'March'
when monthnum = 4 then 'April'
when monthnum = 5 then 'May'
when monthnum = 6 then 'June'
when monthnum = 7 then 'July'
when monthnum = 8 then 'August'
when monthnum = 9 then 'September'
when monthnum = 10 then 'October'
when monthnum = 11 then 'November'
when monthnum = 12 then 'December'
end as monthnanme from airpollution;
#6. (CASE) Write a query to assign air quality scales based on the conversion chart
below. (0.5 pt)
select city, monthnum, case
when aqi >=0 and aqi <= 50 then 'Good'
when aqi >= 51 and aqi <= 100 then 'Moderate'
when aqi >= 101 and aqi <= 150 then 'Unhealthy for Sensitive Groups'
when aqi >= 151 and aqi <= 200 then 'Unhealthy'
when aqi >= 201 and aqi <= 300 then 'Very Unhealthy'
when aqi >= 300 and aqi <= 500 then 'Hazardous'
end as aqiscale from airpollution;
#7. (CASE) Write a query to assign letter grades in the studentgrade table based on
the grading system below. Please note that grades are not rounded. For example,
92.9% is not A but A-. (0.5 pt)
select *, case
when gradepercentage >= 97 then 'A+'
when gradepercentage >= 93 and gradepercentage < 97 then 'A'
when gradepercentage >= 90 and gradepercentage < 93 then 'A-'
when gradepercentage >= 87 and gradepercentage < 90 then 'B+'
end as lettergrade from studentgrade;are with others. Click here ...