questions

Table Name: Employee

Employee_id First_name    Last_name   Salary    Joining_date            Department    Order
----------------------------------------------------------------------------------------------
1           Roy           Thomas      700000    01-FEB-13 12.00.00 AM   Banking       3
2           Michael       Clarke      800000    01-JAN-13 12.00.00 AM   Insurance     2
3           John          Abraham     1000000   01-JAN-13 12.00.00 AM   Banking       5
4           Tom           Jose        600000    01-FEB-13 12.00.00 AM   Insurance     4
5           TestName1     123         650000    01-JAN-13 12.00.00 AM   Services      1
6           Philip        Mathew      750000    01-JAN-13 12.00.00 AM   Services      8
7           Jerry         Pinto       650000    01-FEB-13 12.00.00 AM   Insurance     10
8           TestName2     Lname%      600000    01-FEB-13 12.00.00 AM   Insurance     6

Table name: Incentives

Employee_ref_id     Incentive_date  Incentive_amount
--------------------------------------------------------
1                   01-FEB-13       5000
2                   01-FEB-13       3000
3                   01-FEB-13       4000
1                   01-JAN-13       4500
2                   01-JAN-13       3500




Questions:

1) Generate a list of employees with a salary over $750,000
    Answer: SELECT First_name, Last_name
            FROM Employee
            WHERE salary > 750000;

2) Find any users who may have invalid names (only alphabetic chars and hyphens allowed)
    Answer: SELECT First_name, Last_name
            FROM Employee
            WHERE upper(First_name) NOT LIKE REGEXP [A-Z]*-[A-Z]
                OR upper(Last_name) NOT LIKE REGEXP [A-Z]*-[A-Z];
    
3) Generate a list of employees in the Banking department, sort them by join date
    Answer: SELECT First_name, Last_name, Joining_date
            FROM Employee
            WHERE Department = 'Banking'
            ORDER BY Joining_date;

4) Get the employees who all have incentive dates in Feburary
    Answer: SELECT First_name, Last_name, Incentive_date
            FROM Employee
            WHERE Incentive_date in (SELECT Incentive_date
                                     FROM Incentives
                                     WHERE Employee.Employee_id = Incentives.Employee_ref_id
                                         AND MONTH(Incentives.Incentive_date) = 2);

5) Generate a list of usernames, making it the first initial and last name, in lower case format (example result: Roy Thomas = rthomas)
    Answer: SELECT Employee_id, First_name, Last_name, LOWER(CONCAT(LEFT(First_name, 1), Last_name)) AS usernames
            FROM Employee;

6) Get a list of the employees, sort them (ascending) by the "order" column, only returning 4 rows
    Answer: SELECT First_name, Last_name
            FROM Employee
            ORDER BY Order LIMIT 4;

7) Generate a list of the 3 newest employees in the Banking department or Services department
    Answer: SELECT First_name, Last_name, Joining_date, Department
            FROM Employee
            WHERE Department = 'Banking' OR Department = 'Services
            ORDER BY Joining_date DESC LIMIT 3;