Assignment - 1
Assignment - 1
Assignment - 1
Desc employees;
2. Create a query to display Ename as employee_name and EmpNo as
employee_number from employees. Select ename employee name,empno employee number From employees;
3. Create a query to display the Ename and Sal from employees whose has salary grater
between 7000 and 19000. Select ename , sal From employees Where sal between 7000 and 19000;
5. Create a query to display the Ename and Sal from employees whose salary is not
between 10000 and 20000. Select ename ,sal From employees Where sal not between 10000 and 20000;
6. Create a query to display employees who are hired between 1975 and 2000.
select *from employees where ename like %a% or ename like %i% or ename like %o% or ename like %e% or ename like %u%;
11. create a query to display employees details who work in department number 20.
14. Create a query to display employees details concatenated by his name and salary.
space, and name the column Employee and Title. Select last_name||,|| ||emp_id employee and title From employees;
18. Create a query to display all the data from the EMPLOYEES table. Separate each
column by a comma. Name the column THE_OUTPUT. Select emp_id||,||ename||,||last_name||,||deptno||,||sal||,||commission||,|| mgr||,||job the_output From employees;
19. Display the last name and department number of all employees not in departments
20 and 50 in alphabetical order by name. Select last_name, deptno From employees Where deptno not in(20,50) Order by ename;
20. Display the last name and job title of all employees who do not have a manager.
21. Display the last name, salary, and commission for all employees who earn
commissions. Sort data in descending order of salary and commissions. Select last_name, sal, commission From employees Where commission is not null Order by sal, commissions desc;
22. Display the last name, job, and salary for all employees whose job is sales
representative or stock clerk and whose salary is not equal to 2,500, 3,500, or 7,000. Select last_name, job, sal From employees Where job in (sales ,clerk) and sal not in(2500,3500,7000);
23. Write a query to display the current date. Label the column Date.
increased by 15% and expressed as a whole number. Label the column New Salary. Select emp_no, last_name, sal, round(sal+(sal*15/100)) new salary From employees;
25. Modify your above query to add a column that subtracts the old salary from the new
salary. Label the column Increase. Select emp_no, last_name, sal, round(sal+(sal*15/100)) new salary, (round(sal+(sal*15/100)))-sal increase From employees; Assignment 1
2. Create a query to display all columns from employees.
Desc employees;
3. Create a query to display Ename as employee_name and EmpNo as
between 7000 and 19000. Select ename , sal From employees Where sal between 7000 and 19000;
6. Create a query to display the Ename and Sal from employees whose salary is not
between 10000 and 20000. Select ename ,sal From employees Where sal not between 10000 and 20000;
7. Create a query to display employees who are hired between 1975 and 2000.
10. Create a query to display employees details who has more than 2 a in his name.
select *from employees where ename like %a% or ename like %i% or ename like %o% or ename like %e% or ename like %u%;
12. create a query to display employees details who work in department number 20.
17. Create a query to display unique job codes from the EMPLOYEES table.
space, and name the column Employee and Title. Select last_name||,|| ||emp_id employee and title From employees;
19. Create a query to display all the data from the EMPLOYEES table. Separate each
column by a comma. Name the column THE_OUTPUT. Select emp_id||,||ename||,||last_name||,||deptno||,||sal||,||commission||,|| mgr||,||job the_output From employees;
20. Display the last name and department number of all employees not in departments
20 and 50 in alphabetical order by name. Select last_name, deptno From employees Where deptno not in(20,50) Order by ename;
21. Display the last name and job title of all employees who do not have a manager.
commissions. Sort data in descending order of salary and commissions. Select last_name, sal, commission From employees Where commission is not null
representative or stock clerk and whose salary is not equal to 2,500, 3,500, or 7,000. Select last_name, job, sal From employees Where job in(sales ,clerk) and sal not in(2500,3500,7000);
24. Write a query to display the current date. Label the column Date.
increased by 15% and expressed as a whole number. Label the column New Salary. Select emp_no, last_name, sal, round(sal+(sal*15/100)) new salary From employees;
26. Modify your above query to add a column that subtracts the old salary from the new
salary. Label the column Increase. Select emp_no, last_name, sal, round(sal+(sal*15/100)) new salary, (round(sal+(sal*15/100)))-sal increase From employees;
26. Write a query that displays the employees last names with the first letter
capitalized and all other letters lowercase and the length of the name for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees last names. Select initcap(first_name),length(first_name) From employees Where first_name like j% or first_name like a% or first_name like m% Order by first_name;
27. For each employee, display the employees last name, and calculate the number of
months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.
Select last_name, round(months_between( sysdate,hire_date))"months worked" from employees order by "months worked";
28. Create a query to display the last name and salary for all employees. Format the
salary to be 15 characters long, left-padded with $. Label the column SALARY. Select ename,lpad(sal,15,$) salary From employees;
29. Display each employees last name, hire date, and salary review date, which is the
first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to Monday, the Thirty-First of July, 2000. Select first_name, to_char(hire_date,'day", the "ddspth" of "month","yyyy'),to_char(next_day(add_months(to_date(hire_date),6),'mon'),'day" , the "ddspth" of "month","yyyy') review from employees;
30. Display the last_name, hire_date, and day of the week on which the employee
started. Label the column DAY. Order the results by the day of the week starting with Monday. Select last_name, hire_date, to_char(hire_date,day) day From employees Order by to_char(hire_date,d);