SQL Practice ASSIGNMENT -3
Section A – Logical Operators (AND , OR , NOT)
1. Display employees with salary > 40000 AND department_id = 100.
2. Retrieve first_name, salary where job_id = ‘IT_PROG’ OR job_id = ‘SA_REP’.
3. Show employees whose department_id is NOT 50.
4. Display employee_id, first_name where hire_date > '01-JAN-2018' AND salary < 60000.
5. List employees where job_id = ‘FI_ACCOUNT’ OR department_id = 90.
Section B – – Combined Operators
6. Display employees with salary between 40000 and 60000 using relational operators.
7. Retrieve employees whose salary > 50000 AND commission_pct IS NOT NULL.
8. List first_name, job_id where NOT(job_id = 'HR_REP').
9. Show employees who earn less than 30000 OR were hired after 2021.
10. Display employee_id, salary, annual salary (salary*12) only for employees in department 100 AND salary
>= 70000.
Section C – SELECT with Concatenation, Alias, and NULL
11. Display employee full name by concatenating first_name and last_name as “Full_Name”.
12. SELECT first_name || ' ' || last_name AS Full_Name FROM employees;
13. Show employee email addresses as email || '@[Link]'.
14. Display “Employee: <first_name> works as <job_id>” for all employees.
15. List employee_id along with a string “Salary: ” concatenated with salary.
16. Concatenate first_name and hire_date into a single column “Name_HireDate”.
Section D – Alias (AS)
17. Display employee_id AS “ID”, first_name AS “EmployeeName”, salary AS “MonthlySalary”.
18. Show annual salary (salary * 12) as “Annual_Salary”.
19. Display commission_pct as “BonusRate”.
20. Show concatenated name as “Full_Name” instead of default column heading.
21. Display salary + (salary*0.1) as “Salary_After_Hike”.
Section E – IN, NOT IN, BETWEEN, NOT BETWEEN
22. List employees whose department_id is either 10, 20, or 30.
23. Find employees whose job_id is either 'IT_PROG', 'SA_REP', or 'HR_REP'.
24. Show countries where region_id is in 1 or 2.
25. Retrieve departments located in locations except 1700 or 1800.
26. List employees whose salary is between 5000 and 10000.
27. Show departments whose department_id is between 50 and 100.
28. Retrieve jobs whose min_salary is NOT between 4000 and 6000.
Section F – LIKE Operator (with % and _)
29. List employees whose first_name starts with 'A'.
30. Find employees whose last_name ends with 'son.
31. Retrieve employees whose email has 'a' as the second character:
32. Find departments where department_name contains 'Sales
Section G – REGEXP_LIKE Operator
33. Find employees whose first name starts with a vowel (A, E, I, O, U).
34. List employees whose phone number contains exactly 3 consecutive digits (e.g., 123).
35. Retrieve employees whose email ends with '.com'.
36. Show jobs where job_id contains at least one numeric digit.
Section H – Single Row Functions
37. Get length of last name.
38. Calculate annual salary (monthly salary * 12) and named column as annual salary.
39. Write an SQL query to display the current system date and employee's hire date in 'DD-MON-YYYY'
format.
40. Write an SQL query to display first name and concatenate last name with a space in between.
41. Write an SQL query to round the salary to 0 decimal places.
42. Write an SQL query to display the first three characters of first name.
43. Write an SQL query to display employee_id and NVL(commission_pct, 0) as commission (use 0 if null).
44. Write an SQL query to display the last day of the month for hire_date.
45. Write an SQL query to display the next day after hire_date.
46. Write an SQL query to calculate the number of days between hire_date and SYSDATE.
47. Write an SQL query to extract the year, month and day from hire_date.