Shulou(Shulou.com)05/31 Report--

This article will explain in detail what commonly used SQL sentences are, the editor thinks it is very practical, so share it for you to do a reference, I hope you can get something after reading this article.

Note: the following 50 statements have been tested according to the test data, and it is best to run only one statement at a time.

Question and description:

-1. Student table

Student (Sid,Sname,Sage,Ssex)-Sid student number, Sname student name, Sage date of birth, Ssex

Student gender

-- 2. Class Schedule

Course (Cid,Cname,Tid)-- Cid-- course number, Cname course name, Tid teacher number

-3. Teacher's table

Teacher (Tid,Tname)-Tid

Teacher number, Tname teacher name

-- 4. Score sheet

SC (Sid,Cid,score)-Sid

Student number, Cid course number, score score

, /

-- create test data

Createtable Student (Sidvarchar (10), Snamenvarchar (10), Sagedatetime,Ssex

Nvarchar (10))

Insertinto Studentvalues ('01'

, N 'Zhao Lei'

'1990-01-01', N 'male')

Insertinto Studentvalues ('02'

, N 'Qian Dian'

'1990-12-21', N' male')

Insertinto Studentvalues ('03'

, N 'Sun Feng'

'1990-05-20', N' male')

Insertinto Studentvalues ('04'

, N'Li Yun'

'1990-08-06', N' male')

Insertinto Studentvalues ('05'

, N 'Zhou Mei'

'1991-12-01', N 'female')

Insertinto Studentvalues ('06'

, N'Wu Lan'

'1992-03-01', N 'female')

Insertinto Studentvalues ('07'

, N 'Zheng Zhu'

'1989-07-01', N 'female')

Insertinto Studentvalues ('08'

, N 'Wang Ju'

'1990-01-20', N 'female')

Createtable Course (Cidvarchar (10), Cnamenvarchar (10), Tidvarchar (10))

Insertinto Coursevalues ('01'

, N 'Chinese'

'02')

Insertinto Coursevalues ('02'

, N 'Math'

'01')

Insertinto Coursevalues ('03'

, N 'English'

'03')

Createtable Teacher (Tidvarchar (10), Tnamenvarchar (10))

Insertinto Teachervalues ('01'

, N 'Zhang San')

Insertinto Teachervalues ('02'

, N'Li Si')

Insertinto Teachervalues ('03'

, N 'Wang Wu')

Createtable SC (Sidvarchar (10), Cidvarchar (10), scoredecimal (18d1))

Insertinto SCvalues ('01'

, '01', 80)

Insertinto SCvalues ('01'

, '02', 90)

Insertinto SCvalues ('01'

, '03', 99)

Insertinto SCvalues ('02'

, '01', 70)

Insertinto SCvalues ('02'

, '02', 60)

Insertinto SCvalues ('02'

, '03', 80)

Insertinto SCvalues ('03'

, '01', 80)

Insertinto SCvalues ('03'

, '02', 80)

Insertinto SCvalues ('03'

, '03', 80)

Insertinto SCvalues ('04'

, '01', 50)

Insertinto SCvalues ('04'

, '02', 30)

Insertinto SCvalues ('04'

, '03', 20)

Insertinto SCvalues ('05'

, '01', 76)

Insertinto SCvalues ('05'

, '02', 87)

Insertinto SCvalues ('06'

, '01', 31)

Insertinto SCvalues ('06'

, '03', 34)

Insertinto SCvalues ('07'

, '02', 89)

Insertinto SCvalues ('07'

, '03', 98)

Go

1. Inquire about the information and course scores of the students whose scores in "01" course are higher than those in "02" course.

Idea: course 01 (a set of records), course 02 (a set of records), STUDENT table (a set of records), records contained in these three sets of records, and records with 01 scores > 02 scores.

Select*

Fromstudent s inner

Join (select*

From sc where cid='01') a

On s.sid=a.sidinnerjoin

(select*

From sc where cid='02') b

On s.sid=b.sidwherea.score > b.score

Select a. Coach, B.,

Innerjoinsc b

On a.sid=b.sidandb.cid='01'inner

Join sc c

On a.sid=c.sidandc.cid='02'where

B.score > c.score

1.1. Query the existence of both "01" course and "02" course.

Idea: course 01 (a set of records), course 02 (a set of records), STUDENT table (a set of records), records contained in these three sets of records.

Select*

Fromstudent s inner

Join (select*

From sc where cid='01') a

On s.sid=a.sidinnerjoin

(select

* from sc where cid='02') b

On s.sid=b.sidwherea.sid=b.sid

Select s. Writing journal.

Innerjoinsc a

On s.sid=a.sidanda.cid='01'inner

Joinsc b

On s. Sidesteb.sidandb.cidroomroom02'

Select a.*, b.score [score for course '01'], c.score [score for course' 02'] from

Student a, SC b, SC c

Where a.Sid = b.Sid

And a.Sid = c.Sid

And b.Cid='01'and c.Cid='02'and

B.score > c.score

Query the situation where both the "01" course and "02" course exist and where the "01" course exists but the "02" course may not exist (shown as null if it does not exist) (no explanation if the same content exists below)

Idea: course 01 (a collection of records), course 02 may or may not exist (cid='02' or cid is null), STUDENT table (a collection of records)

Select*

Fromstudent s inner

Joinsc a

On s.sid=a.sidanda.cid='01'left

Join sc b

On s.sid=b.sidand (b.cid='02'or

B.cid is

Null) where a.score > isnull (b.scorejin0)

Select a.*, b.score [score for course "01"], c.score [score for course "02"] from

Student a leftjoin SC b

On a.Sid = b.Sid

And b.Cid='01'leftjoin SC c

On a.Sid = c.Sid

And c. Ciddance 02'

Where b.score > isnull (c.scorePhone0)

2. Inquire about the information and course scores of the students whose scores in "01" course are lower than those in "02" course.

Select*

Fromstudent s inner

Joinsc a

On s.sid=a.sidanda.cid='01'inner

Join sc b

On s.sid=b.sidandb.cid='02'where

A.score=60) b

On s.sid=b.sid

Select*

Fromstudent s inner

Join

(select sid,avg (score) as

Avgscore from scgroup

By sid having

Avg (score) > = 60) a

On s.sid=a.sid

Select a.Sid, a.Sname, cast (avg (b.score) asdecimal (18J.2))

Avg_score

From Student a, sc b

Where a.Sid = b.Sid

Groupby a.Sid, a.Sname

Havingcast (avg (b.score) asdecimal (18jin2)) > = 60

Orderby a.Sid

4. Inquire the student number, student name and average score of the students whose average score is less than 60.

Idea: GPA less than 60 (a set of records), STUDENT (a set of records)

Select s.sid,s.sname,b. [grade point average] fromstudent

S innerjoin

(select sid,convert (decimal (18jue 2), avg (score)) as

'grade point average' from sc

Group by sid

Having avg (score) > 60) b

On s.sid=b.sid

4.1. the SQL statement to query the student information that has grades in the sc table.

Idea: whether the STUDENT table (a collection of records) has records included in the SC table (a collection of records)

Select*

Fromstudent where sid

In (select sidfrom sc)

Select*

Fromstudent s where

Exists (select 1from sc a

Where s.sid=a.sid)

Select a.Sid, a.Sname, cast (avg (b.score) asdecimal (18J.2))

Avg_score

From Student a, sc b

Where a.Sid = b.Sid

Groupby a.Sid, a.Sname

Havingcast (avg (b.score) asdecimal (18jin2)) = 70and a.score=80and a.score=90then 1

Else null

End)) / count (1) * 100) +'%'as

'Excellence rate'

From sc ainner

Join course bon a.cid=b.cidgroup

By b.cid,b.cname

-method 1

Select m.Cid [course number]

M.Cname [course name]

Max (n.score)

[highest score]

Min (n.score)

[lowest score]

Cast (avg (n.score) asdecimal (18pm 2)) [average]

Cast (selectcount (1) from

SC where Cid= m.Cid

And score > = 60) * 100.0 /

(selectcount (1) from

SC where Cid= m.Cid)

Asdecimal (18pr 2)) [pass rate (%)]

Cast (selectcount (1) from

SC where Cid= m.Cid

And score > = 70and score=80and score=90) * 100.0 /

(selectcount (1) from

SC where Cid= m.Cid)

Asdecimal (18pr 2)) [Excellence rate (%)]

From Course m, SC n

Where m.Cid = n.Cid

Groupby m.Cid, m.Cname

Orderby m.Cid

-method 2

Select m.Cid [course number]

M.Cname [course name]

(selectmax (score) from

SC where Cid= m.Cid)

[highest score]

(selectmin (score) from SCwhere Cid

= m.Cid)

[lowest score]

(selectcast (avg (score) asdecimal (18jin2)) from

SC where Cid= m.Cid)

[average score]

Cast (selectcount (1) from

SC where Cid= m.Cid

And score > = 60) * 100.0 /

(selectcount (1) from

SC where Cid= m.Cid)

Asdecimal (18pr 2)) [pass rate (%)]

Cast (selectcount (1) from

SC where Cid= m.Cid

And score > = 70and score=80and score=90) * 100.0 /

(selectcount (1) from

SC where Cid= m.Cid)

Asdecimal (18pr 2)) [Excellence rate (%)]

From Course m

Orderby m.Cid

19. Sort by subject scores and display rankings

Idea: use over (partition by field name order by)

Field name) function.

Normal sort: 1, 2, 3

Select row_number () over (partitionby

Cid order by cid,score

Desc) as sort,*

From sc

Merge repetition does not retain vacancies: 1, 1, 1, 1, 2, 2, 3

Select dense_rank () over (partitionby

Cid order by cid,score

Desc) as sort,*

From sc

Merge and repeat to retain vacancies: 1, 1, 1, 1, 3

Select rank () over (partitionby cid order by cid,score desc) as sort,*

From sc

-- 19.1 sql 2000 completed with subqueries

-- keep the vacancy in the ranking when Score repeats

Select t.*, px=

(selectcount (1) from

SC where Cid= t.Cid

And score > t.score)

+ 1from sc torderby

T.cid, px

-- merge rankings when Score repeats

Select t.*, px=

(selectcount (distinct score) from

SC where Cid= t.Cid

And score > = t.score)

From sc t

Orderby t.cid, px

-- 19.2sql 2005 is done with rank,DENSE_RANK

-- keep the vacancy in the ranking when Score is repeated (rank completed)

Select t.*, px=

Rank () over (partition

By cidorderby scoredesc)

From sc torderby t.Cid, px

-- merge rankings when Score repeats (DENSE_RANK completed)

Select t.*, px=

DENSE_RANK () over (partition

By cidorderby scoredesc)

From sc torderby t.Cid, px

20. Inquire about the total scores of students and rank them.

Idea: the total scores of all students (a set of records), and then use the function to sort.

Select rank () over (orderby

Sum (a.score) desc)

As ranking,s.sid,s.sname,sum (a.score) as

'Total grade 'from student s

Innerjoin sc a

On s.sid=a.sidgroupby

S.sid,s.sname

This query can only find seven students with good grades.

Select dense_rank () over (orderby

Isnull (sum (a.score), 0) desc)

As ranking,s.sid,s.sname

Isnull (sum (a.score), 0) as

'Total score'

From student sleft

Join sc an on s.sid=a.sidgroup

By s.sid,s.sname

With leftjoin, you can find all eight students, including one with no grades.

-- 20.1 inquire about the total scores of students

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student mleftjoin SC non

M.Sid = n.Sid

Groupby m.Sid, m.Sname

Orderby [total score] desc

-- 20.2 query the total scores of students and rank them. Sql 2000 is completed with a sub-query. When the total score is repeated, there are two types of vacancies: keep the vacancy or not keep the vacancy.

Select t1.*, px=

(selectcount (1) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) Total score of t2where >

T1. Total score) + 1from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) T1

Orderby px

Select t1.*, px=

(selectcount (distinct Total score) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) Total score of t2where > =

T1. Total score) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) T1

Orderby px

Sql 2005 is completed with rank,DENSE_RANK. When the total score is repeated, there are two types of vacancy: keep the vacancy and not keep the vacancy.

Select t.*, px=

Rank () over (orderby [Total score] desc) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) t

Orderby px

Select t.*, px=

DENSE_RANK () over (orderby [Total score] desc) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (sum (score), 0) [Total score]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) t

Orderby px

21. Query the average score of different courses taught by different teachers from high to low

Idea: average scores for different courses taught by different teachers (a set of records), and then use the function over (order by field name)

Select rank () over (orderby

Convert (decimal (5 score 2), avg (score)) desc)

As ranking,c.tid,c.tname,b.cid,b.cname

Convert (decimal (5) 2), avg (score)) as

"average score" from sc a

Innerjoin course b

On a.cid=b.cidinner

Join teacher con b.tid=c.tidgroup

By c.tid,c.tname,b.cid,b.cname

Select m.Tid, m.Tname, cast (avg (o.score) asdecimal (18J.2))

Avg_score

From Teacher m, Course n, SCo

Where m.tid = n.Tid

And n.Cid = o.Cid

Groupby m.Tid, m.Tname

Orderby avg_scoredesc

-- 22 ☆, inquire about the second to third students of all courses and the results of the course.

Idea: all course scores of students and course information (a set of records), and then use the function to sort (a set of records), select the second and third records.

; with abc as

(select row_number () over (partition by a.cidorder by a.score

Desc) as ranking,s.sid,s.sname,a.cid,b.cname

A.score from student sinner

Join sc an on s.sid=a.sidinner

Join course b on a.cid=b.cid)

Select

* from abc where ranking in

(2pr 3)

Select

* from

(select row_number () over (partition by a.cidorder by a.score

Desc) as ranking,s.sid,s.sname,a.cid,b.cname

A.score from student sinner

Join sc an on s.sid=a.sidinner

Join course b on a.cid=b.cid) t

Where t.rankingin (2pr 3)

-- 22. 1 sql 2000 completed with subqueries

-- keep the vacancy in the ranking when Score repeats

Select*from (select

T. *, px

= (selectcount (1) from)

SC where Cid= t.Cid

And score > t.score)

+ 1from sc t) mwhere px

Between2and3orderby

M.cid, m.px

-- merge rankings when Score repeats

Select*from (select

T. *, px

= (selectcount (distinct score) from)

SC where Cid= t.Cid

And score > = t.score)

From sc t) m

Where pxbetween2and3orderby

M.cid, m.px

-- 22.2 sql 2005 is completed with rank,DENSE_RANK

-- keep the vacancy in the ranking when Score is repeated (rank completed)

Select*from (select

T. *, px

= rank () over (partitionby cid

Orderby scoredesc)

From sc t) mwhere px

Between2and3orderby

M.Cid, m.px

-- merge rankings when Score repeats (DENSE_RANK completed)

Select*from (select

T. *, px

= DENSE_RANK () over (partitionby cid

Orderby scoredesc)

From sc t) mwhere px

Between2and3orderby

M.Cid, m.px

-- 23 ☆☆☆, statistics of the number of scores of each subject: course number, course name, [100-85], [85-70], [70-60], [0-60] and percentage

Idea: SC table and COURSE table federate queries (a collection of records), then each field is treated as a subquery, and finally these subqueries are pieced together.

Select b.cidas

'course number', b.cnameas

'course name'

Total number of count (1) as''

Count (casewhen a.score=0and a.score=60and

A.score=60and a.score=70and

A.score=70and a.score=85then

1 else null

End) as

"excellent number of people"

Convert (decimal (5), count (casewhen)

A.score > = 85then 1

Else null

End) * 100/count (1)) as

'Excellence rate%'

From sc ainner

Join course bon a.cid=b.cidgroup

By b.cid,b.cname

The above methods are displayed horizontally.

Select b.cidas

'course number', b.cnameas

'course name', (casewhen score=60

And score=70

And score=70and

Score=60and

Score=70and n.score=60and n.score=85then'85-100'

When n.score

> = 70and n.score=60and n.score=85then'85-100'

When n.score

> = 70and n.score=60and n.score=85then'85-100'

When n.score

> = 70and n.score=60and n.score=70and n.score=60and n.score=85then'85-100'

When n.score

> = 70and n.score=60and n.score=85then'85-100'

When n.score

> = 70and n.score=60and n.score=85then'85-100'

When n.score

> = 70and n.score=60and n.score

T1. Grade point average) + 1from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (cast (avg (score) asdecimal (18jin2)), 0) [grade point average]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) T1

Orderby px

Select t1.*, px=

(selectcount (distinct GPA) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (cast (avg (score) asdecimal (18jin2)), 0) [grade point average]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) t2where GPA > =

T1. Grade point average (from)

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (cast (avg (score) asdecimal (18jin2)), 0) [grade point average]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) T1

Orderby px

Sql 2005 is done with rank,DENSE_RANK. When the average score is repeated, there are two kinds of vacancy: keep the vacancy or not.

Select t.*, px=

Rank () over (orderby [grade point average] desc) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (cast (avg (score) asdecimal (18jin2)), 0) [grade point average]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) t

Orderby px

Select t.*, px=

DENSE_RANK () over (orderby [grade point average] desc) from

(

Select m.Sid [student number]

M.Sname

[student name]

Isnull (cast (avg (score) asdecimal (18jin2)), 0) [grade point average]

From Student m leftjoin SC non m.Sid =

N.Sid

Groupby m.Sid, m.Sname

) t

Orderby px

25. Inquire about the records of the top three grades in each subject.

Idea: rank the scores of each discipline (a set of records), and then take the first 3.

Select

* from

(select row_number () over (partition by a.cidorder by a.score

Desc) as ranking

S.sid,s.sname,a.score from student sinnerjoin

Sc an on s.sid=a.sid) t where rankingin

(1, 2, 2, 3)

-25.1 keep the vacancy in the rankings when the scores are repeated

Select m.*, n.Cid, n.scorefrom

Student m, SC nwhere m.Sid

= n.Sid and n.scorein

(selecttop3 scorefrom

Sc where Cid= n.Cid

Orderby scoredesc)

Orderby n.Cid, n.scoredesc

25.2 No vacancy is retained when the scores are repeated, and the rankings are merged.

-- sql 2000 is implemented with subquery

Select*from (select

T. *, px

= (selectcount (distinct score) from)

SC where Cid= t.Cid

And score > = t.score)

From sc t) m

Where pxbetween1and3orderby

M.cid, m.px

-- sql 2005 implemented in DENSE_RANK

Select*from (select

T. *, px

= DENSE_RANK () over (partitionby cid

Orderby scoredesc)

From sc t) mwhere px

Between1and3orderby

M.Cid, m.px

26. Inquire about the number of students who are enrolled in each course.

Idea: the number of students selected for each course (a collection of records).

Select*

Fromcourse an inner

Join

(select cid,count (*) as

Number of people from sc

Group by cid) b

On a.cid=b.cid

Select a. Cidrea.cnameJournal count (1) as

Number of people from course a

Innerjoin sc b

On a.cid=b.cidgroupby

A.cid,a.cname

Select cid, count (Sid) [students] from

Sc groupby Cid

27. Find out the student numbers and names of all the students in only two courses

Select Student.Sid, Student.Sname

From Student, SC

Where Student.Sid= SC.Sid

Groupby Student.Sid, Student.Sname

Havingcount (SC.Cid) = 2

Orderby Student.Sid

28. Inquire about the number of boys and girls

Train of thought:

Select ssex,count (1) number of as' 'from

Student groupby ssex

Selectcount (Ssex) as number of boys from

Studentwhere Ssex

= N 'male'

Selectcount (Ssex) as number of girls from

Studentwhere Ssex

= N 'female'

Selectsum (casewhen

Ssex = N' male 'then1else0end) [number of boys], sum (casewhen

Ssex = N' female 'then1else0end) [number of girls] from

Student

Selectcasewhen Ssex=

N 'then' N 'number of boys' else

N 'number of girls' end [situation of men and women]

, count (1) [number] from

Studentgroupbycasewhen Ssex=

N 'then' N 'number of boys' else

N 'number of girls' end

29. Query the information of students whose names contain the word "wind".

Select*

Fromstudent where sname

Like'% wind%'

Select*from studentwhere

Sname like% Wind%'

Select*from studentwherecharindex (N 'Wind')

, sname) > 0

30. Query the list of same-sex students with the same name and count the number of students with the same name

Idea: GROUP BY according to the last name field, and calculate the number of people at the same time. As long as it is greater than 1, it is the same surname.

Select sname,count (1) as

Number of people from student

Groupby sname

Having count (1) > 1

Select Sname [student name], count (*) [number] from

Studentgroupby Snamehavingcount (*) > 1

31. Query the list of students born in 1990 (Note: the type of Sage column in Student table is datetime)

Select*

Fromstudent where

Datepart (year,sage) = '1990'

Select*from Studentwhereyear (sage) = 1990

Select*from Studentwheredatediff (yy,sage,'1990-01-01') = 0

Select*from Studentwheredatepart (yy,sage) = 1990

Select*from Studentwhereconvert (varchar (4), sage,120) = '1990'

32. Query the average score of each course, and the results are arranged in descending order. When the average score is the same, it is arranged in ascending order of course number.

Idea: the average grade point of each course (a set of records), and then use the function to sort according to the average grade point and course number.

Select row_number () over (orderby

Convert (decimal (18jue 2), avg (a.score)) desc,b.cid) as

'ranking', b.cidrecy b.cnamether conversion (decimal (18pd2), avg (a.score)) as

'grade point average' from sc a

Inner join course b

On a.cid=b.cidgroupby

B.cid,b.cname

Select m.Cid, m.Cname, cast (avg (n.score) asdecimal (18J.2))

Avg_score

From Course m, SC n

Where m.Cid = n.Cid

Groupby m.Cid, m.Cname

Orderby avg_scoredesc, m.Cid

Asc

33. Query the student number, name and grade point average of all students whose GPA is greater than or equal to 85

Select s. Sidgery s.snamePermine convert (decimal (18Phone2), avg (a.score)) as

'grade point average' from student s

Innerjoin sc a

On s.sid=a.sidgroupby

S.sid,s.snamehaving

Avg (a.score) > = 85

Select a.Sid, a.Sname, cast (avg (b.score) asdecimal (18J.2))

Avg_score

From Student a, sc b

Where a.Sid = b.Sid

Groupby a.Sid, a.Sname

Havingcast (avg (b.score) asdecimal (18jin2)) > = 85

Orderby a.Sid

34. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60

Select s.sid,s.sname,b.cname,a.scorefrom

Student sinnerjoin sc a

On s.sid=a.sidinnerjoin

Course b

On a.cid=b.cid

Where b.cnameplate 'math' and

A.score=70

Orderby Student.Sid, SC.Cid

37. Inquire about failed courses

Select s.sid,s.sname,b.cid,b.cname,a.scorefrom

Student sinnerjoin sc a

On s.sid=a.sidinnerjoin

Course b

On a.cid=b.cid

Where a.score=80

Orderby Student.Sid, SC.Cid

39. Ask for the number of students in each course

Select b.cidre b.cnamepene count (1) as

Number of people from sc a

Inner join course b

On a.cid=b.cidgroupby

B.cid,b.cname

Select Course.Cid, Course.Cname,count (*) [number of students]

From Course, SC

Where Course.Cid= SC.Cid

Groupby Course.Cid, Course.Cname

Orderby Course.Cid, Course.Cname

40. Inquire about the information and grades of the students with the highest scores among the students who take the courses taught by teacher Zhang San.

Idea: students in teacher Zhang San's class (a collection of records)

Selecttop 1

* from student s

Inner join sc a

On s.sid=a.sidinnerjoin

Course b

On a.cid=b.cidinnerjoin

Teacher c

On b.tidroomc.tidroomec.tnameplate 'Zhang San' order

By a.scoredesc

40.1 when there is only one highest score

Selecttop1 Student.*

, Course.Cname, SC.Cid, SC.score

From Student, SC, Course, Teacher

Where Student.Sid= SC.Sid

And SC.Cid

= Course.Cidand Course.Tid

= Teacher.Tid

And Teacher.Tname= N 'Zhang San'

Orderby SC.scoredesc

-- 40.2 when there are multiple highest scores

Select Student.*, Course.Cname, SC.Cid, SC.score

From Student, SC, Course, Teacher

Where Student.Sid= SC.Sid

And SC.Cid

= Course.Cidand Course.Tid

= Teacher.Tid

And Teacher.Tname= N'Zhang San 'and

SC.score= (selectmax (SC.score) from

SC, Course, Teacherwhere SC.Cid

= Course.Cid

And Course.Tid= Teacher.Tid

And Teacher.Tname

= N 'Zhang San')

-- 41 ☆, query the student numbers, course numbers and student scores of students with the same scores in different courses

Train of thought:

-method 1

Select m.*from SC m, (select

Cid, score from SCgroupby Cid, scorehavingcount (1) > 1)

N

Where m.Cid = n.Cidand

M.score = n.score

Orderby m.Cid, m.score, m.Sid

-method 2

Select m.*from SC mwhereexists

(select1from (select Cid, scorefrom)

SC groupby Cid, scorehavingcount (1) > 1)

N

Where m.Cid = n.Cidand

M.score = n.score)

Orderby m.Cid, m.score, m.Sid

42. Check the top two with the best scores in each course.

Idea: all grades in each course (a collection of records).

Select

* from (selectrow_number () over (partitionby cid order by score desc) as ranking,* from sc)

A whereranking in (1 dint 2)

Select t.*from sc twhere

Score in (selecttop2 scorefrom

Sc where Cid= T.Cid

Orderby scoredesc)

Orderby t.Cid, t.scoredesc

43. Count the number of students taking each course (for courses with more than 5 students). The course number and the number of electives are required to be output. The query results are arranged in descending order. If the number is the same, the course number is arranged in ascending order.

Select b.cidre b.cnamepene count (1) as

Number of people from sc a

Inner join course b

On a.cid=b.cidgroupby

B.cid,b.cnamehaving

Count (1) > 5order

By count (1)

Desc,b.cid

Select Course.Cid, Course.Cname,count (*) [number of students]

From Course, SC

Where Course.Cid= SC.Cid

Groupby Course.Cid, Course.Cname

Havingcount (*) > = 5

Orderby [number of students] desc

, Course.Cid

44. Retrieve the student numbers of at least two courses

Select s.sidrecoery s.snameBall count (1) as

"number of courses" from student s

Innerjoin sc a

On s.sid=a.sidgroupby

S.sid,s.snamehaving

Count (1) > = 2

Select student.Sid, student.Sname

From student, SC

Where student.Sid= SC.Sid

Groupby student.Sid, student.Sname

Havingcount (1) > = 2

Orderby student.Sid

45. Inquire about the students who have taken all the courses.

Select s.sidrecoery s.snameBall count (1) as

"number of courses" from student s

Innerjoin sc a

On s.sid=a.sidgroupby

S.sid,s.snamehaving

Count (1) > = (selectcount (1) from)

Course)

Method 1 is done according to the quantity.

Select student.*from studentwhere

Sid in

(select Sidfrom sc

Groupby Sidhavingcount (1) =

(selectcount (1) from

Course))

Method 2 uses double negation to accomplish

Select t.*from student twhere

T.Sid notin

(

Selectdistinctm.Sidfrom

(

Select Sid, Cidfrom student, course

) m wherenotexists (select1from

Sc n where n.Sid = m.Sid

And n.Cid = m.Cid)

)

Method 3 uses double negation to accomplish

Select t.*from student twherenotexists (select1from

(

Selectdistinctm.Sidfrom

(

Select Sid, Cidfrom student, course

) m wherenotexists (select1from

Sc n where n.Sid = m.Sid

And n.Cid = m.Cid)

) kwhere k.Sid

= t.Sid

)

46. Inquire about the age of each student

Select*,datediff (year,sage,getdate ()) as

'Age 'from student

Rough algorithm

Select*,datediff (day,sage,getdate ()) / 365as

'Age 'from student

Specific algorithm

-- 46.1 calculated by year only

Select*, datediff (yy, sage, getdate ())

[age] from student

-- 46.2 according to the date of birth, the date of the current month <

On the month and day of the year of birth, the age is reduced by one

Select*, casewhenright (convert (varchar (10), getdate (), 120), 5)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.