MySQL database experiment two: Single table query

experiment two single-table query

First, the purpose of the experiment

Understand the operation and basic usage of the SELECT statement.

Second, the experimental environment

is a Chinese client for MS SQL SERVER 2005.

Iii. Examples of experiments

1. All students are queried for their name, school number and department.

SELECT sname,s#,sdept

From S;

2. Check the detailed records of all students.

SELECT *

From S;

3. Check the names of all students and their year of birth.

SELECT Sname,2011-sage/* assumes the year is 2011 years */

From S;

4. Inquire about the student number of the course.

SELECT s# from SC;

Equivalent to:

SELECT all s# from SC;

Specify distinct keywords to remove duplicate rows from the table

SELECT DISTINCT s#

From SC;

5. Check the name and age of all students under 20 years of age.

SELECT Sname,sage

From S

WHERE Sage < 20;

6, the examination results have failed students of the school number.

SELECT DISTINCT s#

From SC

WHERE grade<60;

7. Check the names, departments and ages of students aged between 20~23岁 (including 20 and 23 years old)

SELECT Sname,sdept,sage

From S

WHERE Sage between and 23;

8, search all surname Liu Cosheng's name, study number and gender.

SELECT Sname,s#,ssex

From S

WHERE Sname like ' Liu ';

9. Check the name of the student whose surname is "Ouyang" and whose full name is three characters.

SELECT Sname

From S

WHERE Sname like ' Ouyang __ ';

10, some students do not take the exam after elective courses, so there is a record of choice of course, but no test results. Check the student's number and the corresponding course number for the missing score.

SELECT s#,c#

From SC

WHERE Grade is NULL

11. Check the name of the computer system under the age of 20.

SELECT Sname

From S

WHERE sdept= ' CS ' and sage<20;

12, query the number of students who took the 3rd courses and their achievements, the query results in descending order of fractions.

SELECT S#,grade

From SC

WHERE c#= ' 3 '

ORDER by Grade DESC;

13, query the whole student situation, the query results according to the Department of the line number in ascending order, the students in the same department in descending order by age.

SELECT *

From S

ORDER by Sdept,sage DESC;

14. Calculate the average grade of the students in the 1th course.

SELECT AVG (Grade)

From SC

WHERE c#= ' 1 ';

15, for each course number and the corresponding number of elective courses.

SELECT C#,count (s#)

From SC

GROUP by C #;

16. Inquire about the number of students enrolled in more than 3 courses.

SELECT s#

From SC

GROUP by s#

Having COUNT (*) >3;

Iv. contents and steps of the experiment

1. Search for the course number and course name of Liu Teacher's course.

2. Search for the number and name of the male student who is older than 23 years old.

3. Statistics on the number of teachers who set up courses in Table C.

4. Count the number of elective courses for each student (more than 5 students are counted). Required to output student number and elective door number, query results in descending order of the number of doors, if the number of doors are the same, according to the number ascending order.

5. The student number and course number of the table sc are searched for the null value of the result.

6. Search for names and ages of all students whose names begin with L.

7. Check the name, department and age of students who are not between 20~23岁

8. The query is neither the information department, the mathematics department, nor the student's name and gender of the computer Science department.

9. Check the names of all students who are not surnamed Liu.

10. Check all grades of student number and course number.

MySQL database experiment two: Single table query