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:

1. 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%'  

1. 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'))  

1. 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')  

1. 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  

1. 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'  
    )  
)  

1. 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)  

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') 

1. 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')  

1. 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')  

1. 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')  

1. 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'  

1. 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