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}