Last answered:

06 Jan 2023

Posted on:

26 Dec 2022

0

Error Code: 1048. Column 'manager_no' cannot be null.

I tried to compare my written queries to the given answer but I cannot find the difference. Below is my answer. Kindly assist. Thank you.

CREATE TABLE emp_manager (
    emp_no INT(11) NOT NULL,
    dept_no CHAR(4) NULL,
    manager_no INT(11) NOT NULL
);

Insert INTO emp_manager SELECT
U.* FROM (SELECT A.* FROM
(SELECT
e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
(SELECT emp_no FROM dept_manager WHERE emp_no = 110022) AS manager_ID
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE e.emp_no <= 10020
GROUP BY e.emp_no
ORDER BY e.emp_no)AS A
UNION
SELECT B.* FROM
(SELECT
e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
(SELECT emp_no FROM dept_manager WHERE emp_no = 110039) AS manager_ID
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE e.emp_no BETWEEN 10021 AND 10040
GROUP BY e.emp_no
ORDER BY e.emp_no)AS B
UNION
SELECT C.* FROM
(SELECT
e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
(SELECT emp_no FROM dept_manager WHERE emp_no = 110039) AS manager_ID
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE e.emp_no = 110022
GROUP BY e.emp_no)AS C
UNION
SELECT D.* FROM
(SELECT
e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
(SELECT emp_no FROM dept_manager WHERE emp_no = 110021) AS manager_ID
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE e.emp_no = 110039
GROUP BY e.emp_no)AS D)AS U;

1 answers ( 0 marked as helpful)
Instructor
Posted on:

06 Jan 2023

0

Hi MingKit!
Thanks for reaching out.

You obtain this error because you are trying to insert null values in the manager_no column, while this is something that you have disallowed by creation of the table. More specifically, you've done this after executing the following code in the CREATE TABLE statement.

manager_no INT(11) NOT NULL

Hope this helps.
Best,
Tsvetelin

Submit an answer