create database Student
go
use Student

create table Stuinfo
(

StuSeat int,
StuName nvarchar(10) not null,
StuAge int,
StuSex nvarchar(1) default('1') check(StuSex in ('1','0')),
StuAddress nvarchar(200),
StuNo varchar(800) primary key
)
insert into Stuinfo(StuNo,StuName,StuAge,StuAddress,StuSex)
values('s2501','张秋利','20','美国硅谷','1'),
('s2502','李斯文','18','湖北武汉','0'),
('s2503','马文才','22','湖南长沙','1'),
('s2504','欧阳俊雄','21','湖北武汉','0'),
('s2505','梅超风','20','湖北武汉','1'),
('s2506','陈旋风','19','美国硅谷','1'),
('s2507','陈凤','20','美国硅谷','0')

select * from Stuinfo

create table Grade
(
ExamNo int identity(1,1),
stuNo varchar(800) references Stuinfo(StuNo),
WrittenExam int,
LabExam nvarchar(100)
)
insert into Grade(stuNo, WrittenExam,LabExam)
values('s2501', 50,70),
('s2502',60,65),
('s2503',86,85),
('s2504',40,80),
('s2505',70,90),
('s2506',85,90)
select StuSeat as 座位号, StuName as 姓名,StuAge as 年龄,StuSex as 性别, StuAddress as 地址,StuNo as 编号  from Stuinfo
select StuName as 姓名,StuAge as 年龄, StuAddress as 地址 from Stuinfo
select ExamNo as 学号, WrittenExam as 笔试, LabExam as 机试 from Grade
select 学号=ExamNo ,笔试=WrittenExam,机试=LabExam from Grade
select ExamNo  学号, WrittenExam  笔试, LabExam  机试 from Grade
select StuName+StuNo+StuAddress 邮箱 from Stuinfo
select ExamNo + WrittenExam+ LabExam  from Grade
--11.将地址是湖北武汉，年龄是20的学生的所有信息查询出来
select * from Stuinfo where StuAddress='湖北武汉' and StuAge='20'
--12.将机试成绩在60-80之间的信息查询出来，并按照机试成绩降序排列（用两种方法实现）
select * from  Grade where LabExam='60-80' order by LabExam desc
--13.查询来自湖北武汉或者湖南长沙的学生的所有信息（用两种方法实现）
select * from Stuinfo where StuAddress='湖北武汉' or StuAddress='湖南长沙'
select * from Stuinfo where StuAddress in ( '湖北武汉','湖南长沙' )
--14.查询出笔试成绩不在70-90之间的信息,并按照笔试成绩升序排列（用两种方法实现）
select * from Grade where WrittenExam<70 and WrittenExam>90 order by WrittenExam asc
select * from Grade where WrittenExam not like '[70-90]' order by WrittenExam asc
--15.查询年龄没有写的学生所有信息
select * from Stuinfo where StuAge is null
--16.查询年龄写了的学生所有信息
select * from Stuinfo where StuAge is not  null
--17.查询姓张的学生信息
select * from Stuinfo where StuName like '张%'
--18.查询学生地址中有‘湖’字的信息
select * from Stuinfo where StuAddress like '湖%'
--19.查询姓张但名为一个字的学生信息
select * from Stuinfo where StuName like '张_'
--20.查询姓名中第三个字为‘俊’的学生的信息，‘俊’后面有多少个字不限制
select * from Stuinfo where StuName like '__俊%'
--21.按学生的年龄降序显示所有学生信息
select * from Stuinfo  order by StuAge desc
--22.按学生的年龄降序和座位号升序来显示所有学生的信息
select * from Stuinfo  order by StuAge desc,StuSeat asc