Experiment 6: advanced query of data table
Experimental content
Note: all the following tables are based on XSKC mode
with Experiment 5 Based on database, please use T-SQL statement to perform the following operations:
- Query the name and student number of the student whose second word in the name is "Xiang", as well as the elective course number and course name;
select sname,student.sno,course.cno,cname from XSKC.student,XSKC.course,XSKC.sc where student.sno = sc.sno AND sc.cno = course.cno AND sname like '_towards%'
- List the student number, name, Department, elective course number and grades of students who have taken 'Mathematics' or' College English ';
select student.sno,sname,sdept,cno,grade from XSKC.student,XSKC.sc where student.sno=sc.sno and cno in( select cno from XSKC.course where cname in ('mathematics','College English'))
- Query the information of all students different from the age of 'tension' (assuming the name is unique);
select student.* from XSKC.student where sage !=( select sage from XSKC.student where sname='tension')
- List the students' credits in the order of "student number, name, department and completed credits". Among them, the completed courses are divided into the sum of course credits that have passed the examination;
select student.sno Student number,sname full name,sdept Department,sum(ccredit) Credits completed from XSKC.student,XSKC.course,XSKC.sc where student.sno=sc.sno and course.cno=sc.cno and grade>=60 group by student.sno,sname,sdept
- Find the student number, name and course number of students who have taken at least one of the same elective courses as tension;
select student.sno,sname,sc.cno from XSKC.student,XSKC.sc where student.sno = sc.sno and sc.sno in( select sc.sno from XSKC.student,XSKC.sc where cno in( select cno from XSKC.sc,XSKC.student where sc.sno = student.sno and Sname = 'tension' ) )
- Query the course number and course name of the course selected by only one student;
select sc.cno,cname from XSKC.sc,XSKC.course where course.cno=sc.cno and sc.cno in( select cno from XSKC.sc group by cno having count(*)=1)
- Use nested query to find the student number and name of the students who have taken the "data structure" course;
select student.sno,sname from XSKC.sc,XSKC.student where student.sno=sc.sno and sc.cno in( select cno from XSKC.course where cname='data structure')
- Use nested query to query the student name, age and Department of a student younger than CS in other departments;
select sname,sage,sdept from XSKC.student where sdept!='CS' and sage < any( select sage from XSKC.student where sdept='CS')
- Use ANY and ALL query to list the names of students younger than ALL students in WM department in other colleges and departments;
select sname from XSKC.student where sdept!='WM' and sage < all( select sage from XSKC.student where sdept='WM')
- Use join query and nested query respectively to list the information of students in a department with 'tension';
Connection query:
select b.* from XSKC.student a,XSKC.student b where a.sname='tension' and a.sdept=b.sdept
Nested query:
select student.* from XSKC.student where sdept=( select sdept from XSKC.student where sname='tension')
- Use the set query to list the student numbers and names of CS students and female students;
select sno,sname from XSKC.student where sdept='CS' intersect select sno,sname from XSKC.student where ssex='female'
- Use set query to list the intersection and difference sets of students in CS department and students no older than 19 years old;
select * from XSKC.student where sdept='CS' union select * from XSKC.student where sage<=19
select * from XSKC.student where sdept='CS' except select * from XSKC.student where sage<=19