【sql语句查询】

use xkgl
go

–1）查询出全体教师的教师号、姓名、性别和出生日期等信息。
select teacherid,teachername,sex,brith
from teacher

–2）查询出学生人数大于等于10人的班级信息，显示班级编号、班级名称、学生人数。
select classid,classname,studentnum
from class
where studentnum >=10

–3）查询出全体教师的姓名和年龄信息，并将结果集的列标题改为中文名。
select teachername as ’ 姓名’,brith as ‘生日’
from teacher

–4）查询 “雷”老师，“刘”老师的姓名及出生日期。
select teachername,brith
from teacher
where teachername like’雷%‘or teachername like’刘%’

–5）查询出姓名中有”云”字的所有学生信息。
select *
from student
where studentname like ‘%云%’

–6）查询所有班级信息,按班级人数排升序。
select *
from class
order by studentnum asc

–7）查询出生日期最大（即年龄最小）的老师姓名及出生日期。
select top 1 teachername as 姓名,brith as 出生日期
from teacher
order by year(getdate())-year(brith)

–利用日期的返回值实现查询：
select top 1 teachername as 姓名,brith as 出生日期
from teacher
order by datediff(year,brith,getdate())

–8）查询年龄大于40岁的教授和副教授的姓名和性别

select teachername as 姓名,sex as 性别,2022-year(brith) as 年龄,Profession as 职称
from teacher
where profession in (‘教授’,‘副教授’)

–9）查询学分值大于等于4的课程的名称
select coursename as 课程名称,credit as 学分值
from course
where credit >=4