# 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 (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 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
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