Lintcode SQL 教程 LEVEL 2

比较运算符
1952 · 查询超过 20 岁的教师

select * from teachers where age > ‘20’;
1953 · 查询中国老师的姓名

select name from teachers where country = ‘CN’;
逻辑运算符
1957 · 查询2020年5月之前开课的课程

select name,created_at from courses where created_at < ‘2020-05-01’ and created_at >= ‘2020-01-01’;
1958 · 查询指定教师所教授的满足条件的课程

select * from courses where teacher_id = ‘4’ and student_count > ‘500’;
2001 · 查询 ‘Web’ 或 ‘Big Data’ 的课程信息

SELECT * from courses where name = ‘Web’ or name = ‘Big Data’
2040 · 查询教师 id 不为 3 且人数大于 800 的课程

select * from courses where not (teacher_id=‘3’) and student_count>‘800’;
特殊条件
1960 · 查询指定时间的课程信息

select * from courses where created_at in (‘2021-01-01’,‘2021-01-03’);
1962 · 查询教师 id 不为 1 和 3 的课程

select name from courses where teacher_id not in (‘1’,‘3’);
1964 · 查询学生数量在指定范围内的课程信息

select * from courses where student_count between ‘50’ and ‘55’;
1972 · 查询有电子邮箱的中日籍教师

select * from teachers where country in (‘JP’,‘CN’) AND email IS NOT NULL;
1974 · 根据邮箱查询教师信息

select name,email from teachers where email LIKE ‘%@qq.com’;
ORDER BY 与 LIMIT
1982 · 查询教师的年龄并按升序排序

select distinct age from teachers order by age;
1977 · 根据中国教师年龄降序排序

select * from teachers where country = ‘CN’ ORDER BY ‘age’ DESC;
1980 · 查询年龄最大的中国教师

select * from teachers where country = ‘CN’ order by age desc limit 1 ;

https://www.nues.cn/index.php/archives/198/