面试官没想到,一个SQL,我可以扯一小时

SQL 小时候可胖了 28℃ 0评论

补充作业一

设有三个关系:

S(SNO, SNAME, AGE, SEX,Sdept)

SC(SNO, CNO, GRADE)

C(CNO, CNAME, TEACHER)

试用关系代数表达式表示下列查询:

1、查询学号为S3学生所学课程的课程名与任课教师名。

2、查询至少选修LIU老师所教课程中一门课的女生姓名。

3、查询WANG同学不学的课程的课程号。

4、查询至少选修两门课程的学生学号。

5、查询选修课程中包含LIU老师所教全部课程的学生学号。

补充作业二

三个关系同上,试用SQL语言表示下列查询:

1、 查询门门课程都及格的学生的学号

方法1:

提示:根据学号分组,就得到每个学生所有的课程成绩,在某个学生这一组成绩里,如果他所有的课程成绩都大于60分则输出该组学生的学号


1
2
Select sno frome sc group by sno having<span class="token punctuation">(<span class="token builtin">min<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">60<span class="token punctuation">)
</span></span></span></span></span></span></span>

2、查询既有课程大于90分又有课程不及格的学生的学号

自身连接:


1
2
Select sno <span class="token keyword">from sc where grade <span class="token operator">&gt;<span class="token number">90 <span class="token operator">and sno <span class="token keyword">in <span class="token punctuation">(select sno <span class="token keyword">from sc where grade<span class="token operator">&lt;<span class="token number">60<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span>

3、查询平均分不及格的课程号和平均成绩


1
2
Select cno <span class="token punctuation">, avg<span class="token punctuation">(GRADE<span class="token punctuation">) <span class="token keyword">from sc group by cno having avg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&lt;<span class="token number">60
</span></span></span></span></span></span></span></span>

查询平均分及格的课程号和课程名


1
2
Select C<span class="token punctuation">.cno <span class="token punctuation">, Cname <span class="token keyword">from SC<span class="token punctuation">,C where C<span class="token punctuation">.cno<span class="token operator">=SC<span class="token punctuation">.cno group by C<span class="token punctuation">.cno having avg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">60
</span></span></span></span></span></span></span></span></span></span></span></span>

4、找出至少选修了2号学生选修过的全部课程的学生

提示:不存在这样的课程y,学生2选修了y,而学生x没有选。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT DISTINCT Sno

   FROM SC <span class="token keyword">as SCX

   WHERE NOT EXISTS

      <span class="token punctuation">(SELECT <span class="token operator">*

       FROM SC <span class="token keyword">as SCY

       WHERE SCY<span class="token punctuation">.Sno <span class="token operator">=‘<span class="token number">2’AND NOT EXISTS

                               <span class="token punctuation">(SELECT <span class="token operator">*

                                  FROM SC SCZ

                          WHERE SCZ<span class="token punctuation">.Sno<span class="token operator">=SCX<span class="token punctuation">.Sno AND SCZ<span class="token punctuation">.Cno<span class="token operator">=SCY<span class="token punctuation">.Cno<span class="token punctuation">)<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

5、求各门课程去掉一个最高分和最低分后的平均分

第一步,求所有成绩的平均分(去掉一个最高分和最低分)


1
2
3
4
select   avg<span class="token punctuation">(GRADE<span class="token punctuation">)   <span class="token keyword">from   SC    
  where   GRADE   <span class="token operator">not   <span class="token keyword">in <span class="token punctuation">(select   top   <span class="token number">1   GRADE   <span class="token keyword">from   SC order   by   GRADE<span class="token punctuation">)  
  <span class="token operator">and     GRADE   <span class="token operator">not   <span class="token keyword">in <span class="token punctuation">(select   top   <span class="token number">1   GRADE   <span class="token keyword">from   SC order   by   GRADE   desc<span class="token punctuation">)  
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

第二步,将所有成绩按各门课程的课程号CNO分组


1
2
3
4
SELECT CNO avg<span class="token punctuation">(GRADE<span class="token punctuation">)   <span class="token keyword">from   SC    
  where   GRADE   <span class="token operator">not   <span class="token keyword">in <span class="token punctuation">(select   top  <span class="token number">1  GRADE   <span class="token keyword">from   SC order   by   GRADE<span class="token punctuation">)  
  <span class="token operator">and     GRADE   <span class="token operator">not   <span class="token keyword">in <span class="token punctuation">(select   top  <span class="token number">1  GRADE   <span class="token keyword">from   SC order   by   GRADE   desc<span class="token punctuation">) group by CNO
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

1、查询7号课程没有考试成绩的学生学号。


1
2
 Select sno fromsc where cno<span class="token operator">=<span class="token string">'7' <span class="token operator">and grade <span class="token keyword">is null
</span></span></span></span>

2、查询7号课程成绩在90分以上或60分以下的学生学号。


1
2
Select sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'7' <span class="token operator">and grade <span class="token operator">not between 60and <span class="token number">9
</span></span></span></span></span></span>

3、查询课程名以“数据”两个字开头的所有课程的课程号和课程名。


1
2
Select cno<span class="token punctuation">,cname <span class="token keyword">from c where cname like <span class="token string">'数据%'
</span></span></span>

4、查询每个学生所有课程的平均成绩,输出学生学号和平均成绩。


1
2
  Select sno<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token keyword">from sc group by sno
</span></span></span></span>

5、查询每门课程的选修人数,输出课程号和选修人数。


1
2
    Selectcno<span class="token punctuation">,count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">) <span class="token keyword">from sc group by cno
</span></span></span></span></span>

6、查询选修7号课程的学生的学号、姓名、性别。


1
2
   Selects<span class="token punctuation">.sno<span class="token punctuation">,sname<span class="token punctuation">,ssex <span class="token keyword">from s<span class="token punctuation">,sc where s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and cno<span class="token operator">=<span class="token string">'7'
</span></span></span></span></span></span></span></span></span></span></span>

或:


1
2
3
4
 Select sno<span class="token punctuation">,sname<span class="token punctuation">,ssex <span class="token keyword">from s where sno <span class="token keyword">in

              <span class="token punctuation">( Select sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'7' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

7、查询选修7号课程的学生的平均年龄。


1
2
    Selectavg<span class="token punctuation">(sage<span class="token punctuation">) <span class="token keyword">from s<span class="token punctuation">,sc where s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and cno<span class="token operator">=<span class="token string">'7'
</span></span></span></span></span></span></span></span></span></span>

或:


1
2
3
4
 Select avg<span class="token punctuation">(sage<span class="token punctuation">) <span class="token keyword">from s where sno <span class="token keyword">in

              <span class="token punctuation">(Select sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'7' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

8、查询有30名以上学生选修的课程号。


1
2
 Select cno fromsc group by cno having count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">&gt;<span class="token number">30
</span></span></span></span></span>

9、查询至今没有考试不及格的学生学号。


1
2
3
4
    Select distinctsno <span class="token keyword">from sc where sno <span class="token operator">not <span class="token keyword">in

         <span class="token punctuation">( Select sno <span class="token keyword">from sc where grade<span class="token operator">&lt;<span class="token number">60 <span class="token punctuation">)
</span></span></span></span></span></span></span></span>

或:


1
2
Select sno <span class="token keyword">from sc group by sno havingmin<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">60
</span></span></span></span></span>

补充三

1、找出选修课程号为C2的学生学号与成绩。


1
2
Select sno<span class="token punctuation">,grade <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'C2'
</span></span></span></span>

2、找出选修课程号为C4的学生学号与姓名。


1
2
Selects<span class="token punctuation">.sno<span class="token punctuation">,sname <span class="token keyword">from s<span class="token punctuation">,sc where s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and cno<span class="token operator">=<span class="token string">'C4'
</span></span></span></span></span></span></span></span></span></span>

注意本题也可以用嵌套做

思考本题改为“找出选修课程号为C4的学生学号、姓名与成绩”后还能用嵌套做吗?

3、找出选修课程名为 Maths 的学生学号与姓名。


1
2
3
   Selects<span class="token punctuation">.sno<span class="token punctuation">,sname <span class="token keyword">from s<span class="token punctuation">,sc<span class="token punctuation">,c
    where  s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and c<span class="token punctuation">.cno<span class="token operator">=sc<span class="token punctuation">.cno andcname<span class="token operator">=<span class="token string">'Maths'
</span></span></span></span></span></span></span></span></span></span></span></span></span></span>

注意本题也可以用嵌套做

4、找出选修课程号为C2或C4的学生学号。


1
2
  Select distinctsno <span class="token keyword">from sc where cno <span class="token keyword">in <span class="token punctuation">(<span class="token string">'C2'<span class="token punctuation">,<span class="token string">'C4'<span class="token punctuation">)
</span></span></span></span></span></span></span>

或:


1
2
Select distinct sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'C2' <span class="token operator">or cno<span class="token operator">=<span class="token string">'C4'
</span></span></span></span></span></span>

5、找出选修课程号为C2和C4的学生学号。


1
2
3
4
   Select sno fromsc where cno<span class="token operator">=<span class="token string">'C2' <span class="token operator">and sno <span class="token keyword">in

         <span class="token punctuation">( Select sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'C4' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

注意本题也可以用连接做

思考:

Select distinct sno from sc where cno=‘C2’ andcno='C4’正确吗?

6、找出不学C2课程的学生姓名和年龄。


1
2
3
4
    Selectsname<span class="token punctuation">,sage <span class="token keyword">from s where sno <span class="token operator">not <span class="token keyword">in

         <span class="token punctuation">( Selectsno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'C2'
</span></span></span></span></span></span></span></span>

或:


1
2
3
4
 Select sname<span class="token punctuation">,sage <span class="token keyword">from s where <span class="token operator">not exists

              <span class="token punctuation">(Select <span class="token operator">* <span class="token keyword">from sc where sno<span class="token operator">=s<span class="token punctuation">.sno <span class="token operator">and cno<span class="token operator">=<span class="token string">'C2' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span>

7、找出选修了数据库课程的所有学生姓名。(同3)


1
2
3
4
  Select snamefrom s<span class="token punctuation">,sc<span class="token punctuation">,c

where  s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.snoand c<span class="token punctuation">.cno<span class="token operator">=sc<span class="token punctuation">.cno <span class="token operator">and cname<span class="token operator">=<span class="token string">'数据库'
</span></span></span></span></span></span></span></span></span></span></span>

8、找出数据库课程不及格的女生姓名。


1
2
3
4
5
6
7
8
9
10
11
12
13
   连接:Select sname <span class="token keyword">from s<span class="token punctuation">,sc<span class="token punctuation">,c
         where  s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno andc<span class="token punctuation">.cno<span class="token operator">=sc<span class="token punctuation">.cno <span class="token operator">and cname<span class="token operator">=<span class="token string">'数据库'

                <span class="token operator">and grade<span class="token operator">&lt;<span class="token number">60 <span class="token operator">and ssex<span class="token operator">=<span class="token string">'女'

    嵌套:Select sname <span class="token keyword">from s where ssex<span class="token operator">=<span class="token string">'女' <span class="token operator">and  sno <span class="token keyword">in

               <span class="token punctuation">(Select sno <span class="token keyword">from sc where grade<span class="token operator">&lt;<span class="token number">60 <span class="token operator">and cno <span class="token keyword">in

                     <span class="token punctuation">( Select cno <span class="token keyword">from c where cname<span class="token operator">=<span class="token string">'数据库' <span class="token punctuation">)

               <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

9、找出各门课程的平均成绩,输出课程名和平均成绩。


1
2
3
4
  Selectcname<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from sc<span class="token punctuation">,c

    wherec<span class="token punctuation">.cno<span class="token operator">=sc<span class="token punctuation">.cno  group by c<span class="token punctuation">.cno<span class="token punctuation">,cname
</span></span></span></span></span></span></span></span></span></span>

思考本题也可以用嵌套做吗?

10、找出各个学生的平均成绩,输出学生姓名和平均成绩。


1
2
3
4
   Selectsname<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from s<span class="token punctuation">,sc

    wheres<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno group by s<span class="token punctuation">.sno<span class="token punctuation">,sname
</span></span></span></span></span></span></span></span></span></span>

思考本题也可以用嵌套做吗?

11、找出至少有30个学生选修的课程名。


1
2
3
4
 Select cnamefrom c where cno <span class="token keyword">in

         <span class="token punctuation">( Selectcno <span class="token keyword">from sc group by cno having count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">30 <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

注意本题也可以用连接做

12、找出选修了不少于3门课程的学生姓名。


1
2
3
4
  Select snamefrom s where sno <span class="token keyword">in

         <span class="token punctuation">( Selectsno <span class="token keyword">from sc group by sno having count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">3 <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

注意本题也可以用连接做

13、找出各门课程的成绩均不低于90分的学生姓名。


1
2
3
4
   Select snamefrom s<span class="token punctuation">,sc where s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno

         group bys<span class="token punctuation">.sno<span class="token punctuation">,sname having <span class="token builtin">min<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">90
</span></span></span></span></span></span></span></span></span></span></span>

方法二:


1
2
3
4
Select sname <span class="token keyword">from s where sno <span class="token operator">not <span class="token keyword">in

         <span class="token punctuation">( Selectsno <span class="token keyword">from sc where grade<span class="token operator">&lt;<span class="token number">90 <span class="token punctuation">)
</span></span></span></span></span></span></span></span>

只要有一门不小于90分就会输出该学生学号

14、找出数据库课程成绩不低于该门课程平均分的学生姓名。


1
2
3
4
5
6
7
8
9
10
    Select snamefrom s<span class="token punctuation">,sc<span class="token punctuation">,c

    where  s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and sc<span class="token punctuation">.cno<span class="token operator">=c<span class="token punctuation">.cno <span class="token operator">and cname<span class="token operator">=<span class="token string">'数据库' <span class="token operator">and grade<span class="token operator">&gt;

         <span class="token punctuation">( Selectavg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from sc<span class="token punctuation">,c

           where sc<span class="token punctuation">.cno<span class="token operator">=c<span class="token punctuation">.cnoand cname<span class="token operator">=<span class="token string">'数据库'

         <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

15、找出各个系科男女学生的平均年龄和人数。


1
2
    Selectsdept<span class="token punctuation">,ssex<span class="token punctuation">,avg<span class="token punctuation">(sage<span class="token punctuation">)<span class="token punctuation">,count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">) <span class="token keyword">from s group by sdept<span class="token punctuation">,ssex
</span></span></span></span></span></span></span></span></span></span>

16、找出计算机系(JSJ)课程平均分最高的学生学号和姓名。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
    Selects<span class="token punctuation">.sno<span class="token punctuation">,sname <span class="token keyword">from s<span class="token punctuation">,sc where s<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and sdept<span class="token operator">=<span class="token string">'JSJ'

    group bys<span class="token punctuation">.sno<span class="token punctuation">,sname

    havingavg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token operator">&gt;=ALL

         <span class="token punctuation">( Selectavg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from s<span class="token punctuation">,sc

           wheres<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and sdept<span class="token operator">=<span class="token string">'JSJ'

           group bys<span class="token punctuation">.sno

         <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

17、(补充)查询每门课程的及格率。

本题可以分三步做:

第1步:得到每门课的选修人数


1
2
3
4
   createview  v_all<span class="token punctuation">(cno<span class="token punctuation">,cnt<span class="token punctuation">)

         <span class="token keyword">as selectcno<span class="token punctuation">, count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">) <span class="token keyword">from sc group by cno
</span></span></span></span></span></span></span></span></span>

第2步:得到每门课及格人数


1
2
3
4
5
  createview  v_pass<span class="token punctuation">(cno<span class="token punctuation">,cnt_pass<span class="token punctuation">)

         <span class="token keyword">as selectcno<span class="token punctuation">, count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">) <span class="token keyword">from sc where grade<span class="token operator">&gt;=<span class="token number">60 group by cno

</span></span></span></span></span></span></span></span></span></span></span>

第3步:每门课的及格人数/每门课选修人数


1
2
3
4
 selectv_all<span class="token punctuation">.cno<span class="token punctuation">, cnt_pass<span class="token operator">*<span class="token number">100<span class="token operator">/cnt  <span class="token keyword">from  v_all<span class="token punctuation">, v_pass

     where v_all<span class="token punctuation">.cno <span class="token operator">= v_pass<span class="token punctuation">.cno
</span></span></span></span></span></span></span></span></span></span>

18、查询平均分不及格的学生的学号,姓名,平均分。


1
2
3
4
5
6
7
8
   Selectsc<span class="token punctuation">.sno<span class="token punctuation">,sname<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from student<span class="token punctuation">,sc

    wherestudent<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno

    group bysc<span class="token punctuation">.sno<span class="token punctuation">,sname

    havingavg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&lt;<span class="token number">60
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

思考本题也可以用嵌套做吗?

19、查询平均分不及格的学生人数。


1
2
3
4
5
6
   Select count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token keyword">from student

    where sno <span class="token keyword">in

         <span class="token punctuation">( selectsno <span class="token keyword">from sc group by sno having avg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&lt;<span class="token number">60 <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span>

下面是一个典型的错误


1
2
Select count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">) <span class="token keyword">from sc group by sno havingavg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&lt;<span class="token number">60
</span></span></span></span></span></span></span></span>

这是每个学生有几门不及格的数目

补充四

1、查询工资在1000到3000元之间的男性业务员的姓名和办公室编号。


1
2
3
4
    SelectYname<span class="token punctuation">,Ono <span class="token keyword">from YWY

    where Salarybetween <span class="token number">1000 <span class="token operator">and <span class="token number">3000 <span class="token operator">and Ysex<span class="token operator">=<span class="token string">'男'
</span></span></span></span></span></span></span></span>

2、查询各个办公室的业务员人数,输出办公室编号和对应的人数。


1
2
 SelectOno<span class="token punctuation">,count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">) <span class="token keyword">from YWY group by Ono
</span></span></span></span></span>

3、查询每个客户在2002年5月购买的总金额,输出客户号和相应的总金额。


1
2
3
4
5
6
   SelectKno<span class="token punctuation">,<span class="token builtin">sum<span class="token punctuation">(Fmoney<span class="token punctuation">) <span class="token keyword">from FP

    where Fdatebetween <span class="token string">'2002.5.1' <span class="token operator">and <span class="token string">'2002.5.31'

    group by Kno
</span></span></span></span></span></span></span></span>

4、查询2002年5月购买次数超过5次的所有客户号,且按客户号升序排序。


1
2
3
4
5
6
7
8
9
10
  Select Kno fromFP

    where Fdatebetween <span class="token string">'2002.5.1' <span class="token operator">and <span class="token string">'2002.5.31'

    group by Kno

    havingcount<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">&gt;<span class="token number">5

    order by KnoASC
</span></span></span></span></span></span></span></span>

5、查询各办公室男性和女性业务员的平均工资。


1
2
   SelectOno<span class="token punctuation">,Ysex<span class="token punctuation">,avg<span class="token punctuation">(Salary<span class="token punctuation">) <span class="token keyword">from YWY group by Ono<span class="token punctuation">,Ysex
</span></span></span></span></span></span>

6、查询2002年5月曾经在王海亮业务员手中购买过商品的客户号、 客户姓名和联系电话。


1
2
3
4
5
6
7
8
9
10
  SelectKno<span class="token punctuation">,Kname<span class="token punctuation">,Phone <span class="token keyword">from KH where Kno <span class="token keyword">in

         <span class="token punctuation">( SelectKno <span class="token keyword">from FP

           whereFdate between <span class="token string">'2002.5.1' <span class="token operator">and <span class="token string">'2002.5.31' <span class="token operator">and Yno <span class="token keyword">in

                      <span class="token punctuation">( Select Yno <span class="token keyword">from YWY where Yname<span class="token operator">=<span class="token string">'王海亮' <span class="token punctuation">)

         <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

注意本题也可以用连接做

7、查询所有工资比1538号业务员高的业务员的编号、姓名和工资。


1
2
3
4
  SelectYno<span class="token punctuation">,Yname<span class="token punctuation">,Salary <span class="token keyword">from YWY where Salary <span class="token operator">&gt;

         <span class="token punctuation">( SelectSalary <span class="token keyword">from YWY where Yno<span class="token operator">=<span class="token string">'1538' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

8、查询所有与1538号业务员在同一个办公室的其他业务员的编号和姓名。


1
2
3
4
    SelectYno<span class="token punctuation">,Yname <span class="token keyword">from YWY where Yno<span class="token operator">!=<span class="token string">'1538' <span class="token operator">and Ono <span class="token keyword">in

         <span class="token punctuation">( SelectOno <span class="token keyword">from YWY where Yno<span class="token operator">=<span class="token string">'1538' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span>

9、查询销售总金额最高的业务员的编号。


1
2
3
4
    Select Yno fromFP group by Yno having <span class="token builtin">sum<span class="token punctuation">(Fmoney<span class="token punctuation">) <span class="token operator">&gt;=ALL

         <span class="token punctuation">( Selectsum<span class="token punctuation">(Fmoney<span class="token punctuation">) <span class="token keyword">from FP group by Yno <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

10、查询所有业务员的编号、姓名、工资以及工资比他高的其他业务员的平均工资。

利用自连接


1
2
3
4
5
6
7
8
    SelectY1<span class="token punctuation">.Yno<span class="token punctuation">,Y1<span class="token punctuation">.Yname<span class="token punctuation">,Y1<span class="token punctuation">.Salary<span class="token punctuation">,avg<span class="token punctuation">(Y2<span class="token punctuation">.Salary<span class="token punctuation">)

    <span class="token keyword">from   YWY Y1<span class="token punctuation">, YWY Y2

    where  Y1<span class="token punctuation">.Salary <span class="token operator">&lt; Y2<span class="token punctuation">.Salary

    group by  Y1<span class="token punctuation">.Yno
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

补充五

1、找出每个班级的班级代码、学生人数、平均成绩。


1
2
  SelectBJDM<span class="token punctuation">,count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token punctuation">,avg<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC group by BJDM
</span></span></span></span></span></span></span></span>

2、找出每个学生的班级代码、学生姓名、考试科目数、总成绩。


1
2
3
4
    SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token punctuation">,<span class="token builtin">sum<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC

    group byBJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM
</span></span></span></span></span></span></span></span></span></span></span></span>

3、输出一张表格,每位学生对应一条记录,包括字段:班级代码、学生姓名、语文成绩、数学成绩、外语成绩。


1
2
3
4
5
6
7
8
9
10
   SelectSC1<span class="token punctuation">.BJDM<span class="token punctuation">,SC1<span class="token punctuation">.XSXM<span class="token punctuation">,SC1<span class="token punctuation">.CJ<span class="token punctuation">,SC2<span class="token punctuation">.CJ<span class="token punctuation">,SC3<span class="token punctuation">.CJ

    <span class="token keyword">from  SC SC1<span class="token punctuation">, SC SC2<span class="token punctuation">, SC SC3

    whereSC1<span class="token punctuation">.BJDM<span class="token operator">=SC2<span class="token punctuation">.BJDM <span class="token operator">and SC1<span class="token punctuation">.BNXH<span class="token operator">=SC2<span class="token punctuation">.BNXH <span class="token operator">and

         SC2<span class="token punctuation">.BJDM<span class="token operator">=SC3<span class="token punctuation">.BJDM <span class="token operator">and SC2<span class="token punctuation">.BNXH<span class="token operator">=SC3<span class="token punctuation">.BNXH <span class="token operator">and

          SC1<span class="token punctuation">.KM<span class="token operator">=<span class="token string">'语文' <span class="token operator">and SC2<span class="token punctuation">.KM<span class="token operator">=<span class="token string">'数学' <span class="token operator">and SC3<span class="token punctuation">.KM<span class="token operator">=<span class="token string">'外语'
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

4、输出一张表格,有成绩低于60分的每位学生对应一条记录,包括字段:班级代码、学生姓名、最低成绩。


1
2
3
4
5
6
7
8
9
10
    SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,<span class="token builtin">min<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC

    where  CJ<span class="token operator">&lt;<span class="token number">60 group by BJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM

    或:  SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,<span class="token builtin">min<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC

          group byBJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM

          havingmin<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token operator">&lt;<span class="token number">60
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

5、输出一张表格,有成绩低于60分的每位学生对应一条记录,包括字段: 班级代码、学生姓名、最高成绩、平均成绩。


1
2
3
4
5
6
SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,<span class="token builtin">max<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC

    group byBJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM

    havingmin<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token operator">&lt;<span class="token number">60
</span></span></span></span></span></span></span></span></span></span></span></span>

请思考下列做法是否正确:


1
2
3
4
    SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,<span class="token builtin">max<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token punctuation">,avg<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC

         where  CJ<span class="token operator">&lt;<span class="token number">60 group byBJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM
</span></span></span></span></span></span></span></span></span></span></span></span></span>

6、输出一张表格,所有成绩都不低于60分的每位学生对应一条记录,包括字段:班级代码、学生姓名、平均成绩。


1
2
3
4
5
6
 SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,avg<span class="token punctuation">(CJ<span class="token punctuation">) <span class="token keyword">from SC

    group by BJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM

    havingmin<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token operator">&gt;=<span class="token number">60
</span></span></span></span></span></span></span></span></span></span></span>

7、输出一张表格,每一位学生对应一条记录,包括字段:班级代码、学生姓名、去掉一个最低分后的平均成绩。


1
2
3
4
    SelectBJDM<span class="token punctuation">,XSXM<span class="token punctuation">,<span class="token punctuation">(<span class="token builtin">sum<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token operator">-<span class="token builtin">min<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token punctuation">)<span class="token operator">/<span class="token punctuation">(count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">-<span class="token number">1<span class="token punctuation">) <span class="token keyword">from SC

    group byBJDM<span class="token punctuation">,BNXH<span class="token punctuation">,XSXM
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

8、输出一张表格,每门科目对应一条记录,包括字段: 科目、去掉一个最低分后的平均成绩。


1
2
3
4
  Select KM<span class="token punctuation">,<span class="token punctuation">(<span class="token builtin">sum<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token operator">-<span class="token builtin">min<span class="token punctuation">(CJ<span class="token punctuation">)<span class="token punctuation">)<span class="token operator">/<span class="token punctuation">(count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">-<span class="token number">1<span class="token punctuation">)<span class="token keyword">from SC

    group by KM
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

补充六

1、查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。


1
2
3
4
5
6
  Selectsno<span class="token punctuation">,sname<span class="token punctuation">,sage <span class="token keyword">from student

    where sagebetween <span class="token number">19 <span class="token operator">and <span class="token number">21 <span class="token operator">and ssex<span class="token operator">=<span class="token string">'女'

    order by sagedesc
</span></span></span></span></span></span></span></span></span>

2、查询姓名中有“明”字的学生人数。


1
2
3
4
   Select count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token keyword">from student

    where snamelike <span class="token string">"%明%"
</span></span></span></span></span>

3、查询1001课程没有成绩的学生的学号。


1
2
    Select sno fromsc where cno<span class="token operator">=<span class="token string">'1001' <span class="token operator">and grade <span class="token keyword">is null
</span></span></span></span>

4、查询JSJ、SX、WL系的学生学号,姓名,结果按系及学号排列。


1
2
3
4
5
6
  Selectsno<span class="token punctuation">,sname<span class="token punctuation">,sdept <span class="token keyword">from student

    where sdept <span class="token keyword">in<span class="token punctuation">( <span class="token string">'JSJ'<span class="token punctuation">, <span class="token string">'SX'<span class="token punctuation">, <span class="token string">'WL' <span class="token punctuation">)

    order bysdept<span class="token punctuation">,sno
</span></span></span></span></span></span></span></span></span></span></span></span>

5、计算每一门课的总分、平均分,最高分、最低分。


1
2
3
4
5
6
 Selectcno<span class="token punctuation">,<span class="token builtin">sum<span class="token punctuation">(grade<span class="token punctuation">)<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token punctuation">,<span class="token builtin">max<span class="token punctuation">(grade<span class="token punctuation">)<span class="token punctuation">,<span class="token builtin">min<span class="token punctuation">(grade<span class="token punctuation">)

    <span class="token keyword">from sc

    group by cno
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

6、查询平均分大于90分的男学生学号及平均分。

连接:


1
2
3
4
5
6
7
8
    selectsc<span class="token punctuation">.sno<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from student<span class="token punctuation">,sc

    wherestudent<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and ssex<span class="token operator">=’男’

    group by sc<span class="token punctuation">.sno

    havingavg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&gt;<span class="token number">90
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

嵌套:


1
2
3
4
5
6
7
8
  selectsno<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from sc

    where sno <span class="token keyword">in <span class="token punctuation">(select sno <span class="token keyword">from student where ssex<span class="token operator">=<span class="token string">'男'<span class="token punctuation">)

    group by sno

    havingavg<span class="token punctuation">(grade<span class="token punctuation">)<span class="token operator">&gt;<span class="token number">90
</span></span></span></span></span></span></span></span></span></span></span></span></span></span>

7、查询选修课程超过2门的学生姓名。


1
2
3
4
5
6
7
8
select snamefrom student<span class="token punctuation">,sc

    where student<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno

    group bysc<span class="token punctuation">.sno<span class="token punctuation">,sname

    havingcount<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">&gt;<span class="token number">2
</span></span></span></span></span></span></span></span></span></span></span>

本题也可以用嵌套做

8、查询 JSJ 系的学生选修的课程号。


1
2
3
4
  Select distinctcno <span class="token keyword">from student<span class="token punctuation">,sc

    where  student<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and sdept<span class="token operator">=<span class="token string">'JSJ'
</span></span></span></span></span></span></span></span>

本题也可以用嵌套做

9、查询选修1002课程的学生的学生姓名(用连接和嵌套2种方法)


1
2
3
4
5
6
7
8
连接:Select sname <span class="token keyword">from student<span class="token punctuation">,sc

          wherestudent<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and cno<span class="token operator">=<span class="token string">'1002'

    嵌套:Select sname <span class="token keyword">from student where sno <span class="token keyword">in

              <span class="token punctuation">(select sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'1002' <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

10、查询学生姓名以及他选修课程的课程号及成绩。


1
2
3
4
    Selectsname<span class="token punctuation">,cno<span class="token punctuation">,grade <span class="token keyword">from student<span class="token punctuation">,sc

    wherestudent<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno
</span></span></span></span></span></span></span>

思考本题也可以用嵌套做吗?

11、查询选修“数据库原理”课且成绩 80 以上的学生姓名(用连接和嵌套2种方法)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
    连接:Select sname <span class="token keyword">from student<span class="token punctuation">,sc<span class="token punctuation">,course

          wherestudent<span class="token punctuation">.sno<span class="token operator">=sc<span class="token punctuation">.sno <span class="token operator">and sc<span class="token punctuation">.cno<span class="token operator">=course<span class="token punctuation">.cno <span class="token operator">and

               cname<span class="token operator">=<span class="token string">'数据库原理' <span class="token operator">and grade<span class="token operator">&gt;<span class="token number">80

    嵌套:Select sname <span class="token keyword">from student where sno <span class="token keyword">in

               <span class="token punctuation">(select sno <span class="token keyword">from sc where grade<span class="token operator">&gt;<span class="token number">80 <span class="token operator">and cno <span class="token keyword">in

                    <span class="token punctuation">( select cno <span class="token keyword">from course where cname<span class="token operator">=<span class="token string">'数据库原理' <span class="token punctuation">)

               <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

14、查询没有选修1002课程的学生的学生姓名。


1
2
3
4
5
6
7
8
9
10
 Select snamefrom student

    where sno notin <span class="token punctuation">( select sno <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'1002'<span class="token punctuation">)

    或: select sname <span class="token keyword">from student

         where notexists

              <span class="token punctuation">(select <span class="token operator">* <span class="token keyword">from sc where cno<span class="token operator">=<span class="token string">'1002' <span class="token operator">and sno<span class="token operator">=student<span class="token punctuation">.sno<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

思考本题也可以用一般的连接做吗?

15、查询平均分最高的学生学号及平均分。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
   Selectsno<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">)

    <span class="token keyword">from sc

    group by sno

    havingavg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token operator">&gt;=ALL <span class="token punctuation">( Select avg<span class="token punctuation">(grade<span class="token punctuation">)

                              <span class="token keyword">from sc

                              group by sno

                            <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span>

16、查询每门课程成绩都高于该门课程平均分的学生学号。

可以先计算每门课程平均分


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  create viewc_avg<span class="token punctuation">(cno<span class="token punctuation">,avg_grade<span class="token punctuation">)

         <span class="token keyword">as selectcno<span class="token punctuation">,avg<span class="token punctuation">(grade<span class="token punctuation">) <span class="token keyword">from sc group by cno

    再查询

    Select distinctsno <span class="token keyword">from sc

    where sno notin <span class="token punctuation">( Select sno <span class="token keyword">from sc<span class="token punctuation">,c_avg

                      where sc<span class="token punctuation">.cno<span class="token operator">=c_avg<span class="token punctuation">.cno <span class="token operator">and grade<span class="token operator">&lt;avg_grade

                    <span class="token punctuation">)

   <span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">==<span class="token operator">=

    SELECT DISTINCT Sno

    FROM SC SC1

    WHERE SC1<span class="token punctuation">.SnoNOT IN

          <span class="token punctuation">( SELECT SC2<span class="token punctuation">.Sno

            FROM SC SC2

            WHERE SC2<span class="token punctuation">.Grade <span class="token operator">&lt;=

                 <span class="token punctuation">( SELECT AVG<span class="token punctuation">(SC3<span class="token punctuation">.Grade<span class="token punctuation">)

                   FROM SC SC3

                   WHERE SC3<span class="token punctuation">.Cno<span class="token operator">=SC2<span class="token punctuation">.Cno

                 <span class="token punctuation">)

          <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

或:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 SELECT DISTINCTSno

    FROM SC SC1

    WHERE NOTEXISTS

          <span class="token punctuation">(SELECT <span class="token operator">*

            FROM SC SC2

            WHERE SC2<span class="token punctuation">.Sno<span class="token operator">=SC1<span class="token punctuation">.Sno AND SC2<span class="token punctuation">.Grade <span class="token operator">&lt;=

                       <span class="token punctuation">(SELECT AVG<span class="token punctuation">(SC3<span class="token punctuation">.Grade<span class="token punctuation">)

                         FROM SC SC3

                         WHERE SC3<span class="token punctuation">.Cno<span class="token operator">=SC2<span class="token punctuation">.Cno

                       <span class="token punctuation">)

          <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

(3)检索至少选修LIU老师所授课程中一门课程的女学生姓名。


1
2
3
4
5
6
7
8
9
10
 SELECT SNAME
    FROM S
    WHERE SEX<span class="token operator">=‘F’ AND S<span class="token comment"># IN
     <span class="token punctuation">(SELECT S<span class="token comment">#
     FROM SC
     WHERE C<span class="token comment"># IN
      <span class="token punctuation">(SELECT C<span class="token comment">#
      FROM C
      WHERE TEACHER<span class="token operator">=‘LIU’<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

NOTICE:有多种写法,比如联接查询写法:


1
2
3
4
5
6
 SELECT SNAME
    FROM S<span class="token punctuation">,SC<span class="token punctuation">,C
    WHERE SEX<span class="token operator">=‘F’ AND SC<span class="token punctuation">.S<span class="token comment">#=S.S#
    AND SC<span class="token punctuation">.C<span class="token comment">#=C.C#
    AND TEACHER<span class="token operator">=<span class="token string">'LIU'
</span></span></span></span></span></span></span></span></span>

但上一种写法更好一些。

(4)检索WANG同学不学的课程的课程号。


1
2
3
4
5
6
7
8
9
10
  SELECT C<span class="token comment">#
    FROM C
    WHERE C<span class="token comment"># NOT IN
     <span class="token punctuation">(SELECT C<span class="token comment">#
     FROM SC
     WHERE S<span class="token comment"># IN
      <span class="token punctuation">(SELECT S<span class="token comment">#
      FROM S
      WHERE SNAME<span class="token operator">=<span class="token string">'WANG'<span class="token punctuation">)<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span>

(5)检索至少选修两门课程的学生学号。


1
2
3
4
 SELECT DISTINCT X<span class="token punctuation">.SNO
    FROM SC X<span class="token punctuation">,SC Y
    WHERE X<span class="token punctuation">.SNO<span class="token operator">=Y<span class="token punctuation">.SNO AND X<span class="token punctuation">.CNO<span class="token operator">&lt;&gt;Y<span class="token punctuation">.CNO
</span></span></span></span></span></span></span></span>

Notice:对表SC进行自连接,X,Y是SC的两个别名。

(6)检索全部学生都选修的课程的课程号与课程名。


1
2
3
4
5
6
7
8
9
10
  SELECT C<span class="token comment">#,CNAME
    FROM C
    WHERE NOT EXISTS
     <span class="token punctuation">(SELECT <span class="token operator">*
     FROM S
     WHERE S<span class="token comment"># NOT IN
      <span class="token punctuation">(SELECT <span class="token operator">*
      FROM SC
      WHERE  SC<span class="token punctuation">.C<span class="token comment">#=C.C#))
</span></span></span></span></span></span></span></span>

要从语义上分解:(1)选择课程的课程号与课程名,不存在不选这门课的同学。

其中,“不选这门课的同学”可以表示为:


1
2
3
4
5
6
7
8
9
10
11
12
SELECT <span class="token operator">*

 FROM  S

 WHERE  S<span class="token comment"># NOT IN

  (SELECT  <span class="token operator">*

      FROM SC  

      WHERE  SC<span class="token punctuation">.C<span class="token comment">#=C.C#)
</span></span></span></span></span>

或者


1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT <span class="token operator">*

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  <span class="token operator">*

      FROM SC  

      WHERE S<span class="token punctuation">.S<span class="token comment">#=C.S# AND

SC<span class="token punctuation">.C<span class="token comment">#=C.C# )
</span></span></span></span></span></span>

(7)检索选修课程包含LIU老师所授课的学生学号。


1
2
3
4
5
6
7
 SELECT DISTINCT S<span class="token comment">#
     FROM SC
     WHERE C<span class="token comment"># IN
      <span class="token punctuation">(SELECT C<span class="token comment">#
      FROM C
      WHERE TEACHER<span class="token operator">=<span class="token string">'LIU'<span class="token punctuation">)<span class="token punctuation">)   
</span></span></span></span></span></span></span></span>

3.3 设有两个基本表R(A,B,C)和S(D,E,F),试用SQL查询语句表达下列关系代数表达式:

(1)πA?(2)σB='17’?(3)R×S(4))πA,F(σC=D(R×S))

(1)SELECT A FROM R

(2)SELECT * FROM R WHERE B=‘17’

(3)SELECT A,B,C,D,E,F FROM R,S

(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4 设有两个基本表R(A,B,C)和S(A,B,C)试用SQL查询语句表达下列关系代数表达式:

(1)R∪S  (2)R∩S  (3)R-S  (4)πA,B?πB,C(S)

(1)


1
2
3
4
5
6
SELECT A<span class="token punctuation">,B<span class="token punctuation">,C
      FROM R
      UNION
      SELECT A<span class="token punctuation">,B<span class="token punctuation">,C
      FROM S
</span></span></span></span>

(2)


1
2
3
4
5
6
  SELECT A<span class="token punctuation">,B<span class="token punctuation">,C
      FROM R
      INTERSECT
      SELECT A<span class="token punctuation">,B<span class="token punctuation">,C
      FROM S
</span></span></span></span>

(3)


1
2
3
4
5
6
7
SELECT A<span class="token punctuation">,B<span class="token punctuation">,C
      FROM R
      WHERE NOT EXISTS
       <span class="token punctuation">(SELECT A<span class="token punctuation">,B<span class="token punctuation">,C
       FROM S
       WHERE R<span class="token punctuation">.A<span class="token operator">=S<span class="token punctuation">.A AND R<span class="token punctuation">.B<span class="token operator">=S<span class="token punctuation">.B AND R<span class="token punctuation">.C<span class="token operator">=S<span class="token punctuation">.C<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

(4)


1
2
3
4
SELECT R<span class="token punctuation">.A<span class="token punctuation">,R<span class="token punctuation">.B<span class="token punctuation">,S<span class="token punctuation">.C
      FROM R<span class="token punctuation">,S
      WHERE R<span class="token punctuation">.B<span class="token operator">=S<span class="token punctuation">.B
</span></span></span></span></span></span></span></span></span>

3.5 试叙述SQL语言的关系代数特点和元组演算特点。

3.6 试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询:

(1)统计有学生选修的课程门数。


1
2
SELECT COUNT<span class="token punctuation">(DISTINCT C<span class="token comment">#) FROM SC
</span></span>

(2)求选修C4课程的学生的平均年龄。


1
2
3
4
5
6
7
  SELECT AVG<span class="token punctuation">(AGE<span class="token punctuation">)
    FROM S
    WHERE S<span class="token comment"># IN
     <span class="token punctuation">(SELECT S<span class="token comment">#
     FROM SC
     WHERE C<span class="token comment">#='C4')
</span></span></span></span></span></span>

或者


1
2
3
4
   SELECT AVG<span class="token punctuation">(AGE<span class="token punctuation">)
    FROM S,SC
    WHERE S<span class="token punctuation">.S<span class="token comment">#=SC.S# AND C#='004'
</span></span></span></span>

(3)求LIU老师所授课程的每门课程的学生平均成绩。


1
2
3
4
5
 SELECT CNAME<span class="token punctuation">,AVG<span class="token punctuation">(GRADE<span class="token punctuation">)
   FROM SC <span class="token punctuation">,C
   WHERE SC<span class="token punctuation">.C<span class="token comment">#=C.C# ANDTEACHER='LIU'
   GROUP BY C<span class="token comment">#   
</span></span></span></span></span></span></span>

(4)统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。


1
2
3
4
5
6
  SELECT DISTINCT C<span class="token comment">#,COUNT(S#)
    FROM SC
    GROUP BY C<span class="token comment">#
    HAVING COUNT<span class="token punctuation">(S<span class="token comment">#)&gt;10
    ORDER BY <span class="token number">2 DESC<span class="token punctuation">, C<span class="token comment"># ASC
</span></span></span></span></span></span></span>

(5)检索学号比WANG同学大,而年龄比他小的学生姓名。


1
2
3
4
 SELECT X<span class="token punctuation">.SNAME
    FROM S AS X<span class="token punctuation">, S AS Y
    WHERE Y<span class="token punctuation">.SNAME<span class="token operator">=<span class="token string">'WANG' AND X<span class="token punctuation">.S<span class="token comment">#&gt;Y.S# AND X.AGE&lt;Y.AGE
</span></span></span></span></span></span></span>

(6)检索姓名以WANG打头的所有学生的姓名和年龄。


1
2
3
4
   SELECT SNAME<span class="token punctuation">,AGE
    FROM S
    WHERE SNAME LIKE <span class="token string">'WANG%'
</span></span>

(7)在SC中检索成绩为空值的学生学号和课程号。


1
2
3
4
  SELECT S<span class="token comment">#,C#
    FROM SC
    WHERE GRADE IS NULL
</span>

(8)求年龄大于女同学平均年龄的男学生姓名和年龄。


1
2
3
4
  SELECT SNAME<span class="token punctuation">,AGE
    FROM S AS X
    WHERE X<span class="token punctuation">.SEX<span class="token operator">=<span class="token string">'男' AND X<span class="token punctuation">.AGE<span class="token operator">&gt;<span class="token punctuation">(SELECT AVG<span class="token punctuation">(AGE<span class="token punctuation">)FROM S AS Y WHERE Y<span class="token punctuation">.SEX<span class="token operator">=<span class="token string">'女'<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span>

(9)求年龄大于所有女同学年龄的男学生姓名和年龄。


1
2
3
4
  SELECT SNAME<span class="token punctuation">,AGE
    FROM S AS X
    WHERE X<span class="token punctuation">.SEX<span class="token operator">=<span class="token string">'男' AND X<span class="token punctuation">.AGE<span class="token operator">&gt;ALL <span class="token punctuation">(SELECT AGE FROM S AS Y WHERE Y<span class="token punctuation">.SEX<span class="token operator">=<span class="token string">'女'<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span>

除法运算

3.7 试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作:

(1)往基本表S中插入一个学生元组(‘S9’,‘WU’,18)。


1
2
 INSERT INTO S<span class="token punctuation">(S<span class="token comment">#,SNAME,AGE) VALUES('59','WU',18)
</span></span>

(2)在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别,并把检索到的值送往另一个已存在的基本表STUDENT(S#,SANME,SEX)。


1
2
3
4
5
6
  INSERT INTO STUDENT<span class="token punctuation">(S<span class="token comment">#,SNAME,SEX)
     SELECT S<span class="token comment">#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      <span class="token punctuation">(SELECT <span class="token operator">* FROM SC WHERE
       GRADE<span class="token operator">&lt;<span class="token number">80 AND S<span class="token punctuation">.S<span class="token comment">#=SC.S#)
</span></span></span></span></span></span></span></span></span>

(3)在基本表SC中删除尚无成绩的选课元组。


1
2
   DELETE FROM SC
     WHERE GRADE IS NULL

(4)把WANG同学的学习选课和成绩全部删去。


1
2
3
4
5
6
    DELETE FROM SC
     WHERE S<span class="token comment"># IN
      <span class="token punctuation">(SELECT S<span class="token comment">#
      FROM S
      WHERE SNAME<span class="token operator">=<span class="token string">'WANG'<span class="token punctuation">)
</span></span></span></span></span></span>

(5)把选修MATHS课不及格的成绩全改为空值。


1
2
3
4
5
6
7
UPDATE SC
    SET GRADE<span class="token operator">=NULL
    WHERE GRADE<span class="token operator">&lt;<span class="token number">60 AND C<span class="token comment"># IN
      <span class="token punctuation">(SELECT C<span class="token comment">#
      FROM C
      WHERE CNAME<span class="token operator">=<span class="token string">'MATHS'<span class="token punctuation">)
</span></span></span></span></span></span></span></span></span>

(6)把低于总平均成绩的女同学成绩提高5%。


1
2
3
4
   UPDATE SC
    SET GRADE<span class="token operator">=GRADE<span class="token operator">*<span class="token number">1.05
    WHERE GRADE<span class="token operator">&lt;<span class="token punctuation">(SELECT AVG<span class="token punctuation">(GRADE<span class="token punctuation">) FROM SC<span class="token punctuation">) AND S<span class="token comment"># IN (SELECT S# FROM SWHERE SEX='F')
</span></span></span></span></span></span></span></span></span>

(7)在基本表SC中修改C4课程的成绩,若成绩小于等于75分时提高5%,若成绩大于75分时提高4%(用两个UPDATE语句实现)。


1
2
3
4
5
6
7
   UPDATE SC
     SET GRADE<span class="token operator">=GRADE<span class="token operator">*<span class="token number">1.05
     WHERE C<span class="token comment">#='C4' AND GRADE&lt;=75
    UPDATE SC
     SET GRADE<span class="token operator">=GRADE<span class="token operator">*<span class="token number">1.04
     WHERE C<span class="token comment">#='C4' AND GRADE&gt;75
</span></span></span></span></span></span></span></span>

3.8 “仓库管理”关系模型有五个关系模式:

零件 PART(P#,PNAME,COLOR,WEIGHT)

项目 PROJECT(J#,JNAME,DATE)

供应商 SUPPLIER(S#,SNAME,SADDR)

供应 P_P(J#,P#,TOTOAL)

采购 P_S(P#,S#,QUANTITY)

(1)试用SQLDDL语句定义上述五个基本表,并说明主键和外键。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 CREATE TABLE PART
    <span class="token punctuation">(P<span class="token comment"># CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR<span class="token punctuation">(<span class="token number">10<span class="token punctuation">)<span class="token punctuation">,WEIGHT REAL<span class="token punctuation">,
    PRIMARY KEY<span class="token punctuation">(P<span class="token comment">#))
    CREATE TABLE PROJECT
    <span class="token punctuation">(J<span class="token comment"># CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE<span class="token punctuation">,
    PRIMARY KEY<span class="token punctuation">(J<span class="token comment">#))
    CREATE TABLE SUPLIER
    <span class="token punctuation">(S<span class="token comment"># CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY<span class="token punctuation">(S<span class="token comment">#))
    CREATE TABLE P_P
    <span class="token punctuation">(J<span class="token comment"># CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY<span class="token punctuation">(J<span class="token comment">#,P#),
    FOREIGN KEY<span class="token punctuation">(J<span class="token comment">#) REFERENCE PROJECT(J#),
    FOREIGN KEY<span class="token punctuation">(P<span class="token comment">#) REFERENCE PART(P#))
    CREATE TABLE P_S
    <span class="token punctuation">(P<span class="token comment"># CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY<span class="token punctuation">(P<span class="token comment">#,S#),
    FOREIGN KEY<span class="token punctuation">(P<span class="token comment">#) REFERENCE PART(P#),
    FOREIGN KEY<span class="token punctuation">(S<span class="token comment">#) REFERENCE SUPLIER(S#))
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

(2)试将PROGECT、P_P、PART三个基本表的自然联接定义为一个视图VIEW1,PART、P_S、SUPPLIER 三个基本表的自然联接定义为一个视图VIEW2。


1
2
3
4
5
6
7
8
9
10
  CREATE VIEW VIEW1<span class="token punctuation">(J<span class="token comment">#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT PROJECT<span class="token punctuation">.J<span class="token comment">#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT<span class="token punctuation">,PART<span class="token punctuation">,P_P
          WHERE PART<span class="token punctuation">.P<span class="token comment">#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW VIEW2<span class="token punctuation">(P<span class="token comment">#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT PART<span class="token punctuation">.P<span class="token comment">#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART<span class="token punctuation">,P_S<span class="token punctuation">,SUPPLIER
          WHERE PART<span class="token punctuation">.P<span class="token comment">#=P_S.P# AND P_S.S#=SUPPLIER.S#
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

(3)试在上述两个视图的基础上进行数据查询:

1)检索上海的供应商所供应的零件的编号和名字。


1
2
    SELECT P<span class="token comment">#,PNAME FROM VIEW2 WHERE SADDR='SHANGHAI'
</span>

2)检索项目J4所用零件的供应商编号和名字。


1
2
   SELECT S<span class="token comment">#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WHERE J#='J4')
</span>

3.9 对于教学数据库中基本表SC,已建立下列视图:


1
2
3
4
5
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT<span class="token punctuation">(C#<span class="token punctuation">),AVG(GRADE)
FROMSC
GROUPBYS#
</span></span>

试判断下列查询和更新是否允许执行。若允许,写出转换到基本表SC上的相应操作。

(1)


1
2
3
4
5
SELECT<span class="token operator">*
FROMS_GRADE
      允许
   SELECT S<span class="token comment">#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#
</span></span>

(2)


1
2
3
4
5
6
SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE><span class="token number">80
    允许
   SELECT S<span class="token comment">#,COUNT(C#) FROM SC WHEREAVG(GRADE)&gt;80
</span></span>

(3)


1
2
3
4
5
6
7
8
9
10
11
SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM><span class="token punctuation">(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’<span class="token punctuation">)
    允许
   SELECT S<span class="token comment">#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT<span class="token punctuation">(X<span class="token punctuation">.C<span class="token comment">#)&gt;(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE Y.S#='S4')
   GROUP BY S<span class="token comment">#
</span></span></span></span></span></span></span>

(4)


1
2
3
4
5
UPDATES_GRADE
SETC_NUM=C_NUM+<span class="token number">1
WHERES#=‘S4’
    不允许
</span>

(5)


1
2
3
4
 DELETEFROMS_GRADE
WHEREC_NUM><span class="token number">4
    不允许
</span>

3.10 预处理方式对于嵌入式SQL的实现有什么重要意义?

预处理方式是先用预处理程序对源程序进行扫描,识别出SQL语句,并处理成宿主语言的函数调用形式; 然后再用宿主语言的编译程序把源程序编译成目标程序。这样,不用扩充宿主语言的编译程序, 就能处理SQL语句。

3.11 在宿主语言的程序中使用SQL语句有哪些规定?

在宿主语言的程序中使用SLQ语句有以下规定:

(1)在程序中要区分SQL语句与宿主语言语句

(2)允许嵌入的SQL语句引用宿主语言的程序变量(称为共享变量),但有两条规定:

1)引用时,这些变量前必须加“:”作为前缀标识,以示与数据库中变量有区别。

2)这些变量由宿主语言的程序定义,并用SQL的DECLARE语句说明。

(3)SQL的集合处理方式与宿主语言单记录处理方式之间要协调。 需要采用游标机制,把集合操作转换成单记录处理方式。

3.12SQL的集合处理方式与宿主语言单记录处理方式之间如何协调?

由于SQL语句处理的是记录集合,而宿主语言语句一次只能处理一个记录, 因此需要用游标(cousor)机制,把集合操作转换成单记录处理方式。

2.13 嵌入式SQL语句何时不必涉及到游标?何时必须涉及到游标?

(1)INSERT、DELETE、UPDATE语句,查询结果肯定是单元组时的SELECT语句, 都可直接嵌入在主程序中使用,不必涉及到游标。

(2)当SELECT语句查询结果是多个元组时,此时宿主语言程序无法使用, 一定要用游标机制把多个元组一次一个地传送给宿主语言处理。

(电商)数据库原理及应用_模拟考核试题及参考答案

一、单选 (每空1分,共10分)

1.在Access数据库中,数据保存在( )对象中。

A.窗体 B.查询 C.报表 D.表

2.如果某一字段数据型为文本型,字段大小为8,该字段中最多可输入( )个汉字。

A.8 B.4 C.16 D.32

3.文本型字段最多可以存放( )个字符。

A.250 B.252 C.254 D.255

4.Access用户操作界面由( )部分组成。

A.4 B.5 C.3 D.6

5.下列( )图标是Access中表对象的标志。

A. B. C. D.

6.在设计Access数据表时,“索引”属性有( )取值。

A.1 B.2 C.3 D.4

7.Access中包含有( )种数据类型。

A.9 B.10 C.7 D.8

8.在一个宏中要打开一个报表,应该使用的操作是( )。

A.OpenForm B.OpenReport C.OpenTable D.OpenQuery

9.可以通过Internet进行数据发布的对象是( )。

A.窗体 B.报表 C.查询 D.数据访问页

10.模块窗口由( )个部分组成。

A.2 B.3 C.4 D.5

二、填空 (每空1分,共20分)

1.在人工管理和文件管理阶段,程序设计__依赖于 ___数据表示。

2.在文件系统中,存取数据的基本单位为___记录____,在数据库系统中,存取数据的基本单位为___数据项_____。

3.若实体A和B是多对多的联系,实体B和C是1对1的联系,则实体A和C是___多_____对___多_____的联系。

4.在一个关系中不允许出现重复的____元组____,也不允许出现具有相同名字的___属性_____。

5.数据库系统中的四类用户分别为____数据库管理员、数据库设计员、应用程序员、终端用户_____。

6.在存取数据库的数据的过程中,使用了两个数据缓冲区,分别为___系统_____缓冲区和____用户 ____缓冲区。

7.学生关系中的班级号属性与班级关系中的班级号主码属性相对应,则____班级号____为学生关系中的___外码___。

8.设一个关系A具有a1个属性和a2个元组,关系B具有b1个属性和b2个元组,则关系A?B具有___a1+b1____个属性和____ a2?b2 ____个元组。

9.设一个学生关系为S(学生号,姓名),课程关系为C(课程号,课程名),选课关系为X(学生号,课程号,成绩),求出所有选课的学生信息的运算表达式为_____?学生号(X)______与____S ____的自然连接。

10.在一个关系R中,若存在X→Y和X→Z,则存在_____ X→(Y,Z)_______,称此为函数依赖的合并性规则。

三、填空 (每空1分,共20分)

1.若一个关系的任何非主属性都不部分依赖依赖于任何候选码,则称该关系达到____第二____范式。

2.在SQL中,列级完整性约束分为__6__种情况,表级完整性约束分为__4__种情况。

  1. 在SQL中,每个视图中的列可以来自不同的___表___,它是在原有表的基础上____建立____的逻辑意义上的新关系。

  2. 在SQL的查询语句中,group by选项实现____分组统计______功能,order by选项实现对结果表的____排序_____功能。

5.对于较复杂的系统,概念设计阶段的主要任务是:首先根据系统的各个局部应用画出各自对应的____局部ER图______,然后再进行综合和整体设计,画出_____整体ER图_____。

6.机器实现阶段的目标是在计算机系统中得到一个满足______设计要求、功能完善、操作方便___的数据库应用系统。

7.Access的用户操作界面由 标题栏、菜单栏、工作区 、工具栏、状态栏等五个部分组成。

8.Access“表”结构设计窗口中上半部分的“表设计器”是由 字段名称、数据类型、说明 等三列组成。

9.Access中的窗体由 页眉、主体 和页脚等三个部分组成。

四、填空 (每空1分,共20分)

  1. 设一个关系为R(A,B,C,D,E),它的最小函数依赖集为FD={A→B,A→C,(C,D)→E},则该关系的候选码为_____(A,D)___,候选码函数决定E是___伪传递___性。

2.设一个关系为R(A,B,C,D,E),它的最小函数依赖集为FD={A→B,A→C,(A,D)→E},该关系只满足___第一_____范式,若要规范化为高一级的范式,则将得到____2____个关系。

3.在实际的数据库管理系统中,对数据库的操作方式有_____命令交互、程序执行、窗口界面______等三种。

4.在SQL中,主码约束的关键字为____ primary key________,外码约束的关键字为______foreignkey ______。

5.基本表属于全局模式中的表,它是____实表____,而视图则属于局部模式中的表,它是____虚表 ____。

  1. 在SQL新版的查询语句中,select选项实现投影运算,from选项实现____连接____运算,where选项实现____选择___运算。

7.数据字典是对系统工作流程中____数据____和____处理____的描述。

8.关系数据库系统中的全局模式由若干个基本表所组成,表与表之间的联系是通过定义的____主码____和____外码____实现的。

9.在旅店管理中,涉及到的基本表有四个,它们分别为客房表、住宿表、_旅客登记表、消费卡表。

10.在设置或撤消数据库密码的过程中,密码对于字母 大小写 是敏感的。

五、填空 (每空1分,共10分)

1.Access的用户操作界面由 标题栏、菜单栏、工作区 、工具栏、状态栏等五个部分组成。

2.Access“表”结构设计窗口中上半部分的“表设计器”是由 字段名称、数据类型 和说明等三列组成。

3.Access中的窗体由 页眉、主体、页脚 等三个部分组成。

4.在Access中模块分为 类模块 和 标准模块 两种类型。

六、根据主教材第四章所给的商品库和教学库,或者按照下列所给的每条SQL查询语句写出相应的功能,或者按照下列所给的每种功能写出相应的SQL查询语句。(每小题4分,共20分)

在名称为商品库的数据库中包含有商品表1和商品表2,它们的定义分别为:

商品表1(商品代号 char(8),分类名 char(8),单价 float,数量 int)

商品表2(商品代号 char(8),产地 char(6),品牌 char(6),)

在名称为教学库的数据库中包含有学生、课程和选课三个表,它们的定义分别为:

学生(学生号 char(7),姓名 char(6),性别 char(2),出生日期 datetime,

专业 char(10),年级 int)

课程(课程号 char(4),课程名 char(10),课程学分 int

选课(学生号 char(7),课程号 char(4),成绩 int)


1
2
3
4
5
6
    <span class="token number">1.select distinct 产地

        <span class="token keyword">from 商品表<span class="token number">2

       功能:从商品库中查询出所有商品的不同产地。
</span></span></span>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 <span class="token number">2.select <span class="token operator">*

        <span class="token keyword">from 学生

        where 学生号 <span class="token keyword">in <span class="token punctuation">(select 学生号

          <span class="token keyword">from 选课

          group by 学生号 having count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">=<span class="token number">1

        <span class="token punctuation">)

    功能:从教学库中查询出只选修了一门课程的全部学生。

    <span class="token number">3.select <span class="token operator">*

         <span class="token keyword">from 学生

         where 学生号 <span class="token keyword">in <span class="token punctuation">(select 学生号

           <span class="token keyword">from 选课

           group by 学生号 having count<span class="token punctuation">(<span class="token operator">*<span class="token punctuation">)<span class="token operator">&lt;=<span class="token number">2  

         <span class="token punctuation">) <span class="token operator">or <span class="token operator">not exists <span class="token punctuation">(select <span class="token operator">*

              <span class="token keyword">from 选课

              where 学生<span class="token punctuation">.学生号<span class="token operator">=选课<span class="token punctuation">.学生号

         <span class="token punctuation">)

    功能: 从教学库中查询出最多选修了<span class="token number">2门课程(含未选任何课程)的全部学生。
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

4.从商品库中查询出每类(即分类名相同)商品的最高单价。


1
2
3
4
5
6
 select 分类名<span class="token punctuation">,<span class="token builtin">max<span class="token punctuation">(单价<span class="token punctuation">) <span class="token keyword">as 最高单价

        <span class="token keyword">from 商品表<span class="token number">1

        group by 分类名
</span></span></span></span></span></span></span>

5.从教学库中查询出至少选修了姓名为@m1学生所选课程中一门课的全部学生。


1
2
3
4
5
6
7
8
9
10
11
12
select distinct 学生<span class="token punctuation">.<span class="token operator">*

        <span class="token keyword">from 学生<span class="token punctuation">,选课

        where 学生<span class="token punctuation">.学生号<span class="token operator">=选课<span class="token punctuation">.学生号 <span class="token operator">and 课程号<span class="token operator">=<span class="token builtin">any<span class="token punctuation">(select 课程号

          <span class="token keyword">from 学生<span class="token punctuation">,选课

          where 学生<span class="token punctuation">.学生号<span class="token operator">=选课<span class="token punctuation">.学生号 <span class="token operator">and 姓名<span class="token operator">=@m1

        <span class="token punctuation">)
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

收藏点赞~~养成好习惯~

微信搜一搜**【程序员一凡】**关注这个文绉绉的程序员,关注后回复【面试】有我准备的一线大厂面试资料和简历模板,希望大家都能找到心仪的工作,学习是一条时而郁郁寡欢,时而开怀大笑的路,加油。如果你通过努力成功进入到了心仪的公司,一定不要懈怠放松,职场成长和新技术学习一样,不进则退。如果有幸我们江湖再见!

如果对软件测试、接口、自动化、性能测试、测试开发、面试经验交流。感兴趣可以1079636098,群内会有不定期的发放免费的资料链接,这些资料都是从各个技术网站搜集、整理出来的,如果你有好的学习资料可以私聊发我,我会注明出处之后分享给大家。

转载请注明:九层楼 » 面试官没想到,一个SQL,我可以扯一小时

喜欢 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址