SQL-练习

attend表，字段如下
num,no,attendance
工号、工资编号、出勤率

employee表，字段如下
num,name,sex,age,departmentno
工号、姓名、性别、年龄、部门

wage表，字段如下
no,amount
编号、工资金额

题目：
–1.查询工资金额为8000的职工工号和姓名，降序排列
–方式一
select employee.num, employee.name from employee, attend
where attend.no in (select DISTINCT no from wage where amount = 8000)
and attend.num = employee.num ORDER BY employee.num desc
–方式二
select num, name from employee where EXISTS (
select num from attend WHERE EXISTS ( select DISTINCT no from wage where amount = 8000 and attend.no = wage.no)
and employee.num = attend.num)
ORDER BY employee.num desc

–2.查询张三的出勤率
–方式一
select attendance from attend where exists(
select num from employee where name=”张三”
and attend.num = employee.num)
–方式二
select attendance from attend where num in(
select num from employee where name=”张三”)
–方式三
select employee.name,attend.attendance from attend,employee
where employee.name=”张三” and attend.num = employee.num

–3.查询3次出勤率为0的职工姓名和工号
–方式一
select num, name from employee
where EXISTS (
select num from attend where attendance = 0
GROUP BY num HAVING count(num) = 3 and employee.num = attend.num)
–方式二
select num, name from employee where num in (select num from attend where attendance = 0 GROUP BY num HAVING count(num) =3)

–4.查询出勤率为10并且工资金额小于2500的职工信息
–方式一
select employee.num,
employee.name,
employee.sex,
employee.age,
employee.departmentno
from (select no from wage where amount < 2500) as wag,
(select no, num from attend where attendance = 10) as att,
employee
where wag.no = att.no
and att.num = employee.num
–方式二
select employee.num,
employee.name,
employee.sex,
employee.age,
employee.departmentno
from employee
where num in (select num
from attend
where no in (select no from wage where amount < 2500)
and attendance = 10)
–方式三
select employee.num,
employee.name,
employee.sex,
employee.age,
employee.departmentno
from employee
where EXISTS (select num
from attend
where EXISTS (select no
from wage
where amount < 2500
and wage.no = attend.no)
and attend.attendance = 10
and employee.num = attend.num)