2017 July 10

sql练习1707

判断三角形

select case
    when a + b <= c or a + c <= b or b + c <= a then 'Not A Triangle'
    when a = b and b = c then 'Equilateral'
    when a = b or a = c or b = c then 'Isosceles'
    else 'Scalene'
end from triangles;

根据职业打印名单列表，并统计人数

select concat(name, '(', left(occupation, 1), ')') from occupations order by name;
select concat('There are a total of ', count(*), ' ', lower(occupation), 's.')
    from occupations group by occupation order by count(*), occupation;

人口统计问题

select count(*) from city where population > 100000;
select sum(population) from city where district = 'California';
select avg(population) from city where district = 'California';
select round(avg(population)) from city;
select sum(population) from city where countrycode = 'JPN';
select max(population) - min(population) from city;

select sum(city.population) from city
    left join country on country.code = city.countrycode
    where continent = 'Asia';

select city.name from city
    join country on country.code = city.countrycode
    and country.continent = 'Africa' and city.name is not NULL;

select country.continent, floor(avg(city.population)) from country
    join city on country.code = city.countrycode
    group by country.continent;

薪水问题

select ceil(avg(salary) - avg(replace(salary, '0', ''))) from employees;
select months * salary, count(1) from employee group by 1 order by 1 desc limit 1;

气象监控站问题

select city, length(city) from station order by 2, 1 limit 1;
select city, length(city) from station order by 2 desc, 1 limit 1;
select city from station where city regexp '^[aeiou]';
select distinct city from station where city regexp '[aeiou]$';

select round(sum(lat_n), 2), round(sum(long_w), 2) from station;
select round(sum(lat_n), 4) from station where lat_n > 38.7880 and lat_n < 137.2345;
select round(max(lat_n), 4) from station where lat_n < 137.2345;
select round(long_w, 4) from station where lat_n < 137.2345 order by lat_n desc limit 1;
select round(min(lat_n), 4) from station where lat_n > 38.7780;
select round(long_w, 4) from station where lat_n > 38.7780 order by lat_n limit 1;
select round(max(lat_n) - min(lat_n) + max(long_w) - min(long_w), 4) from station;
select round(sqrt(pow(max(lat_n) - min(lat_n), 2) + pow(max(long_w) - min(long_w), 2)), 4) from station;
select round(lat_n, 4) from station s where
    (select count(*) from station where lat_n < s.lat_n) = 
    (select count(*) from station where lat_n > s.lat_n)

学生成绩问题

select name from students where marks>75 order by right(name,3), id;

select case when grade > 7 then name else 'NULL' end as nname, grade, marks
    from students, grades
    where marks between min_mark and max_mark
    order by grade desc, nname, marks;
Table of Contents