数据库除法查询技巧

数据库除法查询技巧

学生信息关系：student（sno，sname，age，sex）

学生选课关系： sc（sno，cno，score）

学校课程关系： course（cno，cname）

1.关系代数技巧

• 求至少选择了C001和C003两门课程的学生学号
• 求至少学习了学生S003所学 课程的学生学号
• 求选择了全部课程的学生的学号
• 求选择了全部课程的学生的学号和姓名

要点：我们可以把除法看做从一个全部中扣去一个全部。要我们特别关注的是主键和外键。于是上述查询内容就可以变为全部什么的全部什么 ÷ 全部什么

• 全部学生学的全部课程 ÷ C001和C002全部课程
• 全部学生学的全部课程 ÷ 学生S003学的全部课程
• 全部学生学的全部课程 ÷ 全部课程
• 全部学生学的全部课程 ÷ 全部课程

最后一个至于姓名嘛，拿到了学号，连接一下表就得到了

关系代数如下

${\Pi }_{sno,cno}(sc)÷{\Pi }_{cno}{\sigma }_{cno{=}^{\prime }c001^{\prime }\vee cno{=}^{\prime }c002^{\prime }}(course)$

${\Pi }_{sno,cno}÷{\Pi }_{cno}{\sigma }_{sno{=}^{\prime }s003^{\prime }}(course)$

${\Pi }_{sno,cno}(sc)÷{\Pi }_{cno}(course)$

$\Pi sno,sname(({\Pi }_{sno,cno}(sc)÷{\Pi }_{cno}(course))\bowtie student)$

2. sql语句除法技巧

除法的SQL语句总体结构为

select 选择项 from tableA where not exists(
    select * from tableB where 条件 and not exists(
        select * from tableC where tableA.属性 = tablec.属性 and tableB.属性 = tableC.属性     
    )
)
    

tableA是可以选择出来选择项的表，可能是表与表的连接

tableB是除号后边的那个表，可能是表与表的连接

tableC是除号前边的那个表，可能是表与表的连接

上述例题的SQL语句如下:

---

• 求至少选择了C001和C003两门课程的学生学号

${\Pi }_{sno,cno}(sc)÷{\Pi }_{cno}{\sigma }_{cno{=}^{\prime }c001^{\prime }\vee cno{=}^{\prime }c002^{\prime }}(course)$

select sno from sc A where not exists(
    select * from course B where cno in ('c002','c003') and not exists(
        select * from sc C where A.sno=C.sno and B.cno = C.cno
    )
)

---

• 求至少学习了学生S003所学课程的学生学号
  ${\Pi }_{sno,cno}÷{\Pi }_{cno}{\sigma }_{sno{=}^{\prime }s003^{\prime }}(course)$

select sno from sc A where not exists(
    select * from course B where cno in (select cno from sc where sno='s003') and not exists(
        select * from sc C where A.sno = C.sno and B.cno = C.cno
    )
)

---

• 求选择了全部课程的学生的学号

${\Pi }_{sno,cno}(sc)÷{\Pi }_{cno}(course)$

select sno from sc A where not exists(
    select * from course B and not exists(
        select * from sc C where A.sno = C.sno and B.cno = C.cno
    )
)

---

• 求选择了全部课程的学生的学号

$\Pi sno,sname(({\Pi }_{sno,cno}(sc)÷{\Pi }_{cno}(course))\bowtie student)$

select sno, sname from student A where not exists(
    select * from course B and not exists(
        select * from sc C where A.sno = C.sno and B.cno = C.cno
    )
)

---

• 练习1

职工表：EMP（ENO，ENAME，AGE，SEX，ADDR），其属性分别表示职工的工号，姓名，年龄，性别，地址。

工作表：WORK（ENO，CNO，SALARY，DEDUCT），其属性分别表示职工号，所在的公司编号，工资，扣款。

公司表：COMP（CNO，CNAME，CADDR），其属性分别表示公司编号，公司名，地址。

这里假设一个职工可在多个公司兼职，并在兼职公司各领一份工资。

（1）用SQL语句查询在所有公司都工作的职工姓名

所有公司的所有职工 ÷ 所有公司

select ename from emp A where not exists(
    select * from eopm B where not exists(
        select * from work C where A.eno = B.eno and B.cno = C.cno
    )
)

---

• 练习2

Aq(A#, ANAME, WQTY, CITY)

Bq(B＃, BNAME, PRICE)

ABq(A＃, B＃ ,QTY)

其中各个属性的含义如下：A＃（商店代号）、ANAME（商店名）、WQTY（店员人数）、CITY（所在城市）、B＃（商品号）、BNAME（商品名称）、PRICE（价格）、QTY（商品数量）。。

（1）用SQL语句查询至少销售256商店所有商品的商店，256商店除外

所有商店的所有商品 ÷ 256商店提供的所有商品

select Aname, city from Aq A where not exists(
    select * from ABq B where A# = 256 and not exists(
        select * from ABq C where A.A# = C.A# and B.B# = C.C# and A.A# != 256
    )
)

​