Oct 21, 2014 - Computer Engineering Dept. Database Lab (ECOM ... Write SQL statement to insert a new record into JOBS ta
Islamic University of Gaza Faculty of Engineering Computer Engineering Dept. Database Lab (ECOM 4113)
Lab 2 Exercises Solutions
Structured Query Language (SQL)
Eng. Mohammed Alokshiya
October 21, 2014
Exercises Solutions: 1. Write SQL statement to insert a new record into JOBS table with
the following information: JOB_ID = C_ENG JOB_TITLE = Computer Engineer MIN_SALARY = 20000 MAX_SALARY = 50000
Suggested Solution INSERT INTO JOBS VALUES ('C_ENG', 'Computer Engineer', 20000, 50000);
2. Write SQL statement to modify all employees’ JOB_IDs to
C_ENG, if there salary is between 20000 and 50000. Suggested Solution UPDATE EMPLOYEES SET JOB_ID = 'C_ENG' WHERE SALARY BETWEEN 20000 AND 50000;
3. Write SQL statement to delete all employees who are not
working in any departments, and all employees who are working in department 80, but their TOTAL salary is less than 7000. Note: total salary = salary + salary * commission_pct Suggested Solution DELETE FROM EMPLOYEES WHERE DEPARTMENT_ID IS NULL OR DEPARTMENT_ID = 80 AND SALARY + SALARY * NVL(COMMISSION_PCT, 0) < 7000;
2
4. Write SQL statement to retrieve the last name and salary for all
employees whose salary is not in the range 5000 through 12000. Suggested Solution SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY NOT BETWEEN 5000 AND 12000;
5. Write SQL statement to retrieve the last names of all employees
who have both an “a” and an “e” in their last name. Suggested Solution SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '%a%' AND LAST_NAME LIKE '%e%';
6. Write SQL statement to retrieve the last name, salary, and
commission for all employees who earn commissions. Sort data in descending order of salary and commissions. Use the column’s numeric position in the ORDER BY clause. Suggested Solution SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL ORDER BY 2 DESC, 3 DESC;
7. Write SQL statement to retrieve all employee last names in
which the third letter of the name is “a”. Suggested Solution SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '__a%';
