10-1 2-1(b)查询影星S1的出生日期
select birthdate
from MovieStar
where name='s1';
10-2 A3-1查询订单表中的平均运费
select avg(Freight) as avgFreight
from orders;
10-3 A3-4查询产品表中最大库存量
select max(UnitsInStock) as maxUnitsInStock
from products;
10-4 A4-2统计顾客表中每个国家的顾客数量
select Country,count(*) custCount
from customers
group by Country;
10-5 查看所有学生的基本信息
select *
from student;
10-6 检索所有女同学的基本信息
select *
from student
where sex='女';
10-7 找出所有姓“李”的学生姓名、民族和联系电话
select sname,nation,phone
from student
where sname like '李%';
10-8 检索出所有成绩为空的学号，课号。
select sno,cno
from score
where grade is NULL;
10-9 检索出所有课程性质为“必修”的课程号、课程名和学分。
select cno as 课程号,cname as 课程名,credit as 学分
from course
where attribute='必修';
10-10 查询所有1997出生的学生的学号、姓名、民族和身份证号码。
select sno,sname,nation,pnum
from student
where year(birth)=1997;
10-11 统计每个学院的学生总人数。
select dept as 院部 ,count(*) as 总人数
from student
group by dept;
10-12 查询工资高于4000元的员工信息
select *
from employee
where Salary>4000;
10-13 查看1998年出生的学生信息，查询结果以学号升序排列。
select sno as 学号,sname as 姓名,pnum as 身份证号码
from student
where year(birth)=1998
order by sno asc;
10-14 查询姓名是两个字的学生信息
select *
from students
where sname like '__' ;
10-15 查询各个课程号及相应的选课人数
select cno as 课程号,count(*) as 选课人数
from sc
group by cno;
10-16 统计各系的男、女生人数,结果中包含系别、性别、人数这三个中文列标题
select sdept as 系别,ssex as 性别, count(*) as 人数
from students
group by sdept,ssex;
10-17 统计各系的老师人数，并按人数升序排序
select tdept as 系别,count(*) as 教师人数
from teachers
group by tdept;
10-18 查询编号为“Dp02”系部的系部名称和系部主任
select DepartmentName,DepartmentHeader
from Department
where
DepartmentID='Dp02';
10-19 显示出所有学生的学号，课号以及提高1分后的成绩。
select sno,cno, grade+1 grade
from score;
10-20 找出音乐学院或体育学院的学生基本信息
select *
from student
where dept='音乐学院' or dept='体育学院';
