SQL Quiz 7

1. In EMP table, print update sentence whose employees with a job of ‘SALESMAN’ to 400.

1
2
3
UPDATE EMP_BAK_TABLE
   SET sal = sal + 400
 WHERE job = 'SALESMAN'

2. In EMP table, print update sentence that adds a year as employees whose salary higher than their average salary

1
2
3
UPDATE EMP_BAK_TABLE
   SET hiredate = hiredate + (INTERVAL '1' YEAR)
 WHERE sal > (SELECT AVG(sal) FROM emp)

3. In EMP table, print update sentence that adds 100 in COMM column and multiply 2 times for employee whose job of ‘CLERK’ and multiply 3 times for employee whose job of ‘MANAGER’ and multiply 4 times for employee whose have another job.

1
2
3
4
5
6
7
UPDATE EMP_BAK_TABLE
   SET COMM = COMM + 100,
       SAL = CASE
  WHEN job = 'CLERK' THEN sal * 2
  WHEN job = 'MANAGER' THEN sal * 3
  ELSE sal * 4
END

4. In EMP table, print delete sentence that employee whose name start with ‘M’.

1
2
DELETE FROM EMP_BAK_TABLE
 WHERE ename LIKE 'M%'

5. In EMP table, print delete sentence that employee whose salary higher than another average salary.

1
2
DELETE FROM EMP_BAK_TABLE
 WHERE sal > (SELECT AVG(sal) FROM EMP_BAK_TABLE);