数据库简单与复杂查询

例 5 1 部门的编号 名称和位置 select from department 星号 可以在 select 子句中表示 所有的属性 例 5 2 部门的编号 名称和位置 select dept no dept name location from department 例 5 3 查询位于 Dallas 的部门编号和名称 select dept no dept name from department WHERE location Dallas 例 5 4 查询预算额的 0 51 倍大于 60000 的项目名称 SELECT project name FROM project WHERE budget 0 51 60000 例 5 5 查询在部门编号为 d1 的部门工作且职员编号为 25348 或职工名称为 Matthew 的雇 员信息 SELECT FROM employee WHERE emp no 25348 OR emp fanme Matthew AND dept no d1 例 5 6 查询不在部门编号为 d1 的部门工作的职员编号和姓名 SELECT emp no emp lname FROM employee WHERE NOT dept no d1 例 5 7 获取编号既不是 10102 也不是 9031 的职员的所有列 SELECT FROM employee WHERE emp no NOT IN 10102 9031 例 5 8 获取预算在 95 000 120 000 之间的所有项目的名称和预算 SELECT project name budget FROM project WHERE budget BETWEEN 95000 AND 120000 例 5 9 获取预算小于 95 000 和大于 120 000 之间的所有项目的名称 SELECT project name FROM project WHERE budget NOT BETWEEN 95000 AND 120000 例 5 10 获取为项目 2 工作的 具有未知工作的所有职员的职员编号和相应的项目编号 SELECT emp no project no FROM works on WHERE project no p2 AND job IS NULL 系统函数 ISNULL 允许在显示时用指定的数值来替代 NULL SELECT emp no ISNULL job Job unknown task FROM works on WHERE project no p2 AND job IS NULL 例 5 11 找出所有姓是以字母 J 开头的职员的名字和编号 SELECT emp fname emp lname emp no FROM employee WHERE emp lname LIKE j 例例 5 12 找出所有名中第二个字母是找出所有名中第二个字母是 a 的职员的名字和编号 的职员的名字和编号 SELECT emp fname emp lname emp no FROM employee WHERE emp fname LIKE a 例例 5 13 获取所在地名从获取所在地名从 C 到到 F 的字母打头的所有部门的详细资料 的字母打头的所有部门的详细资料 SELECT FROM DEPARTMENT WHERE LOCATION LIKE C F 例例 5 14 获取姓的打头字母不是获取姓的打头字母不是 J K L M N O 并且名的开头字母是并且名的开头字母是 E 或者或者 Z 的所有职员的的所有职员的 编号和名字 编号和名字 SELECT FROM EMPLOYEE WHERE EMP LNAME LIKE J O AND emp fname LIKE EZ 例例 5 15 所有被方括号括起来的通配符 所有被方括号括起来的通配符 都表示本来的意思 都表示本来的意思 SELECT project no project name FROM project WHERE project NAME LIKE 例例 5 16 获取在研究部门工作的所有职员的名和姓 获取在研究部门工作的所有职员的名和姓 SELECT emp fname emp lname FROM employee WHERE dept no SELECT dept no FROM DEPARTMENT WHERE dept name research 例例 5 17 获取职员获取职员 moser 参与的项目编号 参与的项目编号 SELECT DISTINCT project no FROM works on WHERE emp no SELECT emp no FROM employee WHERE emp lname moser 例例 5 18 获取部门位于获取部门位于 Dallas 的所有职员的详细信息 的所有职员的详细信息 SELECT FROM employee WHERE dept no in SELECT dept no FROM department WHERE location Dallas 例例 5 19 获取为获取为 Apollo 项目工作的所有职员的姓 项目工作的所有职员的姓 SELECT emp lname FROM employee WHERE emp no in SELECT emp no FROM works on WHERE project no in select project no from project where project name Apollo 例例 5 20 获取不是在项目上花费了最多时间的所有职员的编号 项目编号和工作名称 获取不是在项目上花费了最多时间的所有职员的编号 项目编号和工作名称 SELECT DISTINCT EMP NO PROJECT NO JOB FROM works on WHERE enter date any SELECT enter date FROM works on 例例 5 21 获取为项目获取为项目 p1 工作的所有职员的名和姓 工作的所有职员的名和姓 SELECT DISTINCT emp fname emp lname FROM employee WHERE emp no any SELECT emp no FROM works on where project no p1 例例 5 22 选择编号大于等于选择编号大于等于 10000 的所有职员的名字 的所有职员的名字 SELECT DISTINCT emp fname emp lname FROM SELECT FROM employee WHERE emp no 10000 AS emp no10000 例例 5 23 使用项目编号和工种对所有职员分组 使用项目编号和工种对所有职员分组 SELECT project no job FROM works on GROUP BY project no job 例例 5 24 看如下的查询例子 分析正确与否看如下的查询例子 分析正确与否 SELECT emp lname MIN emp no FROM employee 例例 5 25 获取最小的职员编号获取最小的职员编号 SELECT MIN emp no min employee number FROM employee 例例 5 26 获取最小的职员编号和姓获取最小的职员编号和姓 SELECT emp no emp lname FROM employee WHERE emp no SELECT MIN emp no min employee number FROM employee 例例 5 26 获取表获取表 WORKS ON 中进入时间最晚的经理的职员编号中进入时间最晚的经理的职员编号 SELECT emp no FROM WORKS ON WHERE enter date SELECT MAX enter date FROM WORKS ON WHERE job Manager 例例 9 27 计算所有项目预算的总和计算所有项目预算的总和 SELECT SUM BUDGET SUM OF BUDGETS FROM PROJECT 例例 5 28 计算每个项目中不同工种的数目计算每个项目中不同工种的数目 SELECT project no COUNT DISTINCT job job count FROM works on group by project no 例例 5 29 每个项目中有多少职员为其工作每个项目中有多少职员为其工作 SELECT project no COUNT emp count FROM works on group by project no 例例 5 30 获取招募的总人数少于获取招募的总人数少于 4 个的项目个的项目 SELECT project no FROM works on group by project no having count 4 例例 5 31 获取职员的名字和职员编号 以职员编号升序排列获取职员的名字和职员编号 以职员编号升序排列 SELECT emp no emp fname emp lname FROM employee ORDER BY emp no asc desc 表示降序 表示降序 asc 表示升序 表示升序 Group by 默认使用升序默认使用升序 5 32 获取编号小于获取编号小于 2000 的职员所在的部门和其编号 并以姓和名升序排列的职员所在的部门和其编号 并以姓和名升序排列 SELECT emp fname emp lname dept no FROM employee where emp no 20000 ORDER BY emp fname emp lname 5 33 对于每个项目编号 取得其所有的项目编号以及职员数量 以职员数量降序排列 对于每个项目编号 取得其所有的项目编号以及职员数量 以职员数量降序排列 SELECT project no COUNT emp quantity FROM works on group by project no ORDER BY 2 DESC 5 34 获取属于部门获取属于部门 D1 或者参加项目的时间早于或者参加项目的时间早于 1998 年年 1 月月 1 日的所有职员的编号 并以日的所有职员的编号 并以 升序排列 升序排列 SELECT emp no FROM employee where dept no d1 UNION SELECT emp no FROM works on WHERE enter date 0 CREATE TABLE Works on project no char 8 NOT NULL emp no varchar 8 NOT NULL job varchar 50 enter data varchar 50 CONSTRAINT foreign Employee FOREIGN KEY emp no REFERENCES Employee emp no CONSTRAINT foreign Project FOREIGN KEY project no REFERENCES Project project no 插入数据 insert into Department values 科研 01 上海 insert into Department values 会计 02 北京 insert into Department values 会计 03 上海 insert into Department values Seattle 03 广州 insert into Employee values Tom Adl 01 01 insert into Employee values Jane Adl 02 02 insert into Project values Gemini 01 广州 10000 insert into Project values Gemini 02 广州 10000 insert into Project values p1 02 烟台 10001 insert into Project values p1 01 烟台 10001 insert into Project values p2 03 青岛 10002 insert into Project values p3 01 广州 10002 insert into Project values p3 03 青岛 10002 insert into Works on values 01 01 search 1998 10 15 insert into Works on values 01 01 plan 1998 10 15 insert into Works on values 01 02 search 10 15 1998 题目 1 获取每个职员的详细信息 也就是说除了职员的编号 名和姓 相应的部门编号以外 还要获取所在部门的名字和坐落地点 select distinct 是强行删除重复 如果是主码则没必要添加 from Employee Department where Employee dept no Department dept no 2 获取为 Gemini 项目工作的所有职员的全部详细信息 select from Works no Project where Works no project no Project project no and project name Gemimi and 为特定的谓词 sql 允许在句子中使用逻辑连词 and or 和 not 逻辑连词的运算对象可以是包含比较运算符的 和 3 获取每个职员的详细信息 也就是说除了职员的编号 名和姓 相应的部门编号以外 还要获取所在部门的名字和坐落地点 select Employee dept name location from Employee Department where Employee dept no Department dept no 4 获取每个职员的详细信息 也就是说除了职员的编号 名和姓 相应的部门编号以外 还要获取所在部门的名字和坐落地点 select Employee dept name location from Employee Department where Employee dept no Department dept no 关系名被用作前缀 来说明我们使用的是哪个属性 5 获取 1998 年 10 月 15 日加入项目的所有职员的部门编号 select dept no from Employee Works no where Employee emp no Works no emp no AND enter date 10 15 1998 6 获取会计部门中的职员所工作的项目名称 冗余重复元组删去 select distinct Project name from Department Employee Works no Project where Department dept name accounting AND department dept no Employee dept no AND Employee emp no Works no emp no AND Works no project no Project project no 7 获取与至少一个其它的部门拥有相同所在地的所有部门的全部详细信息 select distinct t1 dept no t1 dept name t1 location from Department t1 Department t2 where t1 dept not2 dept no AND t1 location t2 location 8 获取为项目 P3 工作的所有职员的姓 select emp lname from Employee where emp no IN select emp no from Works no where project no p3 9 获取为项目 P1 工作的所有职员的姓 select emp lname from Employee where EXISTS select from Works no where Works no emp no Employee emp no AND project no p1 10 获取工作部门不在 Seattle 的所有职员的姓 select emp lname from Employee where NOT EXISTS select from