Three usages of SQL, LINQ, and Lambda (RPM)

Three usages of SQL, LINQ, and Lambda
Color annotations: SQL linqtosql Lambda QA
1. Query the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student
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, the inquiry teacher all units namely does not duplicate depart column.
Select distinct depart from teacher
Linq:
From T in Teachers.distinct ()
Select T.depart
Lambda:
Teachers.distinct (). Select (t = t.depart)
3. Query all records of student table.
SELECT * FROM Student
Linq:
from S in Students
Select S
Lambda:
Students.select (s = s)

4. Query all records from 60 to 80 of the scores in the score table.
SELECT * from score where degree between and 80
Linq:
from S in Scores
where S.degree >= && S.degree < 80
Select S
Lambda:
Scores.where (
s = (
S.degree >= && S.degree < 80
)
)
5. Check the record of 85, 86 or 88 in the score table.
SELECT * from score where degree in (85,86,88)
Linq:
Inch
from S in Scores
Where (
New decimal[]{85,86,88}
). Contains (S.degree)
Select S
Lambda:
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 S
Lambda:
Scores.where (s = =!) ( New decimal[]{85,86,88}. Contains (S.degree)))
Any () application: Must be a primary key (String) when you make any of the two tables
Customerdemographics Customertypeid (String)
Customercustomerdemos (CustomerID Customertypeid) (String)
One primary key with two main build any (or one key to any)
No, with two primary keys for any with one primary key

From E in Customerdemographics
where!e.customercustomerdemos.any ()
Select E

From C in Categories
where!c.products.any ()
Select C
6, inquires the student table "95031" class or the sex is "the female" the classmate record.
SELECT * FROM student where class = ' 95031 ' or ssex= N ' female '
Linq:
from S in Students
where S.class = = "95031"
|| S.class = = "female"
Select S
Lambda:
Students.where (s = = (S.class = = "95031" | | s.class = = "female"))

7. Query the student table for all records in descending order of class.
SELECT * FROM Student ORDER by Class DESC
Linq:
from S in Students
S.class Descending
Select S
Lambda:
Students.orderbydescending (s = s.class)
8, in CNO Ascending, Degree descending query score all records of the table.
SELECT * FROM Score ORDER by Cno Asc,degree DESC
LINQ: (Here CNO ASC in LINQ to be written on the outermost)
from S in Scores
S.degree Descending
S.cno Ascending
Select S
Lambda:
Scores.orderbydescending (s = s.degree)
. (s = = S.cno)

9. Check the number of students in "95031" class.
Select COUNT (*) from student 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. Check 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
From C in Courses
From SC in Scores
Let 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>"
Solve:
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 ()
11. Check the average of 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 ()

12. Check the average score of the course with at least 5 students enrolled in the score table and begin with 3.
Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have 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))
Linq:sqlmethod
Like can also be written as:
S.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")
13, the query minimum score is greater than 70, the highest score is less than 90 sno column.
Select Sno from score GROUP by Sno have min (degree) > max (degree) < 90
Linq:
from S in Scores
Group S by S.sno
into SS
where SS. Min (cc = CC. degree) > && ss. Max (cc = CC. degree) < 90
Select New
{
Sno = ss. Key
}
Lambda:
Scores.groupby (s = s.sno)
. Where (ss = (ss. Min (cc = CC. degree) > && (ss. Max (cc = CC. degree) < 90)))
. Select (ss = new {
Sno = ss. Key
})
14. Sname, CNO and degree columns for all students are queried.
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.sname,
CNO = SC. CNO,
degree = sc. Degree
})
15. Check the SNO, CNAME and degree columns of all students.
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.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
})
16. Check the sname, CNAME and degree columns of all students.
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}

Three usages of SQL, LINQ, and Lambda (RPM)