数据库简单查询和复杂查询.docx

Department(dept_no, dept_name, location)Employee(emp_no, emp_fname,emp_lname,dept_no)Project(project_no, project_name, budget)Works_on(emp_no, project_no, job, enter_date)/*5.1部门的编号、名称和位置*/select dept_no, dept_name, locationfrom Department/*5.2部门的编号、名称和位置*/select dept_no, dept_name, locationfrom Department/*5.3查询位于Dallas的部门编号和名称*/select dept_no, dept_namefrom Departmentwhere location=Dallas/*5.4查询预算额的0.51倍大于60000的项目名称*/select project_namefrom Project where budget*0.5160000/*5.5查询在部门编号为d1的部门工作且职员编号为25348或职工名称为Matthew 的雇员信息。*/select emp_no, emp_fname,emp_lname,dept_nofrom Employee where dept_no=d1 and (emp_no=25348 or emp_fname=Matthew)/*例5.6 查询不在部门编号为d1的部门工作的职员编号和姓名*/select emp_no, emp_fname,emp_lnamefrom Employee where dept_no!=d1/*例5.7 获取编号既不是10102也不是9031的职员的所有列。*/select *from Employee where emp_no!=10102and emp_no!=9031/*例5.8 获取预算在$95 000 $120 000之间的所有项目的名称和预算。*/select project_name, budgetfrom Project where budget between 9031 and 95000/*例5.9 获取预算小于$95 000和大于 $120 000之间的所有项目的名称。*/select project_namefrom Project where budget between 95000 and 120000/*5.10获取为项目2工作的，具有未知工作的所有职员的职员编号和相应的项目编号。*/select Employee.emp_no,project_nofrom Employee,Works_on where Employee.emp_no=Works_on.emp_no and project_no=2 and job=NULL/*例5.11 找出所有姓是以字母J开头的职员的名字和编号。*/select emp_fname,emp_nofrom Employee where emp_lname likeJ%/*例5.12 找出所有名中第二个字母是a的职员的名字和编号。*/select emp_fname,emp_nofrom Employee where emp_fname like_a%/*例5.13 获取所在地名从C到F的字母打头的所有部门的详细资料。*/select *from Department where location like C%or location like D%or location like E%or location like F%/*例5.14 获取姓的打头字母不是J,K,L,M,N,O,并且名的开头字母是E或者Z的所有职员的编号和名字。*/select emp_no,emp_fnamefrom Employee where (emp_lname not likeJ%and emp_lname not likeK%and emp_lname not likeL%and emp_lname not likeM%and emp_lname not likeN%)and emp_fname likeE% or emp_fname likeZ%/*例5.15 */ SELECT project_no,project_name FROM project WHERE project_NAME LIKE %_%/*例5.16 获取在研究部门工作的所有职员的名和姓。*/select emp_fname,emp_lnamefrom Employeewhere dept_no in(select dept_no from Department where dept_name =Reserch ) /*例5.17 获取职员moser参与的项目编号。*/select project_nofrom Works_on where emp_no in(select emp_no from Employee as E where emp_fname=moser) /*例5.18 获取部门位于Dallas的所有职员的详细信息。*/ select *from Employee where dept_no in(select dept_no from Department where location=Dallas) /*例5.19 获取为项目工作的所有职员的姓。*/ select emp_lnamefrom Employee where emp_no in(select emp_no from Works_on where project_no !=NULL ) /*例5.20 获取不是在项目上花费了最多时间的所有职员的编号、项目编号和工作名称。*/ select emp_no,project_no,jobfrom Works_on where enter_date all(select MIN(enter_date) from Works_on ) /*例5.21 获取为项目p1工作的所有职员的名和姓。*/ select emp_fname,emp_lnamefrom Employee where emp_no in(select emp_no from Works_on where project_no=p1) /*例5.22 选择编号大于等于10000的所有职员的名字。*/ select emp_fnamefrom Employee where emp_no=10000 /*例5.23 使用项目编号和工种对所有职员分组。*/select project_no, count(emp_no)from Works_on group by project_no ,job /*例5.25 获取最小的职员编号*/ select MIN(emp_no)from Employee/*例5.26(0) 获取最小的职员编号和姓*/select emp_no,emp_lnamefrom Employee where emp_no in(select MIN(emp_no) from Employee)/*例5.26(1) 获取表WORKS_ON中进入时间最晚的经理的职员编号*/select emp_nofrom Works_on where enter_date in(select max(enter_date) from Works_on ) /*例9.27 计算所有项目预算的总和*/select SUM(budget) from Project /*例5.28 计算每个项目中不同工种的数目*/select project_no ,COUNT (job)from Works_on group by project_no/*例5.29 每个项目中有多少职员为其工作*/select project_no ,COUNT (emp_no)from Works_on group by project_no /*例5.30 获取招募的总人数少于4个的项目*/ select project_no from Works_on group by project_nohaving COUNT (emp_no)4 /*例5.31 获取职员的名字和职员编号，以职员编号升序排列*/select emp_fname,emp_nofrom Employee order by emp_no asc /*5.32 获取编号小于2000的职员所在的部门和其编号，并以姓和名升序排列*/ select dept_name ,emp_no from Department as D ,Employee as E where D.dept_no=E.dept_no and emp_no2000 order by emp_fname asc /*5.33 对于每个项目编号，取得其所有的项目编号以及职员数量，以职员数量降序排列。*/select project_no ,COUNT (emp_no) as countOfEmp_nofrom Works_on group by project_noorder by countOfEmp_no desc/*5.34 获取属于部门D1或者参加项目的时间早于1998年1月1日的所有职员的编号，并以升序排列。*/select E.emp_nofrom Works_on as W ,Employee as E where W.emp_no=E.emp_no and (E.dept_no =d1or enter_date 1998-01-01 00:00:00.000 )order by emp_no ascDepartment(dept_no, dept_name,location)Employee(emp_no,emp_fname,emp_lname,dept_no)Project(project_no, project_name, budget)Works_on(emp_no, project_no,job,enter_date)/*1. 获取每个职员的详细信息；也就是说除了职员的编号、名和姓、相应的部门编号以外，还要获取所在部门的名字和坐落地点。*/select *from Employee as E,Department as Dwhere E.dept_no=D.dept_no/*2. 获取为Gemini项目工作的所有职员的全部详细信息。*/select emp_no,emp_fname,emp_lname,dept_no,dept_name,locationfrom Employee as E,Department as Dwhere E.dept_no=D.dept_no and E.emp_no in(select emp_no from Works where project_no in (select project_no from Project where project_name=Gemini)/*3 获取每个职员的详细信息；也就是说除了职员的编号、名和姓、相应的部门编号以外，还要获取所在部门的名字和坐落地点。*/select *from Employee as E,Department as Dwhere E.dept_no=D.dept_no/*4 获取每个职员的详细信息；也就是说除了职员的编号、名和姓、相应的部门编号以外，还要获取所在部门的名字和坐落地点。*/select *from Employee as E,Department as Dwhere E.dept_no=D.dept_no/*5 获取年月日加入项目的所有职员的部门编号。*/select dept_nofrom Departmentwhere dept_no in(select dept_no from Employee where emp_no in(select emp_no from Works_on where enter_date=1998-10-15)/*6 获取会计部门中的职员所工作的项目名称（冗余重复元组删去）。*/select distinct project_namefrom Project where project_no in(select project_no from Works_on where emp_no in(select emp_no from Employee where dept_no in (select dept_no from Department where dept_name=会计)/*7 获取与至少一个其它的部门拥有相同所在地的所有部门的全部详细信息。*/select *from Department as D1 where not exists(select * from Department as D2 where D2.dept_name!=D1.dept_name and not exists(select * from Department as D3 where D3.dept_name=D1.dept_name and D3.location=D2.location)select *from Department as D1 where location in(select location from Department as D2 where D2.dept_name!=D1.dept_name)/*8 获取为项目P3工作的所有职员的姓。*/select emp_lnamefrom Employee where emp_no in(select emp_no from Works_on where project_no =P3)/*9 获取为项目P1工作的所有职员的姓。*/select emp_lnamefrom Employee where emp_no in(select emp_no from Works_on where project_no =P1)/*10 获取工作部门不在Seattle的所有职员的姓。*/select emp_lnamefrom Employeewhere dept_no in(select emp_no from Department where dept_name!=Seattle)/*11. 求参加了全部工程