1. Basic data preparation

Header information

Sname = Sname = Sage date of birth; Ssex = Sage date of birth; Ssex = Sage date of birth; Ssex = Sage date of birth

C#, C#, T#,T#,T#,T#

— Tname (T#,Tname) — Tname (T#,Tname

– 4. Result table SC (s #, c #, score) – s # student number, course number, c # score points

Create test data

Students table Student

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))

Insert into sc values(’01’, ’01’, ’01’) insert into SC values(’02’, ’01’, ’01’) N’ male ‘) insert into sc values(’03’, ’03’, ’03’) ‘1990-08-06′, N’ male ‘) INSERT into sc values(’05’, ’02’, N’ female ‘) Insert into sc values(‘ 02 ‘, ’02’, ’02’, Insert into sc values(’08’, ’99’, ’99 ‘)

Chart of Course

 create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10)) 

Insert into sc values(’01’, ’02’, ’02’) ’01’) insert into sc values(’03’, ’03’, ’03’)

Teachers form the Teacher

create table Teacher(T# varchar(10),Tname nvarchar(10))

Insert into sc values(’02’, ’03’) insert into SC values(’02’, ’03’) N ‘Cathy’)

Result table SC

Create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))

insert into SC values(’01’ , ’01’ , 80) insert into SC values(’01’ , ’02’ , 90) insert into SC values(’01’ , ’03’ , 99) insert into SC values(’02’ , ’01’ , 70) insert into SC values(’02’ , ’02’ , 60) insert into SC values(’02’ , ’03’ , 80) insert into SC values(’03’ , ’01’ , 80) insert into SC values(’03’ , ’02’ , 80) insert into SC values(’03’ , ’03’ , 80) insert into SC values(’04’ , ’01’ , 50) insert into SC values(’04’ , ’02’ , 30) insert into SC values(’04’ , ’03’ , 20) insert into SC values(’05’ , ’01’ , 76) insert into SC values(’05’ , ’02’ , 87) insert into SC values(’06’ , ’01’ , 31) insert into SC values(’06’ , ’03’ , 34) insert into SC values(’07’ , ’02’ , 89) insert into SC values(’07’ , ’03’ , 98).

50 questions

Select * from student where ’01’ is higher than ’02’

1.1 Querying courses “01” and “02” exist at the same time

1.2 Query exists “01” course but may not exist “02” course (null if not)

1.3 Querying the case where “01” course does not exist but “02” course exists

2. Select student id, student name, and student gpa from student whose gpa is 60 or higher

3. Query information about students whose grades exist in SC table

Select student id, student name, total number of courses, total score of all courses (null if no result is displayed)

4.1 Check the information of students with grades

5. Query the number of teachers whose surname is Li

6. Query information about students taught by “John”

Select * from student where not enrolled in all courses

Select * from student where student id is’ 01 ‘and student in at least one course

Query information about other students who are taking the same course as student “01

Select * from student where no student has taken any courses taught by Mr. John

Select id, name and gpa from student who failed 2 or more courses

12. Retrieve information from students whose scores in “01” are less than 60 in descending order

13. Shows the grades of all students in all courses in descending order of average, as well as their average

Select top, bottom, average, pass rate, medium rate, Excellent rate, excellent rate, pass rate >=60, Medium rate: 70-80, Excellent rate: 80-90, excellent rate: =60 >=90 The course ID and the number of electives are displayed. The query results are displayed in descending order according to the number of electives. If the number of electives is the same, the query results are displayed in ascending order according to the number of electives

15. The ranking is sorted according to the scores of each subject, and the ranking is displayed. The ranking is vacant when the Score is repeated

15.1 Sort according to the scores of each subject and display the ranking. When Score is repeated, the ranking will be merged

16. Query the total score of students and rank them. If the total score is repeated, the ranking will remain vacant

16.1 Query the total score of students and rank them. No vacancy will be reserved when the total score is repeated

17. Count the number of students in different grades of each subject: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage

18. Query top 3 scores of each subject

Query the number of students enrolled in each course

Select student id and name from student where only two courses are selected

Query the number of boys and girls

Select * from student whose name contains “feng”

23. Check the list of students with the same name and the number of students with the same sex

Query the list of students born in 1990

25. Query the gpa of each course and rank the results in descending order. If the gpa is the same, rank the results in ascending order

Select id, name, and gpa from student where gpa = 85 or higher

Select * from student where course name is’ math ‘and score is less than 60

28. Query the courses and scores of all students (there are cases where students do not get grades or choose courses)

29. Query the name, course name, and grade of any course with a grade of 70 or higher

30. Check for classes that failed

Select * from student where id = 01 and score > 80

Find the number of students in each course

33. The score is not repeated, query “Zhang SAN” teacher taught by the students of the course, the highest score of the student information and results

34. If there is a duplicate score, query the highest score of the students who take the course taught by the teacher “Zhang SAN”

35. The query different course grade students of same student number, course number, students’ grades

36. Query the top two students with the best scores in each subject

37. Count the number of students enrolled in each course (only for courses with more than 5 students).

38. Retrieves the student IDS of students enrolled in at least two courses

Select * from student where all courses are enrolled

Select * from student where age = year

41. In terms of date of birth, age is reduced by one if the date of birth is less than the date of birth

Query student whose birthday is this week

Query student whose birthday is next week

Query student whose birthday is this month

Query student whose birthday is next month