SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE department ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name TEXT ); CREATE TABLE location ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name TEXT ); CREATE TABLE employee ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name TEXT, department_id INTEGER, location_id INTEGER, FOREIGN KEY(department_id) REFERENCES department(id), FOREIGN KEY(location_id) REFERENCES location(id) ); CREATE TABLE salary ( employee_id INTEGER, amount FLOAT, effective_date DATE, expiration_date DATE, FOREIGN KEY(employee_id) REFERENCES employee(id) ); INSERT INTO department (name) VALUES ('Marketing'), ('G & A'), ('Engineering'), ('Sales'), ('Support'), ('Services'); INSERT INTO location (name) VALUES ('Americas'), ('EMEA'), ('Asia'); INSERT INTO employee (name, department_id, location_id) VALUES ('John Doe', 1, 2), ('Jane Austin', 2, 1), ('Bob Dylan', 3, 1), ('Steve Mannings', 3, 1), ('Randall Smith', 4, 3), ('Charles Washer', 4, 3), ('Rodney Hastings', 6, 3), ('David Glenwood', 6, 3), ('Mary Underwood', 5, 3), ('Sanjay John', 2, 1), ('Eugene Kolbasin', 3, 1), ('Tom Staley', 3, 1), ('James Bond', 1, 2), ('Justin Timberlake', 5, 3), ('Joanne Stubbles', 6, 3); INSERT INTO salary (Employee_Id, Amount, Effective_Date, Expiration_Date) VALUES (6, 100000, '2013/10/1', '2014/9/30'), (1, 150000, '2013/10/1', '2014/9/30'), (5, 80000, '2013/10/1', '2014/9/30'), (4, 75000, '2014/1/1', '2014/9/30'), (2, 160000, '2014/1/1', '2014/9/30'), (3, 35000, '2014/1/1', '2014/9/30'), (15, 70000, '2014/1/1', '2014/9/30'), (11, 95000, '2014/1/1', '2014/9/30'), (10, 122500, '2013/10/1', '2014/9/30'), (14, 47500, '2013/10/1', '2014/9/30'), (12, 98765, '2013/10/1', '2014/9/30'), (13, 143000, '2013/10/1', '2014/9/30'), (4, 75000, '2013/10/1', '2013/12/31'), (2, 160000, '2013/10/1', '2013/12/31'), (3, 32000, '2013/10/1', '2013/12/31'), (15, 65000, '2013/10/1', '2013/12/31'), (11, 89000, '2013/10/1', '2013/12/31'); select l.name, min(s.amount) min_sal, max(s.amount) max_sal from employee e, location l, salary s where e.location_id = l.id and e.id = s.employee_id and extract(year from s.effective_date) = 2014 group by l.name; SELECT l.name, MIN(s.amount) AS 'Minimum Salary', MAX(s.amount) AS 'Maximum Salary' FROM location as l INNER JOIN employee as e ON l.id = e.location_id LEFT JOIN salary as s ON (s.employee_id = e.id AND year(s.effective_date) <= 2014 AND year(s.expiration_date) >= 2014) GROUP by l.name; select SUM(AMOUNT) from salary where EFFECTIVE_DATE between '2013/10/01' and '2014/10/01'; SELECT SUM((amount/12) * (TIMESTAMPDIFF(MONTH, effective_date, expiration_date) + 1)) as 'Total Salary' FROM salary WHERE effective_date >= '2013/10/1' AND expiration_date <= '2014/9/30'; select E.NAME from employee E, salary S where E.ID = S.EMPLOYEE_ID and EXTRACT(YEAR FROM S.EFFECTIVE_DATE) = 2013 and E.name not in (select E1.NAME from employee E1, salary S1 where E1.ID = S1.EMPLOYEE_ID and EXTRACT(YEAR FROM S1.EFFECTIVE_DATE) = 2014); SELECT e.name, previousYear.amount as previousSalary, IFNULL(currentYear.amount, previousYear.amount) as currentSalaryInJan2014 FROM employee as e -- 1. Query all employees with salary changes in January 1, 2014 LEFT JOIN (SELECT employee_id, amount FROM salary WHERE effective_date >= '2014/1/1') AS currentYear ON e.id = currentYear.employee_id -- 2. Query all employees with salary before Jan 1, 2014 LEFT JOIN (SELECT employee_id, amount FROM salary WHERE effective_date < '2014/1/1') AS previousYear ON e.id = previousYear.employee_id WHERE IFNULL(currentYear.amount, previousYear.amount) <= previousYear.amount -- 3. if the employee has no salary change in Jan 2014, we are assuming that they still have the same salary from last year ORDER BY e.name;