#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Employees with salary histories; removing duplicates


    Hi guys! My friend Kate is in trouble right now. She has to cope with some problems and i want to help her. She has to do some work with SQL...please, for your brains it will be easy and quick. Save us)


    1) We have the entity "Employee", which is presented by the following attributes: Employee Code, Birth Date, Gender, Full Name, and Salary. Full Name can be defined in multiple languages, and the Salary is historically changed. What structure of DB tables could you suggest to keep this data? What will be the SQL statement to get the Salary for Employee with Employee Code '001' and for April 1 of current year?

    2) We have the DB table for Employee with the following attributes: ID (unique key), SocialID (Employee's Social ID, which should be unique from business point of view, but there is no constraint in DB). It happened so that some duplicate records (with the same SocialID) appeared in DB. What will be the SQL script to remove the duplicates, i.e. to have only one record for each SocialID?
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    Please don't use thread titles like "Save the girls in Russia". It makes your thread look like spam. That's actually why I came here: to delete this.

    1. It varies but the simplest way would be to have another table containing the employee ID as a foreign key, the salary amount, the date the salary became effective, and the end date (if any) that it stopped. Then your query looks like "select the salary for this employee where the start date is on/before April 1st and the end date is either NULL or after April 1st".

    2. Are you sure you can simply remove the duplicates? Any potential side effects to worry about?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    Please don't use thread titles like "Save the girls in Russia". It makes your thread look like spam. That's actually why I came here: to delete this.

    1. It varies but the simplest way would be to have another table containing the employee ID as a foreign key, the salary amount, the date the salary became effective, and the end date (if any) that it stopped. Then your query looks like "select the salary for this employee where the start date is on/before April 1st and the end date is either NULL or after April 1st".

    2. Are you sure you can simply remove the duplicates? Any potential side effects to worry about?
    Ok.thanks for advice))i'm sorry for this...i don't have any expirience in that, actually)

    i don't know how to do it anyway... yes, i have mysql manager but how to write all these "create table blablabla select ect"...i really sorry for being so stupid))
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Unhappy


    i'm looking really tired because of all this(
  8. #5
  9. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    Do you need to do it? Does this all make more sense to this Kate person you're helping?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Unhappy


    Originally Posted by requinix
    Do you need to do it? Does this all make more sense to this Kate person you're helping?
    I love her a lot!We are friends since we went together to the kindergarden, now we are 20 both. She is seek, a flue or just a sort of that. She is really bad, but her employer need this to be done...
  12. #7
  13. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    If she needs to do this kind of work but has no idea how to do it, she's going to have lots of problems. This won't be the last time she needs help and eventually the person/people she's working for will realize that.

    1. Surely there's some sort of tool to deal with database tables. Use that to create the new table. But learn about MySQL and SQL and database fundamentals first.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    If she needs to do this kind of work but has no idea how to do it, she's going to have lots of problems. This won't be the last time she needs help and eventually the person/people she's working for will realize that.

    1. Surely there's some sort of tool to deal with database tables. Use that to create the new table. But learn about MySQL and SQL and database fundamentals first.
    Together we did a first task
    check it please and help with the second...

    create database multilangcorp default character set utf8;
    use multilangcorp;

    create table employee(
    id int unsigned primary key auto_increment
    , code varchar(10) not null unique -- если длина кода фиксированная, то можно и char(N)
    , birth_date date
    , gender enum('male','female') not null
    , full_name_in_master_native_language varchar(100) -- длина - по вкусу
    );

    create table full_name(
    id int unsigned primary key auto_increment
    , employee_id int unsigned not null references employee(id)
    , full_name varchar(100) not null -- длина - по вкусу
    , language_id int unsigned not null references language(id)
    );

    create table language(
    id int unsigned primary key auto_increment
    , language varchar(30) not null unique
    );

    create table salary_history(
    id int unsigned primary key auto_increment
    , employee_id int unsigned not null references employee(id)
    , date_from date not null
    , date_to date
    , salary int unsigned not null
    , unique unq_salary_history_1 (employee_id, date_from, date_to)
    );


    -- What will be the SQL statement to get the Salary for Employee with Employee Code '001' and for April 1 of current year?
    -- Mean 'current year' is 2013.
    select
    s.salary
    from
    employee e
    join salary_history s
    on e.id = s.employee_id
    where
    e.code = '001'
    and s.date_from <= '2013-01-04'
    and (s.date_to > '2013-01-04' or s.date_to is null)
  16. #9
  17. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    Actually that looks fine to me.
    me too...but i'm comletely confused with the second task

IMN logo majestic logo threadwatch logo seochat tools logo