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) REFERENCES
EMP(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;
Great Article
ReplyDeleteData Mining Projects
Python Training in Chennai
Project Centers in Chennai
Python Training in Chennai