SQL Statement Learning

Exercise for a table

1. Build a table

CREATE table student (name char, curriculum char, score char (20));

Insert data:

INSERT into student (Name,curriculum,score) VALUES (' Harry ', ' math ', ' 100 ');

Mysql> select * from student;

+--------+------------+-------+

| name | Curriculum | Score |

+--------+------------+-------+

| Zhang San | language | 81 |

| Zhang San | Math | 75 |

| John Doe | language | 76 |

| John Doe | Math | 90 |

| Harry | language | 81 |

| Harry | Math | 100 |

+--------+------------+-------+

2. Topic starts
(1) use an SQL statement to find out the names of students who are greater than the number of each course , and to understand the group by
Answer:

Select distinct name from student where name is not in (select distinct name from student where score<80);

+--------+

| name |

+--------+

| Harry |

+--------+

SELECT DISTINCT * FROM student where name is not in (select distinct name from student where score<80);

+--------+------------+-------+

| name | Curriculum | Score |

+--------+------------+-------+

| Harry | language | 81 |

| Harry | Math | 100 |

+--------+------------+-------+

SELECT DISTINCT * FROM student where name is not in (select distinct name from student where score<80) group by name;

+--------+------------+-------+

| name | Curriculum | Score |

+--------+------------+-------+

| Harry | language | 81 |

+--------+------------+-------+

(2) Form the following form

+--------+--------+--------+

| name | language | Math |

+--------+--------+--------+

| Zhang San | 81 | 75 |

| John Doe | 76 | 90 |

| Harry | 81 | 100 |

+--------+--------+--------+

Answer:

(3) Show if each section passes, use case when

+--------+------------+-------+-----------+

| name | Curriculum | Score | Pass |

+--------+------------+-------+-----------+

| Zhang San | language | 81 | Pass |

| Zhang San | Math | 75 | Fail |

| John Doe | language | 76 | Fail |

| John Doe | Math | 90 | Pass |

| Harry | language | 81 | Pass |

| Harry | Math | 100 | Pass |

+--------+------------+-------+-----------+

Answer:

Select Name, Curriculum,score, (case is student.score>=80 then ' pass ' ELSE ' fail ' END) as pass from  student;

(4) Order by score

+--------+------------+-------+

| name | Curriculum | Score |

+--------+------------+-------+

| Harry | Math | 100 |

| John Doe | Math | 90 |

| Zhang San | language | 81 |

| Harry | language | 81 |

| John Doe | language | 76 |

| Zhang San | Math | 75 |

+--------+------------+-------+

Answer: (+0 because it is char to int)

SELECT * FROM student ORDER by score+0 Desc;

/********************************

* This article from the blog "Bo Li Garvin"

* Reprint Please indicate the source : Http://blog.csdn.net/buptgshengod

******************************************/

SQL Statement Learning