本文共 3540 字，大约阅读时间需要 11 分钟。

前言

这个实验很简单，我先讲下大致步骤，然后再解释详细点，再附上实验解答

实验内容

这次实验就是说白了，把外部的txt文件通过sqlite等软件导入到一个数据库里面去,这个数据库就是db3后缀的那个文件

实验难点

.初学者不会使用软件，不知道该怎么操作

相关难点剖析：（利用sqlite）

1.txt文件千万不能有多余的空格2.导入时txt文件里面的分割符要和导入的相一致3.import语句不要分号4.导入的时候是会重复导入的，前面导入的后面还会有5.字符串存储相当严格，连空格都会存储进去

实验解答

我看了几年的实验，似乎都是那50道题，认真学习的同学会发现这些都在PPT上能够找到，如果不认真的就上网找吧，但愿你们能够找到这里来，给我博客增添点人气~~~

11,select sID,sName,GPA from Student where GPA>3.6;    12,select sID,sName from Student where GPA>3.6;    13,select sName,cName from Student,Apply where Student.sID = Apply.sID;    14,select distinct sName,cName from Student,Apply where Student.sID = Apply.sID;(从上条我们就可以看出，去除重复元组是有多强)    15,select sName,GPA,decision from Student,Apply where Student.sID=Apply.sID and cName='Stanford' and major='CS' and sizeHS<1000;    16,select distinct College.cName from College,Apply where College.cName=Apply.cName and major='CS' and enrollment>20000;    17,select sName,College.cName,GPA,state,enrollment from Apply,Student,College where Apply.sID = Student.sID and Apply.cName = College.cName order by GPA desc;    在满足上述条件下想让次要条件按enrollment降序就可以在后面加一句enrollment desc;    18,参照17附加       19,select sID,major from Apply where  major like '%bio%';    20,select * from College,Student;    21，...略    22,select s1.sName,s2.sName from Student s1,Student s2 where s1.sID!=s2.sID and s1.GPA=s2.GPA;(去除重复元素似乎不管用) ----------有点问题    22，23 同    24，select cName from College union all select sName from Student;(含重复)        不含重复版本：       select distinct cName from College union all select distinct sName from Student;    25,select distinct cName as name from College union all select distinct sName  as name from Student order by name;    26,25已经完成    27，select sID from Apply where major=='CS' intersect select sID from Apply where major = 'EE';     28,select sID from Apply where major=='CS' except select sID from Apply where major = 'EE';     29,select sName,sID from Student where sID in (select sID from Apply where major='CS');    30,select distinct sName from Student,Apply where Student.sID= Apply.sID and major='CS';    或者：select distinct sName from Student where sID in(select sID from Apply where major='CS');    31,select distinct sID from Apply where sID in (select sID from Apply where major = 'CS') and sID not in (select sID from Apply where major = 'EE');    32,select cName from College s1 where  not exists (select * from College s2 where s1.enrollment
   
    s2.GPA ) and major='CS';    41，select avg(GPA)  from Student natural join Apply where major='CS';    42,select count(*) from College where enrollment>15000;    43,select count(*)  from Apply  where  cName='Cornell' and sID in (select distinct sID from Apply); //这个有问题,牛逼牛逼    44，select CS.avgGPA-NonCS.avgGPA from (select avg(GPA) as avgGPA from Student natural join Apply where major='CS') as CS,(select avg(GPA) as avgGPA from Student  where sID not in(select sID from Apply where major='CS')) as NonCS;    45,select cName,count(*) from Apply group by cName;    46,select state,sum(enrollment) from College group by state;    47,select cName,major,max(GPA),min(GPA) from Student natural join Apply group by cName,major;   //group by 可以循环使用    48,select sID,sName,count(*) from Apply natural join Student group  by Apply.sID;    50,select cName from Apply group by cName having count(*)<5;
   

转载地址：https://blog.csdn.net/qq_23100787/article/details/48328829 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！