You Asked
Hi,
I was practicing some question on sql challenge about dml operation using multiple tables and got some doubt.
CREATE TABLE plch_departments
( department_id INTEGER PRIMARY KEY,
department_name VARCHAR2(30) )
/
CREATE TABLE plch_employees
( employee_id INTEGER PRIMARY KEY,
last_name VARCHAR2(30),
salary NUMBER NOT NULL,
department_id INTEGER REFERENCES plch_departments (department_id)
)
/
INSERT INTO plch_departments VALUES (10, 'Accounting');
INSERT INTO plch_departments VALUES (20, 'Marketing');
INSERT INTO plch_departments VALUES (30, 'Sales');
INSERT INTO plch_departments VALUES (40, 'Advertising');
INSERT INTO plch_employees VALUES (100, 'Jobs', 3000, 10);
INSERT INTO plch_employees VALUES (200, 'Ellison', 5000, 10);
INSERT INTO plch_employees VALUES (300, 'Gates', 4000, 20);
INSERT INTO plch_employees VALUES (400, 'Scott', 2500, 30);
INSERT INTO plch_employees VALUES (500, 'Tiger', 6000, 30);
INSERT INTO plch_employees VALUES (600, 'Mickey', 5000, 30);
INSERT INTO plch_employees VALUES (700, 'Mouse', 1500, NULL);
COMMIT;
We have decided to add a TOTAL_SALARY column to the plch_departments table, that will contain the total of the salaries for each department's employees:
ALTER TABLE plch_departments
ADD ( total_salary NUMBER DEFAULT 0 NOT NULL )
/
The column will be maintained further using a trigger, but, as a first step, we should update the new column to make it reflect the already existing data in the plch_employees table:
SELECT * FROM plch_departments
ORDER BY department_id
/
DEPARTMENT_ID DEPARTMENT_NAME TOTAL_SALARY
------------- ------------------------------ ------------
10 Accounting 8000
20 Marketing 4000
30 Sales 13500
40 Advertising 0
4 rows selected.
Here is my doubt:
Below are two of the solution :
UPDATE plch_departments d
SET total_salary = ( SELECT NVL(SUM(salary),0)
FROM plch_employees
WHERE department_id = d.department_id )
/
Here, nvl is used because a particular department_id might not be present in employee table and result of co-related sub-query will return null.
UPDATE plch_departments d
SET total_salary = NVL( ( SELECT SUM(salary)
FROM plch_employees
WHERE department_id = d.department_id
GROUP BY department_id ), 0)
/
But here nvl inside sub-query does not work and should be kept outside. But i think logic on above solution should also be applied here.
Could you please explain in detail the use of nvl in above solution ?
Thanks,
DIPU
and Connor said...
They are more or less equivalent, although I prefer the first one in terms of readability. (And I dont know why the 'group by' has been added in the second one).
I've changed the second NVL to be a '999' to see the difference - but you can see, the results come out the same
SQL> CREATE TABLE plch_departments
2 ( department_id INTEGER PRIMARY KEY,
3 department_name VARCHAR2(30) )
4 /
Table created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE plch_employees
2 ( employee_id INTEGER PRIMARY KEY,
3 last_name VARCHAR2(30),
4 salary NUMBER NOT NULL,
5 department_id INTEGER REFERENCES plch_departments (department_id)
6 )
7 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO plch_departments VALUES (10, 'Accounting');
1 row created.
SQL> INSERT INTO plch_departments VALUES (20, 'Marketing');
1 row created.
SQL> INSERT INTO plch_departments VALUES (30, 'Sales');
1 row created.
SQL> INSERT INTO plch_departments VALUES (40, 'Advertising');
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO plch_employees VALUES (100, 'Jobs', 3000, 10);
1 row created.
SQL> INSERT INTO plch_employees VALUES (200, 'Ellison', 5000, 10);
1 row created.
SQL> INSERT INTO plch_employees VALUES (300, 'Gates', 4000, 20);
1 row created.
SQL> INSERT INTO plch_employees VALUES (400, 'Scott', 2500, 30);
1 row created.
SQL> INSERT INTO plch_employees VALUES (500, 'Tiger', 6000, 30);
1 row created.
SQL> INSERT INTO plch_employees VALUES (600, 'Mickey', 5000, 30);
1 row created.
SQL> INSERT INTO plch_employees VALUES (700, 'Mouse', 1500, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> ALTER TABLE plch_departments
2 ADD ( total_salary NUMBER DEFAULT 0 NOT NULL )
3 /
Table altered.
SQL>
SQL>
SQL> SELECT * FROM plch_departments
2 ORDER BY department_id
3 /
DEPARTMENT_ID DEPARTMENT_NAME TOTAL_SALARY
------------- ------------------------------ ------------
10 Accounting 0
20 Marketing 0
30 Sales 0
40 Advertising 0
SQL> UPDATE plch_departments d
2 SET total_salary = ( SELECT NVL(SUM(salary),0)
3 FROM plch_employees
4 WHERE department_id = d.department_id )
5 /
4 rows updated.
SQL>
SQL> SELECT * FROM plch_departments
2 ORDER BY department_id
3 /
DEPARTMENT_ID DEPARTMENT_NAME TOTAL_SALARY
------------- ------------------------------ ------------
10 Accounting 8000
20 Marketing 4000
30 Sales 13500
40 Advertising 0
SQL>
SQL> UPDATE plch_departments d
2 SET total_salary = NVL( ( SELECT SUM(salary)
3 FROM plch_employees
4 WHERE department_id = d.department_id
5 GROUP BY department_id ), 999)
6 /
4 rows updated.
SQL>
SQL> SELECT * FROM plch_departments
2 ORDER BY department_id
3 /
DEPARTMENT_ID DEPARTMENT_NAME TOTAL_SALARY
------------- ------------------------------ ------------
10 Accounting 8000
20 Marketing 4000
30 Sales 13500
40 Advertising 999
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment