數(shù)據庫系統(tǒng)原理

1、數(shù)據庫系統(tǒng)原理實驗報告姓名： 指導教師： 學號： 實驗日期：2016/6/7 一、實驗目的2、用SQL語句建立上述表，自定義主鍵和外鍵，對于student表建立約束條件：ssex僅能取male或female；sage在18和22之間。并完成下面的查詢語句。1、查詢所有選修過“Bibliometrics”課的學生的姓名和成績；mysql select s.sname,sc.grade from student as s,sc where s.sno in(select sno from sc where grade=60 and cno=C05) and sc.grade in(select g

2、rade from sc where grade=60 and cno=C05);2、查詢考試成績不及格的學生的個數(shù)；mysql select count(*) from sc where grade select sname,sno,ssex from student where sname like %z%;4、查詢選修了“Introduction to the Internet”課程的學生的學號及其成績，查詢結果按分數(shù)的降序排列；mysql select sno,grade from sc where cno in (select cno from course where cname

3、like Introd%to%Inter%) order by grade desc;5、查詢“Zuo li”同學選修課程的總學時(time)數(shù)mysql select sum(c.ctime) from course c where cno in (select cno from sc where sno in (select sno from student where sname=Zuo li);6、查詢年齡不大于20歲的學生的平均考試成績；mysql select avg(grade) from sc where sno in (select sno from student wher

4、e sage select distinct count(sno) from sc where cno in (select cno from course where cname like D%Syst%) and sno in (Select sno from student where sdept like compu%);8、查詢同時選修課程“Database System”和“Introduction to the Internet”的學生姓名；mysql select sname from student where sno in (select sno from sc where

5、 cno in (select cno from course where cname like D%S%) union (select cno from course where cname like In%to%the%);9、查詢選修的課程中含有“Wang gang”同學所有選修課程的學生姓名。mysql select sname from student where sno in (select sno from sc where cno in (select cno from sc where sno in (select sno from student where sname=W

6、ang gang); 10、查詢“Information Technology for Information Management”考試成績?yōu)榭盏膶W生姓名及專業(yè)名稱。mysql select sname,sdept from student where sno in (select sno from sc where grade is null); 11、查詢“computer science”專業(yè)學生每個人的選修課總學分。mysql select avg(grade) from sc where sno in (select sno from student where sdept lik

7、e compu%); 12、查詢個人考試平均成績高于專業(yè)平均成績的學生姓名mysql select stu.sname from student as stu where stu.sno in (select sno from sc where (select avg(grade) from sc where sno=stu.sno) (select avg(grade) from sc); 13、查詢個人考試平均成績高于女生平均成績的男生姓名mysql select sname from student as stu where stu.ssex=male and stu.sno in (s

8、elect sno from sc where (select avg(grade) from sc where sno=stu.sno) (select avg(grade) from sc where sno in (select sno from student where ssex=female); 14、查詢比“computer science”專業(yè)所有學生年齡都大的學生姓名。mysql select * from student where sdept like com% and sage in(select max(sage) from student where sdept like com%); 15、查詢考試成績僅有一科不及格學生姓名mysql select stu.sname