(31) create a view of information department student information

create view IS_Student
as
select *
from student
where sdept = 'IS';

(32) query students younger than 20 years old in the student view of the information department

select *
from IS_Student
where Sage < 20;

(33) delete all course selection records of Ma Chaoyang

delete 
from sc
where sno in(
select sno
from student
where sname = 'Ma Chaoyang');

(34) query the student number and grades of students who have taken No. 3 course, and arrange them in descending order of scores

select sno,grade
from sc
where cno = '3'
order by grade DESC;

(35) query the database and enter the student number, name and grade of the students who fail the course

select sc.sno,sname,grade
from sc,student
where sc.sno = student.sno 
and grade < 60;

(36) query the situation of all students. The query results are arranged in ascending order according to their department number, and the students in the same department are arranged in descending order according to their age

select *
from student
order by sdept,sage DESC;

(37) inquire about each student and their elective courses

select sc.sno,sname,ssex,sage,sdept,sc.cno,cname,credit,grade,pcno
from student,sc,course
where student.sno = sc.sno
and sc.cno = course.cno;

(38) query the indirect antecedents of each course

select cname,pcno
from course

(39) query the student numbers and names of all students who take No. 1 course and score more than 85

select student.sno,sname
from student,sc
where student.sno = sc.sno 
and cno = '1'
and grade > 85;

(40) query all information of all students

select *
from student

(41) query the student number of students who have taken courses' 1 'and' 2 '

select sno
from sc
where cno = '1' or sno = '2';

(42) create a view of students who have taken course No. 1 in the information department

create view IS_S1(sno,sname,grade)
as
select student.sno,sname,grade
from student,sc
where sdept='IS'
and student.sno = sc.sno
and sc.cno = '1';

(43) establish a view of students who have taken No. 1 course in the information department and scored more than 90 points

create view IS_S2(sno,sname,grade)
as
select sno,sname,grade
from IS_S1
where grade > 90;

(44) query the names of students whose total credits are less than 10 credits

select sname
from student,sc,course
where student.sno = sc.sno
and sc.cno = course.cno
group by sname
having count(credit) < 10;

(45) query the information of all students younger than Liu Chen

select *
from student
where sage < (
select sage
from student
where sname = 'Liu Chen');

(46) query the names of all students who have taken course No. 2

select sname
from student,sc
where student.sno = sc.sno
and cno = '2';

(47) query the name and age of students younger than a student in the information department (IS) in other departments

select sname,sage
from student
where sage < ANY(
select sage
from student
where sdept = 'IS')
and sdept <> 'IS';

(48) query the student's grade of course 2 and output it from high to low

select grade
from sc
where cno = '2'
order by grade DESC;

(49) check the student number of students who have failed the examination

select sno
from sc
where grade < 60;

(50) query the names and ages of students younger than information department (IS) students in other departments

select sname,sage
from student
where sage < ALL(
select sage
from student
where sdept = 'IS')
and sdept <> 'IS';

(51) define all students' student numbers and their average grades as one view

create view VIEW51(sno, GRADE) as
select student.sno,AVG(grade)
from student,sc
where student.sno=sc.sno
group by student.sno; 

(52) in view s_ Query the student number and average score of students with an average score of more than 90 in G:

select sno,avg_grade
from S_G
where avg_grade>=90; 

(53) query the student number, name and age of students who are different from the ages of all students in the Department of Computer Science (CS)

select distinct sno,sname,sage
from student 
where sdept!='IS' and sage NOT IN(		  
select sage
from student 
where sdept='IS'); 

(54) students who have taken the No. 1 course in the inquiry information department

select sc.sno,sname
from student,sc
where sc.cno=1 and sdept='IS' and student.sno=sc.sno;

(55) query the student number, name and age of students who are different from all other students

select a.sno, a.sname, a.sage
from student a
where sage not in(
	select sage
	from student b
	where a.sno!=b.sno
	); 

(56) query the names of students who have taken all courses

select sname
from student,sc
where student.sno=sc.sno and sc.cno=all(
	select cno
	from course); 

(57) ask for course numbers cno and cname that no one takes

select cno,cname
from course
where cno not in(
	select cno
	from sc
	) ; 

(58) query the (sno,cno) pairs that meet the conditions, in which the students of the student number do not take the courses of the course number cno

select sno,course.cno
from course,sc
group by sc.sno,course.cno
having course.cno not in(select sc.cno from sc); 

(59) query the score information (sno,cno,grade) with the highest course score of each student

select b.sno,b.cno,b.grade
from(select sno,max(grade) maxg from sc group by sno) a,sc b
where a.sno = b.sno and a.maxg = b.grade; 

(60) query the total number of students

select COUNT(sno)
from student; 

(61) query the names, names and departments of direct students aged 20-30

select sname,ssex,sdept
from student
where sage>=20 and sage<=30; 

(62) query the total and average academic scores of all courses, as well as the maximum and minimum credits

select SUM(credit) sum, avg(credit) avg, MAX(credit) max ,min(credit) min
from course; 

(64) score information of students whose scores are lower than the average score of the course (sno,cno,grade)

select sno,cno,grade
from sc
where grade<(
	select avg(a.grade)
	from sc a,sc b
	where a.cno=b.cno); 

(65) query the number of students in each department and sort by the number from most to least

select sdept,count(sno) count
from student
group by sdept
order by count desc; 

(66) create a view of students older than 23

create view VIEW66 as
select sage
from student
where sage>23;  

(67) query the total number of students taking elective courses

select COUNT(sno)
from student
where sno in(
	select sno
	from sc); 

(68) query the student number and name of students who have taken more than three courses

select sno
from sc
group by sno
having count(*)>=3; 

(69) query the student number and average score of students with an average score of more than 80

select sno,avg(grade) avg
from sc
group by sno
having avg(grade)>80; 

(70) query the student number of students who have taken more than 2 courses in the "information department" (IS)

select sc.sno
from student,sc
where sdept='IS' and student.sno=sc.sno
group by sc.sno 
having COUNT(sc.sno)>=2; 

(71) query the student number of students who have taken course 1 or 2

select distinct a.sno
from sc a,sc b
where a.cno=1 or b.cno=2 and a.sno=b.sno;

(72) check the student number of students with an average score of less than 70

select sno
from sc
group by sno
having avg(grade)<=70; 

(73) the information department student view is_ The name of the student whose middle school number is "95002" is changed to "Liu Chen"

create view IS_Student
as
select *
from student
where sdept='IS'
update student
set sname='Liu Chen'
where sno = '95002' and sdept='IS'; 

(74) view is to information students_ Insert a new student record in student, student number 95029, name "Liu Yimeng", age 20

insert
into IS_Student
values('95029','Liu Yimeng','female','20','IS'); 

(75) delete information department student view IS_Student's record of student No. 95004

delete
from IS_STUDENT
where sno=95001;