In some answers, the answer or the number of rows may be different for you because I have inserted a few rows for testing.

List names of employees with current salaries between 20000 and 40000.

Answer:

Select first_name, last_name 
From employees join salaries using (emp_no)
Where salary>20000 and 
      salary<40000 and
      to_date > now();
...
85 rows

or

Select first_name, last_name 
From employees join salaries using (emp_no)
Where salary between 20000 and 40000 and
      to_date > now();
...
85 rows

List names of employees between 40 and 50 years old. Hint: use date_add or date_sub functions.

Answer:

Select first_name, last_name 
From employees 
Where date_sub(curdate(), interval 40 year)>birth_date and 
      date_sub(curdate(), interval 50 year)<birth_date;
...
27187 rows

or

select first_name, last_name, birth_date
from employees
where birth_date between date_sub(now(), interval 50 year) 
and date_sub(now(), interval 40 year);
...
27187 rows

List names of employees working for the Marketing Department from 4/27/1992 to date 7/22/1995.

Answer:

Select first_name, last_name 
From employees join dept_emp using (emp_no) join departments using (dept_no)
Where from_date="1992-04-27" and to_date="1995-07-22" and dept_name="Marketing";
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgy     | Dredge    |
+------------+-----------+
1 row in set (0.03 sec)

List names of employees working for the Marketing Department at any point between date 4/27/1992 and date 7/22/1995.

Select first_name, last_name 
from employees join dept_emp using (emp_no) join departments using (dept_no) 
where from_date <= "1995-07-22" and 
      to_date >= "1992-04-27" and 
      dept_name="Marketing";
...
12038 rows

List names of employees that left the company

Select distinct emp_no, first_name, last_name 
from employees join dept_emp de1 using (emp_no)
Where curdate() > all(select to_date from dept_emp de2 where de1.emp_no=de2.emp_no);
...
59900 rows

The above checks that the current date is bigger than all of the to_date's for each employee returned.

or

Select distinct emp_no, first_name, last_name 
from employees 
where emp_no in (Select emp_no 
                 from dept_emp de1
                 Where not exists (select * 
                                   from dept_emp de2 
                                   where de1.emp_no=de2.emp_no and 
                                   to_date>curdate()));
...
59900 rows

The innermost subquery gets the current department for an employee; the one just outside of that is getting the employee numbers of non-current employees. The outermost query turns the emp_no into a name.

or

select distinct de1.emp_no, first_name, last_name
from dept_emp de1 join employees using (emp_no)
where de1.emp_no not in (select de2.emp_no 
                         from dept_emp de2 
                         where to_date>now());
...
59900 rows

The above amounts to subtracting the current employees from all employees

List titles that no current employee has.

Select title 
from titles t 
where t.title not in (select title 
                      from titles join employees using (emp_no) 
                      where to_date>now());
Empty set

Insert a row for a new hire with no other information -- no title, no salary, no department. List names of all employees with their salary history. For new hires who haven't yet been assigned a salary (like the one you entered), print their info with nulls in salary fields

insert into employees values (2000000,"1960-01-01","name1","name2",'M',"2013-01-01");

Select first_name,last_name, from_date, to_date, salary
From employees left join salaries using (emp_no) order by emp_no;
...
2844052 rows

Note that the new employee appears at the end because I gave him a very large emp_no.

List names of employees that were hired into the Marketing department.

Select first_name, last_name 
from employees join dept_emp using (emp_no) join departments using (dept_no)
Where from_date=hire_date and dept_name="Marketing";
...
8171 rows

Note, this can in principle be different from asking which employees worked first in the Marketing Department, which is NOT the following query:

select emp_no, first_name, last_name
from employees e join dept_emp de1 using (emp_no) join departments using (dept_no)
where dept_name="Marketing" and
      hire_date=(select min(from_date) 
            from dept_emp de2
            where de1.dept_no=de2.dept_no and e.emp_no=de2.emp_no);
...
8171 rows

Can you figure out the right one?

List employees with whatever department they were hired into (or null if none assigned).

Answer:

select e.first_name, e.last_name, dept_name 
from (employees e left join dept_emp de on e.emp_no=de.emp_no and from_date=hire_date) 
     left join departments using (dept_no);

...
300028 rows

Some incorrect answers -- why??

Select first_name, last_name, dept_name  
from employees join dept_emp using (emp_no) 
     join departments using (dept_no) 
Where from_date=hire_date;

The above drops rows from employees that were not assigned to a department immediately.

Select first_name, last_name, dept_name  
from employees left join dept_emp using (emp_no) 
     join departments using (dept_no) 
Where from_date=hire_date;

The rows with null dept_no are still dropped.

Select first_name, last_name, dept_name  
from employees left join dept_emp using (emp_no) 
     left join departments using (dept_no) 
Where from_date=hire_date;

Why doesn't the above work? Does left join still drop rows with NULL dept_no?

List employees that currently have two managers.

Answer:

select de1.emp_no
from dept_emp de1 join dept_manager dm1 using (dept_no), dept_emp de2 join dept_manager dm2 using (dept_no)
where de1.to_date>now() and
      de2.to_date>now() and
      dm1.to_date>now() and
      dm2.to_date>now() and
      de1.emp_no=de2.emp_no and
      dm1.emp_no!=dm2.emp_no;
Empty set

A simpler way would be to assume that a department has only one manager at a time. So the employee must be in two different departments to have two different managers. But an employee could be in two different departments and still have only one manager, if the manager manages both departments.

Select de1.emp_no 
from dept_emp de1, dept_emp de2 
where de1.to_date>now() and 
      de2.to_date>now() and 
      de1.dept_no!=de2.dept_no and
      de1.empno=de2.empno;
Empty set (0.81 sec)

List employees that have been or are in two departments simultaneously

List titles that none of the original employees had

Sent from my iPhone