create database Student
use Student
go

create table Student(
 StuNO nvarchar(20) primary key,
 StuName nvarchar(20),
 StuAge int,
 StuAddress nvarchar(20),
 StuSeat int identity(1,1),
 StuSex nvarchar(1),--1是男 2是女
)
insert into Student (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 Student
	select StuNO 学生编号,StuName 姓名,StuAge 年龄,StuAddress 家庭住址,StuSex 性别, StuSeat 排序  from Student
	select StuName ,StuAge ,StuAddress from Student
	select StuNO,StuName,StuAddress from Student
	select StuNo+'@'+StuName+StuAddress 邮箱 from Student
	go
create table Exam(
 examNo int identity(1,1),
 StuNO nvarchar(20) references Student(StuNO),
 writtenExam int,
 labExam int,
)
insert into Exam(StuNO,writtenExam,labExam)
values('s2501',50,70),
	  ('s2502',60,65),
	  ('s2503',86,85),
	  ('s2504',40,80),
	  ('s2505',70,85),
	  ('s2506',85,90)
	  select examNo 学号 from Exam
	  select 笔试=writtenExam from Exam 
	  select labExam as 机试 from Exam
	  select examNO,labExam,StuNO,writtenExam  from Exam
--12.将机试成绩在60-80之间的信息查询出来，并按照机试成绩降序排列（用两种方法实现）
   select *from Exam where labExam like'[60-80]' order by labExam desc
--13.查询来自湖北武汉或者湖南长沙的学生的所有信息（用两种方法实现）
   select *from StuInfo where  StuProvince in('湖北武汉','湖南长沙')

--14.查询出笔试成绩不在70-90之间的信息,并按照笔试成绩升序排列（用两种方法实现）
   select* from Exam where writtenExam not between 70 and 90 order by writtenExam asc
--15.查询年龄没有写的学生所有信息
 select * from Student where StuAge is  null
--16.查询年龄写了的学生所有信息
select* from Student where StuAge is not null
--17.查询姓张的学生信息
 select * from Student where StuName like'张%'
--18.查询学生地址中有‘湖’字的信息
 select * from Student where StuAddress like'湖%'
--19.查询姓张但名为一个字的学生信息
 select*from Student where StuName like '张_'
--20.查询姓名中第三个字为‘俊’的学生的信息，‘俊’后面有多少个字不限制
 select * from Student where StuName like '__俊%'
--21.按学生的年龄降序显示所有学生信息
  select *from Student where StuAge=StuAge order by StuAge desc
--22.按学生的年龄降序和座位号升序来显示所有学生的信息
  select *from Student where StuAge=StuAge order by StuAge asc , StuNO desc