Oracle数据库练习

--hr表

SELECT * FROM employees; 

--1.显示工资在5000到6000之间所有雇员名以及工资（格式为：本地货币符号，加千分位，保留两位小数。如RMB1,234.00）。
--CONCAT(列|字符串,列|字符串)，表示拼接字符串，此函数功能相当于“||”。

SELECT FIRST_NAME||FIRST_NAME 雇员,TO_CHAR(SALARY,'L9999,99')佣金
FROM HR.EMPLOYEES
WHERE SALARY>=5000 AND SALARY<=6000;

--2、分别使用to_char中的‘99999’和‘00000’两种格式显示数值123，比较0和9两个模式元素的区别。
--dual测试表
SELECT TO_char(123,'99999')测试99999 FROM dual;
SELECT TO_char(123,'00000')测试00000 FROM dual;

--3.返回当前日期、时间，格式为2010/03/30 20:08:10。
--日期格式为：'日-月-年'

SELECT to_char('19/8月/2021 20:08:10')日期 FROM dual;

--4.查询EMPLOYEES表，显示部门100所有雇员的FIRST_NAME以及工作月数（保留到整数）。
--trunc(列|数值,保留小数位数n)，当小数位数为“正数”时，对小数点右边的n位进行直接舍去；当小数位数为“负数”时， 

SELECT * FROM employees; 

SELECT
FIRST_NAME,trunc（MONTHS_between（sysdate，hire_date))AS
moths FROM hr.employees WHERE department_id=100;

--5.查询EMPLOYEES表，显示部门90所有雇员的FIRST_NAME以及工作年数（保留到整数）。
SELECT * FROM employees; 

SELECT
FIRST_NAME,trunc（MONTHS_between（sysdate，hire_date)/12)AS
years FROM hr.employees WHERE department_id=90;

--6.显示2002年以后工作的雇员的id和first_name及其直属上级的id和first_name。

SELECT e.employee_id eid,e.first_name ename,m.employee_id mid,m.first_name mname,e.manager_id
FROM (
    SELECT employee_id,first_name,manager_id
    FROM employees
    WHERE to_char(hire_date,'yyyy')>2002
) e LEFT JOIN employees m ON e.manager_id=m.employee_id;

--7.查询job_id为ST_MAN雇员的first_name、工资和所在部门名称。

SELECT t.first_name,t.salary,d.department_name
FROM (
     SELECT e.first_name,e.salary,e.department_id
     FROM (
          SELECT job_id
          FROM jobs
          WHERE upper(job_id)=upper('st_man')
          ) j LEFT JOIN employees e ON j.job_id=e.job_id
) t LEFT JOIN departments d ON t.department_id=d.department_id;

--8.显示job_title以Sales开头的雇员的first_name、工资及其岗位名称job_title（使用自然连接）。
      --模糊查询：like，可以使用通配符"%"(任意多个字符)和"_"(任意单个字符)。例：查询姓名中包含A的学生   sname like '%A%'

SELECT first_name,salary,job_title
FROM hr.employees NATURAL JOIN hr.jobs
WHERE job_title LIKE'Sales%';

--9.查询John的工资和由工资多少判断出的可能的工作岗位名称（使用不等连接，即salary与min_salary和max_salary的比较）

SELECT j.job_title
FROM jobs j,(
      SELECT distinct e.job_id
            FROM employees e
            WHERE NOT e.salary BETWEEN (
        SELECT min(salary)
        FROM employees
        WHERE Upper(first_name)=UPPER('JOHN')
      ) AND (
        SELECT max(salary)
        FROM employees
        WHERE Upper(first_name)=UPPER('JOHN')
      )
) t
WHERE j.job_id =t.job_id;

--10、从EMPLOYEES表中查询所有雇员的FIRST_NAME、LAST_NAME和补助（SALARY+COMMISSION_PCT），并处理null行。

  --NVL()    处理空函数，在数据库中null(空)值进行运算结果仍然为空，如果需要进行运算可以使用此函数进行处理。
SELECT * FROM employees;
  
SELECT first_name,last_name,NVL(salary+commission_pct,0)AS comm
FROM hr.employees;

--11、从EMPLOYEES表中查询所有部门号，并消除重复值。
SELECT * FROM employees; 

SELECT DISTINCT department_id
FROM hr.employees;

--12、从EMPLOYEES表中查询部门号为100的雇员的FIRST_NAME和工资。
SELECT * FROM employees; 

SELECT first_name,salary
FROM hr.employees WHERE department_id=100;

--13、查询EMPLOYEES表，显示JOB_ID中包含CLERK的所有雇员的FIRST_NAME和岗位。
SELECT * FROM employees; 
--模糊查询：like，可以使用通配符"%"(任意多个字符)和"_"(任意单个字符)。例：查询姓名中包含A的学生   sname like '%A%'
SELECT first_name,job_id FROM hr.employees WHERE job_id LIKE'%CLERK%';

--14、查询EMPLOYEES表，显示COMMISSION_PCT非空的所有雇员的LAST_NAME和COMMISSION_PCT。
SELECT * FROM employees; 

SELECT last_name,commission_pct
FROM hr.employees
WHERE commission_pct IS NOT NULL;

--15、显示部门80和90中工资高于9000的所有雇员的FIRST_NAME、LAST_NAME、SALARY和DEPARTMENT_ID。
SELECT * FROM employees; 

SELECT first_name,last_name,salary,department_id
FROM hr.employees
WHERE department_id IN(80,90)AND salary>9000;

--16、查询EMPLOYEES表，显示工资在5000-6000之间的所有雇员的FIRST_NAME 、JOB_ID和SALARY，并以工资降序排序。
SELECT * FROM employees; 

SELECT first_name,job_id,salary
FROM hr.employees
WHERE salary BETWEEN 5000 AND 6000
ORDER BY salary DESC

--17、查询EMPLOYEES表，显示部门号为100的所有雇员的FIRST_NAME 、JOB_ID、SALARY和 DEPARTMENT_ID，并以岗位降序、工资升序进行排序。
SELECT * FROM employees; 

SELECT first_name,job_id,salary,department_id
FROM hr.employees
WHERE department_id=100
      --⑥[ORDER BY 排序列名 [ASC|DESC],排序列名 [ASC|DESC],……]
ORDER BY job_id DESC,salary ASC

--18、查询EMPLOYEES表，显示所有雇员的全名、工资，工资降序进行排序。

SELECT first_name||first_name 雇员,salary 工资
       --⑥[ORDER BY 排序列名 [ASC|DESC],排序列名 [ASC|DESC],……]
FROM hr.employees ORDER BY salary DESC 

--19.查询John的编号、first_name、工作单位名称和工作的城市。

--多表查询 ，效率太低，少用
SELECT e.employee_id,e.first_name,j.job_title,l.city
FROM employees e,departments d,jobs j,locations l
WHERE e.job_id=j.job_id AND e.department_id=d.department_id
AND d.location_id=l.location_id
AND Upper(first_name)=UPPER('JOHN');

--子查询 ，提高效率，多用\
SELECT e.employee_id,e.first_name,e.job_id,d.location_id
FROM (
     SELECT employee_id,first_name,job_id,department_id
     FROM employees
     WHERE Upper(first_name)=UPPER('JOHN')
) e LEFT JOIN departments d ON e.department_id=d.department_id
LEFT JOIN jobs j ON e.job_id=j.job_id
LEFT JOIN locations l ON d.location_id=l.location_id;

--子查询
SELECT t2.employee_id,t2.first_name,t2.job_title,l.city
FROM (
  --查询出工作单位
  SELECT t1.employee_id,t1.first_name,j.job_title,t1.location_id
  FROM (
    --查询出部门
    SELECT e.employee_id,e.first_name,e.job_id,d.location_id
    FROM (
         --过滤出john的数据
         SELECT employee_id,first_name,job_id,department_id
         FROM employees
         WHERE Upper(first_name)=UPPER('JOHN')
    ) e LEFT JOIN departments d ON e.department_id=d.department_id
  ) t1 LEFT JOIN jobs j ON t1.job_id=j.job_id
) t2 LEFT JOIN locations l ON t2.location_id=l.location_id;