Questions about Oracle query statements

01. query all data in the employee table and describe the disadvantages of using *

Select * from emp;

01. query the salaries of employees whose job is 'President'

Select sal from emp where job = 'President ';

02. query the information of employees whose commission is 0 or null

Select * from emp where comm is null or comm = 1;

03. query information of all employees between and

Select * from emp where hiredate between to_date ('1-August-81 ') and to_date ('31-August-81 ')

04 .. query the number and name of all employees whose names are 4

Select empno, ename from emp where length (ename) = 4

05. Show all managers of Department 10 and all employees of Department 20

Select * from emp where job = 'manager' and deptno = 10 or job = 'cler' and deptno = 20;

06. display the detailed information of the employee whose name does not contain the word 'L' or the employee information containing the word 'sm'

Select * from emp where ename not like '% L %' or ename like '% SM % ';

07. display the salaries of various department managers

Select deptno, sal from emp where job = 'manager ';

08. display detailed information of employees with higher commission income than wages

Select * from emp where comm> sal;

10. Consider the hiredate column as the employee's birthday. Ask the employee who has his or her birthday this month:

SQL> select * from emp

Where to_char (hiredate, 'mm') = to_char (sysdate, 'mm ');

11. Consider the hiredate column as the employee's birthday.

SQL> select * from emp

Where to_char (hiredate, 'mm') = to_char (add_months (sysdate, 1

), 'Mm ');

12. Ask the employees who joined the company in 1982

SQL> select * from emp where to_char (hiredate, 'yyyy') = '123 ';

Hiredate is of the date type, and 1982 is of the string type. Only type matching is supported.

13. Ask the employees who joined the company in the second half of 1981

Select * from emp

Where hiredate between to_date ('1970-7-1 ', 'yyyy-mm-dd ')

And to_date ('2017-1-1 ', 'yyyy-mm-dd')-1;

Note: to_char () and to_date () Functions

The to_char () function converts the date type to the specified format.

To_date () converts a string to a date type.

-1 is more accurate

14. Calculate the number of employees hired in each month in 1981.

SQL> select to_char (hiredate, 'mm'), count (*) from emp where to_char (hiredate, 'yyy

Y') = '000000' group by to_char (hiredate, 'mm') order by to_char (hiredate, 'mm ');

Or

Select to_char (hiredate, 'mm'), count (*) from emp where to_char (hiredate, 'yyyy') = '000000' group by to_char (hiredate, 'mm ') order by to_char (hiredate, 'mm ');

PartII

01. query the average salary of each department

SQL> select deptno, avg (nvl (sal, 0) from emp group by deptno;

02. Display minimum wage for various positions

SQL> select job, min (sal) from emp group by job;

03. Arrange employee information from new to old by date of employment

SQL> select hiredate from emp order by hiredate desc;

04. query the basic information of an employee and append his/her superior name (self-Association)

SQL> select e. *, e1.ename from emp e, emp e1 where e. mgr = e1.empno;

05. Show the names and jobs of all employees with higher salaries than 'allen'

SQL> select ename, sal from emp where sal> (

Select sal from emp where ename = 'allen ');

06. display information of employees who work in the same way as scott (subquery)

SQL> select * from emp where job = (

Select job from emp where ename = 'Scott ');

07. display the name of the SALES Department ('sales') Employee

SQL> select e. ename from emp e inner join dept d on e. deptno = d. deptno where d. dname = 'sales ';

08. display the name and salary of the employee whose salaries are the same as those of 'martin 'at Gate 30.

SQL> select ename, sal from emp where sal = (select sal from emp where deptno = 30 and ename = 'martin ');

09. query sales staff whose salaries are higher than average (including all employees)

SQL> select * from emp where sal> (

Select avg (sal) from emp) and job = 'salesman ';

Or

SQL> select * from emp where job = 'salesman' and sal> (

Select avg (sal) from emp );

10. display the names of all employees and the names and salaries of their respective departments (table join)

SQL> select e. *, e. sal, d. dname from emp e inner join dept d on e. deptno = d. deptno;

11. query the ID, name, department, and location of the R & D department (RESEARCH) staff.

SQL> select e. empno, e. ename, d. dname, d. loc from emp e inner join dept d on e. deptno = d. deptno where dname = 'search ';

12. query the names and number of employees of each department

Select e. deptno, d. dname, count (*) from emp e inner join dept d on e. deptno = d. deptno group by e. deptno, d. dname

Analysis:

SQL> select d. dname from emp e inner join dept d on e. deptno = d. deptno group by e. deptno, d. dname;

DNAME

--------------

ACCOUNTING

RESEARCH

SALES

SQL> select e. deptno, d. dname from emp e inner join dept d on e. deptno = d. deptno group by e. deptno, d. dname;

DEPTNO DNAME

--------------------

10 ACCOUNTING

20 RESEARCH

30 SALES

SQL> select e. deptno, d. dname, count (*) from emp e inner join dept d on e. deptno = d. deptno group by e. deptno, d. dname;

Deptno dname count (*)

------------------------------

10 ACCOUNTING 3

20 RESEARCH 5

30 SALES 6

13. query the number of employees in each department who have higher salaries than the average salary (including all employees) and their positions (subquery)

SQL> select count (*), job from emp where sal> (select avg (sal) from emp) group by job;

14. query the salaries and names of employees with the same salary (subquery)

SQL> select sal, ename from emp e where (select count (*) from emp where sal = e. sal group by sal)> 1;

Or

SQL> select e. sal, e. ename from emp e, emp e1 where e. sal = e1.sal and e. ename <> e1.ename;

1 2 Next Page