CKVN_Cracker
Junior Member
Mọi người cho em hỏi tí em có 2 task sau trên Udemy, em đã search trên GG mà không có câu trả lời:
Task 1: Execute the following two queries.
1. Create a temporary table called salaries_adjusted_for_inflation based on the data in the salaries table. It should contain the following five fields for all employees:
1.1 Employee number (emp_no)
1.2 Salary value (salary)
1.3 A field named inflation_adjusted_salary containing the salary value (salary) rounded to the nearest cent, which should be:
- Multiplied by 6.5 if the contract start date (from_date) was between January 1, 1970, and December 31, 1989, inclusive;
- Multiplied by 2.8 if the contract start date (from_date) was between January 1, 1990, and December 31, 1999, inclusive;
- Multiplied by 3 for the rest of the contracts.
1.4 The contract start date (from_date)
1.5 The contract end date (to_date).
Task 2: Copy and paste the SELECT statement creating the salaries_adjusted_for_inflation temporary table in the WITH clause of a common table expression whose top-level SELECT statement joins the common table expression data with the data from the salaries_adjusted_for_inflation table on the employee number (emp_no).
Nhưng em cứ bị lỗi này:
Your code can't run against tests.
There might be something wrong with syntax usage in your code. You can use error details to fix it.
Your evaluation result is too big to display
Ai giúp em với ạ, rốt cuộc code em sai ở đâu ạ
Link em cóp xuống MySQLđâyạ, bản trên Udemy là MySQL Lite: Help.sql (https://drive.google.com/file/d/1tvZg2Dtvk8MJfcwiJfLcjeJJsSkLqoKl/view?usp=sharing)
Task 1: Execute the following two queries.
1. Create a temporary table called salaries_adjusted_for_inflation based on the data in the salaries table. It should contain the following five fields for all employees:
1.1 Employee number (emp_no)
1.2 Salary value (salary)
1.3 A field named inflation_adjusted_salary containing the salary value (salary) rounded to the nearest cent, which should be:
- Multiplied by 6.5 if the contract start date (from_date) was between January 1, 1970, and December 31, 1989, inclusive;
- Multiplied by 2.8 if the contract start date (from_date) was between January 1, 1990, and December 31, 1999, inclusive;
- Multiplied by 3 for the rest of the contracts.
1.4 The contract start date (from_date)
1.5 The contract end date (to_date).
Task 2: Copy and paste the SELECT statement creating the salaries_adjusted_for_inflation temporary table in the WITH clause of a common table expression whose top-level SELECT statement joins the common table expression data with the data from the salaries_adjusted_for_inflation table on the employee number (emp_no).
SQL:
WITH inflation_cte AS (
SELECT
emp_no,
salary,
CASE
WHEN from_date BETWEEN '1970-01-01' AND '1989-12-31' THEN ROUND(salary * 6.5, 2)
WHEN from_date BETWEEN '1990-01-01' AND '1999-12-31' THEN ROUND(salary * 2.8, 2)
ELSE ROUND(salary * 3, 2)
END AS inflation_adjusted_salary,
from_date,
to_date
FROM
salaries
)
SELECT
cte.emp_no,
cte.salary,
cte.inflation_adjusted_salary,
cte.from_date,
cte.to_date,
safi.inflation_adjusted_salary
FROM
inflation_cte cte
INNER JOIN salaries_adjusted_for_inflation safi ON cte.emp_no = safi.emp_no;Nhưng em cứ bị lỗi này:
Your code can't run against tests.
There might be something wrong with syntax usage in your code. You can use error details to fix it.
Your evaluation result is too big to display
Ai giúp em với ạ, rốt cuộc code em sai ở đâu ạ
Link em cóp xuống MySQLđâyạ, bản trên Udemy là MySQL Lite: Help.sql (https://drive.google.com/file/d/1tvZg2Dtvk8MJfcwiJfLcjeJJsSkLqoKl/view?usp=sharing)Tệp đính kèm
Sửa lần cuối: