搜索
classes开课Department开课系别Course开课课程Description课程名称max_students课程容量current_students现有学生数量num_credits学分room_id教室Registered_students学生选修Student_id学生号Department开课系别Course开课课程Grade分数Rooms教室Room_id教室号Building大楼Room_number房间号Number_seats座位数Description说明Students学生Id学号First_name名Last_name姓Major专业Current_credits当前已得学分1．找出所有姓中以S.开头的学生。SQL语句：SELECT*FROMstudentsWHEREfirst_nameLIKE‘S*’;返回结果：FIRST_NAMECURRENT_CRLAST_NAMEMAJORScott11.00000SmithComputerScienceShay3.00000ShariatpanahyComputerScience2．找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。SQL语句：SELECTMajorAS专业,COUNT(Major)AS人数,MAX(Current_cr)AS最高学分,MIN(Current_cr)AS最低学分,AVG(Current_cr)AS平均学分,SUM(Current_cr)AS学分总数FROMstudentsgroupBYMajor;返回结果：专业人数最高学分最低学分平均学分学分总数ComputerScience31137.3333333333333322Economics2877.515History344412Music2745.511Nutrition2888163．找出所有教室的座位数，最大教室的座位数、最小座位数。SQL语句：selectsum(number_sea)as座位数,max(number_sea)as最大座位数,min(number_sea)as最小座位数fromrooms;返回结果：座位数最大座位数最小座位数27851000104．找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。SQL语句：selectbuildingas大楼,max(number_sea)as最大教室座位数,min(number_sea)as最小教室座位数,avg(number_sea)as平均座位数,sum(number_sea)as座位总数fromroomsgroupbybuilding;返回结果：大楼最大教室座位数最小教室座位数平均座位数座位总数Building650050162.5650Building71000503751125MusicBuilding10001050510105．找出各课程尚可选修的人数。SQL语句：selectcourseas开课课程,descriptionas课程名称,max_students-current_studentsas尚可选人数fromclasses;返回结果：开课课程课程名称尚可选人数101ComputerScience10150102ComputerScience10232101Economics10150203Economics20315101History10119301History30130100Music100100410Music4101开课课程课程名称尚可选人数307Nutrition307186．找出开课最多的系。SQL语句：selecttop1departmentas开课系别,count(course)as开课数fromregistered_studentsgroupbydepartmentorderby2desc;返回结果：开课系别开课数HIS11//asc按升序排列//desc按降序排列7．找出开课最少的系。SQL语句：selecttop1departmentas开课系别,count(course)as开课数fromregistered_studentsgroupbydepartmentorderby2asc;返回结果：开课系别开课数NUT28．找出选课最多的学生。SQL语句：selectstudent_idas学生号,count(course)as选课数fromregistered_studentsgroupbystudent_idhavingcount(course)=all(selectcount(course)fromregistered_studentsgroupbystudent_id);返回结果：学生号选课数1000039．找出选课最少的学生。SQL语句：selectstudent_idas学生号,count(course)as选课数fromregistered_studentsgroupbystudent_idhavingcount(course)=all(selectcount(course)fromregistered_studentsgroupbystudent_id);返回结果：学生号选课数100011100041100051学生号选课数10007110011110．找出不及格的学生。SQL语句：selectdistinctstudent_idas学生号,first_nameas名,last_nameas姓,gradeas分数fromregistered_students,studentswhereid=student_idandgrade='E';返回结果：学生号名姓分数10006BarbaraBluesE11．找出各课程平均分以下的学生。SQL语句：selectstudent_idas学号,courseas课程,gradeas分数fromregistered_studentswhereasc(grade)=(selectavg(asc(grade))fromregistered_students);返回结果：查询1学号课程分数10003102C10004101C10005101C10006101E10009101D10006410E12．找出各系所占教室的座位数。SQL语句：selectdepartmentas开课系别,sum(number_sea)as座位数fromclasses,roomswhereclasses.room_id=rooms.room_idgroupbydepartment返回结果：开课系别座位数CS550ECN125HIS1050MUS1010NUT5013．分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。SQL语句：1）创建学生历史表（增加入学日期regdate，学习状态status）SQL语句：createtabledemo.students_his(idint,first_namevarchar(20),last_namevarchar(20),majorvarchar(30),current_creditsfloat,regdatedate,statusvarchar(10));返回结果：2）创建选修课程历史表（增加选课日期enrdate字段）SQL语句：Createtabledemo.registered_students_his(student_idint,departmentvarchar(20),courseint,gradevarchar(20),enrdatedate);返回结果：3）导入当前学生状态SQL语句：Insertintostudents_his(id,first_name,last_name,major,current_cre)select*fromstudents;返回结果：idfirst_namelast_namemajorcurrent_crregdatestatus10000ScottSmithComputerScience1110001MargaretMasonHistory410002JoanneJunebugComputerScience810003ManishMurgatroidEconomics810004PatrickPollHistory410005TimothyTallerHistory410006BarbaraBluesEconomics710007DavidDinsmoreMusic410008EsterElegantNutrition810009RoseRiznitMusic710010RitaRazmatazNutrition810011ShayShariatpanahyComputerScience34）更新入学日期，学习状态SQL语句：Updatestudents_hissetregdate='2017-03-01',status='在学';返回结果：idfirst_namelast_namemajorcurrent_crregdatestatus10000ScottSmithComputerScience112017/3/1在学10001MargaretMasonHistory42017/3/1在学10002JoanneJunebugComputerScience82017/3/1在学10003ManishMurgatroidEconomics82017/3/1在学idfirst_namelast_namemajorcurrent_crregdatestatus10004PatrickPollHistory42017/3/1在学10005TimothyTallerHistory42017/3/1在学10006BarbaraBluesEconomics72017/3/1在学10007DavidDinsmoreMusic42017/3/1在学10008EsterElegantNutrition82017/3/1在学10009RoseRiznitMusic72017/3/1在学10010RitaRazmatazNutrition82017/3/1在学10011ShayShariatpanahyComputerScience32017/3/1在学5）导入当前选修信息SQL语句：insertintoregistered_students_his(student_id,department,course,grade)select*fromregistered_students;返回结果：student_iddepartmentcoursegradeenrdate10000CS102A10000HIS101A10000MUS410B10001HIS101B10002CS102B10002HIS101B10003CS102C10003HIS101A10004HIS101C10005HIS101C10006HIS101E10006MUS410E10007HIS101B10008HIS101A10008NUT307A10009HIS101D10009MUS410B10010HIS101A10010NUT307A10011MUS410B6）更新选课日期SQL语句：Updateregistered_students_hissetenrdate=’2017-09-10’;返回结果：student_iddepartmentcoursegradeenrdate10000CS102A2017/9/1010000HIS101A2017/9/1010000MUS410B2017/9/1010001HIS101B2017/9/1010002CS102B2017/9/1010002HIS101B2017/9/1010003CS102C2017/9/1010003HIS