Setting up for exercise

Refer to the details here and get the setup ready before testing the samples

Fix Invalid DB2 SQL

1. INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (1001, 'John Doe', 'IT', '1234ABCD56', 50000);
2. SELECT * FROM EMP WHERE SALARY = NULL;
3. INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (1002, NULL, 'HR', '9876543210', 45000);
4. INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (1001, 'Jane Smith', 'Finance', '1122334455', 70000);
5. SELECT * FROM EMPLOYEE WHERE DEPARTMENT = 'IT';
6. SELECT * FROM EMP WHERE JOB_TITLE = 'Manager';
7. DELETE EMP WHERE EMP_ID = 1003;
8. UPDATE EMP SET SALARY = SALARY * 1.1 WHERE EMP_ID 1003;
9. Multiple inserts
   • INSERT INTO MENTOR (MENTOR_ID, EMP_ID, SKILLS) VALUES (1000, 1001, 'Java');
   • INSERT INTO MENTOR (MENTOR_ID, EMP_ID, SKILLS) VALUES (1000, 1002, 'Java');
10. ORDER BY NAME;
11. SELECT * FROM EMP WHERE NAME LIKE John%;
12. INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (EMPLOYEE_SEQ.NEXTVAL, 'Mike Ross', 'IT', '5557779999', 48000);
13. SELECT * FROM EMP WHERE COUNT(SALARY) > 1;
14. SELECT DEPARTMENT, NAME, AVG(SALARY) FROM EMP GROUP BY DEPARTMENT;

Create SQL Insert

1. Insert an employee with EMP_ID = 1003, Name = 'Bob Carter', Department = 'IT', Phone = '1234567890', Salary = 52000.
2. Insert another employee using EMP_SEQ.NEXTVAL to generate the EMP_ID.
3. Insert an employee with a NULL phone number. Does it work? Why or why not?
4. Insert an employee with EMP_ID = 1005 and a negative salary. Does it work?
5. Insert an employee without specifying the SALARY. What happens?
6. Insert a mentor linked to EMP_ID 1002 with a skill of 'Python'.
7. Insert another mentor but set EMP_ID to NULL. Does it work?
8. Insert an employee with a name longer than 20 characters. What happens?
9. Insert an employee with an invalid department name that exceeds 10 characters.
10. Insert an employee with a phone number containing letters. What happens?

Create SQL Update

1. Update employee EMP_ID = 1002 to change their salary to 62000.
2. Update the department of all employees inHR to ADMIN.
3. Update the phone number of EMP_ID = 1003 to '1112223333'.
4. Increase the salary of all employees in IT by 10%.
5. Set the phone number to NULL for EMP_ID = 1004. Does it work?
6. Change the department of all employees with salary < 50000 to "JUNIOR".
7. Update an employee's MENTOR_ID to a value that doesn’t exist. Does it work?
8. Update an employee’s salary to 29000. What happens?
9. Update the NAME column to NULL for an employee. Does it work?
10. Try updating a nonexistent EMP_ID. What happens?

Create SQL Delete

1. Delete employee withEMP_ID = 1002. What happens if they are a mentor?
2. Delete all employees with a salary less than 35000. What happens to the mentor records?
3. Delete a mentor withMENTOR_ID = 1001.
4. Delete all mentors where SKILLS = 'Java'.
5. Try to delete an employee whose EMP_ID is referenced in MENTOR but without ON DELETE CASCADE. What happens?
6. Delete all employees in theIT department.
7. Delete all mentors whose EMP_ID is NULL.
8. Delete an employee using a nonexistent EMP_ID. Does it generate an error?
9. Delete all employees except those earning more than 60000.
10. Delete all data from the EMP table but keep the table structure.

Create SQL Query

1. Retrieve all employees earning more than 50,000.
2. Get the names and departments of employees who are also mentors.
3. Find the employees who do not have mentors.
4. Count the number of employees in each department.
5. Display all employees and their mentor skills(if they have a mentor).
6. Get the highest salary in each department.
7. Retrieve all employees sorted by salary in descending order.
8. Show the total salary expense foreach department.
9. Find all employees who have the same salary as someone else.
10. Get the names of employees who earn more than their mentor's mentees.