1) Display the details of all employees

SQL>Select * from emp;

2) Display the depart information from department table

SQL>select * from dept;

3) Display the name and job for all theemployees

SQL>select ename,job from emp;

4) Display the name and salary  for all the employees

SQL>select ename,sal from emp;

5) Display the employee no and totalsalary  for all the employees

SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as”total  salary” from emp;

6) Display the employee name and annual salary for all employees.

SQL>select ename, 12*(sal+nvl(comm,0)) as “annual Sal” from emp;

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

SQL>select emame from emp where deptno=10;

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

SQL>select ename from emp where job=’CLERK’ and sal>3000;

9) Display the employee number and name  who are earning comm.

SQL>select empno,ename from emp where comm is not null;

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

SQL>select empno,ename from emp where comm is null;

11) Display the names of employees who are working as clerks,salesman or analyst and drawing a salary more than 3000.

SQL>select ename  from emp where job=’CLERK’ OR JOB=’SALESMAN’ OR JOB=’ANALYST’ AND SAL>3000;

12) Display the names of the employees who are working in the company for the past 5 years;

SQL>select ename  from emp where to_char(sysdate,’YYYY’)-to_char(hiredate,’YYYY’)>=5;

13) Display the list of employees who have joined the company before 30-JUN-90 or after 31-DEC-90.

SQl> select ename from emp where hiredate < ’30-JUN-1990′ or hiredate >’31-DEC-90′;

14) Display current Date.

SQL>select sysdate from dual;

15) Display the list of all users in your database(use catalog table).

SQL>select username from all_users;

16) Display the names of all tables from current user;

SQL>select tname from tab;

17) Display the name of the current user.

SQL>show user;

18) Display the names of employees working in depart number 10 or 20 or 40 or employees working asCLERKS,SALESMAN or ANALYST.

SQL>select ename from emp where deptno in(10,20,40) or job in(‘CLERKS’,'SALESMAN’,'ANALYST’);

19) Display the names of employees whose name starts with alaphabet S.

SQL>select ename from emp where ename like ‘S%’;

20) Display the Employee names for employees whose name ends with alaphabet S.

SQL>select ename from emp where ename like ‘%S’;

21) Display the names of employees whose names have second alphabet A in their names.

SQL>select ename from emp where ename like ‘_A%’;

22) select the names of the employee whose names is exactly five characters in length.

SQL>select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL>select ename from emp where job not in(‘MANAGER’);

24) Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.

SQL>select ename from emp where job not in(‘SALESMAN’,'CLERK’,'ANALYST’);

25) Display all rows from emp table.The system should wait after every screen full of informaction.

SQL>set pause on;

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job=’CLERK’;

32) Display the maximum salary being paid to depart number 20.

SQL>select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job=’SALESMAN’;

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job=’MANAGER’;

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job=’ANALYST’ and deptno=40;

36) Display the names of the employee in order of salary i.e the name of the employee earning lowest salary    should salary appear first.

SQL>select ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

SQL>select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

SQL>select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by;

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.

SQL>select ename,sal*12 from emp order by sal 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 5% salary,total salary will be(salary+hra+da)-pf.

SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;

42) Display depart numbers and total number of employees working in each department.

SQL>select deptno,count(deptno)from emp group by deptno;

43) Display the various jobs and total number of employees within each job group.

SQL>select job,count(job)from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

46) Display the various jobs and total salary for each job

SQL>select job,sum(sal) from emp group by job;

47) Display the various jobs and total salary for each job

SQL>select job,min(sal) from emp group by job;

48) Display the depart numbers with more than three employees in each dept.

SQL>select deptno,count(deptno) from emp group by deptno having count(*)>3;

49) Display the various jobs along with total salary for each of the jobs where total salary is greater than 40000.

SQL>select job,sum(sal) from emp group by job having sum(sal)>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.

SQL>select job,count(empno) from emp group by job having count(job)>3;

51) Display the name of the empployee who earns highest salary.

SQL>select ename from emp where sal=(select max(sal) from emp);

52) Display the employee number and name for employee working as clerk and earning highest salary among clerks.

SQL>select empno,ename from emp where where job=’CLERK’ and sal=(select max(sal) from emp  where job=’CLERK’);

53) Display the names of salesman who earns a salary more than the highest salary of any clerk.

SQL>select ename,sal from emp where job=’SALESMAN’ and sal>(select max(sal) from emp where job=’CLERK’);

54) Display the names of clerks who earn a salary more than the lowest salary of any salesman.

SQL>select ename from emp where job=’CLERK’ and sal>(select min(sal) from emp where job=’SALESMAN’);

55) Display the names of the employees who earn highest salary in their respective departments.

SQL>select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);

56) Display the names of the employees who earn highest salaries in their respective job groups.

SQL>select ename,sal,job from emp where sal in(select max(sal) from emp group by job);

57) Display the employee names who are working in accounting department.

SQL>select ename from emp where deptno=(select deptno from dept wheredname=’ACCOUNTING’);

58) Display the employee names who are working in Ahmedabad .

SQL>select ename from emp where deptno=(select deptno from dept where LOC=’Ahmedabad’);

59) Display the Job groups having total salary greater than the maximum salary for managers.

SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM EMP WHERE JOB=’MANAGER’);

60) Display the names of employees from department number 10 with salary grether than that of any employee working in other department.

SQL>select ename from emp where deptno=10 and sal>any(select sal from emp where deptno not in 10);

61) Display the names of the employees from department number 10 with salary greater than that of all employee working in other departments.

SQL>select ename from emp where deptno=10 and sal>all(select sal from emp where deptno not in 10);

62) Display the names of the employees in Uppercase.

SQL>select upper(ename)from emp;

63) Display the names of the employees in Lowecase.

SQL>select lower(ename)from emp;

64) Display the names of the employees in Propercase.

SQL>select initcap(ename)from emp;

65) Display the length of Your name using appropriate function.

SQL>select length(‘name’) from dual;

66) Display the length of all the employee names.

SQL>select length(ename) from emp;

67) select name of the employee concatenate with employee number.

SQL>select ename||empno from emp;

68) User appropriate function and extract 3 characters starting from 2 characters from the following  string ‘Oracle’. i.e the out put should be ‘ac’.

SQL>select substr(‘oracle’,3,2) from dual;

69) Find the First occurance of character ‘a’ from the following string i.e ‘Computer Maintenance Corporation’.

SQL>SELECT INSTR(‘Computer Maintenance Corporation’,'a’,1) FROM DUAL;

70) Replace every occurance of alphabhet A with B in the string Allens(use translate function)

SQL>select translate(‘Allens’,'A’,'B’) from dual;

71) Display the informaction from emp table.Where job manager is found it should be displayed as boos(Use replace function).

SQL>select replace(JOB,’MANAGER’,'BOSS’) FROM EMP;

72) Display empno,ename,deptno from emp table.Instead of display department numbers display the related department name(Use decode function).

SQL>select empno,ename,decode(deptno,10,’ACCOUNTING’,20,’RESEARCH’,30,’SALES’,40,’OPRATIONS’) from emp;

73) Display your age in days.

SQL>select to_date(sysdate)-to_date(’10-sep-77′)from dual;

74) Display your age in months.

SQL>select months_between(sysdate,’10-sep-77′) from dual;

75) Display the current date as 15th Augest Friday Nineteen Ninety Saven.

SQL>select to_char(sysdate,’ddth Month day year’) from dual;

76) Display the following output for each row from emp table. A has joined the company on wednesday 13th August ninten nintey.

SQL>select ENAME||’ HAS JOINED THE COMPANY ON  ‘||to_char(HIREDATE,’day ddth Month  year’)   from EMP;

77) Find the date for nearest saturday after current date.

SQL>SELECT NEXT_DAY(SYSDATE,’SATURDAY’)FROM DUAL;

78) Display current time.

SQL>select to_char(sysdate,’hh:MM:ss’) from dual.

79) Display the date three months Before the current date.

SQL>select add_months(sysdate,3) from dual;

80) Display the common jobs from department number 10 and 20.

SQL>select job from emp where deptno=10 and job in(select job from emp where deptno=20);

81)Display the names of employees who earn a salary more than that of A or that of salary grether than   that of B.

SQL>select ename,sal from emp where sal> (select sal from emp where ename=’A')and sal> (select sal from emp where ename=’B');

81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

SQL>select distinct(job) from emp where deptno=10 or deptno=20;

(or)

SQL>select distinct(job) from emp where deptno in(10,20);

82) Display the jobs which are unique to department 10.

SQL>select distinct(job) from emp where deptno=10;

83) Display the details of those who do not have any person working under them.

SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename havingcount(*)=1;

84) Display the details of those employees who are in sales department and grade is 3.

SQL>select * from emp where deptno=(select deptno from dept where dname=’SALES’)and sal between(select losal from salgrade where grade=3)and (select hisal from salgrade where grade=3);

85) Display those who are not managers and who are managers any one.

i)display the managers names

SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;

ii)display the who are not managers

SQL>select ename from emp where ename not in(select distinct(m.ename) from emp e,emp m where m.empno=e.mgr);

86) Display those employee whose name contains not less than 4 characters.

SQL>select ename from emp where length(ename)>4;

87) Display those department whose name start with “S” while the location name ends with “K”.

SQL>select dname from dept where dname like ‘S%’ and loc like ‘%K’;

88) Display those employees whose manager name is akash.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and e.ename=’Akash’;

89) Display those employees whose salary is more than 3000 after giving 20% increment.

SQL>select ename,sal from emp where (sal+sal*.2)>3000;

90) Display all employees while their dept names;

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno;

91) Display ename who are working in sales dept.

SQL>select ename from emp where deptno=(select deptno from dept where dname=’SALES’);

92) Display employee name,deptname,salary and comm for those sal in between 2000 to 5000 while location is Ahmedabad .

SQL>select ename,dname,sal,comm from emp,dept where sal  between 2000 and 5000 and loc=’AHMEDABAD’ and emp.deptno=dept.deptno;

93)Display those employees whose salary greter than his manager salary.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal;

94) Display those employees who are working in the same dept where his manager is work.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.deptno=e.deptno;

95) Display those employees who are not working under any manager.

SQL>select ename from emp where mgr is null;

96) Display grade and employees name for the dept no 10 or 30 but grade is not 4 while joined the company before 31-dec-82.

SQL>select ename,grade from emp,salgrade where sal between losal and hisal and deptno     in(10,30) and grade<>4 and hiredate<’31-DEC-82′;

97) Update the salary of each employee by 10% increment who are not eligiblw for commission.

SQL>update emp set sal=sal+sal*10/100 where comm is null;

98) SELECT those employee who joined the company before 31-dec-82 while their dept location is newyork or  Chicago.

SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND HIREDATE <’31-DEC-82′ AND DEPT.LOC IN(‘CHICAGO’,'NEW YORK’);

99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING AS  MANAGER?

SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not null;

100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS AKKI? –[AND ALSO DISPLAY THEIR MANAGER NAME]?

SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND E.ENAME=’AKKI’;

101) Display name and salary of ford if his salary is equal to hisal of his grade

SQL>select ename,sal,grade from emp,salgrade where sal between losal and hisal and ename =’FORD’ AND HISAL=SAL;

102) Display employee name,job,depart name ,manager name,his grade and make out an under department wise?

SQL>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;

103) List out all employees name,job,salary,grade and depart name for every one in the company  except ‘CLERK’.Sort on salary display the highest salary?

SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB NOT IN(‘CLERK’)ORDER BY SAL ASC;

104) Display the employee name,job and his manager.Display also employee who are without manager?

SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where emp.empno(+)=e.mgr;

105) Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;

106) Display name of those employee who are getting the highest salary?

SQL>select ename from emp where sal=(select max(sal) from emp);

107) Display those employee whose salary is equal to average of maximum and minimum?

SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from emp);

108) Select count of employee in each department  where count greater than 3?

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

109) Display dname where at least 3 are working and display only department name?

SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno and 3>any (selectcount(deptno) from emp group by deptno);

110) Display name of those managers name whose salary is more than average salary of his company?

SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);

111)Display those managers name whose salary is more than average salary of his employee?

SQL>SELECT DISTINCT EMP.ENAME 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;

112) Display employee name,sal,comm and net pay for those employee whose net pay is greter than or equal to any other employee salary of the company?

SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp where sal+nvl(comm,0) >any (select sal from emp);

113) Display all employees names with total sal of company with each employee name?

SQL>SELECT ENAME,(SELECT SUM(SAL)  FROM EMP) FROM EMP;

114) Find out last 5(least)earners of the company.?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE

5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL<=E.SAL)

ORDER BY SAL DESC;

115) Find out the number of employees whose salary is greater than their manager salary?

SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR AND EMP.SAL<E.SAL;

116) Display those department where no employee working?

SQL>select dname from emp,dept where emp.deptno not in(emp.deptno);

117) Display those employee whose salary is ODD value?

SQL>select * from emp where sal<0;

118) Display those employee whose salary contains alleast 3 digits?

SQL>select * from emp where length(sal)>=3;

119) Display those employee who joined in the company in the month of Dec?

SQL>select ename from emp where to_char(hiredate,’MON’)=’DEC’;

120) Display those employees whose name contains “A”?

SQL>select ename from emp where instr(ename,’A')>0;

or

SQL>select ename from emp where ename like(‘%A%’);

121) Display those employee whose deptno is available in salary?

SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;

122) Display those employee whose first 2 characters from hiredate -last 2 characters of salary?

SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp;

123) Display those employee whose 10% of salary is equal to the year of joining?

SQL>select ename from emp where to_char(hiredate,’YY’)=sal*0.1;

124) Display those employee who are working in sales or research?

SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME IN(‘SALES’,'RESEARCH’));

125) Display the grade of Akki?

SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND Ename=’Akki’;

126) Display those employees who joined the company before 15 of the month?

SQL>select ename from emp where to_char(hiredate,’DD’)<15;

127) Display those employee who has joined before 15th of the month.

SQL>select ename from emp where to_char(hiredate,’DD’)<15;

128) Delete those records where no of employees in a particular department is less than 3.

SQL>delete from emp where deptno=(select deptno from emp group by deptno having count(deptno)<3);

129) Display the name of the department where no employee working.

SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;

130) Display those employees who are working as manager.

SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E WHERE E.MGR=M.EMPNO GROUP BY M.ENAME;

131) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP);

132) Print the details of all the employees who are Sub-ordinate to BLAKE?

SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and e.ename=’BLAKE’;

133) Display employee name and his salary whose salary is greater than highest average of department number?

SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

134) Display the 10th record of emp table(without using rowid)

SQL>SELECT * FROM EMP WHERE ROWNUM<11 MINUS SELECT * FROM EMP WHERE ROWNUM<10;

135) Display the half of the ename’s in upper case and remaining lowercase?

SQL>SELECT SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME)) FROM EMP;

136) Display the 10th record of emp table without using group by and rowid?

SQL>SELECT * FROM EMP WHERE ROWNUM<11 MINUS SELECT * FROM EMP WHERE ROWNUM<10;

137) Create a copy of emp table;

SQL>create table new_table as select * from emp where 1=2;

138) Select ename if ename exists more than once.

SQL>select ename  from emp e group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).

SQL>SELECT REVERSE(ENAME)FROM EMP;

140) Display those employee whose joining of month and grade is equal.

SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN

(SELECT LOSAL FROM SALGRADE WHERE

GRADE=TO_CHAR(HIREDATE,’MM’)) AND

(SELECT HISAL FROM SALGRADE WHERE

GRADE=TO_CHAR(HIREDATE,’MM’));

141) Display those employee whose joining DATE is available in deptno.

SQL>SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,’DD’)=DEPTNO;

142) Display those employees name as follows

A ALLEN

B BLAKE

SQL> SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;

143) List out the employees ename,sal,PF(20% OF SAL) from emp;

SQL>SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;

144) Create table emp with only one column empno;

SQL>Create table emp as select empno from emp where 1=2;

145) Add this column to emp table ename vrachar2(20).

SQL>alter table emp add(ename varchar2(20));

146) Oops I forgot give the primary key constraint.  Add in now.

SQL>alter table emp add primary key(empno);

147) Now increase the length of ename column to 30 characters.

SQL>alter table emp modify(ename varchar2(30));

148) Add salary column to emp table.

SQL>alter table emp add(sal number(10));

149) I want to give a validation saying that salary cannot be greater 10,000 (note give a name to this constraint)

SQL>alter table emp add constraint chk_001 check(sal<=10000);

150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.

SQL>again alter the table or drop constraint with  alter table emp drop constraint chk_001 (or)Disable the constraint by using  alter table emp modify constraint chk_001 disable;

151) My boss has changed his mind.  Now he doesn’t want to pay more than 10,000.so revoke that salary constraint.

SQL>alter table emp modify constraint chk_001 enable;

152) Add column called as mgr to your emp table;

SQL>alter table emp add(mgr number(5));

153) Oh! This column should be related to empno.  Give a command to add this constraint.

SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCESEMP(EMPNO);

154) Add deptno column to your emp table;

SQL>alter table emp add(deptno number(5));

155) This deptno column should be related to deptno column of dept table;

SQL>alter table emp add constraint dept_001 foreign key(deptno) reference dept(deptno) [deptno should be primary key];

156) Give the command to add the constraint.

SQL>alter table <table_name) add constraint <constraint_name> <constraint type>;

157) Create table called as newemp.  Using single command create this table as well as get data into this table(use create table as);

SQL>create table newemp as select * from emp;

SQL>Create table called as newemp.  This table should contain only

empno,ename,dname.

SQL>create table newemp as select empno,ename,dname from emp,dept where

1=2;

158) Delete the rows of employees who are working in the company for more than 2 years.

SQL>delete from emp where (sysdate-hiredate)/365>2;

159) Provide a commission(10% Comm Of Sal) to employees who are not earning any commission.

SQL>select sal*0.1 from emp where comm is null;

160) If any employee has commission his commission should be incremented by 10% of his salary.

SQL>update emp set comm=sal*.1 where comm is not null;

161) Display employee name and department name for each employee.

SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno;

162)Display employee number,name and location of the department in which he is working.

SQL>select empno,ename,loc,dname from emp,dept where emp.deptno=dept.deptno;

163) Display ename,dname even if there are no employees working in a particular department(use outer join).

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

164) Display employee name and his manager name.

SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;

165) Display the department name and total number of employees in each department.

SQL>select dname,count(ename) from emp,dept where emp.deptno=dept.deptno group by dname;

166)Display the department name along with total salary in each department.

SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;

167) Display itemname and total sales amount for each item.

SQL>select itemname,sum(amount) from item group by itemname;

168) Write a Query To Delete The Repeted Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename);

169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL>select ename from emp where rowid in(select rowid from emp where rownum<=7 minusselect rowid from empi where rownum<5);

170)  DISPLAY  TOP N ROWS FROM TABLE?

SQL>SELECT * FROM

(SELECT *  FROM EMP ORDER BY ENAME DESC)

WHERE ROWNUM <10;

171) DISPLAY   TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT  * FROM EMP ORDER  BY SAL DESC ) WHERE ROWNUM <4;

172) DISPLAY  9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMP

WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10

MINUS

SELECT ROWID FROM EMP WHERE ROWNUM <10);

173) select second max salary from emp

select max(sal) fromemp where sal<(select  max(sal) from emp);

174) Delete the 10th record of emp table.

SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11 MINUS

SELECT EMPNO FROM EMP WHERE ROWNUM<10);

CREATE TABLE EMP_nm ( 
 EMPNO NUMBER(4),
 ENAME VARCHAR2(20),
 JOB CHAR(10),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(9,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2)
 );

mysql> create database School
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed

mysql> create table emp
    -> ( Empno Integer,
    -> Ename varchar(20),
    -> Job char(10),
    -> Mgr Integer,
    -> Hiredate Date,
    -> Sal Decimal,
    -> Comm Decimal,
    -> DeptNo Integer
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| Empno    | int(11)       | YES  |     | NULL    |       |
| Ename    | varchar(20)   | YES  |     | NULL    |       |
| Job      | char(10)      | YES  |     | NULL    |       |
| Mgr      | int(11)       | YES  |     | NULL    |       |
| Hiredate | date          | YES  |     | NULL    |       |
| Sal      | decimal(10,0) | YES  |     | NULL    |       |
| Comm     | decimal(10,0) | YES  |     | NULL    |       |
| DeptNo   | int(11)       | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
 

7369	Smith	Clerk		7902	1980-12-17	800	NULL	20

7499	Allen	Salesman	7698	1981-02-20	1600	300	30					
7521	Ward	Salesman	7698	1981-02-22	1250	500	30					
7566	Jones	Manager		7839	1981-04-02	2975	NULL	20					
7654	Martin	Salesman	7698	1981-09-28	1250	1400	30					
7698	Blake	Manager		7839	1981-05-01	2850	NULL	30					
7782	Clark	Manager		7839	1981-06-09	2450	NULL	10					
7788	Scott	Analyst		7566	1982-12-09	3000	NULL	20					
7839	King	President	NULL	1981-11-17	5000	NULL	10					
7844	Turner	Salesman	7698	1981-09-08	1500	0	30					
7876	Adams	Clerk		7788	1989-12-03	950	NULL	30					
7902	Ford	Analyst		7566	1981-12-04	3000	NULL	20		

mysql> INSERT INTO EMP
    -> VALUES (7499,'Allen','Salesman',7698,'1981-02-20',1600,300,30);
Query OK, 1 row affected (0.14 sec)

mysql> select * from emp;
+-------+-------+----------+------+------------+------+------+--------+
| Empno | Ename | Job      | Mgr  | Hiredate   | Sal  | Comm | DeptNo |
+-------+-------+----------+------+------------+------+------+--------+
|  7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 |  300 |     30 |
+-------+-------+----------+------+------------+------+------+--------+
1 row in set (0.05 sec)

mysql> INSERT INTO EMP VALUES(7521,'Ward','Salesman',7698,'1981-02-22',1250,500,
30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7566,'Jones','Manager',7839,'1981-04-02',2975,NULL
,20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7654,'Martin','Salesman',7698,'1981-09-28',1250,14
00,30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7698,'Blake','Manager',7839,'1981-05-01',2850,NULL
,30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7782,'Clark','Manager',7839,'1981-06-09',2450,NULL
,10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7788,'Scott','Analyst',7566,'1982-12-09',3000,NULL
,20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7839,'King','President',NULL,'1981-11-17',5000,NUL
L,10);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO EMP VALUES(7844,'Turner','Salesman',7698,'1981-09-08',1500,0,30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7876,'Adams','Clerk',7788,'1989-12-03',950,NULL,30
);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO EMP VALUES(7902,'Ford','Analyst',7566,'1981-12-04',3000,NULL,
20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7369,'Smith','Clerk',7902,'1980-12-17',800,NULL,20
);
Query OK, 1 row affected (0.01 sec)


mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7499 | Allen  | Salesman  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | Ward   | Salesman  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | Jones  | Manager   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | Martin | Salesman  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | Blake  | Manager   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | Clark  | Manager   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | Scott  | Analyst   | 7566 | 1982-12-09 | 3000 | NULL |     20 |
|  7839 | King   | President | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | Turner | Salesman  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | Adams  | Clerk     | 7788 | 1989-12-03 |  950 | NULL |     30 |
|  7902 | Ford   | Analyst   | 7566 | 1981-12-04 | 3000 | NULL |     20 |
|  7369 | Smith  | Clerk     | 7902 | 1980-12-17 |  800 | NULL |     20 |
+-------+--------+-----------+------+------------+------+------+--------+
12 rows in set (0.00 sec)

TABLE : EMP

CREATE TABLE EMP ( 
 EMPNO INTEGER,
 ENAME VARCHAR(20),
 JOB CHAR(10),
 MGR INTEGER,
 HIREDATE DATE,
 SAL DECIMAL,
 COMM DECIMAL,
 DEPTNO INTEGER
 );

INSERT INTO EMP VALUES(7369,'Smith','Clerk',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES(7499,'Allen','Salesman',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'Ward','Salesman',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'Jones','Manager',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'Martin','Salesman',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'Blake','Manager',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'Clark','Manager',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'Scott','Analyst',7566,'1982-12-09',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'King','President',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'Turner','Salesman',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'Adams','Clerk',7788,'1989-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'Ford','Analyst',7566,'1981-12-04',3000,NULL,20);

CREATE TABLE DEPT ( 
 DEPTNO INTEGER,
 DNAME varchar(20),
 LOC varchar(20)
 );

INSERT INTO DEPT VALUES(10,'Accounting','New York');

INSERT INTO DEPT VALUES(20,'Research','Dallas');

INSERT INTO DEPT VALUES(30,'Sales','Chicago');

INSERT INTO DEPT VALUES(40,'Operations','Boston');

STRING

DATE & TIME: