1、實驗一一、創建數據庫stu1 .直接創建（創建后直接刪除）利用“向導”創建（創建后刪除，用drop語句刪除）用 create database 創建二、用create語句創建如下五張數據表，用insert語句插入表格數據。1）表 STUDENTSNOSNAMESSEXSAGECLASS108曾華男1995033105匡明男1795031107王麗女1695033101李軍男1995033109王芳女IS95031103陸君男20950312）表 COURSEenocn：rTLetno3-105計算機導論8253-245操作系統8046-166數據電路8569-888高等數學8313）表SCOR

2、Esnoenodegree1013-105641016-166851033-105921033-245861053-1058S1053-245751073-105911079-888791083-105781086-166811093-105761093-245684）表TEACHERIMO7?JAMETSEXTAGEPROFDEPART804李誠男35副教授計聳機系856張旭男30講師電子工程系825王萍女27助教計聳機系831劉冰女25助教電子工程系5)表GARDElowupprank059E6069D7079C8089B90100A三、用select語句完成如下查詢。1、查詢Studen

3、t表中的所有記錄的Sname、Ssex和Class歹。select sname,ssex,classfrom student2、查詢教師所有的單位即不重復的Depart列。select distinct departfrom teacher3、查詢Student表的所有記錄。select *from student4、查詢Score表中成績在60到80之間的所有記錄。select *from scorewhere degree between 60 and 805、查詢Score表中成績為85, 86或88的記錄。select *from scorewhere degree in (85,86

4、,88)6、查詢Student表中“95031 ”班或性別為女”的同學記錄。select *from studentwhere class =95031or ssex=女7、以Class降序查詢Student表的所有記錄。select *from studentorder by class desc8、以Cno升序、Degree降序查詢Score表的所有記錄。：select *from scoreorder by cno asc,degree desc9、查詢“95031”班的學生人數。select count(distinct sno)from studentwhere class =950

5、31 select avg(degree)from scorewhere cno like 3%group by cno having count(sno) = 510、查詢Score表中的最高分的學生學號和課程號。select sno,cnofrom scorewhere degree =(select max(degree)from score)11、查詢3-105號課程的平均分。select avg(degree)均分from scorewhere cno=3-10512、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。select avg(degree)均分from

6、scorewhere cno like 3%group by cno having count(sno)=513、查詢最低分大于70，最高分小于90的Sno列。select snofrom scoregroup by sno having max(degree)7014、查詢所有學生的Sname、Cno和Degree列。select sname,cno,degreefrom student,scorewhere student.sno=score.sno15、查詢所有學生的Sno、Cname和Degree列。select sno,cname,degreefrom score,coursewhe

7、re o=o16、查詢所有學生的Sname、Cname和Degree列。select sname,cname,degreefrom student,course,scorewhere student.sno=score.snoand o=o17、查詢“95033班所選課程的平均分。select avg(degree)均分from scorewhere sno in(select snofrom studentwhere class=95033)18、查詢選修“3-105”課程且成績高于、109”號同學所有成績的所有同學的記錄。select *from scorewhere cno=3-105and degree(select max(degree)from scorewhere sno=109)19、查詢score中選學一門以上課程的同學中分數為非最高分成