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

Collection operations: types and considerations

Considerations for collection operation

Column names and expressions in the SELECT list must match in quantity

The data type of each column in the second query must match the data type of the corresponding column of the first query

The order of execution can be changed with parentheses.

ORDER BY clause:

-can only appear at the end of the statement

-you can use the column name, alias, or relative location in the first query

Oracle servers and collection operators

In addition to UNION ALL, duplicate records are automatically deleted

The column name is the result returned by the first query

Except for UNION ALL, the system automatically sorts according to the ascending order of the first column in the first query

Use the UNION operator

1. Display the details of all employees in current and previous jobs. Each employee is shown only once.

Select employee_id, job_id

From employees

Union

Select employee_id, job_id

From job_history

Use the UNION ALL operator

1. Show all departments of current and previous employees.

Select employee_id, job_id, department_id

From employees

Union all

Select employee_id, job_id, department_id

From job_history

Order by employee_id

Use the INTERSECT operator

Displays the employee's ID and work ID, with the same current title (that is, they have changed jobs but are now back to the same jobs).

Select employee_id, job_id

From employees

Intersect

Select employee_id, job_id

From job_history

Use the MINUS operator

1. Display the ID of employees who have not changed their jobs once in the employee table.

Select employee_id

From employees

Minus

Select employee_id

From job_history

Matching SELECT statement

Display location_id,department_name,state_province using the UNION operator

When a field does not exist in one or another table, it must match the data type (using the TO_CHAR function or other conversion function)

Select location_id, department_name "Department"

To_char (null) "warehouse location"

From departments

Union

Select location_id, to_char (null) "Department"

State_province

From locations

Use the UNION operator to display the employee's ID, work ID, and salary for all employees

Select employee_id, job_id,salary

From employees

Union

Select employee_id, job_id,0

From job_history

Considerations for using ORDER BY clause in collection operation

The ORDER BY clause can only appear once at the end of a composite query

Each query in a collection operation cannot have a separate ORDER BY clause

Only the columns of the first SELECT query can be recognized in the ORDER BY clause.

By default, the first SELECT query in the first column sorts the output in ascending order.

Please find out the department_id of all departments that do not have ST_CLERK jobs, and require the use of set operators

Select department_id

From departments

Minus

Select department_id

From employees

Where job_id not like 'ST_CLERK'

2. Please write a SQL using the collection operator to find out the country_id,country_name of all countries where no department is located

Select country_id,country_name

From countries

Minus

Select l.country_id,c.country_name

From locations l join countries c

On (l.country_id=c.country_id)

Join departments d

On d.location_id=l.location_id

3. Please write a SQL using the set operator to find out all the job_id,department_id of the department number in 10mem50 and 20.

And it is displayed in the order of 10, 50, 50 and 20.

Select distinct job_id,department_id

From employees

Where department_id = 10

Union all

Select distinct job_id,department_id

From employees

Where department_id= 50

Union all

Select distinct job_id,department_id

From employees

Where department_id= 20

4. Please find out that all the jobs have changed, but after many changes, the current job is the employee_id and job_id of the employees who have done the work they have done before.

Select employee_id,job_id

From employees

Intersect

Select employee_id,job_id

From job_history

5. Colleagues in the HR department would like you to help them generate a report that meets the following two points:

A) get the last_name and department_id of all employees from the EMPLOYEES table (whether they belong to the same department or not)

B) get all department_id and department_name from the DEPARTMENTS table (regardless of whether the department has employees or not)

Select last_name,department_id,to_char (null) dept_name

From employees

Union

Select to_char (null), department_id,department_name

From departments

LAST_NAME DEPARTMENT_ID DEPT_NAME

Abel 80

Ande 80

Atkinson 50

Austin 60

Baer 70

Baida 30

Banda 80

Bates 80

Bell 50

Bernstein 80

Bissot 50

Bloom 80

Bull 50

Cabrio 50

Cambrault 80

Chen 100

Chung 50

Colmenares 30

Davies 50

De Haan 90

Dellinger 50

Dilly 50

Doran 80

Ernst 60

Errazuriz 80

Everett 50

Faviet 100

Fay 20

Feeney 50

Fleaur 50

Fox 80

Fripp 50

Gates 50

Gee 50

Geoni 50

Gietz 110

Grant 50

Grant

Greenberg 100

Greene 80

Hall 80

Hartstein 20

Higgins 110

Himuro 30

Hunold 60

Hutton 80

Johnson 80

Jones 50

Kaufling 50

Khoo 30

King 80

King 90

Kochhar 90

Kumar 80

Ladwig 50

Landry 50

Lee 80

Livingston 80

Lorentz 60

Mallin 50

Markle 50

Marlow 50

Marvins 80

Matos 50

Mavris 40

McCain 50

McEwen 80

Mikkilineni 50

Mourgos 50

Nayer 50

OConnell 50

Olsen 80

Olson 50

Ozer 80

Partners 80

Pataballa 60

Patel 50

Perkins 50

Philtanker 50

Popp 100

Rajs 50

Raphaely 30

Rogers 50

Russell 80

Sarchand 50

Sciarra 100

Seo 50

Sewall 80

Smith 80

Stiles 50

Sullivan 50

Sully 80

Taylor 50

Taylor 80

Tobias 30

Tucker 80

Tuvault 80

LAST_NAME DEPARTMENT_ID DEPT_NAME

Urman 100

Vargas 50

Vishney 80

Vollman 50

Walsh 50

Weiss 50

Whalen 10

Zlotkey 80

10 Administration

20 Marketing

30 Purchasing

40 Human Resources

50 Shipping

60 IT

70 Public Relations

80 Sales

90 Executive

100 Finance

110 Accounting

120 Treasury

130 Corporate Tax

140 Control And Credit

150 Shareholder Services

160 Benefits

170 Manufacturing

180 Construction

190 Contracting

200 Operations

210 IT Support

220 NOC

230 IT Helpdesk

240 Government Sales

250 Retail Sales

260 Recruiting

270 Payroll

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.