ourses,enrolls
wherecourses.course=enrolls.courseandcourses.coursename!='calculusiv'
结果
Student
148
210
298
348
349
358
410
473
548
558
649
654
10.检索至少选修教师'Dr.Lowe'所开全部课程的学生学号
selectdistinctstudent
fromenrolls,teachers,sections
whereexists
(select*
fromenrolls,teachers,sections
whereteachers.teacher=sections.teacherandsections.course=enrolls.courseandteachers.teachername='Dr.Lowe'
groupbystudent
havingcount(distinctenrolls.course)=(selectcount(distinctenrolls.course)
fromenrolls,teachers,sections
whereteachers.teacher=sections.teacherandsections.course=enrolls.courseandteachers.teachername='Dr.Lowe')
)
结果:
Student
148
210
298
348
349
358
410
473
548
558
649
654
11.检索每门课学生的登记人数,相应的课程名,课程号,分组号
selectcourses.course,sum(numstudents)人数,courses.coursename
fromsections,courses
wherecourses.course=sections.course
groupbycourses.coursename,courses.course
结果:
Course人数coursename
290 3 englishcomposition
450 4 westerncivilization
480 5 compilerwriting
730 6 calulusiv
12.检索选修两门以上课程的学生姓名
selectstudentname
fromstudents,enrolls
wherestudents.student=enrolls.student
groupbystudentname
havingcount(*)>=2
结果:
studentname
Allenthomas
BobDawson
CarolDean
HowardMansfield
JohnAnderson
13.检索只有男生选修的课程和学生名
selectcoursename,studentname
fromstudents,courses,enrolls
wherestudents.student=enrolls.studentandcourses.course=enrolls.courseandsex='M'
结果:
Studentnamecoursename
westerncivilization BobDawson
westerncivilization Allenthomas
calulusiv BobDawson
calulusiv JohnAnderson
englishcomposition HowardMansfield
englishcomposition Allenthomas
compilerwriting HowardMansfield
compilerwriting BillJone
compilerwriting JohnAnderson
14.检索所有学生选修的课程名,学生名,授课教师名,该生成绩
selectdistinctcoursename,studentname,teachername,grade
fromstudents,courses,enrolls,sections,teachers
wherecourses.course=sections.courseandsections.course=enrolls.courseandstudents.student=enrolls.studentandteachers.teacher=sections.teacherandstudents.student=enrolls.student
groupbyenrolls.student,courses.coursename,students.studentname,teachers.teachername,enrolls.grade
结果:
coursename
calulusiv sdudentnameteachernamegrade
BobDawson Dr.Lowe 1
calulusiv CarolDean Dr.Lowe 3
calulusiv JohnAnderson Dr.Lowe 4
calulusiv Susanpowell Dr.Lowe 3
calulusiv SusanPugh Dr.Lowe 2
calulusiv ValShipp Dr.Lowe 3
compilerwriting BillJone Dr.Cooke 2
compilerwriting CarolDean Dr.Cooke 0
compilerwriting HowardMansfield Dr.Cooke 3
compilerwriting JohnAnderson Dr.Cooke 4
englishcomposition Allenthomas Dr.Engle 2
englishcomposition HowardMansfield Dr.Engle 3
westerncivilization Allenthomas Dr.Horn 1
westerncivilization Allenthomas Dr.Olsen 1
westerncivilization BobDawson Dr.Horn 3
westerncivilization BobDawson Dr.Olsen 3
westerncivilization CarolDean Dr.Horn 2
westerncivilization CarolDean Dr.Olsen 2
westerncivilization JanetYhomas Dr.Horn 4
westerncivilization JanetYhomas Dr.Olsen 4
15.删去名为'JoeAdams'的所以记录
delete
fromstudents
wherestudentname='JoeAdams'
delete
fromenrolls
whereenrolls.student=
(selectenrolls.student
fromstudents,enrolls
wherestudents.student=enrolls.studentandstudentname='JoeAdams')
结果:
存在于数据库中
16.把教师'Scango'的编号改为'666'
updateteachers
setteacher='666'
whereteachernamelike'%Scango'
结果:
存在于数据库中
17.统计教师'Engle'教的英语课的学生平均分
selectavg(grade)平均分
fromcourses,enrolls,teachers,sections
wherecourses.course=enrolls.courseandteachers.teacher=sections.teacherandcoursename='englishcomposition'andteachernamelike'%Engle'
结果:
平均分
3
18.统计各门课程的选课人数
selectcoursename,count(numstudents)选课人数
fromcourses,sections
wherecourses.course=sections.course
groupbycoursename
结果:
Coursename选课人数
calulusiv 1
compilerwriting 2
englishcomposition 1
westerncivilization 2
19.统计学生来自的省名
selectstudentname,state
fromstudents
groupbystudentname,students.state
结果:
Studentnamestate
Allenthomas IL
BillJone CA
BobDawson R1
CarolDean MA
HowardMansfield VA
JanetLadd PA
JanetYhomas PA
JohnAnderson NY
Susanpowell PA
SusanPugh CN
ValShipp IL
20.输出如下报表:
学生名
课程名
教师名
成绩
selectstudentname学生姓名,courses.coursename课程名,teachername教师姓名,grade成绩
fromstudents,courses,teachers,enrolls,sections
whereteachers.teacher=sections.teacherandsections.course=enrolls.courseandstudents.student=enrolls.studentandcourses.course=enrolls.course
结果:
学生名课程名
Susanpowell calulusiv教师名成绩
Dr.Lowe 3
BobDawson westerncivilization Dr.Horn 3
BobDawson westerncivilization Dr.Olsen 3
BobDawson calulusiv Dr.Lowe 1
HowardMansfield englishcomposition Dr.Engle 3
HowardMansfield compilerwriting Dr.Cooke 3
SusanPugh calulusiv Dr.Lowe 2
BillJone compilerwriting Dr.Cooke 2
CarolDean westerncivilization Dr.Horn 2
CarolDean westerncivilization Dr.Olsen 2
CarolDean calulusiv Dr.Lowe 3
CarolDean compilerwriting Dr.Cooke 0
Allenthomas englishcomposition Dr.Engle 2
ValShipp calulusiv Dr.Lowe 3
JohnAnderson calulusiv Dr.Lowe 4
JohnAnderson compilerwriting Dr.Cooke 4
JanetYhomas westerncivilization Dr.Horn 4
JanetYhomas westerncivilization Dr.Olsen 4
Allenthomas westerncivilization Dr.Horn 1
Allenthomas westerncivilization Dr.Olsen 1
五、实验小结
通过这次课程设计,我更加深刻的了解和认识sql语言的相关查询,更改,删除等操作。同时,也提高了自己的应用能力。在上机过程中,对自己的错误也有了改进的措施,避免以后工作中错误再次发生。
??
??
??
??
2
上一篇:
PHP课程设计+论文(论文和程序)
下一篇:
经济可持续发展论文:将可持续发展内生化的新经济发展方式