DBMS LAB PRACTICAL
NAME: Sakshi Prajapat
ROLL NO: 2300290120202
1.Display employees with salary > 40000 AND department_id = 100.
2. Retrieve rst_name, salary where job_id = ‘IT_PROG’ OR job_id = ‘SA_REP’.
3. Show employees whose department_id is NOT 50.
fi
4. Display employee_id, rst_name where hire_date > '01-JAN-2018' AND
salary < 60000.
5. List employees where job_id = ‘FI_ACCOUNT’ OR department_id = 90
fi
6. Display employees with salary between 40000 and 60000 using rela onal
operators.
ANS : no data found
7. Retrieve employees whose salary > 50000 AND commission_pct IS NOT
NULL.
ANS: no data found
8. List rst_name, job_id where NOT(job_id = 'HR_REP').
fi
ti
9. Show employees who earn less than 30000 OR were hired a er 2021.
10. Display employee_id, salary, annual salary (salary*12) only for employees in
department 100 AND salary >= 70000.
ANS: no data found
11. Display employee full name by concatena ng rst_name and last_name as
“Full_Name”.
ti
fi
ft
12. SELECT rst_name || ' ' || last_name AS Full_Name FROM employees;
13. Show employee email addresses as email || '@company.com'.
14. Display “Employee: works as ” for all employees.
ANS: SELECT 'Employee: ' || rst_name || ' works as ' || job_id AS
employee_info
FROM employees;
fi
fi
15. List employee_id along with a string “Salary: ” concatenated with salary.
16. Concatenate rst_name and hire_date into a single column
“Name_HireDate”.
ANS::SELECT rst_name || ' - ' || hire_date AS Name_HireDate
FROM employees;
17. Display employee_id AS “ID”, rst_name AS “EmployeeName”, salary AS
“MonthlySalary”.
ANS:: SELECT employee_id AS ID, rst_name AS EmployeeName, salary AS
MonthlySalary FROM employees;
18. Show annual salary (salary * 12) as “Annual_Salary”.
ANS:: SELECT (salary * 12) AS Annual_Salary FROM employees;
19. Display commission_pct as “BonusRate”.
ANS::SELECT commission_pct AS BonusRate FROM employees;
20. Show concatenated name as “Full_Name” instead of default column
heading.
ANS :: SELECT rst_name || ' ' || last_name AS Full_Name FROM employees;
21. Display salary + (salary*0.1) as “Salary_A er_Hike”.
fi
fi
fi
fi
fi
ft
ANS::SELECT employee_id,salary,salary + (salary * 0.1) AS Salary_A er_Hike
FROM employees;
22. List employees whose department_id is either 10, 20, or 30.
SELECT employee_id, rst_name,last_name,department_id FROM employees
WHERE department_id IN (10, 20, 30);
23. Find employees whose job_id is either 'IT_PROG', 'SA_REP', or 'HR_REP'.
SELECT employee_id, rst_name last_name,job_id FROM employees WHERE
job_id IN ('IT_PROG', 'SA_REP', 'HR_REP');
24. Show countries where region_id is in 1 or 2.
SELECT country_id, country_name, region_id FROM countries
WHERE region_id IN (1, 2);
25. Retrieve departments located in loca ons except 1700 or 1800.
SELECT department_id, department_name, loca on_id FROM departments
WHERE loca on_id NOT IN (1700, 1800);
26. List employees whose salary is between 5000 and 10000.
SELECT employee_id, rst_name, last_name, salary FROM employees
WHERE salary BETWEEN 5000 AND 10000;
27. Show departments whose department_id is between 50 and 100.
SELECT department_id, department_name, loca on_id FROM departments
WHERE department_id BETWEEN 50 AND 100;
28. Retrieve jobs whose min_salary is NOT between 4000 and 6000.
SELECT job_id,
job_ tle,
min_salary,
max_salary
FROM jobs
WHERE min_salary NOT BETWEEN 4000 AND 6000;
ti
ti
fi
fi
fi
ti
ti
ti
ft
29. List employees whose rst_name starts with 'A'.
30. Find employees whose last_name ends with 'son.
31. Retrieve employees whose email has 'a' as the second character:
ANS: No data found
32. Find departments where department_name contains 'Sales
SELECT department_id,
department_name,
fi
location_id
FROM departments
WHERE department_name LIKE '%Sales%';
33. Find employees whose rst name starts with a vowel (A, E, I, O, U).
34. List employees whose phone number contains exactly 3 consecu ve digits
(e.g., 123).
35. Retrieve employees whose email ends with '.com'.
fi
ti
ANS:: no data found
36. Show jobs where job_id contains at least one numeric digit.
SELECT job_id, job_ tle, min_salary, max_salary FROM jobs WHERE
REGEXP_LIKE(job_id, '[0-9]');
37. Get length of last name.
38. Calculate annual salary (monthly salary * 12) and named column as annual
salary.
ti
39. Write an SQL query to display the current system date and employee's hire
date in 'DD-MON-YYYY' format.
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS current_date,
TO_CHAR(hire_date, 'DD-MON-YYYY') AS hire_date
FROM employees;
40. Write an SQL query to display rst name and concatenate last name with a
space in between. 41. Write an SQL query to round the salary to 0 decimal
places.
fi
42. Write an SQL query to display the rst three characters of rst name.
43. Write an SQL query to display employee_id and NVL(commission_pct, 0) as
commission (use 0 if null).
fi
fi
’’
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 a er hire_date.
ft
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.
