50 SQL statements involved in a project (sorted Version)

/*
Title: 50 SQL statements involved in a project (sorted Version)
Author: AI xinjue Luo. Xin Hua)
Time: 2010-05-10
Location: Chongqing aerospace Vocational College
Note: The following 50 statements have been tested according to the test data. It is best to run only one statement at a time.
Problem and description:
-- 1. Student table
Student (s #, sname, sage, ssex) -- s # student ID, sname Student name, sage year of birth, ssex Student gender
-- 2. Curriculum
Course (C #, cname, T #) -- C # -- course No., cname Course name, T # instructor No.
-- 3. Instructor table
Teacher (T #, tname) -- T # instructor ID, tname instructor name
-- 4. Orders table
SC (s #, C #, score) -- s # student ID, C # course number, score
*/
-- Create Test Data
Create
Table student (s # varchar (10), Sname nvarchar (10), Sage datetime, ssex nvarchar (10))
Insert
Into student values ('01', n' Zhao re', '2017-01-01 ', n' male ')
Insert
Into student values ('02 ', n' Qian Dian', '2017-12-21 ', n' male ')
Insert
Into student values ('03', n' sun feng', '2017-05-20 ', n' mal ')
Insert
Into student values ('04 ', n' Li yun', '2017-08-06', n' male ')
Insert
Into student values ('05 ', n' Zhou mei', '2017-12-01', n' female ')
Insert
Into student values ('06', n'huland', '2017-03-01 ', n'female ')
Insert
Into student values ('07 ', n' Zheng Zhu', '2017-07-01 ', n' female ')
Insert
Into student values ('08', n'wangju', '2017-01-20 ', n'female ')
Create
Table course (C # varchar (10), Cname nvarchar (10), T # varchar (10))
Insert
Into course values ('01', n' ', '02 ')
Insert
Into course values ('02', n' math ', '01 ')
Insert
Into course values ('03', n' ', '03 ')
Create
Table teacher (T # varchar (10), Tname nvarchar (10))
Insert
Into teacher values ('01', n' Zhang san ')
Insert
Into teacher values ('02', n' Li si ')
Insert
Into teacher values ('03', n' Wang wu ')
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)
Go
-- 1. query the information and scores of students whose scores are higher than those of the "01" course and "02" Course
-- 1.1 check whether "01" course and "02" course exist at the same time.
Select a. *, B. Score [score of course '01'], C. Score [score of course '02']
From student A, SC B, SC C
Where a. s # = B. S # And a. s # = c. s # and B. C # =
'01'
And C. C # =
'02'
And B. score> C. Score
-- 1.2 check whether "01" course and "02" course exist at the same time and "01" course but may not exist "02" course (if it does not exist, it is displayed as null) (If the same content exists below, it will not be explained)
Select a. *, B. Score [score of course "01"], C. Score [score of course "02"]
From student
Left
Join SC B on a. s # = B. S # and B. C # =
'01'
Left
Join SC C on a. s # = c. s # and C. C # =
'02'
Where B. score>
Isnull (C. score,0)

-- 2. query the information and scores of students whose scores are lower than those of the "01" course.
-- 2.1 check whether "01" course and "02" course exist at the same time.
Select a. *, B. Score [score of course '01'], C. Score [score of course '02']
From student A, SC B, SC C
Where a. s # = B. S # And a. s # = c. s # and B. C # =
'01'
And C. C # =
'02'
And B. score <C. Score
-- 2.2 check whether "01" course and "02" course exist at the same time and "01" course does not exist but "02" Course
Select a. *, B. Score [score of course "01"], C. Score [score of course "02"]
From student
Left
Join SC B on a. s # = B. S # and B. C # =
'01'
Left
Join SC C on a. s # = c. s # and C. C # =
'02'
Where
Isnull (B. score,0) <C. Score

-- 3. query the student numbers, names, and average scores of students whose average score is greater than or equal to 60.
Select a. s #, A. sname, cast (AVG (B. Score)
Decimal (18,2) Avg_score
From student A, SC B
Where a. s # = B. S #
Group
By a. s #, A. sname
Having
Cast (AVG (B. Score)
Decimal (18,2)> =
60
Order
By a. s #

-- 4. query the student numbers, names, and average scores of students whose average score is less than 60
-- 4.1 SQL statement used to query student information with scores in the SC table.
Select a. s #, A. sname, cast (AVG (B. Score)
Decimal (18,2) Avg_score
From student A, SC B
Where a. s # = B. S #
Group
By a. s #, A. sname
Having
Cast (AVG (B. Score)
Decimal (18,2) <
60
Order
By a. s #
-- 4.2 SQL statement used to query student information that does not have scores in the SC table.
Select a. s #, A. sname, isnull (cast (AVG (B. Score)
Decimal (18,2)),0) Avg_score
From student a left
Join SC B
On a. s # = B. S #
Group
By a. s #, A. sname
Having
Isnull (cast (AVG (B. Score)
Decimal (18,2)),0) <
60
Order
By a. s #

-- 5. query the student numbers, student names, total number of course selections, and total scores of all courses.
-- 5.1 query all SQL statements with scores.
Select a. s # [student ID], A. sname [Student name], count (B. C #) Total number of course selections, sum (score) [total score of all courses]
From student A, SC B
Where a. s # = B. S #
Group
By a. s #, A. sname
Order
By a. s #
-- 5.2 query all SQL statements (including those with or without scores.
Select a. s # [student ID], A. sname [Student name], count (B. C #) Total number of course selections, sum (score) [total score of all courses]
From student a left
Join SC B
On a. s # = B. S #
Group
By a. s #, A. sname
Order
By a. s #

-- 6. query the number of teachers surnamed Li.
-- Method 1
Select
Count (tname) ["Number of Teachers surnamed Li"]
From teacher where tname like n' Li %'
-- Method 2
Select
Count (tname) ["Number of Teachers surnamed Li"]
From teacher where
Left (tname,1) = N' li'
/*
Number of teachers surnamed "Li"
-----------
1
*/
-- 7. Query Information of students who have learned how to teach "Zhang San"
Select
Distinct student .*
From student, SC, course, teacher
Where student. s # = SC. s # And SC. C # = course. C # And course. T # = teacher. T # And teacher. tname = n' John 3'
Order
By student. s #

-- 8. query the information of students who have never learned how to teach "Zhang San ".
Select M .*
From student m where s # Not
In (select
Distinct SC. s # from SC, course, teacher where SC. C # = course. C # and course. T # = teacher. T # and teacher. tname = n 'zhang san') Order
By m. s #

-- 9. query the information of the student who has learned the course "01" and has also learned the course "02 ".
-- Method 1
Select student .*
From student, SC where student. s # = SC. s # And SC. C # =
'01'
And
Exists (select
1
From SC SC _2 where SC _2.s # = SC. s # And SC _2.c # =
'02') Order
By student. s #
-- Method 2
Select student .*
From student, SC where student. s # = SC. s # And SC. C # =
'02'
And
Exists (select
1
From SC SC _2 where SC _2.s # = SC. s # And SC _2.c # =
'01') Order
By student. s #
-- Method 3
Select M .*
From student m where s # In
(
Select s # From
(
Select
Distinct s # from SC where C # =
'01'
Union
All
Select
Distinct s # from SC where C # =
'02'
) T group
By s # Having
Count (1) =
2
)
Order
By m. s #

-- 10. query the information of students who have learned the course "01" but have not learned the course "02 ".
-- Method 1
Select student .*
From student, SC where student. s # = SC. s # And SC. C # =
'01'
And
Not
Exists (select
1
From SC SC _2 where SC _2.s # = SC. s # And SC _2.c # =
'02') Order
By student. s #
-- Method 2
Select student .*
From student, SC where student. s # = SC. s # And SC. C # =
'01'
And student. s # Not
In (select SC _2.s # from SC SC _2 where SC _2.s # = SC. s # And SC _2.c # =
'02') Order
By student. s #

-- 11. Query Information of students who have not completed all courses
-- 11.1,
Select student .*
From student, SC
Where student. s # = SC. s #
Group
By student. s #, student. sname, student. Sage, student. ssex having
Count (C #) <(select
Count (C #) from course)
-- 11.2
Select student .*
From student left
Join SC
On student. s # = SC. s #
Group
By student. s #, student. sname, student. Sage, student. ssex having
Count (C #) <(select
Count (C #) from course)

-- 12. query the information of at least one course and the same students whose student ID is "01"
Select
Distinct student .*
From student, SC where student. s # = SC. s # And SC. C # In (select C # from SC where s # =
'01') and student. s # <>
'01'
-- 13. query the information of other students whose courses are identical to those of "01"
Select student .*
From student where s # In
(Select
Distinct SC. s # from SC where s # <>
'01'
And SC. C # In (select
Distinct C # from SC where s # =
'01 ')
Group
By SC. s # Having
Count (1) = (Select
Count (1) From SC where s # = '01 '))

-- 14. query the names of students who have not learned any course taught by "Zhang San"
Select student .*
From student where student. s # Not
In
(Select
Distinct SC. s # from SC, course, teacher where SC. C # = course. C # and course. T # = teacher. T # and teacher. tname = n 'zhang san ')
Order
By student. s #

-- 15. query the student ID, name, and average score of two or more failed courses
Select student. s #, student. sname, cast (AVG (score)
Decimal (18,2) Avg_score from student, SC
Where student. s # = SC. s # And student. s # In (select s # from SC where score <
60
Group
By s # Having
Count (1)> =
2)
Group
By student. s #, student. sname

-- 16. retrieve information about students whose scores are less than 60 in descending order.
Select student. *, SC. C #, SC. score from student, SC
Where student. s # = SC. s # And SC. score <
60
And SC. C # =
'01'
Order
By SC. Score DESC
-- 17. display the scores and average scores of all students in all courses from high to low on average
-- 17.1 SQL 2000 static
Select a. s # student ID, A. sname Student name,
Max (Case C. cname when n 'China'
Then B. Score else
Null
End) [language],
Max (Case C. cname when n 'mat'
Then B. Score else
Null
End) [mathematics],
Max (Case C. cname when n' English'
Then B. Score else
Null
End) [English],
Cast (AVG (B. Score)
Decimal (18,2) Average score
From student
Left
Join SC B on a. s # = B. S #
Left
Join course C on B. C # = C. C #
Group
By a. s #, A. sname
Order
By average DESC
-- 17.2 SQL 2000 dynamic
Declare
@ SQL
Nvarchar (4000)
Set
@ SQL
=
'Select a. s #'
+ N'student number'
+
', A. sname'
+ N' student name'
Select
@ SQL
=
@ SQL
+
', Max (Case C. cname when n''' + cname + ''' then B. Score else null end) [' + cname + ']'
From (select
Distinct cname from course) as t
Set
@ SQL
=
@ SQL
+
', Cast (AVG (B. Score) as decimal ))'
+ 'N' average score'
+
'From student a left join SC B on a. s # = B. S # left join course C on B. C # = C. C #
Group by a. s #, A. sname order'
+ 'N' average score'
+
'Desc'
Exec (@ SQL)
-- 17.3 For the dynamic and static Writing of SQL 2005, see my article version 2.0 or version 3.0.

-- 18. query the highest score, lowest score, and average score of each subject in the following format: course Id, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, excellent rate
-- Pass> = 60, medium: 70-80, excellent: 80-90, excellent:> = 90
-- Method 1
Select M. C # [course No.], M. cname [Course name],
Max (N. Score) [highest score],
Min (N. Score) [minute score],
Cast (AVG (N. Score)
Decimal (18,2) [Average score],
Cast (select
Count (1) From SC where C # = M. C # And