Oracle EMP,DEPT,BONUS,SALGRADE script

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369, ‘SMITH’, ‘CLERK’, 7902,
TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, ‘ALLEN’, ‘SALESMAN’, 7698,
TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, ‘WARD’, ‘SALESMAN’, 7698,
TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, ‘JONES’, ‘MANAGER’, 7839,
TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, ‘MARTIN’, ‘SALESMAN’, 7698,
TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, ‘BLAKE’, ‘MANAGER’, 7839,
TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, ‘CLARK’, ‘MANAGER’, 7839,
TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, ‘SCOTT’, ‘ANALYST’, 7566,
TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, ‘KING’, ‘PRESIDENT’, NULL,
TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, ‘TURNER’, ‘SALESMAN’, 7698,
TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, ‘ADAMS’, ‘CLERK’, 7788,
TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, ‘JAMES’, ‘CLERK’, 7698,
TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, ‘FORD’, ‘ANALYST’, 7566,
TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, ‘MILLER’, ‘CLERK’, 7782,
TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

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’);

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

Advertisements

Simple Stored Procedure

USE MASTER_DB;
GO
CREATE PROCEDURE SP_GetEmployees_Details
@ENAME nvarchar(50),
@JOB nvarchar(50)

AS

SET NOCOUNT ON;
SELECT e.ENAME, e.JOB, d.DNAME,e.SAL,*
FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO
AND e.ENAME = @ENAME AND e.JOB = @JOB
AND e.COMM IS NULL;
GO

EXECUTE SP_GetEmployees_Details N’BLAKE’, N’MANAGER’;
— Or
EXEC SP_GetEmployees_Details @ENAME = N’BLAKE’, @JOB = N’MANAGER’;
GO
— Or
EXECUTE SP_GetEmployees_Details @ENAME = N’BLAKE’, @JOB = N’MANAGER’;
GO

How to change the column data type to Identity?

How to change the data type int to identity (1,1)? 

When we set identity to any column no need to insert the value. System will automatically inserts the sequential number. It is not possible to change the data type to identity for tables which have large amount of data. Here is the way: Run the below query to create Temp table with the same data structure.  Select * into Temp_table from Original_Table where 1=2;  Right click on the table Select the column Go to the properties Select ‘Is Identity’ option to ‘Yes’ Save.  Now execute the below SQL statement: Insert into Temp_Table select * from Original_table  Now all the data loaded into Temp_Table. Now drop the Original_Table Rename the  Temp_Table to Original_Table. Create keys if existed on Original_Table.How to change the data type int to identity (1,1)? When we set identity to any column no need to insert the value. System will automatically inserts the sequential number. It is not possible to change the data type to identity for tables which have large amount of data. Here is the way: Run the below query to create Temp table with the same data structure.  Select * into Temp_table from Original_Table where 1=2;  Right click on the table Select the column Go to the properties Select ‘Is Identity’ option to ‘Yes’ Save.  Now execute the below SQL statement: Insert into Temp_Table select * from Original_table  Now all the data loaded into Temp_Table. Now drop the Original_Table Rename the  Temp_Table to Original_Table. Create keys if existed on Original_Table.