1. display the details of all employees.

select *
from employees;

2. display the depart information from department table.

select *
from departments;

3. display the name and job for all the employees.

select first_name||' '||last_name as name, job_id job

from employees;

4. display the name and salary for all employees.

select first_name||' '||last_name as name,salary

from employees;

5. display the employee no and total salary for all the employees.

select employee_id,salary+nvl(commission_pct,0) total_salaray

from employees;

6. display the employee name and annual salary for all the employees.

select first_name||' '||last_name as name,salary*12 annual_salary

from employees;

7. display the names of all employees who are working in depart number 10.

select first_name||' '||last_name as name

from employees
where department_id=10;

8. display the names of all employees who are working as clerks and drawing a
salary more than 3000.

select first_name||' '||last_name as name

from employees
where job_id like '%CLERK' and salary>3000;

9. display the employee number and name who are earning comm.

select employee_id,first_name||' '||last_name as name

from employees
where commission_pct IS NOT null;

10. display the employee number and name who do not earn any comm.

select employee_id,first_name||' '||last_name as name

from employees
where commission_pct IS null;

11) display the names of empoyees who are working as clerks, salesman or analyst
and drawing a salary more than 3000.
 select first_name||' '||last_name as name
from employees
where job_id IN ('CLERK','SALESMAN','ANALYST') and salary>3000;

12. display the names of the employees who are working in the company for the past
5 years.

select first_name||' '||last_name names

from employees
where trunc((sysdate-hire_date)/365)>5;

13) display the list of employees who have joined the company before 30_jun_90 or
after 31_dec_90.

select first_name||' '||last_name as name

from employees
where hire_date<TO_DATE('30-JUN-90','DD-MON-YY') OR hire_date>TO_DATE('31-DEC-
90','DD-MON-YY');

14. display current date.

select sysdate from dual;

15. display the list of all users in your database

select username from all_users;
USERNAME
------------------------------
XS$NULL
APEX_040000
APEX_PUBLIC_USER
FLOWS_FILES
HR
MDSYS
ANONYMOUS
XDB
CTXSYS
APPQOSSYS
DBSNMP
ORACLE_OCM
DIP
OUTLN
SYSTEM
SYS

16. display the names of all tables from current user.

select tname from tab;

17. display the name of the current user.

show user;
o/p
USER is "HR"

18) display the names of employees working in dept number 10 or 20 or 40 or

employees working as clerks,salesman or analyst.
select first_name||' '||last_name name
 from employees
where department_id IN (10,20,40) OR job_id IN ('CLERK','SALESMAN','ANALYST');

19. display the names of employees whose name starts with alphabet s.

select first_name||' '||last_name name

from employees
where first_name Like 'S%';
O/P

20. display the employee names for employees whose name ends with alphabet S.
select first_name||' '||last_name name
from employees
where first_name||' '||last_name Like '%s';
O/P

21. display the names of employees whose names have second alphabet A in their
names.
select first_name||' '||last_name name
from employees
where first_name||' '||last_name Like '_a%';

22. select the names of the employee whose names is exactly five characters in
length.
select first_name||' '||last_name name
from employees
where first_name||' '||last_name Like '_____';
(or)
select first_name||' '||last_name name
from employees
where length(first_name||' '||last_name)=5;
(or)
select last_name name
from employees
where length(last_name)=5;

23. display the names of the employees who are not working as MANAGERS.
select first_name||' '||last_name name
from employees
where job_id!='MANAGER';

24. display the names of the employee who are not working as salesman or clerk or
analyst.
select first_name||' '||last_name name
from employees
where job_id NOT IN ('CLERK','SALESMAN','ANALYST');

25. display all rows from emp table.the system should wait after every screen full
of information

set pause on

select *
from emp;
26. display the total number of employees working in the company.
select count(employee_id) no_employees
from employees;

27. display the total salary being paid to all employees.

select first_name||' '||last_name name,salary+nvl(COMMISSION_PCT,0)
total_salary
from employees;

(or)
select sum(salary)
from employees;

28. display the maximum salary from emp table.

select max(salary) max_salary
from employees;

29. display the minimum salary from emp table.

select min(salary) minimum_sal
from employees;

30. display the average salary from emp table.

select avg(salary) average_sal
from employees;

31. display the maximum salary being paid to clerk.

select max(salary) max_salary
from employees
where job_id like '%CLERK';

32. Display the maximum salary being paid to depart number 20.
select max(salary) max_salary
from employees
where department_id=20;

33. display the minimum salary being paid to any salesman.

select min(salary) minimum_sal
from employees
where job='SALESMAN';

34.display the average salary drawn by MANAGERS.

select avg(salary) average_sal
from employees
where jonb_id='MANAGER';

35.display the toatlsalary drawn by analyst working in dept number 40.

select sum(salary)
from employees
where department_id=40 and job_id='ANALYST';

36. DISPLAY the names of the employees in order of salary i.e..the name of the
employee earning lowest salary should appear first.
select first_name||' '||last_name names
from employees
order by salary asc;
37. display the names of the employee in descending order of salary.
select first_name||' '||last_name names,salary
from employees
order by salary desc;

38. display the names of the employee in order of employee name.

select first_name||' '||last_name names
from employees
order by names;

39. display empno, ename,deptno, sal sort the output first be on name and within
name by deptno and within deptno by sal.
select empno,ename,deptno,sal
from emp
order by ename,deptno,sal;

40. display the name of the employee along with their annual salary(sal*12).the
name of the employee earning highest annual salary should apper first.
select first_name||' '||last_name names, salary*12 annual_salary
from employees
order by annual_salary desc;

41. display name,salary,hra,pf,da,total salary for each employee. the output should
be in the order of total salary, hra 15% of salary, da 10% of salary, pf salary,
total salary will be(salary_hra_da)-pf.
select ename,sal,(sal+hra+da)-pf total_salary,sal*0.15 hra,sal*0.1 da,sal*0.5
pf
from emp;

42. display department numbers and total number of employees working in each
department.
select department_id, count(employee_id) number_of_emp
from employees
group by department_id;

43. display the various jobs and total number of employees with each job group.
select job_id,count(employee_id) number_of_emp
from employees
group by job_id;

44.display the depart numbers and total salary for each department.
select department_id,sum(salary) total_salary
from employees
group by department_id;

45. display the depart numbers and max salary for each department.
select department_id,max(salary)
from employees
group by department_id;

46. display the various jobs and total salary for each job.

select job_id,sum(salary) total_salary

from employees
 group by job_id;

48. display the depart numbers with more than three employees in each dept.
select department_id,count(employee_id) number_of_emp
from employees
group by department_id
having count(employee_id)>3;

49. display the various job along with total salary for each of this jobs where
total salary is greater than 40000.

select job_id,sum(salary) total_salary

from employees
group by job_id
having sum(salary)>40000;

50. display the various jobs along with total number of employees in each job. the
output should contain only those jobs with more than three employees.

select job_id,count(employee_id)s
from employees
group by job_id
having count(employee_id)>3;

51) search for the employees with the pattern 'A_B' in their names.
select first_name||' '||last_name names
from employees
where first_name like 'A_b%';

52. find the first occurence of character 'a' from the following string i.e
'computer Maintaince Corporation'.
select INSTR ('Computer Maintaince Corporation','a') from dual;

53. display the information from emp table. where job manager is found it should be
displayed boss(use replace function).

Update emp set JOB='BOSS' WHERE job='MANAGER';

54. display the names of the employees in uppercase.

select upper(first_name||' '||last_name) names
from employees;

55. display the names of the employees in lowercase.

select lower(first_name||' '||last_name) names
from employees;

56. display the names of the employees in proper case.

select INITCAP(first_name||' '||last_name) names
from employees;
57. display the length of your name using appropriate function.
select length('sumanth') FROM DUAL;

58. display the length of all employees names.

select first_name||' '||last_name names, length(first_name||' '||last_name)
length
from employees;

59. select the name of the employee concatenate with employee number.
select first_name||' '||last_name||employee_id names_empid
from employees;

60. use aapropriate function and extract 3 characters starting from 2 characters
from the following string 'oracle.i.e. the output should be 'rac'.

select substr('oracle',2,instr('racle','c')) from dual;

61. display empno, ename, deptno from emp table. instead of display department
numbers display the related department name(use decode function).

select
e.empno,e.ename,decode(e.deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERAT
IONS') DNAMES
from emp e;

62. Display the current date as 15th august friday nineteen ninety seven.
Select to_char(sysdate,'YYYYSP') from dual;

select to_date('15-aug-1977','dd-mon-yyyy') todays_date from dual;

63. display the following output for each row from emp table.
for ex:
scott has joined the company on wednesday 13th august nineteen ninety.
sham hass joined the company on wednesday 13th august nineteen ninety.

select ename||' '||'has joined the company on'||' '||to_char(hiredate,'day')||'

'||to_char(hiredate,'DD')||' '||to_char(hiredate,'MONTH')||' '||
to_char(hiredate,'YYSP') from emp;

64. find the date for nearest saturday after current date.
select next_day(sysdate,'SAT') nearest_sat from dual;

65. display the date three months before the current date.

select add_months(sysdate,-3) thrre_mon_before from dual;

66. select ename from emp where sal =(select max(sal) from emp);

67. select emp_no,ename from emp where SAL = (SELECT MAX(SAL) FROM EMP WHERE
JOB='CLERK');

68. SELECT ENAME FROM EMP WHERE JOB = 'SALESMAN' AND SAL>(SELECT MAX(SAL) FROM EMP
WHERE JOB='CLERK');

69. SELECT ENAME FROM EMP WHERE JOB='CLERK' AND SAL>ANY(SELECT SAL FROM EMP WHERE
JOB='SALESMAN');

70. SELECT ENAME FROM EMP WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME='JONES') OR
SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT');

71. SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

72. SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB;

73. SELECT ENAME FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC
='CHICAGO');

74. SELECT JOB FROM EMP WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE JOB='MANAGER')
GROUP BY JOB;

75. SELECT ENAME FROM EMP WHERE DEPTNO=10 AND SAL>ANY(SELECT SAL FROM EMP WHERE
DEPTNO IN('20','30'));

76. SELECT ENAME FROM EMP WHERE DEPTNO=10 AND SAL>ALL(SELECT SAL FROM EMP WHERE
DEPTNO IN('20','30'));

77. SELECT JOB FROM EMP WHERE DEPTNO = 10 AND JOB IN(SELECT JOB FROM EMP WHERE
DEPTNO=20);

78. SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 10 AND JOB IN(SELECT JOB FROM EMP
WHERE DEPTNO=20);

79. SELECT JOB FROM EMP WHERE DEPTNO=10 AND JOB NOT IN(SELECT JOB FROM EMP WHERE
DEPTNO IN('20','30'));

80. SELECT ENAME FROM EMP WHERE MGR IS NULL;

81. select e.ename,d.dname,grade

from emp e,dept d ,salgrade
where e.deptno=d.deptno and dname='SALES' and grade=3;

82. update emp set sal=sal+ 0.1*sal where comm is null;

83. select ename from emp where hiredate<'31-dec-82' and deptno =(select deptno
from dept where loc in('NEWYORK','CHICAGO'));

84. select ename,job,dname,loc from emp,dept where emp.deptno=dept.deptno and job =

'MANAGER';

85. SELECT e.ename employee,m.ename manager from emp e,emp m where e.mgr=m.emp_no
and m.ename='JONES';

86. select ename,sal,grade from emp,salgrade where sal in hisal and ename ='FORD'
AND HISAL=SAL;

87. SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP E,SALGRADE,DEPT

WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR AND
EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME;
88. select ename,job,sal,grade,dname from emp,salgrade,dept
where emp.sal between losal and hisal and emp.deptno=dept.deptno and job not
in('CLERK') order by sal;

89. select e.ename,e.job,m.ename manager from emp e,emp m where e.mgr=m.empno;

90. SELECT ENAME FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);

91. select * from emp where sal= (select (max(sal)+min(sal))/2 from emp);

92. select count(*) from emp group by deptno having count(*) > 3;

93. SELECT DISTINCT EMP.ENAME,emp.sal FROM EMP,EMP E

WHERE E.SAL <(SELECT AVG(EMP.SAL) FROM EMP WHERE EMP.EMPNO=E.MGR GROUP BY
EMP.ENAME) AND EMP.EMPNO=E.MGR;

94. SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) netpay FROM EMP WHERE sal+NVL(COMM,0)

>=ALL(SELECT SAL FROM EMP);

95. select count(*) from emp e,emp m where e.mgr=m.empno and e.sal>m.sal;

96. select dname,ename from emp,dept where emp.deptno(+) = dept.deptno and ename is
null;

97. select ename,sal from emp where sal like '___%';

98. select ename from emp where hiredate like '___DEC%' ;

OR
select ename from emp where hiredate like '%DEC%';

99. SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';

100. SELECT ENAME FROM EMP WHERE 0.1*SAL = TO_CHAR(HIREDATE,'YY');

101. select * from emp where deptno =ANY(select deptno from DEPT where dname
in('SALES','RESEARCH'));

102. SELECT ENAME,TO_CHAR(HIREDATE,'DD') FROM EMP WHERE

TO_CHAR(HIREDATE,'DD')<'15';

103. delete from emp where deptno in (select deptno from emp group by deptno having
count (*)>3);

104. select dname,ename from emp,dept where emp.deptno(+) = dept.deptno and ename
is null;

105. select ename from emp where job='MANAGER';

106. SELECT * FROM EMP E,EMP M WHERE e.mgr=m.emp_no and m.ename='BLAKE';

107. select * from emp where rownum<11 minus select * from emp where rownum<10;

108. DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11
MINUS
SELECT EMPNO FROM EMP WHERE ROWNUM<10);

109. create table emp(

emp_no number(4) constraint empinfo_emp_no_pk primary key,
 ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2));

110. select ename from emp group by ename having count(*)>1;

111. SELECT REVERSE(ENAME) FROM EMP;

112. SELECT ename FROM emp WHERE TO_CHAR(hiredate,'DD')=deptno;

113. select substr (ename, 1, 1) ||' '||ename from emp;

114. DELETE FROM emp WHERE (sysdate-hiredate)/365>2;

note : we can get the deleted rows by this statemnt "insert into emp
(select * from emp as of timestamp to_timestamp('2022-07-24','yyyy-mm-dd
hh:mi:ss'));"

115. update emp set sal=sal+0.1*sal where comm is null;

116. update emp set comm=comm+0.1*sal where comm is not null;

117. select ename,emp_no,loc from emp,dept where emp.deptno=dept.deptno;

118. select ename,dname from emp,dept where emp.deptno(+)=dept.deptno;

119. select e.ename,m.ename as manager from emp e,emp m where e.mgr=m.emp_no;

120. Delete from emp where rowid not in(select min(rowid)from emp group by ename) ;

121. select * from emp where rownum<8 minus select * from emp where rownum<5;

122. select * from emp where rownum<11;

123. select * from (select * from emp order by sal desc) where rownum <4 ;

124. SELECT ename,deptno,sal FROM emp WHERE sal> (SELECT MAX(AVG(sal)) FROM emp
GROUP BY deptno );

125. create table emp( empno number(4));

126. create table emp( ename varchar2(20));

127. alter table table_name add constraint constraint_name

constraint_type(col_name);

128. alter table emp modify ename varchar2(30);

129. alter table emp add (sal number(5));

130. alter table emp add constraint emp_sal_ck check(sal<=10000);

131. alter table emp

drop constraint emp_sal_ck;
132. alter table table name disable constraint emp_sal_uk;

133. alter table emp

add (mgr number(4));

134. alter table emp

add constraint emp_mgr_fk foriegn key(mgr)
references emp(emp_no);

135. alter table emp add (deptno number(2));

136. alter table emp

add constraint emp_mgr_fk foriegn key(deptno)
references dept(deptno);

137. alter table table_name

add constraint constraint_name constraint_type(col);

138. create table newemp as(select * from emp);

139. create table newemp( empno number(4),ename varchar2(20),deptno number(4));

140. create view emp_vu as select first_name,last_name,job_id,department_id from

employees;

141. for this ques i am creating a table

create table my_emp(name varchar(20),num number(2),loc varchar2(20));

now creating view from this

create or replace view my_empvu as select name,loc from my_emp;

insert into my_empvu values('sandeep','penumaka');

update my_empvu set loc='narsaraopeta' where name='uma';

delete from emp where name='sandeep';

142. create view dept_vu as select department_id dept_id,department_name

dname,manager_id as mgr,location_id as loc from departments
where department_name = 'Sales' with check option;

143. create view dept_vu as select department_id dept_id,department_name

dname,manager_id as mgr,location_id as loc from departments
with read only;

144. create view empvu20 as SELECT DISTINCT EMP.ENAME,emp.sal FROM EMP,EMP E

WHERE E.SAL <(SELECT AVG(EMP.SAL) FROM EMP WHERE EMP.EMPNO=E.MGR GROUP BY
EMP.ENAME) AND EMP.EMPNO=E.MGR;

145. create sequence someone_num_sgen

start with 1
increment by 1
maxvalue 100
nocycle
nocache;
146. insert into someone values(someone_num_sgen.nextval,'&name');

147. create index mgr_ix on emp(mgr);

148.create user user_name identified by password;

Create role role_name ;
Grant privileges to role_name;

149.revoke privileges on object from user;

150.select * from user_tables;