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 … Continue reading Oracle EMP,DEPT,BONUS,SALGRADE script

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 Continue reading Simple Stored Procedure

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. Recovery Pending. Continue reading Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

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 Continue reading Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

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 … Continue reading How to change the column data type to Identity?