SQL query statement practice Experiment 1
Create four tables:
C1_Student(Sno,Sname,Sage,Ssex) table C2_Course(Cno,Cname,Tno) Table C3_SC(Sno,Cno, SCORE) Table C4_Teacher(Tname,Tname) Table
Query the student ID of “001” whose score is higher than “002”; select distinct a.Sno from(select Sno,Grade from C3_SC where Cno=’0000000000′)a,(select Sno,Grade from C3_SC where Cno=’0000000001′)b
where a.Grade>b.Grade;
2Query student ID and gpa of students whose average score is greater than 60;
select Sno,AVG(Grade)
from C3_SC
group by Sno
having AVG(Grade)>60;
3, query all students’ student ID, name, number of courses, total score; select C1_Student.Sno,Sname,COUNT(C3_SC.Cno),SUM(Grade)
from C1_Student left Outer join C3_SC on C1_Student.Sno=C3_SC.Sno
group by C1_Student.Sno,Sname;
Select * from teacher where name = ‘li’; select COUNT(distinct(Tname))
from C4_Teacher
Where Tname like’ li %’;
5. Query the student ID and name of students who have not learned “Ye Ping” teacher’s class;
select Sno,Sname
from C1_Student
Select Sno not in(select distinct(Sno) from C3_SC,C2_Course,C4_Teacher where C4_Teacher C4_Teacher.Tno=C2_Course.Tno and C2_Course.Cno = C3_SC.Cno)
Select id, name from student who has studied “001” and also studied “002”; select Sno,Sname
from C1_Student
where Sno in(select Sno from C3_SC where C3_SC.Cno=’0000000000′)and Sno in(select Sno from C3_SC where C3_SC.Cno=’0000000001′); * * * *
7. Query the student ID and name of the students who have learned all the courses taught by the teacher “Ye Ping”; select Sno,Sname
from C1_Student
where Sno in (select Sno from C3_SC ,C2_Course ,C4_Teacher where C3_SC.Cno=C2_Course.Cno and Group by Sno having count(c3_sc.cno)=(select count(Cno) from s4_sc.cno C4_course,C4_Teacher where c4_course. Tno= c4_course. Tno and Tname=’陈博’));
Select * from student whose score is lower than 001 and whose course number is 002; select Sno,Sname
from(select C1_Student.Sno,C1_Student.Sname,Grade,(select Grade from C3_SC C3_SC_2 where C3_SC_2.Sno=C1_Student.Sno and C3_SC_2.Cno=’0000000001′)Grade2 from C1_Student,C3_SC where C1_Student.Sno=C3_SC.Sno and Cno=’0000000000′)Grade where Grade2<Grade;
9, Query student ID and name of all students whose course score is less than 60; select Sno,Sname
from C1_Student where Sno in (select Sno from C3_SC where C3_SC.Grade<60);
10, select student id, name from student who did not learn all courses; select C1_Student.Sno,C1_Student.Sname
from C1_Student,C3_SC where C1_Student.Sno=C3_SC.Sno group by C1_Student.Sno,C1_Student.Sname having COUNT(Cno)<(select COUNT(C2_Course.Cno) from C2_Course);
Select * from student whose id is 1001 and whose course id is the same as that of student whose id is 1001;
select C1_Student.Sno,C1_Student.Sname
from C1_Student,C3_SC where C1_Student.Sno=C3_SC.Sno and C3_SC.Cno in (select Cno from C3_SC where Sno=’0000003′) and C1_Student.Sno ! = ‘0000003’.
Select * from student whose student ID is “001”; select * from student whose student ID is “001”; select distinct C1_Student.Sno,C1_Student.Sname
from C1_Student,C3_SC where C1_Student.Sno=C3_SC.Sno and C3_SC.Cno in (select Cno from C3_SC where Sno=’0000003′) and C1_Student.Sno ! = ‘0000003’. * * * *
13. Change the scores of the courses taught by Teacher “Ye Ping” in the “SC” table to the average scores of this course; update C3_SC set Grade=(select AVG(C3_SC_2.Grade) from C3_SC C3_SC_2 where C3_SC.Cno=C3_SC_2.Cno)
From C2_Course,C4_Teacher where C2_Course.Cno= c3_sc. Cno and c2_course. Tno=C4_Teacher.
Select * from student whose course is the same as that of student whose course is “1002”; Use student database
select Sno,Sname from C1_Student where Sno in(
select Sno from C3_SC where Cno in (select Cno from C3_SC where Sno=’0000000′) group by
Sno having count(*)=(select count(*) from C3_SC where Sno=’0000000′));
15. Delete the SC table record of learning “Ye Ping” teacher’s class; Delete C3_SC
from C2_course,C4_Teacher
where C2_Course.Cno=C3_SC.Cno and C2_Course.Tno= C4_Teacher.Tno
And Tname=’ 0 ‘;
16Insert some records into the SC table that meet the following conditions:003Student ID of the course,2The average score of the class;
Insert C3_SC select Sno,’0000000000′,(Select avg(Grade)
from C3_SC where Cno=’0000000000′) from C1_Student where Sno not in (Select Sno from C3_SC where Cno=’0000000000′);
17, show all students’ “Database”, “Business Management”, “English” course scores in descending order of average score, in the following format: student ID, “Database”, “Business Management”, English, number of effective courses, effective average score
SELECT Sno as student ID
,(SELECT Grade FROM C3_SC WHERE c3_sc. Sno=t. no AND Cno=’0000000000′) AS C
,(SELECT Grade FROM C3_SC WHERE c3_sc. Sno= t.sc AND Cno=’0000000002′) AS
,COUNT(*) AS valid courses, AVG(t.Geade) AS average grade
FROM C3_SC AS t
GROUP BY Sno
ORDER BY avg(t.Grade)
18, query the highest and lowest scores of each subject: in the following format: course ID, highest score, lowest score
SELECT distinct L. no As a course ID, L.grandas has the highest score, and R.Grandas has the lowest score
FROM C3_SC L ,C3_SC R
WHERE L.Cno = R.Cno and
L.Grade = (SELECT MAX(IL.Grade)
FROM C3_SC AS IL,C1_Student AS IM
WHERE L.Cno = IL.Cno and IM.Sno=IL.Sno
GROUP BY IL.Cno)
AND
R.Grade = (SELECT MIN(IR.Grade)
FROM C3_SC AS IR
WHERE R.Cno = IR.Cno
GROUP BY IR.Cno);
Max (c2_course.cname)AS course name, ISNULL (AVG(Grade),0) AS average Grade
SUM(CASE WHEN ISnull (Grade,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS %
FROM C3_SC T,C2_Course
where t.Cno=C2_Course.Cno
GROUP BY t.Cno
ORDER BY 100 * SUM(CASE WHEN isnull(Grade,0)>=60 THEN 1 ELSE
0 END)/COUNT(*) DESC
Select % gpa and % pass from courses as’ 1 ‘: Business Management (001), Marx (002), OO&UML (003), SELECT SUM(CASE WHEN Cno =’0000000000’ THEN Grade ELSE 0 END)/SUM(CASE Cno WHEN ‘0000000000’ THEN 1 ELSE 0 END) AS C language average
,100 * SUM(CASE WHEN Cno = ‘0000000000’ AND Grade >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cno = ‘0000000000’ THEN 1 ELSE 0 END
Percentage pass in LANGUAGE C
,SUM(CASE WHEN Cno = ‘0000000002’ THEN Grade ELSE 0 END)/SUM(CASE WHEN Cno = ‘0000000002’ THEN 1 ELSE 0 END) AS
,100 * SUM(CASE WHEN Cno = ‘0000000002’ AND Grade >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN Cno = ‘0000000002’ THEN 1 ELSE Percentage of pass in advanced mathematics
FROM C3_SC
SELECT Max (z.no) AS teacher ID, Max (z.name) AS teacher name, c no AS course ID, Max (c.name) AS course name,AVG(Grade) AS average
FROM C3_SC AS T,C2_Course AS C ,C4_Teacher AS Z
where T.Cno=C.Cno and C.Tno=Z.Tno
GROUP BY C.Cno
ORDER BY AVG(Grade) DESC
Select * from student where no. 3 to No. 6 in the following courses: Business Management (001), Marx (002),UML (003), Database (004) [STUDENT ID],[student name], Business Management, Marx,UML, Database, Average Grade SELECT DISTINCT Top 3
C3_sc. Sno As Student ID,
C1_student.sname AS student name,
T1.Grade AS C,
T2.Grade AS database,
T: Yes, I am.
ISNULL(t1. Grade,0) + ISNULL(t2. Grade,0) + ISNULL(t3. Grade,0
FROM C1_Student,C3_SC LEFT JOIN C3_SC AS T1
ON C3_SC.Sno = T1.Sno AND T1.Cno = ‘0000000000’
LEFT JOIN C3_SC AS T2
ON C3_SC.Sno = T2.Sno AND T2.Cno = ‘0000000001’
LEFT JOIN C3_SC AS T3
ON C3_SC.Sno =T3.Sno AND T3.Cno = ‘0000000002’
WHERE C1_Student.Sno=C3_SC.Sno and
ISNULL(T1.Grade,0) + ISNULL(T2.Grade,0) + ISNULL(T3.Grade,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.Grade,0) + ISNULL(T2.Grade,0) + ISNULL(T3.Grade,0)
FROM C3_SC
LEFT JOIN C3_SC AS T1
ON C3_SC.Sno = T1.Sno AND T1.Cno= ‘K1’
LEFT JOIN C3_SC AS T2
ON C3_SC.Sno = T2.Sno AND T2.Cno = ‘K2’
LEFT JOIN C3_SC AS T3
ON C3_SC.Sno = T3.Sno AND T3.Cno = ‘K3’
ORDER BY ISNULL(T1.Grade,0) + ISNULL(T2.Grade,0) + ISNULL(T3.Grade,0) DESC);
SELECT c3_sc. Cno as course ID, Cname,[<60] SELECT c3_sc. Cno as course ID, Cname,[100-85],[85-70],[70-60
As Course Name
,SUM(CASE WHEN Grade BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 – 85]
,SUM(CASE WHEN Grade BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 – 70]
,SUM(CASE WHEN Grade BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 – 60]
,SUM(CASE WHEN Grade < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM C3_SC,C2_Course where C3_SC.Cno=C2_Course.Cno
GROUP BY C3_SC.Cno,Cname;
24, query student’s average score and ranking
SELECT 1+(SELECT COUNT(*))
SELECT Sno,AVG(Grade) FROM (SELECT Sno,AVG(Grade) AS Grade
FROM C3_SC
GROUP BY Sno
) AS T1
WHERE average score > T2.
Sno AS Student ID, gpa
SELECT Sno,AVG(Grade) FROM (SELECT Sno,AVG(Grade) FROM (SELECT Sno,AVG(Grade
FROM C3_SC
GROUP BY Sno
) AS T2
ORDER BY grade DESC;
25, query the top three records of each subject :(not considering the tie)
SELECT t1.Sno as student ID,t1.Cno as course ID,Grade as Grade
FROM C3_SC t1
WHERE Grade IN (SELECT TOP 3 Grade
FROM C3_SC
WHERE t1.Cno= Cno
ORDER BY Grade DESC
)
ORDER BY t1.Cno;
Select Cno, count(Sno) from C3_SC group by Cno;
Sno, c1_student.sname,count(Cno) as select * from c3_sc.sno, c1_student.sname,count(Cno) as select * from c3_sc.sno, c1_student.sname,count(Cno) as select * from c3_sc.sno, c1_student.sname,count(Cno) as select * from c3_sc.sno
from C3_SC,C1_Student
where C3_SC.Sno = C1_Student.Sno group by C3_SC.Sno,C1_Student.Sname having COUNT(Cno)=1;
28, Query the number of boys and girls
Select COUNT(Ssex) as COUNT(Ssex) from C1_Student group by Ssex having Ssex=’ male ‘;
Select COUNT(Ssex) as COUNT(Ssex) from C1_Student group by Ssex having Ssex=’ female ‘;
29Select * from student where name = ‘zhang’
select Sname,Sno,Ssex,Sage,Sdept,Sid,Sdate
from C1_Student
Where Sname like ‘%’;
Select Sname,COUNT(*) as Sname,COUNT(*) as Sname,COUNT(*) as Sname
from C1_Student
group by Sname having COUNT(*)>1;
31, 1999 born in the year of the Student list (note: the type of Student Sage column in the table is a datetime) select Sname, Sno, Ssex, Sage, Sdept, Sid, Sdate
from C1_Student
where Sage=(select DATENAME(YEAR,GETDATE())-1999);
Query the average score of each course, the results are in ascending order, the average score is the same, according to the descending order of the course number
Select Cno,Avg(Grade) from C3_SC group by Cno order by Avg(Grade),Cno DESC ;
33Select id, name, and gpa from student where gpa > 85
select C3_SC.Sno,Sname,AVG(Grade)
from C1_Student,C3_SC
where C1_Student.Sno = C3_SC.Sno group by C3_SC.Sno,Sname having AVG(Grade)>85;
Select Sname,ISNULL(Grade,0) from Sname,ISNULL(Grade,0)
from C1_Student,C2_Course,C3_SC
Where c3_sc. Sno= c1_student. Sno and c2_course. Cno= c3_sc. Cno and c2_course. Cname=’ advanced mathematics’ and c3_sc. Cname=’ advanced mathematics’; * * * *
Query the course selection of all students; select C3_SC.Sno,Sname,C2_Course.Cno,Cname
from C1_Student,C2_Course,C3_SC
where C1_Student.Sno = C3_SC.Sno and C2_Course.Cno = C3_SC.Cno****
Select name, course name and score from any course where score > 70; select Sname,Cname,Grade
from C1_Student,C2_Course,C3_SC
where C1_Student.Sno=C3_SC.Sno and C2_Course.Cno=C3_SC.Cno and C3_SC.Grade>70;
37Select failed courses and rank them by course number
select distinct Cno
from C3_SC where Grade<80 order by Cno;
38Select * from student whose course number is 003 and score > 80;
select C1_Student.Sno,Sname,Grade
from C1_Student,C3_SC
where C1_Student.Sno = C3_SC.Sno and Grade>80 and Cno = ‘0000000000’;
39, find the number of students who took the course
Select COUNT(*) as COUNT from C3_SC; * * * *
Select c1_student.sname,Grade from c1_student.sname,Grade from c1_student.sname,Grade from c1_student.sname,Grade from c1_student.sname,Grade
from C1_Student,C3_SC,C2_Course C,C4_Teacher
where C1_Student.Sno=C3_SC.Sno and C3_SC.Cno=C.Cno and
Grade=(select Max (Grade)from C3_SC where Cno= c.no);
Select C3_SC.Cno,COUNT(*) as COUNT from C3_SC group by Cno;
42, query the student ID, course ID and student score of students with the same score in different courses
select distinct A.Sno,A.Cno,B.Grade from C3_SC A ,C3_SC B where A.Grade=B.Grade and A.Cno <>B.Cno ;
43, query the top two with the best performance of each door
SELECT t1.Sno as student ID,t1.Cno as course ID,Grade as Grade
FROM C3_SC t1
WHERE Grade IN (SELECT TOP 2 Grade
FROM C3_SC
WHERE t1.Cno= Cno
ORDER BY Grade DESC
)
ORDER BY t1.Cno;
44, count the number of students enrolled in each course (only for courses with more than 10 students). Ask output course number and the number of electives, query results in descending order according to the number of people, query results in descending order according to the number of people, if the number of people is the same, according to the number of courses in ascending order
Select Cno as count(*) as number of students
from C3_SC
group by Cno
order by count(*) desc,Cno
45, retrieves the student ID of a student who has taken at least two courses
select Sno
from C3_SC
group by Sno
having COUNT(*)>=2;
46Select * from course where all students are enrolled
select Cno,Cname
from C2_Course
where Cno in(select Cno from C3_SC group by Cno);
Select * from Sname where Sname = ‘select * from Sname’ where Sname = ‘Sname
from C1_Student
where Sno not in
(select Sno
from C2_Course,C3_SC,C4_Teacher
Where c2_course. Tno= c4_teacher. Tno and c3_sc. Cno= c2_course. Cno and Tname=’陈博’);
48, query student id and gpa of students who failed more than two courses
Select Sno, AVG (isnull(Grade,0)) as Grade
from C3_SC where Sno in
(select Sno from C3_SC where
Grade <60 group by Sno having count(*)>2)group by Sno;
Select Sno,Grade from C3_SC where Cno=’0000000001’and Grade <60 order by Grade desc; select * from C3_SC where Cno=’0000000001’and Grade <60 order by Grade desc;
50, delete”002“Classmate”001“Course results
delete from C3_SC where Sno=’0000007’and Cno=’0000000000′;
This article is for the author to practice homework, if there is any mistake, welcome to leave a message to discuss, the blogger likes to progress with you ~~~~
In addition, the article is done for the blogger’s own computer, the purpose of uploading is just to help people in need of help, computer information and information in the database are not hit code, but also hope you don’t malicious attack ha ~~~~~~~~~~~