查询指定用户下所有表的数据库操作

8.8、确定一年内的天数

SELECT (ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12) - TRUNC(SYSDATE, 'YEAR')) AS days_in_year FROM DUAL;

8.9、查询EMP员工表下每个部门工资前二名的员工信息

SELECT e1.deptno, e1.ename, e1.sal 
FROM EMP e1 
WHERE (SELECT COUNT(1) FROM EMP e2 WHERE e2.deptno = e1.deptno AND e2.ename != e1.ename AND e2.sal > e1.sal) < 2
ORDER BY e1.deptno, e1.sal DESC;

或者使用窗口函数：

SELECT * 
FROM (
  SELECT deptno, ename, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn \
  FROM EMP
) WHERE rn < 3;

8.9、查找选了所有课程的学生信息

方法一：使用EXISTS和NOT EXISTS子查询

SELECT DISTINCT sname FROM STUDENT s 
WHERE NOT EXISTS (
  SELECT * FROM COURSE c 
  WHERE NOT EXISTS (
    SELECT * FROM CHOOSE_COURSE cc 
    WHERE s.sid = cc.sid AND c.cid = cc.cid
  )
);

方法二：使用GROUP BY和HAVING子句

SELECT student_id, COUNT(DISTINCT cid) \
FROM CHOOSE_COURSE \
GROUP BY student_id \
HAVING COUNT(DISTINCT cid) = (
  SELECT COUNT(DISTINCT cid) FROM COURSE
);

9.1、查询某用户下所有表

SELECT table_name FROM ALL_TABLES WHERE owner='SCOTT';