1. use a SQL statement to find out the names of students who have more than 80 points per course
Name Kecheng Fenshu
Zhang San language 81
Zhang San Mathematics 75
John Doe Language 76
John Doe Mathematics 90
Harry Language 81
Harry Mathematics 100
Harry English 90
A:select distinct name from table where name isn't in (select distinct name from table where fenshu<=80) keyword distinct Used to return a unique different value
B:select name from table group by name have min (fenshu) >80 keyword having can with aggregate function (aggregate functions such as COUNT, SUM, AVG, MIN, or MAX) function with
C:select name from table group by name have Count (Kecheng) >=3 and Min (Fenshu) >=80
2. Delete the same student redundancy information except auto-numbering
AutoNumber Number name Course number Course name score
1 2005001 Zhang 30,001 Math 69
2 2005002 li 40,001 Math 89
3 2005001 Zhang 30,001 Math 69
A:delete tablename where auto number not in (select min (autonumber) from TableName Group by number, name, course number, course name, score)
3, How to put such a table
Year Month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
Chacheng such a result
Year M1 M2 M3 M4
1991 1.1 1.2) 1.3 1.4
1992 2.1 2.2) 2.3 2.4
A:select year,
(select amount from AAA m where Month=1 and M.year=aaa.year) as M1,
(select amount from AAA m where month=2 and M.year=aaa.year) as M2,
(select amount from AAA m where month=3 and m.year=aaa.year) as M3,
(select amount from AAA m where month=4 and M.year=aaa.year) as M4
From AAA GROUP by year
4. Copy table (copy data, source table name: A target table name: b)
A:insert to B (A, B, c) select D,e,f from A;
5, There is a table, there are 3 fields: Chinese, maths, English.
Greater than or equal to 80 means good, greater than or equal to 60 means passing, less than 60 points for failing.
Display format:
English for Chinese mathematics
Fail to pass a good grade
A:select
(Case when language >=80 then ' excellent '
When language >=60 then ' pass '
Else ' fail ') as language,
(Case when Math >=80 then ' excellent ')
When math >=60 then ' pass '
Else ' fail ') as mathematics,
(Case when English >=80 then ' excellent '
When English >=60 then ' pass '
Else ' fail ') as English,
From table
6, the creation of the table modified delete
Create a student form with the following information, school number, name, age, gender, home address, contact number
A:
Create table Stu (study number int,
Name varchar (8),
Age int,
Sex varchar (4),
Home address varchar (50),
Contact Phone int
);
Modify the structure of student tables, add a column of information, education
Alter table Stu Add education varchar (6);
Modify the structure of student tables, delete a column of information, home address
Alter table Stu Drop column home address
Add the following information to the student table:
School Number name Age gender contact phone education
1A22 Male 123456 Primary School
2B21 Male 119 Middle School
3C23 Male 110 High School
4D18 Female 114 University
Insert into Stu values (1, ' A ', 22, ' Male ', 123456, ' primary ')
Insert into Stu values (2, ' B ', 21, ' Male ', 119, ' secondary ')
Insert into Stu values (3, ' C ', 23, ' Male ', 110, ' High school ')
Insert into Stu values (4, ' D ', 18, ' female ', 114, ' university ')
Revise the data of the student table and change the number of students whose numbers begin with 11 to "junior college"
Update Stu Set education = ' College ' where contact phone like ' 11% '
Delete Student table data, name starts with C, gender is ' male ' record deleted
Delect from stu where sex = ' man ' and name like ' c% '
Check the data of the student table, will all age less than 22 years old, the education is "tertiary", the student's name and school number show
Select name, school number from Stu where age <22 and education = ' Junior College '
Query the Student table data, query all information, list the first 25% records
Select Top percent * from Stu
Check out the names of all students, gender, age descending order
Select name, gender from Stu order by age DESC
Search by gender group for all average ages
Select avg (age) from Stu Group by gender
7, query A (id,name) table in the 31st to 40th record, ID as the primary key may not be continuous growth of the column, the complete query statement as follows:
A:select Top * from a Where ID > (select MAX (ID) from (select top with ID from a, order by a) T) order by a
8, the query table A in the presence of duplicate ID more than three records, the complete query statement as follows:
SELECT * FROM (select COUNT (ID) as count from table Group by ID) T where t.count>3
9. say the meaning of the following aggregate numbers: AVG, SUM, max, Min, Count, COUNT (*)
AVG: Averaging
Sum: Sum
Max: Ask for maximum value
Min: Find minimum value
COUNT (*): Returns all rows
Keyword meaning
Transaction Transaction Trigger TRIGGER continue continue unique unqiue
Primary key primary key identity column identity foreign key foreign key check check
Constraint constraint
10, randomly take out 10 data
Select Top * FROM tablename ORDER by NEWID ()
11, the query average score is more than 60 points of the student's number and average score;
Select Stuid,avg (Score)
From Scores
GROUP BY STUID have AVG (score) >60;
12, https://www.toutiao.com/a6400624100788601090/
13.
SQL Statement Small Exercise