DATA ANALYTICS REFERENCE DOCUMENT |
|
---|---|
Document Title: | Appliead Databases week 3 |
Document No.: | 1549571853 |
Author(s): | Gerhard van der Linde |
Contributor(s): |
REVISION HISTORY
Revision | Details of Modification(s) | Reason for modification | Date | By |
---|---|---|---|---|
0 | Draft release | Document description here | 2019/02/07 20:37 | Gerhard van der Linde |
MySQL - u root -p <employees.sql
mysql> SELECT emp_no, first_name, ucase(last_name) 'last_name' -> FROM employees.employees -> limit 10; +--------+------------+-----------+ | emp_no | first_name | last_name | +--------+------------+-----------+ | 10001 | Georgi | FACELLO | | 10002 | Bezalel | SIMMEL | | 10003 | Parto | BAMFORD | | 10004 | Chirstian | KOBLICK | | 10005 | Kyoichi | MALINIAK | | 10006 | Anneke | PREUSIG | | 10007 | Tzvetan | ZIELINSKI | | 10008 | Saniya | KALLOUFI | | 10009 | Sumant | PEAC | | 10010 | Duangkaew | PIVETEAU | +--------+------------+-----------+ 10 rows in set (0.00 sec)
SELECT * FROM employees.employees # order by length(last_name) # order by last_name # order by first_name order by length(first_name); SELECT * FROM employees.employees order by length(last_name), last_name, length(first_name), first_name;
mysql> SELECT emp_no,birth_date,first_name,last_name,gender,hire_date, concat(left(first_name,1), left(last_name,1)) 'Initials' -> FROM employees.employees -> limit 10; +--------+------------+------------+-----------+--------+------------+----------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | Initials | +--------+------------+------------+-----------+--------+------------+----------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | GF | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | BS | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | PB | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | CK | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | KM | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | AP | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | TZ | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | SK | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | SP | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | DP | +--------+------------+------------+-----------+--------+------------+----------+ 10 rows in set (0.00 sec)
mysql> SELECT * FROM employees.employees -> where gender = 'F' -> and (year(birth_date)>='1950' and year(birth_date)<='1959') -> and (hire_date>='1988-09-01' and hire_date<='1991-02-28') -> ; +--------+------------+------------+------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+------------+--------+------------+ | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 | | 10023 | 1953-09-29 | Bojan | Montemayor | F | 1989-12-17 | | 10041 | 1959-08-27 | Uri | Lenart | F | 1989-11-12 | | 10063 | 1952-08-06 | Gino | Leonhardt | F | 1989-04-08 | | 10074 | 1955-08-28 | Mokhtar | Bernatsky | F | 1990-08-13 | | 10088 | 1954-02-25 | Jungsoon | Syrzycki | F | 1988-09-02 | | 10099 | 1956-05-25 | Valter | Sullins | F | 1988-10-18 | +--------+------------+------------+------------+--------+------------+ 9 rows in set (0.03 sec)
mysql> SELECT round(avg(salary),2) 'avg_salary' FROM employees.salaries; +------------+ | avg_salary | +------------+ | 64417.59 | +------------+ 1 row in set (0.00 sec)
mysql> SELECT emp_no, round(avg(salary),2) avg_sal -> FROM employees.salaries -> group by emp_no -> limit 10; +--------+----------+ | emp_no | avg_sal | +--------+----------+ | 10001 | 75388.94 | | 10002 | 68854.50 | | 10003 | 43030.29 | | 10004 | 56512.25 | | 10005 | 87275.77 | | 10006 | 50514.92 | | 10007 | 70826.71 | | 10008 | 49307.67 | | 10009 | 78284.56 | | 10010 | 76723.00 | +--------+----------+ 10 rows in set (0.01 sec)
mysql> SELECT emp_no, cast(max(salary) as decimal(10,2)) max_sal -> FROM employees.salaries -> group by emp_no -> limit 10; +--------+----------+ | emp_no | max_sal | +--------+----------+ | 10001 | 88958.00 | | 10002 | 72527.00 | | 10003 | 43699.00 | | 10004 | 74057.00 | | 10005 | 94692.00 | | 10006 | 60098.00 | | 10007 | 88070.00 | | 10008 | 52668.00 | | 10009 | 94443.00 | | 10010 | 80324.00 | +--------+----------+ 10 rows in set (0.00 sec)
mysql> SELECT emp_no, -> avg(salary) -> FROM employees.salaries -> where emp_no in (10001, 10021, 10033, 10087) -> and salary > 80000 -> group by emp_no; +--------+-------------+ | emp_no | avg(salary) | +--------+-------------+ | 10001 | 83745.5714 | | 10021 | 83232.0000 | | 10087 | 99015.2500 | +--------+-------------+ 3 rows in set (0.01 sec)
note: forgot to round to 2 decimal points
mysql> SELECT emp_no, round(avg(salary)) as avg_sal -> FROM employees.salaries -> group by emp_no -> having avg_sal > 90000; +--------+---------+ | emp_no | avg_sal | +--------+---------+ | 10024 | 90572 | | 10068 | 101224 | | 10087 | 99015 | +--------+---------+ 3 rows in set (0.00 sec)
mysql> SELECT emp_no as ID, -> if(gender = 'M', 'Mr.','Ms.') as Gender, -> first_name as Name, -> last_name as Surname, -> gender as Gender -> FROM employees.employees -> order by emp_no -> limit 15; +-------+--------+-----------+-----------+--------+ | ID | Gender | Name | Surname | Gender | +-------+--------+-----------+-----------+--------+ | 10001 | Mr. | Georgi | Facello | M | | 10002 | Ms. | Bezalel | Simmel | F | | 10003 | Mr. | Parto | Bamford | M | | 10004 | Mr. | Chirstian | Koblick | M | | 10005 | Mr. | Kyoichi | Maliniak | M | | 10006 | Ms. | Anneke | Preusig | F | | 10007 | Ms. | Tzvetan | Zielinski | F | | 10008 | Mr. | Saniya | Kalloufi | M | | 10009 | Ms. | Sumant | Peac | F | | 10010 | Ms. | Duangkaew | Piveteau | F | | 10011 | Ms. | Mary | Sluis | F | | 10012 | Mr. | Patricio | Bridgland | M | | 10013 | Mr. | Eberhardt | Terkki | M | | 10014 | Mr. | Berni | Genin | M | | 10015 | Mr. | Guoxiang | Nooteboom | M | +-------+--------+-----------+-----------+--------+ 15 rows in set (0.00 sec)
Tax brackets are defined as follows:
Max Salary | Tax Bracket |
---|---|
Under 40,000 | 30% |
Under 60,000 | 40% |
Under 80,000 | 50% |
Over 80,000 | 60% |
mysql> SELECT emp_no as 'Employee Number', -> max(salary) as 'Max Salary', -> CASE -> when max(salary) < 40000 then '30%' -> when max(salary) < 60000 then '40%' -> when max(salary) < 80000 then '50%' -> else '60%' -> END as 'Tax Bracket' -> FROM employees.salaries -> group by emp_no -> limit 15; +-----------------+------------+-------------+ | Employee Number | Max Salary | Tax Bracket | +-----------------+------------+-------------+ | 10001 | 88958 | 60% | | 10002 | 72527 | 50% | | 10003 | 43699 | 40% | | 10004 | 74057 | 50% | | 10005 | 94692 | 60% | | 10006 | 60098 | 50% | | 10007 | 88070 | 60% | | 10008 | 52668 | 40% | | 10009 | 94443 | 60% | | 10010 | 80324 | 60% | | 10011 | 56753 | 40% | | 10012 | 54794 | 40% | | 10013 | 68901 | 50% | | 10014 | 60598 | 50% | | 10015 | 40000 | 40% | +-----------------+------------+-------------+ 15 rows in set (0.00 sec)
SELECT *, if(datediff(to_date,from_date)<356, "under 1 year", "over 1 year") as Time FROM employees.salaries # having Time like '%under%' ;
+--------+--------+------------+------------+-------------+ | emp_no | salary | from_date | to_date | Time | +--------+--------+------------+------------+-------------+ | 10001 | 60117 | 1986-06-26 | 1987-06-26 | over 1 year | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | over 1 year | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | over 1 year | | 10001 | 66596 | 1989-06-25 | 1990-06-25 | over 1 year | | 10001 | 66961 | 1990-06-25 | 1991-06-25 | over 1 year | | 10001 | 71046 | 1991-06-25 | 1992-06-24 | over 1 year | | 10001 | 74333 | 1992-06-24 | 1993-06-24 | over 1 year | | 10001 | 75286 | 1993-06-24 | 1994-06-24 | over 1 year | | 10001 | 75994 | 1994-06-24 | 1995-06-24 | over 1 year | | 10001 | 76884 | 1995-06-24 | 1996-06-23 | over 1 year | +--------+--------+------------+------------+-------------+ 10 rows in set (0.00 sec)
HINT: Don’t for get to change the delimiter when writing the function and change it back to a semi-colon when the function is written.
CREATE FUNCTION `getage`(d1 date, d2 date) RETURNS float(5,1) DETERMINISTIC BEGIN RETURN round(datediff(d2,d1)/365,1); END
+--------+------------+------------+-----------+--------+------------+------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | Age | +--------+------------+------------+-----------+--------+------------+------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 32.8 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 21.5 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 26.8 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 32.6 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 34.7 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | 36.1 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | 31.7 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | 36.6 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | 32.9 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | 26.2 | +--------+------------+------------+-----------+--------+------------+------+ 10 rows in set (0.00 sec)
CREATE DEFINER=`jattie`@`%` PROCEDURE `hires`(y integer, m integer) DETERMINISTIC BEGIN SELECT * FROM employees.employees where year(hire_date) = y and month(hire_date) = m; END
call hires(1988,9);
+--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 | | 10035 | 1953-02-08 | Alain | Chappelet | M | 1988-09-05 | | 10088 | 1954-02-25 | Jungsoon | Syrzycki | F | 1988-09-02 | +--------+------------+------------+-----------+--------+------------+ 3 rows in set (0.01 sec)
HINT: To call a procedure with a NULL value for month (assuming in this case month is the second parameter) procedure_name(1985, NULL). To check if a parameter, e.g. m, is NULL say IF M IS NULL THEN To check if a parameter, e.g. m, is not NULL say IF M IS NOT NULL THEN.
CREATE DEFINER=`jattie`@`%` PROCEDURE `hires_2`(y integer, m integer) DETERMINISTIC BEGIN if m is null then select * from employees where year(hire_date) = y; else select * from employees where year(hire_date) = y and month(hire_date) = m; end if; END
mysql> call hires_2(1988, null); +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10021 | 1960-02-20 | Ramzi | Erde | M | 1988-02-10 | | 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 | | 10035 | 1953-02-08 | Alain | Chappelet | M | 1988-09-05 | | 10039 | 1959-10-01 | Alejandro | Brender | M | 1988-01-19 | | 10052 | 1961-02-26 | Heping | Nitsch | M | 1988-05-21 | | 10065 | 1963-04-14 | Satosi | Awdeh | M | 1988-05-18 | | 10072 | 1952-05-15 | Hironoby | Sidou | F | 1988-07-21 | | 10088 | 1954-02-25 | Jungsoon | Syrzycki | F | 1988-09-02 | | 10099 | 1956-05-25 | Valter | Sullins | F | 1988-10-18 | +--------+------------+------------+-----------+--------+------------+ 9 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)