SQL,Linq,Lambda之间的转换练习

1、查询Student表中的所有记录的Sname、Ssex和Class列。

SQL:select sname,ssex,class from Students  

linq:from s in Students select new{s.sname,s.ssex,s.class}

lambda:Students.select(s=>new{sname=s.sname,ssex=s.ssex,class=s.class})

 

2、查询教师所有的单位即不重复的Depart列。

SQL:select distinct depart from Teachers  

linq:from t in Teachers.distinct() select t.depart

lambda:Teachers.distinct().select(t=>t.depart)

 

3、查询Student表的所有记录。

SQL:select * from students

linq:from s in students select s

lambda:students.select(s=>s)  

 

4、查询Score表中成绩在60到80之间的所有记录。

SQL:select * from Score where degree between 60 and 80  

linq:from s in Score where s.degree>=60 && s.degree<=80 select s

lambda:Score.where(s=>(s.degree>=60 && s.degree<=80))  

 

5、查询Score表中成绩为85，86或88的记录。

SQL:select * from Score where degree in (85,86,88)  

linq:from s in Score where (new decimal[]{85,86,88}).contains(s.degree) select s

lambda:Score.where(s=>(new Decimal[]{85,86,88}.contains(s.degree)))  

查询Score表中成绩不是85，86或88的记录。

SQL:select * from Score where degree not in (85,86,88)  

linq:from s in Score where !(new decimal[]{85,86,88}).contains(s.degree) select s

lambda:Score.where(s=>!(new Decimal[]{85,86,88}.contains(s.degree)))  

 

6、查询Student表中"95031"班或性别为"女"的同学记录。

SQL:select * from students where class ='95031' or ssex= N'女'  

linq:from s in Students where s.class="95031" || s.ssex="女" select s

lambda:Students.where(s=>(s.class="95031"||s.ssex="女"))

 

7、以Class降序查询Student表的所有记录。

SQL:select * from students order by Class DESC

linq:from s in students orderby s.class Descending select s

lambda:Students.OrderByDescending(s=>s.class)  

 

8、以Cno升序、Degree降序查询Score表的所有记录。

SQL:select * from score order by Cno ASC,Degree DESC  

linq:from s in Score orderby s.degree descending orderby s.cno ascending select s

lambda:Score.OrderByDescending(s=>s.degree).OrderBy(s=>s.cno)

 

9、查询"95031"班的学生人数。

SQL:select count(*) from students where class = '95031'  

linq:(from s in Students where s.class="95031" select s).count()

lambda:Students.where(s=>s.class=="95031").Select(s=>s).Count()

 

10、查询Score表中的最高分的学生学号和课程号。

SQL:select distinct s.Sno,c.Cno from students as s,course as c ,score as sc where s.sno=(select sno from score where degree = (select max(degree) from score)) and c.cno = (select cno from score where degree = (select max(degree) from score))

linq:(from s in Students from c in Courses from sc in Scores let maxDegree = (from sss in Scores select sss.degree).max() let sno = (from ss in Scores where ss.degree == maxDegree select ss.sno).single().ToString() let cno = (from ssss in Scores where ssss.degree == maxDegree select ssss.cno).Single().ToString() where s.sno== sno && c.cno=cno select new{s.sno,c.cno}).Distinct()

 

11、查询'3-105'号课程的平均分。

SQL:select avg(degree) from score where cno = '3-105'  

linq:(from s in scores where s.cno="3-105" select s.degree).average()

lambda:Scores.where(s=>s.cno=="3-105").select(s=>s.degree).average()

 

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(degree) from score where cno like '3%' group by Cno having count(*)>=5

linq:from s in Scores where s.cno.StartsWith("3") group s by s.cno into cc where cc.count()>=5  select cc.average(c=>c.degree)

lambda:Scores.where(s=>s.cno.StartsWith("3")).GroupBy(s=>s.cno).where(cc=>(cc.count()>=5)).select(cc=>cc.average(c=>c.degree))

 

13、查询最低分大于70，最高分小于90的Sno列。

select sno from score group by sno having min(degree) > 70 and max(degree) < 90  

linq:from s in Scores group s by s.sno into ss where ss.min(cc=>cc.degree)>70 && ss.max(cc=>cc.degree)<90  select new {sno=ss.key}  

lambda:Scores.GroupBy(s=>s.sno).where(ss=>((ss.Min(cc=>cc.degree)>70)&&(ss.max(cc=>cc.degree)<90))).select(ss=>new{sno=ss.key})

 

14、查询所有学生的Sname、Cno和Degree列。

select s.sname,sc.cno,sc.degree from student as s,score as sc where s.sno = sc.sno  

linq:from s in Students join sc in Scores on s.sno equals sc.sno select new {s.sname,sc.cno,sc.degree}

lambda:Students.join(Scores, s=>s.sno, sc=>sc.sno, (s,sc)=>new{sname=s.name,cno=sc.cno,degree=sc.degree})

 

15、查询所有学生的Sno、Cname和Degree列。

SQL:select sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno = sc.cno

linq:from c in Courses join sc in Scores on c.cno equals sc.cno select new {sc.sno,c.came,sc.degree}  

lambda:Courses.join(Scores,c=>c.no,sc=>sc.cno,(c,sc)=>new{sno=sc.sno,cname=c.cname,degree=sc.degree})

 

16、查询所有学生的Sname、Cname和Degree列。

SQL:select s.sname,c.cname,sc.degree from student as s,course as c,score as sc where s.sno = sc.sno and c.cno = sc.cno

linq:from s in Students from c in Courses from sc in Scores where s.sno==sc.sno && c.cno==sc.cno select new{s.sname,c.cname,sc.degree}