第九章

--查询空值
SELECT *FROM Student WHERE Email IS NULL
--上级一
--1.查询第一学期全部
SELECT *FROM Student WHERE Gradeid='1'
--2.查询第二学期全部学生姓名+电话
SELECT StudentName ,Phone FROM Student WHERE Gradeid='2'
--3.查询第一学期全部女学生信息
SELECT *FROM Student WHERE Sex='女'
--4.查询可是超过60的科目信息
SELECT *FROM [Subject] WHERE ClassHour>60

--上机二
--1.查询第一学期的科目名称
SELECT SubjectName FROM [Subject] WHERE Gradeid='1'
--2.查询第二学期所有的男生姓名和住址
SELECT StudentName,[Address] FROM Student WHERE Sex='男' AND Gradeid='2'
--3.查询无电子邮件的学生姓名和年级信息
SELECT StudentName ,Gradeid FROM Student WHERE Email IS NULL
--4.查询第二学期的学生中所有在1990后出生的学生姓名
SELECT StudentName FROM Student WHERE Gradeid='2' AND BornDate>'1990-1-1'

--上机三
  --1.
  SELECT *FROM Student WHERE Gradeid='1' order by BornDate 
  --2.
  SELECT *FROM Result WHERE Id='1'    order by  ExamDate ,StudentResult DESC   
  --3.
   SELECT  TOP 5 *FROM Result WHERE ExamDate='2013-3-22' order by StudentResult DESC   
   --4.
   SELECT SubjectName  FROM Subject WHERE Gradeid='2'    
   --5.
   SELECT TOP 1 StudentName,Gradeid FROM Student     order by  BornDate  
   --6.
   SELECT  TOP 1 id FROM Result WHERE ExamDate='2013-3-22' order by StudentResult DESC 
   --7.
   SELECT  TOP 1 * FROM Result WHERE StudentNo='S1101003'  
   --8.
   SELECT  TOP 1 * FROM Result WHERE StudentNo='S1101003' 

--上机四
--1.
SELECT   * FROM Student WHERE DATEDIFF(D,BornDate,GETDATE())>=365*20
--2.
SELECT * FROM Student WHERE DATENAME (mm,BornDate )=1
--3.
SELECT   * FROM Student WHERE DATEPART(mm, GETDATE())=DATEPART(m,BornDate) AND DATEPARE(dd,GETDATE())
=DATEPART (dd,BornDate)
--4.
SELECT  RIGHT(Email,LEN(Email)-CHARINDEX('@',Email,1))FROM Student   WHERE StudentNo='Y21003007'