postgresql 练习题（一）

1. 根据数据字典，查询当前用户下有哪些表 

   select tablename from pg_tables where schemaname='public'

2. 查询所有员工编号，员工姓名、邮件、雇佣日期、部门、部门详细地址信息。

   select employees.employee_id,
   employees.first_name||employees.last_name as full_name,employees.email,employees.hire_date,departments.department_name,locations.street_address
   
   from employees,departments,locations
   where employees.department_id=departments.department_id  
   and departments.location_id=locations.location_id;

3. 查询1997年入职员工员工编号，员工姓名，雇佣日期，工龄(保留2位小数)、按照First_Name升序排列

   SELECT
   employee_id,
   first_name,
   last_name,
   hire_date,
   ROUND( ( ( CURRENT_DATE - hire_date ) / 365 ), 2 )
   
   FROM employees
   WHERE EXTRACT ( YEAR FROM hire_date ) = 1997
   ORDER BY first_name;

4. 显示姓、薪水，佣金（commission） ，然后按薪水降序排列

   select first_name,salary,commission_pct as commissions
   from employees
   where commission_pct IS NOT NULL
   order by salary desc;

5. 显示姓名、薪水，佣金（commission），佣金为空的，统一加上0.05;其余的加上0.03，按照薪资降序、变更后佣金升序排列

   select first_name,last_name,salary,coalesce(commission_pct+0.03,0.05) as commissions
   from employees
   order by salary desc,commissions asc;

6. 显示名字以J、K、L、M开头的雇员

   SELECT * FROM employees
   WHERE last_name LIKE 'J%'
   or last_name LIKE 'K%'
   or last_name LIKE 'L%'
   or last_name LIKE 'M%';

7. 显示员工姓名，薪水，调整后薪水(按部门调整：IT提升30%,Salse 提升50%、其余部门提升20%)；按照调整后薪水升序排列

   SELECT e.first_name||e.last_name as full_name,e.salary,
   
   CASE d.department_name
   WHEN 'IT' THEN e.salary * 1.2
   WHEN 'Sales' THEN e.salary * 1.5 
   ELSE e.salary * 1.2
   END AS new_salary
   
   FROM employees e, department d
   WHERE e.department_id = d.department_id
   ORDER BY new_salary ASC;

8. 显示在每月中旬雇佣的员工,显示姓名，雇佣日期

   select first_name || last_name as full_name,hire_date
   from employees
   where extract ( day from hire_date ) between 11 and 20;