Experiment four SQL for complex queries

Select Student.sno,sname,ssex,sage,sdept,cno,grade
From STUDENT,SC
Where student.sno=sc.sno//? query each student and their course selection;

Select First.cno,second.cpno
From Course First,course second
where FIRST.CPNO=SECOND.CNO//? To inquire about indirect first courses in each course

Select Student.sno,sname,ssex,sage,sdept,cno,grade
From student to outer join SC on student.sno=sc.sno//?? Connect the STUDENT,SC to the right

Select Student.sno,sname
From student inner join SC on Student.sno=sc.sno
where cno= ' 3 ' and Sc.sno in
(Select Sno
From SC
where cno= ' 2 ')//query the name and number of students who have enrolled in the course 2nd and 3rd;

Select Student.sno,sname
From student
Where sname!= ' Liu Chen ' and sage=
(Select Sage
From student
Where Sname= ' Liu Chen ')//? Query and Liu Chen students of the same age

Select Sname,sage
From student
Where Sno in
(Select Sno
From SC
where CNO in
(Select CNO
From course
where cname= ' database ')//? The name and age of the student whose course is named "Database"

Select Student.sno,sname
From student
Where sdept<> ' is ' and
Sage<all
(Select Sage
From student
Where sdept= ' is ')//? Check the list of students younger than any other department is

Select Student.sno,sname
From student
Where sdept<> ' is ' and
Sage<any
(Select Sage
From student
Where sdept= ' is ')//? Check the list of students in other departments who are younger than the IS Department

Select Sname
From student
where Sno in
(Select Sno from SC
GROUP BY Sno
Have count (*) = (select count (*) from course))//? Query the name of the student who took all the courses

Select Student.sno,sname
From student
Where sdept= ' is ' and ssex= ' man '//? Query Computer Department students and their gender are male students

Select Sno
From SC
where cno= ' 1 ' except
Select Sno
From SC
where cno= ' 2 '//? Query for elective course 1 student collection and elective class 2nd difference set of Students ' collection

Select CNO
From course
where CNO not in
(Select CNO
From SC
Where Sno in
(Select Sno
From student
Where Sname= ' Yong '))//? Query the course number of the course which Li Yong students do not study

Select AVG (sage) as Avgsage
From student inner join SC on Student.sno=sc.sno
where cno= ' 3 '///query The average age of the students who have enrolled in course number 3rd

Select Cno,avg (grade) as Avggrade
From SC
Group by cno//average results for each course student

Select Course.cno ' Course Number ', COUNT (sc.sno) ' number '
From COURSE,SC
where Course.cno=sc.cno
GROUP BY COURSE.CNO have Count (Sc.sno) >3 ORDER by Count (Sc.sno) desc,course.cno asc//? The number of students enrolled in each course is counted (more than 3 people). Requires the output of the course number and the number of electives, and the results are sorted in descending order of number, in ascending order by course number

Select Sname
From student
where sno>
(select Sno from student where Sname= ' Liu Chen ') and
sage< (select Sage from student where Sname= ' Liu Chen ')//? The query number is larger than Liu Chen, and the student's name is younger than his.

Select Sname,sage
From student
Where ssex= ' man ' and sage>
(select MAX (sage) from student where ssex= ' female ')//-ask for the name and age of a male student older than all female classmates

Experiment four SQL for complex queries