SQL pta习题

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='体育学院';