Setting up for exercise
Refer to the details here and get the setup ready before testing the samples
Fix Invalid DB2 SQL
INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (1001, 'John Doe', 'IT', '1234ABCD56', 50000);
SELECT * FROM EMP WHERE SALARY = NULL;
INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (1002, NULL, 'HR', '9876543210', 45000);
INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (1001, 'Jane Smith', 'Finance', '1122334455', 70000);
SELECT * FROM EMPLOYEE WHERE DEPARTMENT = 'IT';
SELECT * FROM EMP WHERE JOB_TITLE = 'Manager';
DELETE EMP WHERE EMP_ID = 1003;
UPDATE EMP SET SALARY = SALARY * 1.1 WHERE EMP_ID 1003;
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');
ORDER BY NAME;
SELECT * FROM EMP WHERE NAME LIKE John%;
INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY) VALUES (EMPLOYEE_SEQ.NEXTVAL, 'Mike Ross', 'IT', '5557779999', 48000);
SELECT * FROM EMP WHERE COUNT(SALARY) > 1;
SELECT DEPARTMENT, NAME, AVG(SALARY) FROM EMP GROUP BY DEPARTMENT;
Create SQL Insert
Insert an employee with EMP_ID = 1051, Name = 'Bob Carter', Department = 'IT', Phone = '1234567890', Salary = 52000.
Insert another employee using EMP_SEQ.NEXTVAL to generate the EMP_ID.
Insert an employee with a NULL phone number. Does it work? Why or why not?
Insert an employee with EMP_ID = 1052 and a negative salary. Does it work?
Insert an employee without specifying the SALARY. What happens?
Insert a mentor linked to EMP_ID 1053 with a skill of 'Python'.
Insert another mentor but set EMP_ID to NULL. Does it work?
Insert an employee with a name longer than 20 characters. What happens?
Insert an employee with an invalid department name that exceeds 10 characters.
Insert an employee with a phone number containing letters. What happens?
Create SQL Update
Update employee EMP_ID = 1002 to change their salary to 62000.
Update the department of all employees in HR to ADMIN.
Update the phone number of EMP_ID = 1003 to '1112223333'.
Increase the salary of all employees in IT by 10%.
Set the phone number to NULL for EMP_ID = 1004. Does it work?
Change the department of all employees with salary < 50000 to "JUNIOR".
Update a mentor’s EMP_ID to a value that doesn’t exist. Does it work?
Update an employee’s salary to 29000. What happens?
Update the NAME column to NULL for an employee. Does it work?
Try updating a nonexistent EMP_ID. What happens?
Create SQL Delete
Delete an employee with EMP_ID = 1002. What happens if they are a mentor?
Delete all employees with a salary less than 35,000. What happens to their mentor records?
Delete a mentor with MENTOR_ID = 1001.
Delete all mentors where SKILLS = 'Java'.
Delete an employee and then try to insert a mentor record with the deleted EMP_ID. What happens?
Delete all employees in the IT department.
Delete all mentors whose EMP_ID is NULL.
Delete an employee using a nonexistent EMP_ID. Does it generate an error?
Delete all employees except those earning more than 60,000.
Delete all data from the EMP table but keep the table structure.
Create SQL Query
Retrieve all employees earning more than 50,000.
Get the names and departments of employees who are mentors (i.e., exist in the MENTOR table).
Retrieve employees who are mentors (without fetching skills).
Find employees with no recorded phone number.
Find employees who have no salary information recorded.
Retrieve all employees sorted by salary in descending order.
Find all employees in a specific department (e.g., IT).
Get the list of unique skills available in the mentor table.