9.SELECT id
FROM flights AS f
WHERE distance < (
SELECT AVG(distance)
FROM flights
WHERE carrier = f.carrier);
10.SELECT id, avg(a.sale_price) FROM (
SELECT id, sale_price FROM order_items
UNION ALL
SELECT id, sale_price FROM order_items_historic) AS a
GROUP BY 1;
11.兩者都包括
select category from new_products
intersect
select category from legacy_products;
12.第一類有第二類沒有
select category from new_products
except
select category from legacy_products;
13.SELECT COUNT(*) FROM flights WHERE arr_time IS NOT NULL and destination = 'ATL'
14.select
case
when elevation <250 then 'low'
when elevation between 250 and 1749 then 'medium'
when elevation >=1750 then 'high'
end as elevation_tier
, count(*)
from airports
group by 1;
15.select state,
count(case when elevation <1000 then 1 else null end)as
count_low_elevation_airports
from airports
group by state;
select origin, sum(distance)as total_flight_distance,sum(case when carrier='DL' then distance else 0 end)as total_delta_flight_distance
from flights
group by origin;
17.select origin,
100.0*(sum(case when carrier='DL' then distance else 0 end)/sum(distance))as percentage_flight_distance_from_delta
from flights
group by origin;
18.ELECT state, 100.0 * sum(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*) as percentage_high_elevation_airports FROM airports GROUP BY state;
19.select datetime(delivery_time)
from baked_goods;
20.select date(delivery_time),count(*)as count_baked_goods
from baked_goods
group by date(delivery_time);
21.SELECT DATETIME(delivery_time, '+5 hours', '20 minutes', '2 days') as package_time
FROM baked_goods;
22.SELECT ROUND(distance, 2) as distance_from_market
FROM bakeries;
23.select id,min(cook_time,cool_down_time)
from baked_goods;
24.select first_name||' '||last_name as full_name
from bakeries;
25.SELECT REPLACE(ingredients,'enriched_',' ') as item_ingredients
FROM baked_goods;
