SQL编程

DECLARE @name nvarchar(32)
set @name='张三'
SELECT StudentNo,StudentName,BornDate,Address
FROM Student
WHERE StudentName=@name
print @name

DECLARE @money numeric(18,3)
set @money='18.60'
print @money

DECLARE @Brithday datetime
set @Brithday='2017-1-1'
print @Brithday

select @@SERVERNAME

DECLARE @NAME1 varchar(50)
DECLARE @Result decimal(5,2)
DECLARE @No int
SET @No=10000
SELECT @NAME1=StudentName from Student
where studentno=@No
select @Result =StudentResult from Student
inner join Result on Student.StudentNo=Result.StudentNo
inner join Subject on Result.SubjectId=Subject.SubjectId
where SubjectName='Java Lojic' and Student.StudentNo=@No
and ExamDate='2013-2-15'
print '姓名：'+@NAME1
print '成绩：'+cast(@Result as varchar(10))

select StudentResult,SubjectId from Result where  ExamDate>='2013-10-18' and ExamDate<'2013-10-19'
select subjectname from Subject where SubjectId=2
--求出C#语言和数据库技术对应的id
declare @subjectid int
select @subjectid=Subjectid from Subject
where SubjectName='HTML和CSS网页技术'

--查询平均分
declare @avg int 
select @avg=AVG(studentresult) from Result
where ExamDate>='2013-10-18' and ExamDate<'2013-10-19'
and SubjectId=@subjectid

if(@avg>=70)
begin
print '成绩优秀'
select top 3 * from Result 
where ExamDate>='2013-10-18' and ExamDate<'2013-10-19'
and SubjectId=@subjectid
order by StudentResult desc
end
else
begin
print '还需努力'
select top 3 * from Result 
where ExamDate>='2013-10-18' and ExamDate<'2013-10-19'
and SubjectId=@subjectid
order by StudentResult 
end


declare @date datetime
declare @subno int
select @subno=subjectid from Subject
where SubjectName='HTML和CSS网页技术'
select @date=max(examdate) from Result
where SubjectId=@subno
declare @n int
while(1=1)
begin
select @n=COUNT(*) from Result
where SubjectId=@subno and ExamDate=@date and StudentResult<60
if(@n>0)
update Result set StudentResult=StudentResult+2 from Result
where SubjectId=@subno and ExamDate=@date and StudentResult<95
else 
break
end
print '加分后的成绩如下：'
select studentname,studentresult from Result
inner join student on Result.StudentNo=Student.StudentNo
where SubjectId=@subno and ExamDate=@date


declare @date1 datetime
select @date1=max(examdate) from Result
inner join Subject on Result.SubjectId=Subject.SubjectId
where SubjectName='HTML和CSS网页技术'
select 学号=StudentNo,成绩=
case 
when studentresult<60 then 'E'
when studentresult between 60 and 69 then 'D'
when studentresult between 70 and 79 then 'D'
when studentresult between 80 and 89 then 'D'
else 'A'
end 
from Result
inner join Subject on Result.SubjectId=Subject.SubjectId
where SubjectName='HTML和CSS网页技术' and examdate=@date1



declare @date2 datetime
select @date2=max(examdate) from Result
inner join Subject on Result.SubjectId=Subject.SubjectId
where SubjectName='HTML和CSS网页技术'
select 学号=StudentNo,成绩=
case 

when studentresult between 60 and 69 then '★'
when studentresult between 70 and 79 then '★★'
when studentresult between 80 and 89 then '★★★'
when StudentResult>90 then '★★★★'
else '你要努力了！！'
end 
from Result
inner join Subject on Result.SubjectId=Subject.SubjectId
where SubjectName='HTML和CSS网页技术' and examdate=@date2

select * from Student

select Studentno,studentname,sex,borndate,address from Student
where borndate>(select BornDate from Student where StudentName='金蝶')
go

--1月9日练习
declare @subjectid int
select @subjectid=subjectid from subject
where subjectname='HTML和CSS网页技术'

declare @datetime datetime
select @datetime=max(ExamDate) from result
where subjectid=@subjectid

declare @sum int
select @sum=count(studentno) from result
where subjectid=@subjectid
and examdate=@datetime
and studentresult<70

while(@sum>0)
begin
update result set studentresult+=2
where subjectid=@subjectid
and examdate=@datetime
and studentresult<70
end 

select * from result
where subjectid=@subjectid
and  examdate=@datetime



--答案

declare @subid int 
select @subid=subjectid from subject
where subjectname='HTML和CSS网页技术'

--02.找到一个变量，存储最近一次考试时间
declare @maxdate datetime 
select @maxdate= max(examdate) from result
where subjectid=@subid


select * from result
where subjectid=@subid 
and  examdate=@maxdate


--03.投影出符合条件的人数
declare @sum int
select @sum=COUNT(Studentno) from Result
where subjectid=@subid 
and  examdate=@maxdate
and studentresult<70

while(@sum>0)
begin
   --每人+2分
   update result set studentresult+=2
   where subjectid=@subid 
	and  examdate=@maxdate
    and studentresult<95
    
    select @sum=COUNT(Studentno) from Result
where subjectid=@subid 
and  examdate=@maxdate
and studentresult<70

end



 declare @i int
 declare @j int
 declare @str nvarchar(100)
 set @i=1
 set @j=1
 set @str=''
 while(@i<=5)
 begind
   while(@j<=@i)
   begin
     set @str+='*'  
     set @j+=1
   end
   print @str
   set @i+=1
 end




declare @i nvarchar(32),
        @y int,
        @p nvarchar(32)
       
set @y=1
set @i='☆'
set @p=''
 while(@y<=5)
begin
set @p+=@i
print @p
set @y=@y+1
end



create table taa(
pinhao varchar(5),
taday varchar(5),
num int
)
select * from taa

insert into taa 
select '1001','06-08',11 union 
select '1001','06-09',12 union 
select '1001','06-10',13 union 
select '1001','06-11',14 union 
select '1001','06-12',15 union 
select '1001','06-13',16 union


select '1002','06-08',21 union 
select '1002','06-09',22 union 
select '1002','06-10',23 union 
select '1002','06-11',24 union 
select '1002','06-12',25 union 
select '1002','06-13',26 
 
select pinhao,
sum(case when taday='06-08' then num else 0 end) as [06-08],
sum(case when taday='06-09' then num else 0 end) as [06-09],
sum(case when taday='06-10' then num else 0 end) as [06-10],
sum(case when taday='06-11' then num else 0 end) as [06-11],
sum(case when taday='06-12' then num else 0 end) as [06-12],
sum(case when taday='06-13' then num else 0 end) as [06-13]
from taa
group by pinhao
 



 select [DNAME],HMID, UID, HTID, PRICE, TOPIC, CONTENTS, HTIME, COPY, [SNAME]	from hos_house,[dbo].[hos_district],[dbo].[hos_street]
 where hos_house.SID=hos_street.SID
 and hos_district.DID=hos_house.HTID
and  uid in(select uid from sys_user where UNAME='张三')