Mixing
How to find the employee that worked the most trips?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
-1
I need to update by 5% the salary of the employee who has made the most trips from the start of the current year to this day. There are 2 types of employees: Driver and Hostess. Here are my tables:
CREATE TABLE Employe(
nb_emp VARCHAR2(5),
salary NUMBER
);
nb_emp is primary key.
CREATE TABLE Trip(
id_trip NUMBER,
driver VARCHAR2(5),
hostess VARCHAR(5),
date_trip DATE
);
id_trip is primary key, driver and hostess reference nb_emp from table Employee.
And here is my query for the driver:
update employe
set salary = salary*0.05 + salary
where nb_emp in (
select driver
from trip t
where
nb_emp = t.driver
and t.date_trip >= TRUNC(SYSDATE,'YEAR')
AND t.date_trip < SYSDATE
and t.id_trip in (
Select count(tt.id_trip) from trip tt having count(tt.id_trip) = (
Select Max(s) FROM (select count(ttt.id_trip) as s FROM trip ttt
)
)
)
);
sql oracle
edited Jan 3 at 12:59
GMB
22k61128
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
|
show 1 more comment
-1
I need to update by 5% the salary of the employee who has made the most trips from the start of the current year to this day. There are 2 types of employees: Driver and Hostess. Here are my tables:
CREATE TABLE Employe(
nb_emp VARCHAR2(5),
salary NUMBER
);
nb_emp is primary key.
CREATE TABLE Trip(
id_trip NUMBER,
driver VARCHAR2(5),
hostess VARCHAR(5),
date_trip DATE
);
id_trip is primary key, driver and hostess reference nb_emp from table Employee.
And here is my query for the driver:
update employe
set salary = salary*0.05 + salary
where nb_emp in (
select driver
from trip t
where
nb_emp = t.driver
and t.date_trip >= TRUNC(SYSDATE,'YEAR')
AND t.date_trip < SYSDATE
and t.id_trip in (
Select count(tt.id_trip) from trip tt having count(tt.id_trip) = (
Select Max(s) FROM (select count(ttt.id_trip) as s FROM trip ttt
)
)
)
);
sql oracle
edited Jan 3 at 12:59
GMB
22k61128
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
1
Please provide sample data and desired results.
– Gordon Linoff
Jan 3 at 12:22
The query columns don't match the described tables.
– jarlh
Jan 3 at 12:24
e.salary+e.salary*0.5=e.salary * 1.5
– Joakim Danielson
Jan 3 at 12:28
Sorry guys I fell victim for the copy and paste. Edited it all now.
– M. Chakhtoura
Jan 3 at 12:29
No you haven't. What does "since this year" mean, do you mean current year? E.g from January 1st this year to current date?
– Joakim Danielson
Jan 3 at 12:30
|
show 1 more comment
-1
-1
-1
I need to update by 5% the salary of the employee who has made the most trips from the start of the current year to this day. There are 2 types of employees: Driver and Hostess. Here are my tables:
CREATE TABLE Employe(
nb_emp VARCHAR2(5),
salary NUMBER
);
nb_emp is primary key.
CREATE TABLE Trip(
id_trip NUMBER,
driver VARCHAR2(5),
hostess VARCHAR(5),
date_trip DATE
);
id_trip is primary key, driver and hostess reference nb_emp from table Employee.
And here is my query for the driver:
update employe
set salary = salary*0.05 + salary
where nb_emp in (
select driver
from trip t
where
nb_emp = t.driver
and t.date_trip >= TRUNC(SYSDATE,'YEAR')
AND t.date_trip < SYSDATE
and t.id_trip in (
Select count(tt.id_trip) from trip tt having count(tt.id_trip) = (
Select Max(s) FROM (select count(ttt.id_trip) as s FROM trip ttt
)
)
)
);
sql oracle
edited Jan 3 at 12:59
GMB
22k61128
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
I need to update by 5% the salary of the employee who has made the most trips from the start of the current year to this day. There are 2 types of employees: Driver and Hostess. Here are my tables:
CREATE TABLE Employe(
nb_emp VARCHAR2(5),
salary NUMBER
);
nb_emp is primary key.
CREATE TABLE Trip(
id_trip NUMBER,
driver VARCHAR2(5),
hostess VARCHAR(5),
date_trip DATE
);
id_trip is primary key, driver and hostess reference nb_emp from table Employee.
And here is my query for the driver:
update employe
set salary = salary*0.05 + salary
where nb_emp in (
select driver
from trip t
where
nb_emp = t.driver
and t.date_trip >= TRUNC(SYSDATE,'YEAR')
AND t.date_trip < SYSDATE
and t.id_trip in (
Select count(tt.id_trip) from trip tt having count(tt.id_trip) = (
Select Max(s) FROM (select count(ttt.id_trip) as s FROM trip ttt
)
)
)
);
sql oracle
sql oracle
edited Jan 3 at 12:59
GMB
22k61128
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
edited Jan 3 at 12:59
GMB
22k61128
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
edited Jan 3 at 12:59
GMB
22k61128
edited Jan 3 at 12:59
GMB
22k61128
edited Jan 3 at 12:59
GMB
22k61128
22k61128
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
asked Jan 3 at 12:22
M. ChakhtouraM. Chakhtoura
156
156
1
Please provide sample data and desired results.
– Gordon Linoff
Jan 3 at 12:22
The query columns don't match the described tables.
– jarlh
Jan 3 at 12:24
e.salary+e.salary*0.5=e.salary * 1.5
– Joakim Danielson
Jan 3 at 12:28
Sorry guys I fell victim for the copy and paste. Edited it all now.
– M. Chakhtoura
Jan 3 at 12:29
No you haven't. What does "since this year" mean, do you mean current year? E.g from January 1st this year to current date?
– Joakim Danielson
Jan 3 at 12:30
|
show 1 more comment
1
Please provide sample data and desired results.
– Gordon Linoff
Jan 3 at 12:22
The query columns don't match the described tables.
– jarlh
Jan 3 at 12:24
e.salary+e.salary*0.5=e.salary * 1.5
– Joakim Danielson
Jan 3 at 12:28
Sorry guys I fell victim for the copy and paste. Edited it all now.
– M. Chakhtoura
Jan 3 at 12:29
No you haven't. What does "since this year" mean, do you mean current year? E.g from January 1st this year to current date?
– Joakim Danielson
Jan 3 at 12:30
1
1
Please provide sample data and desired results.
– Gordon Linoff
Jan 3 at 12:22
Please provide sample data and desired results.
– Gordon Linoff
Jan 3 at 12:22
The query columns don't match the described tables.
– jarlh
Jan 3 at 12:24
The query columns don't match the described tables.
– jarlh
Jan 3 at 12:24
e.salary+e.salary*0.5 = e.salary * 1.5– Joakim Danielson
Jan 3 at 12:28
e.salary+e.salary*0.5 = e.salary * 1.5– Joakim Danielson
Jan 3 at 12:28
Sorry guys I fell victim for the copy and paste. Edited it all now.
– M. Chakhtoura
Jan 3 at 12:29
Sorry guys I fell victim for the copy and paste. Edited it all now.
– M. Chakhtoura
Jan 3 at 12:29
No you haven't. What does "since this year" mean, do you mean current year? E.g from January 1st this year to current date?
– Joakim Danielson
Jan 3 at 12:30
No you haven't. What does "since this year" mean, do you mean current year? E.g from January 1st this year to current date?
– Joakim Danielson
Jan 3 at 12:30
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
One of the ways to finds workers which made most trips:
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt;
Use this query in update statement like here:
update employe set salary = salary * 1.05
where nb_emp in (
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt);
My test data:
create table employe(nb_emp, salary) as (
select 'D1', 1000 from dual union all
select 'D2', 1000 from dual union all
select 'H1', 1000 from dual union all
select 'H2', 1000 from dual union all
select 'H3', 1000 from dual );
create table trip (id_trip, driver, hostess, date_trip) as (
select 1, 'D1', 'H1', date '2019-01-01' from dual union all
select 2, 'D1', 'H2', date '2019-01-01' from dual union all
select 3, 'D1', 'H2', date '2019-01-02' from dual union all
select 4, 'D2', 'H1', date '2019-01-02' from dual union all
select 5, 'D2', 'H2', date '2019-01-03' from dual );
Driver D1 and hostess H2 made most trips (3) and their salaries were raised to 1050.
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022222%2fhow-to-find-the-employee-that-worked-the-most-trips%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
One of the ways to finds workers which made most trips:
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt;
Use this query in update statement like here:
update employe set salary = salary * 1.05
where nb_emp in (
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt);
My test data:
create table employe(nb_emp, salary) as (
select 'D1', 1000 from dual union all
select 'D2', 1000 from dual union all
select 'H1', 1000 from dual union all
select 'H2', 1000 from dual union all
select 'H3', 1000 from dual );
create table trip (id_trip, driver, hostess, date_trip) as (
select 1, 'D1', 'H1', date '2019-01-01' from dual union all
select 2, 'D1', 'H2', date '2019-01-01' from dual union all
select 3, 'D1', 'H2', date '2019-01-02' from dual union all
select 4, 'D2', 'H1', date '2019-01-02' from dual union all
select 5, 'D2', 'H2', date '2019-01-03' from dual );
Driver D1 and hostess H2 made most trips (3) and their salaries were raised to 1050.
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
add a comment |
0
One of the ways to finds workers which made most trips:
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt;
Use this query in update statement like here:
update employe set salary = salary * 1.05
where nb_emp in (
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt);
My test data:
create table employe(nb_emp, salary) as (
select 'D1', 1000 from dual union all
select 'D2', 1000 from dual union all
select 'H1', 1000 from dual union all
select 'H2', 1000 from dual union all
select 'H3', 1000 from dual );
create table trip (id_trip, driver, hostess, date_trip) as (
select 1, 'D1', 'H1', date '2019-01-01' from dual union all
select 2, 'D1', 'H2', date '2019-01-01' from dual union all
select 3, 'D1', 'H2', date '2019-01-02' from dual union all
select 4, 'D2', 'H1', date '2019-01-02' from dual union all
select 5, 'D2', 'H2', date '2019-01-03' from dual );
Driver D1 and hostess H2 made most trips (3) and their salaries were raised to 1050.
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
add a comment |
0
0
0
One of the ways to finds workers which made most trips:
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt;
Use this query in update statement like here:
update employe set salary = salary * 1.05
where nb_emp in (
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt);
My test data:
create table employe(nb_emp, salary) as (
select 'D1', 1000 from dual union all
select 'D2', 1000 from dual union all
select 'H1', 1000 from dual union all
select 'H2', 1000 from dual union all
select 'H3', 1000 from dual );
create table trip (id_trip, driver, hostess, date_trip) as (
select 1, 'D1', 'H1', date '2019-01-01' from dual union all
select 2, 'D1', 'H2', date '2019-01-01' from dual union all
select 3, 'D1', 'H2', date '2019-01-02' from dual union all
select 4, 'D2', 'H1', date '2019-01-02' from dual union all
select 5, 'D2', 'H2', date '2019-01-03' from dual );
Driver D1 and hostess H2 made most trips (3) and their salaries were raised to 1050.
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
One of the ways to finds workers which made most trips:
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt;
Use this query in update statement like here:
update employe set salary = salary * 1.05
where nb_emp in (
select emp
from (
select emp, count(1) cnt, max(count(1)) over () mcnt
from (
select driver as emp from trip where date_trip between trunc(sysdate, 'year') and sysdate
union all
select hostess from trip where date_trip between trunc(sysdate, 'year') and sysdate)
group by (emp))
where cnt = mcnt);
My test data:
create table employe(nb_emp, salary) as (
select 'D1', 1000 from dual union all
select 'D2', 1000 from dual union all
select 'H1', 1000 from dual union all
select 'H2', 1000 from dual union all
select 'H3', 1000 from dual );
create table trip (id_trip, driver, hostess, date_trip) as (
select 1, 'D1', 'H1', date '2019-01-01' from dual union all
select 2, 'D1', 'H2', date '2019-01-01' from dual union all
select 3, 'D1', 'H2', date '2019-01-02' from dual union all
select 4, 'D2', 'H1', date '2019-01-02' from dual union all
select 5, 'D2', 'H2', date '2019-01-03' from dual );
Driver D1 and hostess H2 made most trips (3) and their salaries were raised to 1050.
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
answered Jan 3 at 13:20
Ponder StibbonsPonder Stibbons
9,14421519
9,14421519
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022222%2fhow-to-find-the-employee-that-worked-the-most-trips%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Please provide sample data and desired results.
– Gordon Linoff
Jan 3 at 12:22
The query columns don't match the described tables.
– jarlh
Jan 3 at 12:24
e.salary+e.salary*0.5=e.salary * 1.5– Joakim Danielson
Jan 3 at 12:28
Sorry guys I fell victim for the copy and paste. Edited it all now.
– M. Chakhtoura
Jan 3 at 12:29
No you haven't. What does "since this year" mean, do you mean current year? E.g from January 1st this year to current date?
– Joakim Danielson
Jan 3 at 12:30