SQL example (1)

Problem description:
Create three tables to manage job business training information:
Student (stu_no, stu_name, stu_workunit, age)

Stu_no, stu_name, stu_workunit, and age indicate the student ID, Student name, unit, and age respectively.

Class (cla_no, cla_name)

Cla_no. cla_name indicates the course number and Course name respectively.

SC (stu_no, cla_no, score)

Stu_no, cla_no, and score indicate the student ID, number of the selected course, and score respectively.

 

Initialization table:

Create Table student (stu_no varchar (20), stu_name varchar (20), stu_workunit varchar (20), age INT); Create Table class (cla_no varchar (20 ), cla_name varchar (20); Create Table SC (stu_no varchar (20), cla_no varchar (20), score float); insert into student values ("001", "zhangsan ", "Emy of mathematics", 25); insert into student values ("002", "padern"," Emy of mathematics", 26); insert into student values ("003 ", "wangwu", "Emy of Literature", 22); insert into student values ("004", "Xiaohong", "external school", 26); insert into student values ("005 ", "Linda", "social science", 24); insert into student values ("006", "Lucy", "journal", 25); insert into student values ("007 ", "Lily", "", 28); insert into student values ("008", "Jim"," 23", 23 ); insert into student values ("009", "green", "", 23); insert into student values ("010", "brone", "", 24 ); insert into student values ("011", "peng", "", 28); insert into student values ("012", "Chern", "", 25); insert into student values ("013", "Tom", "", 23); insert into student values ("014", "wei"," ", 21); insert into class values ("1001", "English"); insert into class values ("1002", "Mathematics"); insert into class values ("1003 ", "language"); insert into class values ("1004", "physical"); insert into class values ("1005", "Geography "); insert into class values ("1006", "chemistry"); insert into class values ("1007", "creature"); insert into class values ("1008 ", "Politics"); insert into class values ("1009", "History"); insert into SC values ("001", "1001", 95 ); insert into SC values ("001", "1002", 90); insert into SC values ("001", "1003", 95 ); insert into SC values ("001", "1006", 96); insert into SC values ("001", "1007", 91 ); insert into SC values ("001", "1009", 50); insert into SC values ("001", "1004", 96 ); insert into SC values ("001", "1005", 81); insert into SC values ("001", "1008", 60 ); insert into SC values ("002", "1001", 56); insert into SC values ("002", "1002", 92 ); insert into SC values ("002", "1005", 66); insert into SC values ("002", "1007", 70 ); insert into SC values ("003", "1002", 83); insert into SC values ("003", "1004", 56 ); insert into SC values ("003", "1005", 92); insert into SC values ("003", "1007", 91 ); insert into SC values ("003", "1009", 60); insert into SC values ("004", "1005", 91 ); insert into SC values ("004", "1006", 90); insert into SC values ("004", "1003", 92 ); insert into SC values ("004", "1001", 76); insert into SC values ("004", "1007", 88 ); insert into SC values ("004", "1009", 82); insert into SC values ("005", "1004", 91 ); insert into SC values ("005", "1006", 56); insert into SC values ("005", "1008", 54 ); insert into SC values ("005", "1009", 67); insert into SC values ("006", "1002", 91 ); insert into SC values ("006", "1003", 89); insert into SC values ("006", "1005", 88 ); insert into SC values ("006", "1001", 78); insert into SC values ("006", "1004", 91 ); insert into SC values ("006", "1006", 100); insert into SC values ("006", "1007", 80 ); insert into SC values ("006", "1008", 83); insert into SC values ("006", "1009", 53 ); insert into SC values ("007", "1003", 91); insert into SC values ("007", "1005", 80 ); insert into SC values ("007", "1007", 95); insert into SC values ("007", "1009", 66 ); insert into SC values ("007", "1008", 71); insert into SC values ("008", "1002", 95 ); insert into SC values ("008", "1004", 90); insert into SC values ("008", "1006", 95 ); insert into SC values ("009", "1001", 90); insert into SC values ("009", "1002", 99 ); insert into SC values ("010", "1002", 92); insert into SC values ("010", "1006", 96 ); insert into SC values ("010", "1007", 91); insert into SC values ("010", "1009", 70 ); insert into SC values ("011", "1001", 91); insert into SC values ("011", "1002", 80 ); insert into SC values ("011", "1004", 75); insert into SC values ("011", "1006", 76 ); insert into SC values ("011", "1008", 41); insert into SC values ("011", "1009", 50 ); insert into SC values ("012", "1001", 90); insert into SC values ("012", "1002", 91 ); insert into SC values ("012", "1003", 75); insert into SC values ("013", "1006", 66 ); insert into SC values ("013", "1008", 41); insert into SC values ("013", "1009", 50 ); insert into SC values ("014", "1004", 81); insert into SC values ("014", "1006", 86 ); insert into SC values ("014", "1007", 91 );

Question: I. Use the standard SQL nested statement to query the student ID and name of the elective course named 'physical'

Select Stu. stu_no, Stu. stu_name from student Stu, (select stu_no from class, scwhere class. cla_no = SC. cla_no and class. cla_name = "physical") selestuwhere Stu. stu_no = selestu. stu_noselect stu_no, stu_name from studentwhere stu_no in (select stu_no from SC, classwhere class. cla_name = "physical" and class. cla_no = SC. cla_no)

Ii. Use standard SQL nested statements to query the name and unit of the student whose elective course number is '123'

select stu_name, stu_workunit from studentwhere student.stu_No in(select stu_No from scwhere cla_No="1002")select student.stu_name,stu_workunit from student,scwhere sc.cla_No="1002" and student.stu_No=sc.stu_No

3. Use standard SQL nested statements to query the names and units of students who do not select the course number as '123'

select stu_name, stu_workunit from studentwhere stu_No Not in(select stu_No from sc where cla_No="1005")

4. Use standard SQL nested statements to query the names and units of trainees who take all courses

select stu_name,stu_workunit from studentwhere stu_No in(select stu_No from (select stu_No,count(*) as numcla from scgroup by stu_No) awhere a.numcla=(select count(*) from class))

5. query the number of students who have selected the course

select count(distinct stu_No) from sc

6. query the student ID and organization of more than 5 Elective Courses

select a.stu_No,stu_workunit from student a,(select stu_No, count(*) as totalnum from scgroup by stu_Nohaving totalnum>5) bwhere a.stu_No=b.stu_Noselect a.stu_No,stu_workunit from student a,(select stu_No, count(*) as totalnum from scgroup by stu_No) bwhere a.stu_No=b.stu_No and b.totalnum>5select stu_No,stu_workunit from student where stu_No in(select stu_No from scgroup by stu_No having count(cla_No)>5)

7. query the scores, names, and scores of the students who have obtained the highest score.

 select stu_name,cla_name,sc.score from sc,student,class, (select stu_No,max(score) as maxscore from sc group by stu_no) b where sc.stu_No=b.stu_No and sc.score=b.maxscore and sc.cla_No=class.cla_No and sc.stu_No=student.stu_No order by stu_name;