Shulou(Shulou.com)06/01 Report--

SQL > conn scott/tiger@clonepdb_plug

Connected.

SQL > desc emp

Name Null? Type

EMPNO NOT NULL NUMBER (4)

ENAME VARCHAR2 (10)

JOB VARCHAR2 (9)

MGR NUMBER (4)

HIREDATE DATE

SAL NUMBER (7 dint 2)

COMM NUMBER (7 dint 2)

DEPTNO NUMBER (2)

SQL > desc dept

Name Null? Type

DEPTNO NOT NULL NUMBER (2)

DNAME VARCHAR2 (14)

LOC VARCHAR2 (13)

Tip: salary = SAL+COMM

SQL > set line 100

SQL > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7566 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 78391981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7900 JAMES CLERK 7698 2450 12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

12 rows selected.

SQL > select * from dept

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO

1. List all departments with at least one employee.

SQL > select dname from dept where deptno in (select DEPTNO from emp)

DNAME

ACCOUNTING

RESEARCH

SALES

2. List all employees who earn more than "SMITH".

SQL > select ENAME from emp where sal > (select sal from emp where ename='SMITH')

ENAME

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

KING

TURNER

JAMES

FORD

MILLER

11 rows selected.

3. List the names of all employees and their immediate superiors.

SQL > select a.ename, (select b.ename from emp b where a.mgr=b.empno) Boss from emp a

ENAME BOSS

SMITH FORD

ALLEN BLAKE

WARD BLAKE

JONES KING

MARTIN BLAKE

BLAKE KING

CLARK KING

KING

TURNER BLAKE

JAMES BLAKE

FORD JONES

ENAME BOSS

MILLER CLARK

12 rows selected.

4. List all employees whose employment date is earlier than their immediate superiors.

SQL > select a.ename from emp a where a.HIREDATE > (select b.HIREDATE from emp b where a.mgr=b.empno)

ENAME

MARTIN

TURNER

JAMES

FORD

MILLER

5. List the name of the department and the employee information of these departments, as well as those departments that do not have employees

SQL > select a.deptnorea.dnamereb.enamereb.empno from dept arecedence emp b where a.deptno=b.deptno (+)

DEPTNO DNAME ENAME EMPNO 10 ACCOUNTING KING 7839 10 ACCOUNTING CLARK 7782 10 ACCOUNTING MILLER 7934 20 RESEARCH FORD 7902 20 RESEARCH SMITH 7369 20 RESEARCH JONES 7566 30 SALES JAMES 7900 30 SALES TURNER 7844 30 SALES MARTIN 7654 30 SALES WARD 7521 30 SALES ALLEN 7499DEPTNO DNAME ENAME EMPNO 30 SALES BLAKE 7698

12 rows selected.

SQL > select a.deptnorea.dnameauthorb.enamereb.empno from dept a left join emp b on a.deptno=b.deptno

DEPTNO DNAME ENAME EMPNO 10 ACCOUNTING KING 7839 10 ACCOUNTING CLARK 7782 10 ACCOUNTING MILLER 7934 20 RESEARCH FORD 7902 20 RESEARCH SMITH 7369 20 RESEARCH JONES 7566 30 SALES JAMES 7900 30 SALES TURNER 7844 30 SALES MARTIN 7654 30 SALES WARD 7521 30 SALES ALLEN 7499DEPTNO DNAME ENAME EMPNO 30 SALES BLAKE 7698

12 rows selected.

Oracle external connections (OUTER JOIN) include the following:

Left outer join (unrestricted table on the left)

Right outer join (the table on the right is unrestricted)

Full external connection (no restrictions on both left and right tables)

Corresponding to SQL:LEFT/RIGHT/FULL OUTER JOIN. The OUTER keyword is usually omitted and written as: LEFT/RIGHT/FULL JOIN.

Both the left join and the right join will be based on a table A, and all the contents of the table will be displayed, followed by a match between table An and table B. If the data in table An is not recorded in table B. Then the column appears as a null value (NULL) in the associated result set row.

For external connections, you can also use "(+)" to indicate. Some considerations about using (+):

The (+) operator can only appear in the WHERE clause and cannot be used in conjunction with OUTER JOIN syntax.

When using the (+) operator to perform an outer join, if there are multiple conditions in the WHERE clause, you must include the (+) operator in all conditions.

The (+) operator applies only to columns, not to expressions.

The (+) operator cannot be used with OR and IN operators.

The (+) operator can only be used for left and right outer joins, not for full outer joins.

LEFT JOIN is based on the record in the left table; implemented with (+), the + sign can be understood as follows: + indicates a supplement, that is, which table has a plus sign, the table is the matching table. If the plus sign is written in the right table, the left table shows all, so it is a left connection.

Full external connection (FULL OUTER JOIN/FULL JOIN)

There are no restrictions on the left and right tables, and all records show that the deficiency in both tables is NULL. Full external connection does not support (+) writing.

6. List the names of all "CLERK" (clerks) and their department names.

SQL > select a.enamedirection b.dname from emp a join dept b on a.deptno=b.deptno and A. jobless cleaning

ENAME DNAME

MILLER ACCOUNTING

SMITH RESEARCH

JAMES SALES

SQL > select a. Ename. b.dname from emp a, dept b where a.deptno=b.deptno and a. Jobless clerk'

ENAME DNAME

SMITH RESEARCH

JAMES SALES

MILLER ACCOUNTING

7. List jobs with a minimum salary greater than 1500.

SQL > select distinct Job from emp group by job having min (sal) > 1500

JOB

PRESIDENT

MANAGER

ANALYST

8. List the names of employees working in the department "SALES" (sales department), assuming that you do not know the department number of the sales department.

SQL > select ename from emp where deptno= (select deptno from dept where dname='SALES')

ENAME

ALLEN

WARD

MARTIN

BLAKE

TURNER

JAMES

6 rows selected.

SQL > select ename from emp a join dept b on a.deptno=b.deptno and b.dnameplate sales

ENAME

JAMES

TURNER

MARTIN

WARD

ALLEN

BLAKE

6 rows selected.

SQL > select ename from emp adept b where a.deptno=b.deptno and b.dnameplates sales'

ENAME

JAMES

TURNER

MARTIN

WARD

ALLEN

BLAKE

6 rows selected.

9. List all employees whose salary is higher than the average salary of the company.

SQL > select * from emp where sal > (select avg (sal) from emp)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

10. List all employees who do the same job as "SMITH"

SQL > select ename from emp where job= (select job from emp where ename='SMITH')

ENAME

SMITH

JAMES

MILLER

11. List the names and salaries of all employees whose salary is equal to that of the employees in the department 30.

SQL > select a.ename.sal from emp a where a.sal in (select b.sal from emp b where b.deptno=30) and a.deptno30

No rows selected

12. List the names and salaries of employees whose salaries are higher than those of all employees working in department 30.

SQL > select a.ename.sal from emp a where a.sal > (select max (b.sal) from emp b where b.deptno=30) and a.deptno30

ENAME SAL

JONES 2975

KING 5000

FORD 3000

13. List the number of employees working in each department and the average salary

SQL > select a.dname, (select count (*) from emp b where a.deptno=b.deptno) as DEPTCOUNT, (select avg (sal) from emp b where a.deptno=b.deptno) as deptavgsal from depta

DNAME DEPTCOUNT DEPTAVGSAL

ACCOUNTING 3 2916.66667

RESEARCH 3 2258.33333

SALES 6 1566.66667

SQL > select (select dname from dept b where a.deptno=b.deptno) as dname,count (a.deptno) as deptcount,avg (sal) as deptavgsal from emp a group by deptno

2

DNAME DEPTCOUNT DEPTAVGSAL

SALES 6 1566.66667

RESEARCH 3 2258.33333

ACCOUNTING 3 2916.66667

14. List the names, department names and salaries of all employees.

SQL > select a.enamedir b.dnametema. Salient NVL (a.commpendium 0) from emp a join dept b on a.deptno=b.deptno

ENAME DNAME A.SAL+NVL (A.COMMPhon0)

KING ACCOUNTING 5000

CLARK ACCOUNTING 2450

MILLER ACCOUNTING 1300

FORD RESEARCH 3000

SMITH RESEARCH 800

JONES RESEARCH 2975

JAMES SALES 950

TURNER SALES 1500

MARTIN SALES 2650

WARD SALES 1750

ALLEN SALES 1900

ENAME DNAME A.SAL+NVL (A.COMMPhon0)

BLAKE SALES 2850

15. List the details of all departments and the number of departments.

SQL > select a.dame.loc, (select count (*) from emp where deptno=a.deptno) from dept a

DNAME LOC (SELECTCOUNT (*) FROMEMPWHEREDEPTNO=A.DEPTNO)

ACCOUNTING NEW YORK 3

RESEARCH DALLAS 3

SALES CHICAGO 6

16. List the minimum wage for various jobs.

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

JOB AVG (SAL)

CLERK 1016.66667

SALESMAN 1400

PRESIDENT 5000

MANAGER 2758.33333

ANALYST 3000

17. List the minimum salary of MANAGER (managers) in each department

SQL > select deptno,avg (sal) from emp where job='MANAGER' group by deptno

DEPTNO AVG (SAL) 30 2850 20 2975 10 2450

18. List the annual salaries of all employees, sorted from lowest to highest.

SQL > select ename, (sal+nvl (comm,0)) 12 from emp order by (sal+nvl (comm,0)) 12

ENAME (SAL+NVL (COMM,0)) * 12

SMITH 9600

JAMES 11400

MILLER 15600

TURNER 18000

WARD 21000

ALLEN 22800

CLARK 29400

MARTIN 31800

BLAKE 34200

JONES 35700

FORD 36000

ENAME (SAL+NVL (COMM,0)) * 12

KING 60000

SQL > select ename, (sal+nvl (comm,0)) * 12 as salpersal from emp order by salpersal

ENAME SALPERSAL

SMITH 9600

JAMES 11400

MILLER 15600

TURNER 18000

WARD 21000

ALLEN 22800

CLARK 29400

MARTIN 31800

BLAKE 34200

JONES 35700

FORD 36000

ENAME SALPERSAL

KING 60000

12 rows selected.

19. Find the name (ENAME) in the EMP table. The third letter is the name of the employee whose letter is A.

SQL > select ename from emp where substr (ename,3,1) ='A'

ENAME

BLAKE

CLARK

SQL > SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE'_ A%'

ENAME

BLAKE

CLARK

Find out the names of employees with An and N in their names in the EMP table.

SQL > SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE'% A%'and ENAME LIKE'% N% ename

ALLEN

MARTIN

Find out all the employees who have commission, list their name, salary and commission, and display the results from small to large.

The commission ranges from big to small.

SQL > select ename,sal+nvl (comm,0) as total, comm from emp order by total, comm desc nulls last

ENAME TOTAL COMM

SMITH 800

JAMES 950

MILLER 1300

TURNER 1500 0

WARD 1750 500

ALLEN 1900 300

CLARK 2450

MARTIN 2650 1400

BLAKE 2850

JONES 2975

FORD 3000

ENAME TOTAL COMM

KING 5000

12 rows selected.

twenty-two。 List all positions with department number 20

SQL > select job from emp where deptno=20

JOB

CLERK

MANAGER

ANALYST

23. List departments that do not belong to SALES

SQL > select dname from dept where dnameplate sales

2

DNAME

ACCOUNTING

RESEARCH

SQL > select dname from dept where dname'SALES'

DNAME

ACCOUNTING

RESEARCH

Displays information about employees whose salary is not between 1000 and 1500: name, salary, sorted by salary from largest to smallest.

SQL > select ename,sal+nvl (comm,0) as total from emp where sal+nvl (comm,0) not between 1000 and 1500 order by total desc

ENAME TOTAL

KING 5000

FORD 3000

JONES 2975

BLAKE 2850

MARTIN 2650

CLARK 2450

ALLEN 1900

WARD 1750

JAMES 950

SMITH 800

10 rows selected.

Displays information about employees whose positions are MANAGER and SALESMAN and whose annual salary is between 15000 and 20000: name, position, annual salary.

SQL > select ename,job, (sal+nvl (comm,0)) 12 as total from emp where (sal+nvl (comm,0)) 12 between 15000 and 20000

ENAME JOB TOTAL

TURNER SALESMAN 18000

MILLER CLERK 15600

Describe the output of the following two SQL statements:

SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL

SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL

SQL > SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL

EMPNO COMM

7369

7566

7698

7782

7839

7900

7902

7934

8 rows selected.

SQL > SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL

No rows selected

IS NULL is to determine whether a field is empty, which is not equivalent to an empty string or the number 0

And = NULL is to determine whether a certain value is equal to NULL,NULL = NULL and NULL NULL are both FALSE

Let the output of the SELECT statement be

SELECT FROM SALGRADE

SELECT FROM BONUS

SELECT FROM EMP

SELECT FROM DEPT

……

List how many data tables the current user has and how many records exist in the result set

SQL > SELECT 'SELECT*FROM' | | TABLE_NAME |'; 'FROM USER_TABLES;'SELECT*FROM' | | TABLE_NAME | |';'

SELECT FROM DEPT

SELECT FROM EMP

SELECT FROM BONUS

SELECT FROM SALGRADE

SQL > SELECT concat (concat ('select * from', table_name),';') FROM user_tables

CONCAT (CONCAT ('SELECT*FROM',TABLE_NAME),';')

Select from DEPT

Select from EMP

Select from BONUS

Select from SALGRADE

Is there an error in the statement SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'?

SQL > SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'

2

ENAME SAL

ALLEN 1600

JONES 2975

BLAKE 2850

CLARK 2450

KING 5000

FORD 3000

6 rows selected.

There are no errors. There are implicit data types here.

SQL > conn hr/hr@pdbtest

Connected.

SQL > desc employees

Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER (6)

FIRST_NAME VARCHAR2 (20)

LAST_NAME NOT NULL VARCHAR2 (25)

EMAIL NOT NULL VARCHAR2 (25)

PHONE_NUMBER VARCHAR2 (20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2 (10)

SALARY NUMBER (8 dint 2)

COMMISSION_PCT NUMBER (2Jing 2)

MANAGER_ID NUMBER (6)

DEPARTMENT_ID NUMBER (4)

SQL > desc DEPARTMENTS

Name Null? Type

DEPARTMENT_ID NOT NULL NUMBER (4)

DEPARTMENT_NAME NOT NULL VARCHAR2 (30)

MANAGER_ID NUMBER (6)

LOCATION_ID NUMBER (4)

SQL > desc REGIONS

Name Null? Type

REGION_ID NOT NULL NUMBER

REGION_NAME VARCHAR2 (25)

twenty-nine。 Let SELECT TO_CHAR (SALARY,'L99999.99') FROM EMPLOYEES WHERE ROWNUM

< 5 输出结果的货币单位是￥和$。 SQL>

SELECT TO_CHAR (SALARY,'L99999.99') FROM EMPLOYEES WHERE ROWNUM

< 5; TO_CHAR(SALARY,'L99, $24,000.00 $17,000.00 $17,000.00 $9,000.00 30.列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以"元"为单位进行四舍五入。 SQL>

Select FIRST_NAME,SALARY,round (SALARY*1.08) from EMPLOYEES where rownum select upper (FIRST_NAME | |''| | LAST_NAME) from EMPLOYEES where MANAGER_ID is null

UPPER (FIRST_NAME | |''| | LAST_NAME)

STEVEN KING

thirty-two。 Find out the name of the direct leader whose First_Name is David,Last_Name and Austin.

SQL > select FIRST_NAME | |''| | LAST_NAME from EMPLOYEES where EMPLOYEE_ID= (select MANAGER_ID from EMPLOYEES where FIRST_NAME='David' and LAST_NAME='Austin')

FIRST_NAME | |''| | LAST_NAME

Alexander Hunold

Who does 33.First_Name lead for Alexander,LAST_NAME and Hunold?

SQL > select FIRST_NAME | |''| | LAST_NAME from EMPLOYEES where MANAGER_ID= (select EMPLOYEE_ID from EMPLOYEES where FIRST_NAME='Alexander' and LAST_NAME='Hunold')

FIRST_NAME | |''| | LAST_NAME

Bruce Ernst

David Austin

Valli Pataballa

Diana Lorentz

thirty-four。 Which employee's salary is higher than that of his immediate boss, list the employee's name and salary, the boss's name and salary.

SQL > select FIRST_NAME | |''| | LAST_NAME,salary, (select FIRST_NAME | |''| | LAST_NAME from EMPLOYEES where EMPLOYEE_id=a.manager_id) as boss_name, (select salary from EMPLOYEES where EMPLOYEE_id=a.manager_id) as boss_salary from EMPLOYEES a where a.salary > (select salary from EMPLOYEES where EMPLOYEE_id=a.manager_id)

FIRST_NAME''LAST SALARY BOSS_NAME BOSS_SALARY

Lisa Ozer 11500 Gerald Cambrault 11000

Ellen Abel 11000 Eleni Zlotkey 10500

SQL > select a.FIRST_NAME | |'| | a.LASTZNAMErect a.salaryReed b.FIRSTRENAME | |'| b.LASTZNAMEauthorb.LASTRENAME from EMPLOYEES a join EMPLOYEES b on b.EMPLOYEE_id=a.manager_id and a.salary > b.salary

A.FIRST_NAME''A. SALARY B.FIRST_NAME''B.LAST_NAME SALARY

Lisa Ozer 11500 Gerald Cambrault 11000

Ellen Abel 11000 Eleni Zlotkey 10500

thirty-five。 Which employees are in the same department as Chen (LAST_NAME)?

SQL > select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES where DEPARTMENT_ID= (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Chen') and lastest namesake

EMPLOYEE_ID FIRST_NAME

108 Nancy 109 Daniel 111 Ismael 112 Jose Manuel 113 Luis

SQL > select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Chen') b using (DEPARTMENT_ID) where lastest namesake

EMPLOYEE_ID FIRST_NAME

108 Nancy 109 Daniel 111 Ismael 112 Jose Manuel 113 Luis

thirty-six。 Which employees hold the same position as De Haan (LAST_NAME)?

SQL > select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_id from EMPLOYEES where LAST_NAME='De Haan') b using (job_id) where lastest name ordered by de Haan'

2

EMPLOYEE_ID FIRST_NAME

101 Neena

SQL > select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_id from EMPLOYEES where LAST_NAME='De Haan') b on a.job_id=b.job_id and recently named de Haan'

2

EMPLOYEE_ID FIRST_NAME

101 Neena

thirty-seven。 Which employees are not in the same department as Hall (LAST_NAME).

SQL > select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Hall') b on a.DEPARTMENTID.DEPARTMENTID

EMPLOYEE_ID FIRST_NAME

100 Steven 101 Neena 102 Lex 103 Alexander 104 Bruce 105 David 106 Valli 107 Diana 108 Nancy 109 Daniel 110 John

EMPLOYEE_ID FIRST_NAME

111 Ismael 112 Jose Manuel 113 Luis 114 Den 115 Alexander 116 Shelli 117 Sigal 118 Guy 119 Karen 120 Matthew 121 Adam

EMPLOYEE_ID FIRST_NAME

122 Payam 123 Shanta 124 Kevin 125 Julia 126 Irene 127 James 128 Steven 129 Laura 130 Mozhe 131 James 132 TJ

EMPLOYEE_ID FIRST_NAME

133 Jason 134 Michael 135 Ki 136 Hazel 137 Renske 138 Stephen 139 John 140 Joshua 141 Trenna 142 Curtis 143 Randall

EMPLOYEE_ID FIRST_NAME

144 Peter 180 Winston 181 Jean 182 Martha 183 Girard 184 Nandita 185 Alexis 186 Julia 187 Anthony 188 Kelly 189 Jennifer

EMPLOYEE_ID FIRST_NAME

190 Timothy 191 Randall 192 Sarah 193 Britney 194 Samuel 195 Vance 196 Alana 197 Kevin 198 Donald 199 Douglas 200 Jennifer

EMPLOYEE_ID FIRST_NAME

201 Michael 202 Pat 203 Susan 204 Hermann 205 Shelley 206 William

72 rows selected.

thirty-eight。 Which employees do different positions with William (FIRST_NAME) and Smith (LAST_NAME).

SQL > select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_ID from EMPLOYEES where FIRST_NAME='William'and LAST_NAME='Smith') b on a. Jobless IDs

EMPLOYEE_ID FIRST_NAME

100 Steven 101 Neena 102 Lex 103 Alexander 104 Bruce 105 David 106 Valli 107 Diana 108 Nancy 109 Daniel 110 John

EMPLOYEE_ID FIRST_NAME

111 Ismael 112 Jose Manuel 113 Luis 114 Den 115 Alexander 116 Shelli 117 Sigal 118 Guy 119 Karen 120 Matthew 121 Adam

EMPLOYEE_ID FIRST_NAME

122 Payam 123 Shanta 124 Kevin 125 Julia 126 Irene 127 James 128 Steven 129 Laura 130 Mozhe 131 James 132 TJ

EMPLOYEE_ID FIRST_NAME

133 Jason 134 Michael 135 Ki 136 Hazel 137 Renske 138 Stephen 139 John 140 Joshua 141 Trenna 142 Curtis 143 Randall

EMPLOYEE_ID FIRST_NAME

144 Peter 145 John 146 Karen 147 Alberto 148 Gerald 149 Eleni 180 Winston 181 Jean 182 Martha 183 Girard 184 Nandita

EMPLOYEE_ID FIRST_NAME

185 Alexis 186 Julia 187 Anthony 188 Kelly 189 Jennifer 190 Timothy 191 Randall 192 Sarah 193 Britney 194 Samuel 195 Vance

EMPLOYEE_ID FIRST_NAME

196 Alana 197 Kevin 198 Donald 199 Douglas 200 Jennifer 201 Michael 202 Pat 203 Susan 204 Hermann 205 Shelley 206 William

77 rows selected.

thirty-nine。 Display the information of the employee with commission: name, commission, name of department, name of region.

SQL > select FIRST_NAME | |''| | LAST_NAME, COMMISSION_PCT from EMPLOYEES a join (DEPARTMENTS b join LOCATIONS c using (LOCATION_ID)) using (DEPARTMENT_ID) where COMMISSION_PCT is not null

FIRST_NAME''LAST COMMISSION_PCT

John Russell. 4

Karen Partners. 3

Alberto Errazuriz. 3

Gerald Cambrault. 3

Eleni Zlotkey. 2

Peter Tucker. 3

David Bernstein. 25

Peter Hall. 25

Christopher Olsen. 2

Nanette Cambrault. 2

Oliver Tuvault. 15

FIRST_NAME''LAST COMMISSION_PCT

Janette King. 35

Patrick Sully. 35

Allan McEwen. 35

Lindsey Smith. 3

Louise Doran. 3

Sarath Sewall. 25

Clara Vishney. 25

Danielle Greene. 15

Mattea Marvins. 1

David Lee. 1

Sundar Ande. 1

FIRST_NAME''LAST COMMISSION_PCT

Amit Banda. 1

Lisa Ozer. 25

Harrison Bloom. 2

Tayler Fox. 2

William Smith. 15

Elizabeth Bates. 15

Sundita Kumar. 1

Ellen Abel. 3

Alyssa Hutton. 25

Jonathon Taylor. 2

Jack Livingston. 2

FIRST_NAME''LAST COMMISSION_PCT

Charles Johnson. 1

34 rows selected.

SQL > select FIRST_NAME | |''| | LAST_NAME, COMMISSION_PCT from EMPLOYEES a, DEPARTMENTS bdepartment HR.locations c where a.DEPARTMENT_ID = b.DEPARTMENT_ID and a.COMMISSION_PCT is not null and b.LOCATION_ID = c.LOCATION_ID

FIRST_NAME''LAST COMMISSION_PCT

John Russell. 4

Karen Partners. 3

Alberto Errazuriz. 3

Gerald Cambrault. 3

Eleni Zlotkey. 2

Peter Tucker. 3

David Bernstein. 25

Peter Hall. 25

Christopher Olsen. 2

Nanette Cambrault. 2

Oliver Tuvault. 15

FIRST_NAME''LAST COMMISSION_PCT

Janette King. 35

Patrick Sully. 35

Allan McEwen. 35

Lindsey Smith. 3

Louise Doran. 3

Sarath Sewall. 25

Clara Vishney. 25

Danielle Greene. 15

Mattea Marvins. 1

David Lee. 1

Sundar Ande. 1

FIRST_NAME''LAST COMMISSION_PCT

Amit Banda. 1

Lisa Ozer. 25

Harrison Bloom. 2

Tayler Fox. 2

William Smith. 15

Elizabeth Bates. 15

Sundita Kumar. 1

Ellen Abel. 3

Alyssa Hutton. 25

Jonathon Taylor. 2

Jack Livingston. 2

FIRST_NAME''LAST COMMISSION_PCT

Charles Johnson. 1

34 rows selected.

forty。 Show what positions are available in the Executive department

SQL > SELECT DISTINCT JOB_ID FROM EMPLOYEES a join DEPARTMENTS b using (DEPARTMENT_ID) where b.DEPARTMENT_NAME = 'Executive'

JOB_ID

AD_VP

AD_PRES

forty-one。 What is the difference between the maximum wage and the minimum wage in the whole company?

SQL > SELECT MAX (SALARY)-MIN (SALARY) FROM EMPLOYEES

MAX (SALARY)-MIN (SALARY) 21900

forty-two。 The number of people whose commission is greater than 0.

SQL > SELECT count (*) FROM EMPLOYEES where COMMISSION_PCT > 0

COUNT (*) 35

forty-three。 Displays the maximum wage, the minimum wage, the sum of wages and the average wage of the whole company to be kept to integer digits.

SQL > SELECT MAX (SALARY), MIN (SALARY), sum (SALARY), avg (SALARY) FROM EMPLOYEES

MAX (SALARY) MIN (SALARY) SUM (SALARY) AVG (SALARY)

24000 2100 691416 6461.83178

forty-four。 How many leaders are there in the whole company?

SQL > SELECT count (DISTINCT (NVL (manager_id,'1') FROM employees e

COUNT (DISTINCT (NVL (MANAGER_ID,'1') 19

forty-five。 List employees who have a late entry date in the same department but earn more than other colleagues:

Name, salary, entry date.

SQL > select distinct a.FIRSTRANAMEMagna. SALARYpapa.HIREDATEDATE from employees a join employees b on a.DEPARTMENT_ID=b.DEPARTMENT_ID and a.SALARY > b.SALARY and a.HIRE_DATE > b.HIRE_DATE order by a.salary desc

FIRST_NAME SALARY HIRE_DATE

Steven 24000 2003-06-17 00:00:00

John 14000 2004-10-01 00:00:00

Karen 13500 2005-01-05 00:00:00

Nancy 12008 2002-08-17 00:00:00

Alberto 12000 2005-03-10 00:00:00

Lisa 11500 2005-03-11 00:00:00

Ellen 11000 2004-05-1100: 00:00

Gerald 11000 2007-10-15 00:00:00

Clara 10500 2005-11-11 00:00:00

Eleni 10500 2008-01-29 00:00:00

Harrison 10000 2006-03-23 00:00:00

FIRST_NAME SALARY HIRE_DATE

Peter 10000 2005-01-30 00:00:00

Tayler 9600 2006-01-24 00:00:00

Danielle 9500 2007-03-19 00:00:00

David 9500 2005-03-24 00:00:00

Alexander 9000 2006-01-03 00:00:00

Peter 9000 2005-08-20 00:00:00

Alyssa 8800 2005-03-19 00:00:00

Jonathon 8600 2006-03-24 00:00:00

Jack 8400 2006-04-23 00:00:00

Adam 8200 2005-04-10 00:00:00

Christopher 8000 2006-03-30 00:00:00

FIRST_NAME SALARY HIRE_DATE

Matthew 8000 2004-07-18 00:00:00

Jose Manuel 7800 2006-03-07 00:00:00

Nanette 7500 2006-12-09 00:00:00

William 7400 2007-02-23 00:00:00

Elizabeth 7300 2007-03-24 00:00:00

Mattea 7200 2008-01-24 00:00:00

David 6800 2008-02-23 00:00:00

Shanta 6500 2005-10-10 00:00:00

Sundar 6400 2008-03-24 00:00:00

Bruce 6000 2007-05-21 00:00:00

Kevin 5800 2007-11-16 00:00:00

FIRST_NAME SALARY HIRE_DATE

Nandita 4200 2004-01-27 00:00:00

Alexis 4100 2005-02-20 00:00:00

Sarah 4000 2004-02-04 00:00:00

Britney 3900 2005-03-03 00:00:00

Kelly 3800 2005-06-14 00:00:00

Jennifer 3600 2005-08-13 00:00:00

Julia 3400 2006-06-24 00:00:00

Laura 3300 2005-08-20 00:00:00

Julia 3200 2005-07-16 00:00:00

Samuel 3200 2006-07-01 00:00:00

Stephen 3200 2005-10-26 00:00:00

FIRST_NAME SALARY HIRE_DATE

Winston 3200 2006-01-24 00:00:00

Alana 3100 2006-04-24 00:00:00

Jean 3100 2006-02-23 00:00:00

Anthony 3000 2007-02-07 00:00:00

Kevin 3000 2006-05-23 00:00:00

Michael 2900 2006-08-26 00:00:00

Shelli 2900 2005-12-24 00:00:00

Timothy 2900 2006-07-11 00:00:00

Girard 2800 2008-02-03 00:00:00

Mozhe 2800 2005-10-30 00:00:00

Vance 2800 2007-03-17 00:00:00

FIRST_NAME SALARY HIRE_DATE

Irene 2700 2006-09-28 00:00:00

John 2700 2006-02-12 00:00:00

Donald 2600 2007-06-21 00:00:00

Douglas 2600 2008-01-13 00:00:00

Randall 2600 2006-03-15 00:00:00

Martha 2500 2007-06-21 00:00:00

Randall 2500 2007-12-19 00:00:00

Ki 2400 2007-12-12 00:00:00

Hazel 2200 2008-02-06 00:00:00

Steven 2200 2008-03-08 00:00:00

65 rows selected.

SQL > select distinct a.FIRSTRANAMEMagna. SALARY. HIREDATE from employees a join employees b using (DEPARTMENT_ID) where a.SALARY > b.SALARY and a.HIRE_DATE > b.HIRE_DATE order by SALARY desc

FIRST_NAME SALARY HIRE_DATE

Steven 24000 2003-06-17 00:00:00

John 14000 2004-10-01 00:00:00

Karen 13500 2005-01-05 00:00:00

Nancy 12008 2002-08-17 00:00:00

Alberto 12000 2005-03-10 00:00:00

Lisa 11500 2005-03-11 00:00:00

Ellen 11000 2004-05-1100: 00:00

Gerald 11000 2007-10-15 00:00:00

Clara 10500 2005-11-11 00:00:00

Eleni 10500 2008-01-29 00:00:00

Harrison 10000 2006-03-23 00:00:00

FIRST_NAME SALARY HIRE_DATE

Peter 10000 2005-01-30 00:00:00

Tayler 9600 2006-01-24 00:00:00

Danielle 9500 2007-03-19 00:00:00

David 9500 2005-03-24 00:00:00

Alexander 9000 2006-01-03 00:00:00

Peter 9000 2005-08-20 00:00:00

Alyssa 8800 2005-03-19 00:00:00

Jonathon 8600 2006-03-24 00:00:00

Jack 8400 2006-04-23 00:00:00

Adam 8200 2005-04-10 00:00:00

Christopher 8000 2006-03-30 00:00:00

FIRST_NAME SALARY HIRE_DATE

Matthew 8000 2004-07-18 00:00:00

Jose Manuel 7800 2006-03-07 00:00:00

Nanette 7500 2006-12-09 00:00:00

William 7400 2007-02-23 00:00:00

Elizabeth 7300 2007-03-24 00:00:00

Mattea 7200 2008-01-24 00:00:00

David 6800 2008-02-23 00:00:00

Shanta 6500 2005-10-10 00:00:00

Sundar 6400 2008-03-24 00:00:00

Bruce 6000 2007-05-21 00:00:00

Kevin 5800 2007-11-16 00:00:00

FIRST_NAME SALARY HIRE_DATE

Nandita 4200 2004-01-27 00:00:00

Alexis 4100 2005-02-20 00:00:00

Sarah 4000 2004-02-04 00:00:00

Britney 3900 2005-03-03 00:00:00

Kelly 3800 2005-06-14 00:00:00

Jennifer 3600 2005-08-13 00:00:00

Julia 3400 2006-06-24 00:00:00

Laura 3300 2005-08-20 00:00:00

Julia 3200 2005-07-16 00:00:00

Samuel 3200 2006-07-01 00:00:00

Stephen 3200 2005-10-26 00:00:00

FIRST_NAME SALARY HIRE_DATE

Winston 3200 2006-01-24 00:00:00

Alana 3100 2006-04-24 00:00:00

Jean 3100 2006-02-23 00:00:00

Anthony 3000 2007-02-07 00:00:00

Kevin 3000 2006-05-23 00:00:00

Michael 2900 2006-08-26 00:00:00

Shelli 2900 2005-12-24 00:00:00

Timothy 2900 2006-07-11 00:00:00

Girard 2800 2008-02-03 00:00:00

Mozhe 2800 2005-10-30 00:00:00

Vance 2800 2007-03-17 00:00:00

FIRST_NAME SALARY HIRE_DATE

Irene 2700 2006-09-28 00:00:00

John 2700 2006-02-12 00:00:00

Donald 2600 2007-06-21 00:00:00

Douglas 2600 2008-01-13 00:00:00

Randall 2600 2006-03-15 00:00:00

Martha 2500 2007-06-21 00:00:00

Randall 2500 2007-12-19 00:00:00

Ki 2400 2007-12-12 00:00:00

Hazel 2200 2008-02-06 00:00:00

Steven 2200 2008-03-08 00:00:00

65 rows selected.

forty-six。 The average, maximum and minimum wages and numbers of each department shall be arranged in ascending order according to the department number.

47.SQL > select distinct DEPARTMENT_ID,max (SALARY), min (salary), count (*) from employees group by DEPARTMENT_ID order by DEPARTMENT_ID

DEPARTMENT_ID MAX (SALARY) MIN (SALARY) COUNT (*)

10 4400 4400 1 20 13000 6000 2 30 11000 2500 6 40 6500 6500 1 50 8200 2100 45 60 9000 4200 5 70 10000 10000 1 80 14000 6100 34 90 24000 17000 3 100 12008 6900 6 110 12008 8300 2

DEPARTMENT_ID MAX (SALARY) MIN (SALARY) COUNT (*)

7000 7000 1

12 rows selected.

forty-seven。 The number of employees with a salary greater than 5000 in each department

SQL > select distinct DEPARTMENT_ID,count (*) from employees where salary > 5000 group by DEPARTMENT_ID

DEPARTMENT_ID COUNT (*)

100 6 30 11 90 3 20 2 70 1 110 2 50 5 80 34 40 1 60 2

11 rows selected.

forty-eight。 The average salary and number of people in each department are arranged in ascending order by the name of the department.

SQL > select DEPARTMENT_name,avg (a.salary), count (*) from employees a left join DEPARTMENTS b using (DEPARTMENT_ID) group by DEPARTMENT_NAME order by b.DEPARTMENT_NAME

DEPARTMENT_NAME AVG (A.SALARY) COUNT (*)

Accounting 10154 2

Administration 4400 1

Executive 19333.3333 3

Finance 8601.33333 6

Human Resources 6500 1

IT 5760 5

Marketing 9500 2

Public Relations 10000 1

Purchasing 4150 6

Sales 8955.88235 34

Shipping 3475.55556 45

DEPARTMENT_NAME AVG (A.SALARY) COUNT (*)

7000 1

12 rows selected.

SQL > select avg (a.salary), count (*) from employees a left join DEPARTMENTS b on a.DEPARTMENT_ID=b.DEPARTMENT_id group by DEPARTMENT_NAME order by b.DEPARTMENT_NAME

AVG (A.SALARY) COUNT (*)

10154 2 4400 1

19333.3333 3

8601.33333 6

6500 1

5760 5

9500 2

10000 1

4150 6

8955.88235 34

3475.55556 45

AVG (A.SALARY) COUNT (*)

7000 1

forty-nine。 List the statistics of employees with the same salary in each department

List their department number, salary, number of people.

SQL > select DEPARTMENT_id,salary,count () from employees group by DEPARTMENT_id,salary having count () > 1

DEPARTMENT_ID SALARY COUNT (*)

90 17000 2 50 3200 4 50 2200 2 50 3600 2 80 10500 2 80 9000 2 50 2700 2 50 3100 3 80 10000 3 50 3000 2 60 4800 2

DEPARTMENT_ID SALARY COUNT (*)

50 3300 280 6200 250 2800 3 50 2500 5 50 2600 3 50 2400 280 9500 3 80 7500 280 11000 280 7000 2 50 2900 2

DEPARTMENT_ID SALARY COUNT (*)

80 8000 2

23 rows selected.

SQL > SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT (*) CNT

2 FROM EMPLOYEES EMP1,EMPLOYEES EMP2

3 WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND

4 EMP1.SALARY = EMP2.SALARY

5 AND EMP1.EMPLOYEE_ID EMP2.EMPLOYEE_ID

6 GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY

DEPARTMENT_ID SALARY CNT

90 17000 2 50 3200 12 50 2200 2 50 3600 2 80 10500 2 80 9000 2 50 2700 2 50 3100 6 80 10000 6 50 3000 2 60 4800 2

DEPARTMENT_ID SALARY CNT

50 3300 280 6200 250 2800 6 50 2500 20 50 2600 6 50 2400 280 9500 6 80 7500 280 11000 280 7000 2 50 2900 2

DEPARTMENT_ID SALARY CNT

80 8000 2

23 rows selected.

fifty。 List the departments with more than 2 employees with a salary higher than 1000 in the same department

Displays the name of the department and area.

SQL > select b.DEPARTMENTNAMEC.CITY having count () from EMPLOYEES a join (DEPARTMENTS b join LOCATIONS c using (LOCATION_ID)) using (DEPARTMENT_ID) where a.SALARY > 1000 GROUP BY b.DEPARTMENTNAME C.CITY having count () > 2

DEPARTMENT_NAME CITY COUNT (*)

IT Southlake 5

Sales Oxford 34

Shipping South San Francisco 45

Purchasing Seattle 6

Executive Seattle 3

Finance Seattle 6

6 rows selected.

fifty-one。 Which employees earn more than the average salary of the whole company?

List the employee's name and salary (in descending order)

SQL > select first_name | |''| | last_name,salary from EMPLOYEES where salary > (select avg (salary) from EMPLOYEES) order by salary desc

FIRST_NAME''LAST SALARY

Steven King 24000

Neena Kochhar 17000

Lex De Haan 17000

John Russell 14000

Karen Partners 13500

Michael Hartstein 13000

Nancy Greenberg 12008

Shelley Higgins 12008

Alberto Errazuriz 12000

Lisa Ozer 11500

Ellen Abel 11000

FIRST_NAME''LAST SALARY

Den Raphaely 11000

Gerald Cambrault 11000

Clara Vishney 10500

Eleni Zlotkey 10500

Peter Tucker 10000

Harrison Bloom 10000

Janette King 10000

Hermann Baer 10000

Tayler Fox 9600

David Bernstein 9500

Danielle Greene 9500

FIRST_NAME''LAST SALARY

Patrick Sully 9500

Daniel Faviet 9000

Alexander Hunold 9000

Peter Hall 9000

Allan McEwen 9000

Alyssa Hutton 8800

Jonathon Taylor 8600

Jack Livingston 8400

William Gietz 8300

Adam Fripp 8200

John Chen 8200

FIRST_NAME''LAST SALARY

Christopher Olsen 8000

Matthew Weiss 8000

Lindsey Smith 8000

Payam Kaufling 7900

Jose Manuel Urman 7800

Ismael Sciarra 7700

Louise Doran 7500

Nanette Cambrault 7500

William Smith 7400

Elizabeth Bates 7300

Mattea Marvins 7200

FIRST_NAME''LAST SALARY

Oliver Tuvault 7000

Kimberely Grant 7000

Sarath Sewall 7000

Luis Popp 6900

David Lee 6800

Susan Mavris 6500

Shanta Vollman 6500

51 rows selected.

fifty-two。 Which employee's salary is between the average salary of department 50 and 80?

SQL > select first_name | |'| | last_name,salary from EMPLOYEES where salary between (select avg (salary) from EMPLOYEES where DEPARTMENT_ID = 50) and (SELECT AVG (SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 80)

FIRST_NAME''LAST SALARY

Bruce Ernst 6000

David Austin 4800

Valli Pataballa 4800

Diana Lorentz 4200

John Chen 8200

Ismael Sciarra 7700

Jose Manuel Urman 7800

Luis Popp 6900

Matthew Weiss 8000

Adam Fripp 8200

Payam Kaufling 7900

FIRST_NAME''LAST SALARY

Shanta Vollman 6500

Kevin Mourgos 5800

Renske Ladwig 3600

Trenna Rajs 3500

Christopher Olsen 8000

Nanette Cambrault 7500

Oliver Tuvault 7000

Lindsey Smith 8000

Louise Doran 7500

Sarath Sewall 7000

Mattea Marvins 7200

FIRST_NAME''LAST SALARY

David Lee 6800

Sundar Ande 6400

Amit Banda 6200

William Smith 7400

Elizabeth Bates 7300

Sundita Kumar 6100

Alyssa Hutton 8800

Jonathon Taylor 8600

Jack Livingston 8400

Kimberely Grant 7000

Charles Johnson 6200

FIRST_NAME''LAST SALARY

Nandita Sarchand 4200

Alexis Bull 4100

Kelly Chung 3800

Jennifer Dilly 3600

Sarah Bell 4000

Britney Everett 3900

Jennifer Whalen 4400

Pat Fay 6000

Susan Mavris 6500

William Gietz 8300

43 rows selected.

fifty-three。 The name of the employee whose average salary is higher than 5000.

SQL > select first_name | |''| | last_name,salary from EMPLOYEES where DEPARTMENT_ID IN (select distinct DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID having avg (salary) > 5000)

FIRST_NAME''LAST SALARY

Steven King 24000

Neena Kochhar 17000

Lex De Haan 17000

Alexander Hunold 9000

Bruce Ernst 6000

David Austin 4800

Valli Pataballa 4800

Diana Lorentz 4200

Nancy Greenberg 12008

Daniel Faviet 9000

John Chen 8200

FIRST_NAME''LAST SALARY

Ismael Sciarra 7700

Jose Manuel Urman 7800

Luis Popp 6900

John Russell 14000

Karen Partners 13500

Alberto Errazuriz 12000

Gerald Cambrault 11000

Eleni Zlotkey 10500

Peter Tucker 10000

David Bernstein 9500

Peter Hall 9000

FIRST_NAME''LAST SALARY

Christopher Olsen 8000

Nanette Cambrault 7500

Oliver Tuvault 7000

Janette King 10000

Patrick Sully 9500

Allan McEwen 9000

Lindsey Smith 8000

Louise Doran 7500

Sarath Sewall 7000

Clara Vishney 10500

Danielle Greene 9500

FIRST_NAME''LAST SALARY

Mattea Marvins 7200

David Lee 6800

Sundar Ande 6400

Amit Banda 6200

Lisa Ozer 11500

Harrison Bloom 10000

Tayler Fox 9600

William Smith 7400

Elizabeth Bates 7300

Sundita Kumar 6100

Ellen Abel 11000

FIRST_NAME''LAST SALARY

Alyssa Hutton 8800

Jonathon Taylor 8600

Jack Livingston 8400

Charles Johnson 6200

Michael Hartstein 13000

Pat Fay 6000

Susan Mavris 6500

Hermann Baer 10000

Shelley Higgins 12008

William Gietz 8300

SQL > select first_name | |''| | last_name,salary from EMPLOYEES join (select distinct DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID having avg (salary) > 5000) using (DEPARTMENT_ID)

FIRST_NAME''LAST SALARY

Steven King 24000

Neena Kochhar 17000

Lex De Haan 17000

Alexander Hunold 9000

Bruce Ernst 6000

David Austin 4800

Valli Pataballa 4800

Diana Lorentz 4200

Nancy Greenberg 12008

Daniel Faviet 9000

John Chen 8200

FIRST_NAME''LAST SALARY

Ismael Sciarra 7700

Jose Manuel Urman 7800

Luis Popp 6900

John Russell 14000

Karen Partners 13500

Alberto Errazuriz 12000

Gerald Cambrault 11000

Eleni Zlotkey 10500

Peter Tucker 10000

David Bernstein 9500

Peter Hall 9000

FIRST_NAME''LAST SALARY

Christopher Olsen 8000

Nanette Cambrault 7500

Oliver Tuvault 7000

Janette King 10000

Patrick Sully 9500

Allan McEwen 9000

Lindsey Smith 8000

Louise Doran 7500

Sarath Sewall 7000

Clara Vishney 10500

Danielle Greene 9500

FIRST_NAME''LAST SALARY

Mattea Marvins 7200

David Lee 6800

Sundar Ande 6400

Amit Banda 6200

Lisa Ozer 11500

Harrison Bloom 10000

Tayler Fox 9600

William Smith 7400

Elizabeth Bates 7300

Sundita Kumar 6100

Ellen Abel 11000

FIRST_NAME''LAST SALARY

Alyssa Hutton 8800

Jonathon Taylor 8600

Jack Livingston 8400

Charles Johnson 6200

Michael Hartstein 13000

Pat Fay 6000

Susan Mavris 6500

Hermann Baer 10000

Shelley Higgins 12008

William Gietz 8300

54 rows selected.

fifty-four。 List the information of the highest-paid employees in each department: name, department number, salary.

55.SQL > select first_name | |''| | last_name,DEPARTMENT_ID,salary from EMPLOYEES where (DEPARTMENT_ID,salary) in (SELECT DEPARTMENT_ID,MAX (SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)

FIRST_NAME''LAST DEPARTMENT_ID SALARY

Nancy Greenberg 100 12008

Den Raphaely 30 11000

Steven King 90 24000

Michael Hartstein 20 13000

Hermann Baer 70 10000

Shelley Higgins 110 12008

Adam Fripp 50 8200

John Russell 80 14000

Susan Mavris 40 6500

Alexander Hunold 60 9000

Jennifer Whalen 10 4400

11 rows selected.

SQL > select first_name | |''| | last_name,DEPARTMENT_ID,salary from EMPLOYEES join (SELECT DEPARTMENT_ID,MAX (SALARY) as salary FROM EMPLOYEES GROUP BY DEPARTMENT_ID) using (DEPARTMENT_ID,salary)

FIRST_NAME''LAST DEPARTMENT_ID SALARY

Steven King 90 24000

Alexander Hunold 60 9000

Nancy Greenberg 100 12008

Den Raphaely 30 11000

Adam Fripp 50 8200

John Russell 80 14000

Jennifer Whalen 10 4400

Michael Hartstein 20 13000

Susan Mavris 40 6500

Hermann Baer 70 10000

Shelley Higgins 110 12008

11 rows selected.

fifty-five。 What is the average salary in the highest department?

SQL > select max (AVGSALARY) from (SELECT DEPARTMENT_ID,AVG (SALARY) AVGSALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID)

MAX (AVGSALARY) 19333.333319334.SQL > select max (avg (salary))

2 from employees

3 group by department_id

MAX (AVG (SALARY)) 19333.3333

fifty-six。 Which departments have more people than department 90?

SQL > select department_id, count () from employees group by department_id having count () > (select count (*) from employees where department_id=90)

DEPARTMENT_ID COUNT (*)

100 6 30 6 50 45 80 34 60 5

Who is the leader of 57.Den (FIRST_NAME) and Raphaely (LAST_NAME) (unrelated subquery)

SQL > select first_name | |''| | last_name from employees where employee_ID = (select manager_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Steven King

SQL > select first_name | |''| | last_name from employees where employee_ID in (select manager_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Steven King

Who is the leader of 58.Den (FIRST_NAME) and Raphaely (LAST_NAME) (unrelated subqueries).

SQL > select first_name | |''| | last_name from employees where MANAGER_ID in (select EMPLOYEE_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Alexander Khoo

Shelli Baida

Sigal Tobias

Guy Himuro

Karen Colmenares

SQL > select first_name | |''| | last_name from employees where MANAGER_ID = (select EMPLOYEE_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Alexander Khoo

Shelli Baida

Sigal Tobias

Guy Himuro

Karen Colmenares

Who is the leader of 59.Den (FIRST_NAME) and Raphaely (LAST_NAME) (related subqueries).

SQL > SELECT FIRST_NAME | |''| | LAST_NAME

2 FROM EMPLOYEES EMP1

3 WHERE EXISTS (

4 SELECT 1 FROM EMPLOYEES EMP2

5 WHERE FIRST_NAME = 'Den'

6 AND LAST_NAME = 'Raphaely'

7 AND EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID)

FIRST_NAME | |''| | LAST

Steven King

Who is the leader of 60.Den (FIRST_NAME) and Raphaely (LAST_NAME) (related subqueries)

SQL > SELECT FIRST_NAME | |''| | LAST_NAME

2 FROM EMPLOYEES EMP1

3 WHERE EXISTS (

4 SELECT 1 FROM EMPLOYEES EMP2

5 WHERE FIRST_NAME = 'Den'

6 AND LAST_NAME = 'Raphaely'

7 AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID)

FIRST_NAME | |''| | LAST

Alexander Khoo

Shelli Baida

Sigal Tobias

Guy Himuro

Karen Colmenares .

sixty-one。 List employees who work in the same department with a late entry date but with a higher salary than other colleagues:

Name, salary, entry date (related subquery).

SQL > SELECT FIRST_NAME | |''| | LAST_NAME,salary,HIRE_DATE

2 FROM EMPLOYEES EMP1

3 WHERE EXISTS (

4 SELECT 1 FROM EMPLOYEES EMP2

5 WHERE EMP1.HIRE_DATE > EMP2.HIRE_DATE and EMP1.salary > EMP2.salary

6 AND EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID)

FIRST_NAME''LAST SALARY HIRE_DATE

Steven King 24000 2003-06-17 00:00:00

Alexander Hunold 9000 2006-01-03 00:00:00

Bruce Ernst 6000 2007-05-21 00:00:00

Nancy Greenberg 12008 2002-08-17 00:00:00

Jose Manuel Urman 7800 2006-03-07 00:00:00

Shelli Baida 2900 2005-12-24 00:00:00

Adam Fripp 8200 2005-04-10 00:00:00

Matthew Weiss 8000 2004-07-18 00:00:00

Shanta Vollman 6500 2005-10-10 00:00:00

Kevin Mourgos 5800 2007-11-16 00:00:00

Laura Bissot 3300 2005-08-20 00:00:00

FIRST_NAME''LAST SALARY HIRE_DATE

Julia Dellinger 3400 2006-06-24 00:00:00

Jennifer Dilly 3600 2005-08-13 00:00:00

Girard Geoni 2800 2008-02-03 00:00:00

Anthony Cabrio 3000 2007-02-07 00:00:00

Vance Jones 2800 2007-03-17 00:00:00

Martha Sullivan 2500 2007-06-21 00:00:00

Randall Perkins 2500 2007-12-19 00:00:00

Donald OConnell 2600 2007-06-21 00:00:00

Douglas Grant 2600 2008-01-13 00:00:00

Michael Rogers 2900 2006-08-26 00:00:00

Winston Taylor 3200 2006-01-24 00:00:00

FIRST_NAME''LAST SALARY HIRE_DATE

Jean Fleaur 3100 2006-02-23 00:00:00

Timothy Gates 2900 2006-07-11 00:00:00

Samuel McCain 3200 2006-07-01 00:00:00

Alana Walsh 3100 2006-04-24 00:00:00

Kevin Feeney 3000 2006-05-23 00:00:00

Julia Nayer 3200 2005-07-16 00:00:00

Irene Mikkilineni 2700 2006-09-28 00:00:00

Mozhe Atkinson 2800 2005-10-30 00:00:00

Stephen Stiles 3200 2005-10-26 00:00:00

John Seo 2700 2006-02-12 00:00:00

Randall Matos 2600 2006-03-15 00:00:00

FIRST_NAME''LAST SALARY HIRE_DATE

Alexis Bull 4100 2005-02-20 00:00:00

Kelly Chung 3800 2005-06-14 00:00:00

Britney Everett 3900 2005-03-03 00:00:00

Steven Markle 2200 2008-03-08 00:00:00

Ki Gee 2400 2007-12-12 00:00:00

Hazel Philtanker 2200 2008-02-06 00:00:00

Nandita Sarchand 4200 2004-01-27 00:00:00

Sarah Bell 4000 2004-02-04 00:00:00

Alberto Errazuriz 12000 2005-03-10 00:00:00

Gerald Cambrault 11000 2007-10-15 00:00:00

Eleni Zlotkey 10500 2008-01-29 00:00:00

FIRST_NAME''LAST SALARY HIRE_DATE

Clara Vishney 10500 2005-11-11 00:00:00

Lisa Ozer 11500 2005-03-11 00:00:00

Harrison Bloom 10000 2006-03-23 00:00:00

Tayler Fox 9600 2006-01-24 00:00:00

Danielle Greene 9500 2007-03-19 00:00:00

Jack Livingston 8400 2006-04-23 00:00:00

Mattea Marvins 7200 2008-01-24 00:00:00

John Russell 14000 2004-10-01 00:00:00

Karen Partners 13500 2005-01-05 00:00:00

Ellen Abel 11000 2004-05-1100: 00:00

Peter Tucker 10000 2005-01-30 00:00:00

FIRST_NAME''LAST SALARY HIRE_DATE

David Bernstein 9500 2005-03-24 00:00:00

Peter Hall 9000 2005-08-20 00:00:00

Alyssa Hutton 8800 2005-03-19 00:00:00

Jonathon Taylor 8600 2006-03-24 00:00:00

Christopher Olsen 8000 2006-03-30 00:00:00

Nanette Cambrault 7500 2006-12-09 00:00:00

William Smith 7400 2007-02-23 00:00:00

Elizabeth Bates 7300 2007-03-24 00:00:00

David Lee 6800 2008-02-23 00:00:00

Sundar Ande 6400 2008-03-24 00:00:00

65 rows selected.

sixty-two。 Which employees are not in the same department as Den (FIRST_NAME) and Raphaely (LAST_NAME) (unrelated subqueries).

SQL > select first_name | |''| | last_name from employees where DEPARTMENT_ID! = (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Ellen Abel

Sundar Ande

Mozhe Atkinson

David Austin

Hermann Baer

Amit Banda

Elizabeth Bates

Sarah Bell

David Bernstein

Laura Bissot

Harrison Bloom

FIRST_NAME | |''| | LAST

Alexis Bull

Anthony Cabrio

Gerald Cambrault

Nanette Cambrault

John Chen

Kelly Chung

Curtis Davies

Lex De Haan

Julia Dellinger

Jennifer Dilly

Louise Doran

FIRST_NAME | |''| | LAST

Bruce Ernst

Alberto Errazuriz

Britney Everett

Daniel Faviet

Pat Fay

Kevin Feeney

Jean Fleaur

Tayler Fox

Adam Fripp

Timothy Gates

Ki Gee

FIRST_NAME | |''| | LAST

Girard Geoni

William Gietz

Douglas Grant

Nancy Greenberg

Danielle Greene

Peter Hall

Michael Hartstein

Shelley Higgins

Alexander Hunold

Alyssa Hutton

Charles Johnson

FIRST_NAME | |''| | LAST

Vance Jones

Payam Kaufling

Janette King

Steven King

Neena Kochhar

Sundita Kumar

Renske Ladwig

James Landry

David Lee

Jack Livingston

Diana Lorentz

FIRST_NAME | |''| | LAST

Jason Mallin

Steven Markle

James Marlow

Mattea Marvins

Randall Matos

Susan Mavris

Samuel McCain

Allan McEwen

Irene Mikkilineni

Kevin Mourgos

Julia Nayer

FIRST_NAME | |''| | LAST

Donald OConnell

Christopher Olsen

TJ Olson

Lisa Ozer

Karen Partners

Valli Pataballa

Joshua Patel

Randall Perkins

Hazel Philtanker

Luis Popp

Trenna Rajs

FIRST_NAME | |''| | LAST

Michael Rogers

John Russell

Nandita Sarchand

Ismael Sciarra

John Seo

Sarath Sewall

Lindsey Smith

William Smith

Stephen Stiles

Martha Sullivan

Patrick Sully

FIRST_NAME | |''| | LAST

Jonathon Taylor

Winston Taylor

Peter Tucker

Oliver Tuvault

Jose Manuel Urman

Peter Vargas

Clara Vishney

Shanta Vollman

Alana Walsh

Matthew Weiss

Jennifer Whalen

FIRST_NAME | |''| | LAST

Eleni Zlotkey

100 rows selected.

SQL > select first_name | |''| | last_name from employees where DEPARTMENT_ID not in (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Matthew Weiss

Adam Fripp

Payam Kaufling

Shanta Vollman

Kevin Mourgos

Julia Nayer

Irene Mikkilineni

James Landry

Steven Markle

Laura Bissot

Mozhe Atkinson

FIRST_NAME | |''| | LAST

James Marlow

TJ Olson

Jason Mallin

Michael Rogers

Ki Gee

Hazel Philtanker

Renske Ladwig

Stephen Stiles

John Seo

Joshua Patel

Trenna Rajs

FIRST_NAME | |''| | LAST

Curtis Davies

Randall Matos

Peter Vargas

Winston Taylor

Jean Fleaur

Martha Sullivan

Girard Geoni

Nandita Sarchand

Alexis Bull

Julia Dellinger

Anthony Cabrio

FIRST_NAME | |''| | LAST

Kelly Chung

Jennifer Dilly

Timothy Gates

Randall Perkins

Sarah Bell

Britney Everett

Samuel McCain

Vance Jones

Alana Walsh

Kevin Feeney

Donald OConnell

FIRST_NAME | |''| | LAST

Douglas Grant

Susan Mavris

Shelley Higgins

William Gietz

Steven King

Neena Kochhar

Lex De Haan

Hermann Baer

Jennifer Whalen

Michael Hartstein

Pat Fay

FIRST_NAME | |''| | LAST

Alexander Hunold

Bruce Ernst

David Austin

Valli Pataballa

Diana Lorentz

Nancy Greenberg

Daniel Faviet

John Chen

Ismael Sciarra

Jose Manuel Urman

Luis Popp

FIRST_NAME | |''| | LAST

John Russell

Karen Partners

Alberto Errazuriz

Gerald Cambrault

Eleni Zlotkey

Peter Tucker

David Bernstein

Peter Hall

Christopher Olsen

Nanette Cambrault

Oliver Tuvault

FIRST_NAME | |''| | LAST

Janette King

Patrick Sully

Allan McEwen

Lindsey Smith

Louise Doran

Sarath Sewall

Clara Vishney

Danielle Greene

Mattea Marvins

David Lee

Sundar Ande

FIRST_NAME | |''| | LAST

Amit Banda

Lisa Ozer

Harrison Bloom

Tayler Fox

William Smith

Elizabeth Bates

Sundita Kumar

Ellen Abel

Alyssa Hutton

Jonathon Taylor

Jack Livingston

FIRST_NAME | |''| | LAST

Charles Johnson

100 rows selected.

sixty-three。 Which employees are not in the same department as Den (FIRST_NAME) and Raphaely (LAST_NAME) (related subqueries).

SQL > select first_name | |''| | last_name from employees emp1 where not exists (select 1 from employees emp2 where FIRST_NAME='Den' and LAST_NAME='Raphaely'and emp1.DEPARTMENT_ID = emp2.DEPARTMENT_ID)

FIRST_NAME | |''| | LAST

Matthew Weiss

Adam Fripp

Payam Kaufling

Shanta Vollman

Kevin Mourgos

Julia Nayer

Irene Mikkilineni

James Landry

Steven Markle

Laura Bissot

Mozhe Atkinson

FIRST_NAME | |''| | LAST

James Marlow

TJ Olson

Jason Mallin

Michael Rogers

Ki Gee

Hazel Philtanker

Renske Ladwig

Stephen Stiles

John Seo

Joshua Patel

Trenna Rajs

FIRST_NAME | |''| | LAST

Curtis Davies

Randall Matos

Peter Vargas

Winston Taylor

Jean Fleaur

Martha Sullivan

Girard Geoni

Nandita Sarchand

Alexis Bull

Julia Dellinger

Anthony Cabrio

FIRST_NAME | |''| | LAST

Kelly Chung

Jennifer Dilly

Timothy Gates

Randall Perkins

Sarah Bell

Britney Everett

Samuel McCain

Vance Jones

Alana Walsh

Kevin Feeney

Donald OConnell

FIRST_NAME | |''| | LAST

Douglas Grant

Susan Mavris

Shelley Higgins

William Gietz

Steven King

Neena Kochhar

Lex De Haan

Hermann Baer

Jennifer Whalen

Kimberely Grant

Michael Hartstein

FIRST_NAME | |''| | LAST

Pat Fay

Alexander Hunold

Bruce Ernst

David Austin

Valli Pataballa

Diana Lorentz

Nancy Greenberg

Daniel Faviet

John Chen

Ismael Sciarra

Jose Manuel Urman

FIRST_NAME | |''| | LAST

Luis Popp

John Russell

Karen Partners

Alberto Errazuriz

Gerald Cambrault

Eleni Zlotkey

Peter Tucker

David Bernstein

Peter Hall

Christopher Olsen

Nanette Cambrault

FIRST_NAME | |''| | LAST

Oliver Tuvault

Janette King

Patrick Sully

Allan McEwen

Lindsey Smith

Louise Doran

Sarath Sewall

Clara Vishney

Danielle Greene

Mattea Marvins

David Lee

FIRST_NAME | |''| | LAST

Sundar Ande

Amit Banda

Lisa Ozer

Harrison Bloom

Tayler Fox

William Smith

Elizabeth Bates

Sundita Kumar

Ellen Abel

Alyssa Hutton

Jonathon Taylor

FIRST_NAME | |''| | LAST

Jack Livingston

Charles Johnson

101 rows selected.

Found a mistake in the above question.

SQL > select first_name | |''| | last_name from employees where nvl (DEPARTMENT_ID,1) not in (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely')

FIRST_NAME | |''| | LAST

Matthew Weiss

Adam Fripp

Payam Kaufling

Shanta Vollman

Kevin Mourgos

Julia Nayer

Irene Mikkilineni

James Landry

Steven Markle

Laura Bissot

Mozhe Atkinson

FIRST_NAME | |''| | LAST

James Marlow

TJ Olson

Jason Mallin

Michael Rogers

Ki Gee

Hazel Philtanker

Renske Ladwig

Stephen Stiles

John Seo

Joshua Patel

Trenna Rajs

FIRST_NAME | |''| | LAST

Curtis Davies

Randall Matos

Peter Vargas

Winston Taylor

Jean Fleaur

Martha Sullivan

Girard Geoni

Nandita Sarchand

Alexis Bull

Julia Dellinger

Anthony Cabrio

FIRST_NAME | |''| | LAST

Kelly Chung

Jennifer Dilly

Timothy Gates

Randall Perkins

Sarah Bell

Britney Everett

Samuel McCain

Vance Jones

Alana Walsh

Kevin Feeney

Donald OConnell

FIRST_NAME | |''| | LAST

Douglas Grant

Susan Mavris

Kimberely Grant

Shelley Higgins

William Gietz

Steven King

Neena Kochhar

Lex De Haan

Hermann Baer

Jennifer Whalen

Michael Hartstein

FIRST_NAME | |''| | LAST

Pat Fay

Alexander Hunold

Bruce Ernst

David Austin

Valli Pataballa

Diana Lorentz

Nancy Greenberg

Daniel Faviet

John Chen

Ismael Sciarra

Jose Manuel Urman

FIRST_NAME | |''| | LAST

Luis Popp

John Russell

Karen Partners

Alberto Errazuriz

Gerald Cambrault

Eleni Zlotkey

Peter Tucker

David Bernstein

Peter Hall

Christopher Olsen

Nanette Cambrault

FIRST_NAME | |''| | LAST

Oliver Tuvault

Janette King

Patrick Sully

Allan McEwen

Lindsey Smith

Louise Doran

Sarath Sewall

Clara Vishney

Danielle Greene

Mattea Marvins

David Lee

FIRST_NAME | |''| | LAST

Sundar Ande

Amit Banda

Lisa Ozer

Harrison Bloom

Tayler Fox

William Smith

Elizabeth Bates

Sundita Kumar

Ellen Abel

Alyssa Hutton

Jonathon Taylor

FIRST_NAME | |''| | LAST

Jack Livingston

Charles Johnson

101 rows selected.

Not in ignores null

What are the positions in the 64.Finance department (unrelated subqueries).

SQL > SELECT DISTINCT JOB_ID FROM EMPLOYEES where DEPARTMENT_ID = (select DEPARTMENT_ID from DEPARTMENTS where DEPARTMENT_NAME = 'Finance')

JOB_ID

FI_ACCOUNT

FI_MGR

SQL > SELECT DISTINCT JOB_ID FROM EMPLOYEES where DEPARTMENT_ID in (select DEPARTMENT_ID from DEPARTMENTS where DEPARTMENT_NAME = 'Finance')

JOB_ID

FI_ACCOUNT

FI_MGR

What are the positions in the 65.Finance department (related subqueries).

SQL > SELECT DISTINCT JOB_ID FROM EMPLOYEES a where exists (select DEPARTMENT_ID from DEPARTMENTS b where a.DEPARTMENT_ID=b.DEPARTMENT_ID and DEPARTMENT_NAME = 'Finance')

JOB_ID

FI_ACCOUNT

FI_MGR

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.