------------- *** LINKS *** ------------- >>> CONTROL FLOW FUNCTIONS http://psoug.org/reference/decode_case.html https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html CASE, if(), ifnull() https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql >>> STRING FUNCTIONS http://psoug.org/reference/string_func.html , + INSTR, SUBSTR, NVL http://dev.mysql.com/doc/refman/8.0/en/string-functions.html https://docs.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql concat_ws(), concat(), instr(), length(), LIKE, lower()/upper(), lpad()/rpad(), ltrim()/rtrim()/trim(), replace(), strcmp(), substr()/substring() >>> NUMERIC FUNCTIONS http://psoug.org/reference/number_func.html ABS, COALESCE, LENGTH, MOD, NVL, POWER, ROUND, SQRT, TRUNC + conversion functions http://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html Operators: http://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html https://docs.microsoft.com/en-us/sql/t-sql/functions/mathematical-functions-transact-sql abs(), ceil()/floor()/round(), conv(), mod(), pow(), rand(), sign(), sqrt() >>> DATE FUNCTIONS http://psoug.org/reference/date_func.html SYSDATE, TO_CHAR, TO_DATE alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD' http://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html curdate(), date_format(), datediff(), extract(), now(), sysdate(), timediff(), unix_timestamp() https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql datepart, datediff ------------- *** JOINS *** ------------- 3.1 List all workers and the name of their departments where the department's name contains the "A" letter. List all workers who have their bosses work in a department where the department's name contains the "A" letter. List all workers who have their bosses' boss work in a department where the department's name contains the "A" letter. 3.4 List the name, job and income for the workers who work in Dallas or Chicago. 3.5 List the average salary for every departments. Order the list by the department names. 3.8 List those departments (name and location) where the average salary is smaller than 2200 USD 3.12 List all workers (name, department's name and income) whose income is the same as anyone's income who work in Dallas. Order the list by the income and the department's name. 3.14 List all bosses who are not Managers. How many bosses are who are not managers? 3.18 List one employee for each boss: the employee must have the smallest salary amongst the employees of the given boss, but his salary must be bigger than 1000 usd 3.20 List the workers (name, salary, job, boss' name and salary, and the ratio between the worker salary and the boss' salary) for every clerks and salesmans. 3.21 List the name, job and salary for the bosses who work in Chicago. Add the average salary of his workers to the list. 3.23 List the one worker per department (name, job, location, years spent at the company) who has the minimum salary in his department. 3.25 List the workers (name, job, location) where the average income of their job is smaller than the average income of the company. Add the difference between the worker salary and the average salary of their department to the list. 3.29 List the name, job, department name and salary for every worker. Add the percentage of this salary compared to the average salary of his job. Add the percentage of this salary compared to the average salary of his department. 3.30 List those workers (name, location) who have their salaries bigger than the average salary of their department. List those workers (name, location) who have their salaries bigger than the average salary of their job. 3.31 List all the workers of the department that has the smallest average salary. ------------- *** VIEWS *** ------------- A1 Create a view that lists all workers from table emp whose income is bigger than 1000 USD and whose boss works in DALLAS or CHICAGO. A2 Create a view called "bonus" for the workers present in the first view. Those whose salary is more than the half of their boss' salary should get 300 USD, the rest should get 700USD bonus. A3 List the name, job, TOTAL income, boss' name and the name of the boss' boss for every worker, ordered primarily by the total income. Every worker should be present in this list, even the ones whose boss is missing. ------------- B1 Create a view that lists all workers from table emp who joined after 1981 and whose boss' income is bigger than 2400 USD. B2 Create a view called "premium" for the workers present in the first view. The ones who have lower income than the average salary of their job should get 600 USD, the rest should get 200USD premium. B3 List the name, job, the TOTAL income, the boss' name and the boss' location for every worker, ordered primarily by the boss' location, secondary descending by the job. If there is no boss for a worker, then the text "no boss" should be displayed instead of the boss' name. ------------- C1 Create a view that lists all workers from table emp whose boss have a boss. C2 Create a view called "bonus" for the workers present in the first view. The ones who have higher income than the average income of their department should get 400 USD, the rest should get 800 USD bonus. C3 List the name, job, department name, the TOTAL income and the boss' name for every worker, ordered primarily by the boss' name, secondary descending by the total income. If there is no boss for a worker, then the text "no boss" should be displayed instead of the boss' name. ------------- *** TABLE *** ------------- B1 v2 Create table 'worker' in a consistent way: it should contain all workers from table emp who joined after 1981 and whose boss' income is bigger than 2400 USD. B2 v2 Extend the worker table worker with a column called "premium". Fill it up so that the ones who have lower income than the average salary of their job should get 600 USD, the rest should get 200USD premium. B3 v2 List the worker table: name, job, the TOTAL income, the boss' name and the boss' location for every worker, ordered primarily by the boss' location, secondary descending by the job. If there is no boss for a worker, then the text "no boss" should be displayed instead of the boss' data.