1) 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;
2) Add column called as mgr to your emp table;
SQL>alter table emp add(mgr number(5));
3) 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) REFERENCES EMP(EMPNO)
4) Add deptno column to your emp table;
SQL>alter table emp add(deptno number(5));
5) 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]
6) Give the command to add the constraint.
SQL>alter table <table_name) add constraint <constraint_name>
<constraint type>
7) 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;
8) 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;
9) 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
10) 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;
21) Display employee name and department name for each employee.
SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno
22)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;
23) 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(+)
24) Display employee name and his manager name.
SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;
25) 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;
26)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;
27) Display itemname and total sales amount for each item.
SQL>select itemname,sum(amount) from item group by itemname;
28) Write a Query To Delete The Repeated Rows from emp table;
SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename)
29) TO DISPLAY 5 TO 7 ROWS FROM A TABLE
SQL>select ename from emp where rowid in(select rowid from emp where rownum<=7 minus select rowid from empi where rownum<5)
30) DISPLAY TOP N ROWS FROM TABLE?
SQL>SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME DESC)WHERE ROWNUM <10;
31) DISPLAY TOP 3 SALARIES FROM EMP;
SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4
32) DISPLAY 9th FROM THE EMP TABLE?
SQL>SELECT ENAME FROM EMPWHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP WHERE ROWNUM <10)
33) select second max salary from emp;
select max(sal) fromemp where sal<(select max(sal) from emp);
