【MySQ】 作业二

作业二

课堂练习

--1.查询全部一年级的学生信息
select s.*, '一年级' gradeid from student s where gradeid=(select gradeid from grade where gradename='一年级');

--2.查询全部二年级的学生的姓名和电话
select s.studentname,s.phone from student s where gradeid=(select gradeid from grade where gradename='二年级');

--3.查询全部一年级女同学的信息
select * from student where gradeid=(select gradeid from grade where gradename='一年级') and sex='女';

--4. 查询课时超过60的科目信息
select * from subjects where classhour>=60; 

--5.查询二年级的科目名称
select *  from subjects where gradeid=(select gradeid from grade where gradename='二年级');

--6.查询二年级男同学的姓名和住址
select s.studentname,s.address from student s where gradeid=(select gradeid from grade where gradename='二年级') and sex='男';

--7.查询无电子邮件的学生姓名和年级信息
select s.studentname,s.gradeid from student s,grade g where s.gradeid=g.gradeid and (email='' or email is null);

--8.查询出生日期在1992年之后的男学生姓名和年级信息
select s.studentname,g.gradename from student s,grade g where s.gradeid=g.gradeid and born>='1992-01-01' and sex='男';

select s.studentname,(select gradename from grade g where s.gradeid=g.gradeid) gradename from student s where born>='1992-01-01' and sex='男';

--9.参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
select * from mark where examdate='2015-07-03' and subjectid=(select subjectid from subjects where subjectname='计算机基本原理');

--10.按照出生日期查询一年级的学生信息
select * from student where gradeid=(select gradeid from grade where gradename='一年级') order by born;

--11.按成绩由高到低的次序查询参加编号为1的科目考试信息
select * from mark where subjectid=1 order by studentscore desc;

--12.查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息
select * from mark where examdate='2015-07-01' and subjectid=(select subjectid from subjects where subjectname<