Wednesday, October 14, 2015
Tuesday, October 13, 2015
Practice for oracle Set Operator
Practices for Set Operator
In this practice, you write queries using the
set operators.
1) The HR department needs a list of department
IDs for departments that do not contain
the job ID . Use the set operators to create ST_CLERK this report.
SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees
WHERE job_id = 'ST_CLERK';
2) The HR department needs a list of countries
that have no departments located in
them. Display the country ID and the name of
the countries. Use the set operators to
create this report.
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) Produce a list of jobs for departments 10,
50, and 20, in that order. Display job ID and
department ID using the set operators.
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) Create a report that lists the employee IDs
and job IDs of those employees who
currently have a job title that is the same as
their job title when they were initially
hired by the company (that is, they changed
jobs, but have now gone back to doing
their original job).
SELECT employee_id,job_id
FROM employees
INTERSECT
SELECT employee_id,job_id
FROM job_history;
5) The HR department needs a report with the
following specifications:
• Last name and department ID of all the
employees from the EMPLOYEES
table,
regardless of whether or not they belong to a
department
• Department ID and department name of all the
departments from the
DEPARTMENTS table, regardless of whether or not they have employees working
in them
Write a compound query to accomplish this.
SELECT last_name,department_id,TO_CHAR(null)
FROM employees
UNION
SELECT
TO_CHAR(null),department_id,department_name
FROM departments;
Your result should be like this for all the 5 Questions.
RESULT FOR QUERY 1
DEPARTMENT_ID
-------------
10
20
30
40
60
70
80
90
100
110
120
130
140
150
160
170
180
190
200
210
220
230
240
250
260
270
26 rows selected
RESULT FOR QUERY 2.
COUNTRY_ID COUNTRY_NAME
---------- ----------------------------------------
AR Argentina
AU Australia
BE Belgium
BR Brazil
CH Switzerland
CN China
DK Denmark
EG Egypt
FR France
IL Israel
IN India
IT Italy
JP Japan
KW Kuwait
ML Malaysia
MX Mexico
NG Nigeria
NL Netherlands
SG Singapore
ZM Zambia
ZW Zimbabwe
21 rows selected
RESULT FOR QUERY 3
JOB_ID DEPARTMENT_ID
---------- -------------
AD_ASST 10
ST_MAN 50
SH_CLERK 50
ST_CLERK 50
MK_MAN 20
MK_REP 20
6 rows selected
RESULT FOR QUERY 4
EMPLOYEE_ID JOB_ID
----------- ----------
176 SA_REP
200 AD_ASST
RESULT FOR QUERY 5
LAST_NAME DEPARTMENT_ID TO_CHAR(NULL)
------------------------- ------------- ------------------------------
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
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
132 rows selected
@copied from internet
Subscribe to:
Posts (Atom)