Course (Cno Cname the Teacher)
SC (Sno Cno Grade)
1. According to all information in the Student table, requirements in front of the department name and school name,
2. According to the student's grade, request output student id, name, grade, class, which hierarchies according to the results, more than 90 points for the "good", more than 80 points for the "good", more than 70 points as "medium", more than 60 points as "pass", below 60 points as "fail",
CodePudding user response:
The create table # tmp_Student (Sno int,
Sname nvarchar (20),
Ssex nvarchar (5),
Sage int,
Sdep nvarchar (20)
)
Insert into # tmp_Student
Select 1, N 'zhang', N 'male', 21, N 'A college'
The union
Select 2, N 'bill', N 'male', 21, N 'B school'
The union
Select 3, N 'Cathy', N 'male', 22, N 'C college'
The union
Select 4, N 'li lei', N 'male', 21, N 'A college'
The union
Select 5, N 'jack', N 'male', 20, N 'B school'
The create table # tmp_Course (
Cno int,
Cname nvarchar (20),
CTeacher nvarchar (20)
)
Insert into # tmp_Course
Select 1, N 'English', N 'miss li'
The union
Select 2, N 'mathematics', N' horse teachers'
The union
Select 3, N 'language', N 'zhang'
The create table # tmp_SC (
Sno int,
Cno int,
Grade int
)
Insert into # tmp_SC
Select 1, 1, 90,
The union
Select 1, 2, 80,
The union
Select 1, 3, 70,
The union
Select 2, 1, 90,
The union
Select 2, 2, 100,
The union
Select 2, 3, 59
The union
Select 3, 1, 40
The union
Select 3, 2, 70,
The union
Select 3, 3, 59
The union
Select 4, 1, 100,
The union
Select 4, 2, 100,
The union
Select 4, 3, 100,
The union
Select 5, 1, 90,
The union
Select 5, 2, 100,
The union
Select 5, 3, 80,
Select * from # tmp_Student
Select * from # tmp_Course
Select * from # tmp_SC
- 1. (the lack of a related table structure)
2 - (lack of score table table structure) is roughly syntax
The select A.s no,
A.s name,
Arthur c. name,
B.g rade,
The case when b.g rade & gt;=90 then N 'good'
The when b.g rade & gt;=80 and b.g rade & lt; 90 then N 'good'
The when b.g rade & gt;=70 and b.g rade & lt; 80 then N 'medium'
The when b.g rade & gt;=60 and b.g rade & lt; 70 then N 'pass'
The else N 'fail' end as grade
The from # tmp_Student A
Left the join # tmp_SC B on A.s no=b.s no
Left the join # tmp_course C on biggest no=Arthur c. no