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

fnsearchstring

select * from fnsearchstring(‘%customer%’)

How to delete a stored procedure

SELECT name as stored_procedure_name
,SCHEMA_NAME(schema_id) as schema_name
,type_desc
,create_date
,modify_date
FROM sys.procedures;

DROP PROCEDURE <stored_procedure_name >;
GO

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

Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

Msg 945, Level 14, State 2, Line 1
Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Capture

Recovery Pending.

Untitled

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

Alter account & unlock

Execute the below SQL code in master database:

ALTER LOGIN sa WITH PASSWORD=’password’ UNLOCK

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.