Oracle Database Review 2. Oracle Database Review

Oracle Database Review 2. Oracle Database Review
Subquery and table connection
1. query the names of the people who make the most money
SELECT ename, sal FROM emp
WHERE sal = (select max (sal) FROM emp );
②. Query the employees whose salaries are above the average salaries of all people.
SELECT ename, sal FROM emp
WHERE sal> (select avg (sal) FROM emp );
③. Find out who has the highest salary in the Department:
SELECT ename, sal FROM emp
JOIN (select max (sal) max_sal, deptno FROM emp group by deptno) t
ON emp. sal = t. max_sal AND emp. deptno = t. deptno;
4. Find the employee name and manager name.
SELECT e1.ename, e2.ename FROM
Emp e1 JOIN emp e2
ON e1.mgr = e2.empno;
⑤ Query the salary grade of each person
SELECT ename, grade FROM emp e JOIN salgrade s
ON (e. sal BETWEEN s. losal AND s. hisal );
6. query the Department name and salary level of each person.
SELECT ename, dname, grade FROM
Emp e JOIN dept d ON (e. deptno = d. deptno)
JOIN salgrade s ON (e. sal BETWEEN s. losal AND s. hisal)
(Left Outer Join) query the employee name and manager name
SELECT e1.ename, e2.ename FROM
Emp e1 left join emp e2
ON e1.mgr = e2.empno;
Locate (right Outer Join) to query the department names of all employees
SELECT ename, dname FROM
Emp e right join dept d
ON e. deptno = d. deptno;
The Department name of all employees is displayed in the dig command.
SELECT ename, dname FROM
Emp e full join dept d
ON e. deptno = d. deptno;
Evaluate the average salary level of the Department
SELECT deptno, avg_sal, grade FROM
(SELECT deptno, AVG (sal) avg_sal FROM emp group by deptno) t
JOIN salgrade s
ON t. avg_sal BETWEEN s. losal AND s. hisal;
-- Calculate the average salary level of the Department
SELECT deptno, AVG (grade) FROM
(SELECT deptno, ename, grade FROM emp JOIN salgrade s ON
Emp. sal BETWEEN s. losal AND s. hisal) t
Group by deptno;
-- Ask employees who are managers
SELECT ename FROM emp
WHERE empno in (select distinct mgr FROM emp );
-- The Group function is not allowed to calculate the highest salary.
SELECT sal FROM emp WHERE sal NOT IN
(Select distinct e1.sal FROM emp e1 JOIN emp e2
ON e1.sal <e2.sal );
-- Department ID of the Department with the highest average salary
SELECT deptno, avg_sal FROM
(SELECT deptno, AVG (sal) avg_sal FROM emp group by deptno );
WHERE avg_sal =
(Select max (avg_sal) FROM
(SELECT deptno, AVG (sal) avg_sal FROM emp group by deptno ));
-- Name of the Department with the highest average salary
SELECT dname FROM dept WHERE deptno =
(SELECT deptno FROM
(SELECT deptno, AVG (sal) avg_sal FROM emp group by deptno)
WHERE avg_sal =
(Select max (avg_sal) FROM
(SELECT deptno, AVG (sal) avg_sal FROM emp group by deptno)
)
);

-- Name of the Department with the lowest average salary
SELECT dept. dname, t1.deptno, grade, avg_sal FROM
(
SELECT deptno, grade, avg_sal FROM
(Select avg (sal) avg_sal, deptno FROM emp group by deptno) t JOIN salgrade s
ON t. avg_sal BETWEEN s. losal AND s. hisal
) T1
JOIN dept ON (t1.deptno = dept. deptno)
WHERE t1.grade =
(Select min (grade) FROM
(SELECT deptno, grade, avg_sal FROM
(Select avg (sal) avg_sal, deptno FROM emp group by deptno) t JOIN salgrade s
ON t. avg_sal BETWEEN s. losal AND s. hisal ))

-- Ask the Department name with the lowest average salary for the department manager


-- Calculate the name of a manager whose salary is higher than that of an ordinary employee
SELECT ename FROM emp WHERE
Empno IN (select distinct mgr FROM emp WHERE mgr is not null)
AND sal>
(SELECT max (sal) FROM emp WHERE empno NOT IN
(Select distinct mgr FROM emp WHERE mgr is not null ))

-- Top five employees with the highest salaries
SELECT ename, sal
FROM (
SELECT ename, sal FROM emp order by sal DESC
)
Where rownum <= 5;
-- 6th to 10th employees with the highest salary
SELECT ename, sal FROM (
SELECT ename, sal, ROWNUM r FROM (
SELECT ename, sal FROM emp order by sal DESC)
)

WHERE r> = 6 AND r <= 10;

Note: The query statement is produced by watching the video of the Trojan soldier.

Level 2 database

Secondary exam, you can choose one of them as the main direction of your exam. Among them, the most practical is Java, and Access. Java can be said to be the most popular software at present. If you want to continue your studies, this is the best choice. Access is a very practical software, no matter which industry you will be engaged in, database Management is very helpful in data compilation, sorting, and processing. This greatly improves your efficiency. As for other VB, VF, and other things, this is not to say that they are useless. They are only obsolete software. Now, it can be said that they are useless, if you want to learn it, it's just for passing the level, but for having a level-2 certificate. If you want to mix the certificate, it is easiest to test foxpro. For books, it is good to read the teaching materials directly, because it is the most authoritative.

How can I read data from two databases at the same time?

The two databases are easy to read and write. Use the following SQL statement in ASP:
Select database 1. name_left + database 2. name_right as name from database 1, database 2

But does the name already exist in the database? Assume that there is a database 3 with the field "people", the above statement should be:
Select database 1. name_left + database 2. name_right as name from database 1, database 2, database 3 where name = database 3. people
If there is a record running, it indicates that it exists in database 3.