SQL statement learning, SQL statements
Exercise for one table
1. Create a table
create table student(name Char(20),curriculum Char(20),score Char(20));
Insert data:
Insert into student (name, curriculum, score) VALUES ('wang wu', 'mat', '123 ');
Mysql> select * from student;
+ -------- + ------------ + ------- +
| Name | curriculum | score |
+ -------- + ------------ + ------- +
| James | Chinese | 81 |
| James | mathematics | 75 |
| Li Si | language | 76 |
| Li Si | mathematics | 90 |
| Wang Wu | language | 81 |
| Wang Wu | mathematics | 100 |
+ -------- + ------------ + ------- +
2. Question start
(1) Use an SQL statement to query the names of students whose scores are greater than 80 in each course.
Answer:
select distinct name from student where name not in (select distinct name from student where score<80);
+ -------- +
| Name |
+ -------- +
| Wang Wu |
+ -------- +
select distinct * from student where name not in (select distinct name from student where score<80);
+ -------- + ------------ + ------- +
| Name | curriculum | score |
+ -------- + ------------ + ------- +
| Wang Wu | language | 81 |
| Wang Wu | mathematics | 100 |
+ -------- + ------------ + ------- +
select distinct * from student where name not in (select distinct name from student where score<80) group by name;
+ -------- + ------------ + ------- +
| Name | curriculum | score |
+ -------- + ------------ + ------- +
| Wang Wu | language | 81 |
+ -------- + ------------ + ------- +
(2) form the following table:
+ -------- +
| Name | Chinese | mathematics |
+ -------- +
| James | 81 | 75 |
| Li Si | 76 | 90 |
| Wang Wu | 81 | 100 |
+ -------- +
Answer:
Select name, (select score from student s where curriculum = 'China' and s. name = student. name) as language, (select score from student s where curriculum = 'mates' and s. name = student. name) as mathematics from student group by name;
(3) Check whether each subject is qualified and use case when
+ -------- + ------------ + ------- + ----------- +
| Name | curriculum | score | pass |
+ -------- + ------------ + ------- + ----------- +
| Michael Zhang | language | 81 | pass |
| James | mathematics | 75 | fail |
| Li Si | language | 76 | fail |
| Li Si | mathematics | 90 | pass |
| Wang Wu | language | 81 | pass |
| Wang Wu | mathematics | 100 | pass |
+ -------- + ------------ + ------- + ----------- +
Answer:
Select name, curriculum, score, (case when student. score> = 80 THEN 'pass 'else' failed' END) as pass from student;
(4) order by score
+ -------- + ------------ + ------- +
| Name | curriculum | score |
+ -------- + ------------ + ------- +
| Wang Wu | mathematics | 100 |
| Li Si | mathematics | 90 |
| James | Chinese | 81 |
| Wang Wu | language | 81 |
| Li Si | language | 76 |
| James | mathematics | 75 |
+ -------- + ------------ + ------- +
Answer: (+ 0 is converted from char to int)
select * from student order by score+0 desc ;
/********************************
* This article is from the blog "Li bogarvin"
* Reprinted please indicate the source: http://blog.csdn.net/buptgshengod
**************************************** **/