Database experiment - Data Query Language

(1) Query the basic information of students;

 

 select * from S;

 

     

 

(2) To inquire the basic information of CS students

select * from S where Sdept ='CS';

(3) Inquire the student number and name of "CS" students whose age is not between 19 and 21

select Sno, Sname from S 

where Sdept = 'CS' and Sage not Between 19 and 21;

 

             

(4) Find out the maximum age

select max(Sage) from S;

(5) Find out the oldest students in "CS" department and display their student number and name;

select * from S where

Sage = (select max(Sage) from S where Sdept = 'CS');   

  

(6) Find out the oldest students in each department and display their student number and name

  select Sno, Sname from S 
  where Sage in
 (select max(Sage) from S group by Sdept);

           

(7) Statistics of the number of students in "CS" department

 select count(*) from S;

(8) The number of students in each department was counted and the results were arranged in ascending order

select Sdept, count(*) from S group by Sdept

Order by count(*) asc;

             

(9) The average age of the students in each department was calculated systematically, and the results were arranged in descending order;

select Sdept, avg(Sage) as AVG from 
group by Sdept 
order by AVG desc;

(10) Query the course name of each course;

select Cname from C;

              

(11) Query the course name and class hours of courses without advanced courses;

select Cname, Ccredit from C where Cpno is null;

            

(12) Count the total number of class hours of courses without advanced courses;

select sum(Ccredit) from C 
where Cpno is null;

              

(13) Count the number of elective courses, credits and average scores of each student;

select Sno ,count(SC.Cno), avg(Grade),Sum(C.Credit)
from SC,C 
where SC.Cno = C.Cno 
group by Sno;

         

(14) Statistics of the number of students taking each course and the average score of each course;

select Cno, count(Sno) as num, avg(Grade) as Avg 
from SC
group by Cno;

(15) Find out the students whose average score is more than 85. The results are grouped by department and arranged in ascending order of average score;

select S.Sdept ,avg(Grade) as Avg , S.Sname as 'name'  from S
left join  SC on S.Sno = SC.Sno 
group by Sdept,S.Sname 
having avg(Grade) > 85 order by avg(Grade);

(16) Inquire the student number and name of the students who have taken "1" or "2" courses;

select S.Sno, S.Sname from S, SC
where (SC.Cno = 1 or SC.Cno = 2)
and S,Sno = SC.Sno group by S.Sno, S.Sname
order by S.Sno, S.Sname;

(18) Inquire the student number, name and score of the students whose course name is "database system" and whose score is below 60;

     

select S.Sno , S.Sname ,SC.Grade from S ,SC, C 
where C.Cname = 'database' 
and Grade < 60 
and SC.Cno = C.Cno 
and SC.Sno = S.Sno;

(19) Query the information of each student who has taken the course (display: student number, name, course number, course name, score);

select * from S,C,SC 
where S.Sno = SC.Sno 
and C.Cno = SC.Cno;

(20) Query the basic information of students without elective courses;

select * from S 
where Sno not in (select Sno from SC);

(21) inquire the student number of students who have taken more than 3 courses;

select Sno 
from SC 
group by Sno 
having count(Cno)>=3;

(22) check that there is at least one student number with a score of more than 80 in the elective courses;

select S.Sno 
from S left join SC on SC.Sno = S.Sno 
where Grade > 80 and Grade is not null 
group by S.Sno 
order by S.Sno

(23) inquire the student ID of students whose scores of elective courses are all above 80;

select Sno, Sname from S 
where Sno in
(select Sno from SC group by Sno having avg(Grade) >=80);

(24) inquire the student ID of students whose average score of elective courses is above 80;

select Sno, Sname from S 
where Sno in
(select Sno from SC group by Sno having avg(Grade) >=80);

Tags: MySQL Database

Posted on Fri, 29 May 2020 01:43:07 -0700 by hl_tan