A variety of sql-table check

Use Student
Go
CREATE TABLE Student1
(
code int,
name varchar,
sex char (ten),
tizhong Decimal (18,1),
Age int,
Chinese Decimal (18,1),
Math Decimal (18,1),
中文版 Decimal (18,1),
Banji varchar (a)

)
Go
INSERT INTO student1 values (1, ' Zhaojia ', ' Male ', 50.22,23,78.5,69.0,35.5, ' five classes ')
INSERT INTO student1 values (2, ' Wang Wei ', ' Male ', 48.3,28,99,66,33, ' five classes ')
INSERT into STUDENT1 values (3, ' Meng ', ' female ', 40.5,20,98,97,88, ' Second class ')
INSERT INTO STUDENT1 values (4, ' Li Wei ', ' Male ', 88,30,30.5,20.0,5.5, ' Class three ')
INSERT into STUDENT1 values (8, ' Lynch ', ' female ', 45.5,23,90,69.0,90, ' Second class ')
INSERT into STUDENT1 values (5, ' Song Hai ', ' Male ', 60,23,70,69.0,93, ' Class four ')
INSERT INTO STUDENT1 values (6, ' Lijia ', ' female ', 55,24,88,66,86, ' Class three ')
INSERT INTO STUDENT1 values (7, ' Jennifer ', ' Female ', 45,19,90,100,100, ' one shift ')
INSERT INTO STUDENT1 values (9, ' Zhaojia one ', ' Male ', 66,24,55,55,33.5, ' five classes ')
INSERT into student1 values (10, ' song Jia ', ' female ', 58,27,60,69.0,70, ' five classes ')
INSERT into student1 values (11, ' Zhao Meng Dream ', ' female ', 50,23,88,93,60, ' Second class ')
INSERT INTO student1 values (12, ' Wang ', ' female ', 53.5,22,98,75,100, ' Second class ')
INSERT into student1 values (13, ' Wang Wei ', ' Male ', 70,23,86,85,77, ' Class three ')
INSERT into student1 values (14, ' Sun Wei ', ' Male ', 65,29,65,62,87, ' five classes ')
INSERT into student1 values (15, ' Qian ', ' female ', 60.2,23,66,90,63, ' five classes ')
INSERT into student1 values (16, ' Zhao ', ' Male ', 89,19,90,92,79, ' one shift ')
INSERT INTO student1 values (17, ' Li Haihai ', ' Male ', 75,23,83,46,90, ' Class four ')
INSERT into student1 values (18, ' Wang Jia ', ' female ', 52,20,65,55,45, ' five classes ')
INSERT INTO student1 values (19, ' Hai Sun good ', ' male ', 67,24,53,69.0,78, ' Class three ')
INSERT into student1 values (20, ' Zhao Wei ', ' Male ', 70,23,66,99,45, ' Class four ')
Go
--access to information on all people surnamed Wang
Select *from student1 where name like ' King% '

2 Wang Wei male 48.32899.066.033.0 five class
12 Wang female 53.52298.075.0100.0 Second class
13 Wang Wei male 70.02386.085.077.0 three classes
18 Wang Jiaju 52.02065.055.045.0 Five class

--Check all student information in one class
Select *from student1 where banji= ' a class '

7 Jennifer Female 45.0 19 90.0 100.0 100.0 class
16 Zhao Male 89.01990.092.079.0 class

--See all female classmates and weigh over 65 names
Select name from Student1 where tizhong>65

Li Wei
Zhao Jiayi
Wang Wei
Zhao
Li Haihai
Sun Haijia
Zhao Wei

--Find out all the information of the highest and lowest students in a class of Chinese
Select top 1 *from student1 where banji= ' a class ' ORDER by Chinese

Zhao male 89.0 90.0 92.0 79.0 class

--Check out all the information of the students with the highest score and lowest score of the math class.
Select Top 1* from Student1 where banji= ' class ' ORDER by math

Zhao male 89.0 90.0 92.0 79.0 class
--Check out all the information of the highest and lowest students in English

Select top 1*from student1 where banji= ' a class ' ORDER by 中文版

16 Zhao men         89.0 19 90.0 92.0 79.0 class
--class all personnel information in descending order of language
SELECT * from Kaoshi where banji= ' five class ' ORDER by Chinese desc

2 Wang Wei male 48.302899.0066.0033.00 five class
1 Zhaojia male 50.222378.5069.0035.50 Five class
15 Li Yunju 60.202366.0090.0063.00 Five class
18 Wang Jiaju 52.002065.0055.0045.00 Five class
14 Sun Wei Male 65.002965.0062.0087.00 Five class
10 song Jia Jia female 58.002760.0069.0070.00 five class
9 Zhao Jia Yi men 66.002455.0055.0033.50 Five class

--Class II All personnel information in ascending order of English
Select *from Kaoshi where banji= ' second class ' ORDER by 中文版

11 Zhao Dream girl 50.00 23 88.00 93.00 60.00 Second Class
3 Li Mengyu 40.502098.0097.0088.00 Second Class
8 Sun Yu 45.502390.0069.0090.00 Second Class
12 Wang female 53.502298.0075.00100.00 Second class

--The number of people who have 75 points in English
Select COUNT (*) as number of from Student1 where english>75

11
-A class with a math score of 70 and more than 3 students
Select Banji as Class, COUNT (*) as number of people from Student1 where math>70 GROUP by Banji have Count (*) >2

Class size
Class Two 3

-A class of more than 3 people weighing more than 50
Select Banji as Class, COUNT (*) as number of people from Student1 where tizhong>40 GROUP by Banji have COUNT (*) >3

Class size
Class Two 4
Class Three 4
Five Class 7

--See several English scores
SELECT distinct English as results from Student1

Achievements
5.5
33.0
33.5
35.5
45.0
60.0
63.0
70.0
77.0
78.0
79.0
86.0
87.0
88.0
90.0
93.0
100.0

--View all the information of all math scores 88 and 99 students
Select *from student1 where math in (88, 99)

20 Zhao Wei men         70.0 23 66.0 99.0 45.0 class four
--view name is Li and only two characters of the student All information
select *from student1 where name like ' li _ '

3 Meng Female 40.5 20 98.0 97.0 88.0 Second Class
4 Li Wei male 88.03030.520.05.5 three classes
6 Li Jiaju 55.02488.066.086.0 three classes
15 Li Yunju 60.22366.090.063.0 Five class

--Query the average gender of the language
Select Sex as Gender, AVG (Chinese) as average of student1 group by sex

Gender average score
Male 70.545454
Female 82.555555

--inquire about the total number of people surnamed Li
Select COUNT (*) as number of from Student1 where name like ' Li% '

Number
5

A variety of sql-table check