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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- simple srinimf
- sql tutorial
- developing a reporting solution with
- sql is a standard computer language for accessing and
- nikos dimitrakas
- it is possible to use a simple sql and access the database
- these are revisons of the notes i gatherer in preparation
- constraints bilaterals org
- sql create database statement
Related searches
- facts assumptions constraints limitations army
- mdmp constraints and limitations
- constraints vs limitations mission analysis
- constraints vs restraints military planning
- army constraints and limitations
- army doctrine constraints and limitations
- military constraints and restraints
- military constraints and restraints examples
- constraints of air pollution
- facts assumptions constraints and limitations
- army constraints vs limitations
- constraints versus restraints military