CONSTRAINTS - bilaterals org



lGuaranteed Popular Links :

Sql url :

Note: Learn from this URL, U will be Master in SQL

IBM Any Document – Reference :-



Gentle Introduction to SQL :



Introduction to Oracle: Tutorial Series :

Oracle PL/SQL Tutorial :

Oracle SQL*Plus Tutorial :

Oracle/SQL Tutorial :

SQL Tutorial :

SQL - A Practical Introduction. (Free Download) :

Oracle9i Database Online Documentation :



Oracle9i Database List of Books (Release 9.0.1)



Administrator Guide – DOC -

PL/SQL REFERENCE Guide :



SQL REFERENCE Guide :



SQL*Plus User's Guide and Reference



CONSTRAINTS

PRIMARY CONSTRAINTS : PRIMARY,UNIQUE,CHECK,REFERENCES)

SECONDARY CONSTRAINTS : NOT NULL,DEFAULT)

CONSTRAINTS BASED ON 2 LEVELS COLUMN LEVEL and TABLE LEVEL

Except Not null all others can be defined as Both table and column level.

But Not null as only column level.

Eg. For SECONDARY CONSTRAINTS

~~~~~~~~~~~~~~~~~~~~~~~

1. NOT NULL CONSTRAINT

CREATE TABLE EMP9( ENO NUMBER(3) NOT NULL,

ENAME VARCHAR2(10));

2. DEFAULT CONSTRAINT

CREATE TABLE EMP9(ENO NUMBER(3) NOT NULL,

ENAME VARCHAR2(10),DOJ DATE DEFAULT SYSDATE);

3. PRIMARY CONSTRAINT(COLUMN LEVEL)

UNIQUE

CREATE TABLE EMP9(

ENO NUMBER(3) NOT NULL CONSTRAINT UNIEMP UNIQUE,

ENAME VARCHAR2(10));

PRIMARY KEY

CREATE TABLE EMP9(

ENO NUMBER(3) CONSTRAINT PKEMP9 PRIMARY KEY,

ENAME VARCHAR2(10));

4. CHECK CONSTRAINT

CREATE TABLE BANK(

ACNO NUMBER(2) CONSTRAINT PKBANK PRIMARY KEY,

ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK ( ACTYPE IN

('SB','CA','RD')),

ACNAME VARCHAR2(10),

AMOUNT NUMBER(4));

5. REFERENCES

CREATE TABLE EMP9(

ENO NUMBER(3) CONSTRAINT PKE9 PRIMARY KEY,

JOB VARCHAR2(10),

ENAME VARCHAR2(10),

MGR NUMBER(4) REFERENCES EMP9(ENO));

6. REFERENCES(REFERING TO DIFFERENT TABLE)

CREATE TABLE DEPT9(DEPTNO NUMBER(2) CONSTRAINT PKDNO PRIMARY KEY, DNAME VARCHAR2(10), LOC VARCHAR2(10));

CREATE TABLE EMP9( EMPNO NUMBER(4),ENAME VARCHAR2(10),

SAL NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FKDNO REFERENCES DEPT9(DEPTNO));

7. TABLE LEVEL CONSTRAINTS

UNIQUE TABLE LEVEL

CREATE TABLE BANK( ACNO NUMBER(3),ACTYPE VARCHAR2(10),

BAL NUMBER(7,2),PLACE VARCHAR2(10),CONSTRAINT UNIBANK UNIQUE(ACNO,ACTYPE));

PRIMARY KEY(TABLE LEVEL)

CREATE TABLE BANK( ACNO NUMBER(2), ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK (ACTYPE IN ('SB','CA','RD')),AMOUNT NUMBER)

1. Display the Details of all employees working in the same Department

as 'FORD'

SELECT * FROM EMPLOYEES EMP, DEPARTMENT DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNAME = ‘FORD’

2. Display the details of all employees who are joined before 'FORD'

SELECT * FROM EMPLOYEES EMP, DEPARTMENT DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.JOINED_DATE(select sal from TEMP_EMP where ename='SMITH')

4. Display all the employees working in chicago

5. find the two minimum salaries among table

SELECT SAL FROM (SELECT * FROM TEMP_EMP ORDER BY SAL)

WHERE ROWNUM 1000 AND < 2000',

LEAST(SAL,3000), '> 2000 AND < 3000',

LEAST(SAL,4000),' = 3000',' > 3000 ') FROM EMP;

19. display the details of all the employees whose salary is equal to

range as specified in grade 5.

select sal from emp where sal >

(select losal from salgrade where grade=5) and

sal< (select hisal from salgrade where grade=5);

20. CREATE A SEQUENCE AND GENERATE SOME NUMBERS .INSERT THESE NUMBERS

INTO THE TABLE BY CONCATENATING IT WITH E.

create sequence s1

start with 1

increment by 1

maxvalue 10

insert into EM3(A) values

(concat('e',s12.nextval));

21.

select lpad(' ',level-1)||ename from emp

START WITH job = 'PRESIDENT'

CONNECT BY PRIOR EMPNO = MGR;

_______________________________________________________________________

END OF REPORT

_______________________________________________________________________

select a.sal,a.ename,a.deptno

from emp a

group by a.deptno having a.sal>all(select avg(sal) from emp b

from emp b

group by deptno);

select distinct a.sal, a.ename, a.deptno

from emp a,emp b

where a.deptno=b.deptno and

a.sal >all(select avg(sal) from emp b group by deptno);

-------------------------------------------------------------------

declare

a number:=&a;

b number:=&b;

c number;

begin

c:=a*b;

b:=a+b;

a:=mod(c,b);

dbms_output.put_line(' '||a);

dbms_output.put_line(' '||b);

dbms_output.put_line(' '||c);

end;

declare

a number:=&a;

n number;

begin

for i in 1..10 loop

n:=a*i;

dbms_output.put_line(a||'*'||i||'='||n);

end loop;

end;

declare

a number:=&a;

b number;

c number:=2;

begin

for i in 1..10 loop

if a:=1 then

b:=c+2;

else

b:=2+i;

dbms_output.put_line(b);

end if;

end loop;

end;

declare

begin

for i in 1..3 loop

dbms_output.put_line('?');

dbms_output.new_line;

dbms_output.put(?);

end loop;

end;

declare

a number(10);

begin

a:=1;

dbms_output.put_line(a);

a:=12;

dbms_output.put_line(a);

a:=123;

dbms_output.put_line(a);

a:=1234;

dbms_output.put_line(a);

a:=12345;

dbms_output.put_line(a);

a:=123456;

dbms_output.put_line(a);

a:=1234567;

dbms_output.put_line(a);

a:=12345678;

dbms_output.put_line(a);

a:=123456789;

dbms_output.put_line(a);

a:=1234567890;

dbms_output.put_line(a);

end;

declare

e1 number;

e2 number;

e3 number;

val number;

begin

val:=&e3;

select deptno into e2 from emp where empno=val;

if e2=10 then update emp set comm=1000 where empno=val;

elsif e2 = 20 then update emp set comm=2000 where empno=val;

elsif e3=30 then update emp set comm=3000 where empno=val;

end if;

end;

declare

type type1 is record(eno number(4) not null:=7788,ena emp.ename%type);

erec type1;

begin

select empno,ename into erec from emp where empno=7788;

dbms_output.put_line(erec.eno||erec.ena);

end;

declare

salary emp.sal%type;

mgr_num emp.mgr%type;

emno emp.empno%type;

enam emp.ename%type;

begin

select ename,empno,sal, mgr into enam,emno,salary,

mgr_num from emp where empno=7788;

while salary0) NN_SAL NOT NULL,

COMM NUMBER(7,2),

DEPTNO NUMBER(2)CONSTRAINT FK_DEPTNO

REFERENCES DEPT(DEPTNO)

13: SELECT ALL THE CONSTRAINTS IN EMP TABLE

SOL: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP';

14: SELECT THE OWNER,CONSTRAINT NAME,CONSTRAINT TYPE,TABLE NAME,STATUS

FOR DEPT TABLE

SOL: SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS

FROM USER_CONSTRAINTS WHERE TABLE_NAME='DEPT';

15: DROP THE CONSTRAINT UQ_ENAME FROM EMP TABLE

SOL: ALTER TABLE EMP DROP CONSTRAINT UQ_ENAME;

16: ADD A NEW COLUMN PINCODE WITH NOT NULL CONSTRAINT

TO THE EXISTING TABLE DEPT

SOL: ALTER TABLE DEPT ADD(PINCODE NUMBER(6) NOT NULL);

17: DISABLE THE CONSTRAINT PK_DEPTNO PRESENT IN DEPT TABLE

SOL: ALTER TABLE DEPT DISABLE CONSTRAINT PK_DEPTNO;

18: ENABLE THE CONSTRAINT PK_DEPTNO WHICH IS DEFINED IN THE

DEPTNO COLUMN OF DEPT TABLE

SOL: ALTER TABLE DEPT ENABLE CONSTRAINT PK_DEPTNO;

II. QUESTIONS BASED ON DATA MANIPULATION LANGUAGE COMMANDS :-

PROBLEMS BASED ON INSERT COMMAND:-

19: INSERT THE GIVEN VALUES INTO THE TABLES:

EMP:

(i)7369,SMITH,CLERK,7902,17-DEC-80,800,NULL,20

(ii)7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

(iii)7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

(iv)7566,JONES,MANAGER,7839,02-APR-81,2975,NULL,20

(v)7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

(vi)7698,BLAKE,MANAGER,7839,01-MAY-81,2850,NULL,30

(vii)7782,CLARK,MANAGER,7839,09-JUN-81,2450,NULL,10

(viii)7788,SCOTT,ANALYST,7566,19-NOV-96,3000,NULL,20

(ix)7839,KING,PRESIDENT,NULL,17-NOV-81,5000,NULL,10

(x)7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

(xi)7876,ADAMS,CLERK,7788,23-DEC-96,1100,NULL,20

(xii)7900,JAMES,CLERK,7698,03-DEC-81,950,NULL,30

(xiii)7902,FORD,ANALYST,7566,03-DEC-81,3000,NULL,20

(xiv)7934,MILLER,CLERK,7782,23-JAN-82,1300,NULL,10

(xv)7943,JOHN,CLERK,7943,10-DEC-83,2000,NULL,50

DEPT:

(i)10,ACCOUNTING,NEW YORK

(ii)20,RESEARCH,DALLAS

(iii)30,SALES,CHICAGO

(iv)40,OPERATIONS,BOSTON

(v)50,COMPUTER,AMERICA

20: INSERT ONLY THE RECORDS OF EMPLOYEE NUMBER,NAME,SALARY

INTO EMP TABLE

SOL: INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES('7955','PAUL',1200);

21: INSERT TWO ROWS INTO EMP TABLE USING PARAMETER SUBSTITUTION

SOL: INSERT INTO EMP VALUES(&EMPNO,'&ENAME','&JOB',&MGR,'&HIREDATE',

&SAL,&COMM,&DEPTNO);

22: INSERT THE CURRENT TRANSACTION DATE TEMPORARY TABLE

SOL: INSERT INTO TEMP VALUES(SYSDATE);

PROBLEMS ON SELECT COMMAND:-

23: LIST THE INFORMATION OF ALL EMPLOYEES

SOL: SELECT * FROM EMP;

24: LIST THE INFORMATION OF ALL THE DEPARTMENTS

SOL: SELECT * FROM DEPT;

25: LIST THE DEPARTMENT NUMBERS,EMPLOYEE NUMBERS AND THEIR MANAGERS

NUMBERS

SOL: SELECT DEPT.DEPTNO,EMP.EMPNO,EMP.MGR FROM EMP,DEPT;

26: LIST DEPARTMENT NAME AND LOCATIONS FROM DEPT TABLE

SOL: SELECT DNAME,LOC FROM DEPT;

27: LIST THE INFORMATION OF EMPLOYEES AND THEIR DEPARTMENTS

IN A SINGLE DML COMMAND

SOL: SELECT * FROM EMP;

28: COPY ALL THE RECORDS OF THREE COLUMNS EMPNO,ENAME,JOB FROM EMP

TABLE AND INSERT THE RECORDS INTO A TEMP TABLE WITH COLUMN NAMES

SAME AS EMPNO,ENAME,JOB

SOL: INSERT INTO TEMP(EMPNO,ENAME,JOB)

SELECT EMPNO,ENAME,JOB

29: LIST THE DETAILS OF BOTH THE TABLES

SOL: SELECT * FROM EMP,DEPT;

30: LIST THE INFORMATION OF ALL THE EMPLOYEES PRESENT IN THE USER

NAMED SCOTT

SOL: SELECT * FROM SCOTT.EMP;

31: LIST THE INFORMATION OF ALL THE DEPARTMENTS FROM YOUR BATCHMATES

DEPT TABLE

SOL: SELECT * FROM ORA252P.DEPT;

32: LIST OUT ALL THE TABLE NAMES IN YOUR SCHEMA

SOL: SELECT * FROM TAB;

33: LIST ALL THE SYSTEM TABLES

SOL: SELECT * FROM SYS.DICTIONARY;

34: GET THE INFORMATION OF THE MAXIMUM AVAILABLE BLOCKS ALLOTED TO A

PARTICULAR USER FOR CREATING TABLES FROM THE SYSTEM TABLES

SOL: SELECT * FROM USER_TS_QUOTAS;

35: LIST OUT ALL THE PRIVILAGES GIVEN TO A PARTICULAR USER

36: LIST OUT ALL THE TABLES WHICH START WITH 'S'

SOL: SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'S%';

37: COPY THE STRUCTURE OF DEPT TABLE ALONE TO A TEMPORARY TABLE

CALLED TEMP1

SOL: CREATE TABLE TEMP1 AS SELECT * FROM DEPT WHERE 1=2;

PROBLEMS ON UPDATE COMMAND:-

38: UPDATE THE SALARY BY 10% HIKE TO ANALYSTS WORKING IN DEPARTMENT

NUMBER 20 AND 30

SOL: UPDATE EMP SET SAL=SAL+(SAL*0.1) WHERE DEPTNO IN (10,20)

AND JOB='ANALYST';

39: GIVE 5% RAISE IN SALARY TO ALL THE SALESMAN

SOL: UPDATE EMP SET SAL=SAL+(SAL*0.5) WHERE JOB='SALESMAN';

40: PROMOTE ALL THE EMPLOYEES DESCRIBED AS SALESMAN TO SALES OFFICER

IF THEIR GROSS SALARY PER MONTH IS GREATER THAN 3000

SOL: UPDATE EMP SET JOB='SALES OFFICER' WHERE JOB='SALESMAN'

AND SAL>3000;

41: GIVE ALL THE EMPLOYEES A COMMISSION OF RS.500

SOL: UPDATE EMP SET COMM=500;

42: CHANGE THE DEPARTMENT OF JAMES TO 20

SOL: UPDATE EMP SET DEPTNO=20 WHERE ENAME='JAMES';

43. CALCULATE ALL THE EMPLOYEES TOTAL SALARY WITH COMMISION

SOL: SELECT SAL+NVL(COMM) "TOTAL" FROM EMP;

PROBLEMS ON DELETE COMMAND:-

44: DELETE ALL THE RECORDS OF EMPLOYEES

SOL: DELETE FROM EMP;

45: GET BACK THE ORIGINAL RECORDS OF EMPLOYEES BACK

SOL: ROLLBACK;

46: DELETE ALLEN'S RECORD ONLY

SOL: DELETE FROM EMP WHERE ENAME='ALLEN';

47: DELETE RECORDS OF ENAME COLUMN ONLY AND VERIFY IT

SOL: NOT POSSIBLE

48: DELETE THE RECORDS OF EMPLOYEE NUMBER 7782

SOL: DELETE FROM EMP WHERE EMPNO=7782;

49: DELETE THE EMPLOYEE'S RECORDS WHO DOES'NT HAVE COMMISION

SOL: DELETE FROM EMP WHERE COMM IS NULL;

50: GET BACK THE ORIGINAL RECORDS

SOL: ROLLBACK;

51: DELETE THE DUPLICATE RECORDS OF THE EMPLOYEE TABLE

SOl: DELETE FROM EMP A

WHERE ROWID(SELECT MIN(ROWID) FROM EMP B

WHERE A.EMPNO=B.EMPNO);

52: DELETE THE FIRST FIVE RECORDS OF EMPLOYEE TABLE

SOL: DELETE FROM EMP X

WHERE 5>(SELECT COUNT(ROWID) FROM EMP Y WHERE Y.ROWID=(SELECT SAL FROM EMP WHERE ENAME LIKE 'FORD%'));

171: LIST THE EMPLOYEES IN DEPARTMENT 10 WITH THE SAME

JOB AS ANY ONE IN SALES DEPARTMENT

SOL: SELECT ENAME,JOB FROM EMP

WHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO=10)

INTERSECT

SELECT ENAME,JOB FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB='SALESMAN');

172: LIST EMPLOYEES WHOSE SALARY IS SAME AS THAT OF SCOTT OR WARD

SOL: SELECT ENAME FROM EMP

173: TO LIST THE DEPARTMENT THAT HAS NO EMPLOYEES

SOL: SELECT DEPTNO FROM DEPT

MINUS

SELECT DEPTNO FROM EMP;

(OR)

SELECT DEPTNO FROM DEPT

WHERE DEPTNO NOT IN

(SELECT DEPTNO FROM EMP);

174: TO LIST THE DEPARTMENT AND COUNT OF JOB

IN WHICH THERE ARE MORE THAN ON SALESMANS.

SOL: SELECT DEPTNO,JOB,COUNT(*) FROM EMP

WHERE JOB='SALESMAN'

GROUP BY DEPTNO,JOB

HAVING COUNT(*)>1;

175: LIST THE DEPARTMENT IN WHICH THEIR ARE MORE THAN ONE

SALESMAN

SOL: SELECT DEPTNO,JOB FROM EMP WHERE JOB='SALESMAN'

AND DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO

HAVING COUNT(JOB)>1);

176: SELECT THE EVEN NUMBER OF ROWS FROM EMPLOYEE TABLE

SOL: SELECT * FROM EMP

WHERE ROWID IN

(SELECT DECODE(MOD(ROWNUM,2),0,ROWID,NULL) FROM EMP);

177: LIST THE ODD NUMBER OF ROWS FROM EMPLOYEE TABLE

SELECT * FROM EMP

WHERE ROWID IN

(SELECT DECODE(MOD(ROWNUM,2),1,ROWID,NULL) FROM EMP);

178: DELETE THE ODD NUMBER ROWS FROM EMPLOYEE TABLE

SOL: DELETE FROM EMP

WHERE ROWID IN

(SELECT DECODE (MOD(ROWNUM,2),1,ROWID,NULL) FROM EMP);

179: DELETE THE EVEN NUMBER ROWS FROM EMPLOYEE TABLE

SOL: DELETE FROM EMP

WHERE ROWID IN

(SELECT DECODE (MOD(ROWNUM,2),0,ROWID,NULL) FROM EMP);

180: LIST THE EMPLOYEES WHOSE SALARY IS GREATER THAN THE

AVERAGE SALARY OF HIS DEPARTMENT

SOL: SELECT DEPTNO,EMPNO,SAL FROM EMP X WHERE

SAL>(SELECT AVG(SAL) FROM EMP Y WHERE X.DEPTNO=Y.DEPTNO);

181: QUERY DEPARTMENTWISE AVERAGE/MAXIMUM/MINIMUM SALARIES

SOL: SELECT EMP.DEPTNO,DNAME,MAX(SAL),MIN(SAL),AVG(SAL)

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

GROUP BY EMP.DEPTNO,DNAME;

182: COUNT THE NUMBER OF MALE AND FEMALE EMPLOYEES

SOL: ALTER TABLE EMP

ADD (SEX CHAR(1));

SELECT SEX,COUNT(*)

FROM EMP

GROUP BY SEX

HAVING SEX ='M';

UNION

SELECT SEX,COUNT(*)

FROM EMP

GROUP BY SEX

HAVING SEX='F';

183: DISPLAY THE YEAR AND NUMBER OF EMPLOYEES FOR THE YEAR

IN WHICH MORE THAN ONE EMPLOYEE WAS HIRED

SOL: SELECT TO_CHAR(HIREDATE,'YYYY') YEAR,COUNT(*)

FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY')

HAVING COUNT(*)>1;

184: DELETE THE DUPLICATE ROWS FROM DEPT TABLE.

SOL: DELETE FROM DEPT

WHERE DEPTNO IN

(SELECT DEPTNO

FROM DEPT

GROUP BY DEPTNO

HAVING COUNT(DEPTNO)>1);

185: DELETE THE DUPLICATE ROWS EXCEPT ITS MINIMUM VALUE

SOL: DELETE FROM EMP A

WHERE ROWID(SELECT MIN(ROWID) FROM EMP B

WHERE A.EMPNO=B.EMPNO);

186: LIST LAST FIVE RECORDS FROM EMP TABLE

SOL: SELECT ENAME,SAL FROM EMP MINUS SELECT ENAME,SAL FROM EMP

WHERE ROWNUM(SELECT MIN(SAL) FROM EMP WHERE

SAL>(SELECT MIN(SAL) FROM EMP))));

189: LIST THE GIVEN RANGE OF RECORDS AS PER THE STORAGE OF

ROWS IN A GIVEN DATABASE TABLE NAMED EMP

SOL: SELECT ROWID FROM EMP X WHERE NOT &N>(SELECT COUNT(ROWID)

FROM EMP Y WHERE Y.ROWID=(SELECT COUNT(ROWID)

FROM EMP Y WHERE Y.ROWIDX.SAL));

191: LIST THE NTH LOWEST SALARY IN THE EMPLOYEE TABLE

SOL: SELECT DISTINCT SAL FROM EMP X WHERE

&N=(SELECT COUNT(DISTINCT SAL) FROM EMP Y

WHERE X.SAL>Y.SAL);

192: LIST EMPLOYEES IN THE EMPLOYEE TABLE ACCEPTING THOSE

FIRST FIVE AND LAST THREE IN THE ORDER OF DATA STORAGE

SOL: SELECT ROWID FROM EMP X WHERE 5>(SELECT COUNT(ROWID)

FROM EMP Y WHERE Y.ROWID(SELECT COUNT(ROWID)

FROM EMP Y

WHERE Y.ROWID>X.ROWID);

193: LIST THE TREE STRUCTURED QUERY WITH EMPLOYEE NAME STARTING

WITH KING

SOL: SELECT LPAD(' ',6*(LEVEL-1))||ENAME NAME FROM EMP

START WITH ENAME='KING'

CONNECT BY MGR=PRIOR EMPNO;

194: LIST THE EMPLOYEES WHOSE SALARY GREATER THAN OR EQUAL TO

AVERAGE SALARY OF DEPARTMENT NUMBER 10

SOL: SELECT SAL,ENAME FROM EMP

WHERE SAL>=(SELECT AVG(SAL) FROM EMP

GROUP BY DEPTNO HAVING DEPTNO=10);

195: SELECT ALL THE ENAMES WITH THE OCCURANCES OF THE LETTER 'S'

SOL: SELECT INSTR(ENAME,'A') FROM EMP;

196: LIST THE DIFFERENT DESIGNATIONS IN DEPARTMENT 20 AND 30

SOL: SELECT DISTINCT JOB FROM EMP

WHERE DEPTNO=20

UNION

SELECT DISTINCT JOB FROM EMP

WHERE DEPTNO=30;

197: LIST THE JOBS COMMON IN DEPARTMENT 20 AND 30

SOL: SELECT JOB FROM EMP

WHERE DEPTNO=20

INTERSECT

SELECT JOB FROM EMP

WHERE DEPTNO=30;

198: LIST THE JOBS UNIQUE TO DEPARTMENT 20

SOL: SELECT JOB FROM EMP

WHERE DEPTNO=20

MINUS

SELECT JOB FROM EMP

WHERE DEPTNO=10

MINUS

SELECT JOB FROM EMP

WHERE DEPTNO=30;

199: LIST THE EMPLOYEES IF AND ONLY IF MORE THAN 10 EMPLOYEES ARE

PRESENT IN DEPARTMENT NUMBER 10

SOL: SELECT * FROM EMP

WHERE EXISTS (SELECT DEPTNO,COUNT(*) FROM EMP

WHERE DEPTNO=10

GROUP BY DEPTNO

HAVING COUNT(*)>=3);

200: LIST THE EMPLOYEE NAMES WHOSE SALARY IS GREATER THAN THE

LOWEST SALARY OF AN EMPLOYEE BELONGING TO DEPARTMENT 20

SOL: SELECT ENAME FROM EMP

WHERE SAL>ANY(SELECT SAL FROM EMP WHERE DEPTNO=20);

201: LIST THE EMPLOYEE NAMES WHOSE SALARY IS GREATER THAN THE

HIGHEST SALARY OF AN EMPLOYEE BELONGING TO DEPARTMENT NUMBER 20

SOL: SELECT ENAME FROM EMP

WHERE SAL>ALL(SELECT SAL FROM EMP WHERE DEPTNO=20);

202: CREATE A VIEW WITH COLUMNS EMPNO AND A 10% HIKE SALARY COLUMN

AND TRY TO INSERT A ROW IN THIS VIEW AND VERIFY IT

SOL: CREATE VIEW EMPSAL AS SELECT EMPNO,SAL*.1 TOT_SAL FROM EMP;

HINT: THE FOLLOWING VIEWS ARE READ - ONLY VIEWS

i.e NO INSERT OR UPDATE THROUGH THESE VIEWS IS POSSIBLE

THIS IS A READ - ONLY VIEW BECAUSE OF THE PRESENCE

OF AN AGGREGATE FUNCTION

203: CREATE A VIEW WITH THE COLUMNS EMPNO,ENAME,DEPTNO SELECTED

FROM THE TABLE EMP AND CORRESPONDING DEPARTMENT NUMBER 30

INSERT ROWS INTO THIS VIEW AND VERIFY THE RESULT

SOL: CREATE VIEW EMPDEPT AS

SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE DEPTNO=30;

HINT: THIS IS NOT A READ - ONLY VIEW. ROWS CAN BE INSERTED

INTO THE EMP TABLE USING THIS VIEW , BUT CANNOT BE VIEWED

BY THE EMPDEPT VIEW AS THE VIEW CAN ONLY RETRIEVE ROWS

BELONGING TO DEPARTMENT 30

204: CREATE A VIEW WITH COLUMNS EMPNO,ENAME,DEPTNO AND DEPTNO=30

WITH A CHECK OPTION AND INSERT A ROW INTO THE VIEW WITH

DEPARTMENT NUMBER 20 AND VERIFY THE RESULT

SOL: CREATE VIEW EMPDEPT1 AS

SELECT EMPNO,ENAME,DEPTNO FROM EMP

WHERE DEPTNO=30

WITH CHECK OPTION;

205: DERIVE A VIEW FROM ANOTHER VIEW HAVING CHECK OPTION

SOL: CREATE VIEW EMPDEPT2 AS

SELECT EMPNO,ENAME,DEPTNO FROM EMPDEPT1;

206: LIST ALL THE VIEW NAMES CREATED IN YOUR LOGIN

SOL: SELECT * FROM USER_VIEWS;

207: CREATE A SIMPLE SEQUENCE

SOL: CREATE SEQUENCE S1 INCREMENT BY 1 START WITH 1000;

208: INSERT THE SEQUENCE NUMBER INTO THE EMPNO COLUMN

OF EMP TABLE

SOL: INSERT INTO EMP(EMPNO) VALUES(S1.NEXTVAL);

209: CREATE A SEQUENCE WITH STEP VALUE=1;

START VALUE=2;MINIMUM VALUE=1;MAXIMUM VALUE=22

AND THE SEQUENCE TO RESET AUTOMATICALLY WHEN IT COMES

TO START VALUE

SOl: CREATE SEQUENCE S2

INCREMENT BY 1

START WITH 2

MINVALUE 1

MAXVALUE 22

CYCLE;

210: DROP THE SEQUENCE

SOL: DROP SEQUENCE S2;

211: LIST ALL THE SEQUNCES CREATED IN YOUR LOGIN

SOL: SELECT * FROM USER_SEQUENCES;

212: CREATE AN INDEX ON COLUMN ENAME OF EMP TABLE

SOL: CREATE INDEX ID1 ON EMP(ENAME);

213: CREATE INDEX ON TWO COLUMNS EMPNO,ENAME

SOL: CREATE INDEX ID2 ON EMP (EMPNO,ENAME);

214: DROP THE INDEX YOU HAVE CREATED

SOL: DROP INDEX ID1;

215: LIST THE INDEXES CREATED ON THE TABLE EMP

SOL: SELECT * FROM USER_INDEXES WHERE TABLE_NAME='EMP';

216: CREATE A CLUSTER BASED ON THE DEPT AND EMP TABLE FOR

THE FIELD DEPTNO

SOL: CREATE CLUSTER C1 (DEPTNO NUMBER(2));

CREATE INDEX ID3 ON CLUSTER C1;

CREATE TABLE DEPT1

(DNO NUMBER(2) PRIMARY KEY,

DNAME CHAR(15),

LOC CHAR(15))

CLUSTER C1(DNO);

CREATE TABLE EMP1

(EMPNO NUMBER(4) PRIMARY KEY,

ENAME CHAR(15),

HIREDATE DATE,

SALARY NUMBER(9,2),

DEPTARTMENTNO NUMBER(2) REFERENCES DEPT1(DNO))

CLUSTER C1(DEPTARTMENTNO);

217: INSERT INTO TABLES DEPT1 AND EMP1 EACH THREE ROWS AND

CHECK THE ROWID OF THE TWO CLUSTER TABLES;

SOL: INSERT INTO DEPT1 VALUES(&DNO,'&DNAME','&LOC');

INSERT INTO EMP1 VALUES(&EMPNO,'&ENAME','&HIREDATE',&SALARY,

&DEPARTMENTNO);

SELECT DEPT1.ROWID,EMP1.ROWID FROM DEPT1,EMP1;

218: LIST ALL THE CLUSTERS CREATED IN YOUR LOGIN

SOL: SELECT * FROM USER_CLUSTERS;

219: CREATE A SYNONYM FOR TABLE EMP

SOL: CREATE SYNONYM SY1 FOR EMP;

220: LIST INFORMATION OF SYNONYM

SOL: SELECT * FROM SY1;

221: LIST ALL THE SYNONYM CREATED IN YOUR LOGIN

SOL: SELECT * FROM USER_SYNONYMS;

222: DROP THE SYNONYM CREATED

SOL: DROP SYNONYM SY1;

EMPNO ENAME JOB MGR HIREDATE SAL

COMM DEPTNO

223: CREATE THE EMPLOYEE REPORT OF THE COLUMNS EMPNO,ENAME,JOB

MGR,HIREDATE,SAL,COMM,DEPTNO IN THE FORMAT BELOW:

HEADER: BRAINWARE INFOTECH(P) LTD

EMPLOYEES REPORT

COLUMN:

NAMES EMPLOYEE EMPLOYEE DESIGNATION MANAGER DATE OF CURRENT

NUMBER NAME NUMBER JOINING SALARY

COMMISION DEPARTMENT

NUMBER

FOOTER: HAVE A NICE DAY !

SOL: TTITLE LEFT'BRAINWARE INFOTECH(P) LTD| CENTER 'EMPLOYEES REPORT'

BTITLE 'HAVE A NICE DAY!'

COLUMN EMPNO HEADING 'EMPLOYEE | NUMBER'

COLUMN ENAME HEADING 'NAME OF | EMPLOYEE'

COLUMN JOB HEADING ' DESIGNATION'

COLUMN MGR HEADING 'MANAGER | NUMBER'

COLUMN HIREDATE HEADING ' DATE OF | JOINING '

COLUMN SAL HEADING ' CURRENT | SALARY '

COLUMN COMM HEADING 'COMMISION '

COLUMN DEPTNO HEADING 'DEPARTMENT | NUMBER'

Package Specification

The package specification contains public declarations. The scope of

these declarations is local to your database schema and global to the

package. So, the declared objects are accessible from your application

and from anywhere in the package.

#****************#

PACKAGES

#****************#

#****************##****************##****************##****************#

1. WRITE A FUNCTION TO RETURN MAX(EMPNO)+1 .

WRITE A PROCEDURE TO FETCH THE DETAILS WHEN EMPNO=RESULT OF FUNCTION.

WRITE A FUNCTION TO PASS EMPLOYEE NUMBER AND GET THE JOB

WRITE A PROCEDURE TO ACCEPT DEPTNO AND DISPLAY THE DETAILS OF ALL

EMPLOYEES OF THAT PARTICULAR DEPARTMENT.(HINT : CURSOR)

WRITE A PACKAGE WHICH INCLUDES ALL THESE PROCEDURES AND FUNCTIONS

_________________________________________________________________-

CREATE OR REPLACE PACKAGE EMP_PACK IS

FUNCTION NEXTEMP RETURN NUMBER;

PROCEDURE P1;

FUNCTION F2(ENO NUMBER) RETURN VARCHAR;

PROCEDURE P2(DNO NUMBER);

CURSOR C1 IS SELECT * FROM EMP;

END emp_pack;

CREATE OR REPLACE PACKAGE BODY EMP_PACK IS

FUNCTION NEXTEMP RETURN NUMBER IS

CURR_MAX NUMBER:=0;

BEGIN

SELECT MAX(EMPNO) INTO CURR_MAX FROM EMP;

CURR_MAX:=CURR_MAX+1;

RETURN CURR_MAX;

END NEXTEMP;

PROCEDURE P1 IS

ENO NUMBER;

NAME VARCHAR2(10);

DEPNO NUMBER;

SALARY EMP.SAL%TYPE;

BEGIN

ENO:=EMP_PACK.NEXTEMP;

PRINT('NEW EMPLOYEE NUMBER IS'||ENO);

SELECT ENAME,SAL,DEPTNO INTO NAME,SALARY,DEPNO FROM EMP WHERE

EMPNO=ENO;

PRINT(ENO||' '||NAME||' '||DEPNO||' '||SALARY);

EXCEPTION

WHEN NO_DATA_FOUND THEN

PRINT('NO DATA FOUND');

END P1;

PROCEDURE P2(DNO NUMBER) IS

CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=DNO;

BEGIN

FOR I IN C1 LOOP

DBMS_OUTPUT.PUT_lINE(I.ENAME||' '||I.JOB||' '||I.DEPTNO);

END LOOP;

EXCEPTION

WHEN NO_dATA_FOUND THEN

PRINT('NO DATA FOUND');

END P2;

FUNCTION F2(ENO NUMBER) RETURN VARCHAR IS

DESIG EMP.JOB%TYPE;

BEGIN

SELECT JOB INTO DESIG FROM EMP WHERE EMPNO=ENO;

RETURN DESIG;

END F2;

END EMP_PACK;

DECLARE

NEXTEMP NUMBER;

F2 EMP.JOB%TYPE;

BEGIN

NEXTEMP:=EMP_PACK.NEXTEMP;

PRINT('NEXTEMP IS '||NEXTEMP);

EMP_PACK.P1;

F2:=EMP_PACK.F2(&EMPNO);

PRINT('PRINTING JOB FROM F2 '||F2);

EMP_PACK.P2(&DEPTNO);

END;

#****************##****************##****************##****************#

1. Create a package that contains the following sub programs

(a) Procedure NEW_EMP to add a new employee to the emp table.

(b) Procedure UPDATE_EMP to update the employee details

(display error message if i.e error)

(c) Function CHECK_EMP to check if an employee is

existing in the table

_______________________________________________________________________

CREATE OR REPLACE PACKAGE GEEPACK IS

PROCEDURE NEW_EMP(eno number,dno number,mg number,name varchar2);

PROCEDURE UPDATE_EMP(N NUMBER);

FUNCTION CHECK_eMP(N NUMBER) RETURN NUMBER;

END GEEPACK;

CREATE OR REPLACE PACKAGE BODY GEEPACK IS

PROCEDURE NEW_EMP(ENO NUMBER, DNO NUMBER,MG NUMBER,NAME VARCHAR2) IS

BEGIN

INSERT INTO EMP(EMPNO,DEPTNO,MGR,ENAME) VALUES(ENO,DNO,MG,NAME);

END NEW_EMP;

PROCEDURE UPDATE_EMP (N NUMBER) IS

DESIG VARCHAR2(10);

BEGIN

SELECT JOB INTO DESIG FROM EMP WHERE EMPNO=N;

DBMS_OUTPUT.PUT_LINE('EMPNO = '||N||' JOB = '||DESIG);

IF DESIG='CLERK' THEN

UPDATE EMP SET JOB='FDC' WHERE EMPNO=N;

print('job updated to fdc');

ELSIF DESIG='MANAGER' THEN

UPDATE EMP SET JOB='DGM' WHERE EMPNO=N;

print('job updated to dgm');

END IF;

END UPDATE_EMP;

FUNCTION CHECK_eMP (N NUMBER) RETURN NUMBER

IS

N1 NUMBER;

BEGIN

SELECT COUNT(*) INTO N1 FROM EMP WHERE EMPNO=N;

IF n1=0 then

PRINT('EMPLOYEE '||n||' does not EXISTS ');

ELSE

PRINT('EMPLOYEE '||n||' EXISTs');

END IF;

return n1;

END CHECK_EMP;

END GEEPACK;

declare

eno number:=&empno;

dno number:=&deptno;

mg number:=&mgr;

name varchar2(10):='&name';

res number;

begin

print('new_emp is called and values will be inserted');

geepack.new_emp(eno,dno,mg,name);

geepack.update_emp(&updateno);

res:=geepack.CHECK_eMP(&checkno);

print(res);

end;

#****************##****************##****************##****************#

WRITE A FUNCTION TO RETURN MAX(EMPNO)+1 .

WRITE A PROCEDURE TO FETCH THE DETAILS WHEN EMPNO=RESULT OF FUNCTION.

WRITE A PROCEDURE TO ACCEPT DEPTNO AND DISPLAY THE DETAILS OF ALL

EMPLOYEES OF THAT PARTICULAR DEPARTMENT.(HINT : CURSOR)

WRITE A PACKAGE WHICH INCLUDES ALL THESE PROCEDURES AND FUNCTIONS

CREATE OR REPLACE PACKAGE PACK IS

FUNCTION F1 RETURN NUMBER ;

PROCEDURE P1(N NUMBER);

PROCEDURE P2(DNO NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY PACK IS

FUNCTION F1 RETURN NUMBER IS

Z NUMBER;

BEGIN

SELECT MAX(EMPNO)+1 INTO Z FROM EMP;

RETURN Z;

END;

PROCEDURE P1(N NUMBER) IS

A NUMBER;

NAME VARCHAR2(10);

BEGIN

A:=F1;

SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=A;

PRINT('THE NAME IS '||A);

EXCEPTION

WHEN NO_DATA_FOUND THEN

PRINT('NO DATA');

END;

PROCEDURE P2(DNO NUMBEr) IS

CURSOR C2 IS SELECT * FROM EMP WHERE DEPTNO=DNO;

BEGIN

FOR I IN C2 LOOP

PRINT(I.ENAME||' '||I.DEPTNO);

END LOOP;

END ;

END;

DECLARE

F1RES NUMBER;

BEGIN

F1RES:=PACK.F1;

PRINT('THE RESULT OF FUNCTION IS '||F1RES);

PACK.P1(F1RES);

PACK.P2(&DEPTNO);

END;

create table OrdTab

(

OrdId Number,

Customer_Name varchar2(10),

OrdDate Date,

Total Number

)

Partition By Range(OrdId)

(

Partition P1 VAlues Less Than (100),

Partition P2 Values Less Than (200),

partition p3 values less than(300)

);

For the column ordid of the ordtab table i have inserted the below

specified values

99

98

100

150

199

200

250

290

when i was above to insert 300 this is what happened

insert into ordtab(ordid) values(300)

*

ERROR at line 1:

ORA-14400: inserted partition key is beyond highest legal partition key

So,to overcome this

create table OrdTab

(

OrdId Number,

Customer_Name varchar2(10),

OrdDate Date,

Total Number

)

Partition By Range(OrdId)

(

Partition P1 VAlues Less Than (100),

Partition P2 Values Less Than (200),

Partition P3 Values Less Than (MaxValue)

);

NOW I INSERTED THE VALUES

90

1000

250

350

450

200

SELECT * FROM ORDTAB PARTITION (P1);

ORDID CUSTOMER_N ORDDATE TOTAL

--------- ---------- --------- ---------

90

TRY THE SAME THING WITH P2 AND P3.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS;

PARTITION_NAME

------------------------------

P1

P2

P3

SQL> ALTER TABLE ORDTAB SPLIT PARTITION P1 AT (50);

Table altered.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS;

PARTITION_NAME

------------------------------

P2

P3

SYS_P61

SYS_P62

SQL> SELECT * FROM ORDTAB PARTITION(SYS_P61);

ORDID CUSTOMER_N ORDDATE TOTAL

--------- ---------- --------- ---------

45

SQL> SELECT * FROM ORDTAB PARTITION(SYS_P62);

ORDID CUSTOMER_N ORDDATE TOTAL

--------- ---------- --------- ---------

90

65

ALTER TABLE ORDTAB SPLIT PARTITION P2 AT

(150) INTO (PARTITION P5,PARTITION P6);

Table altered.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS;

(ALSO GIVE THE COMMAND

SELECT PARTITION_NAME,TABLE_NAME FROM USER_tAB_PARTITIONS)

SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM

USER_tAB_PARTITIONS;

PARTITION_NAME

------------------------------

P5

P3

SYS_P61

SYS_P62

P6

ADD PARTITION

~~~~~~~~~~~~~

CREATE TABLE GG

( ACNO NUMBER(3),

actype varchar2(3),

acbal number(4))

partition by range(acbal)

(partition p1 values less than(5000),

partition p2 values less than(6000));

Table created.

SQL> alter table gg add partition maxp values less than(maxvalue);

Table altered.

DROP PARTITION

~~~~~~~~~~~~~~~~~

ALTER TABLE GG DROP PARTITION P1;

ALTER TABLE DROP PARTITION

RENAME PARTITION

~~~~~~~~~~~~~~~~~~

ALTER TABLE EMP RENAME PARTITION P5 TO P6;

create table ordtab

( ordid number(4),

prdid number(4),

ordname varchar2(10)

)

partition by range(ordid,prdid)

( partition p1 values less than(25,50),

partition p2 values less than(50,75),

partition p3 values less than(75,100)

);

************************************************************************

TO CREATE AN INDEXED PARTITION

create table OrdTab

(

OrdId Number,

Customer_Name varchar2(10),

OrdDate Date,

Total Number

)

Partition By Range(OrdId)

(

Partition P1 VAlues Less Than (100),

Partition P2 Values Less Than (200),

partition p3 values less than(MAXVALUE)

);

CREATE INDEX ORDID ON ORDTAB(ORDID)

LOCAL

(PARTITION P1,PARTITION P2,PARTITION P3);

CREATE INDEX ORDID ON ORDTAB(ORDID)

GLOBAL PARTITION BY RANGE(ORDID)

(PARTITION P1 VALUES LESS THAN(10),

PARTITION P2 VALUES LESS THAN(20),

PARTITION P3 VALUES LESS THAN(MAXVALUE));

LIST THE TREE STRUCTURED QUERY WITH EMPLOYEE NAME STARTING

WITH KING

SOL: SELECT LPAD(' ',6*(LEVEL-1))||ENAME NAME FROM EMP

START WITH ENAME='KING'

CONNECT BY MGR=PRIOR EMPNO;

~~~~~~~~~~~~~~~~~~~~

SIMPLE PL/SQL BLOCKS

~~~~~~~~~~~~~~~~~~~~

declare

a number;

b number;

begin

A:=25;

B:=50;

dbms_output.put_line('THE CONTENT OF A IS'||A);

dbms_output.put_line('THE CONTENT OF B IS'||B);

end;

declare

a number:=&A;

b numbeR:=&B;

begin

dbms_output.put_line('THE CONTENT OF A IS '||A);

dbms_output.put_line('THE CONTENT OF B IS '||B);

end;

declare

a number:=&A;

b numbeR:=&B;

PROD NUMBER;

begin

PROD:=A*B;

dbms_output.put_line('THE CONTENT OF A IS '||A);

dbms_output.put_line('THE CONTENT OF B IS '||B);

dbms_output.put_line('THE PRODUCT IS '||PROD);

end;

declare

a number:=10;

b number:=20;

res number;

begin

res:=a+b;

dbms_output.put_line(a|| ' '||b);

dbms_output.put_line('The sum is '||res);

end;

declare

a number:=&a;

b number:=&b;

res number;

begin

res:=a+b;

dbms_output.put_line(a|| ' '||b);

dbms_output.put_line('The sum is '||res);

end;

declare

a number:=&a;

b number:=&b;

begin

if (a>b) then

dbms_output.put_line('Biggest number is'||' '||a);

else

dbms_output.put_line('Biggest number is'||' '||b);

end if;

end;

declare

a number:=&a;

begin

if mod(a,5)=0 then

dbms_output.put_line('It is divisible by 5');

else

dbms_output.put_line('It is not divisible by 5');

end if;

end;

declare

n char(10):='&n';

begin

SELECT UPPER(N) INTO N FROM DUAL;

if n='F' then

dbms_output.put_line('FIRST CLASS');

ELSIF N='S' THEN

dbms_output.put_line('SECOND CLASS');

ELSIF N='T' THEN

dbms_output.put_line('THIRD CLASS');

else

dbms_output.put_line('FAILED');

END IF;

END;

declare

n number:=1;

begin

loop

dbms_output.put_line(n);

n:=n+1;

exit when n=11;

end loop;

end;

declare

a number:=1;

begin

while (a ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download