use OrderDB;
/*1.查询职工工资按高低排序的前 20%的职工编号、职工姓名和工资。OK
*/
select top 20 percent employeeno,employeename,salary
from Employee
order by salary desc;
/*2.查询业务科或财务科的职工姓名、性别和所在部门,仅显示前面 5 位职工。OK
*/
select top 5 employeename,gender,department
from Employee
where department='业务科'or department='财务科';
/*3.查询 1973 年 9 月份出生且为职员的员工编号、姓名、出生日期以及所在部门,并按出生日期的降序输出。OK
*/
select employeeno,employeename,birthday,department
from employee
where year(birthday)=1973 and month(birthday)=9 and headship='职员'
order by birthday desc;
/*4.查询所有姓张的职工姓名、部门和性别(要求性别以中文显示)。OK
*/
select employeename,department,gender=
case
when gender='M'then '男'
when gender='F'then '女'
end
from Employee
where employeename like '张%';
/*5.查询姓张且全名为两个字的职工姓名。OK
*/
select employeename
from employee
where employeename like '张_';
/*6.查询不同性别员工的人数。OK
*/
select gender=case when gender='M'then '男' when gender='F'then '女' end,人数=count(gender)
from Employee
group by gender;
/*7.查询每个客户的订单编号、客户名称、订单金额。OK
*/
select orderno,customername,ordersum
from ordermaster,customer
where customer.customerNo=OrderMaster.customerNo;
/*8.查询住址在上海的员工所做的订单,输出员工编号、姓名、住址、订单编号、客户编号和订单日期,并按客户编号排序输出。OK【发现:不用desc 默认升序】
*/
select e.employeeno,employeename,e.address,om.orderno,c.customerno,orderdate
from employee e,ordermaster om,customer c
where e.employeeno=om.empl