MySQL database experiment three: Connection query

Experiment three connection query

Experiment Name: Connection query (2 hours)

First, the purpose of the experiment

Understand the operation and basic usage of join statement, master the concept and use of inner connection, outer connection, self-connection.

Second, the experimental environment

is a Chinese client for MS SQL SERVER 2005.

Iii. Examples of experiments

1. Check the status of each student and his/her elective courses

SELECT student.*,sc.*

From STUDENT,SC

WHERE Student.sno = SC. Sno;

2. Search for the student number of at least elective course number C2 and C4

SELECT x.s#

From SC as x,sc as Y

WHERE x.s# =y.s# and x.c# = ' C2 ' and y.c# = ' C4 ';

3. Retrieve the student number and name of at least one of the courses taught by Liu Teacher.

SELECT S.s#,sname

From S,sc,c,t

WHERE s.s#=sc.s# and SC. c#=c.c#

and c.t# =t.t# and tname = ' LIU ';

4. SELECT Student.sno,sname,ssex,sage,sdept,cno,grade

From Student left out JOIN SC on (STUDENT.SNO=SC. Sno);

5. For all students who have enrolled in the 2nd course and have scored above 90 points

SELECT Student.sno, Sname

From Student, SC

WHERE Student.sno = SC. Sno and/* CONNECTION predicate */

C.cno= ' 2 ' and SC. Grade > 90;/* Other qualifying conditions */

6. Query each student's number, name, elective course name and grade

SELECT Student.sno,sname,cname,grade

From Student,sc,course/* Multi-table connection */

WHERE Student.sno = SC. Sno

and SC. Cno = Course.cno;

Iv. contents and steps of the experiment

1. The search number is the course name and the teacher name of the S3 students.

2. Search for the name of the female student who has at least one of the courses taught by Liu Teacher.

3. Retrieve the course number of Wang students ' courses.

4. Retrieve the student number of at least two elective courses.

MySQL database experiment three: Connection query