Oracle table common query Experiment (1)

Oracle table common query Experiment (1)

Exercise 1. query all departments in the DEPT table. Select * from dept; Exercise 2. query all information of the Department number and department name fields in the DEPT table. Select deptno, dname from dept; Exercise 3. query the employee name and salary of Department 10 in Table EMP. Select ename, sal from emp where deptno = 10; Exercise 4. Check the table EMP for the name and salary of the employee CLERK or MANAGER. Select ename, sal from emp where job = 'cler' or job = 'manager '; exercise 5. In the EMP table, find the name, Department number, salary, and work of the employee whose department number is between 10 and 30. Select ename, deptno, sal, job from emp where deptno between 10 and 30; Exercise 6. Find the names, salaries, and positions of all employees whose names start with J in Table EMP. Select ename, sal, job from emp where ename like 'J % '; Exercise 7. Check the name, job, and salary of the employee whose salary is less than 2000 in Table EMP, and arranged in descending order of wages. Select ename, job, sal from emp where sal <= 2000 order by sal desc; exercise 8. query from the table information about the name, salary, Department number, Department name, and Department address of the person who is working with CLERK. Select ename, sal, emp. deptno, dname, loc from emp, dept where emp. deptno = dept. deptno and job = 'cler'; exercise 9. query the names of all employees whose salaries are greater than or equal to 2000 in the EMP table and the names of their managers. Select. ename, B. ename from emp a, emp B where. mgr = B. empno (+) and. sal> = 2000; exercise 10. In table EMP, query the names, jobs, and salaries of all employees whose salaries are higher than JONES. Select ename, job, sal from emp where sal> (select sal from emp where ename = 'Jones '); exercise 11. List the names, jobs, and team numbers of all employees who do not have the corresponding department table information. Select ename, job, deptno from emp where deptno not in (select deptno from dept); exercise 12. Find the salary range of 1000 ~ Select * from emp where deptno in (select distinct deptno from emp where sal between 3000 and 1000 ); exercise 13. Who has the highest salary. Select ename from emp where sal = (select max (sal) from emp); select ename from (select * from emp order by sal desc) where rownum <= 1; exercise 14. Who has the second highest salary among the employees (consider the first case and how to handle it ). Select ename from (select ename, sal from (select * from emp order by sal desc) where rownum <= 2 order by sal) where rownum <= 1;