Three usages of SQL, LINQ, and Lambda

Querying the Sname, Ssex, and class columns of all records in the student table

Select Sname,ssex,class from Studentlinq: 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    })

Query teachers all units that are not duplicated depart columns

Select distinct depart from teacherlinq: from    T in teachers.distinct ()    Select T.departlambda:    Teachers.distinct (). Select (t = t.depart)

Querying all records of a student table

SELECT * from Studentlinq: from    s in Students    select Slambda:    students.select (s + = s)

Query all records from 60 to 80 in the score table

SELECT * from score where degree between and 80Linq: from    s in Scores    where S.degree >= && S.degr EE <    Select Slambda:    scores.where (         s = = (                s.degree >= && s.degree <              ) 
   )

Query for records with a score of 85, 86, or 88 in the score table

SELECT * from score where degree in (85,86,88) Linq:in from    s in Scores    where (            new decimal[]{85,86,88}          ). C Ontains (s.degree)    Select Slambda:    scores.where (s = = new decimal[] {85,86,88}. Contains (S.degree)) not in from    s in Scores    where! (            New decimal[]{85,86,88}          ). Contains (s.degree)    Select Slambda:    scores.where (s + =!) ( New decimal[]{85,86,88}. Contains (s.degree)) any    () application: Dual-table must be a primary key (string)    customerdemographics Customertypeid (string) when any    Customercustomerdemos (CustomerID Customertypeid) (String)    a primary key with two main build any (or a pair of key to any) is    not, With two primary keys to a primary key with any from        E in Customerdemographics    where!e.customercustomerdemos.any ()    Select e        From C in Categories    where!c.products.any ()    Select C

Check the student table for "95031" class or sex for "female" students record

SELECT * FROM student where class = ' 95031 ' or ssex= N ' female ' Linq: from    s in Students    where s.class = = ' 95031 '        | | s . CLASS = = "female"    Select Slambda:    students.where (s = = (S.class = = "95031" | | s.class = "female"))

Querying all records of a student table in descending class

SELECT * FROM Student ORDER by Class Desclinq: from    s in Students    s.class descending    Select slambda:
   students.orderbydescending (s = s.class)

Query all records of score table in CNO Ascending, degree descending order

SELECT * FROM Score ORDER by Cno Asc,degree Desclinq: (Here Cno ASC in LINQ to be written on the outermost) from    s in Scores to    s.degree de Scending    s.cno Ascending     Select Slambda:    scores.orderbydescending (s = = S.degree)          . (s = = S.cno)

Check the number of students in "95031" classes

Select COUNT (*) from student where class = ' 95031 ' Linq:    (from    s in Students        where s.class = = "95031"        Sele CT s    ). Count () Lambda:    students.where (s = = S.class = = "95031")                . Select (s = = s)                    . Count ()

Query the student number and course number of the highest score in the score table

Select distinct S.SNO,C.CNO from student 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 to C in Courses from SC in Scores to Maxdegree = (from SSS in Scores Select SSS. degree). Max () Let Sno = (from the 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 ()
Operational issues? Execution times error: where S.sno = = SNO (this line is quoted) operator "= =" cannot be applied to operands of type "string" and "System.linq.iqueryable<string>": Original: Let SNO = ( From the SS in Scores where SS. degree = = Maxdegree Select SS. SNO). ToString () queryable (). Single () returns the unique element of the sequence, or an exception is thrown if the sequence does not contain exactly one element. Solution: Let Sno = (from the SS in Scores where SS. degree = = Maxdegree Select SS. SNO). Single (). ToString ()

The average score for the ' 3-105 ' course

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 ()

Check the average score for a course that has at least 5 students enrolled in the score table and starts with 3

Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5linq: 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)) Linq:sqlmethodlike can also be written like this:    s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

Query Sno columns with a minimum score greater than 70 and a maximum score of less than 90

Select Sno from score GROUP by Sno have min (degree) > and Max (degree) < 90Linq: from    s in Scores    Group s by S.sno into    SS    where SS. Min (cc = CC. degree) > && ss. Max (cc = CC. degree) <    -select new    {        sno = ss. Key    }lambda:    scores.groupby (s = = S.sno)               . Where (ss = (ss. Min (cc = CC. degree) > && (ss. Max (cc = CC. (degree) <))                   . Select (ss = new {                                        sno = ss. Key                                     })

Query all students for Sname, CNO, and degree columns

Select S.sname,sc.cno,sc.degree from student as s,score as SC where S.sno = sc.snolinq: 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.sname,                                            CNO = sc. CNO,                                            degree = sc. Degree                                          })

Query all students for SNO, CNAME, and degree columns

Select Sc.sno,c.cname,sc.degree from course as c,score as SC where c.cno = sc.cnolinq: From    C in Courses    join SC I n Scores on    c.cno equals SC. CNO    Select New    {        sc. Sno,c.cname,sc. Degree    }lambda:    courses.join (Scores, C = c.cno,                              sc = sc. CNO,                              (c, sc) =                                         = new {                                            SNO = sc. SNO,                                             CNAME = c.cname,                                             degree = sc. Degree                                        })

Query all students for sname, CNAME, and degree columns

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.cnolinq:
   from s in Students from    C in Courses to SC in    Scores    where S.sno = = sc. SNO && C.cno = = sc. CNO    Select New {s.sname,c.cname,sc. degree}

Three usages of SQL, LINQ, and Lambda